Tag Archives: SQL

Update modification timestamp on DB2 table automatically

Ever want to update your modification timestamp field any time an update is made to the row without having to add application logic? Well, DB2 makes it relatively painless…

alter table XX.MY_TABLE add column last_updated_ts timestamp not null
generated by default for each row on update as row change timestamp

Check out these resources for additional information…

  • https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0051498.html
  • https://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000888.html
Please remember to subscribe to the newsletter to stay up to date!

You or someone you know looking to buy or sell?
Disclaimer: Thoughts and opinions are my own, and do not reflect the views of any employer, family member, friend, or anyone else. Some links may be affiliate links, but I don't link to anything I don't use myself. You would think this should be self evident these days, but apparently not...

DB2 Ownership Transfer

If you ever get an error in DB2 along the lines of…

SQLCODE=-727, SQLSTATE=56098, SQLERRMC=1;-551;42501;MARKJ|EXECUTE|XX.PROC_P

…but in your code are executing the proc as another user (say “USR1”) instead of MARKJ, it may be that MARKJ created the proc and until recently still had the necessary access for executing everything in XX.PROC_P. Subsequently permissions were “fixed” and now stuff starts blowing up all over because MARKJ no longer has access. How to fix? Well, like all things I’m sure there are a million ways to do it, but the easiest in this situation was to have MARKJ transfer the ownership on everything he owned. To determine that, the following SQL was run to generate the commands, then the commands were run…


SELECT 
CASE ROUTINETYPE WHEN 'F' THEN
    'TRANSFER OWNERSHIP OF FUNCTION  '||
    RTRIM(ROUTINESCHEMA)||'.'|| ROUTINENAME ||
    ' TO USER DB2DBA PRESERVE PRIVILEGES; '
ELSE
    'TRANSFER OWNERSHIP OF PROCEDURE  '||
    RTRIM(ROUTINESCHEMA)||'.'|| ROUTINENAME ||
    ' TO USER DB2DBA PRESERVE PRIVILEGES; '
END
FROM SYSCAT.ROUTINES 
WHERE OWNER = 'MARKJ' 
WITH UR;

Please remember to subscribe to the newsletter to stay up to date!

You or someone you know looking to buy or sell?

Disclaimer: Thoughts and opinions are my own, and do not reflect the views of any employer, family member, friend, or anyone else. Some links may be affiliate links, but I don't link to anything I don't use myself. You would think this should be self evident these days, but apparently not...

DB2 Stats vs Actual

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.
Please remember to subscribe to the newsletter to stay up to date!

You or someone you know looking to buy or sell?

Disclaimer: Thoughts and opinions are my own, and do not reflect the views of any employer, family member, friend, or anyone else. Some links may be affiliate links, but I don't link to anything I don't use myself. You would think this should be self evident these days, but apparently not...

DB2 Recursive Query

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.Please remember to subscribe to the newsletter to stay up to date!

You or someone you know looking to buy or sell?

Disclaimer: Thoughts and opinions are my own, and do not reflect the views of any employer, family member, friend, or anyone else. Some links may be affiliate links, but I don't link to anything I don't use myself. You would think this should be self evident these days, but apparently not...

How Much Space Is Being Used by 1 LOB / CLOB / BLOB in DB2

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;

Please remember to subscribe to the newsletter to stay up to date!

You or someone you know looking to buy or sell?

Disclaimer: Thoughts and opinions are my own, and do not reflect the views of any employer, family member, friend, or anyone else. Some links may be affiliate links, but I don't link to anything I don't use myself. You would think this should be self evident these days, but apparently not...

Get Space Used by LOB / CLOB / BLOB in DB2

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 linkPlease remember to subscribe to the newsletter to stay up to date!

You or someone you know looking to buy or sell?

Disclaimer: Thoughts and opinions are my own, and do not reflect the views of any employer, family member, friend, or anyone else. Some links may be affiliate links, but I don't link to anything I don't use myself. You would think this should be self evident these days, but apparently not...

Generate test data to help debug DB2 SQL

You can easily create test data to help debug SQL statements:

with data (key, value) as
(   values ('key1', 'a')
         , ('key2', null)
         , ('key3', 'z')
)
select 'min', min(coalesce(value,'')) from data
union
select 'max', max(coalesce(value,'')) from data

Please remember to subscribe to the newsletter to stay up to date!

You or someone you know looking to buy or sell?

Disclaimer: Thoughts and opinions are my own, and do not reflect the views of any employer, family member, friend, or anyone else. Some links may be affiliate links, but I don't link to anything I don't use myself. You would think this should be self evident these days, but apparently not...

How to Tell What Objects Reference a DB2 Table

This “little” 🙂 query will do it for you. Just change the where clause at the bottom…

SELECT Type_X AS "Type" 
, RefObj_X AS "Referencing Object" 
FROM ( 
SELECT RTRIM(SYSPROC.PROCSCHEMA)||'.'||RTRIM(SYSPROC.PROCNAME) AS RefObj_X 
, RTRIM(SYSPROC.PROCSCHEMA) AS Schema_X 
, RTRIM(SYSPROC.PROCNAME) AS Obj_X 
, RTRIM(PDEP.BSCHEMA)||'.'||RTRIM(PDEP.BNAME) AS BaseObj_X 
, 'Proc' AS Type_X 
FROM SYSCAT.PACKAGEDEP PDEP 
INNER JOIN SYSIBM.sysdependencies SYSDEP 
ON SYSDEP.BSCHEMA = PDEP.BSCHEMA 
AND SYSDEP.BNAME = PDEP.PKGNAME 
INNER JOIN SYSIBM.sysprocedures SYSPROC 
ON SYSDEP.DNAME = SYSPROC.SPECIFICNAME 

UNION ALL 

SELECT RTRIM(TRIGSCHEMA)||'.'||RTRIM(TRIGNAME) AS RefObj_X 
, RTRIM(TRIGSCHEMA) AS Schema_X 
, RTRIM(TRIGNAME) AS Obj_X 
, RTRIM(BSCHEMA)||'.'||RTRIM(BNAME) AS BaseObj_X 
, 'Trigger' AS Type_X 
FROM SYSCAT.TRIGDEP 

UNION ALL 

SELECT RTRIM(VIEWSCHEMA)||'.'||RTRIM(CAST((VIEWNAME) AS CHAR(126))) AS RefObj_X 
, RTRIM(VIEWSCHEMA) AS Schema_X 
, RTRIM(CAST((VIEWNAME) AS CHAR(126))) AS Obj_X 
, RTRIM(BSCHEMA)||'.'||RTRIM(BNAME) AS BaseObj_X 
, CASE DTYPE WHEN 'S' 
THEN 'SUMMARY TABLE' 
ELSE 'View' 
END AS Type_X 
FROM SYSCAT.VIEWDEP 

UNION ALL 

SELECT RTRIM(INDSCHEMA)||'.'||RTRIM(INDNAME) AS RefObj_X 
, RTRIM(INDSCHEMA) AS Schema_X 
, RTRIM(INDNAME) AS Obj_X 
, RTRIM(TABSCHEMA)||'.'||RTRIM(TABNAME) AS BaseObj_X 
, 'Index' AS Type_X 
FROM SYSCAT.INDEXES 

UNION ALL 

SELECT RTRIM(TABSCHEMA)||'.'||RTRIM(TABNAME) AS RefObj_X 
, RTRIM(TABSCHEMA) AS Schema_X 
, RTRIM(TABNAME) AS Obj_X 
, RTRIM(BASE_TABSCHEMA)||'.'||RTRIM(BASE_TABNAME) AS BaseObj_X 
, 'Alias' AS Type_X 
FROM SYSCAT.TABLES 
WHERE TYPE = 'A' 

UNION ALL 

SELECT DISTINCT RTRIM(CAST(RTRIM(TABSCHEMA) AS VARCHAR(126))) || '.' || RTRIM(TABNAME) AS RefObj_X 
, RTRIM(CAST(RTRIM(TABSCHEMA) AS VARCHAR(126))) AS Schema_X 
, RTRIM(TABNAME) AS Obj_X 
, RTRIM(REFTABSCHEMA)||'.'||RTRIM(REFTABNAME) AS BaseObj_X 
, 'Table' AS Type_X 
FROM SYSCAT.REFERENCES 
) AS RefTbl 
WHERE BaseObj_X = 'XX.MY_TABLE' 
ORDER BY 
Type_X 
, RefObj_X;

Please remember to subscribe to the newsletter to stay up to date!

You or someone you know looking to buy or sell?

Disclaimer: Thoughts and opinions are my own, and do not reflect the views of any employer, family member, friend, or anyone else. Some links may be affiliate links, but I don't link to anything I don't use myself. You would think this should be self evident these days, but apparently not...

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;

Please remember to subscribe to the newsletter to stay up to date!

You or someone you know looking to buy or sell?

Disclaimer: Thoughts and opinions are my own, and do not reflect the views of any employer, family member, friend, or anyone else. Some links may be affiliate links, but I don't link to anything I don't use myself. You would think this should be self evident these days, but apparently not...

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) || '%'

Please remember to subscribe to the newsletter to stay up to date!

You or someone you know looking to buy or sell?

Disclaimer: Thoughts and opinions are my own, and do not reflect the views of any employer, family member, friend, or anyone else. Some links may be affiliate links, but I don't link to anything I don't use myself. You would think this should be self evident these days, but apparently not...