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
 

 

Hookup group of records in multiple places in input dataset

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Thu Feb 18, 2016 11:49 pm    Post subject: Hookup group of records in multiple places in input dataset
Reply with quote

Hi All,

I have input dataset with SQL query as below. LRECL=80, RECFM=FB

Code:
 

SELECT * FROM A WHERE A1 IN (
)

UNION

SELECT * FROM B WHERE B1 IN (
)

UNION

SELECT * FROM C WHERE C1 IN(
)

WITH UR;

 



I have second dataset that contains list of keys that need to be hooked in each IN clause as below. LRECL=80,RECFM=FB

Code:


 '1','2','3','4','5'
,'12','16','17',18'
,'19','20'
 


This group of records should go into every IN clause of the above query.

Can it be possible to do using SORT?

I am planning to do as below.

1.count the IN clauses in the input query. The number of IN clauses can vary, they are not 3 always as shown above.
2.Repeat the group of records in second dataset based on the count from step 1. Have each group of records tagged with a number and alternate the number for every next group, such as 2,4,6,8 etc...
3.For the query put a tag like <A> from start of the query and the next will be after the IN clause. Essentially group the records will have the same sequence number before IN and after IN clause and alternate way as in 1,3,5,7 etc...
4.Now, copy the query and the repeated group of records and sort by the number attached. This should rearrange the records and prepare as below.

Code:


SELECT * FROM A WHERE A1 IN (
 '1','2','3','4','5'
,'12','16','17',18'
,'19','20'
)

UNION

SELECT * FROM B WHERE B1 IN (
 '1','2','3','4','5'
,'12','16','17',18'
,'19','20'
)

UNION

SELECT * FROM C WHERE C1 IN(
 '1','2','3','4','5'
,'12','16','17',18'
,'19','20'
)

WITH UR;

 



Please suggest me if there is any better way to do this. Thank you very much for the help.

Regards
Back to top
View user's profile Send private message

kranthikumarb

Active User


Joined: 02 Jan 2009
Posts: 114
Location: Hyderabad

PostPosted: Fri Feb 19, 2016 12:12 pm    Post subject:
Reply with quote

Does the number of queries change every time or is it constant? Do you always have 3 or n number of queries in your dataset?
Back to top
View user's profile Send private message
kranthikumarb

Active User


Joined: 02 Jan 2009
Posts: 114
Location: Hyderabad

PostPosted: Fri Feb 19, 2016 1:22 pm    Post subject:
Reply with quote

Assuming your table/select statements count doesn't change, here is some idea.

File1 has only the below
Code:

SELECT * FROM A WHERE A1 IN (                                     
)                                                                 
UNION


File2 has the below

Code:

 '1','2','3','4','5'                                             
,'12','16','17','18'                                             
,'19','20'


Code:

//SUMKK    EXEC PGM=ICETOOL                                       
//SEND     DD   DSN=&&SEND,UNIT=SYSDA,SPACE=(CYL,(250,100)),     
//             DISP=(,PASS)                                       
//CATCH    DD   DSN=*.SEND,DISP=(OLD,PASS),VOL=REF=*.SEND         
//TOOLMSG  DD   SYSOUT=*                                         
//DFSMSG   DD   SYSOUT=*                                         
//SORTIN   DD DSN=file1                                                   
                 DD DSN=file2
//TOOLIN   DD   *                                                 
  COPY FROM(SORTIN) TO(SEND) USING(CTL1)                         
  COPY   FROM(SEND) TO(SORTOUT) USING(CTL2)                       
//*                                                               
//CTL1CNTL DD   *                                                 
  SORT FIELDS=COPY                                               
  INREC IFTHEN=(WHEN=(1,1,CH,EQ,C'S'),OVERLAY=(53:C'1')),           
        IFTHEN=(WHEN=(1,1,CH,EQ,C' '),OVERLAY=(53:C'2')),           
        IFTHEN=(WHEN=(1,1,CH,EQ,C','),OVERLAY=(53:C'3')),           
        IFTHEN=(WHEN=(1,1,CH,EQ,C')'),OVERLAY=(53:C'8')),           
        IFTHEN=(WHEN=(1,1,CH,EQ,C'U'),OVERLAY=(53:C'9'))           
  OUTFIL REPEAT=3,OVERLAY=(50:SEQNUM,3,ZD,RESTART=(1,10))           
/*                                                                 
//CTL2CNTL DD   *                                                   
  SORT FIELDS=(50,4,CH,A)                                           
  OUTREC IFTHEN=(WHEN=(50,3,CH,EQ,C'002',AND,1,1,CH,EQ,C'S'),       
                    OVERLAY=(15:C'B',23:C'B')),                     
         IFTHEN=(WHEN=(50,3,CH,EQ,C'003',AND,1,1,CH,EQ,C'S'),       
                    OVERLAY=(15:C'C',23:C'C'))                     
  OUTFIL FNAMES=SORTOUT,OMIT=(1,1,CH,EQ,C'U',AND,50,3,CH,EQ,C'003'),
    BUILD=(1,49),TRAILER1=('WITH UR;')                             
//SORTOUT  DD   SYSOUT=*                                           



This may not be the best. But see if it helps you in any way.
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Fri Feb 19, 2016 9:35 pm    Post subject:
Reply with quote

Hi Kranthi,

Thank you for your post.

The number of queries are variable and the IN clauses within each query are variable. Means the example query I posted has 3 IN clauses. In the next query I might have only one IN clause. so, I will need to add the records only once.

Regards
Amar
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 -> DFSORT/ICETOOL All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Performing arithmetic on input field zh_lad DFSORT/ICETOOL 24 Tue Dec 06, 2016 8:04 pm
No new posts High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts FTP - JCL failed while passing FTP co... Suneetha1612 JCL & VSAM 12 Wed Nov 16, 2016 7:33 pm


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