IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

How to Reformat a file using File Manager in mainframe.


IBM Mainframe Forums -> All Other Mainframe Topics
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Deep S

New User


Joined: 16 May 2023
Posts: 8
Location: India

PostPosted: Tue May 16, 2023 1:59 pm
Reply with quote

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
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1334
Location: Bamberg, Germany

PostPosted: Tue May 16, 2023 3:05 pm
Reply with quote

Try some SORT product, it may provide better results for your requirement.
Back to top
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1334
Location: Bamberg, Germany

PostPosted: Tue May 16, 2023 3:54 pm
Reply with quote

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
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1334
Location: Bamberg, Germany

PostPosted: Tue May 16, 2023 5:12 pm
Reply with quote

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
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2140
Location: USA

PostPosted: Tue May 16, 2023 5:14 pm
Reply with quote

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
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1334
Location: Bamberg, Germany

PostPosted: Tue May 16, 2023 5:17 pm
Reply with quote

Although REXX and FMNZ is possible, the task is so simple to stick here with SORT.
Back to top
View user's profile Send private message
Deep S

New User


Joined: 16 May 2023
Posts: 8
Location: India

PostPosted: Tue May 16, 2023 6:49 pm
Reply with quote

Thanks Joerg. The sort card which you have shared work for my need. Many thanks.
Back to top
View user's profile Send private message
Deep S

New User


Joined: 16 May 2023
Posts: 8
Location: India

PostPosted: Thu May 18, 2023 1:57 pm
Reply with quote

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
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2140
Location: USA

PostPosted: Thu May 18, 2023 5:41 pm
Reply with quote

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
View user's profile Send private message
Joerg.Findeisen

Senior Member


Joined: 15 Aug 2015
Posts: 1334
Location: Bamberg, Germany

PostPosted: Fri May 19, 2023 10:38 am
Reply with quote

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
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2140
Location: USA

PostPosted: Fri May 19, 2023 7:18 pm
Reply with quote

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... 824.gif
Back to top
View user's profile Send private message
Deep S

New User


Joined: 16 May 2023
Posts: 8
Location: India

PostPosted: Sun May 28, 2023 4:22 pm
Reply with quote

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
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2140
Location: USA

PostPosted: Sun May 28, 2023 5:52 pm
Reply with quote

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
View user's profile Send private message
Deep S

New User


Joined: 16 May 2023
Posts: 8
Location: India

PostPosted: Sun May 28, 2023 8:20 pm
Reply with quote

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
View user's profile Send private message
sergeyken

Senior Member


Joined: 29 Apr 2008
Posts: 2140
Location: USA

PostPosted: Sun May 28, 2023 9:03 pm
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> All Other Mainframe Topics

 


Similar Topics
Topic Forum Replies
No new posts Unable to interpret a hex value to De... COBOL Programming 4
No new posts how to eliminate null indicator value... DB2 7
No new posts Email attachment from mainframe is empty PC Guides & IT News 9
No new posts Format Binary file to EBCDIC JCL & VSAM 4
No new posts Mainframe Dataset Binary or ASCII Mainframe Interview Questions 4
Search our Forums:

Back to Top