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

sqlstart

DB(SQLMACH1)

ARI0020I

Virtual machine addressing mode = 31

Virtual machine storage size = 24576KB

ARI0015I

SYNCPNT parameter value is Y.

ARI0016I

TRACEBUF parameter value is 0.

ARI0016I

CHKINTVL parameter value is 150.

ARI0016I

NCSCANS parameter value is 30.

ARI0016I

NCUSERS parameter value is 5.

ARI0016I

NDIRBUF parameter value is 100.

ARI0016I

NLRBS parameter value is 2520.

ARI0016I

NLRBU parameter value is 1000.

ARI0016I

NPACKAGE parameter value is 10.

ARI0016I

NPACKPCT parameter value is 30.

ARI0016I

NPAGBUF parameter value is 100.

ARI0016I

SLOGCUSH parameter value is 90.

ARI0016I

SOSLEVEL parameter value is 10.

ARI0016I

DISPBIAS parameter value is 7.

ARI0016I

LTIMEOUT parameter value is 0.

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.

ARI0060I

Database manager initialization complete.

ARI0045I

Ready for operator communications.

You can find the value of some of these parameters from the console log, or from

the start up options file in VM, or from an options member that is specified in the

PARM list of the start up EXEC statement. You can also use the ’SHOW

INITPARM’ operator command to display most of the parameters when the server

is running in multiple user mode. For more information, refer to the DB2 Server for

VSE & VM Operation manual.

CIRD Transaction (CICS)

CIRD is a DB2 Server for VSE-supplied transaction, that lets you display a

snapshot of the links between CICS and the application server. While it does not

provide historical information, it can help you diagnose problems with individual

transactions or get an immediate feel for the level of link contention between CICS

and the server.

It contains the following information:

v Which users are waiting for a link

v Which ones are currently using a link to access the server

v Which ones are holding a link but not accessing the application server

v Which ones previously held a link, but currently do not.

For example:

Chapter 2. Measuring Performance

DBDCCICS CONNECTED TO SERVER SQLDB1_NEWYORK_INV.

STATUS OF ONLINE DB2 FOR VSE APPLICATIONS:

TRANSACTIONS WAITING TO ESTABLISH A LINK TO THE APPLICATION

SERVER ARE:

TASKNO TRANID TERMID USERID USERDATA WAIT TIME

------ ------ ------ -------- -------- ---------

000033 MKE2

L222

00:01:32

000025 INV

L224

JIM

00:08:32

TRANSACTIONS HOLDING A LINK AND NOW ACCESSING THE APPLICATION

SERVER ARE:

TASKNO TRANID TERMID USERID USERDATA TIME USED

TOTAL LUW

FOR CURRENT TIME

ACCESS

------ ------ ------ -------- -------- ------------ ---------

000019 CISQ

DEPT222

L199

00:01:32

00:03:48

000037 INV

L209

TERRY

00:00:01

00:00:03

TRANSACTIONS HOLDING A LINK TO THE APPLICATION SERVER BUT NOT

USING ARE:

TASKNO TRANID TERMID USERID USERDATA TIME SINCE TOTAL LUW

LAST ACCESS TIME

------ ------ ------ -------- -------- ------------ ---------

000003 CISQ

WILLIAM L210

00:07:01

00:10:56

TRANSACTIONS WHICH PREVIOUSLY ACCESSED THE APPLICATION SERVER

(NOT HOLDING LINK):

TASKNO TRANID TERMID USERID USERDATA TIME SINCE

LAST ACCESS

------ ------ ------ -------- -------- ------------

000003 MKE2

ROBERT L210

00:20:04

TIME=14:28:23 DATE=04/30/99

For information on how to display CIRD transaction information, and detailed

information on how to use and interpret CIRD display information, refer to the

DB2 Server for VSE System Administration manual.

COUNTER Operator Command

While a detailed description of this operator command is included in the DB2

Server for VSE & VM Operation manual, this section includes an example of how to:

v Use the command with the RESET operator command

v Interpret the counter values

v Turn these values into performance indicators.

After resetting the counters (with the RESET operator command) and performing

several queries, the COUNTER * command was issued:

Performance Tuning Handbook

reset *

Counters reset at DATE=’09-06-99’ TIME=’14:27:00’

ARI0065I Operator command processing is complete.

counter *

Counter values at DATE=’09-06-99’ TIME=’14:58:12’

Calls to RDS

RDSCALL :

Calls to DBSS

DBSSCALL:

139

LUWs started

BEGINLUW:

LUWs rolled back

ROLLBACK:

System checkpoints taken

CHKPOINT:

Maximum locks exceeded

LOCKLMT :

Lock escalations

ESCALATE:

Waits for lock

WAITLOCK:

Deadlocks detected

DEADLCK :

Looks in page buffer

LPAGBUFF:

298722

DBSPACE page reads

PAGEREAD:

200134

DBSPACE page writes

PAGWRITE:

97451

Looks in directory buffer

LDIRBUFF:

5054

Directory block reads

DIRREAD :

4014

Directory block writes

DIRWRITE:

120

Log page reads

LOGREAD :

Log page writes

LOGWRITE:

Total DASD reads

DASDREAD:

4524

Total DASD writes

DASDWRIT:

Total DASD I/O

DASDIO

3986

Lock timeouts detected

LTIMEOUT:

ARI0065I Operator command processing is complete.

There are several important values that you can calculate from the COUNTER

command:

Sampling Interval

Δ TIME. The elapsed time between the RESET and the COUNTER

command. For more information on sampling intervals, refer to

“Monitoring Interval” on page 6.

LUW Load

BEGINLUW/Sampling Interval. This is the average rate at which the

database manager receives logical units of work. It measures the average

load on the database machine or partition. You can also use it as a relative

measure of throughput (refer to “Throughput” on page 5).

Checkpoint Load

CHKPOINT/sampling interval. This is the average rate of checkpoints.

Checkpoints are overhead; they represent an additional load on the

database machine. For more information on checkpoint processing, refer to

“Managing Checkpoints” on page 202.

Deadlock Performance

( DEADLCK/BEGINLUW )x100. Indicates the percentage of time a logical

unit of work is rolled back because of a potential deadlock. Ranges from 0

to 100% where 0% indicates that no LUWs were rolled back. While some

potential deadlocks are a normal occurrence in any multiple user system, a

value above 5% should be investigated. For more information, refer to

“Deadlock” on page 101.

This information can also be expressed as the deadlock hit ratio

(BEGINLUW/DEADLCK). Any value over 20 is usually acceptable.

Waitlock Performance

( WAITLOCK/RDSCALL )x100. Indicates the percentage of time a call to

Chapter 2. Measuring Performance

the relational data system had to wait because it needed a resource that

was blocked by an incompatible lock held by another call. Ranges from 0

to 100% were 0% indicates no waits. While waits are a normal occurrence

in any multiple user system, a value above 10% should be investigated. For

more information, refer to “Locking Contention” on page 94.

Note: While this value gives you an indication of how often an agent

waits, it does not indicate the length of each wait. Always listen to

your users and look for agents that are stuck in a lock wait with the

operator SHOW LOCK commands, refer to “Locking Contention” on

page 35.

This information can also be expressed as the waitlock hit ratio

(RDSCALL/WAITLOCK). Any value over 10 is usually acceptable.

Lock Request Block Performance

ESCALATE+LOCKLMT. Indicates the number of times that a logical unit

of work reached the user (NLRBU) or system (NLRBS) lock limit. This

value should be close to zero, which indicates that there was no shortage

of lock request blocks during the monitoring interval. For more

information, refer to “Lock Escalation” on page 99.

Local Buffers Effective Use

( 1-PAGEREAD/LPAGBUFF )x100. Indicates the percentage of time the

database manager found a page in the local buffers and did not need to

retrieve it from DASD. Ranges from 0 to 100%, where 100% means that

every page the database manager needed was in the local buffers. For

more information on the local buffer pool, refer to “Database I/O” on page

85.

This information can also be expressed as the local buffers hit ratio

(LPAGBUFF/PAGEREAD).

Directory Buffers Effective Use

( 1-DIRREAD/LDIRBUFF )x100. Indicates the percentage of time the

database manager found a page in the directory buffer pool and did not

need to retrieve it from DASD. Ranges from 0 to 100%, where 100% means

that every directory page the database manager needed was in the

directory buffer pool. For more information on the directory buffer pool,

refer to “Database I/O” on page 85.

This information can also be expressed as the directory buffers hit ratio

(LDIRBUFF/DIRREAD).

For example, from the previous output:

v Sampling Interval: 1872 seconds (14:58:12-14:27:00)

v LUW Load: 3.54/second (58/1872)

v Checkpoint Load: 2/hour (1/1872*3600) This value is not statistically valid. You

need to monitor checkpoints over a longer period of time for an accurate

calculation.

v Deadlock Performance: 1.72% (1/58)X100. While this value is statistically

questionable it is far enough below the recommended value of 5% that deadlock

should not be considered a significant problem.

v Deadlock Hit Ratio: 58 (58/1). 58 is greater than 20 and should be acceptable.

v Waitlock Performance 5.88% (4/68)X100. Below 10% and not a problem.

v Lock Request Block Performance: 0

Performance Tuning Handbook

v Local Buffers Effective Use: 33% ( 1-200134/298722 )x100. Check the type of

transactions you are running. Unless you are performing update intensive

transactions, this value should be much higher. Consider increasing the

NPAGBUFF initialization parameter.

v Local Buffers Hit Ratio: 1.49 (298722/200134)

v Directory Buffers Effective Use: 20.58% ( 1-4014/5054 )X100. This value is as bad

as the local buffer use value. Consider increasing the NDIRBUF initialization

parameter.

v Directory Buffers Hit Ratio: 1.26 (5054/4014).

DB2 VM Data Spaces Support

DB2 VM Data Spaces Support also includes additional counters that can help you

monitor the performance of the DASD I/O systems. Each storage pool has its own

set of four counters. There is also a set for internal dbspaces and a set for the

directory. For a complete description of all these counters and how to use them,

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

SHOW Commands

The SHOW commands, documented in the DB2 Server for VSE & VM Operation

manual, provide information on how your application server is performing. This

section includes examples of how to use these commands to understand how the

server is managing: storage, concurrency, locking.

Storage

Available Storage Pool Space: The SHOW DBEXTENT command displays

physical storage information about each storage pool defined. For example,

consider a database with an SOSLEVEL of 10% (refer to “Short on Storage

Cushion” on page 59):

show dbextent

POOL

TOTAL

NO. OF

NO.

PAGES PAGES USED FREE PAGES RESV PAGES USED

EXTENTS SOS

741

730

171

160

114

FREE

22500

The asterisk (*) under the SOS column indicates that storage pool number one is

short on storage. The flag is set if the difference between the NO. OF PAGES USED

and the TOTAL PAGES is less than the SOSLEVEL percentage times the TOTAL PAGES.

In this case, pool one has 99% (730/741 X 100) of its pages used or 1% or its pages

are free. Because this is less than the 10% SOSLEVEL, the flag is set.

Note: The NO. OF PAGES USED includes the number of shadow pages in the pool.

Proportion of Available Pages: The SHOW DBSPACE command shows the

division of pages between header, data, and index pages in a dbspace. For

example:

Chapter 2. Measuring Performance

show dbspace 3

TYPE

NUMBER

NUMBER OF

% FREE

NUMBER OF

OF PAGES OF PAGES OCCUPIED PAGES

SPACE

EMPTY PAGES

HEADER

( 12 %)

73 %

DATA

1365

756

( 55 %)

25 %

392

INDEX

128

( 27 %)

79 %

ARI0065I Operator command processing is complete.

(The SYSTEM.SYSDBSPACES catalog table contains additional information on

dbspaces, refer to page 39.)

This example shows more than half of the data page space is occupied. It also

shows a large number of empty data pages, which indicates that you may need to

drop and reacquire (reorganize) this dbspace. As an alternative to reorganizing the

dbspace, you can use the VM utility SQLRELEP to release the empty pages. In

VSE, bringing up the server with STARTUP=P can be used to release empty pages.

For more information, refer to “Running out of Dbspace Pages” on page 65.

Note: This command performs a dbspace scan, which can take a significant

amount of time and affect the performance of other users if the dbspace is

large. Refer to “Dbspace Scans” on page 118.

Available Dbextent Storage: The SHOW POOL command displays physical

storage information about each dbextent in a storage pool.

For example, consider a database with two storage pools and an SOSLEVEL of

10% (refer to “Short on Storage Cushion” on page 59 ):

show pool

POOL NO.

NUMBER OF EXTENTS =

EXTENT TOTAL

NO. OF

NO.

PAGES PAGES USED

FREE PAGES RESV PAGES USED

285

274

285

252

741

TOTAL

1311

307

1004

POOL NO.

NUMBER OF EXTENTS =

SHORT ON

STORAGE

EXTENT TOTAL

NO. OF

NO.

PAGES PAGES USED

FREE PAGES RESV PAGES USED

285

260

TOTAL

285

260

FREE AREAS: NUMBER OF DELETED EXTENTS =

EXTENT TOTAL

NO.

PAGES

END

10350

TOTAL

10350

Maximum number of DBEXTENTs = 64

ARI0065I Operator command processing is complete.

Performance Tuning Handbook

This example shows that pool number two is short on storage (SOS). While

dbextent number one has less storage available than dbextent number four, the

pool it belongs to (pool one) is not short of storage because it still contains two

dbextents, each containing a significant amount of storage.

Virtual Storage: The SHOW STORAGE command displays how much of the

database machine or partition’s virtual address space has been allocated to various

storage queues, refer to “Storage Queues” on page 46. Two storage queues are

created for:

v Each real agent

v The OPERATOR agent, used to process operator commands

v The CHECKPT agent, used to process checkpoints

v The RECOVERY agent, used to write archives

v An area called PROTOTYPE that is used for global storage blocks such as

accounting records.

v The TCPIP agent, used to handle TCP/IP communications. This agent is ONLY

present if you have TCP/IP communication started by your server.

Storage that has been allocated to control blocks and programming structures that

must reside below the 16MB line are indicated by a B in the LOC column. Storage

that may be allocated anywhere above or below the line is indicated by an A in the

LOC column.

The SHOW STORAGE command displays how much of the database machine or

partition’s address space has been ALLOCATED to each queue. It also displays how

much of it is actually IN USE and how much is FREE. “Free space” is space that has

been reserved by the queue but is not currently in use.

Space allocated to a specific real agent queue (including “free” space) is released at

the end of a logical unit of work (LUW). (A minimum amount of space, 8KB,

always remains allocated for each real agent queue.)

The HIGHSTOR column contains the maximum amount of storage allocated to a

queue since the RESET HIGHSTOR operator command was last issued. One high

storage entry exists for storage that is restricted to below the 16MB line and one

exists for storage that can reside either above or below the line.

A USERID column is also included for each real agent. It indicates the user ID that

held the real agent when the A, or B high storage value was set.

SUMMARY contains the total amount of virtual storage allocated to all the QUEUES. It

also contains the total amount of virtual storage allocated to the entire database

manager SYSTEM. These values include the storage allocated to the queues plus the

storage used by the database manager itself for structures such as trace buffers.

Chapter 2. Measuring Performance

show storage

Status of Storage at DATE=’01-15-99’ TIME=’11:57:28’

AGENT

LOC ALLOCATED

IN USE

FREE HIGHSTOR

USERID

OPERATOR

4096

1104

2992

4096

CHECKPT

2048

RECOVERY

4096

16384

1952

14432

144816

MARISSA

8192

72776

67360

5416

136472

LAURA

8192

2912

5280

161200

ANDREW

8192

16384

3936

12448

185016

DAVID

8192

16384

2992

13392

241600

DANIEL

8192

PROTOTYPE A

1154768

1053240

101528

1218784

307232

299136

8096

307232

SUMMARY

QUEUES

1295128

1133496

161632

1702992

358432

299136

59296

358432

PACKAGES

245760

201000

44760

270048

SYSTEM

1295952

1703816

388936

ARI0065I Operator command processing

is complete.

Concurrency

The SHOW ACTIVE command displays the status of active real agent structures.

Agents are used by the database manager to divide processor time between

multiple users and its own internal tasks, such as checkpoint processing and

operator commands. For more information, refer to “Agents” on page 88.

Use this command to:

v Identify the current state of an active agent — for example, whether it is

currently processing work or waiting, and if the latter, what its waiting for.

v How many agents are available, and how many are currently being used.

For example:

show active

Status of agents:

Checkpoint agent is not active.

User Agent:

1 User ID: SMITH

is R/W APPL

7B4

Agent is

processing and is in I/O

Wait.

User Agent:

2 User ID: MICHAEL is R/O SUBS

7B9

Agent is not processing and is in communication Wait.

User Agent:

3 User ID: JESSICA IS R/O APPL

5A4

Agent is processing an operator command.

User Agent:

4 User ID: TESTUSER IS R/W APPL

7BB

Agent is

processing and is in I/O

Wait.

User Agent:

5 User ID: MACNIELL IS R/O APPL

7B9

Agent is not processing and is in communication Wait.

agent(s) not connected to an APPL or SUBSYS.

ARI0065I Operator command processing is complete.

Performance Tuning Handbook

This display shows that there are five real agents available and they are all

currently being used (0

agent(s) not connected). It also shows that agents one,

two, four, and five are all processing work but are waiting for either

communications or an I/O operation. Agent three is currently processing an

operator command.

The SHOW USERS command (VM systems only) displays the status of both

pseudo and real agent structures. (For information on these structures, refer to

“Agents” on page 88.) You can use it to see how many, if any, pseudo agents are

waiting for real agent structures. For example, consider a database machine that

owns seven disks: four dbextents, one directory disk, and two log disks (do not

include the service or the production disk):

show users

Status of connected users:

users are connected to the application server.

Users are active.

User ID: DAVE

SQL ID: SMITH not processing

User ID: POTTS

SQL ID: BRIAN not processing

User ID: TUNA

SQL ID: FISH

Users are waiting.

User ID: KIM

SQL ID: TASK115

User ID: MICHAEL

SQL ID: MIKE2

Users are inactive.

User ID: KOHLMANN SQL ID: PETER

Agents are available.

User connections are available.

ARI0065I Operator command processing is complete.

This example shows that:

v MAXCONN is set to 58 (44+6+7+1). 44 connections are available, six users are

connected, seven disks are connected to the machine, and one for the connection

to *IDENT. (Refer to “VM (MAXCONN)” on page 92.)

v NCUSERS is set to 3 (3+0=3). All three real agents are occupied. (Refer to

“Tuning Parameters (NCUSERS)” on page 89.)

v Two users (KIM and MICHAEL) are waiting for real agents. Neither KIM nor

MICHAEL will become active until DAVE, POTTS, or TUNA complete their

current logical unit of work.

v One user (KOHLMANN) is inactive; it is neither waiting for a real agent nor is

it processing work.

v User ID TUNA is currently processing work.

Not only does the SHOW CONNECT command display much of the information

included with the SHOW ACTIVE and SHOW USERS operator commands, it also

includes:

v Information that uniquely identifies DRDA^* application requestors

v The timestamp when the current state began

v The timestamp when the user was connected to the application server

v The CPU time used since the user was connected to the server (only displayed if

you start the server with accounting on).

Chapter 2. Measuring Performance

For the VSE System

The VSE SHOW CONNECT statement contains several additional values. See

“VSE SHOW CONNECT” on page 33.

You can use this information to force specific users to end their work and

terminate their conversations with the server. Refer to the DB2 Server for VSE

& VM Operation manual.

show connect

Status of Connected Users

1999-02-04

10:25:33

Checkpoint agent is not active.

User Agent:

User-ID: SHUM

SQL-ID: SHUM

is R/W APPL

7B1

Agent is processing with LPAGEBUF=1032

State started: 1999-02-04

10:15:30

Conversation started: 1999-02-04

10:12:45

CPU time: 00:00:01

User Agent:

User-ID: SQLUSRSS SQL-ID: SQLUSRSS

is R/O APPL

30BD

Agent is not processing and is in communication wait.

State started: 1999-02-04

09:48:28

Conversation started: 1999-02-04

09:48:00

CPU time: 00:00:02

LUWID: CAIBMOML.*IDENT.A532D460755B.0001

EXTNAM: SQLUSRSS.1

Requester: SQLDS/VM V3.3.0

at TORVMLB4

User Agent:

User-ID: PETERSON SQL-ID: PETERSON

is R/O APPL

3758

Agent is processing and is in LOCK wait.

State started: 1999-02-04

10:23:11

Conversation started: 1999-02-04

10:22:15

CPU time: 00:00:01

LUWID: CAIBMOML.STLLU.A5241A50FABD.0001

EXTNAM: PETERSON

.BATCH

.PETERSON.DSNESPRR

Requester: DB2

V2.3.0

at IBMREGRDBSTL0012

User-ID: SWAGRMAN SQL-ID: SQLDBA

User is waiting for an agent

State started: 1999-02-04

10:22:11

Conversation started: 1999-02-04

10:03:05

CPU time: 00:00:02

LUWID: IBMNET07.*IDENT.AB457DFF69BC.0001

EXTNAM: SWAGRMAN.1

Requester: SQLDS/VM V3.3.0

at TOROLAB3

User-ID: JAVIER SQL-ID: JAVIER

User is inactive

State started: 1999-02-04

10:02:11

Conversation started: 1999-02-04

09:27:49

CPU time: 00:00:03

LUWID: IBMNET07.*IDENT.AB457DFF6ABC.0001

EXTNAM: JAVIER.1

Requester: SQLDS/VM V3.3.0

at TOROLAB

Users are active.

Users are waiting.

Users are inactive.

Agents are available.

User connections are available.

ARI0065I Operator command processing is complete.

Performance Tuning Handbook

The current time is 10:25:33. There are three active users:

v Agent 1 (SHUM) has been processing for approximately ten minutes

(10:25-10:15), and has used one second of CPU time since it connected to the

server.

v Agent 2 (SQLUSRSS) has been in a communication wait for almost 37 minutes,

and has used two seconds of CPU time.

v Agent 3 (PETERSON) has been in a lock wait for a little less than two minutes,

and used one second of CPU time.

There are two other connected users. One is waiting for an agent, the other is

inactive.

v User-ID SWAGRMAN has been waiting for an agent for a little over three

minutes, and has used two seconds of CPU time.

v Finally, User-ID JAVIER has been inactive for over 23 minutes, and has used

three seconds of CPU time.

You can also determine from the additional lines of information (LUWID,

EXTNAM, Requester) that all the requestors, with the exception of Agent 1, are

DRDA requestors.

The example below an agent is executing a stored procedure.

Chapter 2. Measuring Performance

show connect

Status of Connected Users

1999-09-30

08:56:42

Checkpoint agent is not active.

User Agent:

User-ID: SQLUSRKJ SQL-ID: SQLUSRKJ

is R/O APPL

1666

Agent is not processing and is in communication wait.

State started: 1999-09-30

08:56:39

Conversation started: 1999-09-30

08:56:12

Protocol: SQLDS

Package: SQLDBA.MAINPGM

Section: 4

Procedure: PROC1

Modname: MYPROC

Procedure Package: SQLDBA.MYPROC

Section: 4

User Agent:

User-ID: SQLUSRJR SQL-ID: SQLUSRJR

is R/O APPL

1667

Agent is not processing and is waiting for a stored procedure

server in group GROUP1

State started: 1999-09-30

08:56:39

Conversation started: 1999-09-30

08:56:12

Protocol: SQLDS

Package: SQLDBA.MAINPGM2

Section: 3

User Agent:

User-ID: SQLUSRTH SQL-ID: SQLUSRTH

is R/O APPL

1668

Agent is processing with LPAGEBUFF=1032

State started: 1999-09-30

08:56:39

Conversation started: 1999-09-30

08:56:12

Protocol: SQLDS

Package: SQLDBA.MAINPGM3

Section: 4

Procedure: PROC3

Modname: MYPROC3

Procedure Package: SQLDBA.MYPROC3

Section: 2

User Agent:

User-ID: SQLUSRJR SQL-ID: SQLUSRTL

is R/O APPL

1669

Agent is not processing and is waiting for stored procedure

PROC4 AUTHID SQLUSRTL to be started

State started: 1999-09-30

08:58:00

Conversation started: 1999-09-30

08:57:35

Protocol: SQLDS

Package: SQLDBA.MAINPGM4

Section: 3

Users are active.

Users are waiting.

Users are inactive.

Agents are available.

User connections are available.

ARI0065I Operator command processing is complete.

In the example above:

v The information for User Agent 1 shows both Package and Procedure Package

information, which means that the agent is currently running a stored

procedure. In this example, Agent 1 is running the package

SQLDBA.MAINPGM, and SQLDBA.MAINPGM has called the stored procedure

executing the package SQLDBA.MYPROC. The agent is in communication wait,

which in this case means that the stored procedure is processing, and the

database manager is waiting for the procedure to end or to pass another SQL

command.

v The information for User Agent 2 shows Package information, but no Procedure

Package information. This means that the agent is running the main program,

which in this example is SQLDBA.MAINPGM2. The agent is waiting for a

stored procedure server in group GROUP1, which means that it has issued an

SQL CALL statement, but no stored procedure servers are available in the group

in which the procedure can run (GROUP1).

Performance Tuning Handbook

v The information for User Agent 3 shows both Package and Procedure Package

information, which means that the agent is currently running a stored

procedure. In this example, Agent 3 is running the package

SQLDBA.MAINPGM3, and SQLDBA.MAINPGM3 has called the stored

procedure executing the package SQLDBA.MYPROC3. The agent is processing,

which means that the database manager is currently executing an SQL command

that was passed to it by the stored procedure.

VSE SHOW CONNECT

If a CICS user requests that the operator terminate a CICS transaction containing

DB2 Server for VSE statements, the operator should first force the associated DB2

Server for VSE agent by using the FORCE command before terminating the

transaction. Prior to SQLDS Version 3 Release 5, the operator may not be able to

determine which agent to force, because multiple agents may use the same DB2

Server for VSE user ID. Furthermore, if the agent is not forced before the

transaction is terminated, the resource adapter may encounter an error and shut

itself down. This would cause the links to DB2 Server for VSE through that

particular resource adapter to be lost. In SQLDS Version 3 Release 4, only the

CICS task number representing the AXE transaction is displayed, and only for

remote (DRDA) users.

Operators can now identify which agent should be forced by displaying the CICS

task number, the CICS terminal ID, and the RMID for all local CICS users as part

of the output for the SHOW CONNECT command. This information will be

displayed for both VSE and VM (Guest Sharing) users.

The additional information on the SHOW CONNECT command will enable the

operator to identify which agent should be forced. This is accomplished by the

following steps:

1. The user tells the operator to cancel the task associated with a particular

terminal ID. Alternatively, they may ask that a specific task be terminated.

2. The operator then issues the SHOW CONNECT command to determine which

agent is associated with either the task ID or the terminal ID that was specified

by the user.

3. The operator can then force the correct agent and then terminate the CICS

transaction.

The CICS task number, CICS terminal id, and RMID will be displayed for all local

(VSE or VM Guest Sharing) CICS transactions whenever the agent is in work. The

CICS terminal ID may contain a value of 'N/A' indicating that the terminal ID is

not available, such as when a user issues queries through ISQL. The CICS task

number, the CICS terminal id, and the RMID will not be displayed for batch users,

or for agents whose work status is NIW (not in work). For remote (DRDA) users,

only the CICS task number representing the AXE transaction will be displayed; the

CICS terminal id and the RMID will not be displayed.

When a CICS transaction is using a release of the Resource Adapter prior to

Version 3 Release 5, the CICS terminal id and the RMID are not available to the

database server and 'N/A' will be displayed.

Included below are sample outputs for each of the cases where additional

information may be displayed.

Chapter 2. Measuring Performance

SHOW CONNECT for CICS Transaction (Version 3 Release

5):

F4 004 User Agent: 1 User-ID: JOAO SQL-ID: JOAO

F4 004 is R/O APPL 12BCF

F4 004 Agent is processing and is in communication wait.

F4 004 State started: 1999-09-02 15:21:22

F4 004 Conversation started: 1999-09-02 15:21:22

F4 004 Task no.: 147 RMID: 32 Term. id: 077D

Figure 2. CICS Transaction (Version 3 Release 5 Requester)

SHOW CONNECT for ISQL Query:

F4 004 User Agent: 1 User-ID: JOAO SQL-ID: JOAO

F4 004 is R/O APPL 12BCF

F4 004 Agent is processing and is in communication wait.

F4 004 State started: 1999-09-02 15:21:22

F4 004 Conversation started: 1999-09-02 15:21:22

F4 004 Task no.: 147 RMID: 32 Term. id: N/A

Figure 3. ISQL Query

SHOW CONNECT for Agent Not in Work:

F4 004 User Agent: 2 User-ID: DBDCCICS SQL-ID: DBDCCICS

F4 004 is NIW SUBS

F4 004 Agent is not processing and is in communication wait.

F4 004 State started: 1999-09-03 15:19:57

F4 004 Conversation started: 1999-09-03 15:19:57

Figure 4. Agent Not in Work

SHOW CONNECT for Batch User:

F4 004 User Agent: 2 User-ID: SQLDBA SQL-ID: SQLDBA

F4 004 is R/W APPL 18D9

F4 004 Agent is not processing and is in communication wait.

F4 004 State started: 1999-09-08 15:34:51

F4 004 Conversation started: 1999-09-08 15:34:41

Figure 5. Batch User

Performance Tuning Handbook

SHOW CONNECT for DRDA User:

F4 004 User Agent: 2 User-ID: EDUARDA SQL-ID: EDUARDA

F4 004 is R/W APPL 12FC4

F4 004 Agent is processing and is in communication wait.

F4 004 State started: 1999-09-02 15:23:17

F4 004 Conversation started: 1999-09-02 15:23:15

F4 004 CPU time: 00:00:01

F4 004 LUWID: CAIBMOML.OECGW001.A6773D6F8611.0001

F4 004 EXTNAM: EDUARDA.1

F4 004 Requester: SQLDS/VM V3.5.0 at TOIVMLB6

F4 004 Rmtuser ID: 2

F4 004 LU name: OMPGW001

F4 004 Task no.: 0000134

Figure 6. DRDA User Accessing VSE Database

SHOW CONNECT for Guest Sharing:

User Agent: 1 User-ID: VSEMCH10 SQL-ID: SQLDBA

is R/O SUBS 1796

Agent is not processing and is in communication wait.

State started: 1999-09-08 10:42:55

Conversation started: 1999-09-08 10:42:43

Task no.: 371 RMID: 12 Term. id: N/A

Figure 7. VSE Guest Sharing User to VM Database Using ISQL

SHOW CONNECT for VM User:

User Agent: 1 User-ID: SQLUSRMR SQL-ID: SQLUSRMR

is R/O APPL 178F

Agent is not processing and is in communication wait.

State started: 1999-09-08 10:41:11

Conversation started: 1999-09-08 10:41:04

Figure 8. VM Requester Accessing VM Database

SHOW CONNECT for CICS Transaction (Version 3 Release

4):

F4 004 User Agent: 1 User-ID: JOAO SQL-ID: JOAO

F4 004 is R/O APPL 12BCF

F4 004 Agent is processing and is in communication wait.

F4 004 State started: 1999-09-02 15:21:22

F4 004 Conversation started: 1999-09-02 15:21:22

F4 004 Task no.: 147 RMID: N/A Term. id: N/A

Figure 9. CICS Transaction (Version 3 Release 4 Requester)

Locking

Locking Contention: The SHOW LOCK command can help you understand and

resolve immediate locking contention problems. (For information on this area, refer

to “Locking Contention” on page 94.) Consider the following situation:

1. The default lock level (PAGE) is in effect.

2. PETER, BRIAN, and LAURA all select the salary of MICHAEL THOMPSON in

the EMPLOYEE table through ISQL. They are all granted a SHARE (S) lock on

Chapter 2. Measuring Performance

the pages that contain Michael’s salary and the page that contains index keys

used to retrieve it. They are also granted an INTENT SHARE (IS) lock on the

table and dbspace that contain Michael’s salary.

3. Instead of clearing her query, LAURA leaves its results on her screen. This

places her in a communication wait.

4. PETER tries to add $1000 to THOMPSON’s salary, but is placed in a lock wait.

(While he is granted an UPDATE (U) lock on the data page, the EXCLUSIVE

(X) lock he needs on that page is incompatible with BRIAN and LAURA’s

SHARE (S) lock.)

5. BRIAN tries to increase THOMPSON’s salary by 5%, but is also placed in a

lock wait. (The update lock he needs is incompatible with the update lock that

PETER already holds.)

A SHOW LOCK ACTIVE command reveals that LAURA is in a communication

wait, and BRAIN and PETER are in a lock wait. (You can also determine this with

a SHOW ACTIVE command.)

show lock active

WAIT TOTAL LONG WANTLOCK WANTLOCK

AGENT USER

STATE LOCKS LOCKS TYPE

DBSPACE

CHECKPT NIW

BRIAN

LOCK

PAGE

PETER

LOCK

PAGE

LAURA

COMM

ARI0065I Operator command processing is complete.

A SHOW LOCK MATRIX reveals that BRIAN is waiting for PETER, and PETER is

waiting for LAURA. (The number seven in the lock matrix indicates that the

contention is in dbspace seven.)

show lock matrix

Lock Request Block (LRB) and Lock Status:

NLRBS IN USE FREE NLRBU MAX USED BY LUW

------- ------- ------- ------- ---------------

2520

213

2307

1000

386

*** THE LOCKWAIT TABLE ***

ENTRY = DBSPACE NUMBER ON WHICH THERE IS LOCK CONTENTION

The presence of an entry shows

the agent requesting the lock and

the agent contending for or holding the lock.

AGENT

AGENT CONTENDING FOR OR HOLDING THE LOCK

REQUESTING

LOCK

BRIAN

PETER

LAURA

BRIAN

........ 7

PETER

........ ........ 7

LAURA

ARI0065I Operator command processing is complete.

A SHOW LOCK GRAPH of BRIAN clearly shows the chain of lock contention

that has occurred. Until LAURA clears her screen and returns her SHARE (S) lock,

neither PETER nor BRIAN can leave their lock wait.

Performance Tuning Handbook

show lock graph brian

LOCK

WAIT LOCK DBSP LOCK

REQ

REQUESTER

HOLDER

STAT TYPE NUMBR QUALIFIER

STATE

MODE DUR

BRIAN

PETER

LOCK PAGE 7

G WAIT U

LONG

PETER

LAURA

COMM PAGE 7

C WAIT X

LONG

ARI0065I Operator command processing is complete.

The SHOW LOCK USER command displays both how many locks of each type

are held plus the number they are waiting for.

show lock user

DBSPACE LOCK

NUMBER

AGENT USER

NUMBER TYPE IN SIX IS IX S U X Z

WAITERS

BRIAN

DBSP

BRIAN

IPAG

BRIAN

TABL

LAURA

DBSP

LAURA

IPAG

LAURA

PAGE

LAURA

TABL

PETER

DBSP

PETER

IPAG

PETER

PAGE

PETER

TABL

ARI0065I Operator command processing is complete.

In this case, PETER has an UPDATE (U) lock on the page that holds

THOMPSON’s salary, but it cannot be promoted to an EXCLUSIVE (X) lock

because it is incompatible with LAURA’s SHARE (S) lock. BRIAN has a SHARE

lock but cannot promote it to an UPDATE lock because it is incompatible with

PETER’s UPDATE lock.

Lock Escalation: The SHOW LOCK MATRIX command can help you understand

and resolve lock escalation problems. (For information on this area, refer to “Lock

Escalation” on page 99.) Consider the following situation:

show lock matrix

Lock Request Block (LRB) and Lock Status:

NLRBS IN USE FREE NLRBU MAX USED BY LUW

------- ------- ------- ------- ---------------

2520

213

2307

1000

386

The maximum number of lock request blocks that can be held by a single agent

(NLRBU) is set to 1000. The number that can be held by all the agents (NLRBS) is

set to 2520. 213 blocks are currently in use and 2307 are free. The maximum

number of blocks held since the last lock escalation is 386.

Do not rely on this command alone. The MAX USED BY LUW may appear to be

significantly lower than NLRBS, but remember MAX USED is reset to zero after every

escalation. If the database manager is constantly escalating locks, you may be

unlucky enough to only see the value immediately following an escalation. Make

Chapter 2. Measuring Performance

sure that you consult the ESCALATE and LOCKLMT counters as well. Refer to

“Lock Request Block Performance” on page 24.

Database catalog

Information about the database is maintained in a set of tables called the catalog

which are created during database generation. They describe tables, columns,

indexes, keys, packages, authorities and other objects in the database. This section

describes how to select information from various tables that contain performance

information.

The catalog also holds statistical information on the data stored in the database.

The database manager uses it to select an access path for each SQL request it

processes. Refer to “Keeping Database Statistics Current” on page 137 for a

description of each table that contains this information and what the values in each

column means. Also refer to “Using Catalog Statistics” on page 139 for a

discussion of how to model a large production database with a small test database

by altering the values in the catalog.

SYSTEM.SYSCATALOG

The following SQL statement retrieves performance information from the

SYSCATALOG table about all the tables in the sample dbspace:

SELECT tname, avgrowlen, rowcount, npages, noverflow

FROM system.syscatalog

WHERE dbspacename=’sample’

AND creator=’sqldba’

TNAME

AVGROWLEN

ROWCOUNT

NPAGES

NOVERFLOW

------------------

---------

------------

ACTIVITY

DEPARTMENT

EMP_ACT

EMPLOYEE

INVENTORY

OPERATIONS

PROJ_ACT

PROJECT

PROJECTS

QUOTATIONS

SUPPLIERS

AVGROWLEN

The average length of all the rows in the table, measured in bytes, refer to

“Free Space in Data Pages” on page 61.

ROWCOUNT and NOVERFLOW

ROWCOUNT is the total number of rows in the table. NOVERFLOW is the

number of rows in the tables that have overflowed from their original page

in storage to another page. This is caused by variable length rows

expanding because of updates. As a rule of thumb, if NOVERFLOW is

greater than 5% of ROWCOUNT, it is probably time to reorganize the

table, refer to “Reorganizing a Single Table” on page 71. However,

remember that there are no absolute rules in performance tuning. You have

to balance the cost of reorganization against the performance impact of the

overflow rows.

Performance Tuning Handbook

If you decide to reorganize the table because of this, you may also want to

use the ALTER DBSPACE command to increase the PCTFREE value of the

dbspace that contains the table, refer to “Free Space in Data Pages” on

page 61.

NPAGES

An estimate of the number of pages on which rows of this table appear.

SYSTEM.SYSCOLUMNS

The following SQL statement retrieves performance information from the

SYSCOLUMNS table about all columns in the employee table:

SELECT cname, coltype, length, nulls, ccsid

FROM system.syscolumns

WHERE tname = ’employee’

AND creator=’sqldba’

CNAME

COLTYPE LENGTH NULLS

CCSID

------------------

--------

-------

-----

------------

BIRTHDATE

DATE

BONUS

DECIMAL

( 9, 2) Y

COMM

DECIMAL

( 9, 2) Y

EDLEVEL

SMALLINT

EMPNO

CHAR

500

FIRSTNME

VARCHAR

500

HIREDATE

DATE

JOB

CHAR

500

LASTNAME

VARCHAR

500

MIDINIT

CHAR

500

PHONENO

CHAR

500

SALARY

DECIMAL

( 9, 2) Y

SEX

CHAR

500

WORKDEPT

CHAR

500

COLTYPE and LENGTH

The datatype and length of the column. It is important that the predicates

in a WHERE clause have the same data type and length, refer to “Column

Attributes” on page 123.

NULLS

Whether a column can contain NULL values affects how it is accessed.

(Refer to page 119 and page 2 on page 128.)

CCSID

The coded character set identifier (CCSID) of the column. CCSIDs can

affect whether a predicate becomes sargable or residual, refer to “Impact of

CCSIDs on Sargability” on page 131.

SYSTEM.SYSDBSPACES

The following SQL statement retrieves performance information from the

SYSDBSPACES table about the subscriptions dbspace:

Chapter 2. Measuring Performance

SELECT dbspaceno, npages, nactive, pctindx, freepct, lockmode, pool

FROM system.sysdbspaces

WHERE dbspacename=’subscriptions’

AND creator=’sqldba’

DBSPACENO

NPAGES

NACTIVE PCTINDX FREEPCT LOCKMODE

POOL

---------

------------

-------

--------

------

480819

18427

* End of Result *** 1 Rows Displayed ***Cost Estimate is 1*********************

NPAGES

The number of logical 4KB (kilobyte) pages available in this dbspace. In

this case there are 1.878GB (gigabytes) of storage in this dbspace, refer to

“Dbspace Full” on page 65.

NACTIVE

The number of active pages in the dbspace. It represents the number of

4KB data pages that must be read during a dbspace scan. In this case, the

database manager must scan almost 72MB of storage to complete a

dbspace scan. For more information refer to “Dbspace Scans” on page 118.

PCTINDX

The percentage of pages to be reserved for index pages, refer to

“Proportion of Index to Data and Header Pages” on page 61.

FREEPCT

The current percentage of space on each page that is kept free when data is

inserted in the dbspace, refer to “Free Space in Data Pages” on page 61.

LOCKMODE

Indicates whether row (T), page (P), or dbspace (S) level locking is being

used for this dbspace, refer to “Minimum Lock Level” on page 96.

POOL

The number of the storage pool where pages from this dbspace are stored.

You can use the SHOW POOL operator command to display information

about this pool, refer to page 26.

SYSTEM.SYSINDEXES

The following SQL statement retrieves performance information from the

SYSINDEXES table about all the indexes in the sample dbspace:

Performance Tuning Handbook

SELECT iname, cluster, clusterratio, lockmode, ipctfree, release

FROM system.sysindexes, system.syscatalog

WHERE system.sysindexes.tname = system.syscatalog.tname

AND dbspacename=’sample’

AND creator=’sqldba’

INAME

CLUSTER CLUSTERRATIO LOCKMODE IPCTFREE RELEASE

------------------

-------

------------

--------

-------

PKEYB1PAIBMXWNCV

9375

7.1.0

PKEYB1PAIAXH1U6L

10000

7.1.0

MGRNOI

10000

7.1.0

PROJNOIN

9306

7.1.0

EMPNOIN

10000

7.1.0

PKEYB1PAIA5RUD1W

10000

7.1.0

WORKDEPTI

8667

7.1.0

INV1

10000

7.1.0

OPE1

9231

7.1.0

PKEYB1PAIBSXHRBH

10000

7.1.0

DEPTNOI

10000

7.1.0

PKEYB1PAIBEPN7Y2

10000

7.1.0

RESPEMPI

10000

7.1.0

PRO1

10000

7.1.0

QUO1

10000

7.1.0

SUP1

10000

7.1.0

You can also retrieve the same information for the indexes of a single table. In this

case the employee table:

SELECT iname, cluster, clusterratio, lockmode, ipctfree, release

FROM system.sysindexes

WHERE tname=’employee’

AND creator=’sqldba’

INAME

CLUSTER CLUSTERRATIO LOCKMODE IPCTFREE RELEASE

------------------

-------

------------

--------

-------

PKEYB1PAIA5RUD1W

10000

7.1.0

WORKDEPTI

8667

7.1.0

CLUSTER and CLUSTERRATIO

CLUSTER indicates whether the index is a clustering index, refer to “The

Clustering Index” on page 67. You can also use it to get an idea of whether

an index is clustered, refer to “Clustered Indexes” on page 66.

CLUSTERRATIO is updated when the index’s statistics are updated

(SYSTEM.SYSINDEXES catalog table). It indicates the percentage of time

that the data pages are in a logical sequence in relation to the index. In this

case, when the statistics for WORKDEPTI were last updated, the data

pages it referred to were in a logical sequence 86.67% of the time.

For more information on how to interpret CLUSTER and CLUSTERRATIO,

refer to “Identifying Unclustered Indexes” on page 68.

LOCKMODE

Indicates whether page (P) or row level locking (K) is being used on this

index, refer to “Minimum Lock Level” on page 96.

IPCTFREE

The amount of free space reserved in the index for later insertions and

updates, refer to “Free Space in Index Pages” on page 62.

Chapter 2. Measuring Performance

Chapter 3. Managing Storage and Configuring the Operating

System

Real and Virtual Storage

There are two types of storage: real and virtual.

Real Storage

Composed of main and auxiliary storage. Main storage is the fastest

storage and it is where data and programs must be before the CPU can

directly act upon them. Auxiliary storage comprises expanded storage, and

system paging DASD. Data and programs reside in one of these two areas

when there is no room in main storage.

Virtual Storage

Virtual storage is an addressable space image for the user from which

instructions and data are mapped into real storage locations. The operating

system uses real storage (main and auxiliary storage) to create virtual

machines (in the case of VM) or partitions (in the case of VSE).

Virtual Addressing

In VM, each virtual machine has its own virtual address space, which is where you

load and run programs. VSE supports multiple address spaces that can each

contain several partitions.

Because these address spaces are virtual, the operating system does not dedicate a

piece of main storage to each virtual machine or partition. You do not need to buy

8MB of main storage for each 8MB virtual machine or partition. Instead the

operating system only uses main storage for those parts of virtual storage you

need right now, or are likely to need in the near future.

Pages

These parts of virtual storage are divided into 4KB (4096 byte) blocks called pages.

When a virtual machine or partition needs a page that it has not accessed before,

the operating system retrieves the page from its location on DASD, and loads it

into an empty page in main storage. (Before a page can be used, it must be in main

storage.)

Auxiliary Storage

When the operating system runs out of free pages in main storage, it moves the

least recently used (“oldest”) page to auxiliary storage to create a free space for a

new page.

The VM operating system uses two types of auxiliary storage: system paging

DASD, and optional expanded storage. If your system has expanded storage, a

page will be moved there first. If expanded storage is full, the least recently used

page in expanded storage is moved to system paging DASD by way of main

storage. When a virtual machine needs a page that it has previously used, the

operating system moves it back to main storage from expanded storage or from

system paging DASD, if it is not already in main storage.

Figure 11. Standard VSE Partition Storage

This paging system accomplishes two things. First, it allows each virtual machine

or partition to use much more storage than could be accommodated in main

storage alone. Second, it keeps the most recently used pages in the storage devices

that are the fastest to access. (The most recently used pages are the ones most

likely to be used again in the near future.) Main and expanded storage are much

faster than system paging DASD, and while expanded storage can be as fast as

main storage, it is effectively slower because the operating system still needs to

move the page into main storage before it can use it.

The Hidden Cost of System Paging DASD: Each time the database machine or

partition (or the CICS partition) requires a page that the operating system cannot

find in main storage, a page fault occurs. The entire database machine or partition,

and therefore the entire database manager, must wait until the page is returned

from auxiliary storage. Consider a system that requires an average of 50ms to

return one page. So at 6 faults per second the database manager is idle 300 out of

1000ms, or almost one third of the time.

Note: This is not true for DASD I/O. A database machine does not wait for

*BLOCKIO, nor does a database partition wait for VSAM. The database

Chapter 3. Managing Storage and Configuring the Operating System

manager will dispatch another agent (unless you are running in single user

mode) while it waits for the DASD I/O to complete. Refer to “Database

I/O” on page 85.

This is also not true for Page Faults that occur in Data Space (when VMDSS

is enabled). Only one agent must wait for this kind of Page Faults.

Partition Deactivation (VSE): The TPBAL command (VSE system control

statement) specifies the number of partitions that are eligible for deactivation.

Whenever the TPIN macro (refer to the IBM VSE/ESA System Macros Reference

manual) is executed, the number of partitions specified by the TPBAL command

will be deactivated starting with the lowest priority partition and proceeding to the

highest eligible partition (excluding the partition which executed the TPIN macro).

All deactivated partitions are suspended (kept idle) until the TPOUT macro is

executed. This can become a severe problem if, for example, the deactivated

partition is an application server or a requester that is currently holding database

locks. Whenever possible, ensure that the database partition, the CICS partition, or

any DB2 Server for VSE batch partitions are NOT eligible for deactivation.

Storage Queues

A storage queue is a control structure that the database manager uses to share its

virtual space between processes. Queues are created at startup for:

v Each real agent

v The operator

v For checkpoint processing

v Recovery

v Global storage blocks such as accounting records.

The database manager allocates virtual storage to the different processes as they

require it (within limits). Once the storage has been allocated, it cannot be used by

any other process until the owning process releases it.

Real agent queues release all but 8KB of their allocated storage at the end of a

logical unit of work (LUW). Even if the real agent no longer requires the virtual

storage, it may keep it until the end of its current LUW.

Virtual space may be allocated either above or below the 16MB virtual storage line

(refer to “Storage Above 16MB (31 Bit Addressing)” on page 47.) However, because

certain control blocks and program structures must always reside below the 16MB

line, two storage queues are created for each process. One storage queue is for

blocks and structures that must always reside below the line, the other queue is for

blocks and structures that may reside either below or above the line.

When an agent requests virtual storage, the database manager decides whether the

blocks and structures must reside below the 16MB line or whether they can reside

either above or below the line. If they must reside below the line, space is allocated

in the below or B queue. If they may reside above or below the line, space is

allocated in the anywhere or A queue.

When the database manager looks for address space for blocks and structures in

the B queue, it looks below the 16MB line. When the database manager looks for

address space for blocks and structures in the A queue, it first tries to find free

space above the 16MB line. If there is none, it will try to find free space below the

line. If there is no free space below the line for either the A queue or for the B

Performance Tuning Handbook

queue, releasing unused packages from storage. If it can no longer release

packages, you will receive an error message and the database machine or partition

may abend.

Address Space Size

While increasing the size of your virtual database machine or partition may

increase your application server’s capacity, it does not necessarily improve its

performance. This is because the operating system has to supply enough fast real

storage to make the virtual storage appear to be real storage.

However, if sufficient real storage is available, you can take advantage of

additional virtual storage, by increasing:

v The number of concurrent users (NCUSERS). Refer to “Agents” on page 88.

v In VM, the number of pseudo agents (MAXCONN). Refer to “VM

(MAXCONN)” on page 92.

v In VSE, increase the number of connections between the CICS partition and the

database partition (CIRB transaction). Refer to “Tuning Parameters” on page 90.

You can also increase the number of remote DRDA users (RMTUSERS). Refer to

“VSE (RMTUSERS)” on page 92.

v The size of the buffers pools (NPAGBUF, NDIRBUF). Refer to “Database I/O”

on page 85.

v The number of lock request blocks (NLRBU, NLRBS). Refer to “Lock Escalation”

on page 99.

v The size of the package cache (NPACKAGE). Refer to “Package Cache” on page

88.

You need to monitor the real storage and the I/O in your system. If you do not

have enough main (and in VM expanded) storage to support additional virtual

storage, you may dramatically increase the load on your I/O subsystem and on

your processor. In extreme cases this can lead to thrashing. (The processor and I/O

subsystem spend most of their resources moving pages from main to auxiliary

storage and have little or no resources left for practical work.)

Storage Above 16MB (31 Bit Addressing)

In VM, your database machine’s address space can be larger than the old 16MB

limit. In VSE, you can use a database partition larger than the old 16MB limit.

(As mentioned in “Address Space Size,” anytime you increase your virtual storage,

you can realize significant performance improvements by using storage above

16MB, but only if you have the resources to support it.)

Saved Segments (VM Only)

A saved segment is a range of pages of virtual storage you can define to hold data

or reentrant code (programs), which can be shared by multiple virtual machines.

For detailed information, on how to create saved segments and which DB2 Server

for VM components can be loaded into them, refer to the DB2 Server for VM System

Administration manual.

Loading frequently used DB2 Server for VM components in saved segments has

several advantages:

v Because several users can access the same physical storage, real storage use is

minimized.

v Using saved segments decreases the I/O rate and DASD paging space

requirements, thereby improving virtual machine performance.

Chapter 3. Managing Storage and Configuring the Operating System

v Saved segments attached to a virtual machine can reside above its defined

virtual storage. This allows the virtual machine to use its defined storage for

other purposes.

For more information on saved segments, refer to the DB2 Server for VM System

Administration manual.

Virtual Disk Support for VSE/ESA for Internal Dbspaces

Your internal dbspaces can use a virtual disk to improve their performance. Virtual

Disk Support lets you use a data space as a virtual disk. A virtual disk is much

faster than a conventional disk because it uses main storage instead of DASD. The

virtual disk appears to any program or job as just another disk, only faster.

However, virtual disk storage is temporary. Anything in a virtual disk is lost

whenever the VSE operating system is restarted. For this reason, DO NOT use

virtual disks for anything other than internal dbspaces. These dbspaces are only

used as temporary workspace, so it does not matter if their contents are lost. The

storage pool containing the virtual disk must NOT be used for any permanent

dbspaces.

While the use of virtual disks is limited to internal dbspaces, they can improve the

performance of index creation, joins, sorts, and other operations that require

temporary workspace.

Remember, as mentioned in “Address Space Size” on page 47, anytime you

increase your use of virtual storage, you can realize significant performance

improvements, but only if you have the real storage to support it.

Note: It is recommended that database generation be done with real minidisks

only. If you decide to generate a database which uses a virtual disk, ensure

that the virtual disk is used in a pool containing only internal dbspaces.

Using Virtual Disks with Internal Dbspaces: To use a virtual disk with internal

dbspaces, you must:

1. Take an archive of your database, before making any changes to it.

2. Modify the IPL procedure and the background initialization procedure to create

a virtual disk.

3. Define a VSAM user catalog and dbextent on the virtual disk.

4. Add a label for the dbextent in the cataloged procedure and add the dbextent

to a NEW storage pool that will contain only internal dbspaces.

5. Move some of the dbextents from the original pool that contained the internal

dbspaces into the new pool or define additional physical dbextents to be added

to the new pool.

6. Add internal dbspaces to the new storage pool.

7. Backup the VSAM user catalog defined on the virtual disk so that it can be

restored whenever the VSE system is restarted.

8. Modify the application server startup job to restore the VSAM user catalog if

the VSE system has been restarted since the VSAM user catalog and dbextent

were created.

9. Archive your database after making the above changes so that you have an

archive that reflects these changes.

Performance Tuning Handbook

A detailed example of how to complete these steps appears below. For more

information on virtual disks in VSE/ESA, refer to the IBM VSE/ESA Planning

manual and the IBM VSE/ESA Extended Addressability manual.

Note: All of the following steps should be read before executing any of them.

Step 1, Archive Your Database: This will be needed if problems arise during the

setup for using a virtual disk and you need to restore your database to its previous

state without any virtual disk.

Step 2, Modify IPL Procedure: Modify the IPL procedure to do the following:

v Include ADD statements for the virtual disk addresses

v Increase VSIZE and page data set allocation to accommodate the new virtual

disk.

For example, consider a system where a 20MB virtual disk is added to a VSE

system with VSIZE=75MB:

009,$$A$SUPX,VSIZE=95M,VIO=576K,VPOOL=194K,LOG

ADD 900:906,FBAV

DPD VOLID=DOSRES,CYL=209,NCYL=100,TYPE=N,DSF=N

DPD VOLID=DOSRES,CYL=398,NCYL=8,TYPE=N,DSF=N

DPD VOLID=DOSRES,CYL=410,NCYL=29,TYPE=N,DSF=N

DLA NAME=AREA1,VOLID=DOSRES,CYL=60,NCYL=3,DSF=N

SVA PSIZE=640K,SDL=300,GETVIS=768K

This example increases the VSIZE of the VSE operating system from 75MB to

95MB. It reserves virtual addresses 900 through 906 for fixed block architecture

virtual disks, and it sets aside an additional 29 cylinders of 3390 DASD on

DOSRES volume starting at address 410 for system paging DASD. (108 cylinders

were already being used at addresses 209, and 398. Also, there are 180 4KB pages

in every 3390 cylinders. So 29 cylinders is equal to 20MB.)

Step 3, Define and Initialize a Virtual Disk: Create a procedure (to be invoked

as part of the background initialization JCL, for example $0JCL) that will do the

following:

v Define data space size using SYSDEF command

v Initialize virtual disks using // VDISK command.

For example:

Chapter 3. Managing Storage and Configuring the Operating System

* DEFINE THE SIZE OF THE DATASPACE

// SYSDEF DSPACE,DSIZE=20M

* DEFINE AND INITIALIZE EACH VIRTUAL DISK

// VDISK UNIT=900,BLKS=40320,VOLID=QPVDS1,VTOC=008

This example reserves approximately 20MB of virtual storage for virtual disks.

(Remember you do not need 20MB of real storage to support 20MB of virtual

storage.) The virtual disk at address 900 uses approximately 20MB of that storage

(40320 512-byte blocks, with 8 512-byte blocks of that reserved for the VTOC). (The

virtual disk addresses were defined in Step 2.)

Note: Because of the structure of a virtual disk, blocks must be allocated in

multiples of 960 blocks. So instead of 2048 512-byte blocks for 1MB of

storage, you can only allocate 1920 blocks.

Step 4, Define a Backup File: Define a sequential file on a real disk for use later

when backing up the VSAM user catalog (defined on the next step on a virtual

disk). For example:

* DEFINE A SEQUENTIAL FILE FOR VDISK UCAT BACKUP

// EXEC IDCAMS,SIZE=AUTO

DEFINE NONVSAM (NAME(VDISK1.UCAT.BKUP) -

DEVICETYPES(3390) VOLUMES(SYSWK1))

This example creates a backup file called VDISK1.UCAT.BKUP.

Note: The above JCL does not work if it is used on FBA DASD. If only FBA DASD

is used, do one of the following:

v Skip this step

v Use a VSAM ESDS file for the backup file.

Step 5, Define a VSAM User Catalog: Define a VSAM user catalog on the virtual

disk using the DEDICATE option. For example:

* DEFINE A VSAM USER CATALOG

// EXEC IDCAMS,SIZE=AUTO

DEFINE USERCATALOG ( -

NAME (VDS1.USER.CATALOG

) -

DEDICATE -

VOLUME (QPVDS1))

This example creates a VSAM user catalog called VDS1.USER.CATALOG on volume

QPVDS1 and dedicates the entire volume for this VSAM user catalog. (Volume

QPVDS1 was defined in Step 3.)

Performance Tuning Handbook

Step 6, Define a Virtual Disk Dbextent: Define a dbextent (VSAM cluster) on the

virtual disk. For example:

* ADD CLUSTERS FOR THE DATA BASE VIRTUAL DISK

// DLBL VDSUC1,’VDS1.USER.CATALOG’,,VSAM

* DEFINE CLUSTERS

// EXEC IDCAMS,SIZE=AUTO

DEFINE CLUSTER (NAME(SQL34.DDSK8.VDSK.DB) NONINDEXED REUSE -

CNVSZ (4096) BLOCKS(39360) VOL(QPVDS1) -

RECSZ (4089 4089) SHR(2)) CAT(VDS1.USER.CATALOG)

This example defines a dbextent named SQL34.DDSK8.VDSK.DB in the VSAM user

catalog VDS1.USER.CATALOG (The VSAM user catalog was defined in Step 5.)

Step 7, Add a Label for the Virtual Disk Dbextent: Update the cataloged

procedure to include a DLBL statement for the new dbextent. For example:

* CATALOG DATABASE DBEXTENT LABELS

// EXEC LIBR,PARM=’MSHP’

ACCESS S=IJSYSRS.SYSLIB

CATALOG DTLDVDSK.PROC R=Y

***************** SQL/DS DBEXTENT LABELS

**************

// DLBL TSQLUC,’TSQL.USER.CATALOG’,,VSAM

// DLBL VDSUC1,’VDS1.USER.CATALOG’,,VSAM

// DLBL BDISK,’SQL34.BDISK.DTLD.DB’,,VSAM,CAT=TSQLUC

// DLBL LOGDSK1,’SQL34.LOGDSK1.DTLD.DB’,,VSAM,CAT=TSQLUC

// DLBL DDSK1,’SQL34.DDSK1.DTLD.DB’,,VSAM,CAT=TSQLUC

// DLBL DDSK7,’SQL34.DDSK7.DTLD.DB’,,VSAM,CAT=TSQLUC

// DLBL DDSK8,’SQL34.DDSK8.VDSK.DB’,,VSAM,CAT=VDSUC1

This example adds a DLBL statement for DDSK8 that identifies dbextent

SQL34.DDSK8.VDSK.DB. (The dbextent was created in Step 6.) DDSK7 is one of the

dbextents that belonged to the original internal dbspace storage pool.

Step 8, Add Dbextents to a New Storage Pool: Add dbextents to the new storage

pool by following instructions included in the DB2 Server for VSE System

Administration manual. (Refer to “adding and deleting dbextents”.)

To avoid using too much real storage, it is recommended that you include at least

two dbextents in the new pool. The first must be the virtual disk. The second,

should be a physical dbextent that can accommodate the overflow from the virtual

disk. You can use some of the dbextents from the original pool that contained the

internal dbspaces (in this example DDSK7). Make the total size of both dbextents

large enough to accommodate your current internal dbspaces and make the virtual

disk as large as possible without over committing real storage.

Also, ensure that you add the virtual dbextent before you add any physical

dbextents. The database manager searches the dbextents for a free page in the

order that they were added.

Chapter 3. Managing Storage and Configuring the Operating System

Attention: Do not accidentally place the virtual disk in an existing storage pool

that contains anything other than internal dbspaces. You will lose valuable data

and a full database or pool level restore will be required.

The following is an example of the ARISADD member, which specifies how

procedure ARIS250D will add and delete dbextents to and from pools:

POOL 9

ADD 8 9

DELETE 7

ADD 7 9

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