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;

Like this? Consider joining my mailing list, or at least using my affiliate link when buying from Amazon πŸ™‚

You or someone you know looking to buy or sell?

Quick apt-get Tutorial

List installed packages…

dpkg --get-selections | grep -v deinstall

List installed packages (but filter based on name)…

dpkg --get-selections | grep -v deinstall | grep filter

Install a package…

sudo apt-get install the-package-name

Uninstall/remove a package…

sudo apt-get remove the-package-name

Update package lists/dependencies (does not actually install anything)…

sudo apt-get update

Apply updates to existing packages based on an “update” call (without removing anything)…

sudo apt-get upgrade

Apply updates to existing packages based on an “update” call and remove obsolete packages…

sudo apt-get dist-upgrade
Like this? Consider joining my mailing list, or at least using my affiliate link when buying from Amazon πŸ™‚

You or someone you know looking to buy or sell?

Force values into DB2 GENERATED ALWAYS fields

If doing a conversion of data where you need to force a value into a field that is GENERATED ALWAYS after the conversion, you can still load values into the generating and generated fields.

In your conversion load process, use the “generatedoverride” modifier (ex: LOAD FROM C012 OF CURSOR MODIFIED BY generatedoverride MESSAGES $l_sLog). This will allow you to force values into GENERATED ALWAYS fields.

Once your load completes, issue one of the following statements to take your table out of the “set integrity pending” state:

SET INTEGRITY FOR table-name GENERATED COLUMN IMMEDIATE UNCHECKED; 
-- will not verify the values you supplied in the load

SET INTEGRITY FOR table-name IMMEDIATE CHECKED; 
-- will verify the values you supplied in the load

Ref: http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/c0004592.htm

Like this? Consider joining my mailing list, or at least using my affiliate link when buying from Amazon πŸ™‚

You or someone you know looking to buy or sell?

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.

Like this? Consider joining my mailing list, or at least using my affiliate link when buying from Amazon πŸ™‚

You or someone you know looking to buy or sell?

Quick CRON Tutorial

The fastest way to setup scheduled tasks in Linux is with cron. To list your cron tasks, run:

sudo crontab -l

To modify your cron tasks, run:

sudo crontab -e

For help generating the string for when your tasks should run, just google around for “cron calculator”, and remember to test, test, test.

Like this? Consider joining my mailing list, or at least using my affiliate link when buying from Amazon πŸ™‚

You or someone you know looking to buy or sell?

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;

Like this? Consider joining my mailing list, or at least using my affiliate link when buying from Amazon πŸ™‚

You or someone you know looking to buy or sell?

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;
Like this? Consider joining my mailing list, or at least using my affiliate link when buying from Amazon πŸ™‚

You or someone you know looking to buy or sell?

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? Like this? Consider joining my mailing list, or at least using my affiliate link when buying from Amazon πŸ™‚

You or someone you know looking to buy or sell?

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

Like this? Consider joining my mailing list, or at least using my affiliate link when buying from Amazon πŸ™‚

You or someone you know looking to buy or sell?

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

Like this? Consider joining my mailing list, or at least using my affiliate link when buying from Amazon πŸ™‚

You or someone you know looking to buy or sell?