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

Extracting few records from records spanned in multiple line


IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Aditya.Srivastava

New User


Joined: 21 Aug 2012
Posts: 11
Location: India

PostPosted: Thu Feb 07, 2013 4:12 pm
Reply with quote

Hi,

This is my first post in this forum.

I have a requirement wherein I have to extract some records based on a condition from a file. The file is having multi line records, that is records spanned accross multiple lines. The data in the file is secure so I am mocking up the data in the input file.
Input file will look like follows:

Code:

----+----1----+----2----+----3----+----4----+
BANK1   HBANK1 NAME                         
        DBI <<DETAIL RECORD 1 >>             
        1C  ADDITIONAL DETAIL 1 RECORD 1     
        2C  ADDITIONAL DETAIL 2 RECORD 1     
        C 1234562222222222 000               
        DBI <<DETAIL RECORD 2 >>             
        1C  ADDITIONAL DETAIL 1 RECORD 2     
        2C  ADDITIONAL DETAIL 2 RECORD 2     
        C 3456761111111111 000               
        90000002                             
BANK2   HBANK2 NAME                         
        DBI <<DETAIL RECORD 1 >>             
        1C  ADDITIONAL DETAIL 1 RECORD 1     
        C 9876543333333333 000               
        DBI <<DETAIL RECORD 2 >>             
        1C  ADDITIONAL DETAIL 1 RECORD 2     
        C 9876542222222222 000               
        DBI <<DETAIL RECORD 3 >>             
        1C  ADDITIONAL DETAIL 1 RECORD 3     
        2C  ADDITIONAL DETAIL 2 RECORD 3     
        C 9876542222222222 000               
        90000003                             


There is no key in every line (row) which can identify that how many lines(row) belong to each record. The only way to know this is that a record starts with a detail record followed by multiple lines of additional record and ending in the card line(row). Details records are clubbed between a header and a trailer record with the counts of total records present in the trailer record.
Example of one complete record for a card with various details is as follows:
Code:

DBI <<DETAIL RECORD 3 >>                 
1C  ADDITIONAL DETAIL 1 RECORD 3         
2C  ADDITIONAL DETAIL 2 RECORD 3         
C 9876542222222222 000                   



The input file represents 2 records for BANK1 and 3 records for BANK2. This is prominent from the trailer records in both the batch(BANK1 and BANK2).
The column number 9 will identify each record. For example:
H --> Header record [ having details of Bank1 and Bank2 ]
D --> Detail record [Detail of the card]
1,2,3,4,5 --> Additional detail of record. [This is optional record and may or may not be present for a detail record]
C--> This represents the secure data e.g.card number.

Secure data can be like a card number and my requirement is to extract records for which the first 6 bytes of a card number is say '123456'.
Card number will start at column 11.


That way the output should be as follows:

Code:

----+----1----+----2----+----3----+----4-
BANK1   HBANK1 NAME                     
        DBI <<DETAIL RECORD 1 >>         
        1C  ADDITIONAL DETAIL 1 RECORD 1
        2C  ADDITIONAL DETAIL 2 RECORD 1
        C 1234562222222222 000           
        90000001                         


The output records should have a header (same as the header from which the record is extracted) and a trailer with the number of records updated.

The LRECL of both the input and output file is 800 and RECFM=FB.

Help is appreciated.

Thanks
Back to top
View user's profile Send private message
bodatrinadh

Active User


Joined: 05 Jan 2007
Posts: 101
Location: chennai (India)

PostPosted: Fri Feb 08, 2013 5:17 pm
Reply with quote

Hi Adithya,

Try this code..

Note :- I took LRECL=80 for my convenience...You can change as per your requirement. Tested on Syncsort. Hope it will work on DFSORT..

Code:


//S05S EXEC PGM=SORT
//SYSPRINT DD SYSOUT=*
//SYSOUT   DD SYSOUT=*
//SORTOUT  DD DSN=&&TEMP,DISP=(,PASS,KEEP),SPACE=(CYL,(1,1),RLSE)
//SORTIN DD *
BANK1   HBANK1 NAME
        DBI <<DETAIL RECORD 1 >>
        1C  ADDITIONAL DETAIL 1 RECORD 1
        2C  ADDITIONAL DETAIL 2 RECORD 1
        C 1234562222222222 000
        DBI <<DETAIL RECORD 2 >>
        1C  ADDITIONAL DETAIL 1 RECORD 2
        2C  ADDITIONAL DETAIL 2 RECORD 2
        C 3456761111111111 000
        90000002
BANK2   HBANK2 NAME
        DBI <<DETAIL RECORD 1 >>
        1C  ADDITIONAL DETAIL 1 RECORD 1
        C 9876543333333333 000
        DBI <<DETAIL RECORD 2 >>
        1C  ADDITIONAL DETAIL 1 RECORD 2
        C 8933332222222222 000
        DBI <<DETAIL RECORD 3 >>
        1C  ADDITIONAL DETAIL 1 RECORD 3
        2C  ADDITIONAL DETAIL 2 RECORD 3
        C 3728782222222222 000
        90000003
//SYSIN DD *
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,4,CH,EQ,C'BANK'),PUSH=(51:1,20,
  81:ID=3,SEQ=3),END=(9,1,CH,EQ,C'9')),                           
        IFTHEN=(WHEN=GROUP,BEGIN=(9,3,CH,EQ,C'DBI'),PUSH=(91:ID=3,
                SEQ=3),END=(9,1,CH,EQ,C'C')),                     
        IFTHEN=(WHEN=(9,2,CH,EQ,C'1C'),OVERLAY=(71:C'3')),
        IFTHEN=(WHEN=(9,2,CH,EQ,C'2C'),OVERLAY=(71:C'4')),
        IFTHEN=(WHEN=(9,2,SS,EQ,C'C'),OVERLAY=(71:C'2')),
        IFTHEN=(WHEN=(9,1,SS,EQ,C'9'),OVERLAY=(71:C'6')),
        IFTHEN=(WHEN=(9,3,CH,EQ,C'DBI'),OVERLAY=(71:C'5')),
        IFTHEN=(WHEN=NONE,OVERLAY=(71:C'1'))
  SORT FIELDS=(81,3,A,91,3,A,71,1,A),FORMAT=CH
  OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(9,2,CH,EQ,C'C '),PUSH=(91:11,6,
            SEQ=3),END=(9,3,CH,EQ,C'DBI')),
         IFTHEN=(WHEN=GROUP,BEGIN=(9,1,CH,EQ,C'9'),PUSH=(110:9,8))
  OUTFIL INCLUDE=(91,6,SS,EQ,C'123456'),
                           BUILD=(1,50,/,51,20,/,08X,110,8)
//*                                     
//S10S EXEC PGM=SORT
//SYSPRINT DD SYSOUT=*
//SYSOUT   DD SYSOUT=*
//SORTOUT  DD SYSOUT=*
//SORTIN DD DSN=&&TEMP,DISP=SHR
//SYSIN DD *
  INREC IFTHEN=(WHEN=(1,4,CH,EQ,C'BANK'),OVERLAY=(81:C'0')),
        IFTHEN=(WHEN=(9,2,CH,EQ,C'1C'),OVERLAY=(81:C'2')),
        IFTHEN=(WHEN=(9,2,CH,EQ,C'2C'),OVERLAY=(81:C'3')),
        IFTHEN=(WHEN=(9,3,CH,EQ,C'DBI'),OVERLAY=(81:C'1')),
        IFTHEN=(WHEN=(9,2,SS,EQ,C'C '),OVERLAY=(81:C'4')),
        IFTHEN=(WHEN=(9,1,CH,EQ,C'9'),OVERLAY=(81:C'9'))
  SORT FIELDS=(81,1,CH,A)
  SUM FIELDS=NONE
  OUTFIL BUILD=(1,80)       


Output :-

Code:

BANK1   HBANK1 NAME                       
        DBI <<DETAIL RECORD 1 >>         
        1C  ADDITIONAL DETAIL 1 RECORD 1 
        2C  ADDITIONAL DETAIL 2 RECORD 1 
        C 1234562222222222 000           
        90000002                         


Thanks
-3nadh
Back to top
View user's profile Send private message
Aditya.Srivastava

New User


Joined: 21 Aug 2012
Posts: 11
Location: India

PostPosted: Fri Feb 08, 2013 7:41 pm
Reply with quote

Hi bodatrinadh,

I have tried your solution and it worked for this sample data. I will test with my actual data and will post back in this topic.

Thanks for the reply.

-Aditya
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Sat Feb 09, 2013 2:56 am
Reply with quote

Aditya.Srivastava,

I guess there are much simpler ways to get the desired results. what is the LRECL and RECFM of the input file? And do you dynamically change the condition to extract? Is it just one credit number you want to extract or more than 1? if so are they in a separate file?
Back to top
View user's profile Send private message
Aditya.Srivastava

New User


Joined: 21 Aug 2012
Posts: 11
Location: India

PostPosted: Mon Feb 11, 2013 11:52 am
Reply with quote

Hi Skolusu,

The LRECL and RECFM of the input file will be 800 and FB respectively.

The condition will not be dynamic. We know all the list of identification numbers (the first 6 digit of the credit number). We can have the list stored in an 80 byte parm but we will always have a fixed list before the process runs in production. This list will not change frequently.

Appreciate the help !!

Thanks
Back to top
View user's profile Send private message
Aditya.Srivastava

New User


Joined: 21 Aug 2012
Posts: 11
Location: India

PostPosted: Mon Feb 25, 2013 4:41 pm
Reply with quote

Hi,

I was able to test the solution provided by bodatrinadh but can there be a simpler way of achieving this ?

If yes, then please suggest what should I try.

Thanks,
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose

PostPosted: Tue Feb 26, 2013 3:23 am
Reply with quote

Aditya.Srivastava,

Use the following DFSORT JCL which will give you the desired results.

Code:

//STEP0100 EXEC PGM=SORT                                           
//SYSOUT   DD SYSOUT=*                                             
//INA      DD DISP=SHR,DSN=Your input FB 800 byte file
//INB      DD *                                                     
987654                                                             
//SORTOUT  DD SYSOUT=*                                             
//SYSIN    DD *                                                     
  JOINKEYS F1=INA,FIELDS=(11,6,A)                                   
  JOINKEYS F2=INB,FIELDS=(01,6,A)                                   
  JOIN UNPAIRED,F1                                                 
  REFORMAT FIELDS=(F1:1,820,?)                                     
  SORT FIELDS=(801,16,CH,A,821,1,CH,A,817,4,CH,A)                   
                                                                   
  OUTREC IFTHEN=(WHEN=GROUP,BEGIN=(1,4,CH,EQ,C'BANK'),             
  END=(9,1,CH,EQ,C'9'),PUSH=(822:1,800)),                           
  IFTHEN=(WHEN=GROUP,BEGIN=(821,1,CH,EQ,C'B'),                     
  END=(9,1,CH,EQ,C'9'),PUSH=(1622:1,821)),                         
  IFTHEN=(WHEN=GROUP,KEYBEGIN=(2422,8),PUSH=(2438:SEQ=4))           
                                                                   
  OUTFIL INCLUDE=((801,16,CH,EQ,2422,16,CH),AND,821,1,CH,EQ,C'1'), 
  REMOVECC,IFOUTLEN=800,                                           
  IFTHEN=(WHEN=(2438,4,ZD,EQ,2),BUILD=(822,800,/,1,800)),           
  SECTIONS=(801,16,TRAILER3=(1622,800))                             
//*                                                                 
//JNF1CNTL DD *                                                     
  INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,4,CH,EQ,C'BANK'),               
  END=(9,1,CH,EQ,C'9'),PUSH=(801:ID=8)),                           
  IFTHEN=(WHEN=GROUP,BEGIN=(9,3,CH,EQ,C'DBI'),                     
  END=(9,1,CH,EQ,C'9'),PUSH=(809:ID=8,SEQ=4))                       
//*
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 -> DFSORT/ICETOOL

 


Similar Topics
Topic Forum Replies
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
No new posts Pulling a fixed number of records fro... DB2 2
Search our Forums:

Back to Top