Ever have a DB2 query that all of a sudden went from performing just fine to being dog slow? If so, there may be an issue with the stats on one or more of the tables being used in the query. To check, run the SQL below replacing XX and TABLE_NM with your appropriate schema and table in 2 places…

-- What DB2 thinks the table count is
SELECT  'Stats' AS TYPE_X,
        CARD AS COUNT_NB,
        STATS_TIME AS UPDATED_TS
FROM    SYSCAT.TABLES 
WHERE   TABSCHEMA = 'XX' 
        AND TABNAME ='TABLE_NM'
    UNION
-- Actual table count
SELECT  'Actual' AS TYPE_X,
        COUNT(*) AS COUNT_NB,
        CURRENT_TIMESTAMP AS UPDATED_TS
FROM    XX.TABLE_NM
FOR READ ONLY WITH UR

… if the record counts are way off, have your DBA do a runstats on the table in question followed by a rebind of any stored procs that use the table.

If you’ve ever had to do a recursive query in DB2 to get a hierarchy of records, feel free to use this as a starting point…

WITH LINKS (PARENT_ID, CHILD_ID, UPDATED_TS) AS 
    ( 
    SELECT  ROOT.PARENT_ID, ROOT.CHILD_ID, ROOT.UPDATED_TS 
    FROM    XX.EVENTS ROOT 
    WHERE   ROOT.CHILD_ID = '234ASDFASDF' 
        UNION ALL 
    SELECT  CHILD.PARENT_ID, CHILD.CHILD_ID, CHILD.UPDATED_TS 
    FROM    LINKS PARENT, XX.EVENTS CHILD 
    WHERE   PARENT.PARENT_ID = CHILD.CHILD_ID 
    ) 
SELECT  PARENT_ID, CHILD_ID, UPDATED_TS 
FROM    LINKS 
ORDER BY UPDATED_TS 
FOR READ ONLY WITH UR

… yes, newer versions of DB2 have this ability built in but you’re not always using the most recent version are you??

Thanks to this page for pointing me in the right direction.

Building off of yesterdays lesson on how to determine how much space a table is using (including LOBs and Indexes), there may come a time when you need to determine how much space 1 or more specific records are taking up. To do that, use the LENGTH() function which will return you how many bytes the field is using. Simply do some math to get a more useful value (like KB).

SELECT length(LOB_DATA_X) BYTE_A, length(LOB_DATA_X) / 1024 KB_A
FROM XX.MY_TABLE
FETCH FIRST 10 ROWS ONLY WITH UR FOR READ ONLY;

If you have a DB2 table and want to get information as to how much space a particular table is using, you could try and deduce it from things like the row length, or you could just query the view via a table function and make your life a heck of a lot easier.

For instance, start with something like this…

SELECT * FROM TABLE(SYSPROC.ADMIN_GET_TAB_INFO('XX', 'MY_TABLE'));

Previous versions of this function include ADMIN_GET_TAB_INFO_V97 and ADMIN_GET_TAB_INFO_V95. The nice thing is that it appears you don’t have to be a DBA to query the info which is nice for us app developers out there!

Keep in mind that sizes returned are in KB, so you may want to convert the data to something more useful like so…

SELECT  TRIM(TABSCHEMA) || '.' || TRIM(TABNAME) AS TABLE_NM,
        DATA_OBJECT_P_SIZE / 1024 AS DATA_P_SIZE_NB, 
        LOB_OBJECT_P_SIZE / 1024 AS LOB_P_SIZE_MB, 
        XML_OBJECT_P_SIZE / 1024 AS XML_P_SIZE_MB, 
        LONG_OBJECT_P_SIZE / 1024 AS LONG_P_SIZE_MB, 
        (DATA_OBJECT_P_SIZE + LOB_OBJECT_P_SIZE + XML_OBJECT_P_SIZE + LONG_OBJECT_P_SIZE) / 1024 AS TOTAL_P_SIZE_MB,
        INDEX_OBJECT_P_SIZE / 1024 AS INDEX_OBJECT_P_SIZE_MB
FROM TABLE(SYSPROC.ADMIN_GET_TAB_INFO('XX', 'MY_TABLE'));

See also: this link