how to split file

In this Mainframe Forum - You can post your queries on DFSORT, ICETOOL , SyncSort & JCL Utilities

Moderators: Frank Yaeger, Moderator Group

Post Reply
amolpawar
Member
Posts: 25
Joined: Mon Jun 29, 2009 2:52 pm

how to split file

Post by amolpawar » Thu Jul 22, 2010 2:53 pm

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.

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Thu Jul 22, 2010 9:53 pm

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

amolpawar
Member
Posts: 25
Joined: Mon Jun 29, 2009 2:52 pm

Post by amolpawar » Fri Jul 23, 2010 9:28 am

Below is the format of file. i want to seperate the file by using "table" keywords.
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

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Fri Jul 23, 2010 8:37 pm

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

amolpawar
Member
Posts: 25
Joined: Mon Jun 29, 2009 2:52 pm

Post by amolpawar » Mon Jul 26, 2010 11:21 am

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.

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Mon Jul 26, 2010 8:42 pm

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

amolpawar
Member
Posts: 25
Joined: Mon Jun 29, 2009 2:52 pm

Post by amolpawar » Mon Aug 02, 2010 5:05 pm

Hi,

Thank You !

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 help me.

Thanks,
Amol Pawar

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Mon Aug 02, 2010 9:49 pm

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?
I don't know what you mean by this. It doesn't seem to relate to your earlier example. Again, for this new situation:

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

amolpawar
Member
Posts: 25
Joined: Mon Jun 29, 2009 2:52 pm

Post by amolpawar » Tue Aug 03, 2010 7:03 am

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.

User avatar
Frank Yaeger
Moderator
Posts: 812
Joined: Sat Feb 18, 2006 5:45 am
Location: San Jose, CA
Contact:

Post by Frank Yaeger » Tue Aug 03, 2010 10:35 pm

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

Post Reply

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



Other References
Mainframe Tools and others