IBM Mainframe Forum

by **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

Select all

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

Select all

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

by **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

Select all

---+----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 )

Select all

----+----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

Select all

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:

Select all

----+----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

by **shiitiizz** » Fri Jan 17, 2014 8:55 am

plz help people....anyone?

by **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

Select all

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

Select all

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.

IBM Mainframe Forum

Writing output record to form UPDATE query

Writing output record to form UPDATE query

Re: Writing output record to form UPDATE query

Re: Writing output record to form UPDATE query

Re: Writing output record to form UPDATE query