DB2 Server for VSE. Operations Guide / Handbooks (2004-2007)

You also need to substitute your minidisk addresses or SFS directory names in

the VMSES/E commands in any subsequent steps.

5. Copy the new ARISQLLD LOADLIB to SQLMACH’s production and service

disk or directory.

a. If installing on minidisks, enter the following commands:

access 195 i

vmfcopy arisqlld L* i = = l (prodid 5697F42X%DB2VM olddate replace

access 193 j

vmfcopy arisqlld L* j = = m (prodid 5697F42X%DB2VM olddate replace

The VMFCOPY command updates the VMSES PARTCAT file on the

production disk (195) and the service disk (193).

b. If installing using Shared File System, enter the following commands:

access 5697F42X.sql.production i

access SQLMACH.sql.production l

vmfcopy arisqlld L* i = = l (prodid 5697F42X%DB2VM olddate replace

access 5697F42X.sql.service j

access SQLMACH.sql.service m

vmfcopy arisqlld L* j = = m (prodid 5697F42X%DB2VM olddate replace

The VMFCOPY command updates the VMSES PARTCAT file.

Step 10: Resave the DBSS Saved Segment

If you are using a saved segment for DBSS then you need to resave it. Use the

following steps to resave the saved segment, otherwise continue with Step 11.

Step 10a. Prepare to Build the DB2 Server for VM Segments: Before building the

new DB2 Server for VM segment, following these steps:

Clear your virtual machine by entering the following IPL command. This

command bypasses the execution of the system profile (SYSPROF EXEC) and

without loading the installation saved segment (CMSINST).

ipl cms parm clear nosprof instseg no

Note: ** DO NOT press ENTER at the VM READ!**

Bypass the execution of the PROFILE EXEC by entering the following

command:

access (noprof

Access the VMSES/E code by entering the following command:

access 5e5 b

Link and access the Software Inventory disk by entering the following

commands:

link MAINT 51d 51d mr

access 51d d

Access the database machine, SQLMACH, production minidisk or SFS directory

by entering the following command:

access vdev k

vdev is the address the database machine production minidisk is linked as by

the installation user ID, or vdev is the name of the database machine production

SFS directory. You need write access to this minidisk or directory.

Before running the VMFBLD command to save the segments, activate the user

language files by entering the following CMS command:

set language ameng (add ari user

Chapter 6. Data Spaces Support for VM/ESA

181

7. Release the database machine, SQLMACH, production minidisk or SFS

directory by entering the following command:

rel k

Step 10b. Build the DB2 Server for VM Segments: To build the DB2 Server for

VM segments, enter the following command:

vmfbld ppf segbld esasegs segblist SQLSQLDS (serviced

If you are using a different name for the DBSS saved segment substitute your

name in place of SQLSQLDS in the VMFBLD command. The ARISAVES is called

by the VMFBLD command.

Step 10c. Create a Bootstrap Package: If you responded YES when prompted by

the ARISAVES EXEC to use the saved segments you that loaded as defaults, you

do not have to do this step, as ARISAVES would have generated a default

bootstrap package (SQLDBA) for you. Continue with “Step 11: Log off the DB2 for

VM Installation User ID” on page 184.

If you answered NO to the prompt, you must run the SQLGENLD EXEC to create

a bootstrap package for the saved segments you loaded. To run SQLGENLD EXEC,

you must log off of the installation user ID and log on to the database machine.

Because SQLGENLD prompts you for certain information about the new bootstrap,

you should determine the contents of the bootstrap package before you run the

SQLGENLD EXEC. For more information, see “Contents of a Bootstrap Package.”

Contents of a Bootstrap Package: A bootstrap package contains modules created by

the SQLGENLD EXEC. SQLGENLD places the modules on the production

minidisk (Q-disk). Note that, even though the DBSS and RDS components are

loaded in different saved segments, there is only one bootstrap module for them.

All of those components are needed to run the DB2 Server for VM system code in

a database machine. Thus, one bootstrap identifies the location of the DBSS and

RDS components.

Not all modules are needed because the database manager uses defaults when a

module of a bootstrap is missing. For more information on the defaults, see “Using

SQLGENLD” on page 183.

Figure 37 summarizes the different bootstrap modules that you can have.

Resource adapter

dcssid

SQLRMBT

DBSS/RDS ...

dcssid

SQLDBBT

ISQL

dcssid

SQLISBT Q

Figure 37. Bootstrap Package Contents

The dcssid (saved segment ID) is the name you give to the bootstrap package with

SQLGENLD. It is the dcssid that you use in the DCSSID parameter of various

IBM-supplied execs (such as, SQLSTART or SQLINIT). When dcssid is specified in a

DCSSID parameter, the bootstrap package production disk entries are copied to the

182

Performance Tuning Handbook

execution machine’s A-disk as shown in Figure 38.

Production

| Execution Machine

Q-disk Entry

| A-disk Entry

--------------------- |

|---------------------

FM | COPY/RENAME | FN

------

-------

-- |-------------| --------

------

dcssid

SQLRMBT

| ARISRMBT MODULE A

dcssid

SQLDBBT

| ARISDBBT MODULE A

dcssid

SQLISBT

| ARISISBT MODULE A

Figure 38. Bootstraps Copied to the Execution Machine A-disk

The resource adapter bootstrap is incomplete when it is copied to the A-disk of the

user machine. It is completed when the user runs the SQLINIT EXEC, which

supplies the missing server name to be accessed.

Use SQLGENLD to generate bootstrap packages for running the database manager

in saved segments. You cannot use this EXEC to generate a bootstrap package for

running the database manager in a default mode. The SQLDBA bootstrap package

identifies the default mode, which can be default saved segments (if you have

defined them) or user free storage.

Using SQLGENLD: When you identify the bootstraps to be contained in the

package you are creating and the location where you want them to load the code,

you can use the SQLGENLD EXEC. To use SQLGENLD, obtain read access to the

service minidisk by entering the following command:

access 193 v

You can run SQLGENLD only from the database machine:

sqlgenld

When it runs, the SQLGENLD EXEC obtains both read and write access to the

production minidisk. Both kinds of access are available to a defined database

machine. You should ensure that no other machine has write access to the

production minidisk when you run SQLGENLD.

If you are running SQLGENLD from a database machine that does not own the

production minidisk, SQLGENLD prompts you for the write password.

The SQLGENLD EXEC prompts you for dcssid. This is the name of the new

bootstrap package. If a bootstrap package with this name already exists,

SQLGENLD replaces the existing bootstraps. The EXEC does not let you replace

the initial SQLDBA bootstrap package. The SQLDBA bootstrap package is used as

a default by many IBM-supplied execs. Do not modify or erase the SQLDBA

bootstrap package.

When you supply dcssid, SQLGENLD prompts if you want to create a resource

adapter bootstrap, a DBSS/RDS bootstrap, and an ISQL bootstrap. For each

bootstrap that you choose to create, you are prompted for the saved segment name

(or, in the case of DBSS/RDS, names). The name is the name you used in the

DEFSEG command.

The database manager prompts if you want this bootstrap package to be the

default DCSSID for user machines that have a link to this production (Q) disk.

Specify this as the default if you have users linking to this Q-disk who will be

Chapter 6. Data Spaces Support for VM/ESA

183

accessing a database machine that does not own this production (Q) disk, and if

you do not have saved segments identified by the SQLDBA bootstrap package.

Because the database manager provides a default DCSSID, these users are not

required to specify the DCSSID parameter when they run the SQLINIT EXEC.

Note: The SQLDCSID DEFAULT file cannot be used by a user if the file resid

SQLDBN exists on the production (Q) disk they are linked to. This is

because the default bootstrap package for a database is identified in the resid

SQLDBN file. The SQLDCSID DEFAULT file is used by users that are

accessing an application server other than the one that owns the Q-disk to

which they are linked.

If you say that you want this bootstrap to be the default for users with a link to

this production (Q) disk, a new file SQLDCSID DEFAULT will be created on the

production (Q) disk to contain the default DCSSID. When the bootstraps are

created, SQLGENLD places them on the production minidisk. They are then erased

from the database machine A-disk.

Step 10d. Restart the Application Server: Restart the application server in

multiple user mode with the required PROTOCOL parameter.

Step 11: Log off the DB2 for VM Installation User ID

Log off the DB2 Server for VM installation machine (if not already done).

Step 12: Log onto the SQLMACH Machine

Log onto the SQLMACH machine (if not already done).

Step 13: Verify the Installation

You must now verify that you enabled the VMDSS code successfully.

Perform either Step 13A or 13B, depending on whether you chose to enable in

non-XC mode or XC mode.

Step 13A: Verify non-XC Mode Installation

Check that your database machine is not in XC mode by typing #cp query set. For

example:

#cp query set

cp query set

MSG ON

, WNG ON

, EMSG ON

, ACNT OFF, RUN OFF

LINEDIT ON , TIMER ON

, ISAM OFF, ECMODE ON

ASSIST OFF

, PAGEX OFF, AUTOPOLL OFF

IMSG ON

, SMSG ON

, AFFINITY NONE

, NOTRAN OFF

VMSAVE OFF, 370E OFF

STBYPASS OFF

, STMULTI OFF

00/000

MIH OFF , VMCONIO OFF , CPCONIO OFF , SVCACCL OFF , CONCEAL OFF

MACHINE XA, SVC76 CP, NOPDATA OFF, IOASSIST OFF

CCWTRAN ON

If you are in XC mode (MACHINE XC), go to “Step 2: Update the CP Directory”

on page 177 and check that the CP directory entries listed in that step are not

included in your database machines directory entries.

Start the application server in multiple user mode by entering:

SQLSTART DB(server_name)

184

Performance Tuning Handbook

Replace server_name with the name of your database. This name is specified in the

IUCV *IDENT statement contained in the CP directory for the database machine. For

more information on the SQLSTART command, refer to the DB2 Server for VSE &

VM Operation manual.

For example:

sqlstart db(sqldba)

ARI0717I Start SQLSTART EXEC: 05/28/99 11:05:52 EDT.

ARI0320I The default database name is SQLDBA.

ARI2015I The storage pool specification input file was not

found. The database manager will use the default values.

ARI2020I The machine is not in XC-mode.

Data spaces will not be used.

ARI2027I No storage pools will use data spaces.

ARI0283I Log analysis is complete.

ARI0282I LUW UNDO is completed.

ARI0281I LUW REDO is completed.

ARI0143I The application server has been initialized

with the following values:

CHARNAME = INTERNATIONAL, DBCS = NO, CHARSUB = SBCS,

CCSIDSBCS = 500, CCSIDMIXED = 0, CCSIDGRAPHIC = 0.

ARI0134I Application server FTMACH6 has been

identified as a global resource.

ARI0060I database manager initialization complete.

ARI0045I Ready for operator communications.

Note, the underlined messages show that VMDSS is installed. Because the database

machine is not in XC mode, the database manager will use the standard DASD

I/O system instead of Data Spaces Support. To confirm this, type counter pool 1.

You should see something like the following:

counter pool 1

Counter values at DATE=’05-28-99’ TIME=’11:14:35’.

Pool No. 1:

*BLOCKIO

Pages looked at in the buffer LBUFLOOK:

121

Page reads

PGREAD

Page writes

PGWRITE :

IUCV *BLOCKIO I/O requests

IUCVBIO :

ARI0065I Operator command processing is complete.

If *BLOCKIO appears, this tells you that the database manager is using the standard

DASD I/O system for storage pool 1.

While you cannot use data spaces in non-XC mode, you can use striping. Since it is

the default setting for the database manager to use striping, you should see

something like the following if you type show pool 1:

Chapter 6. Data Spaces Support for VM/ESA

185

show pool 1

POOL NO.

NUMBER OF EXTENTS = 2

BLK STR

EXTENT TOTAL NO. OF

NO. OF

NO.

PAGES PAGES USED FREE PAGES RESV PAGES USED

855

778

855

TOTAL

1710

1633

ARI0065I Operator command processing is complete.

If STR appears, this tells you that the database manager is using striping for storage

pool 1.

Continue with “Step 14: Optional System Activities” on page 187.

Step 13B: Verify XC Mode Installation

Check that your database machine is in XC mode by typing #cp query set. For

example:

#cp query set

CP QUERY SET

MSG ON

, WNG ON

, EMSG ON

, ACNT OFF, RUN OFF

LINEDIT ON , TIMER OFF , ISAM OFF, ECMODE ON

ASSIST OFF

, PAGEX OFF, AUTOPOLL OFF

IMSG ON

, SMSG ON

, AFFINITY NONE

, NOTRAN OFF

VMSAVE OFF, 370E OFF

STBYPASS OFF

, STMULTI OFF

00/000

MIH OFF , VMCONIO OFF , CPCONIO OFF , SVCACCL OFF , CONCEAL OFF

MACHINE XC , SVC76 CP, NOPDATA OFF, IOASSIST OFF

CCWTRAN ON

If you are not in XC mode, return to “Step 2: Update the CP Directory” on page

177 and check the CP directory entries for your database machine.

Start the application server in multiple user mode by entering:

SQLSTART DB(server_name)

Note: A storage pool used only for internal dbspaces and which has a dbextent on

a virtual disk cannot be used with data spaces turned on for that pool. This

storage pool must be specified with the BLK and SEQ options in the storage

pool specification file. See Appendix A, “Storage Pool Specification File

Format,” on page 207.

Replace server_name with the name of your database. This name is specified in the

IUCV *IDENT statement contained in the CP directory for the database machine. For

more information on the SQLSTART command, refer to the DB2 Server for VSE &

VM Operation manual.

For example:

186

Performance Tuning Handbook

sqlstart db(sqldba)

ARI0717I Start SQLSTART EXEC: 05/28/99 10:51:06 EDT.

ARI0320I The default database name is SQLDBA.

ARI0015I SEPINTDB parameter value is Y.

ARI0016I SAVEINTV parameter value is 10.

ARI0015I MAPPING parameter value is L.

ARI0016I TARGETWS parameter value is 32.

ARI2015I The storage pool specification input file was not

found. The database manager will use the default values.

ARI2026I Some or all storage pools will use data spaces.

ARI0283I Log analysis is complete.

ARI0282I LUW UNDO is completed.

ARI0281I LUW REDO is completed.

ARI0143I The application server has been initialized

with the following values:

CHARNAME = INTERNATIONAL, DBCS = NO, CHARSUB = SBCS,

CCSIDSBCS = 500, CCSIDMIXED = 0, CCSIDGRAPHIC = 0.

ARI0134I Application server FTMACH6 has been

identified as a global resource.

ARI0060I database manager initialization complete.

ARI0045I Ready for operator communications.

Note, the underlined messages show that VMDSS is installed and, because the

database machine is in XC-mode, the database manager is using Data Spaces

Support. To confirm this, type counter pool 1. You should see something like the

following:

counter pool 1

Counter values at DATE=’05-28-99’ TIME=’10:59:24’.

Pool No. 1: Data Spaces

Pages looked at in the buffer LBUFLOOK:

121

Pages moved from DS to buffer DSREAD

Pages moved from buffer to DS DSWRITE :

DS page fault notifications

DSFAULT :

ARI0065I Operator command processing is complete.

If Data Spaces appears, this tells you that the database manager is using Data

Spaces Support for storage pool 1.

Step 14: Optional System Activities

You may now choose any of the following options:

v Create a new database to use Data Spaces Support (refer to “Using Data Spaces

Support with a New Database” on page 195).

v Reblock the directory disk of an existing database to use Data Spaces Support

(refer to “Using Data Spaces with the Directory” on page 193).

v Change the VMDSS storage pool specifications (refer to “Storage Pool

Specifications” on page 188). These specifications turn Data Spaces Support on

and off, set storage residency priorities, and turn striping on and off.

v Change the VMDSS initialization parameters (refer to the DB2 Server for VSE &

VM Operation manual). These parameters set your application server’s save

interval, target working storage, and whether it will use mapped or unmapped

internal dbspaces.

Chapter 6. Data Spaces Support for VM/ESA

187

Disabling VMDSS

If you want to disable VMDSS from your service and production disks perform the

Steps 1-4 and 6 in the database machine (SQLMACH), and Step 5 from the

installation user machine.

Disable Step 1: Archive your Database

If you regularly archive your database, type the following at the operator console:

SQLEND ARCHIVE

For more information on the SQLEND command, refer to “Step 2: Update the CP

Directory” on page 177 or see the DB2 Server for VSE & VM Operation manual.

Disable Step 2: Access the Service Disk or Directory

Accesses the DB2 Server for VM service minidisk with file mode V.

ACCESS 193 V

If you are using a service SFS directory instead of a minidisk, access it with file

mode V.

Disable Step 3: Reblock the Directory Disk

Your directory disk must be formatted with a block size of 512-bytes in order for

the database manager to be able to use it without VMDSS. If it is formatted with

the 4KB size, you must reblock it. Follow the instructions in “Reblocking the

Database Directory” on page 193.

Disable Step 4: Remove the VMDSS Files

To remove the VMDSS files you need to run ARISDBMA with the DSS(N) option.

See “Step 6: Activate VMDSS” on page 179.

Disable Step 5: Link-Edit the Load Library

Rebuild the database manager without VMDSS by link-editing the DBSS

component. To do the link-edit on the load library follow “Step 8: Log onto the

DB2 for VM Installation User ID (5697F42X)” on page 179 through “Step 10:

Resave the DBSS Saved Segment” on page 181. These steps will include the

rebuilding of the DBSS saved segment.

Disable Step 6: Restart the Application Server

Start the application server in multiple user mode using your normal operating

procedures.

Operating

This section describes how to complete the tasks associated with operating and

customizing VMDSS.

Storage Pool Specifications

There are three VMDSS specifications that you can set for storage pools:

v Whether Data Spaces Support or the standard DASD I/O system is used

v The working storage residency priority, for those pools that use Data Spaces

v Whether or not striping is used.

The default settings are that every storage pool will use data spaces, a working

storage residency priority of 3, and striping.

188

Performance Tuning Handbook

Note: A storage pool used only for internal dbspaces and which as a dbextent on a

virtual disk cannot be used with data spaces turned on for that pool. This

storage pool must be specified with the BLK and SEQ options in the storage

pool specification file. See Appendix A, “Storage Pool Specification File

Format,” on page 207.

You can change these settings either at database startup, or (except for the first

one) dynamically while the database is running.

Changing Storage Pool Specifications at Startup

To change the storage pool specifications of your database at startup, you need to

create a storage pool specification file. You can read the next few sections to learn

how to do this, or you can refer to Appendix A, “Storage Pool Specification File

Format,” on page 207 for a summary of the file’s syntax.

At startup, the application server looks for the storage pool specification file. It

should have a file name that corresponds to your database’s server_name, a file type

of ARISPOOL and a file mode of *.

If you want to use a different file name or file type, enter a CMS FILEDEF

command to identify a file as the storage pool specification file. For example:

FILEDEF ARISPOOL DISK SPSPEC FILE A

where SPSPEC FILE A identifies the storage pool specification file. The FILEDEF

syntax is:

►►

FILEDEF ARISPOOL DISK filename filetype filemode

►◄

filename filetype filemode

Specifies the file name, file type, and file mode of the storage pool

specification file.

Add your specifications to the specification file as described below, and start the

application server. If you want to add or change any specifications, you must:

1. Stop the application server (SQLEND)

2. Update the storage pool specification file

3. Restart the application server (SQLSTART)

Specifying Either Data Spaces Support or Standard DASD I/O

To change this setting for a particular storage pool, add a line to the specification

file to specify either Data Spaces Support (DS) or standard DASD I/O (BLK). (DS

is the default.)

Note: If your internal dbspaces reside in a storage pool which contains a virtual

disk, you MUST specify BLK and SEQ for that storage pool. A virtual disk

cannot be mapped to a Data Space.

For example, consider a database with five storage pools. To use Data Spaces

Support for storage pool 1 and standard DASD I/O for pools 2 to 5, your

specification file would look like:

Chapter 6. Data Spaces Support for VM/ESA

189

-- Storage Pool Specification File

-- This line turns on Data Spaces Support for pool 1

2-5 BLK -- This line turns off Data Spaces Support

-- for pools 2 to 5

The text is optional comments. If you add any comments, precede them by two

dashes.

Note that DS is the default parameter, so you can also code the file like:

-- Storage Pool Specification File

2-5 BLK -- This line turns off Data Spaces Support

-- for pools 2 to 5

For information on when to use data spaces with storage pools, refer to “Storage

Pool” on page 199.

Specifying Storage Residence Priorities

To set the storage residence priority of a storage pool that uses Data Spaces

Support, add an integer (from 1 to 5) to the end of the DS parameter in your

specification file. (3 is the default.)

For example, to use priority 1 with pools 3 and 4, priority 3 with pool 2, and

priority 4 with pool 5, your specification file would look like:

-- Storage Pool Specification File

BLK -- This line turns off Data Spaces Support for pool 1

-- This line uses residency priority 3 for pool 2

3-4 DS1 -- This line uses residency priority 1 for pools 3 and 4

DS4 -- This line uses residency priority 4 for pool 5

Pool 1 is not using Data Spaces Support, so it is not assigned any priority. Pool 2 is

using the default value, so the integer 3 does not have to be included.

For a description of the five priorities and how to choose one, refer to “Choosing

Storage Residence Priorities” on page 201.

Turning Striping On and Off

To turn striping on for a particular storage pool, add the three-letter code STR to

the end of the line for that pool. To turn it off, add the code SEQ.

For example, to turn striping on for storage pools 1, 3, and 5, and to turn it off for

pools 2 and 4, your specification file would look like:

190

Performance Tuning Handbook

-- Storage Pool Specification File

BLK STR

-- Striping turned on

DS SEQ

-- Striping turned off

DS1 STR

-- Striping turned on

DS1 SEQ

-- Striping turned off

DS4

-- Striping left on by default

For information on when to use striping, refer to “Using Striping” on page 204.

Checking Your Current Storage Pool Specifications

You can display your current storage pool specifications from the operator console,

or through ISQL, with the SHOW POOL operator command.

For example, the following screen shows you that pool 2 is using Data Spaces

Support with a storage residency priority of 3, and striping:

show pool 2

POOL NO.

NUMBER OF EXTENTS = 6

DS3 STR

EXTENT TOTAL NO. OF

NO. OF

NO.

PAGES PAGES USED FREE PAGES RESV PAGES USED

200070

55821

144249

200070

54645

145425

200070

56965

143105

200070

56336

143734

200070

55210

144860

200070

56267

143803

TOTAL

1200420

335244

865176

ARI0065I Operator command processing is complete.

For a detailed description of this command, refer to the DB2 Server for VSE & VM

Operation manual.

Changing Storage Pool Specifications Dynamically

Once the application server is running, if you want to change the setting for Data

Spaces Support (DS or BLK) you must shut down and restart the database

manager as described in “Changing Storage Pool Specifications at Startup” on page

189. However, the two other specifications —working storage residency priority

and striping— can be changed dynamically by issuing the SET POOL command

from the operator console. For example:

set pool 1 ds2 seq

ARI0065I Operator command processing is complete.

show pool 1

POOL NO.

NUMBER OF EXTENTS = 2

DS2 SEQ

EXTENT TOTAL NO. OF

NO. OF

NO.

PAGES PAGES USED FREE PAGES RESV PAGES USED

855

781

855

808

TOTAL

1710

121

1589

ARI0065I Operator command processing is complete.

Chapter 6. Data Spaces Support for VM/ESA

191

Note: Any changes you make using the SET POOL command are only in effect

while the application server is running. If you stop and restart the

application server, it will use the settings in the storage pool specification

file, which are unchanged by the SET POOL command.

For a detailed description of the SET POOL command, refer to the DB2 Server for

VSE & VM Operation manual.

Using Data Spaces with Internal Dbspaces

This section describes how to use internal dbspaces with data spaces. For

information on whether these dbspaces should be mapped or unmapped, refer to

“Internal Dbspaces” on page 199.

Unmapped Internal Dbspaces

To use separate (unmapped) internal dbspaces, set the initialization parameter

SEPINTDB to “Y” (Y is the default). For example:

sqlstart db(SQLDBA) parm(sepintdb=Y)

ARI0717I Start SQLSTART EXEC: 05/23/99 09:44:24 EDT.

ARI0320I The default database name is SQLDBA.

ARI0015I SEPINTDB parameter value is Y.

Message ARI0015I should tell you that the SEPINTDB parameter is set to Y. If it

does not, check that you are operating your database machine in XC mode. You

cannot use unmapped internal dbspaces in ESA mode.

Before you use unmapped internal dbspaces, you must allocate more DASD to VM

system paging. Refer to “VM/ESA Paging DASD” on page 173.

Attention: If VM runs out of system paging DASD, CP will abend if it does not

have sufficient spool DASD to accommodate the overflow.

Mapped Internal Dbspaces

To use mapped internal dbspaces, turn Data Spaces Support on for the storage

pool containing internal dbspaces and set the initialization parameter SEPINTDB to

“N”. For example:

sqlstart db(SQLDBA) parm(sepintdb=N)

ARI0717I Start SQLSTART EXEC: 05/23/99 09:39:02 EDT.

ARI0320I The default database name is SQLDBA.

ARI0015I SEPINTDB parameter value is N.

Note, just setting SEPINTDB=N does not turn Data Spaces Support on or off for

your internal dbspaces. You can use either Data Spaces Support or the standard

DASD I/O system with internal dbspaces. Because internal dbspaces are assigned

to one storage pool, they will use whichever DASD I/O system is specified for that

pool (see “Specifying Either Data Spaces Support or Standard DASD I/O” on page

189).

192

Performance Tuning Handbook

Note: A storage pool used only for internal dbspaces and which as a dbextent on a

virtual disk cannot be used with data spaces turned on for that pool. This

storage pool must be specified with the BLK and SEQ options in the storage

pool specification file. See Appendix A, “Storage Pool Specification File

Format,” on page 207.

Using Data Spaces with the Directory

You can use the directory with either Data Spaces Support or the standard DASD

I/O system.

To use Data Spaces Support, format your directory disk with a block size of 4096

bytes (4KB). The database manager will automatically use data spaces when it

detects the 4KB blocks. If the directory disk is formatted with a 512-byte block size,

the standard I/O system will be used instead.

If your directory disk is currently formatted for 512-byte blocks and you want to

use Data Spaces Support, you can reblock your disk with the SQLCDBEX EXEC

(refer to “Reblocking the Database Directory”).

If the database manager is using Data Spaces Support with the directory, you will

see the following message at startup time:

ARI2022I the database manager is using data spaces for the directory.

You can also check this information by displaying the storage pool counters for the

directory. For example:

counter pool dir

Counter values at DATE=’05-23-99’ TIME=’15:41:07’

Directory: Data Spaces

Pages looked at in the buffer LBUFLOOK:

Pages moved from DS to buffer DSREAD

Pages moved from buffer to DS DSWRITE :

DS page fault notifications

DSFAULT :

ARI0065I Operator command processing is complete.

For information on when to use data spaces with the directory, refer to “Directory”

on page 200.

Reblocking the Database Directory

The SQLCDBEX EXEC is updated for VMDSS, and now asks you which block size

you want the output directory to be. For a block size of 512 bytes, type 512; for a

block size of 4KB bytes, type 4096. If you do not type in a size and just press Enter,

the EXEC will make the output directory disk the same block size as the input

directory disk.

For more information on the standard SQLCDBEX EXEC refer to DB2 Server for

VM System Administration.

Note that if you reblock the directory from 512-bytes blocks to 4KB blocks, you

will not need as much DASD storage on the new directory disk; if you reblock

Chapter 6. Data Spaces Support for VM/ESA

193

from 4K to 512, you will need more. To calculate the number of cylinders you will

need, refer to “Database Disks” on page 174.

If you plan to switch between blocking sizes often, you may want to keep one disk

reserved for the 512-byte blocked directory and one for the 4KB directory. You can

define each disk to the appropriate size for its blocking, and copy the directory

back and forth between disks.

If you plan to move from a 512-byte to a 4KB disk of the same size, you can take

advantage of the 4KB blocking by expanding the directory to fit the new disk.

However, if you need to return to a 512-byte disk, you will have to copy it back to

a larger disk.

(When the SQLCDBEX EXEC finds that there is more room on a new disk than it

needs for the current directory, it will ask you whether it should expand the

directory to fit the new disk. If you tell it not to expand the directory, you cannot

take advantage of the unused portion of the new disk.)

Example

Consider a database with a directory disk (B-disk) at address 300 and a block size

of 512 bytes. To reblock the B-disk to 4096 bytes, run the SQLCDBEX EXEC to

copy the directory onto a new disk (305) blocked to 4KB as shown in the following

example:

194

Performance Tuning Handbook

sqlcdbex

db(SQLDBA)

ARI0717I

Start SQLCDBEX EXEC: 05/23/99 08:58:36 EDT.

ARI6102A

Enter DBEXTENT number (or LOGDSK1, LOGDSK2,

or BDISK) to copy.

(Enter a null response to end input or

enter QUIT to exit.)

bdisk

ARI6188A

Enter the output block size of the directory.

(Enter 512 or 4096,

or a null response to use the original size,

or 111(Quit) to exit)

4096

ARI6103A

Enter virtual address for new BDISK.

(Enter a null response to end input or

enter QUIT to exit.)

305

ARI6110D

Disk 305 is already formatted. Continuing will erase

all data on this disk. Do you want to use the disk?

Enter 0(No), 1(Yes), or 111(Quit).

yes

ARI6146D

Are you expanding the SQL/DS^™ directory?

Enter 0(No), 1(Yes), or 111(Quit).

ARI0647D

Do you want to do a CMS FORMAT/RESERVE command on disk

305?

Enter 0(No) or 1(Yes).

yes

ARI6118I

Formatting in progress. Please wait...

ARI6131I

Copying in progress. Please wait...

ARI6108I

Minidisk copied successfully. The SQLDBA SQLFDEF file

will be updated.

ARI6109I

SQLDBA SQLFDEF file has been updated on the A disk.

ARI6102A

Enter DBEXTENT number (or LOGDSK1, LOGDSK2,

or BDISK) to copy.

(Enter a null response to end input or

enter QUIT to exit.)

ARI0620I

SQLDBA SQLFDEF file

successfully copied to production disk.

ARI0673I

All COPY DBEXTENT processing completed successfully.

ARI0796I

End SQLCDBEX EXEC: 05/23/99 09:09:43 EDT

Ready; T=14.66/24.00 09:09:43

The database manager will now use the new directory disk at address 305. You can

confirm this when you start it. For example:

sqlstart db(SQLDBA)

ARI0717I Start SQLSTART EXEC: 05/23/99 16:06:02 EDT.

ARI0320I The default database name is SQLDBA.

ARI0663I FILEDEFS in effect are:

ARISPOOL DISK

SPSPEC FILE

ARISQLLD DISK

TEMSQLLD LOADLIB

ARISQLLD DISK

ARISQLLD LOADLIB

BDISK

DISK

305

Using Data Spaces Support with a New Database

If you are creating a new database, you can specify whether it will use Data Spaces

Support or the standard DASD I/O system with the directory.

Chapter 6. Data Spaces Support for VM/ESA

195

To use Data Spaces Support, you must format the directory disk in 4KB blocks

when you create the database. Run the SQLDBINS EXEC and include the

parameter:

DIRBLK (4096)

The SQLDBINS and the SQLDBGEN EXECs are updated in VMDSS to accept this

new parameter. For example:

sqldbins db(SQLDBA) dirblk(4096)

ARI0717I Start SQLDBINS EXEC: 07/19/99 15:02:24 EDT.

ARI6010D Do you want to install English SQL/DS HELP text?

Enter 0(No), 1(Yes), or 111(Quit).

ARI0720I Default DB2 Server for VM bootstrap file SQLDBA SQLRMBT created

on the production disk.

ARI0720I Default DB2 Server for VM bootstrap file SQLDBA SQLDBBT created

on the production disk.

ARI0720I Default DB2 Server for VM bootstrap file SQLDBA SQLISBT created

on the production disk.

ARI0721I Get DB2 Server for VM production minidisk READ access: SQLDBA 195.

ARI0717I Start SQLDBGEN EXEC: 07/19/99 15:02:33 EDT.

ARI0633A Please enter the CUU of the

BDISK disk.

300

ARI0647D Do you want to do a CMS FORMAT/RESERVE command on disk 300?

Enter 0(No) or 1(Yes).

yes

Note: Make sure that you answer Yes when asked if you want to do a CMS

FORMAT/RESERVE (message ARI0647D).

To use the standard I/O system, either include the parameter:

DIRBLK (512)

or omit the DIRBLK parameter entirely. The default value for DIRBLK is 512.

For more information on the standard SQLDBINS and SQLDBGEN EXEC refer to

DB2 Server for VM System Administration.

196

Performance Tuning Handbook

Chapter 7. Tuning Performance for Data Spaces Support

This chapter describes the various configuration options and tuning parameters

that you can use to optimize the performance of your application server with

VMDSS.

Deciding When to Use Data Spaces

This section describes the advantages of using Data Spaces Support over the

standard DASD I/O system, and when to use Data Spaces Support with:

v Storage Pools

v Internal Dbspaces

v The Directory.

Advantages

The paging system in Data Spaces Support can be much faster and more efficient

than the standard DASD I/O system.

The data spaces act like a large DASD cache, keeping the most recently used data

in the fastest storage. While this is similar to using a large pool of buffers or DASD

caching, there are significant advantages to using Data Spaces Support over these

two methods. (Refer to DB2 Server for VM System Administration for more

information on buffer pools.)

Some of the advantages are:

v Shorter path length

v Asynchronous page fault processing

v Striping

v Blocking and prefetching

v Dynamic working storage size management

v More asynchronous writes.

These are described in turn below.

Shorter Path Length

There is a series of internal processes between when the database manager

requests a page from DASD, and when the operating system transfers it to main

storage. This series is shorter when you use Data Spaces Support than when you

use the standard DASD I/O system.

Asynchronous Page Fault Processing

Since the operating system treats the buffers like part of the database manager

code, it may page them out to system paging DASD if it needs main storage.

Whenever the database manager needs a piece of code (or a buffer) that has been

moved to paging DASD, it and all its users must wait for that page to return from

DASD.

With Data Spaces Support, you can use a smaller pool of local buffers, decreasing

the chance of a buffer being paged out. If a page fault occurs in a data space (the

operating system cannot find the page in main or expanded storage) the database

manager can proceed with other users and return to the original user when the

fault has been resolved.

197

Striping

When you use striping, the database system tries to keep related data pages

physically close together on DASD. (It allocates pages in groups of 16.) Thus, when

the operating system needs to retrieve related pages from DASD, there is a good

chance that the pages will be located together. The operating system can then read

in a whole series of pages with one I/O operation, which improves the

performance of your system.

Striping also spreads these groups of 16 pages across all the dbextents in a storage

pool. If the dbextents are on separate physical devices, the operating system can

read several groups of pages at the same time (asynchronously). This improves

blocking and prefetching (see below), and helps you balance the load between

DASD packs.

Blocking and Prefetching

When you use the Data Spaces Support, the operating system tracks the way you

access pages. It records which pages you have used together (in a block) and the

order in which you use them. Then, when the database manager requests a page

from a data space, if the page is on DASD, CP will start retrieving (prefetching)

other pages in the same block in the order you previously followed. Since DASD

I/O can proceed in parallel (because of striping), this effectively places pages in

main storage before the database manager needs them.

In some cases, the database manager will pass information to the operating system

about how it expects to use pages. The operating system uses this information to

modify its own reference pattern and thereby further improve prefetching.

Dynamic Working Storage Size Management

You can dynamically manage how the database manager uses main and expanded

storage:

v You can set a target working storage size (refer to “Target Working Storage Size

Parameter” on page 166) to control how much main and expanded storage your

database machine uses.

v You can favor some storage pools over others by setting their working storage

residence priority (refer to “Working Storage Residency Priorities” on page 166).

This lets you improve the performance of critical storage pools, even if you have

a limited amount of main and expanded storage.

v You can set a save interval (refer to “The Save Interval” on page 167). When the

number of blocks of modified pages in a data space exceeds this parameter, the

database manager directs the operating system to write all the modified pages in

that data space to DASD. This reduces the number of modified pages in storage.

As a result, there are fewer pages to be saved during checkpoint processing,

which reduces checkpoint processing time.

More Asynchronous Writes

With Data Spaces Support, the database manager can write modified pages back to

DASD (refer to “Modifying Pages” on page 162 ) “more” asynchronously than

without it.

With Data Spaces Support off: If the database manager needs a buffer occupied

by a modified page, it first writes the page to DASD, then loads the buffer with a

new page.

198

Performance Tuning Handbook

When it does this, it puts the current agent into an I/O Wait State until the write is

complete. Since the database manager continues to service agents that are not in

wait states, this process is asynchronous between agents.

With Data Spaces Support on: When the database manager writes a modified

page to a data space, the current agent is not put into a wait state. The operating

system ensures that the page is eventually written to DASD (before the next

checkpoint) without stopping the current agent. This process is asynchronous

within an agent and therefore more asynchronous than without Data Spaces

Support.

Storage Pool

We suggest that you turn Data Spaces Support on for all your storage pools. Even

without adding main or expanded storage to facilitate caching, you should see

performance improve due to the advantages of shorter path length, striping,

blocking, and prefetching.

If you want the additional benefit of caching, you should first consider the cost in

main and expanded storage. Whenever you use Data Spaces Support, the

operating system will use main and expanded storage to cache any data the

database manager uses. If the database manager needs this data again, the

operating system can retrieve it quickly. However, if the cached data is not used

very often, it may be swapped out of main or expanded storage before it is

referenced again. If this happens, you are using main or expanded storage to cache

pages without receiving any of the benefit. Thus, if the main and expanded storage

in your system is limited, you should only use caching for your most active pools.

(You can effectively turn caching off for a particular storage pool without turning

Data Spaces Support off, by using working storage residence priority “1”. Refer to

“Choosing Storage Residence Priorities” on page 201.)

Internal Dbspaces

You can improve the performance of your database by using unmapped internal

dbspaces. We suggest that you do so unless you do not have enough VM paging

DASD (refer to “VM/ESA Paging DASD” on page 173).

Unmapped internal dbspaces have the following advantages over mapped ones:

v You can use all the space in your storage pools for public and private dbspaces.

v The database manager never writes unmapped internal dbspace pages to DASD.

This reduces your overall DASD I/O, without affecting the integrity of your

system. (You do not need a record of the internal dbspaces to recover your

database.) Note that the operating system may still swap unmapped internal

dbspace pages to VM paging DASD.

If you want to manage your internal dbspaces the same way you manage all your

other dbspaces, you may want to use mapped internal dbspaces. If you place your

internal dbspaces in a separate storage pool, you can turn Data Spaces Support on

or off, and set a working storage residence priority for them.

For information on how to customize your database for internal dbspaces, refer to

“Using Data Spaces with Internal Dbspaces” on page 192.

Chapter 7. Tuning Performance for Data Spaces Support

199

Directory

We suggest that you use Data Spaces Support with the directory. However, you

may choose not to if you need to switch your database machine between XC mode

and ESA mode.

Every time you switch to a processor or operating system that does not support

XC mode (for example a backup system) you must reblock the directory disk.

(Refer to “Reblocking the Database Directory” on page 193.)

For information on how to customize a database to use Data Spaces Support with

the directory, refer to “Using Data Spaces with the Directory” on page 193.

Managing Your Working Storage Size

Working storage is composed of:

v The database manager code and the storage it uses to hold control information

(control blocks)

v The directory buffers

v The local buffers

v Data space pages in main and expanded storage, including those in public,

private and internal dbspaces.

While you do not have direct control over how much storage the database

manager and its control blocks use (refer to DB2 Server for VM System

Administration under “virtual storage requirements”), you can control the amount

of storage used by the directory buffers, the local buffers, and by data space pages.

The amount of storage used by the directory buffers is NDIRBUF*560 bytes, where

NDIRBUF is the number of directory buffers. This applies whether you are using

Data Spaces Support or not. The storage used by the local buffers is NPAGBUF*4144

bytes. (Each buffer page requires 48 bytes of overhead. For example a 4KB page

requires 4096+48 bytes or 4144 bytes of storage.) By reducing or increasing the

number of directory and local buffers you are using you can reduce or increase

your working storage.

There are five parameters to help you manage the number of data space pages in

main and expanded storage that your database machine uses.

v Target working storage size

v Working storage residency priority

v SEPINTDB (mapped or unmapped internal dbspaces)

v Checkpoint interval

v Save interval.

These parameters are discussed in the following sections.

Choosing the Target Working Storage Size

The target working storage parameter (TARGETWS) helps you to balance the

amount of main and expanded storage used by your database machine, with the

amount used by other virtual machines in your VM system.

If you set TARGETWS too low, you may unnecessarily restrict the amount of

available storage your database machine can use. You may also find that the

operating system does not release pages fast enough and your current storage size

always exceeds your target. If this happens, some working storage residence

priorities are not effective. Remember, the database manager starts releasing most

200

Performance Tuning Handbook

pages when the target working storage size is reached: if your current storage size

is always greater than your target, the database manager only keeps those pages

with a residence priority of 4 or 5. In this case, pages with any other priority will

not be differentiated.

If you set it too high, your database machine may never reach the target you set.

VM may not give your database machine the amount of main and expanded

storage it asks for. You may find that the operating system restricts your working

storage size before the database manager does. If this happens, the database

manager only releases those pages with a residence priority of 1 or 2.

Once you find an acceptable target working storage size, it is important not to let

your current size exceed it by too much. If it does it can have the same effect as

setting TARGETWS too high (VM restricts storage). If your working storage is too

high, it means that you are either:

v Keeping too many modified pages in main or expanded storage (reduce

SAVEINTV)

v Using too many unmapped internal dbspace pages (use mapped internal

dbspaces instead)

v Setting your storage residence priorities too high (use a setting of 3 or less).

For information on managing modified pages, refer to “Managing Checkpoints” on

page 202, and for information on unmapped pages refer to “Unmapped Internal

Dbspaces” on page 202. For more information about how the TARGETWS

mechanism works, refer to Appendix C, “Why is the TARGETWS Value Frequently

Exceeded?,” on page 215.

Choosing Storage Residence Priorities

If you set a realistic target working storage size (large enough to be effective but

not so large as to overload the operating system), you will be able to use storage

residence priorities to favor pages from certain storage pools. When the database

manager copies a page from a data space into its buffers, it checks the residence

priority of that page. At the default value of 3, it releases the data space page from

main and expanded storage if the current working storage size is greater than the

target. However, the buffer page stays in the buffer pool until the database

manager needs the space for a new page.

For most applications, the default priority should be correct. However, if you can

identify certain storage pools as “high priority” pools that contain

performance-critical dbspaces, you can favor them by assigning them a high

residence priority. Low priority pools can be assigned a low residence priority.

You can assign one of five storage residence priorities:

The database manager always releases pages when possible,

regardless of the current working storage size. This effectively

turns caching off. (For low re-used pages.)

The database manager always releases pages, except index pages,

when possible. It will only release index pages when the current

working storage size exceeds your target. (For low re-used pages,

randomly accessed using indexes.)

The database manager releases pages when the current working

storage size exceeds your target. This is the default priority.

The database manager releases data pages when the current

Chapter 7. Tuning Performance for Data Spaces Support

201

working storage size exceeds your target. It does not release index

pages. (For high re-used pages, randomly accessed using indexes.)

The database manager never releases pages. (For only the most

re-used or most important pages where dbscans are frequent.)

Table 13 summarizes the five storage residence priorities. An R indicates that the

database manager releases a page from main and expanded storage after it has

been moved to a local buffer.

Table 13. Storage Residence Priorities

Current Working

Page Type

Storage Size

Working Storage Residence Priority

Data

≤ target

> target

Index

≤ target

> target

Unmapped Internal Dbspaces

Whether you are using mapped or unmapped internal dbspaces also affects your

current working storage size. (The operating system controls it by moving pages

from main storage to and from system paging DASD.) However, unmapped pages

are included in your total current working storage size, and can inflate it beyond

your target.

For example, if you are performing operations that use large amounts of internal

dbspace storage (creating large indexes, or sorting large tables), you may fill

unmapped internal dbspaces with pages that are not released until the index or

sort is complete. Even if this increases your current working storage above your

target, the database manager will not release internal dbspace pages internal

dbspace pages when it no longer needs the internal dbspace; frequently not until

the end of a logical unit of work.

Managing Checkpoints

A checkpoint is an internal operation where the database manager writes modified

data and status information to DASD, and writes a summary status record to the

log.

When the database manager takes a checkpoint:

v It writes all modified data and directory pages back to DASD.

v It frees all shadow pages. (Whenever it “modifies” a page in a storage pool, it

creates a new page in the same pool, and keeps the original as a shadow page.)

v It writes the log buffer out to the log disks.

v If LOGMODE=Y (no archive), the database manager clears space in the log up to

the beginning of the oldest LUW still active when the checkpoint is taken.

v It updates the directory pages to account for released shadow pages and

updated page allocation maps.

A checkpoint has two performance implications:

202

Performance Tuning Handbook

v It performs a high amount of I/O to DASD. It writes all the modified buffer

pages and data space pages back to DASD, and updates the directory disk.

v It holds up processing. User agents must wait until the checkpoint is finished

before they can proceed.

Choosing the Checkpoint Interval

To control the duration between database checkpoints, use the CHKINTVL

initialization parameter. This parameter specifies how many log pages the database

manager will fill before it takes its next checkpoint.

Setting the Time Between Checkpoints

The time between checkpoints depends on the number of modifications you make

to the database. If logging is turned on, the database manager writes to the log

every time you perform an insert, update, or delete. The more modifications you

make, the faster you will reach a checkpoint. If you only perform queries, the

database manager may never perform a checkpoint.

We recommend that you adjust the CHKINTVL parameter so that the database

manager takes a checkpoint every 10 to 15 minutes. Should you experience a

system error, it should take you no longer than 10 to 15 minutes to restart the

database manager after you have recovered your system. If you adjust CHKINTVL

so that checkpoints occur less frequently, for example every four hours, it may take

up to or more than four hours to restart your database.

If you set the CHKINTVL parameter too low, you minimize the risk of filling the

log or storage pools. However, while each checkpoint is faster, you increase the

overall number of checkpoints.

If you set it too high, you lower the overhead associated with checkpoint

processing. However, you risk filling the log and storage pools, and you increase

the time required to complete a checkpoint. It may also take longer to recover from

a system error.

Choosing the Save Interval

The SAVEINTV parameter limits the number of modified pages in main and

expanded storage. When the number of blocks of modified pages in a data space

exceeds this parameter, the database manager directs the operating system to write

all the modified pages in that data space to DASD.

This is done asynchronously, meaning that the database manager can continue

servicing other users while the save completes.

If you set the save interval appropriately, you can reduce the time it takes to

perform a checkpoint. While the checkpoints will take place at the same intervals

(the database manager still fills log pages at the same rate), they will be shorter

because there will be fewer modified pages to write to DASD.

While the default setting should work well for most databases, you may consider

changing it. If you find that your checkpoints take too long, reduce SAVEINTV. If

checkpoint processing is not a problem, consider increasing it.

You may also need to reduce SAVEINTV if your current working storage size is

always much larger than your target. The database manager does not release

modified pages from main storage until a save interval or a checkpoint. So if you

Chapter 7. Tuning Performance for Data Spaces Support

203

are using a high SAVEINTV, and performing many inserts, updates, or deletes, the

database machine may keep too many modified pages in main storage.

You can compare the number of times the database manager requests the operating

system to save pages to the number of times it performs a checkpoint, by using the

COUNTER and COUNTER INTERNAL operator commands. The COUNTER

command displays the CHKPOINT counter, which records the number of

checkpoints that occurred since the last time you reset the counter. The COUNTER

INTERNAL command displays the SAVEGNRL counter. SAVEGNRL counts the

number of times the database manager directs the operating system to write all the

modified pages in a data space to DASD. If you reset both the CHKPOINT and

SAVEGNRL counters at the same time, you can monitor the number of save

requests between each checkpoint.

For more information on the COUNTER and COUNTER INTERNAL operator

commands, refer to the DB2 Server for VSE & VM Operation manual.

Using Striping

Striping evenly distributes all new and modified pages across all the dbextents in a

storage pool. We suggest that you use striping, even if you are not using Data

Spaces Support.

For information on how to use striping, refer to “Turning Striping On and Off” on

page 190.

With One Dbextent Per Pool

You may choose not to use striping for a particular storage pool if it has only one

dbextent, because in that case the database manager cannot distribute your data

across several dbextents in the storage pool.

However, even with only one dbextent you may find a small performance

improvement. The database manager still allocates space on the disk in blocks of

16 4KB-pages. By doing this it improves the probability that the pages you need

are close together.

One Dbextent Per Device

For the storage pools that will use striping, it is recommended that you assign each

dbextent in the pool to a separate physical storage device. While the database

manager distributes pages across dbextents, it does not recognize whether those

dbextents are on the same physical device or several different ones. If you assign

two dbextents to one physical storage device, performance will be degraded,

because the database manager cannot retrieve pages from both dbextents in

parallel.

Dbextent Size

If you plan to use striping, you should define several dbextents of the same size in

each storage pool. If you have large and small dbextents mixed in the same pool,

you may find that the database manager does not distribute pages evenly across

them. Rather, it distributes pages across all the dbextents until the smallest one is

full. It then continues to fill the larger dbextents.

204

Performance Tuning Handbook

Number of Dbextents

For best performance, use at least four dbextents per storage pool. CP will only

prefetch pages from four dbextents in a storage pool simultaneously. Any less than

four means that CP does not have as many devices as possible to prefetch from in

parallel. (Refer to “Blocking and Prefetching” on page 198 for more information on

prefetching.)

Using Striping with Existing Data

Striping only evenly distributes new or modified pages. It does not reallocate

existing pages. To ensure that striping works with all your pages, unload all the

dbspaces in your database, and reload them with striping turned on. This makes

all the pages “new pages”.

Choosing Logical or Physical Mapping

Logical mapping is the default and recommended type of mapping for most

applications. However, applications that perform mostly updates may perform

better with physical mapping.

Because you can only change the mapping parameter at startup time, you should

always use logical mapping for your production applications, and consider

physical for single-user-mode dataloads.

Real Storage Requirements for Data Spaces

For each data space, CP must keep one real storage page until the database is shut

down. If you are using VMDSS with many databases or with a very large

database, and have a constrained real storage environment, this will further reduce

any real storage availability and increase system paging.

The only way to increase real storage availability in these situations is to reduce

the number of databases using data spaces, or reduce the number of storage pools

which are mapped to data spaces, or both.

For each data space which is less than or equal to 1024 megabytes, CP must keep

one real storage page until the database is shut down.

Chapter 7. Tuning Performance for Data Spaces Support

205

Appendix A. Storage Pool Specification File Format

This appendix describes the format and syntax of the control file used to tailor

VMDSS.

For an overview of storage pool specifications, refer to “Storage Pool

Specifications” on page 188. For a step by step description of how to use the

storage pool specification file, refer to “Changing Storage Pool Specifications at

Startup” on page 189.

File Format

The storage pool specification file must have a fixed record length of 80 characters.

It can include three types of lines:

Data Specifies a storage pool or a series of pools, and each pool’s VMDSS

operating parameters. (See below.)

Blank Allowed anywhere in the file.

Comment

Any line that begins with two dashes (--) is a comment line. You can also

include a comment at the end of a data line by adding two dashes there.

(See below.) The comment ends at the end of the line.

Data Line Syntax

Each data line of the storage pool specification file follows the following syntax:

(1)

►► n1

►◄

-n2

BLK

STR

--comment

SEQ

Notes:

You must include at least one of these blocks.

Specifies that you want to change the specifications for storage pool n1.

Valid values are integers from 1 to 999.

Specifies a range of storage pools from n1 to n2. Valid values are integers

from 1 to 999. n2 must be greater than or equal to n1.

BLK Turns Data Spaces Support off for the storage pools you specify.

DS Turns Data Spaces Support on for the storage pools you specify. This is the

default.

Sets the working storage residency priority of the storage pools you specify

to n. Valid values are integers from 1 to 5. The default value for n is 3.

STR Turns on striping for the storage pools you specify. This is the default.

207

SEQ Turns off striping for the storage pools you specify. The database system

will allocate pages sequentially on DASD.

comment

You can include a comment at the end of the data line. Precede it with two

dashes (--).

Note: If you do not include a storage pool in the storage pool specification file, the

database system will use all the default settings for that storage pool.

Ordering Data Lines

The database manager reads the storage pools specification file from the top down,

reading each specification in sequence. It starts with every pool’s specifications set

to the default values, and updates the current settings with every line it

encounters. For example, consider the following specification file:

-- Storage Pool Specification File

4-5

SEQ

-- Line 1

DS2

-- Line 2

DS5

-- Line 3

2-3

BLK

-- Line 4

As the database manager reads the file, pools 1 through 5 will all start with Data

Spaces Support on, a working storage residency priority of 3, and striping on.

Line 1

Turns striping off for pools 4 and 5

Line 2

Sets the working storage residency priority for pool 1 to 2

Line 3

Sets the priority for pool 4 to 5.

Line 4

Turns Data Spaces Support off for pools 2 and 3.

You can also achieve the same results with the following specification file:

-- Storage Pool Specification File

DS2 STR

BLK STR

DS5 SEQ

DS3 SEQ

While both files are effectively the same, the second file defines each pool without

relying on default values and is much easier to read and decipher.

Specification File Example

Consider a database where you want to:

v Turn on striping for storage pools 1 to 10

v Turn off striping for storage pools 11 to 20

v Only use Data Spaces Support for storage pools 2, 5, 10, 11, and 15 to 20

v Set the working storage residency priority to 2 for storage pools 10, 11, and 15 to

208

Performance Tuning Handbook

Appendix B. Determining Number of Data Spaces

This appendix describes how to calculate the maximum number of data spaces

your database machine will need, and their total size. It also describes how to

determine how many spaces your database machine is currently using.

Maximum Number of Data Spaces

To calculate the maximum number of data spaces that your database may use,

follow the instructions for the type of mapping you are using (logical or physical).

(Logical is the default and suggested type of mapping for most applications.)

Logical Mapping

If you are using logical mapping, you can calculate the maximum number of data

spaces by selecting the correct formula from Table 14. Substitute the total number

of public, private and internal dbspaces in your database into the formula. You

should also substitute Number of Data Pages by 262144 and 131072 for 32 bit and

64 bit CP addressing mode respectively.When appropriate also substitute the

number of cylinders in your directory disk and the conversion factor for your

directory disk s DASD type. (Use Table 15 to look up the directory conversion

factor for your database.)

Table 14. Calculating the maximum number of data spaces your database will use with

logical mapping.

Data Spaces

Unmapped

used with the

Internal

SQL/DS

Formula for Maximum Data Spaces (Round up to the

Dataspaces

DB2 Server for VSE. Operations Guide / Handbooks (2004-2007) - page 32