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
 

 

Syncsort/ICETOOL query

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

New User


Joined: 15 Mar 2007
Posts: 8
Location: delhi

PostPosted: Thu Apr 15, 2010 10:36 pm    Post subject: SYNC TOOL Query
Reply with quote

Hi,

I have a query which i need to solve throgh ICE TOOL.

I have three flat files which has one field in common. But that field is repeated again in the file 1 & File2 but unique in file 3. In the below file 1 has 6 records but first 10 byte of the records are repeated 3 times. In the second file same first 10 byte is repeated 2 times and in the third file it is unique. So in the output file i will be having 12 records as one record in the input file 1 is repeated 2 times in the file2 so it will be written twice.
I want to solve this query through sort or ICETOOL. Please check the below example and provide me the JCL for this.

File1 - Length 30 byte
Code:
JET0000001COM   HIMANSHU KAUSH
JET0000001FAR   HIMANSHU KAUSH
JET0000001PREPR HIMANSHU KAUSH
JET0000002COM   HIMANSHU KAUSH
JET0000002FAR   HIMANSHU KAUSH
JET0000002PREPR HIMANSHU KAUSH


File 2 -Length 20 Byte
Code:
JET0000001NABCDE/XYZ
JET0000001YABCDE/XYZ
JET0000002NABCDE/XYZ
JET0000002YABCDE/XYZ
JET0000003NABCDE/XYZ
JET0000003YABCDE/XYZ


FILE 3 - LENGTH 15 BYTE
Code:
JET0000001YESNO
JET0000002YESNO
JET0000003YESNO


I Want my output files like -

Output file - LENGTH 31 BYTE

Code:
COM   JET0000001NABCDE/XYZYESNO
COM   JET0000001YABCDE/XYZYESNO
FAR   JET0000001NABCDE/XYZYESNO
FAR   JET0000001YABCDE/XYZYESNO
PREPR JET0000001NABCDE/XYZYESNO
PREPR JET0000001YABCDE/XYZYESNO
COM   JET0000002NABCDE/XYZYESNO
COM   JET0000002YABCDE/XYZYESNO
FAR   JET0000002NABCDE/XYZYESNO
FAR   JET0000002YABCDE/XYZYESNO
PREPR JET0000002NABCDE/XYZYESNO
PREPR JET0000002YABCDE/XYZYESNO
Back to top
View user's profile Send private message

Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Thu Apr 15, 2010 11:23 pm    Post subject:
Reply with quote

Here's a DFSORT/ICETOOL job that will do what you asked for. You'll need the DFSORT Nov, 2009 PTF to use DFSORT's JOINKEYS function.
For more information on this PTF, see:

http://www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000174

Code:

//S1    EXEC  PGM=ICETOOL
//TOOLMSG DD SYSOUT=*
//DFSMSG  DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//IN1 DD DSN=...  input file1 (FB/30)
//IN2 DD DSN=...  input file2 (FB/20)
//IN3 DD DSN=...  input file3 (FB/10)
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)
//OUT DD DSN=...  output file (FB/31)
//TOOLIN DD *
COPY JKFROM TO(T1) USING(CTL1)
COPY JKFROM TO(OUT) USING(CTL2)
/*
//CTL1CNTL DD *
  JOINKEYS F1=IN1,FIELDS=(1,10,A),SORTED,TASKID=AA
  JOINKEYS F2=IN2,FIELDS=(1,10,A),SORTED,TASKID=AA
  REFORMAT FIELDS=(F1:11,6,1,10,F2:11,10)
/*
//CTL2CNTL DD *
  JOINKEYS F1=T1,FIELDS=(7,10,A),SORTED,TASKID=BB
  JOINKEYS F2=IN3,FIELDS=(1,10,A),SORTED,TASKID=BB
  REFORMAT FIELDS=(F1:1,26,F2:11,5)
/*


If you don't have the Nov, 2009 PTF, ask your System Programmer to install it (it's free).
Back to top
View user's profile Send private message
Himanshu-kaushik

New User


Joined: 15 Mar 2007
Posts: 8
Location: delhi

PostPosted: Fri Apr 16, 2010 11:02 am    Post subject:
Reply with quote

Hi Frank,

After submitting the JCL I am getting MAXCC 12.
Please check my JCL.

Code:
//TSO51AOA JOB (IT-00,'H12/1386'),'DB2 SQL',                 
//          CLASS=3,MSGCLASS=O,                             
//         NOTIFY=&SYSUID                                   
//*                                                         
//S1    EXEC  PGM=ICETOOL                                   
//TOOLMSG DD SYSOUT=*                                       
//DFSMSG  DD SYSOUT=*                                       
//SYSUDUMP DD SYSOUT=*                                       
//IN1 DD DSN=TLIFE20.TSO51AO.ZETS.SCHEDULE.TEST,DISP=SHR     
//IN2 DD DSN=TLIFE20.TSO51AO.ZETS.MAPPER.TEST,DISP=SHR       
//IN3 DD DSN=TLIFE20.TSO51AO.ZETS.TTRPT.TEST,DISP=SHR       
//T1 DD DSN=&&T1,UNIT=SYSDA,SPACE=(CYL,(5,5)),DISP=(,PASS)   
//OUT DD DSN=TLIFE20.TSO51AO.ZETS.OUTPUT.TEST,DISP=OLD       
//TOOLIN DD *                                               
COPY JKFROM TO(T1) USING(CTL1)                               
COPY JKFROM TO(OUT) USING(CTL2)                             
/*                                                           
//CTL1CNTL DD *                                             
  JOINKEYS F1=IN1,FIELDS=(1,10,A),SORTED,TASKID=AA           
  JOINKEYS F2=IN2,FIELDS=(1,10,A),SORTED,TASKID=AA     
  REFORMAT FIELDS=(F1:11,6,1,10,F2:11,10)               
/*                                                     



I am getting below error.

Code:
SYT000I  SYNCTOOL RELEASE 1.6.2 - COPYRIGHT 2007  SYNCSORT INC.                 
SYT001I  INITIAL PROCESSING MODE IS "STOP"                                     
SYT002I  "TOOLIN" INTERFACE BEING USED                                         
                                                                               
         COPY JKFROM TO(T1) USING(CTL1)                                         
SYT050E  INVALID OPERAND ON "COPY" STATEMENT                                   
SYT030I  OPERATION COMPLETED WITH RETURN CODE 12                               
                                                                               
SYT015I  PROCESSING MODE CHANGED FROM "STOP" TO "SCAN" DUE TO OPERATION FAILURE
                                                                               
         COPY JKFROM TO(OUT) USING(CTL2)                                       
SYT050E  INVALID OPERAND ON "COPY" STATEMENT                                   
                                                                               
SYT004I  SYNCTOOL PROCESSING COMPLETED WITH RETURN CODE 12


Please let me know how to solve this problem.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10276
Location: italy

PostPosted: Fri Apr 16, 2010 3:13 pm    Post subject: Reply to: Syncsort/ICETOOL query
Reply with quote

Quote:
SYT050E INVALID OPERAND ON "COPY" STATEMENT


... what does the manual say about the message ?
... what are the operands allowed on a copy statement ?

meditate on those questions, and You will be be able to solve the issue Yourself!
Back to top
View user's profile Send private message
Himanshu-kaushik

New User


Joined: 15 Mar 2007
Posts: 8
Location: delhi

PostPosted: Fri Apr 16, 2010 3:16 pm    Post subject:
Reply with quote

I don't have manual.

Nobody at my place used this type of condition before so nobody knows here. That is why i need help.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10276
Location: italy

PostPosted: Fri Apr 16, 2010 4:14 pm    Post subject: Reply to: Syncsort/ICETOOL query
Reply with quote

Quote:
don't have manual.


a forum is not a tool meant to fix poor organization issues icon_cool.gif

in many topics where the TS complained about the lack of syncsort manuals
Alissa Margulis was available to take actions in order to provide them!
search the forums and You will find the Alissa' s contact email address
like for example in ...
http://ibmmainframes.com/viewtopic.php?t=41086&highlight=alissa+manuals
Back to top
View user's profile Send private message
Frank Yaeger

DFSORT Moderator


Joined: 15 Feb 2005
Posts: 7130
Location: San Jose, CA

PostPosted: Fri Apr 16, 2010 10:35 pm    Post subject:
Reply with quote

Himanshu,

The job I showed works fine with DFSORT which supports JKFROM.

However, the SYT messages indicate you're using Syncsort, not DFSORT.
Syncsort does not support JKFROM.
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 Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts Using ICETOOL, how we can seperate th... bshkris SYNCSORT 5 Tue May 09, 2017 8:33 pm
No new posts Group elements using ICETOOL Learncoholic DFSORT/ICETOOL 2 Fri Apr 14, 2017 11:01 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm


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