DB2 Version 10.1 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

Read syntax diagramSkip visual syntax diagram
>>-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 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 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.