What should be coded in the SQL (SPUFI Input) to get the output in next sequence line ? ( Tried CHR(10) and CHR(13) no luck
For example, I have generated GRANT statement which is like below
GRANT DISPLAYDB, IMAGCOPY, LOAD, RECOVERDB, REORG, STARTDB, STATS, STOPDB ON DATABASE X TO RAJ;
when executing it, since the line length exceeds I am getting errors. Could you please advise what can be done ?
This is my query which is created fetch the list of databases for which GRANT has to be given..
SELECT DISTINCT 'GRANT DISPLAYDB, IMAGCOPY, LOAD, RECOVERDB, REORG, STARTDB, STATS, STOPDB '||
' ON DATABASE '||A.NAME||' TO RAJ;'
FROM SYSIBM.SYSDATABASE A
WHERE CREATOR LIKE 'XX%'
AND A.NAME IN
(SELECT B.NAME
FROM SYSIBM.SYSDBAUTH B
WHERE GRANTOR = 'XX'
AND ( (GRANTEE = 'XX' AND REORGAUTH = 'Y')
OR (GRANTEE = 'XX' AND DISPLAYDBAUTH = 'Y')
)
)
WITH UR;
Sorry, It is not displaying any error now. But I am not getting anything displayed after STARTDB
Expected output :
GRANT DISPLAYDB, IMAGCOPY, LOAD, RECOVERDB,REORG, STARTDB, STATS, STOPDB ON DATABASE XXXX TO RAJ;'
Present Output :
GRANT DISPLAYDB, IMAGCOPY, LOAD, RECOVERDB, REORG, STARTDB (Spaces)
Query :
SELECT DISTINCT 'GRANT DISPLAYDB, IMAGCOPY, LOAD, RECOVERDB,
REORG, STARTDB, STATS, STOPDB '||
'ON DATABASE '||A.NAME||' TO RAJ;'
FROM SYSIBM.SYSDATABASE A
WHERE CREATOR LIKE 'KB%'
AND A.NAME IN
(SELECT B.NAME
FROM SYSIBM.SYSDBAUTH B
WHERE GRANTOR = 'KB'
AND ( (GRANTEE = 'TB1' AND REORGAUTH = 'Y')
OR (GRANTEE = 'TBL' AND DISPLAYDBAUTH = 'Y')
)
)
WITH UR;
Welcome!
Please use code tags going forward and try this..
Code:
SELECT
DISTINCT 'GRANT DISPLAYDB, IMAGCOPY, LOAD, RECOVERDB,',
'REORG, STARTDB, STATS, STOPDB ON DATABASE',
A.NAME||' TO RAJ;'
FROM SYSIBM.SYSDATABASE A
WHERE CREATOR LIKE 'KB%'
AND A.NAME IN
(SELECT B.NAME
FROM SYSIBM.SYSDBAUTH B
WHERE GRANTOR = 'KB'
AND ( (GRANTEE = 'TB1' AND REORGAUTH = 'Y')
OR (GRANTEE = 'TBL' AND DISPLAYDBAUTH = 'Y')
)
)
WITH UR;
