5.9.2 Determining the number of pages for base rows that are needed for storing work tables
Use the following formula to determine the number of pages for base rows that are needed for storing the work tables (WP(i)).
Formula
|
|
![[Figure]](GRAPHICS/F0000239.GIF)
Explanation of variables
- row_num
-
Number of rows to be stored in the table (rows)
- page_size
-
Page size in the work table DB area (bytes)
Substitute the value specified for the initialization option adb_init_wrk_page_size determined in 5.7 Designing a work table DB area.
The unit for the value of adb_init_wrk_page_size is kilobytes and the unit for the value of the page_size variable is bytes. Note this difference in the units.
For details about the initialization option adb_init_wrk_page_size, see adbinit (Initialize the Database) in the manual HADB Command Reference.
Use the following formula to determine the variable ROWSZ.
If the value determined for ROWSZ by this formula is greater than 32,704, add the value of 2 x (col_num + 1) to the result of the formula.
Formula
|
|
![[Figure]](GRAPHICS/F0000240.GIF)
- Explanation of variables
-
col_num: Total number of columns in the work table (columns)
col_size(i): Data length of each column (bytes)
For the variable col_size(i), determine the data length of each column from the table shown below. Then, calculate the sum total for all columns.
|
No. |
Classification |
Data type |
Data length (bytes) |
|
|---|---|---|---|---|
|
1 |
Numeric data |
INTEGER |
8 |
|
|
2 |
SMALLINT |
4 |
||
|
3 |
DECIMAL(m, n)# |
1 ≤ m ≤ 4 |
2 |
|
|
4 |
5 ≤ m ≤ 8 |
4 |
||
|
5 |
9 ≤ m ≤ 16 |
8 |
||
|
6 |
17 ≤ m ≤ 38 |
16 |
||
|
7 |
DOUBLE PRECISION |
8 |
||
|
8 |
Character string data |
CHARACTER(n) |
n |
|
|
9 |
VARCHAR(n) |
wrk_var_col_size |
||
|
10 |
Datetime data |
DATE |
4 |
|
|
11 |
TIME(p) |
3 + ↑p ÷ 2↑ |
||
|
12 |
TIMESTAMP(p) |
7 + ↑p ÷ 2↑ |
||
|
13 |
Binary data |
BINARY(n) |
n |
|
|
14 |
VARBINARY(n) |
d + 2 |
||
- Legend:
-
m, n: Positive integers
d: Actual data length
p: 0, 3, 6, 9 or 12
- #
-
Indicates a fixed-point number that has a total of m digits, with n digits following the decimal point. If m is omitted, 38 is assumed.
- wrk_var_col_size: Data length of VARCHAR columns for work tables
-
Determine the data length based on the following table:
Table 5‒20: Data lengths of VARCHAR columns for work tables No.
Definition length n (bytes)
Data length (bytes)
1
1 ≤ n ≤ 32,000
d + 2
2
32,001 ≤ n ≤ 64,000
d +8
- Legend:
-
n: Positive integer
d: Actual data length