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

linkedited to create a phase, which must be catalogued into a library identified

in the LIBDEF statement of the JCL that starts the stored procedure server.

v Defined to the SYSTEM.SYSROUTINES catalog table by the database

administrator, by issuing the SQL CREATE PROCEDURE statement.

v Started, by issuing the START PROC command

If a stored procedure load module or phase is modified, a STOP PROC command

must be issued, followed by a START PROC command, so that the database

manager will cause the stored procedure server to load the new copy of the stored

procedure load module or phase into memory.

After these steps are complete, the user that created the package associated with

the stored procedure is able to GRANT RUN authority on the package to other

users, allowing them to issue the SQL CALL statement to run the stored procedure.

Dropping or Altering a Stored Procedure

The following describes how to remove or alter a stored procedure:

v Issue the STOP PROC command for that procedure, specifying the REJECT

option.

v Use the SQL DROP PROCEDURE statement to delete the rows for the procedure

from SYSTEM.SYSROUTINES and SYSTEM.SYSPARMS, or the SQL ALTER

PROCEDURE statement to change the definition in SYSTEM.SYSROUTINES.

v Optionally delete the load module or phase

Note: There is a special case where issuing the STOP PROC ACTION REJECT

command will not suffice to allow altering or dropping a stored procedure

definition. If the procedure is still running in a stored procedure server, you will

not be allowed to alter or drop its definition, even if the status is STOP-REJ. The

execution of an SQL ALTER or DROP PROCEDURE command will return with

SQLCODE -15000. You can use the SHOW PSERVER and SHOW PROC operator

commands to monitor the procedure’s progress before you try to alter or drop its

definition.

Setting Up Schema Stored Procedures for CLI/ODBC/JDBC/

OLE DB Client Applications

If you plan to use VSE or VM databases as servers that can be accessed by CLI,

ODBC, JDBC, or OLE DB client applications using DB2 UDB Version 8 or later, you

need to prepare the schema stored procedures to run and load the corresponding

packages into the database. See 281.

Initialization Parameters Affecting Stored Procedure Execution

PTIMEOUT Parameter

This parameter serves two purposes:

1. The number of seconds before DB2 Server for VSE & VM ceases to wait for an

SQL CALL to be assigned to a stored procedure server. If the PTIMEOUT

interval expires, the SQL statement fails, and SQLCODE -913 is returned with

SQLSTATE 40001.

2. The number of seconds before DB2 Server for VSE & VM ceases to wait for the

stored procedure server connection request to be established. If the PTIMEOUT

interval expires, message ARI4168I is displayed and the connection attempt

terminates. DB2 Server for VSE & VM will then try to use the next available

stored procedure server, thus the SQL CALL request will not be terminated.

218

Database Administration

A value of 0 means that no PTIMEOUT is in effect. The default for PTIMEOUT is

180.

PROCMXAB Parameter

Specify the number of times a stored procedure is allowed to terminate abnormally,

after which a STOP PROC ACTION REJECT is performed against the procedure

and all subsequent SQL CALL statements are rejected. Note that a time-out that

occurs while waiting for a stored procedure server to be assigned for an SQL

CALL statement is not included in this count. The default, 0, means that the first

abend of a stored procedure causes SQL CALLs to that procedure to be rejected.

For production systems, you should accept the default.

Summary of Environment Interactions

Figure 59 shows the interactions between the database manager, the stored

procedure server, the stored procedure handler, and the stored procedure itself. The

figure does not show the definition of the stored procedure server or of the stored

procedure itself; it is assumed that this has already been done.

Database Machine/

Stored Procedure Server

Parition

DB2 Server

Stored Procedure Server

Application

Requester

Start PSERVER

ARISPRC

Procedure

Complete START

LE/370

PSERVER

EXEC SQL

CALL procedure

Run Time

processing

CALL

Resource

procedure

Receive CALL and

Adapter

Invoke procedure

Return to requester

Procedure done,

return results to DB2

Server and clean up

Figure 59. Stored Procedure Environment

1. If the AUTOSTART value in the cached information from

SYSTEM.SYSPSERVERS is Y, the database manager starts the stored procedure

server during SQLSTART processing. If the AUTOSTART value is N, then the

operator issues the START PSERVER command to start the stored procedure

server. The START PSERVER command changes the status of the stored

procedure server to STARTING. In VM, it also allocates a pseudoagent, and in

VSE, it allocates a new XPCC block, both to be used for the connection between

the database manager and the stored procedure server. See ″The START

PSERVER Command″ in the DB2 Server for VSE & VM SQL Reference for more

details.

2. The user application at the application requester executes an EXEC SQL CALL

statement.

Chapter 11. Stored Procedures

219

An SQL application can contain one or more SQL CALL statements. The SQL

CALL statement is stored in a package in the DB2 Server for VSE & VM

database using the DB2 Server for VSE & VM preprocessor. When the SQL

CALL statement is received, the database manager consults the cached

information from SYSTEM.SYSROUTINES and SYSTEM.SYSPARMS to:

v Determine the load module or phase associated with the stored procedure.

v Determine the programming language used to implement the stored

procedure.

v Determine the run time options for the procedure.

v Validate the parameter list supplied.

v Perform any necessary data conversion between the parameters provided by

the requester and the arguments required by the stored procedure.

v Determine the stored procedure server to use. See ″Stored Procedure Server

Allocation″ on page 213 for more information on how DB2 Server for VSE &

VM resolves this. If the stored procedure server that is found has a status of

STARTING, the database manager must complete the START PSERVER

processing before sending the SQL CALL statement to the stored procedure

server. In this case, the database manager establishes a connection with the

stored procedure server, invokes the stored procedure server, and sets the

status of the stored procedure server to STARTED. When the stored

procedure handler starts, it initializes the communications and run-time

environments and waits for instructions from the database manager

concerning which procedure to run.

The database manager hooks the agent used by the requester that issued the

SQL CALL to the selected stored procedure server. The database manager must

maintain the CONNECT information for the original requester as well, in order

to return the result of the SQL CALL statement.

The database manager saves its environment in preparation for receiving and

processing requests from the stored procedure. Finally it sends a request to the

stored procedure handler to invoke the stored procedure.

The stored procedure handler (ARISPRC) receives the request, and does the

following:

v Sets up the parameters the stored procedure expects, using the parameters

sent by the database manager.

v Loads the procedure that is to run, if it is not already loaded from a previous

execution.

v Initializes the resource adapter environment, to ensure that no residual data

is inherited from a prior execution.

v Passes control to the stored procedure.

The stored procedure server effectively becomes a local requester and uses the

connection that exists to communicate directly with the database manager. It

uses private flows to execute the stored procedure. The database manager

receives and processes the requests, and sends replies to the resource adapter.

This continues until the stored procedure finishes. Note that the resource

adapter is responsible for ensuring that disallowed statements are detected. For

more details on the disallowed statements see ″The SQL CALL″ in the DB2

Server for VSE & VM SQL Reference.

When the stored procedure terminates, control returns to ARISPRC.

ARISPRC packages any output parameters and sends them to the database

manager. Any cursors that were declared with the WITH RETURN option, and

are left open when the stored procedure terminates, define result sets that can

be fetched by the requester that issued the SQL CALL. Result sets are returned

by the database server in the order the cursors were opened in the stored

220

Database Administration

Appendix A. Estimating Your Dbspace Requirements

This appendix describes procedures and calculations you can follow to determine

the amount of storage to allocate to your dbspaces. You must determine:

1. The required size of each permanent dbspace, described in “Estimating

Dbspace Size”

2. The storage required to hold a working set of the data, described “Estimating

Internal Dbspace Size and DASD Needs for Sort Operations” on page 238.

Estimating Dbspace Size

You need to estimate data storage requirements to establish dbspace sizes. The

required size of a dbspace depends on:

v The total number of tables and indexes to be stored in the dbspace

v The size of tables

v The size of indexes

v The amount of free space

v The allowance made for unused pages.

Using the above estimated values, you can calculate the required size of the

dbspace by determining and adding the number of pages required for:

v The sum of the storage requirements of each table

Refer to “Estimating Storage for a Table” on page 224. For each table the storage

requirement is further described in:

“Estimating the Number of Header Pages” on page 226

–

“Estimating the Number of Data Pages” on page 227

–

“Estimating the Number of Index Pages” on page 235.

v An allowance for unused pages and free space.

Setting allowances and using defaults rather than estimating the number of

header and index pages is discussed in “General Guidelines” on page 223.

The following formula shows how these values are used to calculate the number of

dbspace pages needed for a set of tables:

DBSPACE PAGES = HEADER PAGES + DATA PAGES + INDEX PAGES + ALLOWANCE

General Guidelines

For most dbspaces, it is sufficient to use the default value of 8 for the number of

HEADER PAGES. You should also use the default of 33 percent for PCTINDEX,

rather than estimate the number of index pages needed, unless you anticipate

doing extensive indexing. This default reserves approximately one third of the total

space for indexes. If you assume both defaults, 8 HEADER PAGES and 33 percent

for PCINDEX, the above formula becomes:

DBSPACE PAGES = 8 + 1.50 x (DATA PAGES + ALLOWANCE)

The DBSPACE PAGES number derived must be rounded up to a multiple of 128.

That is,

REQUIRED DBSPACE PAGES = TRUNC [ (DBSPACE PAGES + 127 ) / 128 ] x 128

223

The TRUNC function, for truncate operation, indicated here means to compute the

value between the brackets [ ] and then use only the integer part of that value. For

example, if the value calculated is 27.8, use 27.

You should allow from 50 to 200 percent for ALLOWANCE, depending on the

nature of the tables to be stored. If the number of rows are relatively stable and

you do not anticipate adding columns to tables (or even adding tables), adding an

ALLOWANCE of 50 percent is safe. To allow all forms of growth (inserting rows,

adding columns, and adding tables), you should consider an ALLOWANCE of 200

percent (2 x data pages).

Notes:

1. The ALLOWANCE in the above formulas correspond to reserved unused

pages. As such, they will not use real space in the storage pool. Dbspace

allocations should be substantially greater than what appears to be necessary

by the above algorithm. Because dbspace pages do not occupy storage pool

slots until they are loaded, there is little to be gained in making a table a tight

fit in a dbspace.

2. When a dbspace can no longer contain a table, you can change the parameters

of the dbspace. See “Altering the Design of a Dbspace” on page 73 for more

information.

Estimating Storage for a Table

To estimate the amount of storage required for a table, consider:

v Amount (or average amount) of storage required for a row of the table

v Storage for long-field columns (average for each column)

v Number of rows the table is likely to have.

Note: For tables with variable length rows, data page requirements will depend

upon the placement of the rows of different length on the pages. Some

orderings will require more data pages than others. When estimating

storage for these tables, refer to “Estimating Data Pages for a Table with

Variable Length Rows” on page 232.

There are no guidelines for estimating the number of rows your table will have.

However, for the purpose of determining the dbspace requirements, it is wise to

look ahead to potential growth of the table in the foreseeable future. Consider the

estimated size of the table 2 or 3 years from now, rather than its current size.

The length of a stored row can be estimated using Table 28. To complete the

calculations of Table 28, you must know the type and length of all columns in your

tables. If long-field columns are involved, you should first calculate the average

length of long-field columns using Table 29 on page 226.

Table 28. Form for Calculating the Average Row Length of a Stored Row

COLUMN OVERHEAD

The number of columns supporting nulls

___

The number of VARCHAR(n) columns with n≤254

___

The number of VARGRAPHIC(n) columns with n≤127

___

The number (N) of LONG FIELDS¹

N x 6

___

SUM OF COLUMN OVERHEAD FACTORS

___

224

Database Administration

Table 28. Form for Calculating the Average Row Length of a Stored Row (continued)

COLUMN DATA STORAGE FACTORS²

INTEGER:

___

SMALLINT:

___

DECIMAL: TRUNC [PRECISION/2 + 1]

___

FLOAT:

___

FLOAT:

___

CHAR(n): n

___

GRAPHIC(n): n x 2

___

DATE:

___

TIME:

___

TIMESTAMP:

___

VARCHAR(n) n≤254: average length

___

VARGRAPHIC(n) n≤127: average length x 2⁴

___

LONG FIELDS : calculated separately (See Table 29 on page 226.)

___

SUM OF COLUMN DATA STORAGE FACTORS

___

ROW OVERHEAD FACTOR

AVERAGE LENGTH OF EACH STORED ROW

(AVGROWLEN)

___

Notes:

The following data types are long fields: VARCHAR(n) with n>254, VARGRAPHIC(n) with n>127, LONG

VARCHAR, and LONG VARGRAPHIC.

The factors indicated in the COLUMN DATA STORAGE FACTORS area are to be used for each column of the

type specified. The sum of those factors goes on the line at the right. For example, if a table consists of 4

columns of DECIMAL data, calculate the factor for each DECIMAL column, add up those factors, and enter the

sum on the line opposite DECIMAL.

The value 8 for FLOAT is for double-precision floating point columns (FLOAT(n) where 22≤n≤53, or n is not

specified). The value 4 for FLOAT is for single-precision floating point columns (FLOAT(n) where 1≤n≤21).

Each graphic character occupies 2 bytes of storage. When you determine the average length of a GRAPHIC or

VARGRAPHIC column in characters, multiply that number by 2 to get the number of bytes.

Column Overhead refers to descriptive information stored with an instance of the

column. The overhead depends upon the characteristics of the column, as follows:

v If it is allowed to be NULL, each value has a 1-byte prefix for indication of a

null entry.

v If it is a varying length character string (that is VARCHAR(n) with n≤254), each

value has a 1-byte length indicator.

v If it is a varying length graphic string (VARGRAPHIC(n) with n≤127), each value

has a 1-byte length indicator.

Pointers to the long-field values are stored in a special internal format that

involves 6 bytes of control information in the stored row (2-byte length value and

4-byte tuple identifier (TID)).

Column Data Storage refers to the storage space occupied by the actual column

values. The numbers shown are number of bytes.

v For DECIMAL data, the data is stored in a packed decimal format. Each digit

(precision) occupies half a byte and the sign occupies half a byte. As the data is

stored in whole bytes, you must round up to the next whole byte. If the number

of digits is n, the field occupies TRUNC [(n + 2) / 2] bytes.

v For varying-length data columns, estimate the average length. If there is a wide

variation in the individual lengths, estimate a higher number for the average. If

Appendix A. Estimating Your Dbspace Requirements

225

the rows are long, the DB2 database manager may move to new, empty, pages

sooner in the loading process than otherwise is necessary.

Row Overhead is a fixed overhead for each row in the table. It consists of a 6-byte

row header and a 2-byte offset into the page, for a total of 8 bytes.

Table 29. Formula for Calculating the Average Length of a Long-Field Column

LONG-FIELD VALUE OVERHEAD = (TRUNC [ (average length + 3999) / 4000 ] x 20)

LONG-FIELD VALUE STORAGE = (TRUNC [ (average length + 249) / 250 ] x 250)

AVERAGE LENGTH OF EACH STORED LONG FIELD (AVGCOLLEN) =

LONG-FIELD VALUE OVERHEAD + LONG-FIELD VALUE STORAGE

Note: The above formula should be used to calculate the average length of a stored long field. The calculation

needs to be done for each column of a table that is a long field. The following data types are long fields:

VARCHAR(n) with n>254, VARGRAPHIC(n) with n>127, LONG VARCHAR, and LONG VARGRAPHIC.

Graphic characters occupy 2 bytes of storage. When you determine the average length of a GRAPHIC or

VARGRAPHIC column in characters, multiply that number by 2 to get the number of bytes.

LONG-FIELD Value Overhead

The value of a LONG FIELD is stored separately from the rest of the stored row.

The value is stored as a chain of entries in an internal table. Each entry of the

internal table is composed of 16 columns of 250 bytes each (some potentially null).

Each record has 20 bytes of overhead (a 2-byte offset, a 6-byte row header, and a

12-byte unary link pointer chain). Thus, the overhead for a LONG-FIELD value

depends on the actual length of the data and includes 20 bytes for each 4000-byte

(16 columns of 250 bytes) entry required to store the value.

Long-Field Value Storage

Long-field values are stored in increments of 250 bytes. Each increment is one

fixed-length 250-byte column value in the internal table. For example a 10-byte

long-field value occupies 250 bytes of storage, plus the long-field-value overhead

of 20 bytes. A 248-byte long-field value occupies 250 bytes, a 260-byte long-field

value occupies 500 bytes storage, and so on.

Estimating the Number of Header Pages

The number of header pages for a dbspace can be established on the SQL

ACQUIRE DBSPACE statement. In general, you should use the default value of 8

for this option.

A more precise estimate of the number of header pages follows. It is more complex

than the general guidelines above, but will assist you in your calculations if you

require a better estimation.

The header pages contain information of the objects defined in a dbspace. Each

object defined in the dbspace, such as a table or an index, is recorded in the header

pages via a control row. For more information on the types of objects that can be

defined in a dbspace and the types of control rows that are inserted in the header

pages, see the DB2 Server for VSE & VM Diagnosis Guide and Reference manual.

To estimate the number of header pages required:

1. Calculate the number of bytes required by the objects defined in the dbspace as

follows:

v Dbspace control information occupies 24 bytes.

226

Database Administration

v For each table created in the dbspace, add 32 + 2c bytes where c equals the

number of columns in the table.

v For each index created on a table in the dbspace, add 20 + 2d bytes where d

equals the number of indexed columns.

v For each table in the dbspace containing one or more long-field columns

(LONG VARCHAR, LONG VARGRAPHIC, VARCHAR(n) where n > 254, or

VARGRAPHIC(n) where n > 127), add 84 bytes.

2. Divide the total number of bytes required by 4080.

3. Round the result to the next highest integer.

4. The result is the number of header pages required for the dbspace.

For example, assume you are planning to acquire a dbspace that will contain 3

tables. Table A has 10 columns, 2 indexes each defined on a single column, and no

long-field columns. Table B has 14 columns; 1 index containing 3 columns, and no

long-field columns. Table C contains 3 columns, 1 index containing 1 column, and

2 long-field columns. The estimated number of header pages for this dbspace is as

follows:

DBSPACE:

Table A:

32 + (2 x 10)

← table

20 + (2 x 1)

← index 1

20 + (2 x 1)

← index 2

Table B:

32 + (2 x 14)

← table

20 + (2 x 3)

← index

Table C:

32 + (2 x 3)

← table

20 + (2 x 1)

← index

← long-field columns

=============

Total:

350 bytes

Divide by 4080

1 header page required.

Estimating the Number of Data Pages

The number of data pages required to store a table depends on whether the rows

in the table are of fixed or variable length. The next section describes a method for

calculating the pages required for storing tables with fixed length rows. For tables

with variable length rows (rows with VARCHAR or VARGRAPHIC data), refer to

“Estimating Data Pages for a Table with Variable Length Rows” on page 232.

Note: Long-field columns do not produce variable length rows because the

long-field values are stored separately.

Pages Required for Storing Tables with Fixed Length Rows

The number of data pages required to hold the tables can be estimated after

determining the average row lengths (AVGROWLEN) for each table and the

effective page size (EPS) based on PCTFREE setting at the time the pages are to be

loaded.

The number of data pages required is estimated as follows:

1. Determine the average row length (AVGROWLEN) as in Table 28 on page 224.

2. Determine the free space requirement (PCTFREE). Use a whole number for

PCTFREE. That is, if the percent free is 10, use 10 for PCTFREE, not 0.10.

3. Calculate:

Appendix A. Estimating Your Dbspace Requirements

227

40 x PCTFREE + AVGROWLEN

4. Use the number calculated in step 3 to find the corresponding EPS in the

following table:

Table 30. Effective Page Size Based on Free Space Requirement

40 x PCTFREE + AVGROWLEN

EPS (Effective Page Size)

8-17

4065 + AVGROWLEN

18-32

4050 + AVGROWLEN

33-52

4030 + AVGROWLEN

53-102

3980 + AVGROWLEN

103-252

3830 + AVGROWLEN

253-502

3580 + AVGROWLEN

503-1002

3080 + AVGROWLEN

1003-2002

2080 + AVGROWLEN

2003-4020

62 + AVGROWLEN

4021-4080

2 + AVGROWLEN

4081-

See note below.

Note: For the case where (40 x PCTFREE + AVGROWLEN) >= 4081:

If AVGROWLEN <= 4080, number of rows per page = 1 and EPS = AVGROWLEN.

If AVGROWLEN > 4080, row size exceeds DB2 limits. Reduce your row size and

recalculate.

Calculate the number of rows per page:

Rows per Page = MINIMUM (256, TRUNC [ EPS/AVGROWLEN ] )

Calculate the number of data pages required as follows:

If the average long-field length is <= 4020, then:

REQUIRED

Number of Rows Number of Rows x Number of Long Fields

DATA

-------------- + --------------------------------------

PAGES

Rows per Page

4020 / Average Long-Field Length

Note: When evaluating the expression, truncate the denominator 4020/average

long-field length to the nearest integer and round up the results of both

division expressions to the nearest integer before adding them.

If the average long-field length is >4020:

REQUIRED

Number of Rows

DATA

-------------- + Number of Rows x Number of Long Fields

PAGES

Rows per Page

Average Long-Field Length

x -------------------------

4020

Note: Round the results of both division terms up to the nearest integer before

evaluating the expression.

If you are loading tables separately, calculate the number of pages required for

each table separately. If you are loading the tables in an interleaved fashion, use

the longest AVGROWLEN of all the tables in determining the Effective Page Size.

Notes:

1. Storage for long fields (LONG VARCHAR, LONG VARGRAPHIC,

VARCHAR(n) with n>254, VARGRAPHIC(n) with n>127) columns must be

228

Database Administration

calculated apart from the rest of the row. AVGROWLEN will include six bytes

for each long-field value. However, storage for the actual long field will be

calculated separately.

2. If you have already established a database and are defining a new dbspace,

you can get an estimate of the data pages required by modeling the dbspace.

That is, create the tables in a test dbspace and load a sample of the data. Then

you can issue queries against SYSTEM.SYSDBSPACES and

SYSTEM.SYSCATALOG to find out how many pages were required for the data

sample. The data for the real tables will be proportionately higher. When

modeling data, avoid using nulls in the sample. Nulls tend to produce low

results.

Examples of Estimating the Number of Data Pages

Example 1: The example work sheet shown in Table 31

is for a table that has just

one CHAR(100) column supporting nulls.

Table 31. Example 1 — Calculating the Average Row Length

COLUMN OVERHEAD

The number of columns supporting nulls

The number of VARCHAR(n) columns with n≤254

The number of VARGRAPHIC(n) columns with n≤127

The number (N) of Long Fields

v N x 6

SUM OF COLUMN OVERHEAD FACTORS

COLUMN DATA STORAGE FACTORS

v INTEGER:

v SMALLINT:

v DECIMAL: TRUNC [PRECISION/2 + 1]

v FLOAT:

(for double-precision)

v FLOAT:

(for single-precision)

v CHAR(n):

100

v GRAPHIC(n): n x 2

v DATE:

v TIME:

v TIMESTAMP:

v VARCHAR(n): average length

v VARGRAPHIC(n): average length x 2

v Long Fields:

calculated separately (See

Table 29.)

SUM OF COLUMN DATA STORAGE FACTORS

100

ROW OVERHEAD FACTOR

AVERAGE LENGTH OF EACH STORED ROW

109

The number of DATA PAGES required to load

25000

rows into this table in a

dbspace defined to have 10 % free space is:

1. Determine the Average Row Length.

AVGROWLEN = 109

2. Determine the Free Space Requirement.

PCTFREE = 10

3. Calculate 40 x PCTFREE + AVGROWLEN.

40 x 10 + 109 = 509

Appendix A. Estimating Your Dbspace Requirements

229

4. From Table 30 on page 228, determine the Effective Page Size (EPS), using the

number calculated in step 3 to find the corresponding EPS.

503 - 1002

3080 + AVGROWLEN

EPS = 3080 + 109 = 3189

5. Calculate the number of rows per page.

Rows per Page = MINIMUM (256, TRUNC [ 3189/109 ] ) = 29

6. The number of data pages required is:

Number of Rows Number of Rows x Number of Long Fields

-------------- + --------------------------------------

= 863

Rows per Page

4020 / Average Long-Field Length

Example 2: The example work sheet shown in Table 32 is for a table that has:

2 DECIMAL(6,0) columns supporting nulls (4 bytes each)

1 DECIMAL(9,0) column defined as NOT NULL (5 bytes)

1 INTEGER column defined as NOT NULL (4 bytes)

1 SMALLINT column supporting nulls (2 bytes)

1 CHAR(3) column supporting nulls (3 bytes)

1 CHAR(4) column supporting nulls (4 bytes)

1 GRAPHIC(10) column defined as NOT NULL (20 bytes)

1 DATE column supporting nulls (4 bytes)

1 TIME column defined as NOT NULL (3 bytes)

2 VARCHAR(10) columns supporting nulls (average 8 bytes each)

1 VARCHAR(15) column supporting nulls (average 12 bytes)

1 VARCHAR(250) column supporting nulls (average 32 bytes)

1 VARGRAPHIC(15) column supporting nulls (average

characters or

bytes)

Table 32. Example

— Calculating the Average Row Length of a Stored Row

COLUMN OVERHEAD

The number of columns supporting nulls

The number of VARCHAR(n) columns with n≤254

The number of VARGRAPHIC(n) columns with n≤127

The number (N) of Long Fields

v N x 6

SUM OF COLUMN OVERHEAD FACTORS

COLUMN DATA STORAGE FACTORS

v INTEGER:

v SMALLINT:

v DECIMAL: TRUNC [PRECISION/2 + 1]

v FLOAT:

(for double-precision)

v FLOAT:

(for single-precision)

v CHAR(n): n

v GRAPHIC(n): n x 2

v DATE:

v TIME:

v TIMESTAMP:

v VARCHAR(n): average length

v VARGRAPHIC(n): average length x 2

v Long Fields:

calculated separately

SUM OF COLUMN DATA STORAGE FACTORS

137

ROW OVERHEAD FACTOR

AVERAGE LENGTH OF EACH STORED ROW

161

230

Database Administration

The number of DATA PAGES required to load 600 rows into this table in a dbspace

defined to have 15 percent free space is:

1. Determine the Average Row Length.

AVGROWLEN = 161

2. Determine the Free Space Requirement.

PCTFREE = 15

3. Calculate 40 x PCTFREE + AVGROWLEN.

40 x 15 + 161 = 761

4. From Table 30 on page 228, determine the Effective Page Size (EPS), using the

number calculated in step 3 to find the corresponding EPS.

503 - 1002

3080 + AVGROWLEN

EPS = 3080 + 161 = 3241

5. Calculate the number of rows per page.

Rows per Page = MINIMUM (256, TRUNC [ 3241/161 ] ) = 20

6. The number of data pages required is:

Number of Rows Number of Rows x Number of Long Fields

-------------- + --------------------------------------

= 30

Rows per Page

4020 / Average Long-Field Length

Usually, you store a table this small in a dbspace with other tables. If a dbspace

has more than one table, the total number of DATA PAGES required for the

dbspace is the sum of the data page requirements of all the tables in the dbspace.

Example 3: The example work sheets shown in Table 33 and Table 34 on page 232

are for a table that has:

2 DECIMAL(6,0) columns supporting nulls (4 bytes each)

1 DECIMAL(9,0) column defined as NOT NULL (5 bytes)

1 INTEGER column defined as NOT NULL (4 bytes)

1 SMALLINT column supporting nulls (2 bytes)

1 CHAR(3) column supporting nulls (3 bytes)

1 CHAR(4) column supporting nulls (4 bytes)

1 GRAPHIC(10) column defined as NOT NULL (20 bytes)

2 DATE columns supporting nulls (4 bytes each)

1 TIMESTAMP column defined as NOT NULL (10 bytes)

2 VARCHAR(10) columns supporting nulls (average 8 bytes each)

1 VARCHAR(15) column supporting nulls (average 12 bytes)

1 VARGRAPHIC(15) column supporting nulls (average 12 characters or 24 bytes)

1 LONG VARCHAR column supporting nulls (average 32 bytes)

Table

33. Example

— Calculating the Average Row Length of a Stored Row

COLUMN OVERHEAD

The number of columns supporting nulls

The number of VARCHAR(n) columns with n≤254

The number of VARGRAPHIC(n) columns with n≤127

The number (N) of Long Fields

v N x 6

SUM OF COLUMN OVERHEAD FACTORS

Appendix A. Estimating Your Dbspace Requirements

231

Table 33. Example 3 — Calculating the Average Row Length of a Stored Row (continued)

COLUMN DATA STORAGE FACTORS

v INTEGER:

v SMALLINT:

v DECIMAL: TRUNC [PRECISION/2 + 1]

v FLOAT:

(for double-precision)

v FLOAT:

(for single-precision)

v CHAR(n):

v GRAPHIC(n): n x 2

v DATE:

v TIME:

v TIMESTAMP:

v VARCHAR(n): average length

v VARGRAPHIC(n): average length x 2

v Long Fields:

calculated separately (See

Table 34.)

116

SUM OF COLUMN DATA STORAGE FACTORS

ROW OVERHEAD FACTOR

AVERAGE LENGTH OF EACH STORED ROW

146

Table 34. Example 3 — Calculating the Average LONG VARCHAR Stored Length

LONG VARCHAR VALUE OVERHEAD

The number(N) of LONG VARCHAR columns

• N x (TRUNC [ (average length + 3999) / 4000] x 20)

LONG VARCHAR VALUE STORAGE

TRUNC

[ (average length + 249) / 250 ] x 250

250

AVERAGE

LENGTH OF

EACH STORED LONG VARCHAR

270

The number of DATA PAGES required to load 25000

rows into this table in a

dbspace defined to have 10 percent free space is:

1. Determine the Average Row Length.

AVGROWLEN = 146

2. Determine the Free Space Requirement.

PCTFREE = 10

3. Calculate 40 x PCTFREE + AVGROWLEN.

40 x 10 + 146 = 546

4. From Table 30 on page 228, determine the Effective Page Size (EPS), using the

number calculated in step 3 to find the corresponding EPS.

503 - 1002

3080 + AVGROWLEN

EPS = 3080 + 146 = 3226

5. Calculate the number of rows per page.

Rows per Page = MINIMUM (256, TRUNC [ 3226/146 ] ) = 22

6. The number of data pages required is:

Number of Rows Number of Rows x Number of Long Fields

-------------- + --------------------------------------

= 2923

Rows per Page

4020 / Average Long-Field Length

Estimating Data Pages for a Table with Variable Length Rows

The following methods provide estimates for tables containing variable length data

with data types VARCHAR and VARGRAPHIC. Tables with columns containing

variable length data types result in rows of differing lengths that can be distributed

232

Database Administration

throughout a dbspace in different ways depending upon the order in which data is

loaded. The distribution of the variable length rows in the dbspace can

significantly affect the number of data pages occupied by a table.

There are three different methods you can use to more accurately estimate the data

page requirements for tables with variable length rows:

Modeling

An approach using a test dbspace containing a test table that

contains a representative sample of the data. The accuracy of this

estimates depends solely on the representativeness of the test table.

Worst case

An approach that provides an estimate of the number of data

pages that will accommodate the table regardless of the order of

the rows. This approach will overestimate the number of pages in

many cases, but will always ensure that you have allocated enough

pages.

Splitting

An approach requiring an approximation of the number of rows

that fall within a range of row lengths. This may produce a more

realistic number of pages than the worst case method but does not

ensure that the table will fit.

Estimating Data Pages with Modeling: Establish a database before you begin

modeling your data page requirements. Then, do the following:

1. Acquire a test dbspace.

2. Create the table in the dbspace and load a sampling of the data into it.

3. Ensure that the statistics for the table are up-to-date. If the statistics are not

current, this can be done by performing a load (with statistics set on), or by

performing an explicit UPDATE STATISTICS on the table.

4. Get the NACTIVE value for this dbspace from the SYSDBSPACES catalog table.

Since there is only one table (and its companion table, if there is one or more

long fields) in the dbspace, then the NACTIVE value indicates the number of

data pages this table is currently using.

5. Multiply the NACTIVE value by a factor representing the relationship between

the actual table size and this test table size. The result is an estimate of the

number of data pages the actual table requires.

Consider the following when modeling your data page requirements in this way:

v Design a test table large enough to cover at least several data pages.

v Before creating the test table in the test dbspace, drop the dbspace and acquire it

again. This ensures that previous use of the dbspace does not affect your

modeling results.

v Try to arrange the various lengths of the rows in a sequence as close as possible

to what you expect from your real table.

Estimating Using the Worst Case Method: This method is the safest method to

use; it will ensure that you have enough pages regardless of the distribution of the

rows in the table. However, it may overestimate your requirements.

To use this method you need to know the:

v Length of the longest row in the table

v Average length of a row in the table

v Number of rows in the table.

Then do the following:

Appendix A. Estimating Your Dbspace Requirements

233

1. Calculate the maximum row length (MAXROWLEN) by using the maximum

length of the VARCHAR and VARGRAPHIC columns instead of the average

length as shown in Table 28 on page 224.

2. Substituting MAXROWLEN for AVGROWLEN, perform steps 1 to 4 of the

formula for estimating the number of pages as shown in “Estimating the

Number of Data Pages” on page 227. This produces the Effective Page Size for

the MAXROWLEN (denoted EPSmax).

3. Estimate the average lengths of columns in your table and calculate the average

row length (AVGROWLEN) as described in Table 28 on page 224.

4. Calculate the worst case estimate using the following formula:

AVGROWLEN x Number of Rows

Worst Case = MINIMUM ( Number of rows, -------------------------- )

EPSmax - MAXROWLEN + 1

Example using the Worst Case Method:

Consider a 500,000 row table being loaded into a dbspace with PCTFREE=10.

Assume the overall AVGROWLEN value is 50 bytes. Assume the calculated

MAXROWLEN value is 110 bytes for this table.

Calculate the EPSmax value as follows:

40 x PCTFREE + MAXROWLEN = 40 x 10 + 110 = 510

The corresponding EPSmax is 3190.

Substitute the values in the worst case formula:

50 x 500000

MINIMUM (500000, --------------) = MINIMUM (500000, 8114.2)

3190 - 110 + 1

To store this table you need at most 8115 data pages.

Estimating Using the Splitting Method: This method assumes that you can

approximate the frequency of different ROWLENGTHs in the table to be stored.

This method is as follows:

1. Split the set of all rows into several ROWLENGTH groups and calculate page

requirements for each group as if it were a separate table using the formula

described in “Estimating the Number of Data Pages” on page 227.

2. Add the page requirements for the groups to estimate the total table page

requirements.

Try several different groupings of rows, making sure that each group is large

enough to cover several data pages. If groups of rows do not cover several data

pages, the estimate could be too high.

Different groupings will give different results. Select the highest overall page

estimate to ensure that your estimate includes a contingency.

Example Using the Splitting Method:

Consider a 2000 row table to be loaded into a dbspace with PCTFREE=0. Assume

the overall AVGROWLEN value to be 1000 bytes. Assume, also, that 25 percent of

the rows in the table are longer than 800 bytes, with an AVGROWLEN value =

2500 bytes. The remaining 75 percent of the rows are less than 800 bytes long, with

an AVGROWLEN value = 500 bytes.

234

Database Administration

We consider two groups of rows for this calculation:

Group 1

with 1500 rows and AVGROWLEN = 500

Group 2

with 500 rows and AVGROWLEN = 2500.

Perform the calculations described in “Estimating the Number of Data Pages” on

page 227 treating each group as a table.

For Group 1 with AVGROWLEN = 500 and PCTFREE = 0, the EPS is 4080.

Therefore we can fit 8 rows per page (4080/500 = 8.16) and we need 188 pages

(1500/8 = 187.5) to store the 1500 rows.

For Group 2 with AVGROWLEN = 2500 and PCTFREE = 0, the EPS is 2562.

Therefore we can fit 1 row per page (2562/2500 = 1.02) and we need 500 pages

(500/1 = 500) to store the 500 rows.

Adding these two page requirements together gives an overall estimate of 688 data

pages (188 + 500) to store the whole 2000 row table.

Compare this to the result if you used the formula for fixed length rows. Using

only the formula described in “Pages Required for Storing Tables with Fixed

Length Rows” on page 227 and the overall AVGROWLEN of 1000, the EPS is 4080.

Therefore we can fit 4 rows per page (4080/1000 = 4.08) and we need 500 pages

(2000/4 = 500) to store all 2000 rows. This is considerably less than the 688 pages

estimated. The real number of data pages required is likely between 500 and 688

depending on the order in which the rows are being stored in the dbspace.

Estimating the Number of Index Pages

Generally speaking, you should take the default allocation for index pages in the

dbspace (PCTINDEX=33). This is means that the number of index pages is

approximately DATA PAGES / 2. This leaves you considerable freedom to vary the

indexing you do on the tables in the dbspace. Another way of looking at this is

that if the number of index pages is more than half the number of data pages, you

may be trying to support too many indexes on the tables in the dbspace. As a

result, you may experience performance problems on INSERT, UPDATE, and

DELETE operations.

However, if the data in the dbspace is largely used for read only operations, you

may want to create a lot of different indexes. If this is the case, you may need

more index pages than is provided for by the default PCTINDEX value of 33

percent. For such read only (or read mostly) cases, you may want to do the

detailed analysis of index page requirements to determine the appropriate

PCTINDEX value based on the size of indexes you plan on supporting.

If the data in the dbspace is to have very few indexes with rather small key

lengths, then you may want to do the detailed analysis of index page requirements

to determine an appropriate PCTINDEX value that is smaller than the default.

The formula for calculating the appropriate PCTINDEX value is:

TOTAL INDEX PAGES

PCTINDEX =

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

HEADER PAGES + DATA PAGES + TOTAL INDEX PAGES

In this formula, TOTAL INDEX PAGES is the sum of the number of index pages

required for each planned index.

Appendix A. Estimating Your Dbspace Requirements

235

The next section provides the guidelines for estimating the number of index pages

required for an index.

Estimating the Size of an Index

Index storage is allocated in pages. Each page contains data for only one index.

The minimum storage required for any index is one page.

To estimate the amount of storage required for an index, you must consider the

type of information in the index key and the amount of information in the table

being indexed. The following table information must be considered for calculating

the size of an index:

v The number of rows in the table

v The length of a key value

v Whether the key is variable or fixed in length

v The number of distinct keys in the table

For indexes that are not unique, this result may be less than the total number of

rows in the table. Each entry in a leaf page of the index consists of a key value

and one or more row pointers, called Tuple Identifiers or TIDs, for the row

having this key value.

For unique indexes, each entry contains just one TID.

These entries are called clusters.

v The amount of free space (PCTFREE) defined for the index. The PCTFREE value

is the percentage of free space to be left on index pages during index creation.

For fixed length unique key indexes, the following calculations for index size will

be accurate. For variable length or non-unique indexes, the calculations may either

overestimate or underestimate the size of an index.

Generally, the size may be overestimated if:

v A large variable length column is the last column in the index or

v There are a large number of duplicates in the index.

The size may be underestimated if the varying length keys are not evenly

distributed. For example, if the ordering of the keys in the index is from shortest to

longest, then the lengths are not evenly distributed and space will be

underestimated.

To calculate the size of the index perform the following steps:

1. Calculate the Effective Index Page Size

The Effective Index Page Size (EIPS) is similar to the effective page size

calculated for data pages. The formula for index pages differs for fixed length

and variable length index keys.

For fixed length index keys:

EIPSmax = 4056 - (41 x PCTFREE)

For variable length index keys:

a. Calculate the maximum encoded length of each variable length column in

the index (in bytes).

For a short VARCHAR column, if it is the last column in the key,

VARCOL(n) = maximum length of column

Otherwise,

VARCOL(n) = (INTEGER((max length of column - 1) / 4) + 1) * 5

For a short VARGRAPHIC column, if it is the last column in the key,

236

Database Administration

VARCOL(n) = 2*(maximum length of column)

Otherwise,

VARCOL(n) = (INTEGER((2*max length of column-1)/4)+1) * 5

b. Calculate the maximum length of a key

KEYLENmax = the sum of the lengths of fixed columns (in bytes)

+ VARCOL(1) + ... + VARCOL(n)

+ 1 for the length of the key

+ 1 for each column that allows nulls

c. Use this KEYLENmax value to calculate the maximum length of a cluster

with 1 TID.

CLUSTERmax = KEYLENmax

+ 1 for number of TIDs

+ 4 for the TID

d. Use this CLUSTERmax value to calculate the minimum effective index

page size for leaf pages.

EIPSminleaf = 4056 - (41 x PCTFREE) - CLUSTERmax + 1

e. Use the KEYLENmax value again to calculate the maximum length of a

nonleaf pair.

PAIRLENmax = KEYLENmax

+ 3 for the page number

+ 4 (if index is not unique)

f. Use this PAIRLENmax value to calculate the minimum effective index page

size for nonleaf pages.

EIPSmin-nonleaf = 4056 - (41 x PCTFREE) - PAIRLENmax + 1

Calculate the average number of rows per key value.

The average number of rows identified in one cluster is:

NUMBER_KEYS = Number of distinct keys

ROWSPERCLUSTER = Number of rows in table

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

NUMBER_KEYS

If ROWSPERCLUSTER is greater than 255, then the key must be duplicated.

In this case, the following calculations must be done:

NUMBER_KEYS = (TRUNC [1 + (ROWSPERCLUSTER/255) ]) x NUMBER_KEYS

Number of rows in table

ROWSPERCLUSTER = -----------------------

NUMBER_KEYS

Calculate the average length of a key value.

a. Calculate the average encoded length of each variable length column in

the index, if any, in bytes.

VARCOLavg(n) = (1.25 x average length of column) + 3

These numbers must be rounded up to integer values.

Once again, when determining the length of graphic data, allow 2 bytes

for each character.

b. Calculate the average length of a key in the index.

KEYLEN = the sum of the lengths of fixed columns (in bytes)

+ VARCOLavg(1) + ... + VARCOLavg(n)

+ 1 if there are any variable-length columns

+ 1 for each column that allows nulls

Calculate the cluster size for the index, using the value of ROWPERCLUSTER

from step 2.

CLUSTERSIZE = 1 + KEYLEN + (4 x ROWSPERCLUSTER)

Appendix A. Estimating Your Dbspace Requirements

237

Calculate the number of keys that can be put on a leaf page, using the value

of CLUSTERSIZE from step 4 on page 237.

#KEYSleaf = TRUNC [EIPS/CLUSTERSIZE]

where EIPS is EIPSmax for an index with fixed length keys or EIPSminleaf for

an index with variable length keys.

Calculate the number of leaf pages, using the the values of NUMBER_KEYS

from step 2 on page 237 and #KEYSleaf from step 5:

LEAF PAGES = TRUNC [1 + (NUMBER_KEYS/#KEYSleaf)]

Calculate the length of a nonleaf page entry with the value of KEYLEN from

step 3b on page 237.

PAIRLEN = KEYLEN + 3

+ 4 (if index is not unique)

Use the value of PAIRLEN from step 7 to calculate the number of keys that

can be put on a nonleaf page.

#KEYSnonleaf = TRUNC [EIPS/PAIRLEN]

where EIPS is EIPSmax for an index with fixed length keys or

EIPSmin-nonleaf for an index with variable length keys.

Calculate the number of nonleaf pages required at each level, using the value

of LEAF PAGES from step 6.

level = 1

NONLEAF PAGES(level)=

TRUNC [1 + (LEAF PAGES/KEYSnonleaf)]

While the number of nonleaf pages at the current level is greater than 1, do

the following:

level = level + 1

NONLEAF PAGES(level) =

TRUNC [1 + (NONLEAF PAGES(level-1)/KEYSnonleaf)]

10.

Calculate the total number of index pages by adding the LEAF PAGES value

from step 6 and the nonleaf pages for every level as calculated in step 9.

INDEX PAGES = LEAF PAGES + NONLEAF PAGES(1) + ... + NONLEAF PAGES(n)

Estimating Internal Dbspace Size and DASD Needs for Sort Operations

Internal dbspaces are most commonly used as work areas for sorting data. It is

helpful to predict the amount of space needed to perform a sort, in order to

estimate how big your internal dbspaces should be.

This section will discuss how much space is required to perform a particular sort.

Since multiple users can be performing a sort concurrently, it is more difficult to

determine the maximum internal dbspace requirements for your database than for

a given sort. This maximum depends both on the expected size of a sort, as well as

how many sorts are expected to be occurring concurrently. You must also consider

non-sort usage of internal dbspaces, such as to contain materialized views or

intermediate query results. Refer to the DB2 Server for VM System Administration or

DB2 Server for VSE System Administration manual for more information about

internal dbspace usage, including guidelines for determining the number and size

of internal dbspaces for your database.

The size of internal dbspaces in a database is often dictated by the largest sort

operation possible in that database, such as the sort needed to create an index on

the largest table in the database.

238

Database Administration

When Do We Sort?

Sorting is performed whenever an operation requires that data be ordered or that

duplicate values be eliminated, and no appropriate index exists that provides the

necessary ordering. Even if an appropriate index exists, the Optimizer may decide

not to use it.

In most cases, it is readily apparent where a sort can occur. The following is a list

of all cases:

v Index creation, such as a result of the CREATE INDEX statement, the adding or

activating of a PRIMARY KEY or UNIQUE CONSTRAINT, or the reorganizing

of an invalid index. We sort on the index or key/constraint columns.

v UNION statement. We sort on the columns listed in the SELECT list of the

queries being unioned. The sort eliminates duplicate values. (No duplicate

elimination occurs for UNION ALL.)

v ORDER BY and GROUP BY clauses. Both these clauses request that data be

ordered. We sort on the columns or expressions (ORDER BY can sort on the

result of an expression in the SELECT list) listed in the clause.

v DISTINCT clause. This is another case of sorting to eliminate duplicate values.

We sort on the columns listed in the clause.

v Merge/scan (type 2) join. This type of join requires that the columns on which

we are joining be ordered. We sort on the join columns.

You can use the EXPLAIN command if you are unsure whether or not a particular

query performs a sort. If you query performs more than one of the above, then it

may perform multiple sorts. If you UNION or join more than two tables, another

sort may be performed for each additional table, since we UNION and join tables

two at a time.

For further information on sorting, refer to the DB2 Server for VSE & VM Diagnosis

Guide and Reference manual.

Internal Dbspace Characteristics

The characteristics of an internal dbspace are different from those of a permanent

dbspace:

v Each page is 4096 bytes.

v No free space is reserved on pages.

v There is no space at the end of a page reserved for pointers to each row on the

page, and the limit of 256 rows per page is removed.

v There is always exactly one header page.

v Pages of internal dbspaces are never shadowed when they are modified.

There are no free space classes for internal dbspace pages, since data is always

added at the end, and hence there is never a need to search for free space in which

to store a row. This avoids the space wastage which can occur due to the

granularity of free space classes (a row will be stored on a page in an internal

dbspace whenever the page has enough free bytes to hold the row).

You can see that internal dbspaces are much simpler than permanent dbspaces.

Calculating the number of pages needed to hold a certain amount of data is also

simpler.

Appendix A. Estimating Your Dbspace Requirements

239

Calculating Internal Dbspace Size Requirements

We will calculate the amount of space required to hold a copy of the working set

of data during a sort. Specifically, we will calculate the size of the initial working

set, since the working set can only get smaller due to the elimination of duplicate

values. In building the initial working set, we retrieve a portion of the input data

(enough to fill an internal sort buffer), sort it, and write the sorted portion to an

internal dbspace. Duplicates may be eliminated during the sort. We will not

consider the effects of duplicate elimination in these calculations, since these effects

are dependent on the order in which data is encountered.

The following steps calculate the size of a sort row. The sort row is made up

mostly of the columns by which we are ordering, that is the sort key, plus any

other columns which must appear in the result.

Calculate the average length of a sort key.

a. Calculate the average encoded length of each variable length ordering

column (in bytes). The average length should not include trailing blanks (if

any) since these blanks are not stored in the sort key.

VARCOLavg(n) = (1.25 x average length of varying-length

ordering column n) + 3

These numbers must be rounded up to integer values.

The encoding of varying-length values incurs an overhead of approximately

25 percent.

b. Calculate the average length of a sort key.

SORTKEYLEN = the sum of the lengths of fixed-length ordering columns

(in bytes)

+ VARCOLavg(1) + ... + VARCOLavg(n)

+ 1 for each ordering column that allows nulls

For index creation, the TID of the data row is part of the sort key/row. If

the sort is for index creation:

SORTKEYLEN = SORTKEYLEN + 4

Calculate the average length of a sort row. We add overhead for the sort row

header, plus add any non-ordering columns which must appear in the result.

There are no non-ordering columns for index creation. Non-ordering columns

are not encoded.

SORTROWLEN = SORTKEYLEN

+ 3 bytes (sort row header)

+ the sum of the lengths of fixed-length non-ordering columns

(in bytes)

+ the sum of the average lengths of varying-length non-ordering

columns (in bytes)

+ 1 for each non-ordering column which allows nulls

For cases other than index creation, where the sort key contains at least one

varying-length column, there will be the following additional overhead:

v A one-byte counter will indicate the number of varying-length key columns

containing trailing blanks. This counter is used even if none of the columns

contain trailing blanks. In this case it is set to zero.

SORTROWLEN = SORTROWLEN + 1

v In addition to the column counter, the number of trailing blanks that each

column originally had is recorded. If at least one column had trailing blanks,

then a one-byte counter is allocated for each varying-length column.

SORTROWLEN = SORTROWLEN + number of varying-length sort key columns

If the data does not contain trailing blanks, then this overhead is not

incurred.

240

Database Administration

3. Adjust for varying-length data.

For varying length sort rows, the order in which rows are encountered and

stored can affect the number of pages required. To account for this possibility,

we can use a method similar to the Effective Index Page Size used in

calculating the size of an index. Briefly, this method models the worst case

where the last sort row we try to insert into a page is the largest possible sort

row, and the page has one fewer bytes of free space available. This gives us our

maximum space wastage per page, and will yield the upper bound on the

number of pages we will use. To determine the Effective Internal Dbspace Page

Size (EIDPS), do the following:

a. For fixed-length data

EIDPS = 4080

b. For varying-length data, repeat the previous calculations to determine

SORTROWLEN, substituting the maximum length of varying-length

columns for the average length. This gives us MAX SORTROWLEN.

EIDPS = 4080 - (MAX_SORTROWLEN + 1)

The following steps will calculate the number of pages required to hold a copy of

all sort rows. This is the minimum size of internal dbspace that is required to

perform the sort.

First calculate how many rows will fit on a page.

ROWS_PER_PAGE = TRUNC [EIDPS/SORTROWLEN]

Determine the number of sort rows.

For index creation, the number of sort rows is the same as the number of rows

in the table. For cases where only a subset of the rows in a table participates in

a sort, then the number of participating rows must be estimated based on your

knowledge of the query and the contents of the table.

NROWS = number of rows expected to participate in the sort

Compensate for effect of sort buffering

A block of input rows is encoded and stored in a sort buffer. The contents of

this buffer are then sorted and written out to pages of an internal dbspace.

Since the buffer is large enough to fill several internal dbspace pages, and the

space in the buffer is contiguous while each internal dbspace page has a

header, then we must account for this in determining the number of pages

required.

a. Calculate how many rows are in the block of rows that would fill the sort

buffer.

ROWS_PER_BLOCK = TRUNC [40948 / SORTROWLEN]

b. Calculate how many internal dbspace pages would be filled by a block of

rows.

PAGES_PER_BLOCK = ROWS_PER_BLOCK / ROWS_PER_PAGE

This number must be rounded up to an integer value.

c. Calculate how many full blocks the expected number of sort rows would

generate.

FULL_BLOCKS = TRUNC [NROWS / ROWS_PER_BLOCK]

d. Calculate how many rows would be in the last (not full) block.

ROWS_LAST_BLOCK = NROWS - (ROWS_PER_BLOCK x FULL_BLOCKS)

Finally, using all the information we have derived so far, calculate the number

of pages required. We add one more page to account for the header page of the

internal dbspace.

Appendix A. Estimating Your Dbspace Requirements

241

NPAGES = (FULL_BLOCKS x PAGES_PER_BLOCK)

+ (ROWS_LAST_BLOCK / ROWS_PER_PAGE)

← round up

+ 1

For a sort to be successful, the internal dbspace must be defined to have at

least NPAGES pages.

Calculating Total Internal Dbspace and DASD Needs

So far we have calculated the size of the sort working set. After the initial working

set has been created, we then merge all the sorted portions to yield a final sorted

result. Multiple merge passes may occur before the final result is created. During

this process, two copies of the working set exist, in two internal dbspaces. For

successful completion of a sort, more than one internal dbspace must be available.

The final result may be smaller than intermediate results, due to such things as the

elimination of the three byte sort row header, and the decoding of varying-length

values in cases other than index creation. The amount of DASD required is

reduced only in the case where a single merge pass is performed; that is, when one

pass is made through the data from the initial working set to the final result. This

only occurs on sorts which are sufficiently small, or where the data is already

almost completely sorted.

When sorting for duplicate elimination, the merge process will remove duplicates.

As with the duplicate elimination which occurred during sorting, it is difficult to

predict the effect this will have. Note that, for calculating DASD requirements, we

are only interested in the duplicate elimination which would occur during the first

merge pass, since the second copy of the working set is created by this pass. The

completion of the first merge pass is the point at which our peak DASD usage

occurs.

We will not consider these cases, and calculate the amount of DASD required to

perform the sort as:

number of DASD pages = NPAGES x 2

For the sort to complete successfully, the storage pool to which the internal

dbspaces are assigned must have sufficient DASD pages available.

242

Database Administration

Appendix B. CMS EXECs

SQLINIT EXEC

The SQLINIT EXEC initializes a user machine for application server access. With

this EXEC, users specify the application server they wish to access and any special

options. Each user must run the SQLINIT EXEC.

Note: The user machine must be initialized regardless of whether you are

operating in single user mode or multiple user mode.

Initializing a User Machine

Before a user can run any DB2 Server for VM application program, use the DBS

Utility, run the preprocessors, or use ISQL:

1. The user machine must have read access to a database machine’s production

minidisk (Q-disk), read/write access to its own work minidisk (A-disk), and be

able to communicate with the database machine (by IUCV or APPC/VM).

For Information about providing minidisk access to user machines and

allowing user machines to communicate with the database machine, see the

DB2 Server for VM System Administration manual.

2. The user must log on and enter IPL CMS (if this was not done during the

logon procedure).

3. The user must initialize the user machine for application server access using

the SQLINIT EXEC. The syntax and options of the SQLINIT EXEC are

discussed below.

Note: Because the SQLINIT EXEC may issue the CMS NUCXLOAD and CMS

NUCXDROP commands, it should not be run in the CMS/DOS

environment.

Figure 60 on page 244 shows the format of the SQLINIT EXEC.

243

►►

SQLINIT

►◄

▼

Dbname

( server_name

)

dcssID

( dcss_id

)

SYNChronous

(

Yes

)

SQLDS

Protocol

(

AUTO

)

DRDA

QryBlksize

(

integer

)

DBCS

(

Yes

)

CHARNAME

( charname

)

ISO

DATEformat

(

USA

)

EUR

JIS

LOCal

ISO

TIMEformat

(

USA

)

EUR

JIS

LOCal

Yes

WorkUnit

(

)

TraceRA

(

)

0000

TraceDRRM

(

nnnn

)

TraceCONV

(

)

SSSNAME

( string

)

STack

QueRY

RESET

Figure 60. SQLINIT EXEC

The parameters of the SQLINIT EXEC are as follows:

Dbname

specifies the application server to be accessed. For the DBNAME keyword, you

can use any initial substring (for example, D, DB, DBN, DBNA, or DBNAM). If

DBNAME is omitted, the name of the last application server specified is used

as a default. If SQLINIT cannot determine the last application server accessed,

you are prompted to reissue the SQLINIT EXEC with the DBNAME parameter

specified. The application server can be either:

v A DB2 Server for VM application server

v Any application server that supports IBM’s implementation of the

Distributed Relational Database Architecture (DRDA) protocol.

dcssID

specifies the name of the bootstrap package that contains the saved segment ID

of the RA and ISQL components.

This parameter should be specified only if

244

Database Administration

you want to use a specific saved segment for the database manager code;

otherwise, it should be omitted. If you specify DCSSID, you must specify

DBNAME.

You can specify ID instead of DCSSID for the keyword. No other abbreviation

is valid. For more information about using saved segments for the database

manager code, refer to the DB2 Server for VM System Administration manual.

DCSSID identifies a bootstrap package that invokes RA or ISQL code that

resides in a discontiguous saved segment. If DCSSID is not specified, the

dcss_id value from the resid SQLDBN file on the production disk is used. If the

resid SQLDBN file is not available, and you are in a VM/ESA environment, the

dcss_id value from the SQLDCSID DEFAULT file (if available) is used. If

neither value is available, SQLDBA SQLRMBT and SQLDBA SQLISBT are

used. See “SQLINIT, SQLSTART, Bootstrap Modules and SQLDBN files” on

page 250 for more information on this topic.

Note: In a VM/ESA environment, resid may or may not be the same as

server_name.

SYNChronous

determines whether synchronous or asynchronous communication is used

between the user and database machines. Synchronous communication

performs better than asynchronous communication but has the following

restriction: you cannot use SQLHX or CANCEL to cancel SQL statements. The

only way to end an LUW is to use the DB2 Server for VM FORCE operator

command, or to re-IPL CMS.

Use synchronous communication primarily when running a well tested

production application against local application servers. The default value for

SYNCHRONOUS is NO.

Protocol

indicates the application requester access protocol to be used for

communicating with the application server.

If you specify the SQLDS option, the SQLDS protocol is used for

communication between a DB2 Server for VM application requester and a DB2

Server for VM application server. If this option is specified, the application

requester cannot connect to a non-DB2 Server for VM application server. Use

this option if both the application server and the application requester are part

of DB2 Server for VM system and both use the same default CCSIDs. SQLDS is

the default value.

Note: If PROTOCOL(SQLDS) is specified, the CCSID defaults set for the

application requester with the SQLINIT EXEC are not used; the CCSIDs

set for the application server are used.

If you specify the AUTO option, the application requester uses the SQLDS

protocol when communicating with a DB2 Server for VM application server

and the DRDA protocol when communicating with other application servers. If

both the application requester and the application server use AUTO protocol

but have different default CCSIDs, CCSID conversion is done correctly for

requests and replies. The AUTO option lets you access both like and unlike

systems without changing the PROTOCOL option and reissuing SQLINIT. You

should use this option in the following cases:

v The user needs access to both like and unlike systems.

Appendix B. CMS EXECs

245

v The CCSID defaults are not the same on the application server and the

application requester. For correct CCSID conversion the application server

must also use AUTO protocol.

v You need an LUWID associated with each task so that you can easily trace a

task back to its originating site.

If you specify the DRDA option, the application requester will use the DRDA

protocol when communicating with a like or unlike application server. If the

database machine is running code prior to Version 3 Release 3, the SQLDS

protocol is forced for that connection. Of the three options, DRDA has the

greatest performance overhead and storage requirements.

Notes:

1. The PROTOCOL value is ignored in single user mode and the SQLDS

protocol is used for the connection.

2. The DRDA and AUTO options can only be specified if:

v The DRDA facility is installed on the DB2 Server for VM application

requester

v The other application server to which you want to connect supports

IBM’s implementation of the DRDA protocol.

QryBlksize

specifies the block size of the returned rows of data when blocking performs

FETCHes or the block size of the buffer that will carry Inserts to the database

server. The number is specified in denominations of 1K and can range

anywhere from 1K to 32K. The default value is 8K.

Note: The QRYBLKSIZE parameter is ignored when the SQLDS protocol is

used for the connection.

DBCS

specifies whether DBCS character handling of SO/SI pairs is done or not. This

value is used by ISQL, the DBS Utility, and the preprocessors instead of the

value currently found in the SYSTEM.SYSOPTIONS table. If NO is specified,

keywords are converted from lowercase to uppercase by ISQL and the DBS

Utility. If YES is specified, error checking is done on DBCS data by ISQL, the

DBS Utility, and the preprocessors. The default value for DBCS is NO.

CHARNAME

specifies the CCSID values (CCSIDSBCS, CCSIDMIXED, and CCSIDGRAPHIC)

used by the application requester, and is used to determine how to fold

characters from lowercase to uppercase. Its value must be a valid character

name, such as those found in the CHARNAME column of the

SYSTEM.SYSCCSIDS table. CHARNAME is supported for the DRDA and

AUTO PROTOCOL options. The DB2 Server for VM product is shipped with

CHARNAME of the user machine initially set to INTERNATIONAL.

The SQLPREP EXEC, the DBS Utility, and the Resource Adapter use the

CHARNAME value for folding support in both single user mode and multiple

user mode. See the DB2 Server for VSE & VM Application Programming manual

for information on the SQLPREP EXEC.

DATEformat

specifies the date format. The default is ISO. This parameter is for information

only. It represents the date format in which the user wants to see date values

returned.

246

Database Administration

TIMEformat

specifies the time format. The default is ISO. This parameter is for information

only. It represents the time format in which the user wants to see time values

returned.

WorkUnit

specifies whether CMS Work Unit support is to be used for an application. The

default is Yes.

TraceRA

specifies the parts of the Resource Adapter (RA) that are to be traced and the

level of the trace. The positional digits correspond to the following Resource

Adapter subcomponents and functions:

v RA control flow

v Communications.

When 0 is specified, tracing is turned off. When 1 is specified, tracing is done

in limited detail. When 2 is specified, tracing is done in greater detail. The

default value for TRACERA is 00.

Note: A data stream trace is obtained by tracing the communications

subcomponent.

The following CMS FILEDEF command was entered to define the default trace

output file:

FILEDEF ARITRAC TAP2 SL (BLKSIZE 4096 NOCHANGE PERM

You can enter a different CMS FILEDEF command to override the default. The

options you specify on your CMS FILEDEF command will not be overridden

unless you reenter a CMS FILEDEF command to change them.

Note: The trace output, requested using the TRACERA, TRACEDRRM, and

TRACECONV parameters, is stored in a single file.

TraceDRRM

specifies the parts of the DRRM component that are to be traced and the level

of the trace. The positional digits correspond to the following DRRM

subcomponents and functions:

v Parser

v Generator

v Dictionary

v RDIIN Manager.

When 0 is specified, tracing is turned off. When 1 is specified, tracing is done

in limited detail. When 2 is specified, tracing is done in greater detail. The

default value for TRACEDRRM is 0000.

The following CMS FILEDEF command was entered to define the default trace

output file:

FILEDEF ARITRAC TAP2 SL (BLKSIZE 4096 NOCHANGE PERM

You can enter a different CMS FILEDEF command to override the default. The

options you specify on your CMS FILEDEF command will not be overridden

unless you reenter a CMS FILEDEF command to change them.

Notes:

1. The TRACEDRRM parameter is ignored when the SQLDS protocol is used

for the connection.

Appendix B. CMS EXECs

247

2. The trace output, requested using the TRACERA, TRACEDRRM, and

TRACECONV parameters is stored in a single file.

TraceCONV

specifies that the data conversion component is to be traced and the level of

the trace.

When 0 is specified, tracing is turned off. When 1 is specified, tracing is done

in limited detail. When 2 is specified, tracing is done in greater detail. The

default value for TRACECONV is 0.

The following CMS FILEDEF command was entered to define the default trace

output file:

FILEDEF ARITRAC TAP2 SL (BLKSIZE 4096 NOCHANGE PERM

You can enter a different CMS FILEDEF command to override the default. The

options you specify on your CMS FILEDEF command will not be overridden

unless you reenter a CMS FILEDEF command to change them.

Note: The trace output, requested using the TRACERA, TRACEDRRM, and

TRACECONV parameters, is stored in a single file.

SSSNAME

specifies the name of the status shared segment. This parameter is optional.

For more information on defining the status shared segment for the DB2 Server

for VM system, refer to the DB2 Server for VM System Administration manual.

STack

places all values currently set for the parameters of the SQLINIT EXEC, except

DCSSID, onto the CMS stack in the same sequence as shown for QUERY.

QueRY

displays all values currently set for the parameters of the SQLINIT EXEC,

except DCSSID. It also displays the resource adapter code release level, CCSID

values, LDATELEN value, and LTIMELEN value. (See “SQLGLOB EXEC” on

page 252 for information about LDATELEN and LTIMELEN.)

Note: The value returned for CHARNAME is valid only if the value specified

for the PROTOCOL parameter is not SQLDS. If the PROTOCOL

parameter value is SQLDS, the CHARNAME value returned for the

application requester is the same as the CHARNAME value of the

application server to which it is connected (even if they are not the

same).

The following is sample output from an SQLINIT QUERY.

248

Database Administration

ARI0717I Start SQLINIT EXEC: 05/29/92 14:52:40 EDT.

SELECTED TABLE IS: SQL/DS

DBNAME=SQLDBA

DBCS=NO

SYNCHRONOUS=NO

DATEFORMAT=ISO

TIMEFORMAT=ISO

TRACERA=00

LDATELEN=0

LTIMELEN=0

RELEASE=3.3.0

WORKUNIT=NO

QRYBLKSIZE=8

PROTOCOL=SQLDS

CHARNAME=INTERNATIONAL

CCSIDSBCS=500

CCSIDMIXED=0

CCSIDGRAPHIC=0

TRACEDRRM=0000

TRACECONV=0

SSSNAME=

ARI0796I End SQLINIT EXEC: 05/29/92 14:52:40 EDT

RESET

resets all values currently set for the parameters of the SQLINIT EXEC, except

DCSSID. The next time the SQLINIT EXEC is invoked, the defaults are used.

The SQLINIT EXEC parameter values are stored in the CMS LASTING GLOBALV

file. Each time the SQLINIT EXEC is run, the parameter values are appended to

the LASTING GLOBALV file. To maintain the LASTING GLOBALV file size,

duplicate entries can be removed. Subsequently, when the user reenters the

SQLINIT EXEC, the parameter value is established as follows:

1. If the user specifies a parameter on the SQLINIT EXEC, that parameter value is

used.

2. If a parameter is not specified on the SQLINIT EXEC, the value stored in the

LASTING GLOBALV file is used. That is, the default is the value used on the

most recent SQLINIT EXEC.

3. If there are no values in the LASTING GLOBALV file (no values were specified

on a previous SQLINIT EXEC, or SQLINIT RESET has reset the entries to

blanks in the LASTING GLOBALV file), the application server-wide defaults

established by the SQLGLOB EXEC are used.

4. If nothing is available, that is, if no application server-wide defaults established

by the SQLGLOB EXEC exist, the SQLINIT EXEC will supply hardcoded

defaults, except for the DBNAME parameter.

The parameter values remain in the LASTING GLOBALV file until explicitly

changed through a subsequent SQLINIT EXEC with new parameters. SQLINIT

RESET resets the entries to blanks, for any SQLINIT EXEC parameter values

currently stored in the LASTING GLOBALV file.

The LASTING GLOBALV file is left on the user’s A-disk. This means that users do

not have to run the SQLINIT EXEC every time they log on. (This also means that

users do not need to run the SQLINIT EXEC from their PROFILE EXECs.) The

only times the user needs to rerun the SQLINIT EXEC are:

v When the user wants to change the default application server

v When the user wants to change any of the SQLINIT EXEC parameter values.

Appendix B. CMS EXECs

249

SQLINIT, SQLSTART, Bootstrap Modules and SQLDBN files

The SQLINIT EXEC provides for a user’s program to communicate with the

database machine by copying to the user’s A-disk the following bootstraps:

dcss-id SQLRMBT Q

---> ARISRMBT MODULE A

dcss-id SQLISBT Q

---> ARISISBT MODULE A

The bootstrap modules reside on the production minidisk (Q-disk).

Prior to Version 3 Release 1, the SQLINIT EXEC used the ARISRMBT module to

obtain default SQLINIT EXEC values. The SQLINIT EXEC now uses the LASTING

GLOBALV file instead of this bootstrap module to obtain default values. The

bootstrap module is still produced to maintain compatibility with load modules

generated prior to Version 3 Release 1.

The ARISRMBT module is for the resource adapter, but it is incomplete. The

resource adapter needs to know the name of the database machine with which it is

to communicate.

Note: In a VM/ESA environment, the resource adapter only needs to know the

database (resource) name.

The resource adapter also needs to know the default DCSSID. At this time

ARISRMBT does not contain this information. The SQLINIT EXEC uses a CMS file

called a SQLDBN file to locate information about a database. The SQLDBN file is

created by the SQLSTART EXEC. When the SQLSTART EXEC is invoked, it starts

the database manager code in a particular machine to access a particular

application server. The SQLSTART EXEC creates a CMS file on the production

minidisk to record this information (if the CMS file does not already exist). The

name of the file is taken from the DBNAME parameter if the resid is the same as

the server-name; otherwise, the resid will be resolved using the RESID NAMES file.

The filetype is SQLDBN. So, suppose you log on the SQLDBA database machine

and enter:

SQLSTART DBNAME(DB01) DCSSID(MYBOOT)

The SQLSTART EXEC accesses or creates the DB01 SQLDBN Q file. DB01 contains

the following information:

1. The server-name of the application server being accessed (DB01)

2. The name of the database machine that is accessing the application server

(SQLDBA)

Note: The name of the database machine is not needed to complete the

bootstrap in a VM environment.

3. The name of the bootstrap or DCSSID being used (MYBOOT).

When the database machine is shut down, the resid SQLDBN file remains on the

production minidisk. (The name of the file is taken from the DBNAME parameter

if the resid is the same as the server-name; otherwise, the resid will be resolved using

the RESID NAMES file.) It is updated whenever a database machine is started to

access the application server and one of the following is true:

v The DCSSID specified on the SQLSTART EXEC is different from the one stored

in the SQLDBN file

v The AMODE specified on the SQLSTART EXEC is different from the one stored

in the SQLDBN file

250

Database Administration

v The DBNAME specified on the SQLSTART EXEC is different from the one stored

in the SQLDBN file

v The database machine trying to access the application server is different from

the one that last created the SQLDBN file.

The SQLINIT EXEC uses these SQLDBN files to complete the resource adapter

bootstrap module. That is, the SQLINIT EXEC looks for the SQLDBN file having

the resid that corresponds to the DBNAME parameter. If the DBNAME is greater

than 8 bytes, it uses the SQLDCSID DEFAULTS file. Otherwise, the SQLINIT EXEC

reads the information in the SQLDBN file and then generates the complete

resource adapter bootstrap on the work minidisk:

ARISRMBT MODULE A

resid SQLDBN Q

ARISRMKC TEXT Q

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

ARISRMBT MODULE A

The new module is called ARISRMBT. It will replace any existing ARISRMBT

MODULE on the user’s A-disk. ARISRMBT serves two purposes:

v It identifies where the resource adapter code is to be loaded

v It tells the resource adapter where to direct its communications.

Note the resid SQLDBN files will not be available to user machines that:

v Access an application server that resides on a different processor

v Access an application server that does not own the Production (Q) minidisk to

which the user has a link.

If the SQLDBN file is not available, the SQLINIT EXEC looks for the SQLDCSID

DEFAULT file for a default saved segment (DCSSID). If the SQLDBN file or

SQLDCSID DEFAULT file is not found on the Production (Q) disk, the default

SQLDBA SQLRMBT and SQLDBA SQLISBT bootstrap modules are used. If the

dcss-id from the default SQLDBN file is not the one desired, specify the dcss-id on

the SQLINIT EXEC to override it.

Note: The SQLDCSID DEFAULT file is only used in a VM environment. The

SQLDCSID DEFAULT file is created by the SQLGENLD EXEC. See the DB2

Server for VM System Administration manual for more information on saved

segments.

The ISQL bootstrap module, on the other hand, only identifies where the ISQL

code is to be loaded. Because ISQL uses the resource adapter also, there is no need

to identify the database machine in the ISQL bootstrap.

When an application initially calls the database manager, the bootstraps are

executed to load the resource adapter and to help establish communication with

the database machine. The database name is used for APPC/VM communication in

VM environments.

Note that the bootstrap modules are left on the user’s A-disk. This means that a

user does not have to run the SQLINIT EXEC every time he or she logs on. (This

also means that users do not need to run the SQLINIT EXEC from their PROFILE

EXECs.) The only times the user needs to rerun SQLINIT are:

v When the user wants to change the default application server. If for any reason

the bootstraps are not valid, it is detected and a message is issued to the user.

Appendix B. CMS EXECs

251

v When the user wishes to use bootstraps that have been defined after running the

SQLINIT EXEC.

For example, if bootstrap modules are defined, the user runs the SQLINIT EXEC,

and then new bootstrap modules are defined, the user will have to run the

SQLINIT EXEC again to take advantage of the new bootstrap modules.

When the DBNAME parameter is not specified on the SQLINIT EXEC, the name of

the application server last accessed will be obtained from the ARISRMBT module

residing on the user’s A-disk. New versions of the ARISRMBT and ARISISBT

modules will then be generated to reflect the information stored in the SQLDBN

file for that application server.

You can run the SQLINIT EXEC any number of times from within another EXEC.

SQLGLOB EXEC

Use the SQLGLOB EXEC to set the default parameter values for the SQLINIT

EXEC, except DCSSID, for your local DB2 Server for VM application server. The

default values will only be used for application requests that have linked to the

production disk of the local application server. The syntax of the SQLGLOB EXEC

is similar to that of the SQLINIT EXEC.

The SQLGLOB EXEC creates a file on the production disk, called SQLGLOB

DEFAULTS, containing all the default values for the SQLINIT EXEC, except

DCSSID. If a user runs the SQLINIT EXEC without specifying some of the

parameter values and these values are not stored in the LASTING GLOBALV file,

then the missing parameter values are taken from the SQLGLOB DEFAULTS file

that was created with the SQLGLOB EXEC. The syntax of the SQLGLOB EXEC is

shown in Figure 61 on page 253.

252

Database Administration

►►

SQLGLOB

►◄

▼

Dbname

( server_name

)

SYNChronous

(

Yes

)

SQLDS

Protocol

(

AUTO

)

DRDA

QryBlksize

(

integer

)

DBCS

(

Yes

)

CHARNAME

( charname

)

ISO

DATEformat

(

USA

)

EUR

JIS

LOCal

ISO

TIMEformat

(

USA

)

EUR

JIS

LOCal

LDATELEN

(

integer

)

LTIMELEN

(

integer

)

Yes

WorkUnit

(

)

TraceRA

(

)

0000

TraceDRRM

(

nnnn

)

TraceCONV

(

)

SSSNAME

( string

)

STack

QueRY

RESET

Figure 61. SQLGLOB EXEC

The parameters of the SQLGLOB EXEC are as follows:

Dbname

specifies the application server to be accessed. For the DBNAME keyword, you

can use any initial substring (for example, D, DB, DBN, DBNA, or DBNAM). If

DBNAME is omitted, the name of the last application server specified is used

as a default. The application server can be either:

v A DB2 Server for VM application server

v Any application server that supports IBM’s implementation of the

Distributed Relational Database Architecture (DRDA) protocol.

Appendix B. CMS EXECs

253

Note: Access to non-DB2 Server for VM application servers is only possible

if the DRDA facility is installed on the DB2 Server for VM application

requester.

SYNChronous

determines whether synchronous or asynchronous communication is used

between the user and database machines. Synchronous communication

performs better than asynchronous communication but has the following

restriction: you cannot use SQLHX or CANCEL to cancel SQL statements. The

only ways to end an unwanted LUW is to use the FORCE command, or to

re-IPL CMS.

Use synchronous communication primarily when running a well tested

production application against local application servers. The default value for

SYNCHRONOUS is NO.

Protocol

indicates the application requester access protocol to be used for

communicating with the application server.

If you specify the SQLDS option, the SQLDS protocol is used for

communication between a DB2 Server for VM application requester and a DB2

Server for VM application server. If this option is specified, the application

requester cannot connect to a non-DB2 Server for VM application server. Use

this option if both the application server and the application requester are part

of DB2 Server for VM system and both use the same default CCSIDs. SQLDS is

the default value.

Note: If PROTOCOL(SQLDS) is specified, the CCSID defaults set for the

application requester with the SQLINIT EXEC are not used; the CCSIDs

set for the application server are used.

If you specify the AUTO option, the application requester uses the SQLDS

protocol when communicating with DB2 Server for VM application servers and

the DRDA protocol when communicating with other application servers. If

both the application requester and the application server use AUTO protocol

but have different default CCSIDs, CCSID conversion is done for requests and

replies. The AUTO option lets you access both like and unlike systems without

changing the PROTOCOL option and reissuing SQLINIT. You should use this

option in the following cases:

v The user needs access to both like and unlike systems.

v The CCSID defaults are not the same on the application server and the

application requester. For correct CCSID conversion the application server

must also use AUTO protocol.

v You need an LUWID associated with each task so that you can easily trace a

task back to its originating site.

If you specify the DRDA option, the application requester will use the DRDA

protocol when communicating with a like or unlike application server. If the

database machine is running code prior to Version 3 Release 3, the SQLDS

protocol is forced for that connection. Of the three options, DRDA has the

greatest performance overhead and storage requirements.

Notes:

1. The PROTOCOL value is ignored in single user mode and the SQLDS

protocol is used for the connection.

2. The DRDA and AUTO options can only be specified if:

254

Database Administration

v The DRDA facility is installed on the DB2 Server for VM application

requester

v The other application server to which you want to connect supports

IBM’s implementation of the DRDA protocol.

QryBlksize

specifies the block size of the returned rows of data when blocking performs

FETCHes or the block size of the buffer that will carry Inserts to the database

server. The number is specified in denominations of 1K and can range

anywhere from 1K to 32K. The default value is 8K.

Note: The QRYBLKSIZE parameter is ignored when the SQLDS protocol is

used for the connection.

DBCS

specifies whether DBCS character handling of SO/SI pairs is done or not. This

value is used by ISQL, the DBS Utility, and the preprocessors instead of the

value currently found in the SYSTEM.SYSOPTIONS table. If NO is specified,

keywords are converted from lowercase to uppercase by ISQL and the DBS

Utility. If YES is specified, error checking is done on DBCS data by ISQL, the

DBS Utility, and the preprocessors. The default value for DBCS is NO.

CHARNAME

specifies the CCSID values (CCSIDSBCS, CCSIDMIXED, and CCSIDGRAPHIC)

used by the application requester, and determines how to fold characters from

lowercase to uppercase. Its value must be a valid character name, such as those

found in the CHARNAME column of the SYSTEM.SYSCCSIDS table.

CHARNAME is supported for the DRDA and AUTO PROTOCOL options. The

DB2 Server for VM product is shipped with CHARNAME of the user machine

initially set to INTERNATIONAL.

The SQLPREP EXEC, the DBS Utility, and the Resource Adapter use the

CHARNAME value for folding support in both single user mode and multiple

user mode. See the DB2 Server for VSE & VM Application Programming manual

for information on the SQLPREP EXEC.

DATEformat

specifies the date format. The default is ISO. This parameter is for information

only. It represents the date format in which the user wants to see date values

returned.

TIMEformat

specifies the time format. The default is ISO. This parameter is for information

only. It represents the time format in which the user wants to see time values

returned.

LDATELEN

gives the length of the local date format. The values may range from 10 to 254,

and 0. The default is 0, which indicates that LOCAL format is not available.

LTIMELEN

gives the length of the local time format. The values may range from 8 to 254,

and 0. The default is 0, which indicates that LOCAL format is not available.

WorkUnit

specifies whether CMS Work Unit support is to be used for an application. The

default is Yes. CMS Work Units are supported in VM environments only.

Appendix B. CMS EXECs

255

TraceRA

specifies the parts of the Resource Adapter (RA) that are to be traced and the

level of the trace. The positional digits correspond to the following Resource

Adapter subcomponents and functions:

v RA control flow

v Communications.

When 0 is specified, tracing is turned off. When 1 is specified, tracing is done

in limited detail. When 2 is specified, tracing is done in greater detail. The

default value for TRACERA is 00.

The following CMS FILEDEF command was entered to define the default trace

output file:

FILEDEF ARITRAC TAP2 SL (BLKSIZE 4096 NOCHANGE PERM

You can enter a different CMS FILEDEF command to override the default. The

options you specify on your CMS FILEDEF command will not be overridden

unless you reenter a CMS FILEDEF command to change them.

Note: The trace output, requested using the TRACERA, TRACEDRRM, and

TRACECONV parameters is stored in a single file.

TraceDRRM

specifies the parts of the DRRM component that are to be traced and the level

of the trace. The positional digits correspond to the following DRRM

subcomponents and functions:

v Parser

v Generator

v Dictionary

v RDIIN Manager.

When 0 is specified, tracing is turned off. When 1 is specified, tracing is done

in limited detail. When 2 is specified, tracing is done in greater detail. The

default value for TRACEDRRM is 0000.

The following CMS FILEDEF command was entered to define the default trace

output file:

FILEDEF ARITRAC TAP2 SL (BLKSIZE 4096 NOCHANGE PERM

You can enter a different CMS FILEDEF command to override the default. The

options you specify on your CMS FILEDEF command will not be overridden

unless you reenter a CMS FILEDEF command to change them.

Notes:

1. The TRACEDRRM parameter is ignored when the SQLDS protocol is used

for the connection.

2. The trace output, requested using the TRACERA, TRACEDRRM, and

TRACECONV parameters is stored in a single file.

TraceCONV

specifies that the data conversion component is to be traced and the level of

the trace.

When 0 is specified, tracing is turned off. When 1 is specified, tracing is done

in limited detail. When 2 is specified, tracing is done in greater detail. The

default value for TRACECONV is 0.

The following CMS FILEDEF command was entered to define the default trace

output file:

256

Database Administration

FILEDEF ARITRAC TAP2 SL (BLKSIZE 4096 NOCHANGE PERM

You can enter a different CMS FILEDEF command to override the default. The

options you specify on your CMS FILEDEF command will not be overridden

unless you reenter a CMS FILEDEF command to change them.

Note: The trace output, requested using the TRACERA, TRACEDRRM, and

TRACECONV parameters is stored in a single file.

SSSNAME

specifies the name of the status shared segment. This parameter is optional,

and is intended for use with products such as the IBM SystemView

Information Warehouse DataHub Support/VM software. For more information

on defining the status shared segment for the DB2 Server for VM system, refer

to the DB2 Server for VM System Administration manual.

STack

places all values currently set for the parameters of the SQLGLOB EXEC onto

the CMS stack in the same sequence as shown for QUERY.

QueRY

displays all values currently set for the parameters of the SQLGLOB EXEC. It

also displays the resource adapter code release level and CCSID values. The

following is sample output from an SQLGLOB QUERY.

ARI0717I Start SQLGLOB EXEC: 05/29/92 10:27:36 EDT.

DBNAME=SQLDBA

DBCS=NO

SYNCHRONOUS=NO

DATEFORMAT=ISO

TIMEFORMAT=ISO

TRACERA=00

LDATELEN=0

LTIMELEN=0

RELEASE=3.3.0

WORKUNIT=NO

QRYBLKSIZE=8

PROTOCOL=SQLDS

CHARNAME=INTERNATIONAL

CCSIDSBCS=500

CCSIDMIXED=0

CCSIDGRAPHIC=0

TRACEDRRM=0000

TRACECONV=0

SSSNAME=

ARI0796I End SQLGLOB EXEC: 05/29/92 10:27:36 EDT

RESET

resets all values currently set for the parameters of the SQLGLOB EXEC. The

next time the SQLGLOB EXEC is run, the defaults are used.

SQLCIREO EXEC

This EXEC reorganizes the indexes on the catalog tables. The following diagram

shows the format of the SQLCIREO EXEC.

►► SQLCIREO Dbname

( dbname

)

►◄

dcssID

( ssid

)

PARM

( parameter

)

Figure 62. SQLCIREO EXEC

Appendix B. CMS EXECs

257

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