When Was a DB2 Table Created and by Who?

This little bit of SQL should help you determine who created a table or when it was created…

SELECT 
CASE TAB.TYPE 
WHEN 'A' THEN 'Alias' 
WHEN 'N' THEN 'Nickname' 
WHEN 'S' THEN 'MQT' 
WHEN 'T' THEN 'Table' 
WHEN 'V' THEN 'View' 
ELSE 'OTHER: '||TAB.TYPE 
END AS T_TYPE 
,RTRIM(TAB.TABSCHEMA)||'.'||TAB.TABNAME AS TABLE_NAME 
,DATE(CREATE_TIME) AS CREATED 
,DATE(ALTER_TIME) AS ALTERED 
,TAB.COLCOUNT AS COLUMNS 
,TAB.DEFINER AS CREATED_BY 
FROM SYSCAT.TABLES TAB 
WHERE TABSCHEMA = 'XX' 
ORDER BY TABLE_NAME 
FOR READ ONLY 
WITH UR;

How to Force or Reset the Starting Index for a DB2 IDENTITY Column

Of course you want to use our old friend “ALTER TABLE”…

ALTER TABLE XX.MY_TABLE ALTER COLUMN THE_COL RESTART WITH 1001;

Now Reading Atlas Shrugged

Now Reading Ayn Rand’s Classic: Atlas Shrugged

It’s been a long time since I’ve started on a book that has been hard to put down. After hearing about this book for years I finally had someone get it for me off of my wishlist and I’ve been up every night until around midnight to 1am reading. At over 1000 pages it’s a long read but it’s a great story with a message I mostly agree with (so far). Of course there are a few things I’m not entirely on board with, but I’m interested to see how it all shakes out in the end. By far my favorite part is Francisco’s speech at Jim Taggat’s wedding about 35% of the way into the book. Right now I’m on John Galt’s radio address near the end and sadly it’s my least favorite part. Too long winded. If he wants to sell his idea to the world I say he needs to cut to the chase instead of blathering on. The mental image I keep getting is of the entire nation listening, and after about ten minutes just tuning out.

Still, this is one book I would highly recommend.

Anyone else out there read it? Thoughts?

Check DB2 Tablespace Status for Load Pending State

Did you do a LOAD and lock up your tablespace? The best way to find out is from the command line via…

LIST TABLESPACES SHOW DETAIL

Note: The issuer must have sysadm, sysctrl, sysmaint, dbadm, or load authorization

Find DB2 Fields Containing Carriage Returns, Line Feeds, or Both CRLF

select * 
from HH.MY_TABLE 
where TEXT_X like '%' || chr(13) || chr(10) || '%' 

... or ...

select * 
from HH.MY_TABLE 
where TEXT_X like '%' || chr(10) || '%' 

... or ...

select * 
from HH.MY_TABLE 
where TEXT_X like '%' || chr(13) || '%'

Find time between records in a logging table with DB2

One trick I’ve used for quite a while is to have stored procs “log” information to a table with the structure…

XX.SYS_PROC_AUD
(
PROC_X,
ACTIVITY_X,
CREATED_TS,
ERROR_NB
)

…which is great for seeing what’s going on across the board, on a proc by proc basis, or looking for specific things. But what about when management request “metrics” about how things are running? Well, since I already log stop and start of the procs to the aforementioned table, you can use a WITH query to help get run times of procs and even between procs without resorting to Excel like so…

WITH    rows AS
        (
        SELECT  ROW_NUMBER() OVER (ORDER BY CREATED_TS) AS rn, CREATED_TS, PROC_X
        FROM    XX.SYS_PROC_AUD  
        WHERE   (
                PROC_X = 'XX.FIRST_PROC_P'  
			    AND ACTIVITY_X = 'Starting Exec'  
                )
                OR
                (
                PROC_X = 'XX.LAST_PROC_P'  
			    AND ACTIVITY_X = 'Finished Exec'  
                )
        ORDER BY CREATED_TS DESC
        FETCH FIRST 1000 ROWS ONLY WITH UR
        )
SELECT  mc.CREATED_TS as START_TS, 
        mp.CREATED_TS as END_TS, 
        TIMESTAMPDIFF(2, CHAR(mp.CREATED_TS - mc.CREATED_TS)) AS SEC, 
        TIMESTAMPDIFF(2, CHAR(mc.CREATED_TS - mpe.CREATED_TS)) AS SEC_BETWEEN_LAST
FROM    rows mc
            INNER JOIN rows mp
                ON mc.rn = mp.rn - 1
            INNER JOIN rows mpe
                ON mc.rn = mpe.rn + 1
WHERE   mc.PROC_X = 'XX.FIRST_PROC_P'
ORDER BY mc.CREATED_TS DESC
FETCH FIRST 100 ROWS ONLY WITH UR FOR READ ONLY

Note: I prefer to see the most recent things first so you may need to adjust your ordering appropriately if you don’t like that setup.

Thanks to this post for getting me started.

Find Nullable Columns in DB2 with no Null Values

I’m not a fan of NULL columns. So check out the query below if you want to find DB2 fields that are defined as nullable, but which contain no null values. You can also modify the query to show you how many records contain null values. Make sure to set the COLS.TABSCHEMA in the WHERE predicate.

SELECT RTRIM(TAB.TABSCHEMA)||'.'||TAB.TABNAME AS TABLE_OBJECT_NAME 
, COLS.COLNAME AS COLNAME , COLS.TYPENAME AS 
TYPE ,RTRIM(CHAR(COLS.LENGTH))|| 
CASE COLS.SCALE 
WHEN 0 
THEN ' ' 
ELSE ','||RTRIM(CHAR(COLS.SCALE)) 
END || 
CASE COLS.TYPENAME 
WHEN 'VARCHAR' 
THEN ' AVG:'||RTRIM(CHAR(COLS.AVGCOLLEN)) 
ELSE ' ' 
END AS LENGTH 
,TAB.CARD as TAB_CARD 
,COLS.COLCARD AS COL_CARD 
,CASE 
WHEN NULLS ='Y' 
THEN 'NULL ('||RTRIM(CHAR(NUMNULLS))||')' 
WHEN NULLS = 'N' THEN 'NOT NULL' 
ELSE '??' END as NUM_NULLS 
,RTRIM(COLS.TABSCHEMA)||'.'||COLS.TABNAME||'2' AS SORTCOL,COLS.COLNO AS SORTCOL2 
FROM SYSCAT.COLUMNS COLS 
INNER JOIN SYSCAT.TABLES TAB ON (COLS.TABSCHEMA = TAB.TABSCHEMA AND COLS.TABNAME = TAB.TABNAME) 
WHERE RTRIM(COLS.TABSCHEMA) = 'XX' 
AND ( 
(NULLS = 'Y' AND NUMNULLS=0 AND TAB.CARD > 0) 
--OR (COLS.TYPENAME = 'VARCHAR' AND COLS.AVGCOLLEN >= COLS.LENGTH-2) 
) 
ORDER BY SORTCOL,SORTCOL2 
FOR READ ONLY WITH UR;

How to Truncate a DB2 Table

There’s 2 ways to do this…

The first way should work on any version of DB2. Make sure to add to add the savecount and nonrecoverable as seen below so you don’t put your table space into a bad condition.

{CALL SYSPROC.ADMIN_CMD('LOAD FROM /dev/null of del savecount 1000 replace into XX.MY_TABLE nonrecoverable')};

The seconds is easier but has only been around since v9.5 or v9.7…

TRUNCATE TABLE XX.MY_TABLE IMMEDIATE

Just make sure you include that IMMEDIATE in there 🙂

DB2: When were index stats last updated on a table?

To find out when stats were updated for an index, run the following SQL substituting your schema and table names…

SELECT  IND.TABSCHEMA,
        IND.TABNAME,
        IND.INDNAME,
        IND.COLNAMES,
        IND.STATS_TIME AS IDX_STATS_TIME, 
        T.STATS_TIME TBL_STATS_TIME, 
        CASE T.VOLATILE
            WHEN 'C' THEN 'YES'
            ELSE T.VOLATILE
        END AS TBL_VOLATILE,
        T.OWNERTYPE AS TBL_OWNERTYPE,
        T.TYPE AS TBL_TYPE
FROM    SYSCAT.INDEXES IND 
            LEFT OUTER JOIN SYSCAT.TABLES T
                ON IND.TABSCHEMA = T.TABSCHEMA
                    AND IND.TABNAME = T.TABNAME
WHERE   T.OWNERTYPE != 'S'
        AND IND.TABNAME = 'MY_TABLE' 
        AND IND.TABSCHEMA = 'XX' 
ORDER BY 
        IND.TABSCHEMA,
        IND.TABNAME,
        IND.INDNAME 
FOR READ ONLY WITH UR;