DEV Community

Bipin Radhakrishnan
Bipin Radhakrishnan

Posted on • Originally published at blog.bipinr.com on

2 1

5 Useful IBM DB2 Queries

5 Useful DB2 Queries

Here are some IBM DB2 queries that can be very useful.

Query to identify Stores Procedures that is using a particular table

/*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
view raw SPTables.sql hosted with ❤ by GitHub

Identify Schema used by a Stored Procedures

/*Schema Of Stored Procedure*/
SELECT *
FROM SYSIBM.SYSROUTINEAUTH
WHERE
specificname LIKE 'SP_Name'
AND grantee NOT IN ('@B00120','@C00120')
view raw schematable.sql hosted with ❤ by GitHub

Identify Primary Key of a table

/*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'
view raw PrimaryKey.sql hosted with ❤ by GitHub

Identify Foreign key of a table

/*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'
view raw Foreignkey.sql hosted with ❤ by GitHub

View used by table

/*Schema Of Stored Procedure*/
SELECT
CREATOR
FROM SYSIBM.SYSVIEWS
WHERE NAME='Table_Name'
view raw viewtable.sql hosted with ❤ by GitHub

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Please leave a ❤️ or a friendly comment on this post if you found it helpful!

Okay