how to split file
Moderators: Frank Yaeger, Moderator Group
how to split file
HI,
i am having a file which contians sql queries for 12 tables , at the starting of each query threre is a word "TABLE".
I want to seperate that queries into differerent files.
Please hel me.
i am having a file which contians sql queries for 12 tables , at the starting of each query threre is a word "TABLE".
I want to seperate that queries into differerent files.
Please hel me.
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
You can use DFSORT's GROUP function to do this kind of thing, but I need more information to show you how.
Please show an example of the records in your input file and what you expect for output. Give the RECFM and LRECL of your input file. Give the starting position, length and format of the relevant fields.
Please show an example of the records in your input file and what you expect for output. Give the RECFM and LRECL of your input file. Give the starting position, length and format of the relevant fields.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Below is the format of file. i want to seperate the file by using "table" keywords.
seperate file is for each table.
seperate file is for each table.
Code: Select all
Table01 tablename
INSERT INTO tablename(
4,
200,
'MY',
');
SQLend
Table02 tablename2
SQLstart
INSERT INTO Vtablename2 VALUES (
4,
'2',
'P ',
200,
);
SQLend
SQLstart
UPDATE table3
SET
name = 'amol',
SQLend
FileEnd
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
As asked previously:
Please show the expected output for your input example.
Give the RECFM and LRECL of your input file. Give the starting position, length and format of the relevant fields.
If you can't be bothered to give me the information I ask for, then I can't be bothered to write the job for you. There's no reason I should have to guess.
Please show the expected output for your input example.
Give the RECFM and LRECL of your input file. Give the starting position, length and format of the relevant fields.
If you can't be bothered to give me the information I ask for, then I can't be bothered to write the job for you. There's no reason I should have to guess.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Hi,
below is my input file.
Table01 table1
SQLstart
INSERT INTO table1 VALUES (
4000100,
200,
'%TIMESTAMP%',
'%TIMESTAMP%',
0 ),
SQLend
Table02 table2
SQLstart
INSERT INTO table2 VALUES (
'2010',
200,
'%TIMESTAMP%',
'%TIMESTAMP%',
' ',
'300 ',
'N',
'N');
SQLend
SQLstart
UPDATE table3
SET
name = 'amol',
WHERE
class = 'A ' AND
sub_cls = 'B';
SQLend
FileEnd
================
I want to split this file into two files by using keyword as TABLE01 and TABLE02 and so on ---.
These file should contain data between "TABLE01 AND TABLE02" and "TABLE02 AND TABLE03" and so on (upto 12 tables).
number of tables should equal to number of files.
TABLEXX is at starting having length of 7
FILE DETAILS.
RECFM = FB
LRECL = 80
Please help me.
Thanks,
Amol Pawar.
below is my input file.
Table01 table1
SQLstart
INSERT INTO table1 VALUES (
4000100,
200,
'%TIMESTAMP%',
'%TIMESTAMP%',
0 ),
SQLend
Table02 table2
SQLstart
INSERT INTO table2 VALUES (
'2010',
200,
'%TIMESTAMP%',
'%TIMESTAMP%',
' ',
'300 ',
'N',
'N');
SQLend
SQLstart
UPDATE table3
SET
name = 'amol',
WHERE
class = 'A ' AND
sub_cls = 'B';
SQLend
FileEnd
================
I want to split this file into two files by using keyword as TABLE01 and TABLE02 and so on ---.
These file should contain data between "TABLE01 AND TABLE02" and "TABLE02 AND TABLE03" and so on (upto 12 tables).
number of tables should equal to number of files.
TABLEXX is at starting having length of 7
FILE DETAILS.
RECFM = FB
LRECL = 80
Please help me.
Thanks,
Amol Pawar.
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
Here's a DFSORT job that will do what I think you're asking for:
Code: Select all
//S1 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=... input file (FB/80)
//OUT01 DD DSN=... output file1 (FB/80)
//OUT02 DD DSN=... output file2 (FB/80)
//OUT03 DD DSN=... output file3 (FB/80)
...
//OUT12 DD DSN=... output file12 (FB/80)
//SYSIN DD *
OPTION COPY
INREC IFTHEN=(WHEN=GROUP,BEGIN=(1,5,CH,EQ,C'Table'),
PUSH=(81:ID=2))
OUTFIL FNAMES=OUT01,INCLUDE=(81,2,ZD,EQ,1),BUILD=(1,80)
OUTFIL FNAMES=OUT02,INCLUDE=(81,2,ZD,EQ,2),BUILD=(1,80)
OUTFIL FNAMES=OUT03,INCLUDE=(81,2,ZD,EQ,3),BUILD=(1,80)
...
OUTFIL FNAMES=OUT12,INCLUDE=(81,2,ZD,EQ,12),BUILD=(1,80)
/*
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
I don't know what you mean by this. It doesn't seem to relate to your earlier example. Again, for this new situation:I have done with spliting of file using a specific keyword, but in a file instead of ' %TIMESTAMP%', I want ' current timestamp' . Is it possible using JCL?
Please show an example of the records in your input file and what you expect for output. Give the RECFM and LRECL of your input file. Give the starting position, length and format of the relevant fields.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Hi,
below is my input file.
Table01 table1
SQLstart
INSERT INTO table1 VALUES (
4000100,
200,
'%TIMESTAMP%',
'%TIMESTAMP%',
0 ),
SQLend
Table02 table2
SQLstart
INSERT INTO table2 VALUES (
'2010',
200,
'%TIMESTAMP%',
'%TIMESTAMP%',
' ',
'300 ',
'N',
'N');
SQLend
SQLstart
UPDATE table3
SET
name = 'amol',
WHERE
class = 'A ' AND
sub_cls = 'B';
SQLend
FileEnd
This file is same as previous file.
I have seperated it by using TABLE01 and TABLE 02 etc.
following is the output
file1
INSERT INTO table1 VALUES (
4000100,
200,
'%TIMESTAMP%',
'%TIMESTAMP%',
0 ),
===========
file2
INSERT INTO table2 VALUES (
'2010',
200,
'%TIMESTAMP%',
'%TIMESTAMP%',
' ',
'300 ',
'N',
'N');
UPDATE table3
SET
name = 'amol',
WHERE
class = 'A ' AND
sub_cls = 'B';
================
but instead of '%TIMESTAMP%', i want "current timestamp" in file.
Is it possible using jcl?
Please help me.
Thanks,
Amol Pawar.
below is my input file.
Table01 table1
SQLstart
INSERT INTO table1 VALUES (
4000100,
200,
'%TIMESTAMP%',
'%TIMESTAMP%',
0 ),
SQLend
Table02 table2
SQLstart
INSERT INTO table2 VALUES (
'2010',
200,
'%TIMESTAMP%',
'%TIMESTAMP%',
' ',
'300 ',
'N',
'N');
SQLend
SQLstart
UPDATE table3
SET
name = 'amol',
WHERE
class = 'A ' AND
sub_cls = 'B';
SQLend
FileEnd
This file is same as previous file.
I have seperated it by using TABLE01 and TABLE 02 etc.
following is the output
file1
INSERT INTO table1 VALUES (
4000100,
200,
'%TIMESTAMP%',
'%TIMESTAMP%',
0 ),
===========
file2
INSERT INTO table2 VALUES (
'2010',
200,
'%TIMESTAMP%',
'%TIMESTAMP%',
' ',
'300 ',
'N',
'N');
UPDATE table3
SET
name = 'amol',
WHERE
class = 'A ' AND
sub_cls = 'B';
================
but instead of '%TIMESTAMP%', i want "current timestamp" in file.
Is it possible using jcl?
Please help me.
Thanks,
Amol Pawar.
- Frank Yaeger
- Moderator
- Posts: 812
- Joined: Sat Feb 18, 2006 5:45 am
- Location: San Jose, CA
- Contact:
So you want to replace %TIMESTAMP% with the "current timestamp"?
I guess I'm supposed to read your mind to know what exactly you want for the current timestamp and the answers to my other questions.
I imagine you can do what you want (whatever it is exactly) with DFSORT's FINDREP or IFTHEN function, depending on where
%TIMESTAMP% can appear (only starting in position 1? anywhere in the record?).
If you give me more details, I could give you more help.
I guess I'm supposed to read your mind to know what exactly you want for the current timestamp and the answers to my other questions.
I imagine you can do what you want (whatever it is exactly) with DFSORT's FINDREP or IFTHEN function, depending on where
%TIMESTAMP% can appear (only starting in position 1? anywhere in the record?).
If you give me more details, I could give you more help.
Frank Yaeger - DFSORT Development Team (IBM) - yaeger@us.ibm.com
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
Specialties: JOINKEYS, FINDREP, WHEN=GROUP, ICETOOL, Symbols, Migration
=> DFSORT/MVS is on the Web at http://www.ibm.com/storage/dfsort
FREE TUTORIALS
Tutorials
Free tutorials from mainframegurukul
- JCL Tutorial
Covers all important JCL concepts. - Cobol Tutorial
This tutorials covers all Cobol Topics from STRING to COMP-3. - DB2 Tutorial
DB2 Tutorial focuses on DB2 COBOL Programming. - SORT Tutorial
This Tutorial covers all important aspects of DFSORT with examples - CICS Tutorial
This CICS tutorial covers CICS concepts and CICS Basics, CICS COBOL Programming.
Interview
Mainframe Interview questions
- Cobol Interview Questions
50+ Interview Questions - JCL Interview Questions
50+ Interview Questions - DB2 Interview Questions
100+ Interview Questions - CICS Interview Questions
70+ Interview Questions - VSAM Interview Questions
27 Interview Questions
Other References
Mainframe Tools and others
- XPEDITER Reference
Explains how we can debug a program - FILEAID Reference
Explains how to browse , edit and delete datasets - Change Man Reference
Quick Start tutorial on Changeman - Abend Reference
Important Abend codes explained - FaceBook Page
MainframeGurukul FaceBook Page - LinkedIn Page
MainframeGurkul Linkedin Page