Need help on SYNCSORT

IBM Mainframe Forums -> JCL & VSAM

Quick References

View previous topic :: View next topic

Author

Message

venuhunev

New User

Joined: 26 May 2007
Posts: 70
Location: chennai

HI,

I have given my requirement below

Code:

it is a record. there are multiples of this kind in the file. I have to select 000000000162 from this record. ( i.e., from this position at 2nd line of each record) and write it in a output file with the comma placed at hte end of the each value in the output file.

Thing to note is, i should not have comma at the last record.

how to proceed ? please help

dick scherrer

Moderator Emeritus

Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

Posted: Wed Mar 19, 2008 12:51 am

Hello, I suspect you need to provide more and better details than what you have posted so far. Is this question about dfsort, syncsort, db2, or something other? You need to post a few sample inputs and what you want as output when this executes.

Craq Giegerich

Senior Member

Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

He is trying to convert a bunch of DB2 inserts into a list he can use in a SELECT with an IN clause.

Code:

SELECT * FROM XXX.YYY
WHERE ?? IN (
000000000162,

000000009999
);

What he is showing isn't A record, there are muliple records and he needs to get the value in the records following the INSERT. I would guess that the records are FB 80 and the output should be FB 80 also. A few dozen more posts and he will have provided you with all the details he has.

shrivatsa
Warnings : 1
Active User

Joined: 17 Mar 2006
Posts: 174
Location: Bangalore

here is the solution for your requirement

Code:

//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN2 DD *
INSERT INTO XXX.YYY VALUES ('2008-03-31-10.01.01.000001',
000000000162,'00049832','SOLUTION',7072562360,0,001,'2008-03-31',
'9999-12-31',12345678,0,'0001-01-01','0001-01-01','2008-01-31',
'9999-12-31','0','0','XRESE','I',' ',' ',' ',' ','01',
CURRENT TIMESTAMP ,'0001-01-01','9999-12-31',
'0001-01-01','9999-12-31');
/*
//TOOLIN DD *
COPY FROM(IN2) TO(OUT) USING(CTL3)
/*
//OUT DD DSN=&&TEMP1,UNIT=SYSDA,SPACE=(TRK,(1,1)),DISP=(,PASS)
//CTL3CNTL DD *
SORT FIELDS=COPY
OUTREC FIELDS=(1:4,13)
/*
//S2 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN3 DD DSN=&&TEMP1,DISP=OLD
/*
//TOOLIN DD *
COPY FROM(IN3) TO(OUT) USING(CTL4)
/*
//OUT DD DSN=&&TEMP2,DISP=(,PASS),SPACE=(TRK,(1,1)),UNIT=SYSDA
//CTL4CNTL DD *
SORT FIELDS=COPY
OMIT COND=(4,1,CH,EQ,C' ',OR,
8,1,CH,EQ,C' ',OR,
9,1,CH,EQ,C'-')
/*
//S2 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN8 DD DSN=&&TEMP2,DISP=OLD
/*
//TOOLIN DD *
COPY FROM(IN8) TO(OUT) USING(CTL8)
/*
//OUT DD DSN=&&TEMP7,DISP=(,PASS),SPACE=(TRK,(1,1)),UNIT=SYSDA
//CTL8CNTL DD *
INREC OVERLAY=(81:SEQNUM,8,ZD)
SORT FIELDS=(81,8,ZD,A)
OUTFIL BUILD=(1,80)
/*
//S3 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN4 DD DSN=&&TEMP2,DISP=OLD
/*
//TOOLIN DD *
COPY FROM(IN4) TO(OUT) USING(CTL5)
/*
//OUT DD DSN=&&TEMP3,DISP=(,PASS),SPACE=(TRK,(1,1)),UNIT=SYSDA
//CTL5CNTL DD *
INREC OVERLAY=(81:SEQNUM,8,ZD)
SORT FIELDS=(81,8,ZD,D)
OUTFIL ENDREC=1,BUILD=(1,80)
/*
//S3 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN5 DD DSN=&&TEMP3,DISP=OLD
/*
//TOOLIN DD *
COPY FROM(IN5) TO(OUT) USING(CTL6)
/*
//OUT DD DSN=&&TEMP5,DISP=(,PASS),SPACE=(TRK,(1,1)),UNIT=SYSDA
//CTL6CNTL DD *
OUTREC OVERLAY=(13:14,1)
/*
//S3 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN7 DD DSN=&&TEMP7,DISP=OLD
// DD DSN=&&TEMP5,DISP=OLD
/*
//TOOLIN DD *
COPY FROM(IN7) TO(OUT) USING(CTL8)
/*
//OUT DD DSN=&&TEMP9,DISP=(,PASS),SPACE=(TRK,(1,1)),UNIT=SYSDA
//CTL8CNTL DD *
SORT FIELDS=COPY
/*
//S3 EXEC PGM=SORT
//SYSOUT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SORTIN DD DSN=&&TEMP9,DISP=OLD
//SORTOUT DD SYSOUT=*
/*
//SYSIN DD *
OPTION SKIPREC=1
SORT FIELDS=COPY
/*

The above code looks big, some one can modify...

dick scherrer

Moderator Emeritus

Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

Posted: Wed Mar 19, 2008 8:11 pm

Hello, Is that tested? Please post the output from the run.

shrivatsa
Warnings : 1
Active User

Joined: 17 Mar 2006
Posts: 174
Location: Bangalore

Here is my input

Code:

INSERT INTO XXX.YYY VALUES ('2008-03-31-10.01.01.000001',
000000000162,'00049832','SOLUTION',7072562360,0,001,'2008-03-31',
'9999-12-31',12345678,0,'0001-01-01','0001-01-01','2008-01-31',
'9999-12-31','0','0','XRESE','I',' ',' ',' ',' ','01',
CURRENT TIMESTAMP ,'0001-01-01','9999-12-31',
'0001-01-01','9999-12-31');
INSERT INTO XXX.YYY VALUES ('2008-03-31-10.01.01.000001',
000000000163,'00049832','SOLUTION',7072562360,0,001,'2008-03-31',
'9999-12-31',12345678,0,'0001-01-01','0001-01-01','2008-01-31',
'9999-12-31','0','0','XRESE','I',' ',' ',' ',' ','01',
CURRENT TIMESTAMP ,'0001-01-01','9999-12-31',
'0001-01-01','9999-12-31');
INSERT INTO XXX.YYY VALUES ('2008-03-31-10.01.01.000001',
000000000164,'00049832','SOLUTION',7072562360,0,001,'2008-03-31',
'9999-12-31',12345678,0,'0001-01-01','0001-01-01','2008-01-31',
'9999-12-31','0','0','XRESE','I',' ',' ',' ',' ','01',
CURRENT TIMESTAMP ,'0001-01-01','9999-12-31',
'0001-01-01','9999-12-31');
INSERT INTO XXX.YYY VALUES ('2008-03-31-10.01.01.000001',
000000000165,'00049832','SOLUTION',7072562360,0,001,'2008-03-31',
'9999-12-31',12345678,0,'0001-01-01','0001-01-01','2008-01-31',
'9999-12-31','0','0','XRESE','I',' ',' ',' ',' ','01',
CURRENT TIMESTAMP ,'0001-01-01','9999-12-31',
'0001-01-01','9999-12-31');
INSERT INTO XXX.YYY VALUES ('2008-03-31-10.01.01.000001',
000000000166,'00049832','SOLUTION',7072562360,0,001,'2008-03-31',
'9999-12-31',12345678,0,'0001-01-01','0001-01-01','2008-01-31',
'9999-12-31','0','0','XRESE','I',' ',' ',' ',' ','01',
CURRENT TIMESTAMP ,'0001-01-01','9999-12-31',
'0001-01-01','9999-12-31');
INSERT INTO XXX.YYY VALUES ('2008-03-31-10.01.01.000001',
000000000167,'00049832','SOLUTION',7072562360,0,001,'2008-03-31',
'9999-12-31',12345678,0,'0001-01-01','0001-01-01','2008-01-31',
'9999-12-31','0','0','XRESE','I',' ',' ',' ',' ','01',
CURRENT TIMESTAMP ,'0001-01-01','9999-12-31',
'0001-01-01','9999-12-31');
INSERT INTO XXX.YYY VALUES ('2008-03-31-10.01.01.000001',
000000000168,'00049832','SOLUTION',7072562360,0,001,'2008-03-31',
'9999-12-31',12345678,0,'0001-01-01','0001-01-01','2008-01-31',
'9999-12-31','0','0','XRESE','I',' ',' ',' ',' ','01',
CURRENT TIMESTAMP ,'0001-01-01','9999-12-31',
'0001-01-01','9999-12-31');
INSERT INTO XXX.YYY VALUES ('2008-03-31-10.01.01.000001',
000000000169,'00049832','SOLUTION',7072562360,0,001,'2008-03-31',
'9999-12-31',12345678,0,'0001-01-01','0001-01-01','2008-01-31',
'9999-12-31','0','0','XRESE','I',' ',' ',' ',' ','01',
CURRENT TIMESTAMP ,'0001-01-01','9999-12-31',
'0001-01-01','9999-12-31');
INSERT INTO XXX.YYY VALUES ('2008-03-31-10.01.01.000001',
000000000170,'00049832','SOLUTION',7072562360,0,001,'2008-03-31',
'9999-12-31',12345678,0,'0001-01-01','0001-01-01','2008-01-31',
'9999-12-31','0','0','XRESE','I',' ',' ',' ',' ','01',
CURRENT TIMESTAMP ,'0001-01-01','9999-12-31',
'0001-01-01','9999-12-31');
INSERT INTO XXX.YYY VALUES ('2008-03-31-10.01.01.000001',
000000000171,'00049832','SOLUTION',7072562360,0,001,'2008-03-31',
'9999-12-31',12345678,0,'0001-01-01','0001-01-01','2008-01-31',
'9999-12-31','0','0','XRESE','I',' ',' ',' ',' ','01',
CURRENT TIMESTAMP ,'0001-01-01','9999-12-31',
'0001-01-01','9999-12-31');

Output I got is

Code:

000000000163,
000000000164,
000000000165,
000000000166,
000000000167,
000000000168,
000000000169,
000000000170,
000000000171,
000000000162

dick scherrer

Moderator Emeritus

Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

Posted: Thu Mar 20, 2008 7:13 pm

Cool Thanks, d

rajatbagga

Active User

Joined: 11 Mar 2007
Posts: 199
Location: india

Hello venuhunev,

You can also try out with below DFSORT code which will exactly meet your requrement:

Code:

//VZM1CKKN JOB (3GAHF3,R),
// 'RAJAT TEST',CLASS=X,MSGCLASS=Y,NOTIFY=&SYSUID
//S1 EXEC PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG DD SYSOUT=*
//IN1 DD *
INSERT INTO XXX.YYY VALUES ('2008-03-31-10.01.01.000001',
000000000162,'00049832','SOLUTION',7072562360,0,001,'2008-03-31',
'9999-12-31',12345678,0,'0001-01-01','0001-01-01','2008-01-31',
'9999-12-31','0','0','XRESE','I',' ',' ',' ',' ','01',
CURRENT TIMESTAMP ,'0001-01-01','9999-12-31',
'0001-01-01','9999-12-31');
INSERT INTO XXX.YYY VALUES ('2008-03-31-10.01.01.000001',
000000000164,'00049832','SOLUTION',7072562360,0,001,'2008-03-31',
'9999-12-31',12345678,0,'0001-01-01','0001-01-01','2008-01-31',
'9999-12-31','0','0','XRESE','I',' ',' ',' ',' ','01',
CURRENT TIMESTAMP ,'0001-01-01','9999-12-31',
'0001-01-01','9999-12-31');
INSERT INTO XXX.YYY VALUES ('2008-03-31-10.01.01.000001',
000000000169,'00049832','SOLUTION',7072562360,0,001,'2008-03-31',
'9999-12-31',12345678,0,'0001-01-01','0001-01-01','2008-01-31',
'9999-12-31','0','0','XRESE','I',' ',' ',' ',' ','01',
CURRENT TIMESTAMP ,'0001-01-01','9999-12-31',
'0001-01-01','9999-12-31');
INSERT INTO XXX.YYY VALUES ('2008-03-31-10.01.01.000001',
000000000160,'00049832','SOLUTION',7072562360,0,001,'2008-03-31',
'9999-12-31',12345678,0,'0001-01-01','0001-01-01','2008-01-31',
'9999-12-31','0','0','XRESE','I',' ',' ',' ',' ','01',
CURRENT TIMESTAMP ,'0001-01-01','9999-12-31',
'0001-01-01','9999-12-31');
/*
//TMP1 DD DSN=&&TEMP1,UNIT=SYSDA,SPACE=(TRK,(1,1)),DISP=(,PASS)
//TOOLIN DD *
COPY FROM(IN1) TO(TMP1) USING(CTL1)
/*
//CTL1CNTL DD *
SORT FIELDS=COPY
OMIT COND=(4,1,CH,EQ,C'E',OR,
4,1,CH,EQ,C'''',OR,
4,1,CH,EQ,C'C')
OUTREC FIELDS=(1:4,12,81:SEQNUM,8,ZD)
/*
//S1 EXEC PGM=ICEMAN
//SYSOUT DD SYSOUT=*
//SORTIN DD DSN=&&TEMP1,UNIT=SYSDA,SPACE=(TRK,(1,1)),DISP=(MOD,PASS)
//SYM DD DSN=&&S1,UNIT=SYSDA,SPACE=(TRK,(1,1)),DISP=(,PASS)
//SYSIN DD *
OPTION COPY
OUTFIL FNAMES=SYM,REMOVECC,NODETAIL,
BUILD=(80X),
TRAILER1=('LASTRCD,+',COUNT=(M11,LENGTH=8))
/*
//S2 EXEC PGM=ICEMAN
//SYSOUT DD SYSOUT=*
//SYMNAMES DD DSN=&&S1,DISP=(OLD,PASS)
//SORTIN DD DSN=&&TEMP1,UNIT=SYSDA,SPACE=(TRK,(1,1)),DISP=(MOD,PASS)
//SORTOUT DD SYSOUT=*
//SYSIN DD *
OPTION COPY
INREC IFOUTLEN=23,
IFTHEN=(WHEN=INIT,
BUILD=(1,12,C',',24:SEQNUM,8,ZD)),
IFTHEN=(WHEN=(24,8,ZD,EQ,LASTRCD),
OVERLAY=(13:X))
/*

OUTPUT :

Code:

000000000162,
000000000164,
000000000169,
000000000160

I hope this will help you.....

Rajat

venuhunev

New User

Joined: 26 May 2007
Posts: 70
Location: chennai

Posted: Mon Mar 24, 2008 4:27 pm

Thanks for your replies. Your suggestions helped me

View previous topic :: :: View next topic

View Bookmarks
All times are GMT + 6 Hours

Forum Index -> JCL & VSAM

Topic		Forum	Replies
Similar Topics
	Compare only first records of the fil...	SYNCSORT	7
	Count Records with a crietaria in a f...	DFSORT/ICETOOL	5
	DFSORT/SYNCSORT/ICETOOL	JCL & VSAM	8
	Syncsort "Y2C" Function	SYNCSORT	1
	Arithmetic division using Syncsort	SYNCSORT	6

Search our Forums:

IBMMainframes.com is not an official and/or affiliated with IBM^® in anyway
Board Rules \| FAQ \| Downloads \| Wiki \| SiteMap \| Contact Us