The ANALYZE_LOG_SPACE procedure returns the log space analysis
results for each of the database partitions of the given database
partition group.
Syntax
>>-ANALYZE_LOG_SPACE--(--inDBPGroup--,--inMainTbSchema--,------->
>--inMainTable--,--analysisType--,--inStmgTime --,-------------->
>--addDropOption --,--addDropList --,--pNumber --,-------------->
>--pWeight --)-------------------------------------------------><
The schema is SYSPROC.
Procedure parameters
- inDBPGroup
- An input argument of type VARCHAR (128) that specifies the database
partition group name.
- inMainTbSchema
- An input argument of type VARCHAR (128) that specifies the schema
of the main table
- inMainTable
- An input argument of type VARCHAR (128) that specifies the main
table within the database partition group, usually the largest table
in the database partition group.
- analysisType
- An input argument of type SMALLINT that specifies an indicator
for analysis type:
- SWRD_USE_STMG_TABLE (1): indicates that the information in the
storage management tables is used to find the table row count per
database partition. This type should only be used if the storage management
tables are setup, and at least one storage snapshot has been taken
for the database partition group that is to be redistributed.
- SWRD_USE_REALTIME_ANALYSIS (2): indicates that a SELECT query
is used to find the table row count per database partition.
- inStmgTime
- An input argument of type VARCHAR (26) that specifies the timestamp
for the storage management record. This parameter is ignored when analysisType is set to SWRD_USE_REALTIME_ANALYSIS.
- addDropOption
- An input argument of type CHAR (1) that specifies database partitions
are being added or dropped:
- 'A': Adding database partitions.
- 'D': Dropping database partitions.
- 'N': No adding or dropping.
- addDropList
- An input argument of type VARCHAR (6000) that specifies the database
partitions to be added or dropped. This database partition numbers
are specified in a comma-separated string format and no spaces are
allowed in the string.
- pNumber
- An input argument of type VARCHAR (6000) that specifies all the
database partition numbers corresponding to the database partition
weight. Each database partition number is between 0 and 999, and the
database partition numbers are specified in a comma-separated string
with no spaces in the string.
- pWeight
- An input argument of type VARCHAR (6000) that specifies all the
database partition weights that the user has specified corresponding
to the database partition numbers in the pNumber string. Each database partition weight is a number between
0 and 32767, and database partition weights are specified in a comma-separated
string with no spaces in the string.
Authorization
- SYSADM, SYSMON, SYSCTRL, or SYSMAINT
- EXECUTE privilege on the ANALYZE_LOG_SPACE procedure
Default PUBLIC privilege
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC
when the procedure is automatically created.
Example
Analyze the effect of adding a database
partition without applying the changes. In the following case, the
hypothesis is adding database partition 40, 50 and 60 to the database
partition group, and for database partitions 10,20,30,40,50,60, using
a target ratio of 1:2:1:2:1:2. Note that in this example,
only partitions 10, 20 and 30 actually exist in the database partition
group
CALL SYSPROC.ANALYZE_LOG_SPACE('IBMDEFAULTGROUP', 'TEST',
'EMP', 2, ' ', 'A', '40,50,60', '10,20,30,40,50,60',
'1,2,1,2,1,2')
Analyze the effect of dropping
a database partition without applying the changes. In the following
case, the hypothesis is dropping database partition 30 from the database
partition group, and redistributing the data in database partitions
10 and 20 using a target ratio of 1 : 1. Note that in this
example, all database partitions 10, 20 and 30 should exist in the
database partition group
CALL SYSPROC.ANALYZE_LOG_SPACE('IBMDEFAULTGROUP', 'TEST',
'EMP', 2, ' ', 'D', '30', '10,20','1,1')
Usage notes
"-1" is used as an output
value for parameters when their values cannot be obtained.
The redistribute stored procedures and functions work only in partitioned
database environments, where a distribution key has been defined
for each table.
Information returned
The ANALYZE_LOG_SPACE
procedure returns a result set (an open cursor) of the log space analysis
results, containing the following fields for each of the database
partitions of the given database partition group.
Table 1. Information returned by the ANALYZE_LOG_SPACE procedureColumn name |
Column type |
Description |
PARTITION_NUM |
SMALLINT |
The database partition number of the log space analysis. |
TOTAL_LOG_SIZE |
BIGINT |
Total log space allocated in bytes, -1 indicates unlimited
size. |
AVAIL_LOG_SPACE |
BIGINT |
The amount of log space in bytes that is free and can be used
by the redistribute process. |
DATA_SKEW |
BIGINT |
The absolute value in bytes of the size of data which is deviated
from the target level. |
REQ_LOG_SPACE |
BIGINT |
The amount of space in bytes required to reach the wanted
data distribution. |
NUM_OF_STEPS |
SMALLINT |
The number of steps needed to reduce the data skew to zero. |
MAX_STEP_SIZE |
BIGINT |
The maximum amount of data in bytes that can be moved at a
time, without causing a log full error. |