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,
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
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.
.jpg)
