New Features in DB2 UDB V9 - Part 1

[johannes_heinrich]

While the focus of the new Version of DB2 for Unix and Windows (codenamed Viper) is certainly on the native XML capabilities, there are also news for everyone using this database for SAP. Based on the excellent partnership between IBM and SAP, numerous new helpful features are integrated in Viper. In this blog I will introduce these new features step by step. Todays post is about accessing specific information about tables via SQL.

Table information via SQL

The new UDF (User Defined Function) ADMIN_GET_TAB_INFO delivers details about tables which are not available elsewhere. It is a table UDF so the result comes back in form of a table. Corresponding to this function there is a view called SYSIBM.ADMINTABINFO - if you query the view or call the UDF is up to you. There is, however, one difference. The UDF accepts two input parameter (TABSCHEMA and TABNAME). If you actually provide these input parameters the UDF will be faster than the view.

Now let's find out details about what the UDF delivers. I will do this as the DB2 administration user in a SAP system running on DB2 V9 using the DB2 command line processor.

db2 => describe table SYSIBMADM.ADMINTABINFO Column Type Type name schema name Length Scale Nulls ------------------------------ --------- ------------------ -------- ----- ------ TABSCHEMA SYSIBM VARCHAR 128 0 Yes TABNAME SYSIBM VARCHAR 128 0 Yes TABTYPE SYSIBM CHARACTER 1 0 Yes DBPARTITIONNUM SYSIBM SMALLINT 2 0 Yes DATA_PARTITION_ID SYSIBM INTEGER 4 0 Yes AVAILABLE SYSIBM CHARACTER 1 0 Yes DATA_OBJECT_L_SIZE SYSIBM BIGINT 8 0 Yes DATA_OBJECT_P_SIZE SYSIBM BIGINT 8 0 Yes INDEX_OBJECT_L_SIZE SYSIBM BIGINT 8 0 Yes INDEX_OBJECT_P_SIZE SYSIBM BIGINT 8 0 Yes LONG_OBJECT_L_SIZE SYSIBM BIGINT 8 0 Yes LONG_OBJECT_P_SIZE SYSIBM BIGINT 8 0 Yes LOB_OBJECT_L_SIZE SYSIBM BIGINT 8 0 Yes LOB_OBJECT_P_SIZE SYSIBM BIGINT 8 0 Yes XML_OBJECT_L_SIZE SYSIBM BIGINT 8 0 Yes XML_OBJECT_P_SIZE SYSIBM BIGINT 8 0 Yes INDEX_TYPE SYSIBM SMALLINT 2 0 Yes REORG_PENDING SYSIBM CHARACTER 1 0 Yes INPLACE_REORG_STATUS SYSIBM VARCHAR 10 0 Yes LOAD_STATUS SYSIBM VARCHAR 12 0 Yes READ_ACCESS_ONLY SYSIBM CHARACTER 1 0 Yes NO_LOAD_RESTART SYSIBM CHARACTER 1 0 Yes NUM_REORG_REC_ALTERS SYSIBM SMALLINT 2 0 Yes INDEXES_REQUIRE_REBUILD SYSIBM CHARACTER 1 0 Yes LARGE_RIDS SYSIBM CHARACTER 1 0 Yes LARGE_SLOTS SYSIBM CHARACTER 1 0 Yes DICTIONARY_SIZE SYSIBM BIGINT 8 0 Yes 27 record(s) selected.

ADMIN_GET_TAB_INFO provides meaningful information about table size, partitioning, reorganization plus a few other things. Let's discuss the more interesting columns.

The first two columns, TABSCHEMA and TABNAME are self-explanatory. TABTYPE is one of

• T for table
• S for materialized query table
• H for hierarchy table

Expect to see only tables of type T in SAP systems.

The next two columns are about partitioning. DBPARTITIONNUM refers to the well-known DPF (Database Partition Feature), DATA_PARTITION_ID is about table partitioning which is introduced in Viper. I will cover partitioning in an extra post.

Size matters

- especially table size. You like to know how much space your tables occupy. There is, however, a difference between the size as it would be reported by the operating system and the actual space occupied by the table data. Why? Imagine you do a lot of INSERTs into a table. Obviously, the table grows and aquires more space on disk by allocating new extents. Now some DELETEs are done against that table. Is the table size on disk shrinking now? The answer is no. The space released by deleting records is now available for new records which will be inserted into the table, but it is not automatically given back to the filesystem. This leads us to a subtle but important distinction regarding size: we talk about the physical size of a table to describe the space the table actually occupies on disk. Logical size refers to the space the table uses 'internally'. Stated in other words: The difference between physical and logical size represents space that could be reclaimed by an REORG. The columns DATA_OBJECT_L_SIZE upto XML_OBJECT_P_SIZE deliver logical and physical size information about the various data portions of a table.

Let's see an example. We examine a table belonging to the APO (Advanced Planner and Optimizer) in a SAP system Q1I based on DB2 V9.

db2 => select count(*) from "SAPQ1I"."/SAPAPO/SALES" 1 ----------- 0 db2 => select data_object_l_size, data_object_p_size, index_object_l_size, index_object_p_size from table(ADMIN_GET_TAB_INFO('SAPQ1I', '/SAPAPO/SALES')) AS X DATA_OBJECT_L_SIZE DATA_OBJECT_P_SIZE INDEX_OBJECT_L_SIZE INDEX_OBJECT_P_SIZE -------------------- -------------------- -------------------- -------------------- 64 4224 96 2688

First we find out that the table is actually empty. Second we use ADMIN_GET_TAB_INFO to get size information about the table. Let me add a few remarks regarding the syntax here: As already said before the table function accepts two input parmeters, the table schema and the table name. Parameters for table functions must be in single quotes. If you leave out these parameters (writing ...from table ADMIN_GET_TAB_INFO('','')) with two times two single quotes), all tables in all schematas will be considered. You can also leave out just one parameter. A common mistake in using user defined table functions is to forget the alias (...AS X). If you forget the alias you will see the error message 'SQL0104N An unexpected token "END-OF-STATEMENT" was found...'. By the way, /SAPAPO/SALES is a normal table name. You will find table names like this following a sepcific naming schema more often in newer SAP releases.

The result tells us two things. Firstly, despite the fact that the table is empty, the logical 'internal') table size (without the indexes) is 64 KByte. This is because a table allocates initially space equivalent to two extents. In our exmaple the table is located in a tablespace with 16K page size and an extent size of 2 pages (32K). Two extends sum up to 64K. Secondly, on disk the table data occupies 4224 KByte. This tells us that the table was once populated, the space acquired at this time is not deallocated so far. The same reasoning applies to the index size. Note that for a complete size consideration we would have to take additionally the columns LONG_OBJECT_L_SIZE, LONG_OBJECT_P_SIZE, LOB_OBJECT_L_SIZE and LOB_OBJECT_P_SIZE into account. For the sake of simplicity I left them out in this example (their values are all 0 here). XML_OBJECT_L_SIZE and XML_OBJECT_P_SIZE are always 0 in a SAP system.

db2 => REORG TABLE "SAPQ1I"."/SAPAPO/SALES" DB20000I The REORG command completed successfully. db2 => select data_object_l_size, data_object_p_size, index_object_l_size, index_object_p_size from table(ADMIN_GET_TAB_INFO('SAPQ1I', '/SAPAPO/SALES')) AS X DATA_OBJECT_L_SIZE DATA_OBJECT_P_SIZE INDEX_OBJECT_L_SIZE INDEX_OBJECT_P_SIZE -------------------- -------------------- -------------------- -------------------- 64 64 96 96

As you can see, reorganizing the table will reclaim the once acquired space. Accessing size information about tables this way provides a definite advantage over using the existing GET SNAPSHOT FOR TABLES functionality.

Covering new features

Let's go on to the next columns. INDEX_TYPE is very helpful as it allows to find out which index type (1 or 2) is used. With DB2 V8 the so called Type 2 Index was introduced. So far you had to use utilities like db2dart or inspect to determine the index type. Now it is possible to find out which index type is used by the indexes of a table with one single SQL statement. Note that in Viper indexes of type 1 are deprecated.

Another important information is revealed by REORG_PENDING. If an ALTER TABLE operationaffects the row format of the table, a REORG is required before the table is accessible again. The need for such a REORG is indicated by the REORG_PENDIG flag (it is set to 'Y'). NUM_REORG_REC_ALTERS counts the number of the table alterations which require a REORG. INPLACE_REORG_STATUS shows details about the current status of an REORG...INPLACE command.

A number of columns deals with LOAD operations and I will skip them here. There are three more interesting columns: LARGE_RIDS and LARGE_SLOTS are about another new V9 feature: larger row identifiers (RIDs). Large RIDs enable table sizes beyond the 64GByte limit (in a 4K table space) in Version 8. I will cover the details about the conversion to large RIDs in my next post.

Finally, the last column I'd like to mention is about row compression. This is another new feature in V9 and is is not to be confused with value compression (which exists already before). Using row compression common byte patterns in the rows of a table will be replaced with shorter symbol strings. For the mapping of these symbol strings to the byte patterns, a dictionary is required. DICTIONARY_SIZE tells us about the size of this dictionary in bytes.

In summary, one can say that ADMIN_GET_TAB_INFO provides a lot of useful additional information about a table. The function will be used in SAP coding, especially in the DBA Cockpit. Administrators will benefit especially from the detailed size calculations which are now possible via SQL. For the official and complete IBM documentation of this function have a look here.