|
View previous topic :: View next topic
|
| Author |
Message |
inforajesh
New User
Joined: 09 Dec 2020
Posts: 20
Location: India
|
|
|
|
Hi all,
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; |
|
| Back to top |
|
 |
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2209
Location: USA
|
|
|
|
What kind of error???  |
|
| Back to top |
|
|
inforajesh
New User
Joined: 09 Dec 2020
Posts: 20
Location: India
|
|
|
|
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; |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2209
Location: USA
|
|
|
|
Do something step by step.
- use the most simple statements, one by one, not put everything into one big mess.
- Try to use simple SELECT <one field> from <one table> LIMIT 10;
- Check the result.
- Then go further, depending on previous results.
Unless you present here your DETAILED report (code, data, messages, output, etc) nobody would give you ANY advice. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3087
Location: NYC,USA
|
|
|
|
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; |
|
|
| Back to top |
|
|
guptae
Moderator
Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India
|
|
|
|
Hello There,
You can split the line to 80 using ICETOOL it works.
| Code: |
//TOOLIN DD *
RESIZE FROM(INPUT) TO(OUTPUT) TOLEN(80) USING(CTL1)
/*
//CTL1CNTL DD *
OUTFIL FNAMES=OUTPUT, |
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3087
Location: NYC,USA
|
|
|
|
| What I suggested works ... This is a Db2 question how does sort helps here? Besides, TS did not show up at all to care and confirm. |
|
| Back to top |
|
|
|
|
|
