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.
