Just like any other DBMS, DB2 keeps all of your data in tables. The tables are organized in a usual way i.e. data is stored in rows with each row having one or more columns. This is true even in the case when the data that is stored as XML. Hopefully it is not a surprise that DB2 Express-C has a “hybrid” storage engine that stores both relational data and XML data in their native formats. We call DB2’s ability to deal with XML data “pureXML”. For XML, this means that XML documents are stored in DB2 as pre-parsed trees. These XML trees are not a replacement for rows and columns. Instead, XML is just another data type for a column and each table can have zero or more columns of XML type.
Since table objects (or just tables) are so fundamental to your ability to store and retrieve data there comes a time when you want to get some information on a particular table in your database. For example, you may want to know how much disk space has been allocated for a particular table, or what state it is in. Should it be reorganized? Has the last load job finished loading data in to it and so on. If you were using MySQL, you may have just looked at the file that represents a table and were able to figure out disk usage that way. There is a better way to do this in DB2 Express-C (and every other DB2 for Linux, Unix and Windows v9.5). Since DB2 speaks SQL, it makes sense to query information about its tables using SQL. DB2 provides not one but 2 ways of getting cornucopia of information about tables via SQL. One way is to use a SYSIBMADM.ADMINTABINFO view. At this point in time you may be tempted to just fire up your DB2 Command Line Processor (CLP) and type in this query:
SELECT * FROM SYSIBMADM.ADMINTABINFO
Go ahead and try this. I’ll bet you are not very impressed with the results. You are getting the right information but it is just too much of a good thing. The query is returning about 29 columns and this is for every table in your database. CLP is not the ideal way to display such query results. The good thing is that this is just a view and we are using SQL. Obviously we can apply a predicate to, for example, only return information for a particular table and specify only the columns we are interested in to make it more manageable. And, we can always use a better tool than CLP to run the query; say DB2 Control Center.
Instead of retrieving all of the columns in the view, you will likely be interested in a subset of columns. DB2 information Center provides a very comprehensive description of each column in this view. I will provide a much shorter description here to save you the hastle of trying to figure out what information applies to DB2 Express-C and what you can ignore.
| Column name | Data type | Description |
|---|---|---|
| TABSCHEMA | VARCHAR(128) | Schema name. Schema in DB2 is a convenient way to manage the name space for objects in the same database. |
| TABNAME | VARCHAR(128) | Table name. |
| TABTYPE | CHAR(1) | Table type:
|
| DBPARTITIONNUM | SMALLINT | Database partition number. Not applicable to DB2 Express-C |
| DATA_PARTITION_ID | INTEGER | Data partition number. Not applicable to DB2 Express-C |
| AVAILABLE | CHAR(1) | State of the table:
Note: Rollforward through an unrecoverable load will put a table into the unavailable state.
|
| DATA_OBJECT_L_SIZE | BIGINT | Amount of disk space logically allocated for the table, reported in kilobytes. This size represents the logical size of the base table only. Space consumed by LOB data, Long Data, Indexes and XML objects are reported by other columns. |
| DATA_OBJECT_P_SIZE | BIGINT | Amount of disk space physically allocated for the table, reported in kilobytes. This size represents the physical size of the base table only. Space consumed by LOB data, Long Data, Indexes and XML objects are reported by other columns. |
| INDEX_OBJECT_L_SIZE | BIGINT | Amount of disk space logically allocated for the indexes defined on the table, reported in kilobytes. |
| INDEX_OBJECT_P_SIZE | BIGINT | Amount of disk space physically allocated for the indexes defined on the table, reported in kilobytes. |
| LONG_OBJECT_L_SIZE | BIGINT | Amount of disk space logically allocated for long field data in a table, reported in kilobytes. |
| LONG_OBJECT_P_SIZE | BIGINT | Amount of disk space physically allocated for long field data in a table, reported in kilobytes. |
| LOB_OBJECT_L_SIZE | BIGINT | Amount of disk space logically allocated for LOB data in a table, reported in kilobytes. |
| LOB_OBJECT_P_SIZE | BIGINT | Amount of disk space physically allocated for LOB data in a table, reported in kilobytes. |
| XML_OBJECT_L_SIZE | BIGINT | Amount of disk space logically allocated for XML data in a table, reported in kilobytes. |
| XML_OBJECT_P_SIZE | BIGINT | Amount of disk space physically allocated for XML data in a table, reported in kilobytes. |
| INDEX_TYPE | SMALLINT | Indicates the type of indexes currently in use for the table. Returns:
|
| REORG_PENDING | CHAR(1) | A value of ‘Y’ indicates that a reorg recommended alter has been applied to the table and a classic (offline) reorg is required. Otherwise ‘N’ is returned. |
| INPLACE_REORG_STATUS | VARCHAR(10) | Current status of an inplace table reorganization on the table. The status value can be one of the following:
|
| LOAD_STATUS | VARCHAR(12) | Current status of a load operation against the table. The status value can be one of the following:
|
| READ_ACCESS_ONLY | CHAR(1) | ‘Y’ if the table is in Read Access Only state, ‘N’ otherwise. A value of ‘N’ should not be interpreted as meaning that the table is fully accessible. If a load is in progress or pending, a value of ‘Y’ means the table data is available for read access, and a value of ‘N’ means the table is inaccessible. Similarly, if the table status is set integrity pending (refer to SYSCAT.TABLES STATUS column), then a value of ‘N’ means the table is inaccessible. |
| NO_LOAD_RESTART | CHAR(1) | A value of ‘Y’ indicates the table is in a partially loaded state that will not allow a load restart. A value of ‘N’ is returned otherwise. |
| NUM_REORG_REC_ALTERS | SMALLINT | Number of reorg recommend alter operations (for example, alter operations after which a reorganization is required) that have been performed against this table since the last reorganization. |
| INDEXES_REQUIRE_REBUILD | CHAR(1) | ‘Y’ if any of the indexes defined on the table require a rebuild, and ‘N’ otherwise. |
| LARGE_RIDS | CHAR(1) | Indicates whether or not the table is using large row IDs (RIDs) (4 byte page number, 2 byte slot number). A value of ‘Y’ indicates that the table is using large RIDs and ‘N’ indicates that it is not using large RIDs. A value of ‘P’ (pending) will be returned if the table supports large RIDs (that is, the table is in a large table space), but at least one of the indexes for the table has not been reorganized or rebuilt yet, so the table is still using 4 byte RIDs (which means that action must be taken to convert the table or indexes). |
| LARGE_SLOTS | CHAR(1) | Indicates whether or not the table is using large slots (which allows more than 255 rows per page). A value of ‘Y’ indicates that the table is using large slots and ‘N’ indicates that it is not using large slots. A value of ‘P’ (pending) will be returned if the table supports large slots (that is, the table is in a large table space), but there has been no offline table reorganization or table truncation operation performed on the table yet, so it is still using a maximum of 255 rows per page. |
| DICTIONARY_SIZE | BIGINT | Size of the dictionary, in bytes, used for row compression if a row compression dictionary exists for the table. The value will always be zero since DB2 Express-C does not support row compression. |
| BLOCKS_PENDING_CLEANUP | BIGINT | For MDC tables, the number of blocks pending cleanup. Value will always be zero as DB2 Express-C does not support MDC tables. |
| STATSTYPE | CHAR(1) |
|
As you can see, many of the columns are not applicable to the DB2 Express-C environment but they are there to preserve 100% compatibility with other editions of DB2.
At the start of the post I said that there are two SQL based ways to get detailed information on a DB2 table. ADMINTABINFO view is one. The other is a to use SYSPROC.ADMIN_GET_TAB_INFO_V95 table function. This function, like any other table function, can be used in a SELECT statement. It accepts table schema and table name as parameters and returns exactly the same information as the ADMINTABINFO view.
SELECT * FROM TABLE (SYSPROC.ADMIN_GET_TAB_INFO_V95('LEON', 'DEPARTMENT'))
I recommend using ADMINTABINFO and to stay away from using ADMIN_GET_TAB_INFO_V95 table function directly. There is absolutely nothing that the ADMINTABINFO can not provide that ADMIN_GET_TAB_INFO_V95 can. And, you probably already noticed “V95” in the name. This on its own should serve as the warning sign.
Just want to point out that I was able to create Materialized query table in my express-c with “refresh deferred” option, only the “refresh immediate” wasn’t working. You may want to correct the above
TABTYPE column’s description
* ‘H’ = hierarchy table
* ‘S’ = materialized query table. Materialized Query Tables (MQT) are not supported in DB2 Express-C.
* ‘T’ = table
Arun, thanks for giving it a try. while you may have been able to create a Materialized Query Table DB2 Express-C license does not allow it. In other words, it is a license limitation not a technical limitation. And, yes, our license code should have prevented that.
I never really thought of that before