DB2 Administrative SQL Cookbook: Listing Row-Organized Tables

The DB2 Administrative SQL Cookbook is a series of blog entries that are short. Each focuses on one or a few SQL statements that may be useful when administering DB2 databases. They usually address system catalog views, snapshot tables/views, or snapshot table functions that are standard in all DB2 LUW implementations. The version the SQL has been tested on is noted.

Purpose

This statement lists tables that are not system tables, but that are organized by row. It may be useful in a BLU database to identify any tables that are row-organized, and may need different maintenance or other treatment than the column-organized tables

Version

This has been tested on DB2 10.5 Fixpack 5. It should work on any 10.5 implementation. It will not work on previous versions, because table organization was not an available column in syscat.tables, or even a concept that made sense before 10.5.

Statement

SELECT  substr(tabschema,1,18) AS tabschema, 
        substr(tabname,1,40) AS tabname 
FROM    syscat.tables 
WHERE   tableorg='R' 
        AND tabschema NOT LIKE ('SYS%') 
WITH UR

Sample Output

TABSCHEMA          TABNAME
------------------ ----------------------------------------
DB2INST1           EXPLAIN_INSTANCE
DB2INST1           OBJECT_METRICS
DB2INST1           ADVISE_INSTANCE
DB2INST1           EXPLAIN_STATEMENT
DB2INST1           EXPLAIN_ARGUMENT
DB2INST1           EXPLAIN_OBJECT
DB2INST1           EXPLAIN_OPERATOR
DB2INST1           EXPLAIN_PREDICATE
DB2INST1           EXPLAIN_STREAM
DB2INST1           EXPLAIN_DIAGNOSTIC
DB2INST1           EXPLAIN_DIAGNOSTIC_DATA
DB2INST1           ADVISE_INDEX
DB2INST1           ADVISE_WORKLOAD
DB2INST1           ADVISE_MQT
DB2INST1           ADVISE_PARTITION
DB2INST1           ADVISE_TABLE
DB2INST1           EXPLAIN_ACTUALS
...

Caveats and Modifications

Hard numbers are used to limit the length of the schema and table names. If you have longer schema and table names, you may want to increase these numbers.
You can easily reverse this to list column-organized tables by specifying a TABLEORG of ‘C’.
Strictly speaking “with ur” is not needed, but I’m in the habit of adding it to the end of every query that doesn’t have an isolation requirement.

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *