|
View previous topic :: View next topic
|
| Author |
Message |
inforajesh
New User
Joined: 09 Dec 2020
Posts: 20
Location: India
|
|
|
|
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 |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2272
Location: USA
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
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 |
|
|
inforajesh
New User
Joined: 09 Dec 2020
Posts: 20
Location: India
|
|
|
|
| Thank you for the response |
|
| Back to top |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
Hi Rajesh,
Curious Are you trying to build a CRUD report? |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
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 |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2272
Location: USA
|
|
|
|
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 |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3109
Location: NYC,USA
|
|
|
|
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 |
|
|
|
|
  |
All times are GMT + 6 Hours |
|
