5 Useful IBM 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