DB2 LUW: Useful queries for L1/L2 support

(1) Table (Vs) Tablespace mapping
Select NAME , TBSPACE from sysibm.systables where NAME like ‘%RAVI1%’

(2) List existing schemas
select schemaname from syscat.schemata

(3) Table details
SELECT SUBSTR(TABSCHEMA, 1, 20) TABSCHEMA, SUBSTR(TABNAME, 1, 20) TABNAME, SUBSTR(OWNER, 1, 20) OWNER, TYPE, STATUS FROM syscat.tables WHERE TABNAME like ‘%TAB_CUST_CONV%’

(4) Check for a specific user permissions on a specific table
select * from sysibm.systabauth where TTNAME=’TABPART’ and GRANTEE=’RAVIK’

Where, TTNAME: Table Name
GRANTEE: User to which you are looking to check permissions

(5) List user privileges
select SUBSTR(GRANTOR,1,10) GRANTOR, GRANTORTYPE, SUBSTR(GRANTEE,1,10) GRANTEE, GRANTEETYPE, SUBSTR(TABSCHEMA, 1, 10) TABSCHEMA, SUBSTR(TABNAME,1,10) TABNAME, CONTROLAUTH, ALTERAUTH, DELETEAUTH, INDEXAUTH, INSERTAUTH, REFAUTH, SELECTAUTH, UPDATEAUTH from syscat.tabauth where grantee = ‘RAVIK’

Remaining … coming soon …!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: