DB2 Server for VM: System Administration

Estimating ISQL Dbspace Requirements

An allocation of 1 024 pages should be sufficient for most ISQL users. If you have many users or expect to make extensive use of the ISQL stored queries facility, consider increasing this allocation.

The recommended size (in pages) for the PUBLIC.ISQL dbspace is 1024 or .88 x the number of stored queries, whichever is larger.

Estimating Dbspace Sizes for Routines

The size of ROUTINE tables can vary greatly from user to user and from installation to installation. You can place the ROUTINE tables for all users in the same public dbspace, or you can place the ROUTINE table for each user in that user's private dbspace.

The following formulas are condensed versions of size estimation formulas in the DB2 Server for VSE & VM Database Administration manual. They simplify the work required to estimate the size of the dbspace needed to hold routines. The following assumptions have been used in the formulas:

The PCTFREE value is 15.
The PCTINDEX value is 33.
ALLOWANCE was not included in the formula.

The following formula can be used to calculate the average row length for the routines:

AVGROWLEN = 23 + average command line length
               + average remark length

Note: The average command line length is not the same as the average command length. A command can be entered on multiple command lines. A command line in a routine has a maximum length of 254 characters. A command has a maximum length of 2048 characters. Be sure to use the command line length in your estimate.

The following formula can be used to calculate the number of dbspace pages required for your ROUTINE tables:

                   number         average           average
                     of     x     number of   x     number
                   users          routines          of lines
 
Dbspace pages =    (2074 / AVGROWLEN) (from previous formula)

Examples:

Table 55. Example of Estimating the Number of Dbspace Pages for a Routine

Number of Users Number of Routines For Each User Number of Lines For Each Routine Row Length Dbspace Pages
1 20 20 80 16
20 20 20 80 309
50 60 35 75 3797

Number of Users	Number of Routines For Each User	Number of Lines For Each Routine	Row Length	Dbspace Pages
1	20	20	80	16
20	20	20	80	309
50	60	35	75	3797

Estimating Dbspace Size for Stored SQL Statements (Stored Queries)

The following assumptions are used in the formula for calculating the size of the dbspace required for stored SQL statements:

The PCTFREE value is 15.
The PCTINDEX value is 33.
ALLOWANCE is not included in the formula.
One page was included for one routine named PROFILE. It can contain up to 25 lines with an average row length of 80.

The following formula can be used to calculate the number of dbspace pages needed for stored SQL statements:

   Dbspace pages = 1 + (.037 x number of statements) +
 
   ((Truncate [(avglen + 250) / 250 x 250)]  x number of statements
   ---------------------------------------------------------------
                     2667

When calculating the average length of your stored queries, you must include the FORMAT information for all SELECT statements. The length of the FORMAT information can be calculated by the following formula:

Format length = 504 + (number of columns x 44)
 
                or
 
                2048, whichever is smaller

The following examples show the number of dbspace pages required for each user for the two types of stored SQL statements. The two types are:

SQL SELECT statements (true stored queries)
Other SQL statements.

The examples in Table 56 and Table 57 show the number of dbspace pages required for one user for 10 stored SQL statements. If a user has some of each type of stored SQL statement, you must add the values from each table as needed.

Table 56. Examples -- Dbspace Pages for Each User for Stored SELECT Statements

Number of Selects Number of Columns Length of Select Format Length Adjusted Length¹ Dbspace Pages for Each User
10 10 70 944 1250 5.88
10 20 70 1384 1500 5.99
10 10 400 944 1500 5.99
10 20 400 1384 2000 7.87
10 40 400 2048 2500 9.38
10 46 2048 2048 4250 16.30

Number of Selects	Number of Columns	Length of Select	Format Length	Adjusted Length¹	Dbspace Pages for Each User
10	10	70	944	1250	5.88
10	20	70	1384	1500	5.99
10	10	400	944	1500	5.99
10	20	400	1384	2000	7.87
10	40	400	2048	2500	9.38
10	46	2048	2048	4250	16.30

¹ The adjusted length is the stored data length of the stored SQL statements. For more information on adjusted lengths columns, see the DB2 Server for VSE & VM Database Administration manual.

Table 57. Examples -- Dbspace Pages for Each User for Stored SQL Statements Other than SELECTs

Number of Commands Average Length Adjusted Length¹ Pages for Each User
10 70 250 1.31
10 400 500 2.25
10 999 1 000 4.12
10 1 499 1500 6.00
10 2 048 2 250 8.81

Number of Commands	Average Length	Adjusted Length¹	Pages for Each User
10	70	250	1.31
10	400	500	2.25
10	999	1 000	4.12
10	1 499	1500	6.00
10	2 048	2 250	8.81

¹The adjusted length is the stored data length of the stored SQL statements. For more information on adjusted lengths columns, see the DB2 Server for VSE & VM Database Administration manual.

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