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

Next line in SQL


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: 7
Location: India

PostPosted: Wed Dec 09, 2020 8:43 pm
Reply with quote

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 icon_sad.gif

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
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 947
Location: Maryland

PostPosted: Wed Dec 09, 2020 9:01 pm
Reply with quote

What kind of error??? icon_redface.gif
Back to top
View user's profile Send private message
inforajesh

New User


Joined: 09 Dec 2020
Posts: 7
Location: India

PostPosted: Wed Dec 09, 2020 9:46 pm
Reply with quote

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
View user's profile Send private message
sergeyken

Active Member


Joined: 29 Apr 2008
Posts: 947
Location: Maryland

PostPosted: Thu Dec 10, 2020 1:46 am
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Thu Dec 10, 2020 10:42 pm
Reply with quote

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
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1208
Location: Bangalore,India

PostPosted: Thu Feb 11, 2021 5:33 pm
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


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

PostPosted: Thu Feb 11, 2021 7:11 pm
Reply with quote

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
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 Command line placement in DB2I panels TSO/ISPF 2
No new posts File-aid batch how to repeat a line... Compuware & Other Tools 2
No new posts Need to split single line into two li... DFSORT/ICETOOL 7
No new posts Am Getting a vertical line in ISPF SC... TSO/ISPF 3
No new posts Print next line strings when a condit... DFSORT/ICETOOL 9
Search our Forums:

Back to Top