DB2 Server for VM: System Administration

Initial Virtual Storage Requirements of Components

The virtual storage requirements for the database manager are its sum of the static and dynamic storage requirements. Static storage is used for the database manager code, database I/O control blocks, the page and directory buffers, and other reasons. Dynamic storage is used for the preprocessing and execution of SQL statements.

Table 37 and Table 38 show the static storage required in a database machine to start the application server.

The storage required for these purposes can vary greatly, depending on the complexity of the SQL statements that, the size of the package, and the number of packages in virtual storage.

The minimum dynamic storage required is NCUSERS x 130000. If the DRDA code is installed, it is NCUSERS x 180000.

Table 37. Initial Storage Requirements of DB2 Server for VM Database Machines

Recommended Minimums for
Formula/Constraints Single-User Mode Multiple-User Mode

DB2 Server for VM Code ^{1, 14}
Message Repository ¹⁴

DB2 Server for VM Control Blocks
* Base
* Lock Request Blocks
NLRBS x 24 ^{2, 3, 5}
* Concurrent Scans Storage
NCSCANS x 50 ^{2, 3} x (NCUSERS+3) ^{2, 6}
* Miscellaneous LRBs
8 x (First Prime Number > (NLRBS/4))
* Static Agent Storage below 16M
NCUSERS x 57500 ¹⁴
* Static Agent Storage anywhere
NCUSERS x 50000
* Working Storage Area ¹⁴
* Working Storage Queue Headers ^{2, 3}
32 + (2 + 2 x (NCUSERS + 3) + NPACKAGE x
(NCUSERS + 1)) x 68
* Package Control Blocks
NCUSERS x NPACKAGE x 72 bytes ^{2, 3}
* Communications Control Blocks
MAXCONN x 336 ^{7, 13}
* If TCP/IP Communications is used, add:
(NCUSERS+3) x 264+2000 ¹⁶
* Accounting Control Blocks
(MAXCONN/51+1) x 4096 ⁷

* Stored Procedure Caches
(76 * the number of stored procedure servers)
+(32 * the number of stored procedure server groups)
+(for each procedure,
116
+the length of the LE options string (max 254)
+ (68 * the number of parameters) )

DB2 Server for VM Buffers
* 4 x Max of ((the 1st power of 2 that is >=
(NPAGBUF/8)) or 256) + NPAGBUF x 4144 ^{2, 3, 10}
* 4 x Max of ((the 1st power of 2 that is >=
(NDIRBUF/8)) or 256) + NDIRBUF x 560 ^{2, 3, 11}

Database Control Blocks
* Base control blocks
* MAXDBSPC x 8 ¹²
* MAXPOOLS x 18 ¹²
* MAXEXTNT x 6 ¹²

Database I/O Control Blocks
* NCUSERS x 216 x # of minidisks ⁴
* Minimum of (64 or NPAGBUF/10) x (NCUSERS+3) x 16 ¹⁵

3324000
250000

140000

12288

4500

1048

57500

50000
109000

848

72

21168

0

9156

0

59040

8864

88000
8000
576
384

2808
336
---------------
4147588 ^{8, 9}

3077000
250000

140000

60480

12000

5048

287500

250000
109000

5336

3600

21168

0

9156

0

125344

17824

88000
8000
576
384

14040
384
---------------
4484840 ^{8, 9}

Note: The storage requirements above do not include the virtual storage used by the user program or CMS. (Even if CMS is defined as a saved system, CMS still uses some storage in the user's virtual machine.)
The Stored Procedure example assumes that no stored procedures or stored procedure servers have been defined.

Note:

The above numbers are in bytes.
¹ If the DRDA code is installed on the application server, an additional 1.8 megabytes is required (this applies to both multiple-user mode and single-user mode); if it is installed on the application requester, an additional 1.8 megabytes is required for single-user mode.
² The numbers for multiple-user mode assume the following defaults: NCUSERS (5), NPACKAGE (10), NCSCANS (30), NLRBS (2520), NLRBU (1000), NPAGBUF(30), NDIRBUF(30), PROTOCOL=SQLDS and TCPPORT not equal to zero.
³ The numbers for single-user mode assume the following defaults: NCUSERS (1), NPACKAGE (1), NCSCANS (30), NLRBS (512), NLRBU (1000), NPAGBUF(14), NDIRBUF(14) and TCPPORT is not applicable.
⁴ The number of minidisks = 13 (10 dbextents + 1 directory minidisk + 2 logs for dual logging)
⁵ The calculation for NLRBS is: NCUSERS x 2 + (NCUSERS x NLRBU)/2 + 10.
⁶ In single-user mode, NCSCANS x 50 x 3.
⁷ Assume VM MAXCONN is 63 (13 for minidisks and 50 for user connections).
⁸ If you have coded an accounting exit, add its size to the total virtual storage requirements for both single and multiple-user mode. If you have coded any field procedures, add their total size to the requirements for both single and multiple-user mode. In addition, add NCUSERS times the maximum working storage used by any of your field procedures. For more information, see Field Procedures.
⁹ Add the dynamic storage requirement of active agents.
¹⁰ The calculation for the default NPAGBUF is: 10 + 4 x NCUSERS
¹¹ The calculation for the default NDIRBUF is: 10 + 4 x NCUSERS
¹² The recommended minimum assumes the defaults for MAXDBSPC (1000), MAXPOOLS (32), and MAXEXTNT (64).
¹³ If using PROTOCOL=AUTO, the Communications Control Blocks calculation is: MAXCONN x 552.
¹⁴ The following always resides below 16 megabytes: 1.2 megabytes of the DB2 Server for VM Code, the Message Repository, the Static Agent Storage below 16 megabytes, and the Working Storage Area.
¹⁵ In single-user mode, minimum of (256 or NPAGBUF/2) x 48.
¹⁶ TCP/IP communications is only used in multiple-user mode, so the single-user mode value is always zero.

User Machine Components

Table 38 shows the virtual storage requirements in a user machine using the base code only. A 6MB machine size is recommended.

Table 38. Virtual Storage Requirements of Components in a User Machine

Resource adapter 277 (Used for any DB2 Server for VM facility or user program)

ISQL 597 (320 for ISQL + 277 for the resource adapter)

DBS utility 497 (220 for the DBS utility + 277 for the resource adapter)

Any DB2 Server for VM Preprocessor 511 (234 for the preprocessor + 277 for the resource adapter)

Note: The storage requirements above do not include the virtual storage used by the user program or CMS. (Even if CMS is defined as a saved system, CMS still uses some storage in the user's virtual machine.)

Notes:

All numbers are in kilobytes.
The resource adapter uses a minimum of 277 kilobytes of virtual storage. If the DRDA code is installed in the user machine, an extra 2048 kilobytes are required.
These figures assume that national language message repositories are in saved segments. For each one not in a saved segment, add 250 kilobytes. In addition, if you use a VM/ESA operating system, add 5 kilobytes for the second and each subsequent CMS work unit used by the application.
You should add this value to the virtual storage requirements of the SQL application to arrive at the size of the user machine.
To operate any DB2 Server for VM preprocessor in a user machine, you would need at least 511 kilobytes of free storage. To operate a preprocessor on the database machine in single-user mode you would add 234 kilobytes to the size of the machine.
If CMS is not defined as a saved system, its size must be added when calculating the storage requirements.
ISQL cannot be run in the same machine as the database manager.
For user programs, if the fetch/insert blocking option is in effect, add 8 kilobytes for each concurrently open cursor that qualifies for blocking.
If you have coded an accounting exit, a cancel exit, or a TRANSPROC, add its size to the total virtual storage requirements.

The amount of virtual storage required in the user machine for ISQL varies. The following variables should also be considered:

Use of SELECT
Use of routines or EXECs.

Use of SELECT

The row length is the only variable that affects the amount of storage needed by a SELECT result. The number of rows retrieved from a SELECT has no effect on the amount of storage required.

ISQL gets a buffer for a certain number of rows before retrieving any rows from tables. The number of rows depends on the size of the terminal screen. Specifically, ISQL gets a buffer to hold (SCREEN_HEIGHT - 4)*2 row -- that is, enough storage for two screens of rows. (Four is subtracted from the screen height because of the column header and input/status areas of the screen.) If more than two screens of rows are retrieved, this buffer is reused.

The number of columns referenced in a SELECT command in ISQL is limited to 45. ISQL uses the length of the retrieved columns to determine the buffer size not the number of column references. The storage needed is the number of rows for two screens multiplied by the length of one row. (Note that the length of the row may be larger than the length of the screen.) If the storage required for the buffer exceeds 32 kilobytes, not all selected columns are displayed, or the user receives an error message.

The SET VARCHAR and FORMAT VARCHAR ISQL commands will change the size of the row and thus the size of the buffer.

Use of Routines or EXECs

If you use ISQL routines or stack ISQL commands in an EXEC, the amount of virtual storage used in the user machine increases, because VM program stacks are used. Thus, the amount of storage required is related to the number of lines in the routines (or EXECs). For more information on program stacks, see the VM/ESA REXX/VM User's Guide manual.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]