Tag Archives: SQL

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.

DB2 Get Current MAX value for all IDENTITY Columns

If you’ve ever dealt with DB2 IDENTITY columns chances are you may have wanted to know what the currently assigned max value is for all of the IDENTITY columns in the DB. If so, you can use the following SQL to generate the SQL that will give you your answer. Hope it helps!

select 'select '''||trim(t.tabname)||''', '''||trim(c.colname)||''', max('||trim(c.colname)||') from '||trim(t.tabschema)||'.'||trim(t.tabname)||' for read only with ur;'
from syscat.sequences s join syscat.tables t on
(s.seqschema=t.tabschema and s.create_time=t.create_time)
join syscat.columns c on
(t.tabschema=c.tabschema and t.tabname=c.tabname)
where s.seqname like 'SQL%'
and c.identity='Y'

DB2: How to find records with non-printable characters (SQLSTATE 01517)

If you run a select statement and get the following error…

"SQLSTATE 01517: A character that could not be converted was 
replaced with a substitute character."

… you can use the TRANSLATE function to strip away printable chars, and compare that to a zero length string like so…

SELECT * 
FROM XX.TBL 
WHERE TRANSLATE(UPPER(FIELD_X),'','ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890!@#$%^&*()-=+/\{}[];:.,<>? ') <> '' 
FOR READ ONLY WITH UR;

How to concatenate multiple columns in a DB2 select statement to return a single column

Use “||” like so…

SELECT LastName||', '||FirstName 
FROM [schema].[table]

If one of the columns is numeric you’ll probably want to convert to CHAR and TRIM like so…

SELECT LastName||', '||FirstName||' - '||TRIM(CHAR(EmplId))
FROM [schema].[table]

Extract and insert tables (or portions of them) in DB2 – similar to BCP for Sybase and SQL Server

You’ll need to be in the DB2 Command Line for this. Also, the version of your DB2 client should match the version of the DB or be higher than the DB version (i.e. no using a version 7 client to go after a version 8 DB).

To get data out of a db2 table:

db2 "export to [file_name] of [file_type] select * from [table]"

[file_name] is a fully qualified name of the ouput file
[file_type] is either del (delimited) or ixf (db2 native format).
[table] is a fully qualified table, nickname, or view

For example:

db2 "export to /home/user/people.ixf of ixf select * from db2dba.people"

To import data into a db2 table:

db2 "import from [file_name] of [file_type] commitcount [value] insert into [table]"

For example:

db2 "import from /home/user/people.ixf of ixf commitcount 10000 insert into db2dba.people"

commitcount is optional but should be used to avoid filling up the transaction log. I generally use 10k to 20k.

Notice on the export command you use SQL syntax to get the data. You could use a where clause. You can also select and insert specific columns (Strongly recommended).

The import command allows you to insert into, replace into (replaces existing data), and create into (creates a new table).

You may wish to consult the DB2 Command Reference manual for more information

DB2 Duplicate Key Debugging

If you’ve ever gotten a DB2 error like this…

SQL0803N  One or more values in the INSERT statement, UPDATE statement, 
or foreign key update caused by a DELETE statement are not valid because 
the primary key, unique constraint or unique index identified by "1" 
constrains table "XX.TABLE_NAME" from having duplicate values for 
the index key.  SQLSTATE=23505

You may be asking yourself, but exactly which unique index is causing the problem?

Use the info from the error along with this query to find out:

SELECT *
FROM   SYSCAT.INDEXES
WHERE  IID = 1
       AND TABSCHEMA = 'XX'
       AND TABNAME = 'TABLE_NAME';

Of course you will want to replace the WHERE clause conditions with your values.

Generate a GUID or UUID in DB2

Want to generate the equivalent of a GUID or UUID in DB2? The closest you’ll get is with GENERATE_UNIQUE(), but beware that you need to do some conversion on it as the value is “BIT DATA” and not character data. To put the value into a typical CHAR or VARCHAR field (like you would with a java UUID), use this…

select TRIM(CHAR(HEX(GENERATE_UNIQUE()))) from sysibm.sysdummy1;

I should probably also mention that there is the IDENTITY and SEQUENCE, but the GENERATE_UNIQUE() function is the closest you’ll get to a GUID or UUID.