Page 1 of 2

Records into a query

PostPosted: Fri Mar 21, 2014 12:47 am
by avinashmusic
Hi Experts,

I have the following requirement

Input file: LRECL:10
DATA0001
DATA00002
DATA03
DATA4
DATA 005
 DATA06


The space you see in the records are deliberate and not typo. The field in the input file might vary in length, i need all the fields within single quotes followed by a comma except for the last record(last record will have single quotes but should exclude comma and include a closed bracket ')')

Expected Output: LRECL:13
'DATA0001',
'DATA00002',
'DATA03',
'DATA4',
'DATA 005',
' DATA06')


Sort card i used is as follows:
SORT FIELDS=COPY
INREC IFTHEN=(WHEN=INIT,
BUILD=(1:1,10,JFY=(SHIFT=LEFT,LEAD=C'''',TRAIL=C''',',LENGTH=13)))
OUTFIL FNAMES=SORTOUT,REMOVECC
TRAILER1=(1,12,C')')


The output i am getting:
'DATA0001',
'DATA00002',
'DATA03',
'DATA4',
'DATA 005',
'DATA06',
'DATA06',   )


Issues with the above output:
1. When there is a space in the first position of the field(last record) it is getting shifted left as i have left justified, but i want the first character to be space(as input) in the output.
2. I was not able to remove the comma in the last record.
3. I am getting a repetition of the last record, as i have used TRAILER1 function. If possible, i want to avoid this, though it is harmless.

Can someone please help me with this?

Re: Records into a query

PostPosted: Fri Mar 21, 2014 5:13 am
by BillyBoyo
Have a look for the "Smart DFSORT Tricks" publication available from the IBM DFSORT wesite and look at "Add comma at end of all records except the last" on p35.

DATASORT will not "see" the headers/trailers excluded in the INREC, but they will all be there in OUTFIL processing. You can then put the ")" on the record which does not contain a "," (use field-type of SS to do a sub-string search for the ","). You won't then need the TRAILER1.

The SORT is done on 1,1 (a quote) and OPTION EQUALS is the default in DATASORT, so the input order will not change. Since they are only control cards, so you can't have that many, you won't notice the "overhead" of the SORT in any meaningful way.

If you only have one leading space, you can insert a "'" in position 1, copy 1,1 in the BUILD and then do the JFY from 2,9 without the LEAD=.

Re: Records into a query

PostPosted: Fri Mar 21, 2014 3:58 pm
by avinashmusic
Hi Billy. . Thanks for your response. But I am not able to collate all what you said. Cos there were too many things. If you could put it up in a sort card, it would be really great. :)

Re: Records into a query

PostPosted: Fri Mar 21, 2014 6:12 pm
by BillyBoyo
Did you find the document and read the example? Also read the DFSORT Application Programming Guide for ICETOOL generally and DATASORT specifically.

You take the Smart Trick, which is close to what you want, and your code, which is close to what you want, and apply them to get what you want.

Look at it this way: your code has TRAILER1 which causes the duplication, so remove that; the DATASORT puts a comma on each line but the last, which you want, but you want the ")" as well.

Work on that first.

Then your some-time leading space is getting lost in the JFY. So just don't include it (or any other first character) in the JFY, BUILD=(C''',1,1,2,9,JFY...)

Re: Records into a query

PostPosted: Mon Mar 24, 2014 10:08 pm
by avinashmusic
Hi Billy.. The smart df sort trick is written for fixed length field. But my field is of variable length.

Re: Records into a query

PostPosted: Mon Mar 24, 2014 11:29 pm
by skolusu
//STEP0100 EXEC PGM=SORT                                             
//SYSOUT   DD SYSOUT=*                                               
//INA      DD DISP=SHR,DSN=YOUR INPUT FB 10 BYTE FILE
//INB      DD DISP=SHR,DSN=SAME INPUT FB 10 BYTE FILE
//SORTOUT  DD SYSOUT=*                                               
//SYSIN    DD *                                                     
  OPTION COPY                                                       
  JOINKEYS F1=INA,FIELDS=(11,8,A),SORTED,NOSEQCK                     
  JOINKEYS F2=INB,FIELDS=(01,8,A),SORTED,NOSEQCK                     
  JOIN UNPAIRED,F1                                                     
  REFORMAT FIELDS=(F1:1,10,?)                                       
                 
  INREC IFOUTLEN=13,IFTHEN=(WHEN=INIT,BUILD=(C'''',1,10,14:11,1)),   
  IFTHEN=(WHEN=INIT,OVERLAY=(1,13,JFY=(SHIFT=RIGHT,TRAIL=X'7D6B'))),
  IFTHEN=(WHEN=(14,1,CH,EQ,C'1'),                                   
  OVERLAY=(13:X,1:1,13,JFY=(SHIFT=LEFT))),                           
  IFTHEN=(WHEN=NONE,BUILD=(1,13,JFY=(SHIFT=LEFT)))                   
//*
//JNF1CNTL DD *                                                     
  INREC OVERLAY=(11:SEQNUM,8,ZD)                                     
//*                                                                 
//JNF2CNTL DD *                                                     
  INREC BUILD=(SEQNUM,8,ZD,START=0)                                 
//*

Re: Records into a query

PostPosted: Wed Mar 26, 2014 12:02 am
by avinashmusic
Thanks kolusu. It works, can you please explain why we are doing that join. I could not understand that part.

Re: Records into a query

PostPosted: Wed Mar 26, 2014 12:46 am
by skolusu
avinashmusic wrote:Thanks kolusu. It works, can you please explain why we are doing that join. I could not understand that part.


Your goal is to identify the last record and remove the comma. So what do you do? Compare the same file to itself but for file1 you assign the sequence number for every record starting with 1 and for file2 you assign the sequence number for every record starting with 0. what happens when you compare these 2 sequence numbers? The last record will NOT have a match and will be unique.

Re: Records into a query

PostPosted: Wed Mar 26, 2014 4:45 pm
by avinashmusic
Thanks. That was great logic but there will be Another record having the Sequence number as 0 in the second file which will not have a match right? How that is handled?

Re: Records into a query

PostPosted: Wed Mar 26, 2014 5:32 pm
by BillyBoyo
File with three records
      SeqFrom1 SeqFrom0
DATA1        1        0
DATA2        2        1
DATA3        3        2


The JOIN specifies UNPAIRED,F1 - which gets all matches plus mis-matches for F1. Mis-matches for F2 are simply ignored, not presented to the Main Task.

The Main Task sees

      MatchMarker
DATA1           B
DATA2           B
DATA3           1


Due to the REFORMAT statement, the Main Task does not see the sequence numbers for the above, but they were 1, 2 and 3.

With UNPAIRED,F1,F2 the mis-matched F2 would be included, the REFORMAT would give blanks (or FILL character if specified) and the Match Marker would be 2. There would be four records processed by the Main Task, and sequences would be 0, 1, 2 and 3 (the 0 for F2 only, the 3 for F1 only, and the 1 and 2 for both sources).

You can verify all this through experimentation:

  JOIN UNPAIRED,F1,F2                                                     
  REFORMAT FIELDS=(F1:1,10,?,F1:11,8,F2:1,8)


Remove all the code in the INREC and look at the output you receive.