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

File formatting using SYNCSORT


IBM Mainframe Forums -> JCL & VSAM
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Thu Jun 18, 2009 8:57 am
Reply with quote

Hello,

I have an input file of LRECL=175,RECFM=FB, this way:

Code:


----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+
                             VERSION: 20080403


                                            PRODUCT - ABC     ENTITY 1234 
ACCOUNT

00000000000000123456789
00000000000000111334556
00000000000000101334556
00000000000000117657889

                                            PRODUCT - DEF     ENTITY 4568
ACCOUNT

00000000000000198876789
00000000000000111332424
00000000000000101324242
00000000000000117687676

                                            PRODUCT - GHI     ENTITY 9012
ACCOUNT

00000000000000182648789
00000000000000122472848
00000000000000129823647
00000000000000113497326



The records will continue like this and we don't really know how many such series of ACCOUNT, PRODUCT, ENTITY combination records exist in the file. The format, like i mentioned above is uniform though.

I need the output file to be:

Code:

----+----1----+----2----+----3----+----4----+----5----+----6----+----7

ACCOUNT                     PRODUCT           ENTITY        DATE

00000000000000123456789     ABC               1234          20080403
00000000000000111334556     ABC               1234          20080403
00000000000000101334556     ABC               1234          20080403
00000000000000117657889     ABC               1234          20080403
00000000000000198876789     DEF               4568          20080403
00000000000000111332424     DEF               4568          20080403
00000000000000101324242     DEF               4568          20080403
00000000000000117687676     DEF               4568          20080403
00000000000000182648789     GHI               9012          20080403
00000000000000122472848     GHI               9012          20080403
00000000000000129823647     GHI               9012          20080403
00000000000000113497326     GHI               9012          20080403



Please help. I'm trying parallely in this way

-> If we find the word PRODUCT, set a SEQNUM in some position in the file. Similarly for the word ENTITY set another SEQNUM in another position

-> the tricky part is, to fetch the value that is right beside the words PRODUCT, ENTITY and write them by the side of the account numbers as shown in the output file, for that particular PRODUCT,ENTITY group.

When i say group, it is like this:

Code:
                                            PRODUCT - ABC     ENTITY 1234 
ACCOUNT

00000000000000123456789
00000000000000111334556
00000000000000101334556
00000000000000117657889

-> For the next group,the corresponding PRODUCT, ENTITY values need to be picked up.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Thu Jun 18, 2009 6:35 pm
Reply with quote

Hello,

Here's one way of achieving this in SyncSort. I assumed an input LRECL of 80.
Code:
//STEP0100 EXEC PGM=SORT                                         
//SYSOUT   DD SYSOUT=*                                           
//T1       DD DSN=&&T1,DISP=(NEW,PASS)                           
//T2       DD DSN=&&T2,DISP=(NEW,PASS)                           
//S1       DD DSN=&&S1,DISP=(NEW,PASS)                           
//SORTIN   DD DSN= Input file ---> FB/80                                               
//SYSIN    DD *                                                 
  OMIT COND=(1,1,CH,EQ,C'A')                                     
  INREC IFTHEN=(WHEN=INIT,OVERLAY=(81:SEQNUM,8,ZD,START=0)),     
        IFTHEN=(WHEN=(45,1,CH,EQ,C'P'),OVERLAY=(81:SEQNUM,8,ZD)),
        IFTHEN=(WHEN=(45,1,CH,EQ,C' '),OVERLAY=(89:SEQNUM,8,ZD, 
                      81:81,8,ZD,SUB,89,8,ZD,M11,LENGTH=8))     
  SORT FIELDS=COPY                                               
  OUTFIL FNAMES=T1,INCLUDE=(45,1,CH,EQ,C' '),BUILD=(1,88)       
  OUTFIL FNAMES=T2,INCLUDE=(45,1,CH,EQ,C'P'),BUILD=(1,88)       
  OUTFIL FNAMES=S1,SAVE,BUILD=(30,3,C',''',39,8,C'''',80:X)
/*     
//STEP0200 EXEC PGM=SORT                                         
//SYSOUT   DD SYSOUT=*                                         
//SORTOUT  DD DSN= Output file ---> FB/80
//SORTJNF1 DD DSN=&&T1,DISP=(OLD,PASS)                         
//SORTJNF2 DD DSN=&&T2,DISP=(OLD,PASS)                         
//SYMNAMES DD DSN=&&S1,DISP=(OLD,PASS)                         
//SYSIN    DD *                                               
  JOINKEYS FILE=F1,FIELDS=(81,8,A),SORTED                     
  JOINKEYS FILE=F2,FIELDS=(81,8,A),SORTED                     
  REFORMAT FIELDS=(F1:1,23,F2:55,3,F2:70,4)                   
  INREC BUILD=(1,23,29:24,3,47:27,4,61:VER)                   
  SORT FIELDS=COPY                                             
  OUTFIL REMOVECC,                                             
         HEADER1=('ACCOUNT',29:'PRODUCT',47:'ENTITY',61:'DATE')
Back to top
View user's profile Send private message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Thu Jun 18, 2009 8:43 pm
Reply with quote

Hello Arun,

I didnot understand the purpose of the following piece of code, in this context. Could you pls explain me:

Code:
OUTFIL FNAMES=S1,SAVE,BUILD=(30,3,C',''',39,8,C'''',80:X)
Back to top
View user's profile Send private message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Thu Jun 18, 2009 8:45 pm
Reply with quote

Never mind, Arun. Thats for the version. I got it icon_smile.gif
Back to top
View user's profile Send private message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Thu Jun 18, 2009 10:15 pm
Reply with quote

Hmm..i didnot imagine that this will be a hinderance, but this is how my input file looks like actually.....

Code:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+
**********                             
                             VERSION: 20080403
                       REPORT ID: ABCDEF

                                            PRODUCT - ABC     ENTITY 1234 
ACCOUNT                                 PREV         CURR

00000000000000123456789       05/08/00    05/03/01   ----12345--

00000000000000142435556       01/03/06    03/06/02   ***!34552--


00000000003131101334556       06/09/08    01/03/09   27488***


00000000003243536334556

.................................................
    04/03/08                                  ***ABCDEFGHIJK****
    22:50:39                                     

                                            PRODUCT - DEF     ENTITY 4568
ACCOUNT                                 PREV         CURR

12456789                       01/08/00    05/03/01   ----1122D--

17127224                       09/08/00    06/03/01   ----QEFSF--



10138742                       01/08/09    05/08/01   ----AFSFSF--


11134416                       05/08/03    02/03/01   ----DFDCASR--




.................................................
    04/03/08                                  ***ABCDEFGHIJK****
    22:50:39



I tried executing the code you posted and got S013 abend code ( UNSUCCESSFUL SORT ) for the 2nd step. May be because i didnot mention the input file properly, based on which you
wrote the sort card, it would have failed. Noteably,

a) There is data below the PRODUCT, ENTITY tags and beside the account numbers. It is not space, like i mentioned before
b) a dotted line followed by timestamp appears after each of the so called 'group'

Code:
.................................................
    04/03/08                                  ***ABCDEFGHIJK****
    22:50:39


c) Account numbers differ in length from group to group and the records can have spaces between them, like in the input file mentioned above.


I'm trying to change the code accordingly. if you want me to make any adjustments to the previously posted code, please let me know.

Thanks!
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Thu Jun 18, 2009 10:28 pm
Reply with quote

Quote:
Hmm..i didnot imagine that this will be a hinderance, but this is how my input file looks like actually.....


if You knew from the beginning,
can You explain why in h**l You asked for something,
and after somebody has replied with a solution,
only then You come up with the final requirement icon_question.gif icon_evil.gif icon_question.gif

it just shows no respect for people spending their own time testing and providing solutions for Your needs

do You behave in the same way when dealing with Your manager???
Back to top
View user's profile Send private message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Thu Jun 18, 2009 11:25 pm
Reply with quote

It isn't that way Enrico.

i tried changing the fileformat to what was 'desired', but did i ever say that the code that was posted here willnot help? I guess you are under a wrong assumption that i treat my manager and others in a different way....... I have a lot of respect for people out here and even if some one lashes out at me because i didnot make something clear, im fine, because you guys are a great bunch of people and are helping us out time and again. So apologies for assuming something and confusing you.

if its possible for you to help me out, i'll be more than happy. if you are not able to also, it is fine because you tried your level best to help me out already.
Back to top
View user's profile Send private message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Mon Jun 22, 2009 1:21 pm
Reply with quote

Hi Arun,

I wrote a syncsort card to arrive 'almost' at the first input file, from the second input file i posted previously. Here it is:

Code:
INREC IFTHEN=(WHEN=INIT,BUILD=(1:2,23,45:61,13,63:118,15)),         
      IFTHEN=(WHEN=(1,6,ZD,EQ,NUM),OVERLAY=(1,25,56X)),             
      IFTHEN=(WHEN=(1,7,CH,EQ,C'ACCOUNT'),OVERLAY=(9:72X'40')),     
      IFTHEN=(WHEN=(45,7,CH,EQ,C'PRODUCT'),OVERLAY=(44X'40',45,36)),
      IFTHEN=(WHEN=(45,7,CH,NE,C'PRODUCT',AND,                     
                    1,23,CH,NE,C'ACCOUNT                ',         
              AND,1,6,ZD,NE,NUM),OVERLAY=(1:80X'40')),             
      IFTHEN=(WHEN=(1,7,CH,EQ,C'-------'),OVERLAY=(1:80X'40'))     
SORT FIELDS=COPY
OUTFIL OMIT=(1,1,CH,EQ,C' ',AND,45,7,CH,NE,C'PRODUCT'),
BUILD=(1,80)                                           


But the problem i face is that there is lot of other data in the second input file from position 1,25. So, even though i considered all records with numeric data in position 1,6 and also those with the tag ACCOUNT in the above sort card and eliminated those that didnot meet these conditions, iam seeing some alphanumeric data in the output that has first 6 digits as numeric and the rest being alphabetic.

Here is a sample of what i got as output

Code:


570877068 ABCDEG                                                               
570877068 BCUDEH
570877068 JHISINU                                                             
                                            PRODUCT - ABC     ENTITY 1234
ACCOUNT                                                                       
00000000059101000000001                                                       
00000000059101000000002                                                       

                                            PRODUCT - DEF     ENTITY 4568
ACCOUNT                                                                       
00000000059101000000003                                                       
00000000059101000000004                                                       



It should only be:

Code:


                                                           
                                            PRODUCT - ABC     ENTITY 1234
ACCOUNT                                                                       
00000000059101000000001                                                       
00000000059101000000002                                                       

                                            PRODUCT - DEF     ENTITY 4568
ACCOUNT                                                                       
00000000059101000000003                                                       
00000000059101000000004                                                       



So, is there a way to check for consecutive alphabets ( any number of ) in a field using SYNCSORT? That will help me get rid of the unwanted data in the output. Upon overcoming this problem, i will be able to use the sort card you posted.

Please guide me.
Back to top
View user's profile Send private message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Tue Jun 23, 2009 9:07 am
Reply with quote

Hi Arun,

Please let me know if there is a way to accomplish this task. Your inputs would be very valuable.
Back to top
View user's profile Send private message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Tue Jun 23, 2009 9:18 am
Reply with quote

Just to make it clear..The consecutive alphabetic characters could start anywhere with in the field and end up anywhere ,They need not necessarily be towards the end or beginning of the field.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Jun 23, 2009 9:34 am
Reply with quote

Hello,

Suggest you look at the last part of this topic, posted a short while ago:
ibmmainframes.com/viewtopic.php?t=41631
Back to top
View user's profile Send private message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Tue Jun 23, 2009 11:50 am
Reply with quote

Excellent, it worked!! Thank you Dick and Arun for your help in accomplishing an important task. As always, i've reveived very timely help from you guys.

I'll ensure from now that the final file creation criteria is mentioned clearly right from the beginning of the topic.
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2481
Location: @my desk

PostPosted: Tue Jun 23, 2009 12:53 pm
Reply with quote

You're welcome. Would you mind posting the final solution here?
Back to top
View user's profile Send private message
surya4ug

New User


Joined: 08 Jul 2008
Posts: 62
Location: chennai

PostPosted: Tue Jun 23, 2009 7:50 pm
Reply with quote

Sure Arun, here we go....Initially when you posted the code, we picked VERSION from the top of the file to fetch the date value adjacent to it. Now, i tweaked the logic bit to fetch the date from some other place of the input file.

I guess we would have accomplished the task thru EZYTRIEVE with a bit of lesser effort and coding, but certainly, i prefer SYNCSORT icon_smile.gif and believe me, its a lot faster and just took about 0.8 mins to run all the 4 steps, as opposed to nearly 5-6 mins thru EZYTRIEVE. This is considerable difference because, i need to execute the same piece of code for nearly 360 input files.

Code:
//*************************************************************
//****  FORMAT THE INITIAL FILE TO REMOVE UNWANTED TAGS    ****
//****  AS WE WE NEED ONLY ACCOUNT INFORMATION HERE        ****
//*************************************************************

//STEP1 EXEC PGM=SORT                                                 
//SYSOUT   DD SYSOUT=*                                                 
//SORTIN   DD DISP=SHR,DSN=MY INPUT FILE             
//SORTOUT  DD DSN=&&OUTPUT FILE1,
//          DISP=(NEW,CATLG,DELETE)                                   
//SYSIN   DD *
  INREC IFTHEN=(WHEN=INIT,BUILD=(1:6,23,28:65,13,45:122,15,64:64,8,     
                                         73:51,8)),                     
   IFTHEN=(WHEN=(1,6,ZD,EQ,NUM),OVERLAY=(1,25,35X,62:64,8,3X,73:73,8)),
   IFTHEN=(WHEN=(1,7,CH,EQ,C'ACCOUNT'),OVERLAY=(9:72X'40')),           
   IFTHEN=(WHEN=(28,7,CH,EQ,C'PRODUCT'),OVERLAY=(27X'40',28,32,21X)),   
   IFTHEN=(WHEN=(28,7,CH,NE,C'PRODUCT',AND,                             
                      1,23,CH,NE,C'ACCOUNT                ',           
                AND,1,6,ZD,NE,NUM),OVERLAY=(1:80X'40')),               
   IFTHEN=(WHEN=(1,7,CH,EQ,C'-------'),OVERLAY=(1:80X'40'))             
   SORT FIELDS=COPY                                                     
   OUTFIL OMIT=(1,1,CH,EQ,C' ',AND,28,7,CH,NE,C'PRODUCT'),             
   BUILD=(1,80)                                                         
/*                                                                     

//*************************************************************
//****  INCASE ANY 'CUSTOMER' LEVEL DATA APPEARS IN THE  **** 
//****  FILE WHEN THE 'FIRST 6 DIGIT NUMERIC' INCLUSION  **** 
//****  DATA CHECK FAILS, THIS STEP WILL REMOVE ALL THE  **** 
//****  RECORDS HAVING 4 CONSECUTIVE ALPHABETS STARTING  **** 
//****  FROM 7TH POSITION THRU 23RD IN THE OUTPUT OF     **** 
//****  STEP1                                            **** 
//*************************************************************

//STEP2 EXEC PGM=ICEMAN                                       
//SYSOUT   DD SYSOUT=*                                         
//SYMNAMES DD *                                               
  CHECK,'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'
//SORTIN   DD DISP=SHR,DSN=&&OUTPUT FILE1
//SORTOUT  DD DSN=&&OUTPUT FILE2,       
//          DISP=(NEW,CATLG,DELETE),                           
//SYSIN    DD *             
  OPTION COPY               
    OMIT FORMAT=SS,         
      COND=(7,1,EQ,CHECK,&, 
            8,1,EQ,CHECK,&, 
            9,1,EQ,CHECK,&, 
            10,1,EQ,CHECK,OR,
*                           
            8,1,EQ,CHECK,&, 
            9,1,EQ,CHECK,&, 
            10,1,EQ,CHECK,&,
            11,1,EQ,CHECK,OR,
*                           
            9,1,EQ,CHECK,&, 
            10,1,EQ,CHECK,&,
            11,1,EQ,CHECK,&,
            12,1,EQ,CHECK,OR,
*                           
            10,1,EQ,CHECK,&,
            11,1,EQ,CHECK,&,
            12,1,EQ,CHECK,&,
            13,1,EQ,CHECK,OR,
*                           
            11,1,EQ,CHECK,&,
            12,1,EQ,CHECK,&,
            13,1,EQ,CHECK,&,
            14,1,EQ,CHECK,OR,
*                           
            12,1,EQ,CHECK,&,
            13,1,EQ,CHECK,&,
            14,1,EQ,CHECK,&,
            15,1,EQ,CHECK,OR,
*                           
            13,1,EQ,CHECK,&,
            14,1,EQ,CHECK,&,
            15,1,EQ,CHECK,&,
            16,1,EQ,CHECK,OR,
*                           
            14,1,EQ,CHECK,&,
            15,1,EQ,CHECK,&,
            16,1,EQ,CHECK,&, 
            17,1,EQ,CHECK,OR,
*                             
            15,1,EQ,CHECK,&, 
            16,1,EQ,CHECK,&, 
            17,1,EQ,CHECK,&, 
            18,1,EQ,CHECK,OR,
*                             
            16,1,EQ,CHECK,&, 
            17,1,EQ,CHECK,&, 
            18,1,EQ,CHECK,&, 
            19,1,EQ,CHECK,OR,
*                             
            17,1,EQ,CHECK,&, 
            18,1,EQ,CHECK,&, 
            19,1,EQ,CHECK,&, 
            20,1,EQ,CHECK,OR,
*                             
            18,1,EQ,CHECK,&, 
            19,1,EQ,CHECK,&, 
            20,1,EQ,CHECK,&,     
            21,1,EQ,CHECK,OR,   
*                               
            19,1,EQ,CHECK,&,     
            20,1,EQ,CHECK,&,     
            21,1,EQ,CHECK,&,     
            22,1,EQ,CHECK,OR,   
*                               
            20,1,EQ,CHECK,&,     
            21,1,EQ,CHECK,&,     
            22,1,EQ,CHECK,&,     
            23,1,EQ,CHECK)       
/*                               

//*************************************************************
//****  FORMAT THE OUTPUT OF STEP2 BY ADDING SEQUENCE NUM  ****
//****  TO  RECOGNISABLE TAGS LIKE 'PRODUCT' etc TO        ****
//****  SEGREGATE THE FILE TO 'PRODUCT' TAG FILE AND A FILE****
//****  WITH RECS NOT HAVING A 'PRODUCT' TAG               ****
//*************************************************************
//STEP3    EXEC PGM=SORT                                       
//SYSOUT   DD SYSOUT=*                                         
//T1       DD DSN=&&T1,DISP=(NEW,PASS),                       
//          SPACE=(CYL,(1500,1400),RLSE),BLKSIZE=0,           
//          VOL=(,,,90)                                       
//T2       DD DSN=&&T2,DISP=(NEW,PASS),                       
//          SPACE=(CYL,(1500,1400),RLSE),BLKSIZE=0,           
//          VOL=(,,,90)                                       
//SORTIN   DD DSN=&&OUTPUT FILE2,DISP=SHR
//SYSIN    DD *                                                     
  OMIT COND=(1,7,CH,EQ,C'ACCOUNT')                                 
    INREC IFTHEN=(WHEN=INIT,OVERLAY=(81:SEQNUM,8,ZD,START=0)),     
          IFTHEN=(WHEN=(28,1,CH,EQ,C'P'),OVERLAY=(81:SEQNUM,8,ZD)),
          IFTHEN=(WHEN=(28,1,CH,EQ,C' '),OVERLAY=(89:SEQNUM,8,ZD,   
                         81:81,8,ZD,SUB,89,8,ZD,M11,LENGTH=8))     
    SORT FIELDS=COPY                                               
    OUTFIL FNAMES=T1,INCLUDE=(28,1,CH,EQ,C' '),BUILD=(1,88)         
    OUTFIL FNAMES=T2,INCLUDE=(28,1,CH,EQ,C'P'),BUILD=(1,88)         
/*                                                                 

//*************************************************************
//****  JOIN THE SEQUENCE NUMS ADDED TO THE OUTPUT FILES IN****
//****  STEP3 AND GENERATE THE FINAL INFOPAC FILE WITH     ****
//****  ACCOUNT,PRODUCT,ENTITY,CURRENT MAINTENANCE DATE,   ****
//****  PREVIOUS MAINTENANCE DATE                          ****
//*************************************************************
//STEP4 EXEC PGM=SORT                                 
//SYSOUT   DD SYSOUT=*                                 
//SORTOUT  DD DSN=&&OUTPUT FILE3,
//          DISP=(NEW,CATLG,DELETE)                 
//SORTJNF1 DD DSN=&&T1,DISP=(OLD,PASS)                 
//SORTJNF2 DD DSN=&&T2,DISP=(OLD,PASS)                 \
//SYSIN    DD *                                                 
    JOINKEYS FILE=F1,FIELDS=(81,8,A),SORTED                     
    JOINKEYS FILE=F2,FIELDS=(81,8,A),SORTED                     
    REFORMAT FIELDS=(F1:1,23,F2:38,3,F2:52,8,F1:62,8,F1:73,8)   
    INREC BUILD=(1,23,29:24,3,40:27,8,50:35,8,70:43,8,30X)     
    SORT FIELDS=(1,23,CH,A)                                     
    SUM FIELDS=NONE                                             
    OUTFIL REMOVECC,                                           
             HEADER1=('ACCOUNT',29:'PRODUCT',40:'ENTITY',       
                      50:'CURR MAINT DATE',70:'PREV MAINT DATE')

/*



Thanks again!
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Jun 23, 2009 8:05 pm
Reply with quote

Good to hear it is working - thank you for letting us know and posting your solution icon_smile.gif

d
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 -> JCL & VSAM

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts Access to non cataloged VSAM file JCL & VSAM 18
No new posts Compare only first records of the fil... SYNCSORT 7
Search our Forums:

Back to Top