DB2 Version 9.7 for Linux, UNIX, and Windows

ANALYZE_LOG_SPACE procedure - Retrieve log space analysis information

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

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 procedure
Column 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 required 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.