IBM – ITM 630 TDW Table Partitioning ITM v630 TDW TABLE PARTITIONING How to check Partitioning Status 1.Table of Contents 1 - Introduction............................................................................................................................2 2 - Partitioned table.....................................................................................................................2 2.1 - ITM environment variables.............................................................................................2 2.2 - ITM table partitioning naming for TDW...........................................................................3 2.2.1 - DB2 LUW and Oracle.............................................................................................3 2.2.2 - DB2 Z/OS................................................................................................................3 2.3 - Is the table partitioned with an IBM scheme ?..............................................................3 2.3.1 - DB2 LUW SQL query to detect an IBM partition scheme.......................................4 2.3.2 - Oracle SQL query to detect an IBM partition scheme.............................................5 2.3.3 - DB2 Z/os SQL query to detect an IBM partition scheme........................................5 2.4 - How many rows per partition ?.......................................................................................6 2.4.1 - DB2 LUW statistics..................................................................................................7 2.4.2 - Oracle statistics.......................................................................................................7 2.4.3 - DB2 Z/os statistics...................................................................................................7 2.5 - How many partitions ?....................................................................................................7 2.5.1 - DB2 LUW SQL query to find the number of partitions............................................7 2.5.2 - Oracle SQL query to find the number of partitions..................................................8 2.5.3 - DB2 Z/os SQL query to find the number of partitions.............................................9 2.6 - Which table space for each partition ?...........................................................................9 2.6.1 - DB2 LUW SQL query to get the table space per partition....................................10 2.6.2 - Oracle SQL query to get the table space per partition..........................................10 2.7 - Is the partitioned table compressed ?...........................................................................11 2.7.1 - DB2 LUW SQL query to find out if the partitions are compressed........................11 2.7.2 - Oracle SQL query to find out if the partitions are compressed.............................12 3 - Partitioned Index..................................................................................................................12 3.1 - Do we have a partitioned index ?.................................................................................12 3.1.1 - DB2 LUW SQL query to find out if an index is partitioned....................................12 3.1.2 - Oracle SQL query to find out if an index is partitioned.........................................13 3.1.3 - DB2 Z/os SQL query to find out if an index is partitioned.....................................14 3.2 - Is the partitioned index contained in a specified table space ?....................................14 3.2.1 - DB2 LUW SQL query to find out if an index is contained in a specific table space ..........................................................................................................................................14 3.2.2 - Oracle SQL query to find out if an index is contained in a specific table space...15 3.3 - Is the partitioned index compressed ?..........................................................................16 3.3.1 - DB2 LUW SQL query to find out if a partitioned index is compressed.................16 Catherine Cook Page 1 IBM – ITM 630 TDW Table Partitioning 3.3.2 - Oracle SQL query to find out if a partitioned index is compressed.......................17 1 Introduction ITM 6.3.0 introduces Tivoli Data Warehouse significant improvements in pruning and querying large Tivoli Data Warehouse databases by allowing to partition database tables. Range partitioning permits the fast roll out of data without having to perform a resource intensive DELETE operation on blocks of rows. Range partitioning also improves query performance when the partitioning key is part of the query clause. Performance is improved because the database can discard unnecessary partitions from the list of blocks that must be fetched from disk. ITM TDW provides configuration settings to allow the table range partitioning, the compression of the data and the possibility to provide a default container for all ITM tables and or all ITM indices. This document will describe which environment variables corresponds to each configuration settings and will also give you sample SQL queries to check the status of a database table and index for the 3 database types supported by Tivoli Data Warehouse : IBM DB2 Linux and Windows, IBM DB2 ZOS and Oracle. The queries describe : • If a table is already partitioned , • How many partitions exist, • If the partition naming follows the IBM scheme, • In which table space is located each partition, • If the partitioned table is compressed, • If there is a partitioned index • If the partitioned index is contained in a specific table space, • If the partitioned index is compressed Refer to this link for more details about the TDW range partitioning in Tivoli Monitoring 6.3.0. 2 Partitioned table Starting at ITM 6.3 , The Warehouse Proxy Agent (KHD product code) and the summarization and Pruning agent (KSY product code) offer the possibility to enable the range partitioning for database tables and also provides a way to defines a default container for tables and indices. The compression of databases objects is available since ITM 6.2.3. 2.1 ITM environment variables The Warehouse Proxy Agent (KHD product code) and the summarization and Pruning agent (KSY product code) provide the following variables to enable the range partitioning. K[HD/SY]_PARTITION = Y or N. Controls whether partitioning is enabled or disabled. When disabled, non partitioned tables and indices will be created and the old pruning behavior will be in effect. When enabled, partitioned tables and indices will be created (if supported by the database). Default value is N. K[HD/SY]_PARTITIONS_UPWARD: Defines the number of partitions in the future that should be created. For detailed, hourly and daily, the value is in days. For other aggregations, it is the number of units in that aggregation granularity. Valid values are between 1 and 10. This allows for an outage to occur and not cause disruption on the monitoring system, permitting agents to upload their collected historical data. Default value is Catherine Cook Page 2 IBM – ITM 630 TDW Table Partitioning 10. K[HD/SY]_DB_COMPRESSION = Y or N . Controls whether compression is enabled or disabled. When enabled, tables and indices will be created with database compression set. K[HD/SY]_DEFAULT_TABLE_CONTAINER: Defines the default table space for all ITM tables. K[HD/SY]_DEFAULT_INDEX_CONTAINER: Defines the default table space for all ITM indices. If the TDW database is DB2 LUW and if only the default table table space is specified for the WPA or the SPA, the indexes are automatically created in the same table space as the table. The table space can be either a system managed or database managed table space. If a table table space and an index table space are specified, both must be database managed table spaces. 2.2 ITM table partitioning naming for TDW A table partitioned by TDW contains partition names that follow an IBM scheme that we describe in the following paragraphs. 2.2.1 DB2 LUW and Oracle For DB2 LUW and Oracle the partition name is PYYYYMMDD and PYYYYMMDD_MV where: • YYYY is the year • MM is the month • DD is the day • _MV is the catch-all partition for older data A table partitioned by TDW has a partition key set to the aggregation time. The default aggregation time is the WRITETIME column, however some agents may have an alternate column in the table to be used as a timestamp during aggregation. This column is specified by the AGTIM keyword in the ODI file. For instance : • • • • partition key for WAREHOUSELOG is EXPORTTIME partition key for WAREHOUSEAGGREGLOG is STARTTIME partition key for NT_Memory_64, Windows Agent (KNT) is WRITETIME partition key for WebSphere_XS_Server, Monitoring of ITCAM WebSphere Application Servers (KYN) is SDATE_TIME 2.2.2 DB2 Z/OS DB2 ZOS partition name is only a number starting at 1. 2.3 Is the table partitioned with an IBM scheme ? Depending on the database type, partition information can be found in different catalog tables. Catherine Cook Page 3 IBM – ITM 630 TDW Table Partitioning The following paragraphs describe the queries you can execute to find out if a table is partitioned with an IBM scheme or not: • • • DB2 LUW SQL query to detect an IBM partition scheme Oracle SQL query to detect an IBM partition scheme DB2 Z/os SQL query to detect an IBM partition scheme Edit the query and replace the parameters by the values corresponding to the table you choose to query. • • • <schema name > : user with which the Summarization and Pruning agent (SPA) and the Warehouse Proxy agent (WPA) are connected to the TDW database. <table name> : name of the table you want to verify the partitioning. <aggregation time column> : default WRITETIME column or column name described in ODI by the tag AGTIM. 2.3.1 DB2 LUW SQL query to detect an IBM partition scheme SYSCAT.DATAPARTITIONS catalog view contains information about the data partitions for a table. SYSCAT.DATAPARTITIONEXPRESSION catalog view contains information about the partition key. The following SQL will return 1 only if the table is partitioned with the IBM scheme and if its partitioned key is the default WRITETIME or the one described in ODI by the tag AGTIM. SELECT 1 FROM SYSIBM.SYSDUMMY1 WHERE EXISTS( SELECT * FROM SYSCAT.DATAPARTITIONEXPRESSION WHERE TABSCHEMA = '<schema name>' AND TABNAME = '<table name>' AND CAST(DATAPARTITIONEXPRESSION AS VARCHAR(128)) = '<agtime column>' ) AND EXISTS( SELECT * FROM SYSCAT.DATAPARTITIONS WHERE TABSCHEMA = '<schema name>' AND TABNAME = '<table name>' AND (DATAPARTITIONNAME LIKE 'P________' OR DATAPARTITIONNAME LIKE 'P________\\_MV' ESCAPE '\\') ); Catherine Cook Page 4 IBM – ITM 630 TDW Table Partitioning 2.3.2 Oracle SQL query to detect an IBM partition scheme USER_TAB_PARTITIONS describes partition-level partitioning information, partition storage parameters, and partition statistics for all partitions owned by the current user. Its columns are the same as those in ALL_TAB_PARTITIONS. The following SQL will return 1 only if the table is partitioned with the IBM scheme and if its partitioned key is the default WRITETIME or the one described in ODI by the tag AGTIM. SELECT 1 FROM DUAL WHERE EXISTS( SELECT * FROM USER_PART_KEY_COLUMNS WHERE NAME = '<table name>' AND OBJECT_TYPE = 'TABLE' AND COLUMN_NAME = '<agtime column>' AND COLUMN_POSITION = 1 ) AND EXISTS( SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = '<table name>' AND REGEXP_LIKE(PARTITION_NAME,'^P[1-2][0-9]{7}(_MV)?$') ); 2.3.3 DB2 Z/os SQL query to detect an IBM partition scheme SYSIBM.SYSTABLES is a DB2 catalog table that contains information about the tables such as table name, table space name containing the table and database name in which the table space has been created. The SYSIBM.SYSTABLEPART table contains one row for each nonpartitioned table space and one row for each partition of a partitioned table space. The following SQL will return 1 only if the table is partitioned and the limitkey for each partition ends with 23599999, which corresponds to 23h 59 min.The partition key is always suffixed with 235959999 . SELECT 1 FROM SYSIBM.SYSDUMMY1 WHERE Catherine Cook Page 5 IBM – ITM 630 TDW Table Partitioning EXISTS( SELECT * FROM SYSIBM.SYSTABLEPART P, SYSIBM.SYSTABLES T WHERE T.DBNAME = P.DBNAME AND T.TSNAME = P.TSNAME AND T.CREATOR = '<schema name>' AND T.NAME = '<table name>' AND P.LIMITKEY LIKE '%235959999' ) AND EXISTS( SELECT * FROM SYSIBM.SYSCOLUMNS WHERE NAME = '<table name>' AND TBCREATOR = '<schema name>' AND TBNAME = <table name> AND PARTKEY_COLSEQ = 1 ); 2.4 How many rows per partition ? This chapter describes how to get details on each partition for a table. Among the information, a column will represent the number of rows per partition, However this column is only correct when you run the statistics on the partition table for DB2 LUW and Oracle or when you run the statistics on the partition tablespace containing the partition table for DB2 Z/os . The following paragraphs indicate the command to run to get the statistics per database type and then which query to execute to find statistics details on each partition. • • • DB2 LUW statistics Oracle statistics DB2 Z/os statistics The following paragraphs described the queries you can execute to find out the number of partitions per table: • • • DB2 LUW SQL query to find the number of partitions Oracle SQL query to find the number of partitions DB2 Z/os SQL query to find the number of partitions 2.4.1 DB2 LUW statistics Run the following DB2 command line to generate the statistics on a specified table: db2 runstats on table <schema name>.<table name> on KEYS COLUMNS AND INDEXES ALL Catherine Cook Page 6 IBM – ITM 630 TDW Table Partitioning 2.4.2 Oracle statistics Run the following Oracle stored procedure to generate the statistics on a specified table: call dbms_STATS.gather_table_stats('<schema name>','<table name>',cascade=>true) 2.4.3 DB2 Z/os statistics On DB2 Z/os you need to run a JCL job to call the RUNSTATS utility. Here is an example of a JCL job. Update it with your userid, password , prefix library and the table spaces names according to your environment. //RUNSTATS JOB MSGCLASS=X,REGION=0M,USER=<username>,PASSWORD=<password> //**************************************************************** // JCLLIB ORDER=<prefix library>.PROCLIB //JOBLIB DD DSN=<prefix library>.SDSNLOAD,DISP=SHR //* //* PRODUCE STATISTICS //* //RUNSTATS EXEC DSNUPROC,PARM='<database name>,DSNTEX',COND=(4,LT) //SORTLIB DD DSN=SYS1.SORTLIB,DISP=SHR //SORTOUT DD UNIT=SYSDA,SPACE=(4000,(20,20),,,ROUND) //DSNTRACE DD SYSOUT=* //SYSUT1 DD UNIT=SYSDA,SPACE=(4000,(50,50),,,ROUND) //SYSREC DD UNIT=SYSDA,SPACE=(4000,(200,200),,,ROUND) //SYSIN DD * RUNSTATS TABLESPACE <dbName>.<tbspName> /* // 2.5 How many partitions ? Depending on the database type, partition information can be found in different catalog tables. 2.5.1 DB2 LUW SQL query to find the number of partitions The column CARD indicates the number of rows per partitions. SELECT TABNAME, TABSCHEMA, DATAPARTITIONNAME, CARD, LOWVALUE, LOWINCLUSIVE, HIGHVALUE, HIGHINCLUSIVE FROM SYSCAT.DATAPARTITIONS WHERE Catherine Cook Page 7 IBM – ITM 630 TDW Table Partitioning TABSCHEMA = '<schema name>' TABNAME = '<table name>' ORDER BY DATAPARTITIONID ; AND Example : • • • Table KHD_LOAD_STATISTICS (KHDLOADST) has 7 partitions. The lower partition time is MINVALUE, which means anything below 2012/09/15 23h50min59s. The higher partition time is 2012/09/20 23h59m59s. 2.5.2 Oracle SQL query to find the number of partitions The column NUM_ROWS indicates the number of rows per partitions. SELECT PARTITION_NAME, TABLE_NAME, NUM_ROWS, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = '<table name>' ORDER BY PARTITION_POSITION; Example : • • Table KHD_LOAD_STATISTICS (KHDLOADST) has 7 partitions. The lower partition time is anything below 2012/09/16 0h00min00s. The higher partition time is 2012/09/21 23h59m59s. Catherine Cook Page 8 IBM – ITM 630 TDW Table Partitioning 2.5.3 DB2 Z/os SQL query to find the number of partitions The column CARD indicates the number of rows per partitions. SELECT T.NAME, T.CREATOR, P.PARTITION, P.TSNAME, P.DBNAME, P.CARD, P.LIMITKEY FROM SYSIBM.SYSTABLEPART P, SYSIBM.SYSTABLES T WHERE T.DBNAME = P.DBNAME AND T.TSNAME = P.TSNAME AND T.CREATOR = '<schema name>' AND T.NAME = '<table name>' ORDER BY P.PARTITION WITH UR; Example : • • • Table KHD_LOAD_STATISTICS (KHDLOADST) has 7 partitions. The lower partition time is 2012/09/13 23h59m59s. The higher partition time is 2012/09/19 23h59m59s. 2.6 Which table space for each partition ? This chapter describes how to get the table space used for each partition. • • DB2 LUW SQL query to get the table space per partition Oracle SQL query to get the table space per partition ITM 6.3 does not support setting a unique default table container for DB2 Z/OS for all ITM tables. The CREATE statement with a partitioning by range for DB2 ZOS in ITM6.3 implies that the table is placed in a Range-partitioned Universal table space. There is only one table per Range-partitioned Universal table space. 2.6.1 DB2 LUW SQL query to get the table space per partition Catherine Cook Page 9 IBM – ITM 630 TDW Table Partitioning SELECT D.SEQNO, D.TABNAME, D.DATAPARTITIONNAME, T.TBSPACE FROM SYSCAT.DATAPARTITIONS D, SYSCAT.TABLESPACES T WHERE D.TABSCHEMA = '<schema name>' AND D.TABNAME='<table name>' AND D.TBSPACEID= T.TBSPACEID ORDER BY SEQNO; Example: Table KHD_LOAD_STATISTICS (KHDLOADST) has 7 partitions contained in the table space TBSPTEST: 2.6.2 Oracle SQL query to get the table space per partition SELECT TABLESPACE_NAME, PARTITION_NAME, TABLE_NAME, NUM_ROWS, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = '<table name>' ORDER BY PARTITION_POSITION; Example: Table KHD_LOAD_STATISTICS (KHDLOADST) has 7 partitions contained in the table space TBSPTEST: Catherine Cook Page 10 IBM – ITM 630 TDW Table Partitioning 2.7 Is the partitioned table compressed ? This chapter describes how to find out if a partitioned table is compressed. • • • DB2 LUW SQL query to find out if the partitions are compressed Oracle SQL query to find out if the partitions are compressed ITM 6.3 does not support setting the compression for DB2 Z/OS for all ITM tables. 2.7.1 DB2 LUW SQL query to find out if the partitions are compressed Each partition is treated independently from a compression dictionary perspective, but the main table definition must have the COMPRESSION YES option set for any of the partitions to be compressed. The system catalog table SYSCAT.TABLES contains information about the table compression. SELECT TABSCHEMA, TABNAME, COMPRESSION FROM SYSCAT.TABLES WHERE TABSCHEMA = '<schema name>' TABNAME = '<table_name>' AND Example : Table KHD_LOAD_STATISTICS is compressed. N = No compression is activated; a row format that does not support compression is used R = Row compression is activated if licensed; a row format that supports compression might be used 2.7.2 Oracle SQL query to find out if the partitions are compressed SELECT COMPRESSION, TABLESPACE_NAME, PARTITION_POSITION , Catherine Cook Page 11 IBM – ITM 630 TDW Table Partitioning PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME = '<table_name>' ORDER BY PARTITION_POSITION Example: Table KHD_LOAD_STATISTICS (KHDLOADST) has 7 partitions contained in the table space TBSPTEST and all compressed: 3 Partitioned Index An index itself can be partitioned such that each data partition has an associated index partition. 3.1 Do we have a partitioned index ? This chapter describes how to find out if an index is partitioned: • DB2 LUW SQL query to find out if an index is partitioned • Oracle SQL query to find out if an index is partitioned • DB2 Z/os SQL query to find out if an index is partitioned 3.1.1 DB2 LUW SQL query to find out if an index is partitioned SELECT P.DATAPARTITIONID, P.DATAPARTITIONNAME, I.INDNAME, I.TABNAME FROM SYSCAT.INDEXPARTITIONS I, SYSCAT.DATAPARTITIONS P WHERE I.TABSCHEMA = '<schema name>' AND I.INDNAME = '<index name>' AND P.DATAPARTITIONID = I.DATAPARTITIONID AND P.PARTITIONOBJECTID= I.INDPARTITIONOBJECTID AND P.TABSCHEMA=I.TABSCHEMA AND P.TABNAME=I.TABNAME ORDER BY I.INDNAME, Catherine Cook Page 12 IBM – ITM 630 TDW Table Partitioning P.DATAPARTITIONID Example : Index KHDLOADST_IDX on table KHD_LOAD_STATISTICS (KHDLOADST) is a partitioned index. 3.1.2 Oracle SQL query to find out if an index is partitioned SELECT PARTITION_POSITION , PARTITION_NAME, INDEX_NAME FROM USER_IND_PARTITIONS WHERE INDEX_NAME = '<index name>' ORDER BY PARTITION_POSITION Example : Index KHDLOADST_IDX on table KHD_LOAD_STATISTICS (KHDLOADST) is a partitioned index.1 1 Catherine Cook Page 13 IBM – ITM 630 TDW Table Partitioning 3.1.3 DB2 Z/os SQL query to find out if an index is partitioned SELECT PARTITION, IXNAME, IXCREATOR FROM SYSIBM.SYSINDEXPART WHERE IXCREATOR = '<schema name>' AND IXNAME = '<index name>' ORDER BY PARTITION Example : Index KHDLOADST_IDX on table KHD_LOAD_STATISTICS (KHDLOADST) is a partitioned index. 3.2 Is the partitioned index contained in a specified table space ? This chapter describes how to find out if an index is partitioned in a specified table space: • DB2 LUW SQL query to find out if an index is contained in a specific table space • Oracle SQL query to find out if an index is contained in a specific table space • ITM 6.3 does not support setting a unique default table container for DB2 Z/OS for all ITM indices. 3.2.1 DB2 LUW SQL query to find out if an index is contained in a specific table space SELECT T.TBSPACE, P.DATAPARTITIONID, P.DATAPARTITIONNAME, Catherine Cook Page 14 IBM – ITM 630 TDW Table Partitioning I.INDNAME, I.TABNAME FROM SYSCAT.INDEXPARTITIONS I, SYSCAT.DATAPARTITIONS P, SYSCAT.TABLESPACES T WHERE I.TABSCHEMA = '<schema name>' AND I.INDNAME = '<table name>' AND P.DATAPARTITIONID = I.DATAPARTITIONID AND P.PARTITIONOBJECTID= I.INDPARTITIONOBJECTID AND P.TABSCHEMA=I.TABSCHEMA AND P.TABNAME=I.TABNAME AND P.TBSPACEID= T.TBSPACEID ORDER BY I.INDNAME, P.DATAPARTITIONID Example: The partitioned index KHDLOADST_IDX is contained in the table space TBSPTEST for each partition: 3.2.2 Oracle SQL query to find out if an index is contained in a specific table space SELECT TABLESPACE_NAME, PARTITION_POSITION , PARTITION_NAME, INDEX_NAME FROM USER_IND_PARTITIONS WHERE INDEX_NAME = '<index name>' ORDER BY PARTITION_POSITION Example: The partitioned index KHDLOADST_IDX is contained in the table space TBSPTEST for each partition: Catherine Cook Page 15 IBM – ITM 630 TDW Table Partitioning 3.3 Is the partitioned index compressed ? This chapter describes how to find out if an index is partitioned in a specified table space: • DB2 LUW SQL query to find out if a partitioned index is compressed • Oracle SQL query to find out if a partitioned index is compressed • ITM 6.3 does not support setting the compression for DB2 Z/OS for all ITM indices. 3.3.1 DB2 LUW SQL query to find out if a partitioned index is compressed SELECT I.COMPRESSION, P.DATAPARTITIONID, P.DATAPARTITIONNAME, I.INDNAME, I.TABNAME FROM SYSCAT.INDEXPARTITIONS I, SYSCAT.DATAPARTITIONS P WHERE I.TABSCHEMA = '<schema name>' AND I.INDNAME = '<table_name>' AND P.DATAPARTITIONID = I.DATAPARTITIONID AND P.PARTITIONOBJECTID= I.INDPARTITIONOBJECTID AND P.TABSCHEMA=I.TABSCHEMA AND P.TABNAME=I.TABNAME ORDER BY I.INDNAME, P.DATAPARTITIONID Example: The KHDLOADST_IDX index is a partitioned and compressed index. Catherine Cook Page 16 IBM – ITM 630 TDW Table Partitioning 3.3.2 Oracle SQL query to find out if a partitioned index is compressed SELECT COMPRESSION, TABLESPACE_NAME, PARTITION_POSITION , PARTITION_NAME, INDEX_NAME FROM USER_IND_PARTITIONS WHERE INDEX_NAME = '<index name>' ORDER BY PARTITION_POSITION Example: The KHDLOADST_IDX index is a partitioned and compressed index. Catherine Cook Page 17
© Copyright 2024