Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need help on SYNCSORT

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
venuhunev

New User


Joined: 26 May 2007
Posts: 70
Location: chennai

PostPosted: Tue Mar 18, 2008 9:27 pm    Post subject: Need help on SYNCSORT
Reply with quote

HI,

I have given my requirement below

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');                                           



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

dick scherrer

Site Director


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

PostPosted: Wed Mar 19, 2008 12:51 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


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

PostPosted: Wed Mar 19, 2008 1:08 am    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
shrivatsa
Warnings : 1

Active User


Joined: 17 Mar 2006
Posts: 171
Location: Bangalore

PostPosted: Wed Mar 19, 2008 3:59 pm    Post subject: Reply to: Need help on SYNCSORT
Reply with quote

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...
Back to top
View user's profile Send private message
dick scherrer

Site Director


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

PostPosted: Wed Mar 19, 2008 8:11 pm    Post subject:
Reply with quote

Hello,

Is that tested?

Please post the output from the run.
Back to top
View user's profile Send private message
shrivatsa
Warnings : 1

Active User


Joined: 17 Mar 2006
Posts: 171
Location: Bangalore

PostPosted: Thu Mar 20, 2008 9:18 am    Post subject: Reply to: Need help on SYNCSORT
Reply with quote

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
Back to top
View user's profile Send private message
dick scherrer

Site Director


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

PostPosted: Thu Mar 20, 2008 7:13 pm    Post subject: Reply to: Need help on SYNCSORT
Reply with quote

Cool icon_cool.gif

Thanks,

d
Back to top
View user's profile Send private message
rajatbagga

Active User


Joined: 11 Mar 2007
Posts: 188
Location: india

PostPosted: Fri Mar 21, 2008 5:44 am    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
venuhunev

New User


Joined: 26 May 2007
Posts: 70
Location: chennai

PostPosted: Mon Mar 24, 2008 4:27 pm    Post subject:
Reply with quote

Thanks for your replies.
Your suggestions helped me icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SYNCSORT SEQNUM and FI fields. nartcr SYNCSORT 7 Sat Jan 21, 2017 4:02 am
No new posts Syncsort Help to group fields sudhakarraju SYNCSORT 6 Thu Dec 29, 2016 1:38 am
No new posts syncsort: copy lines after the keyword shreya19 SYNCSORT 7 Fri Dec 02, 2016 9:47 am
No new posts Syncsort - Hard coded Value in output PORYES SYNCSORT 1 Thu Nov 03, 2016 9:23 am
No new posts Syncsort - NULL in Integer field chec... nartcr SYNCSORT 4 Thu Oct 06, 2016 6:47 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us