Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Some useful DB2 Queries
/*Foreign Key Of Table*/
SELECT
A.TBNAME,
REFTBNAME,
COLNAME
FROM SYSIBM.SYSRELS A,
SYSIBM.SYSFOREIGNKEYS B
WHERE A.RELNAME = B.RELNAME
AND A.TBNAME = B.TBNAME
AND A.TBNAME='Table_Name'
/*Primary Key of Table*/
SELECT A.COLNAME,
B.TBNAME
FROM SYSIBM.SYSKEYS A,
SYSIBM.SYSINDEXES B
WHERE B.NAME = A.IXNAME
AND B.TBNAME='Table_Name'
/*Schema Of Stored Procedure*/
SELECT *
FROM SYSIBM.SYSROUTINEAUTH
WHERE
specificname LIKE 'SP_Name'
AND grantee NOT IN ('@B00120','@C00120')
/*Find all stored procedure using a table*/
SELECT
GRANTOR,
GRANTEE,
SELECTAUTH,
INSERTAUTH,
UPDATEAUTH,
DELETEAUTH
FROM SYSIBM.SYSTABAUTH
WHERE
GRANTEE LIKE 'SP_Prefix%'
AND TTNAME LIKE '%table_name%'
AND GRANTOR IN ('table_schema')
ORDER BY GRANTEE ASC
/*Schema Of Stored Procedure*/
SELECT
CREATOR
FROM SYSIBM.SYSVIEWS
WHERE NAME='Table_Name'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment