Variable Sequence Number



Support for NetApp SyncSort for z/OS, Visual SyncSort, SYNCINIT, SYNCLIST and SYNCTOOL

Variable Sequence Number

Postby mainframe_novice » Fri May 07, 2010 2:57 am

I have a table with following strucure

Sales_Date             ,  Date
Transaction_id       , SMALLINT
Amount                 ,  Decimal(11,2)


Every 4 hrs I get the file with following structure .
Sales_Date  (10 Positions),
Amount (Decimal 10,2)


Transaction_id is a sequence number .

The job to load the table will run every 6 times in a day.
When the job is run second time and onwards , I have to fetch the maximum Transaction_id from a table , need to add 1 to it and assign it to the first record of the file and then increment it for each record .

File Structure for load is similar to the table structure .

Sales_Date             (1:10)
Transaction_id       (11-13)
Amount                 (14:25)


Can anyone help me with this ?

Additional information ...if required ..please let me know .
mainframe_novice
 
Posts: 35
Joined: Tue Apr 27, 2010 1:29 am
Has thanked: 0 time
Been thanked: 0 time

Re: Variable Sequence Number

Postby Alissa Margulies » Fri May 07, 2010 3:21 am

Please provide sample data records and desired output so that we can better assist you. Thank you.
Alissa Margulies
Syncsort Mainframe Product Services
201-930-8260
zos_tech@syncsort.com
Alissa Margulies
Global moderator
 
Posts: 369
Joined: Tue Feb 26, 2008 11:15 pm
Location: USA
Has thanked: 1 time
Been thanked: 3 times

Re: Variable Sequence Number

Postby mainframe_novice » Fri May 07, 2010 3:31 am

Okay...

1st job loaded following Data into table

Sales_Date   Transaction_id   Amount
2010-05-05   1                      123456.12
2010-05-05   2                        34678.12
2010-05-05   3                      823796.12
2010-05-05   4                        83456.12


I have a input file with following data
Sales_Date             Amount
2010-05-05             978.18
2010-05-05           1234.21


Need to create the output file which will have sequence number . But that sequence number should be now 5 for first record and 6 for second record as we already have 4 records in the table .

So output file will be
Sales_Date             Transaction_Id     Amount
2010-05-05                  5                      978.18
2010-05-05                  6                    1234.21


Once this File is loaded into table ..the table should look like
Sales_Date   Transaction_id   Amount
2010-05-05   1                      123456.12
2010-05-05   2                        34678.12
2010-05-05   3                      823796.12
2010-05-05   4                        83456.12
2010-05-05   5                           978.18
2010-05-05   6                          1234.21
mainframe_novice
 
Posts: 35
Joined: Tue Apr 27, 2010 1:29 am
Has thanked: 0 time
Been thanked: 0 time

Re: Variable Sequence Number

Postby Alissa Margulies » Fri May 07, 2010 9:36 pm

Your sample records do not match your column descriptions above. Please make sure that the field positions in your sample records are correct, otherwise the solution we provide may not be accurate.
Alissa Margulies
Syncsort Mainframe Product Services
201-930-8260
zos_tech@syncsort.com
Alissa Margulies
Global moderator
 
Posts: 369
Joined: Tue Feb 26, 2008 11:15 pm
Location: USA
Has thanked: 1 time
Been thanked: 3 times

Re: Variable Sequence Number

Postby mainframe_novice » Sat May 08, 2010 12:32 am

Sales_Date 10 Positions
transaction_id smallint ...with 3 positions in a file
Amount Decimal(11,2) , it would take 12 positions in a file
mainframe_novice
 
Posts: 35
Joined: Tue Apr 27, 2010 1:29 am
Has thanked: 0 time
Been thanked: 0 time

Re: Variable Sequence Number

Postby Alissa Margulies » Sat May 08, 2010 1:22 am

Do the sales_date and Amount appear in the same positions in the input file and in the table?
Alissa Margulies
Syncsort Mainframe Product Services
201-930-8260
zos_tech@syncsort.com
Alissa Margulies
Global moderator
 
Posts: 369
Joined: Tue Feb 26, 2008 11:15 pm
Location: USA
Has thanked: 1 time
Been thanked: 3 times

Re: Variable Sequence Number

Postby mainframe_novice » Sat May 08, 2010 3:13 am

Yes...That is correct .
mainframe_novice
 
Posts: 35
Joined: Tue Apr 27, 2010 1:29 am
Has thanked: 0 time
Been thanked: 0 time

Re: Variable Sequence Number

Postby Alissa Margulies » Mon May 10, 2010 9:15 pm

Here is a sample SyncSort for z/OS job you can use:
//SORT1 EXEC PGM=SORT                       
//SORTIN DD *                               
SALES_DATE ID      AMOUNT                   
2010-05-05  1   123456.12                   
2010-05-05  2    34678.12                   
2010-05-05  3   823796.12                   
2010-05-05  4    83456.12                   
//       DD *                               
SALES_DATE         AMOUNT                   
2010-05-05         978.18                   
2010-05-05        1234.21                   
//SORTOUT DD SYSOUT=*                       
//SYSOUT  DD SYSOUT=*                       
//SYSIN   DD *                               
   OMIT COND=(1,1,CH,EQ,C'S')               
   SORT FIELDS=COPY                         
   OUTFIL OVERLAY=(11:SEQNUM,3,ZD),         
     HEADER1=(1:C'SALES_DATE ID      AMOUNT')
/*

This is the output produced:
SALES_DATE ID      AMOUNT
2010-05-05001   123456.12
2010-05-05002    34678.12
2010-05-05003   823796.12
2010-05-05004    83456.12
2010-05-05005      978.18
2010-05-05006     1234.21

If your files do not have actual header records, then you can simply remove the OMIT and HEADER statements.
Alissa Margulies
Syncsort Mainframe Product Services
201-930-8260
zos_tech@syncsort.com
Alissa Margulies
Global moderator
 
Posts: 369
Joined: Tue Feb 26, 2008 11:15 pm
Location: USA
Has thanked: 1 time
Been thanked: 3 times

Re: Variable Sequence Number

Postby mainframe_novice » Wed May 12, 2010 8:31 pm

Thanks Alisa , it worked .
mainframe_novice
 
Posts: 35
Joined: Tue Apr 27, 2010 1:29 am
Has thanked: 0 time
Been thanked: 0 time


Return to Syncsort/Synctool

 


  • Related topics
    Replies
    Views
    Last post