Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

db2 select - how to split the output record?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
jctgf
Currently Banned

Active User


Joined: 04 Nov 2006
Posts: 109

PostPosted: Sat May 01, 2010 6:50 pm    Post subject: db2 select - how to split the output record?
Reply with quote

hi there,

i have an unload with a select that will produce one only output record.
this record will be written to a sequential file.
can i split the output record into many records so that none of them is longer than 80 bytes?
i'd like to have everything done by a db2 select, if possible.

example:

the select:
Code:

SELECT COLA, COLB, COLC, COLD
FROM
TABLE WHERE COLX = 123;

produces the only record:
Code:

AAAABBBBCCCCDDDD

I'd like the output to be:

Code:
AAAABBBB
CCCCDDDD


this example is to simplify the question. the actual requirement is that i'd like to create a dfsort SYSIN file with a select and each SYSIN record can only be 80 bytes long.

thanks.
Back to top
View user's profile Send private message

Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1226
Location: Israel

PostPosted: Sat May 01, 2010 7:12 pm    Post subject:
Reply with quote

jctgf wrote:
SYSIN record can only be 80 bytes long.

That is correct (for sort) but... your data will be in SORTIN, not SYSIN.

BTW, in SYNCSORT you can have a DB2 SELECT statement as input file.
I'm pretty sure DFSORT can do that too.
Back to top
View user's profile Send private message
jctgf
Currently Banned

Active User


Joined: 04 Nov 2006
Posts: 109

PostPosted: Sat May 01, 2010 7:36 pm    Post subject:
Reply with quote

Actually the file that I want to create with the select will be used as a sysin.

The real select would be more or less like this:

Code:
SELECT CHAR(' SORT FIELDS=COPY')
          , CHAR(' INCLUDE COND=(1,10,CH,EQ,')
          , CHAR(X'7D')
          , COLA
          , CHAR(X'7D')
          , CHAR (')')
FROM TABLE
WHERE COLB = 123;

if I execute it, it will produce:
Code:
SORT FIELDS=COPY INCLUDE COND=(1,10,CH,EQ,'01.01.2001')


I need it to be like this:

Code:

 SORT FIELDS=COPY
 INCLUDE COND=(1,10,CH,EQ,'01.01.2001')


Thanks.
Back to top
View user's profile Send private message
Ronald Burr

Active User


Joined: 22 Oct 2009
Posts: 293
Location: U.S.A.

PostPosted: Sat May 01, 2010 8:42 pm    Post subject:
Reply with quote

Since you are using a SELECT, rather than a FETCH, AND since part of the output from the select includes a SORT FIELDS statement, I am led to assume that the select will always return only ONE result row.

If so, then given what you show as output there should be no need to "break up" the output into multiple records - the SORT products do not care how many spaces occur between keywords ( i.e. the COPY keyword and the INCLUDE keyword ), so you can code it all on a single record. So, as long as the total length of the SELECT output is less than 80 bytes, you should be good to go.

Now, if you REALLY need to break the output up into 80-byte records, then it is possible to use some "magic" to accomplish what you wish to achieve, but it requires "lying" to the machine. To wit:
Change your SELECT so that it always generates exactly 160 bytes of output. For example, if COLA is really 10 bytes long:
Code:
SELECT CHAR(' SORT FIELDS=COPY                       ')
     , CHAR('                                        ')  to make 1st rec = 80 bytes
     , CHAR(' INCLUDE COND=(1,10,CH,EQ,')
     , CHAR(X'7D')
     , COLA
     , CHAR(X'7D')
     , CHAR (')                                         ')  to make 2nd rec = 80 bytes
FROM TABLE
WHERE COLB = 123;


Make sure that the output file is defined as RECFM=FB,LRECL=160

Now, for the actual SORT step that will use your generated SYSIN, define the file as RECFM=FB,LRECL=80 ( not 160, this is the 'lie' but the OS will accept it willingly ).

If your REAL output is larger, just be sure to generate exactly 80 bytes for each desired output line, and code the output LRECL as a multiple of 80, but always "lie" to the SORT by "overriding" the LRECL to just 80.

Good luck.
Back to top
View user's profile Send private message
jctgf
Currently Banned

Active User


Joined: 04 Nov 2006
Posts: 109

PostPosted: Sat May 01, 2010 9:12 pm    Post subject:
Reply with quote

thanks a lot!
2014.gif
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1226
Location: Israel

PostPosted: Sun May 02, 2010 10:38 am    Post subject:
Reply with quote

Or, since the first line is constant, you could have the following select:
Code:
SELECT CHAR(' INCLUDE COND=(1,10,CH,EQ,')
          , CHAR(X'7D')
          , COLA
          , CHAR(X'7D')
          , CHAR (')')
FROM TABLE
WHERE COLB = 123;
and the following SYSIN:
Code:
//SYSIN   DD   *
    SORT FIELDS=COPY
//        DD   DISP=SHR,DSN=HLQ.SELECT.RESULT
Back to top
View user's profile Send private message
Ronald Burr

Active User


Joined: 22 Oct 2009
Posts: 293
Location: U.S.A.

PostPosted: Sun May 02, 2010 8:12 pm    Post subject:
Reply with quote

Marso,
I'm not sure that what you posted will actually work - that is, attempting to directly concatenate a DD * with a DD DSN.
The way I've always seen it done ( and have done so successfully myself ) is to code like:
Code:
//SYSIN  DD DDNAME=SYSIN2
//       DD DSN=HLQ.SELECT.RESULT,DISP=SHR
//SYSIN2 DD *
  SORT FIELDS=COPY
/*


If anyone has coded JCL as shown in your example and it worked, then I will have learned something new - not unusual.
Back to top
View user's profile Send private message
jctgf
Currently Banned

Active User


Joined: 04 Nov 2006
Posts: 109

PostPosted: Sun May 02, 2010 8:28 pm    Post subject:
Reply with quote

Hi,

Marsos's suggestion works and it is actually the way things have been implemented so far.

In a procedure I can't use DD * and I will have:
Code:
//SYSIN   DD   DSN=&CARDLIB(ABCDEFG),DISP=SHR
//        DD   DISP=SHR,DSN=HLQ.SELECT.RESULT

and the content of ABCDEFG will be:
Code:

    SORT FIELDS=COPY

but, if it's not a procedure, the DD * can be used without any problem.
Back to top
View user's profile Send private message
Ronald Burr

Active User


Joined: 22 Oct 2009
Posts: 293
Location: U.S.A.

PostPosted: Sun May 02, 2010 9:15 pm    Post subject:
Reply with quote

Glad I said "I'm not sure" icon_smile.gif
Thanks for letting me know that that construct does work. This old dog learned ( at least ) one new trick today.
Back to top
View user's profile Send private message
jctgf
Currently Banned

Active User


Joined: 04 Nov 2006
Posts: 109

PostPosted: Sun May 02, 2010 10:21 pm    Post subject:
Reply with quote

i'm an old dog too (since 1984) and i learn new things here everyday! icon_fU.gif
Back to top
View user's profile Send private message
Ronald Burr

Active User


Joined: 22 Oct 2009
Posts: 293
Location: U.S.A.

PostPosted: Mon May 03, 2010 1:25 am    Post subject:
Reply with quote

In dog years, I've been programming mainframes for 336 years! - since 1962. And I, too, continue to learn. In fact, I look forward to it.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Record Not found in AlX but record re... mukun264 COBOL Programming 7 Fri Mar 24, 2017 9:28 am
No new posts Count the length of the record & ... sreekusr DFSORT/ICETOOL 4 Thu Mar 23, 2017 7:52 pm
No new posts Problem reading GTF trace output danik56 ABENDS & Debugging 7 Thu Mar 16, 2017 1:02 pm
No new posts SORT VSAM file with each field one by... maxsubrat DFSORT/ICETOOL 6 Tue Mar 14, 2017 1:07 pm
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us