The PUBLIC.SYS0001 dbspace is reserved for the catalog tables during database generation, and cannot be redefined. You establish its size (and storage pool) when you generate the database. The size should be large enough to hold all of your database catalog information for the life of the database.
| Note: | Physical space is not actually consumed until it is required. Consequently, you can define the SYS0001 dbspace to be very large without penalty. Be generous. The penalty for defining the SYS0001 dbspace too small is that, when it has no more usable space, you must completely regenerate the database. This can be a considerable task for a production database. For more information, see Preparing for Database Regeneration. |
The formula shown in Figure 7 should provide ample storage space for most uses of the database manager. The formula was derived based on a set of assumptions that may not be valid for your database. Review the assumptions and modify the general formula if the assumptions do not accurately represent your planned usage of the database manager.
The following sections describe:
You should review these assumptions to determine whether they apply for your planned usage of the database manager. If they do not, you should modify the assumptions (and the resulting formula) to more accurately represent your planned usage.
This formula is described in Formula for SYS0001 Storage Estimating.
These examples show how to use the SYS0001 storage estimating formula based on three example situations.
This section provides the formulas used to derive the general formula. You can modify the general formula if you want to change some of the assumptions made in deriving the general formula.
The general formula for SYS0001 storage estimating was derived based on:
Table 43 shows the length of the fixed portions of catalog rows, the
maximum stored row length for each catalog table, and an average row length
for each of the catalog tables. The average row length is the length
assumed in developing the general formula for estimating catalog storage space
requirements.
Table 43. Stored Lengths of Catalog Rows
| Catalog Table |
Minimum Length |
Maximum Length |
Estimated Average Length |
|---|---|---|---|
| SYSACCESS | 46 | 90 | 52 |
| SYSCATALOG | 64 | 385 | 170 |
| SYSCCSIDS | 39 | 39 | 39 |
| SYSCHARSETS | 393 | 411 | 400 |
| SYSCOLAUTH | 46 | 82 | 72 |
| SYSCOLSTATS | 27 | 123 | 59 |
| SYSCOLUMNS | 54 | 398 | 156 |
| SYSDBSPACES | 40 | 58 | 46 |
| SYSDROP | 13 | 13 | 13 |
| SYSINDEXES | 62 | 232 | 131 |
| SYSKEYCOLS | 55 | 91 | 67 |
| SYSKEYS | 77 | 113 | 89 |
| SYSOPTIONS | 11 | 301 | 100 |
| SYSPARMS | 82 | 82 | 82 |
| SYSPROGAUTH | 46 | 54 | 49 |
| SYSPSERVERS | 11 | 281 | 60 |
| SYSROUTINES | 58 | 581 | 170 |
| SYSSTRINGS | 286 | 286 | 286 |
| SYSSYNONYMS | 26 | 62 | 36 |
| SYSTABAUTH | 57 | 101 | 84 |
| SYSUSAGE | 36 | 72 | 51 |
| SYSUSERAUTH | 35 | 35 | 35 |
| SYSVIEWS | 20 | 293 | 200 |
In Table 43, the minimum and maximum row lengths for each catalog table
are determined using the description of the catalog tables in the DB2 Server for VSE & VM Database Administration manual. The length of a row depends on the data type of each
column in the catalog table. The minimum length for each column is
found using these values for each data type.
Table 44. Minimum Column Length
| Data Type | Value |
|---|---|
| DBAINT | 4 |
| DBAHW | 2 |
| INTEGER | 4 |
| SMALLINT | 2 |
| CHAR(n) | n |
| TIMESTAMP | 10 |
| VARCHAR(n) | 1 |
| Note: | The data types DBAINT and DBAHW are used internally by the database manager. Externally, they look like the data types INTEGER and SMALLINT. |
For CHAR columns, the length is the column length (n). The column lengths are added. For each column that can contain nulls, 1 is added to this figure. The value 8 is then added to this total for catalog table overhead. The resulting number is the minimum row length for the catalog table.
The maximum length for each column is found using these values:
Table 45. Maximum Column Length
| Data Type | Value |
|---|---|
| DBAINT | 4 |
| DBAHW | 2 |
| INTEGER | 4 |
| SMALLINT | 2 |
| CHAR(n) | n |
| TIMESTAMP | 10 |
| VARCHAR(n) | n + 1 |
For CHAR columns, the length is the column length (n). For VARCHAR columns, the length is the maximum column length plus one (n + 1). For each column that can contain nulls, 1 is added to this figure. The value 8 is then added to this total for catalog table overhead. The resulting number is the maximum row length for the catalog table.
The average length for each column is calculated this way for most catalog tables:
(maximum length - minimum length)
3 + minimum length
This produces a number one third of the way between the minimum and maximum lengths. In some situations, higher values are used because those columns are typically longer. An example is the SYSTEM.SYSVIEWS catalog table, where the VIEWTEXT column contains the command used to create the view. Because these commands are usually over 100 bytes long, a number one third of the way between the minimum and maximum lengths of the column would be too low. In this situation, the number 200 is chosen arbitrarily.
If you make your own estimates of catalog table row lengths (using the chart provided in Table 50), you should choose values for the average row lengths that are accurate for your database. Otherwise, you could underestimate the size of the SYS0001 dbspace. In particular, you should not underestimate the average length of rows in the SYSTEM.SYSCOLUMNS catalog table. If you use the REMARKS or CLABEL columns of this catalog table, your average row length could be far greater than the number (156) given in Table 43. Because the SYSTEM.SYSCOLUMNS table can become quite large (it has a row for every column in every table in the database), its size is a major factor in the size of the SYS0001 dbspace.
The average number of rows for each catalog table was determined based on
the assumptions in Table 46. These assumptions were used in generating the
general formula for SYS0001.
Table 46. Assumptions of Catalog Bytes/Pages for Each Object
| Object | Catalog Entries | Bytes | Bytes for Each Object | Pages for Each Object |
|---|---|---|---|---|
| Table |
1 SYSCATALOG 1 SYSTABAUTH 2 SYSINDEXES |
169 84 262 | 515 | 0.13 |
| View |
1 SYSCATALOG 1 SYSVIEWS 2 SYSTABAUTH 2 SYSUSAGE |
169 200 168 102 | 639 | 0.16 |
| Column | 1 SYSCOLUMNS | 156 | 156 | 0.04 |
| Package |
1 SYSPROGAUTH 15 SYSUSAGE |
49 765 | 814 | 0.20 |
| Dbspace (including package dbspaces) | 1 SYSDBSPACES | 46 | 46 | 0.01 |
| User |
1 SYSUSERAUTH 50 SYSTABAUTH 50 SYSSYNONYMS 150 SYSCOLAUTH |
35 4,200 1,800 10,800 | 16,835 | 4.11 |
| Package dbspaces | 255 SYSACCESS | 13,260 | 13,260 | 3.24 |
| Character Set | 1 SYSCHARSETS | 400 | 400 | 0.10 |
| Keys |
1 SYSKEYS 2 SYSKEYCOLS |
89 134 | 223 | 0.05 |
| Other | 15 SYSOPTIONS | 1200 | 1200 | 0.30 |
When a table is created, one entry is made in the SYSTEM.SYSCATALOG table and one in the SYSTEM.SYSTABAUTH table. This formula assumes an average of two indexes for each table. For each index created, one entry is made in SYSTEM.SYSINDEXES.
When a view is created, one entry is made in SYSTEM.SYSCATALOG. In addition, as many as 32 entries are made in SYSTEM.SYSVIEWS. With the assumption that the average view definition is less than 254 bytes, only one row is required. One entry is also made in the SYSTEM.SYSTABAUTH and SYSTEM.SYSUSAGE tables for each table on which the view is defined. The general formula assumes that, on average, a view is defined on two tables.
One entry is made in SYSTEM.SYSCOLUMNS for every table and view column.
When a package is created, one entry is made in SYSTEM.SYSPROGAUTH. In addition, entries are made in SYSTEM.SYSUSAGE for every table, view, index, and dbspace used by the package. (A package uses a dbspace if it uses a table in the dbspace.)
The general formula assumes 15 such entries in SYSTEM.SYSUSAGE.
One entry is made in SYSTEM.SYSDBSPACES for each dbspace added to the database, including package dbspaces.
One entry is placed in SYSTEM.SYSUSERAUTH for each user of the database. Each user is assumed to have access to an average of 50 tables (and views) belonging to other users. This explains the 50 entries in SYSTEM.SYSTABAUTH and SYSTEM.SYSSYNONYMS. Specific column update authorization is assumed to average about 3 columns for each table (or view) that is shared (3 for each of the 50 tables or views). This yields an estimate of 150 entries in SYSTEM.SYSCOLAUTH for each user.
For each package dbspace added, one entry is made in SYSTEM.SYSDBSPACES, which was accounted for earlier, and 255 entries are made in SYSTEM.SYSACCESS. The 255 entries are made because all 255 packages are preallocated in the dbspace, even though they can all be empty.
For each character set you define, you must load one row into SYSTEM.SYSCHARSETS.
For each key, one row is added to SYSTEM.SYSKEYS, and two rows are added to SYSTEM.SYSKEYCOLS (assuming that each key is made up of two columns).
Finally, three rows exist in SYSTEM.SYSOPTIONS for every database.
The assumptions in the preceding section provide a means of estimating the data pages required in SYS0001. Assuming the PCTFREE value for the SYS0001 dbspace is 0, the SYS0001 data pages are:
SYS0001 data pages = .13 x the number of tables
+ .16 x the number of views
+ .04 x the number of columns
+ .20 x the number of packages
+ .01 x the number of dbspaces
(including package dbspaces)
+ 4.11 x the number of users
+ 3.24 x the number of package dbspaces
+ .10 x the number of character sets
+ .05 x the number of keys
+ .30 (for the SYSTEM.SYSOPTIONS table)
To get the total number of SYS0001 dbspace pages, you must add the header pages and the index pages. SYS0001 has eight header pages.
The initial set of catalog entries generated by the database generation process fills 4 pages. The PCTINDX value for SYS0001 is 60. Thus, to get the total number of pages you must add 12 and divide by 0.4:
SYS0001 pages = ( 12 + SYS0001 data pages ) / 0.40
The SYS0001 data pages is your estimate for the number of data pages for your catalog entries.
When the adjustments described in Derivation of the General Formula for SYS0001 Storage Estimating are made, the formula for the total number of SYS0001 dbspace pages becomes:
SYS0001 pages = 30 + .33 x the number of tables
+ .40 x the number of views
+ .10 x the number of columns
+ .50 x the number of packages
+ .03 x the number of dbspaces
(including package dbspaces)
+ 10.28 x the number of users
+ 8.10 x the number of package dbspaces
+ .25 x the number of character sets
+ .13 x the number of keys
( + .74 (for the SYSTEM.SYSOPTIONS table) )
This number should be rounded up to the next higher multiple of 128. Because the number of pages needed for the SYSTEM.SYSOPTIONS catalog table is so small, the number is omitted from the general formula and any further calculations.
The following examples illustrate the use of the general formula for estimating the required dbspace size for SYS0001.
Table 47 illustrates the estimate for a small set of catalog tables
that can be used in generating a test database.
Table 47. Example of Estimating the Catalog Dbspace for a Test Database
| Example Number of Objects | Number of Pages Calculation | Number of Pages |
|---|---|---|
| Reserved | 30 | 30 |
| 50 tables | .33 X 50 | 17 |
| 100 views | .40 X 100 | 40 |
| 1500 columns | .10 X 1 500 | 150 |
| 25 packages | .50 X 25 | 13 |
| 50 dbspaces | .03 X 50 | 2 |
| 15 users | 10.28 X 15 | 154 |
| 1 package dbspace | 8.10 X 1 | 8 |
| 2 character sets | .25 X 2 | 1 |
| 20 keys | .13 X 20 | 3 |
| Total number of SYS0001 pages = 418 | ||
| Rounded to the next higher multiple of 128 is: 512 | ||
Table 48 illustrates the estimate for a medium sized set of catalog
tables that might be used in generating a test database to support development
of multiple application systems. The number of package dbspaces needed
was determined by adding the number of views to the number of packages and
dividing the sum by 255. The maximum number of packages that can be
defined in a package dbspace is 255. This number could be reduced if
the packages are large. The maximum 255 packages may not fit in the
allocated pages for the dbspace.
Table 48. Example of Estimating the Catalog Dbspace for an Application Development Database
| Example Number of Objects | Number of Pages Calculation | Number of Pages |
|---|---|---|
| Reserved | 30 | 30 |
| 500 tables | .33 X 500 | 165 |
| 1000 views | .40 X 1000 | 400 |
| 15,000 columns | .10 X 15,000 | 1,500 |
| 50 packages | .50 X 50 | 25 |
| 500 dbspaces | .03 X 500 | 15 |
| 25 users | 10.28 X 25 | 257 |
| 5 package dbspaces | 8.10 X 5 | 40 |
| 6 character sets | .25 X 6 | 2 |
| 200 keys | .13 X 200 | 26 |
| Total number of SYS0001 pages = 2461 | ||
| Rounded to the next higher multiple of 128 is: 2560 | ||
Table 49 illustrates the estimate for a large sized set of catalog
tables that could be used to support a production database.
Table 49. Example of Estimating the Catalog Dbspace for a Production Database
| Example Number of Objects | Number of Pages Calculation | Number of Pages |
|---|---|---|
| Reserved | 30 | 30 |
| 3000 tables | .33 X 3000 | 990 |
| 5000 views | .40 X 5000 | 2000 |
| 75,000 columns | .10 X 75,000 | 7500 |
| 250 packages | .50 X 250 | 125 |
| 500 dbspaces | .03 X 500 | 15 |
| 50 users | 10.28 X 50 | 514 |
| 21 package dbspaces | 8.10 X 21 | 170 |
| 6 character sets | .25 X 6 | 2 |
| 1,200 keys | .13 X 1 200 | 156 |
| Total number of SYS0001 pages = 11,502 | ||
| Rounded to the next higher multiple of 128 is: 11,520 | ||
Table 50 and Table 51 assist you if you want to modify any of the
assumptions used in deriving the general formula. If you have generated
the starter database, you should compare the data in the catalog tables
against the assumptions made here. You can do so by issuing UPDATE
STATISTICS for each of the catalog tables after you have used the starter
database. Queries against SYSTEM.SYSCATALOG give you the
statistics for comparison.
Table 50. Your Estimated Stored Lengths of Catalog Rows
| Catalog Table |
Minimum Length |
Maximum Length |
Estimated Average Length |
|---|---|---|---|
| SYSACCESS | 46 | 64 |
|
| SYSCATALOG | 64 | 384 |
|
| SYSCCSIDS | 39 | 39 | 39 |
| SYSCHARSETS | 393 | 411 |
|
| SYSCOLAUTH | 46 | 82 |
|
| SYSCOLSTATS | 27 | 123 |
|
| SYSCOLUMNS | 56 | 400 |
|
| SYSDBSPACES | 40 | 58 |
|
| SYSDROP | 13 | 13 | 13 |
| SYSINDEXES | 62 | 232 |
|
| SYSKEYCOLS | 55 | 91 |
|
| SYSKEYS | 77 | 113 |
|
| SYSOPTIONS | 13 | 303 |
|
| SYSPARMS | 82 | 82 |
|
| SYSPROGAUTH | 46 | 54 |
|
| SYSPSERVERS | 11 | 281 |
|
| SYSROUTINES | 58 | 581 |
|
| SYSSTRINGS | 286 | 286 | 286 |
| SYSSYNONYMS | 26 | 62 |
|
| SYSTABAUTH | 57 | 101 |
|
| SYSUSAGE | 36 | 72 |
|
| SYSUSERAUTH | 35 | 35 | 35 |
| SYSVIEWS | 20 | 292 |
|
Table 51. Your Assumptions of Catalog Bytes or Pages for Each Object
| Object | Catalog Entries | Bytes | Bytes for Each Object | Pages for Each Object |
|---|---|---|---|---|
| Table |
1 SYSCATALOG 1 SYSTABAUTH __ SYSINDEXES |
___ ___ ___ |
|
|
| View |
1 SYSCATALOG 1 SYSVIEWS __ SYSTABAUTH __ SYSUSAGE |
___ ___ ___ ___ |
|
|
| Column | 1 SYSCOLUMNS |
___ |
|
|
| Package |
1 SYSPROGAUTH ___ SYSUSAGE |
___ ___ |
|
|
|
Dbspace (including package dbspaces) | 1 SYSDBSPACES |
___ |
|
|
| User |
1 SYSUSERAUTH ___ SYSTABAUTH ___ SYSSYNONYMS ____ SYSCOLAUTH |
35 ___ ___ ___ |
|
|
|
Package dbspaces | 255 SYSACCESS |
___ |
|
|
|
Character Set | 1 SYSCHARSETS |
___ |
|
|
| Keys |
1 SYSKEYS ___ SYSKEYCOLS |
___ ___ | ||
| Other | 3 SYSOPTIONS |
___ |
|
|