Category Archives: DB2

How to Tell who has DB Admin Privileges in DB2

If you…

SELECT * FROM SYSCAT.DBAUTH

... you will get back a lot of details about what permissions people have including DB Admin.

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;

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;

DB2 Stored Proc Performance Analysis

Have you ever wondered what tables and indexes a DB2 stored proc are using? How about if the proc has been rebound since the stats were last updated? Are there even stats for the table you’re querying? Luckily I work with a very talented application DBA (Fred Johnson) who put together the following query to tell you all these sorts of things. It’s quite long, but all you need to do is set your proc in the “FILTER_PARMS” “VALUES” section at the top of the query.

-------------------------------------------------------------------------
-- DB2 for AIX QUERY
--PROCS WITH DEPENDENT TABLES INDEXES CALLED_PROCS AND OTHER OBJECTS 
--FLAG column shows possible stats issues.  See FOOTNOTES
-------------------------------------------------------------------------
-- Directions: Change FILTER_PARMS,Cut and paste, run, examine output
--             More than 1 proc can be reviewed by adding addition lines
-------------------------------------------------------------------------
WITH FILTER_PARMS   (ROUTINESCHEMA,ROUTINENAME) AS 
            (VALUES 
                                ('XX','PROC1_P') 
                               ,('XX','PROC2_P')
                       )    
,ROUTINEDEP (STOREDPROC,TEXTX,OBJECTNAME,CARD,BIND_STATS_CREATE_TIME,FLAG,SORTCOL) AS
(SELECT RTRIM(X.ROUTINESCHEMA)||'.'||X.ROUTINENAME AS STOREDPROC , 
        'PACKAGE' AS TEXTX ,RTRIM(P1.PKGSCHEMA)||'.'||P1.PKGNAME AS 
        OBJECTNAME ,' ' AS CARD
               , 'BIND:   '||CHAR(DATE(P1.LAST_BIND_TIME))||'*'||CHAR(TIME(P1.LAST_BIND_TIME)) 
               AS BIND_STATS_CREATE_TIME 
               ,CASE
               WHEN  P1.LAST_BIND_TIME < CURRENT_TIMESTAMP - 9 DAYS
               THEN '*STALE REBIND ' ELSE ' ' 
                END
                 ||
        CASE
          WHEN P1.VALID = 'N' THEN '*PACKAGE INVALID '
          WHEN P1.VALID = 'X' THEN '*PACKAGE INOPERATIVE '
          ELSE '' END  
                
                 AS FLAG 
               ,RTRIM(X.ROUTINESCHEMA)||X.ROUTINENAME||'1' AS SORTCOL
      FROM SYSCAT.PACKAGES P1
        INNER JOIN SYSCAT.ROUTINEDEP R
        ON (P1.PKGNAME = R.BNAME
           AND P1.PKGSCHEMA = R.ROUTINESCHEMA)
        INNER JOIN  SYSCAT.ROUTINES X
        ON (R.ROUTINENAME = X.SPECIFICNAME
          AND R.ROUTINESCHEMA = X.ROUTINESCHEMA)
        INNER JOIN FILTER_PARMS FP
         ON (FP.ROUTINESCHEMA  = X.ROUTINESCHEMA 
             AND FP.ROUTINENAME =  X.ROUTINENAME)
         UNION
    --TABLES
    SELECT ' ' AS STOREDPROC , CASE
          WHEN P.BTYPE = 'A' THEN 'ALIAS'
          WHEN P.BTYPE = 'B' THEN 'TRIGGER'
          WHEN P.BTYPE = 'D' THEN 'SERVER DEF'
          WHEN P.BTYPE = 'F' THEN 'PROC/FUNC'
          WHEN P.BTYPE = 'I' THEN 'INDEX'
          WHEN P.BTYPE = 'M' THEN 'FUNCTION MAP'
          WHEN TAB.TYPE = 'N' THEN 'NICKNAME'
          WHEN P.BTYPE = 'O' THEN 'PRIVILEGE DEP'
          WHEN P.BTYPE = 'P' THEN 'PAGE SIZE'
          WHEN P.BTYPE = 'R' THEN 'STRUCT TYPE '
          WHEN P.BTYPE = 'S' THEN 'MQTABLE'
          WHEN P.BTYPE = 'T' THEN 'TABLE'
          WHEN P.BTYPE = 'U' THEN 'TYPED TABLE'
          WHEN P.BTYPE = 'V' THEN 'VIEW'
          WHEN P.BTYPE = 'W' THEN 'TYPED VIEW'
          ELSE ' ?' END AS TEXTX  ,RTRIM(P.BSCHEMA)||'.'||
        P.BNAME AS OBJECTNAME ,RTRIM(CHAR(TAB.CARD)) AS CARD 
                ,'STATS:'||COALESCE(CHAR(DATE(TAB.STATS_TIME))||'*'||CHAR(TIME(TAB.STATS_TIME)) ,'NO STATS')
                 AS BIND_STATS_CREATE_TIME
        ,CASE
          WHEN TAB.CARD = -1
          THEN '*NO TABLESTATS'
          WHEN TAB.STATS_TIME > P1.LAST_BIND_TIME
            THEN '*PROC BIND < STATS'
          ELSE ' '
        END 
        ||
        CASE
          WHEN  TAB.STATUS <> 'N'
            THEN '*STATUS '||RTRIM(TAB.STATUS)
          ELSE '' 
        END 
        AS FLAG
        
         ,RTRIM(X.ROUTINESCHEMA)||X.ROUTINENAME||'2'||RTRIM(
        P.BSCHEMA)||'.'||P.BNAME||'2' AS SORTCOL
      FROM SYSCAT.PACKAGES P1
        INNER JOIN SYSCAT.ROUTINEDEP R
        ON (P1.PKGNAME = R.BNAME
           AND P1.PKGSCHEMA = R.ROUTINESCHEMA)
        INNER JOIN  SYSCAT.ROUTINES X
        ON (R.ROUTINENAME = X.SPECIFICNAME
          AND R.ROUTINESCHEMA = X.ROUTINESCHEMA)
        INNER JOIN FILTER_PARMS FP
         ON (FP.ROUTINESCHEMA  = X.ROUTINESCHEMA 
             AND FP.ROUTINENAME =  X.ROUTINENAME)
        INNER JOIN SYSCAT.PACKAGEDEP P
        ON (P1.UNIQUE_ID = P.UNIQUE_ID
        AND P.PKGNAME = R.BNAME
        AND P.PKGSCHEMA = R.ROUTINESCHEMA)
       
        INNER JOIN SYSCAT.TABLES TAB
        ON (P.BSCHEMA = TAB.TABSCHEMA
            AND P.BNAME = TAB.TABNAME
             AND P.BTYPE IN ( 'T','N','S','U','W'))
    UNION
      --INDEXES
    SELECT ' ' AS STOREDPROC , '            INDEX' AS TEXTX ,RTRIM(P.BSCHEMA)
        ||'.'||P.BNAME AS OBJECTNAME ,RTRIM(CHAR(IND.FULLKEYCARD)) AS CARD
               ,'STATS:'||COALESCE(CHAR(DATE(IND.STATS_TIME))||'*'||CHAR(TIME(IND.STATS_TIME)),'NO STATS')
               AS BIND_STATS_CREATE_TIME 
        ,CASE
          WHEN IND.FULLKEYCARD = -1
          THEN '*NO INDEXSTATS'
          WHEN IND.STATS_TIME > P1.LAST_BIND_TIME THEN '*PROC BIND < STATS '
                  ELSE ''
          END 
               ||CASE
          WHEN IND.STATS_TIME < TAB.STATS_TIME - 10 SECONDS
            THEN '** IND.STATS_TIME < TAB.STATS_TIME'
          ELSE '' END AS FLAG 
                ,RTRIM(X.ROUTINESCHEMA)||X.ROUTINENAME||'2'||RTRIM(IND.TABSCHEMA)||'.'||IND.TABNAME||'3'
               AS SORTCOL
      FROM      SYSCAT.PACKAGES P1
        INNER JOIN SYSCAT.ROUTINEDEP R
        ON (P1.PKGNAME = R.BNAME
           AND P1.PKGSCHEMA = R.ROUTINESCHEMA)
        INNER JOIN  SYSCAT.ROUTINES X
        ON (R.ROUTINENAME = X.SPECIFICNAME
          AND R.ROUTINESCHEMA = X.ROUTINESCHEMA)
        INNER JOIN FILTER_PARMS FP
         ON (FP.ROUTINESCHEMA  = X.ROUTINESCHEMA 
             AND FP.ROUTINENAME =  X.ROUTINENAME)
        INNER JOIN SYSCAT.PACKAGEDEP P
        ON (P1.UNIQUE_ID = P.UNIQUE_ID
        AND P.PKGNAME = R.BNAME
        AND P.PKGSCHEMA = R.ROUTINESCHEMA)
        INNER JOIN SYSCAT.INDEXES IND 
         ON (P.BSCHEMA = IND.INDSCHEMA
         AND P.BNAME = IND.INDNAME
        AND  P.BTYPE = 'I')
        INNER JOIN SYSCAT.TABLES TAB
        ON (IND.TABSCHEMA = TAB.TABSCHEMA
        AND IND.TABNAME = TAB.TABNAME)
    UNION
    SELECT ' ' AS STOREDPROC , '        OTHER '||
        CASE
          WHEN XDEP.ROUTINETYPE = 'F' THEN 'FUNCTION '
          WHEN XDEP.ROUTINETYPE = 'M' THEN 'METHOD'
          WHEN XDEP.ROUTINETYPE = 'P' THEN 'STORED PROC'
          ELSE ' ?'  END AS TEXTX
               ,RTRIM(XDEP.ROUTINESCHEMA)||'.'||XDEP.ROUTINENAME AS OBJECTNAME 
               ,' ' AS CARD 
               ,'CREATED:'||CHAR(DATE(XDEP.CREATE_TIME))
                 AS BIND_STATS_CREATE_TIME
               ,CASE 
                 WHEN XDEP.ROUTINETYPE = 'P' 
                 THEN CASE
                      WHEN NOT EXISTS
                      (SELECT 1
                        FROM FILTER_PARMS FP
                WHERE  FP.ROUTINESCHEMA  =  XDEP.ROUTINESCHEMA
                   AND FP.ROUTINENAME    =  XDEP.ROUTINENAME)
               THEN  ','||'('||''''||RTRIM(XDEP.ROUTINESCHEMA)
                        ||''''||','||''''||RTRIM(XDEP.ROUTINENAME)
                        ||''''||')' 
               ELSE ' ' END
                 ELSE ' ' END AS FLAG  --('CH','GET_NEXTID_P') 
        ,RTRIM(X.ROUTINESCHEMA)||X.ROUTINENAME||'3' AS SORTCOL
      FROM SYSCAT.PACKAGES P1
        INNER JOIN SYSCAT.ROUTINEDEP R
        ON (P1.PKGNAME = R.BNAME
           AND P1.PKGSCHEMA = R.ROUTINESCHEMA)
        INNER JOIN  SYSCAT.ROUTINES X
        ON (R.ROUTINENAME = X.SPECIFICNAME
          AND R.ROUTINESCHEMA = X.ROUTINESCHEMA)
        INNER JOIN FILTER_PARMS FP
         ON (FP.ROUTINESCHEMA  = X.ROUTINESCHEMA 
             AND FP.ROUTINENAME =  X.ROUTINENAME)
        INNER JOIN SYSCAT.PACKAGEDEP P
        ON (P1.UNIQUE_ID = P.UNIQUE_ID
        AND P.PKGNAME = R.BNAME
        AND P.PKGSCHEMA = R.ROUTINESCHEMA)
       INNER JOIN SYSCAT.ROUTINES XDEP
       ON (P.BNAME = XDEP.SPECIFICNAME
        AND P.BSCHEMA = XDEP.ROUTINESCHEMA)
        UNION
      --OTHER
     SELECT ' ' AS STOREDPROC , '        OTHER '||
        CASE
          WHEN P.BTYPE = 'A' THEN 'ALIAS'
          WHEN P.BTYPE = 'B' THEN 'TRIGGER'
          WHEN P.BTYPE = 'D' THEN 'SERVER DEF'
          WHEN P.BTYPE = 'F' THEN 'PROC/FUNC'
          WHEN P.BTYPE = 'I' THEN 'INDEX'
          WHEN P.BTYPE = 'M' THEN 'FUNCTION MAP'
          WHEN P.BTYPE = 'N' THEN 'NICKNAME'
          WHEN P.BTYPE = 'O' THEN 'PRIVILEGE DEP'
          WHEN P.BTYPE = 'P' THEN 'PAGE SIZE'
          WHEN P.BTYPE = 'R' THEN 'STRUCT TYPE '
          WHEN P.BTYPE = 'S' THEN 'MQTABLE'
          WHEN P.BTYPE = 'T' THEN 'TABLE'
          WHEN P.BTYPE = 'U' THEN 'TYPED TABLE'
          WHEN P.BTYPE = 'V' THEN 'VIEW'
          WHEN P.BTYPE = 'W' THEN 'TYPED VIEW'
          WHEN P.BTYPE = 'Q' THEN 'Sequence object'
          WHEN P.BTYPE = 'G' THEN 'Global temporary table'
          ELSE ' ?'||RTRIM(P.BTYPE) END AS TEXTX 
                 ,RTRIM(P.BSCHEMA)||'.'||P.BNAME AS OBJECTNAME 
                 ,' ' AS CARD
                 , '' AS BIND_STATS_CREATE_TIME ,' '
        AS FLAG ,RTRIM(X.ROUTINESCHEMA)||X.ROUTINENAME||'4' AS SORTCOL
      FROM SYSCAT.PACKAGES P1
        INNER JOIN SYSCAT.ROUTINEDEP R
        ON (P1.PKGNAME = R.BNAME
           AND P1.PKGSCHEMA = R.ROUTINESCHEMA)
        INNER JOIN  SYSCAT.ROUTINES X
        ON (R.ROUTINENAME = X.SPECIFICNAME
          AND R.ROUTINESCHEMA = X.ROUTINESCHEMA)
        INNER JOIN FILTER_PARMS FP
         ON (FP.ROUTINESCHEMA  = X.ROUTINESCHEMA 
             AND FP.ROUTINENAME =  X.ROUTINENAME)
        INNER JOIN SYSCAT.PACKAGEDEP P
        ON (P1.UNIQUE_ID = P.UNIQUE_ID
        AND P.PKGNAME = R.BNAME
        AND P.PKGSCHEMA = R.ROUTINESCHEMA)
      WHERE P.BTYPE NOT IN ( 'I','F','T','S','N','U','W')
       ) 
SELECT STOREDPROC,TEXTX,OBJECTNAME,CARD,BIND_STATS_CREATE_TIME,FLAG
  FROM ROUTINEDEP
  ORDER BY SORTCOL
  WITH UR;
-----------------------------------------------------------------------
-- FLAG column show possible stats mismatch where a
-- procs bind time < stats time for Tables/Indexes
--
-- Also review CARD, which shows row counts for tables based on stats
-- is that what you expect?
--
-- Flag column also generates values clause that you can cut and paste
-- into the values clause to review called procs using this query
------------------------------------------------------------------------

And a few things Fred suggested to be mindful of... "It should work with DB2luw and has been tested in AIX v9.5-10.2. There is one major caveat. The query assumes one proc has only one specific name i.e., it only works for non-overloaded procs. It also uses at least one deprecated column, ROUTINENAME in ROUTINEDEP. Changing the proc to use specific names wouldn’t be a big deal."

Have fun, and if you use this or have any suggested enhancements, please leave a comment below.