|
View previous topic :: View next topic
|
| Author |
Message |
Deep S
New User
Joined: 16 May 2023
Posts: 6
Location: India
|
|
|
|
Hi All,
Thanks for your help in advance!
I need help on below query.
I want to update a column of db2 table for 50 K rows. I will have key values of 50 k records in an Input file. I want to use file manager tool to create a 50 k update query. But after every 500 records commit should be there.
Input file :
1999-08-27-17.24.19.833872
2001-08-27-17.24.19.833872
Like this 50 k records
Output expected
UPDATE ACCTQAP.ACCT_ABCD
SET USE_UPID = ‘ABCDEF’
WHERE XYZ_ACCT = ‘1999-08-27-17.24.19.833872’;
UPDATE ACCTQAP.ACCT_ABCD
SET USE_UPID = ‘ABCDEF’
WHERE XYZ_ACCT = ‘2001-08-27-17.24.19.833872’;
So on until 500 records then after that
COMMIT;
Again records query from 501th record from input.
I do have code snippet for this to achieve using file aid pgm but file aid do not get used at my organisation instead file manager get used now. Hence looking for solution using file manager program PGM=FMNMAIN |
|
| Back to top |
|
 |
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1255
Location: Bamberg, Germany
|
|
|
|
| Try some SORT product, it may provide better results for your requirement. |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1255
Location: Bamberg, Germany
|
|
|
|
| Code: |
//WHATEVER EXEC PGM=SORT
//SORTIN DD *
1999-08-27-17.24.19.833872
2001-08-27-17.24.19.833872
/*
//SYSOUT DD SYSOUT=*
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
INREC IFTHEN=(WHEN=GROUP,RECORDS=500,PUSH=(81:ID=8))
OUTFIL FNAMES=(SORTOUT),
REMOVECC,
BUILD=(C'UPDATE ACCTQAP.ACCT_ABCD',/,
C'SET USE_UPID = ABCDEF',/,
1,80,SQZ=(SHIFT=LEFT,LEAD=C'WHERE XYZ_ACCT =',
TRAIL=C';')),
SECTIONS=(81,8,
TRAILER3=(C'COMMIT;'))
END
/* |
DD:SORTOUT will hold your SQLCARDS.
PS: Do not use attachments, use the Code Tag button above the edit box to format code/data instead. |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1255
Location: Bamberg, Germany
|
|
|
|
Minor update:
| Code: |
OPTION COPY
INREC IFTHEN=(WHEN=GROUP,RECORDS=500,PUSH=(81:ID=8))
OUTFIL FNAMES=(SORTOUT),
REMOVECC,
BUILD=(C'UPDATE ACCTQAP.ACCT_ABCD',/,
C'SET USE_UPID = ''ABCDEF''',/,
1,80,SQZ=(SHIFT=LEFT,LEAD=C'WHERE XYZ_ACCT =''',
TRAIL=C''';')),
SECTIONS=(81,8,
TRAILER3=(C'COMMIT;'))
END |
| Code: |
****** **************************** Datenanfang *******
000001 UPDATE ACCTQAP.ACCT_ABCD
000002 SET USE_UPID = 'ABCDEF'
000003 WHERE XYZ_ACCT ='1999-08-27-17.24.19.833872';
000004 UPDATE ACCTQAP.ACCT_ABCD
000005 SET USE_UPID = 'ABCDEF'
000006 WHERE XYZ_ACCT ='2001-08-27-17.24.19.833872';
000007 COMMIT;
****** **************************** Datenende ********* |
|
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2023
Location: USA
|
|
|
|
| Deep S wrote: |
Hi All,
Thanks for your help in advance!
I need help on below query.
|
1. Please!!! Do not use screenshots, and other attachments in your messages.
2. Please!!! Learn how to use the Code button to emphasize your samples of code, and/or data.
3. FILEAID is too far from any text processing tool you need. SORT products are much closer to what you need, but the most suitable tool is REXX code. |
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1255
Location: Bamberg, Germany
|
|
|
|
| Although REXX and FMNZ is possible, the task is so simple to stick here with SORT. |
|
| Back to top |
|
|
Deep S
New User
Joined: 16 May 2023
Posts: 6
Location: India
|
|
|
|
| Thanks Joerg. The sort card which you have shared work for my need. Many thanks. |
|
| Back to top |
|
|
Deep S
New User
Joined: 16 May 2023
Posts: 6
Location: India
|
|
|
|
Can above sort Card used if we have to key fields coming in the input file. And both key will be part of reformatted query (on two different columns).
Input file :
1999-08-27-17.24.19.833872EFGHIJ
2001-08-27-17.24.19.833872MNOPQ
Like this 50 k records
Output expected
UPDATE ACCTQAP.ACCT_ABCD
SET USE_UPID = ‘ABCDEF’
WHERE XYZ_ACCT = ‘1999-08-27-17.24.19.833872’
AND ACCT_CODE= ‘EFGHIJ’;
UPDATE ACCTQAP.ACCT_ABCD
SET USE_UPID = ‘ABCDEF’
WHERE XYZ_ACCT = ‘2001-08-27-17.24.19.833872’
AND ACCT_CODE= ‘MNOPQ’;
.
.
After 500 records
COMMIT;
Then again query for 501th records |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2023
Location: USA
|
|
|
|
Please!!! Learn how to use the Code button to emphasize your samples of code, and/or data.
Please!!! Learn how to use the Code button to emphasize your samples of code, and/or data.
Please!!! Learn how to use the Code button to emphasize your samples of code, and/or data.
Please!!! Learn how to use the Code button to emphasize your samples of code, and/or data.
Please!!! Learn how to use the Code button to emphasize your samples of code, and/or data.
|
|
| Back to top |
|
|
Joerg.Findeisen
Senior Member
Joined: 15 Aug 2015
Posts: 1255
Location: Bamberg, Germany
|
|
|
|
The provided code can be easily modified to achieve the desired output.
| Code: |
****** **************************** Datenanfang *****
000001 UPDATE ACCTQAP.ACCT_ABCD
000002 SET USE_UPID = 'ABCDEF'
000003 WHERE XYZ_ACCT = '1999-08-27-17.24.19.833872'
000004 AND ACCT_CODE= 'EFGHIJ';
000005 UPDATE ACCTQAP.ACCT_ABCD
000006 SET USE_UPID = 'ABCDEF'
000007 WHERE XYZ_ACCT = '2001-08-27-17.24.19.833872'
000008 AND ACCT_CODE= 'MNOPQ';
000009 COMMIT;
****** **************************** Datenende ******* |
|
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2023
Location: USA
|
|
|
|
| Joerg.Findeisen wrote: |
The provided code can be easily modified to achieve the desired output.
| Code: |
****** **************************** Datenanfang *****
000001 UPDATE ACCTQAP.ACCT_ABCD
000002 SET USE_UPID = 'ABCDEF'
000003 WHERE XYZ_ACCT = '1999-08-27-17.24.19.833872'
000004 AND ACCT_CODE= 'EFGHIJ';
000005 UPDATE ACCTQAP.ACCT_ABCD
000006 SET USE_UPID = 'ABCDEF'
000007 WHERE XYZ_ACCT = '2001-08-27-17.24.19.833872'
000008 AND ACCT_CODE= 'MNOPQ';
000009 COMMIT;
****** **************************** Datenende ******* |
|
The TS wanted to get data for his WHERE conditions from a separate file...
But something prevented him from reading manuals...  |
|
| Back to top |
|
|
Deep S
New User
Joined: 16 May 2023
Posts: 6
Location: India
|
|
|
|
| I understand same sort card can be used, only problem I am having is for SQZ line. How to ensure only certain length(timestamp)from input goes to one where clause and other length( user) goes to another where clause? |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2023
Location: USA
|
|
|
|
| Deep S wrote: |
| I understand same sort card can be used, only problem I am having is for SQZ line. How to ensure only certain length(timestamp)from input goes to one where clause and other length( user) goes to another where clause? |
What is this “SQZ line”? The one from the provided example is applied to each input line, separately.
Please, try to express your thoughts in the manner for others to be able to understand, too. Otherwise it makes no sense to post unclear sentences. |
|
| Back to top |
|
|
Deep S
New User
Joined: 16 May 2023
Posts: 6
Location: India
|
|
|
|
| I am referring to above sort card where SQZ is used. I understand this applies to each record. Earlier Each Input has only one key. Now Each Input record has two key. How to modify that sort card such a way that for each input record both keys go to two different where clause? Or if I want to simplify my problem I am looking for output exactly what Joerg shares in his last comment. What Shall I modify in the sort card mentioned above? |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2023
Location: USA
|
|
|
|
| Deep S wrote: |
| I am referring to above sort card where SQZ is used. I understand this applies to each record. Earlier Each Input has only one key. Now Each Input record has two key. How to modify that sort card such a way that for each input record both keys go to two different where clause? Or if I want to simplify my problem I am looking for output exactly what Joerg shares in his last comment. What Shall I modify in the sort card mentioned above? |
You need to extract SEPARATE fields from each of your input records, and then substitute each extracted field separately into the output record, or into several output records.
The above said is the basic on which the SORT utility is designed from the very beginning. One has to read at least the basic manual before asking such questions.
Joerg.Findeisen has already done more than 50% of your job for you, free of charge!
THIS IS A HELP FORUM, NOT A "DO-MY-JOB-FOR-ME" FORUM. |
|
| Back to top |
|
|
|
|
