manojvoona
Beginner
Joined: 05 Jan 2006
Posts: 43
Topics: 14
|
 Posted: Sun Jan 21, 2007 9:46 pm Post subject: Generate Sql Statement to Update Table using SORT |
 |
|
Hi ,
We Need some help in writing update query.
We have a input file of 10,000 records with account number and update value. We need to generate the update query along with Commits in betweej out of this
| Code: |
UPDATE table_name
SET market_value = 1111.11
WHERE account_number = 1234456 ;
|
Here the market value and the account number feilds are to be replaced with the input file.
Can some one please help me in creating the sortparm to generate the above queries. I have looked in the threads but could not find the sort parm. Instead i could find fileaid.
Thanks
Manoj Voona |
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Sun Jan 21, 2007 11:13 pm Post subject: |
|
|
manojvoona,
If you are looking for a sort solution you should have posted in the Sort utilties forum instead of DB2 forum. Any try this untested DFSORT solution. The following Job generates sql statements and also adds a commit statement after every 100 records. I assumed that the account number starts at pos 1 for 6 bytes and the market value starts at pos 8 for 7 bytes.
| Code: |
//STEP0100 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD *
123456 1111.11
//SORTOUT DD SYSOUT=*
//SYSIN DD *
SORT FIELDS=COPY
INREC IFTHEN=(WHEN=INIT,
BUILD=(01,80,C';',6X,SEQNUM,3,ZD)),
IFTHEN=(WHEN=(89,2,CH,EQ,C'00'),
OVERLAY=(81:C'COMMIT;'))
OUTFIL OUTREC=(C'UPDATE TABLE_NAME ',/,
C' SET MARKET_VALUE = ',08,07,/,
C' WHERE ACCOUNT_NUMBER = ',01,06,/,
81,7,80:X)
/*
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
