IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SYSIBM Tables Query


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
inforajesh

New User


Joined: 09 Dec 2020
Posts: 12
Location: India

PostPosted: Thu Mar 11, 2021 3:46 pm
Reply with quote

Hello all,

I have a requirement to fetch the following information from SYSIBM tables. Input will be a table name and as an OUTPUT it is expected to display the below columns at one go,

Code:
DATABASE_NAME   
TABLESPACE_NAME
TABLE_NAME       
TABLE_CREATOR
VIEW_NAME       
VIEW_CREATOR 
GRANTEE         
DELETEAUTH                       
INSERTAUTH                         
SELECTAUTH                       
UPDATEAUTH                         
COLLID         
PACKAGE_NAME
PACKAGE_OWNER   
EX_GRANTEE     
EX_RIGHT                 
PLAN_NAME     
PLAN_CREATOR   
PLAN_GRANTEE 
PLAN_RIGHT   


This is the query created by me, However I am getting -905 error

Code:
SELECT                                   
SUBSTR(T.DBNAME,1,08) AS DATABASE_NAME, 
SUBSTR(T.TSNAME,1,08) AS TABLESPACE_NAME,
SUBSTR(A.BNAME,1,08) AS TABLE_NAME,     
SUBSTR(A.BCREATOR,1,08) AS TABLE_CREATOR,
SUBSTR(A.DNAME,1,08) AS VIEW_NAME,       
SUBSTR(A.DCREATOR,1,08) AS VIEW_CREATOR,
SUBSTR(B.GRANTEE,1,08) AS GRANTEE,       
B.DELETEAUTH AS D,
B.INSERTAUTH AS I,
B.SELECTAUTH AS S,
B.UPDATEAUTH AS U,
SUBSTR(C.DCOLLID,1,08) AS COLLID,     
SUBSTR(C.DNAME,1,08) AS PACKAGE_NAME, 
SUBSTR(C.DOWNER,1,08) AS PACKAGE_OWNER,
SUBSTR(X.GRANTEE,1,08) AS EX_GRANTEE, 
X.EXECUTEAUTH AS EX_RIGHT,             
SUBSTR(D.PLANNAME,1,08) AS PLAN_NAME, 
SUBSTR(E.CREATOR,1,08) AS PLAN_CREATOR,
SUBSTR(Y.GRANTEE,1,08) AS PLAN_GRANTEE,
Y.EXECUTEAUTH AS PLAN_RIGHT   
FROM SYSIBM.SYSVIEWDEP  A         
JOIN SYSIBM.SYSTABAUTH  B         
ON A.BCREATOR = B.TCREATOR         
AND A.DTYPE IN ('V')               
AND A.BTYPE IN ('T')               
AND B.GRANTEE >= ' '               
LEFT OUTER JOIN SYSIBM.SYSTABLES  T
ON T.NAME = A.BNAME
AND T.CREATOR = A.DCREATOR
LEFT OUTER JOIN SYSIBM.SYSPACKDEP  C   
ON C.BQUALIFIER  = A.DCREATOR         
AND C.BNAME = A.BNAME                 
AND C.BTYPE IN ('T')                   
AND B.GRANTEE=C.DOWNER                 
LEFT OUTER JOIN SYSIBM.SYSPACKLIST D   
ON D.LOCATION = '*'                   
AND D.NAME = C.DNAME                   
LEFT OUTER JOIN SYSIBM.SYSPACKAGE P   
 ON P.COLLID = C.DCOLLID               
 AND P.NAME = C.DNAME                 
LEFT OUTER JOIN SYSIBM.SYSPLAN     E   
 ON E.NAME = D.PLANNAME               
AND E.NAME >= ' '   
LEFT OUTER JOIN SYSIBM.SYSPACKAUTH X
 ON X.LOCATION = ' '               
 AND X.COLLID  = C.DCOLLID         
 AND X.NAME = C.DNAME               
 AND X.EXECUTEAUTH <> ' '           
LEFT OUTER JOIN SYSIBM.SYSPLANAUTH Y
 ON Y.NAME = E.NAME                 
 AND Y.GRANTOR >= ' '               
 AND X.GRANTEE = Y.GRANTEE         
 AND Y.GRANTORTYPE >= ' '           
 AND Y.EXECUTEAUTH <> ' '           
WHERE A.BNAME = 'XXXXXXXX'   --> Hardcoded Tablename       
WITH UR;


Can someone tell me any alternate way to achieve this requirement ?
Coded for you
Back to top
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1009

PostPosted: Thu Mar 11, 2021 7:10 pm
Reply with quote

Follow these steps:

1. Learn how to use code tags

2. RTFM

3. Read about SQLCODE -905

4. Try to think a little bit

5. Start from the most simple examples, like SELECT * FROM single_table;

6. If doesn’t help, then restart from #2.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2632
Location: NYC,USA

PostPosted: Thu Mar 11, 2021 8:27 pm
Reply with quote

You can run it in a batch job , if its timeout in spufi or any sql editor.

Also, review all the joins, break it down to see which join is causing -905 and add more cardinality to it, consult DBA's at site.

Please use Code tags going forward.
Back to top
View user's profile Send private message
inforajesh

New User


Joined: 09 Dec 2020
Posts: 12
Location: India

PostPosted: Sun Mar 14, 2021 10:24 pm
Reply with quote

Thank you for the response
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1586
Location: Andromeda Galaxy

PostPosted: Sun Mar 21, 2021 2:04 am
Reply with quote

Hi Rajesh,

Curious Are you trying to build a CRUD report?
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2632
Location: NYC,USA

PostPosted: Mon Mar 22, 2021 7:26 pm
Reply with quote

It is a CRUD Report.
Code:
B.DELETEAUTH AS D,
B.INSERTAUTH AS I,
B.SELECTAUTH AS S,
B.UPDATEAUTH AS U,


You don't need this left join as nothing is referenced from this in the SELECT.
Code:
LEFT OUTER JOIN SYSIBM.SYSPACKAGE P   
 ON P.COLLID = C.DCOLLID               
 AND P.NAME = C.DNAME
Back to top
View user's profile Send private message
sergeyken
Warnings : 2

Senior Member


Joined: 29 Apr 2008
Posts: 1009

PostPosted: Mon Mar 22, 2021 7:45 pm
Reply with quote

As I said before: never run the most complex code as your starting point. Neither in SQL, nor anywhere else.

If something is not working as you expected, mandatory split it into simple parts, or fragments, and run them separately to verify, that each part REALLY produces what you expected. Next, try to combine those parts into more complex code.

In your case I strongly recommend you to start from
SELECT * FROM table WHERE ... [LIMIT 50]
If you followed my advice, then highly likely you would resolve your issue by yourself, without whining at this forum.
Back to top
View user's profile Send private message
Pandora-Box

Global Moderator


Joined: 07 Sep 2006
Posts: 1586
Location: Andromeda Galaxy

PostPosted: Tue Mar 23, 2021 12:51 am
Reply with quote

Why beat around the bush so much if the requirement is CRUD

All you need is just SYSTABAUTH, That has all the needed info
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2632
Location: NYC,USA

PostPosted: Tue Mar 23, 2021 1:20 am
Reply with quote

I agree. CRUD can be derived just from SYSTABAUTH.

The following join is abogus. its one to many relationship. SYSIBM.SYSVIEWDEP is not needed at all.
Code:
FROM SYSIBM.SYSVIEWDEP  A         
JOIN SYSIBM.SYSTABAUTH  B         
ON A.BCREATOR = B.TCREATOR         
AND A.DTYPE IN ('V')               
AND A.BTYPE IN ('T')               
AND B.GRANTEE >= ' '


Just use SYSTABAUTH and any columns that is not in SYSTABAUTH but you need it only use left outer join for them.

Let us wait for TS to respond, I hope he must be reading these comments.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Cobol-db2 : SQL query is running for ... DB2 4
No new posts Reading subsequent rows in a query. DB2 12
No new posts Discrepancy b/w SYSIBM tables and BMC... DB2 0
No new posts Column names in SYSIBM tables DB2 5
No new posts Need Help with on of the coalesce query DB2 4
Search our Forums:

Back to Top