Writing output record to form UPDATE query



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

Writing output record to form UPDATE query

Postby shiitiizz » Tue Jan 07, 2014 5:02 pm

Hi,

I have a PS File LRECL 133 having records as below

--+----3----+----4----+----5----+----6----+----7----+----8----+----9----
***************************** Top of Data ******************************
  XXXXX         |   2S65            | AB                | 2014-01-07   
  AAAAA         |   2N15            | DC                | 2014-01-07   
 BBBBB          |   CN15            | CC                | 2014-01-07   


I need to write the output file (making DB2 UPDATE query ) taking the values from the above file, sample shown below
 UPDATE NEDY1.AAADM.AAT_052_UPDTCNTL                 
 SET (DT_DB2_UPDT_DATE,DT_PART_UPDT_DT)               
      = ('2013-01-06','2013-01-06')                   
 WHERE (NO_PARTNUM_PREFIX = '  XXXX'   AND           
        NO_PART_NUM_BASE  = '  YYYYY' AND           
        NO_PARTNUM_SUFFIX = 'ZZ');                   


I am trying to do form this query in a o/p dataset using sort, we can get the hard-coded names of tables fields etc using OUTFIL
OUTFIL REMOVECC,                                                       
        HEADER2=(5:'UPDATE NEDY1.AAADM.AAT_052_UPDTCNTL',/,           
                 5:'SET (DT_DB2_UPDT_DATE,DT_PART_UPDT_DT)=')         

For SET part, I need to take dates from input file, deduct a year and form a query as shown in sample above.

Also, for the WHERE section, it needs to read record from input file, picks data from respective position and append to WHERE clause.

Kindly suggest on how to progress
shiitiizz
 
Posts: 16
Joined: Thu Dec 06, 2012 2:30 pm
Has thanked: 3 times
Been thanked: 0 time

Re: Writing output record to form UPDATE query

Postby shiitiizz » Tue Jan 07, 2014 8:02 pm

Hi...Just an update on what I tried and where I reached...
So my i/p file is having data as below, LRECL=133
---+----3----+----4----+----5----+----6----+----7----+----8----+----9---
***************************** Top of Data ******************************
   XXXXX         |   2S65            | AB                | 2014-01-07 
   AAAAA         |   2N15            | DC                | 2014-01-07 
  BBBBBB         |   CN15            | CC                | 2014-01-07 


And desired output is ( for all records in i/p file a set of update query as below )
----+----1----+----2----+----3----+----4----+----5----+----6----+--
***************************** Top of Data *************************
    UPDATE NEDY1.AAADM.AAT_052_UPDTCNTL                           
    SET (DT_DB2_UPDT_DATE)=                                       
    ('2013-01-06')                                                 
    WHERE (NO_PARTNUM_PREFIX = '  2S65'   AND                     
           NO_PART_NUM_BASE  = '  XXXXX' AND                     
           NO_PARTNUM_SUFFIX = 'AB');                             


The code I have written is
SORT FIELDS=COPY   
OUTFIL REMOVECC,                                                       
HEADER2=(5:'UPDATE NEDY1.AAADM.AAT_052_UPDTCNTL',/,                   
               5:'SET (DT_DB2_UPDT_DATE,DT_PART_UPDT_DT)=',/,         
               5:'(',6:82,10,16:')',/,                                 
               5:'WHERE (NO_PARTNUM_PREFIX = ',                       
               33:42,6,44:'AND',/,                                     
               5:'NO_PART_NUM_BASE  = ',                               
               33:23,8,44:'AND',/,                                     
               5:'NO_PARTNUM_SUFFIX = ',                               
               33:62,8,42:')')                                         


I am getting o/p as below:
----+----1----+----2----+----3----+----4----+----5----
********************************* Top of Data ********
    UPDATE NEDY1.AAADM.AAT_052_UPDTCNTL               
    SET (DT_DB2_UPDT_DATE,DT_PART_UPDT_DT)=           
    (          )                                     
    WHERE (NO_PARTNUM_PREFIX =             AND       
    NO_PART_NUM_BASE  =                    AND       
    NO_PARTNUM_SUFFIX =                  )           


Having two issues now..

Whats the mistake that date field and AND the NO_PARTNUM_PREFIX/BASE/PREFIX is getting populated?
Also, I would need them in quotes ( as shown in desired output snippet )
How to achieve that ?

Thanks
shiitiizz
 
Posts: 16
Joined: Thu Dec 06, 2012 2:30 pm
Has thanked: 3 times
Been thanked: 0 time

Re: Writing output record to form UPDATE query

Postby shiitiizz » Fri Jan 17, 2014 8:55 am

plz help people....anyone?
shiitiizz
 
Posts: 16
Joined: Thu Dec 06, 2012 2:30 pm
Has thanked: 3 times
Been thanked: 0 time

Re: Writing output record to form UPDATE query

Postby Thampy » Sun Jan 19, 2014 5:11 pm

I tried the below Sort JCL using the data you provided. I copied the data to a FB file of LRECL 133 and ran the SORT JCL. The results looks okay to me.
If the record format is FBA, then you have to adjust the position accordingly

SORT FIELDS=COPY                                                 
 OUTFIL BUILD=(5:C'UPDATE NEDY1.AAADM.AAT_052_UPDTCNTL',/,       
                5:C'SET (DT_DB2_UPDT_DATE,DT_PART_UPDT_DT)=',/,   
                5:C'(',C'''',81,10,C''')',/,                     
                5:C'WHERE (NO_PARTNUM_PREFIX = ',                 
                33:C'''',43,4,C'''',C' AND',/,                   
                5:C'NO_PART_NUM_BASE  = ',                       
                33:C'''',22,8,C'''',C' AND',/,                   
                5:C'NO_PARTNUM_SUFFIX = ',                       
                33:C'''',61,2,C'''',C');',80:X)   


Output
    UPDATE NEDY1.AAADM.AAT_052_UPDTCNTL                   
    SET (DT_DB2_UPDT_DATE,DT_PART_UPDT_DT)=               
    ('2014-01-07')                                         
    WHERE (NO_PARTNUM_PREFIX =  '2S65' AND                 
    NO_PART_NUM_BASE  =         '   XXXXX' AND             
    NO_PARTNUM_SUFFIX =         'AB');                     
    UPDATE NEDY1.AAADM.AAT_052_UPDTCNTL                   
    SET (DT_DB2_UPDT_DATE,DT_PART_UPDT_DT)=               
    ('2014-01-07')                                         
    WHERE (NO_PARTNUM_PREFIX =  '2N15' AND                 
    NO_PART_NUM_BASE  =         '   AAAAA' AND             
    NO_PARTNUM_SUFFIX =         'DC');                     
    UPDATE NEDY1.AAADM.AAT_052_UPDTCNTL                   
    SET (DT_DB2_UPDT_DATE,DT_PART_UPDT_DT)=               
    ('2014-01-07')                                         
    WHERE (NO_PARTNUM_PREFIX =  'CN15' AND                 
    NO_PART_NUM_BASE  =         '  BBBBBB' AND             
    NO_PARTNUM_SUFFIX =         'CC');                     
Code'd

N.B If you expect suffix and prefix field contain varying characters, then the above sort won't work. Have to code some trickly sort to handle that scenario.
Thampy
 
Posts: 36
Joined: Sat Sep 26, 2009 2:27 pm
Has thanked: 0 time
Been thanked: 3 times


Return to Syncsort/Synctool

 


  • Related topics
    Replies
    Views
    Last post