IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Multiple files in JOIN
IBM Mainframe Forums -> DFSORT/ICETOOL
Post new topic   Reply to topic Quick References
View previous topic :: View next topic  
Author Message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
PostPosted: Fri Jul 27, 2012 4:18 pm
 Reply with quote
Hello Team,

I have to convert beloq sql query to corresponding sort so is it possible to do it?

Code:
SELECT B.name                       
          , A.userid         
          , A.number             
  FROM name1 A                   
     , userid1    B                   
     , number1 C                   
 WHERE                                     
           A.uu <> 0             
   AND A.char           = B.char       
   AND A.number       = B.number
   AND A.aset       = C.aset 

Below are the unload files and corresponding offsets,

1)name1
userid=1:10
number=11:10
uu=31:5
char=40:5
aset=55:8

2)userid1
char=10:5
number=22:10
Name=30:10

3)number1

aset=11:8
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Fri Jul 27, 2012 5:08 pm
 Reply with quote
OK, we don't particularly care about the SQL.

Define your requirement, fully, in plain language, and show sample data which covers the possible combinations you want. LRECL and RECFM of all files.
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
PostPosted: Fri Jul 27, 2012 5:22 pm
 Reply with quote
Join 2 files at a time... then join the result with the 3rd file.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
PostPosted: Fri Jul 27, 2012 7:14 pm
 Reply with quote
Hello,

How are you going to get the data out of the table so the sort can access it?

If there are millions and millions of rows in the tables, this does not sound like an acceptable approach . . .
Back to top
View user's profile Send private message
knickraj
Warnings : 1
New User


Joined: 11 Jun 2007
Posts: 50
Location: Euro
PostPosted: Fri Jul 27, 2012 10:26 pm
 Reply with quote
Asumming you unload the table to 3 datasets A,B,C
can be done in 2 steps using joinkeys
============================

1 step
======
join file A and C on asset

Code:
A.aset       = C.aset

use include/Omit cond for file A to eliminate in the joinkey subtask.
Code:
A.uu <> 0

Get all the fields of file A and use in step 2

2 step
======
join file A(from step1) and B on the required keys
Code:
    A.char  = B.char  AND       
    A.number  = B.number

and using reformat fields get the data which you want.
Back to top
View user's profile Send private message
Bill Woodger

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
PostPosted: Fri Jul 27, 2012 10:29 pm
 Reply with quote
knickraj,

What do you mean by "eliminate the joinkey subtask"? It'd be a cool trick if you can do it, but I suspect you mean something else, so, what?

EDIT:

Sorry knickraj, didn't notice the crucial word "in".

Thanks dbz.
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Fri Jul 27, 2012 11:47 pm
 Reply with quote
Rohit Umarjikar,

Your USERID file have overlapping fields. If the number field starts at pos 22 for a length of 10 bytes you cannot have the Name field start at position 30. I assumed that the name field in userid file starts at pos 35.

Use the following DFSORT JCL

Code:

//STEP0100 EXEC PGM=SORT                                   
//SYSOUT   DD SYSOUT=*                                     
//INA      DD DSN=Your NAME1 fileA,DISP=SHR
//INB      DD DSN=Your NUMBER1 fileC,DISP=SHR
//SORTOUT  DD DSN=&&TEMP,DISP=(,PASS),SPACE=(CYL,(X,Y),RLSE)
//SYSIN    DD *                                             
  OPTION COPY                                               
  JOINKEYS F1=INA,FIELDS=(55,8,A)                           
  JOINKEYS F2=INB,FIELDS=(11,8,A)                           
  REFORMAT FIELDS=(F1:1,62)                                 
//*                                                         
//JNF1CNTL DD *                                             
  OMIT COND=(31,5,ZD,EQ,0)                                 
//*                                                         
//STEP0200 EXEC PGM=SORT                                   
//SYSOUT   DD SYSOUT=*                                     
//INA      DD DSN=&&TEMP,DISP=SHR                           
//INB      DD DSN=Your USERID1 fileB,DISP=SHR
//SORTOUT  DD SYSOUT=*                                     
//SYSIN    DD *                                             
  OPTION COPY                                               
  JOINKEYS F1=INA,FIELDS=(40,5,A,11,10,A)                   
  JOINKEYS F2=INB,FIELDS=(10,5,A,22,10,A)                   
  REFORMAT FIELDS=(F2:35,10,F1:1,20)                       
//*
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
PostPosted: Mon Jul 30, 2012 4:21 pm
 Reply with quote
@Dick:
Your are correct, but I do have the unload files for these tables and the reason I am trying it to convert the SQLs to SORT is beacuse of more CPU time is being taken by these SQLs in batch.

@Skolusu:
Thanks for your support as I always get, I will try this out.

@Rest All:
Thanks a lot for your views.
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
PostPosted: Mon Jul 30, 2012 9:31 pm
 Reply with quote
@Skolusu,

Code:
SELECT Count (Distinct B.name )                       
          , Count (Distinct A.userid)         
          , Count (Distinct A.number)             
  FROM name1 A                   
     , userid1    B                   
     , number1 C                   
 WHERE                                     
           A.uu <> 0             
   AND A.char           = B.char       
   AND A.number       = B.number
   AND A.aset       = C.aset 


This is the query and the one u gave up is working fine can you suggest me to modify the SORT?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
PostPosted: Mon Jul 30, 2012 9:33 pm
 Reply with quote
Hello,

Skolusu already gave you a sort solution. What else is needed?
Back to top
View user's profile Send private message
Skolusu

Senior Member


Joined: 07 Dec 2007
Posts: 2205
Location: San Jose
PostPosted: Mon Jul 30, 2012 10:09 pm
 Reply with quote
Rohit Umarjikar wrote:
@Skolusu,

This is the query and the one u gave up is working fine can you suggest me to modify the SORT?


Rohit Umarjikar,


Is there an end to your queries ? icon_sad.gif I provided the frame work for the matching , now YOU can/have to expand it to get to the counts. Just because I tend to provide complete solutions , doesn't mean that you can get your work done by me. Look up the reporting features TRAILER , SECTIONS and COUNT keywords.

If you're not familiar with DFSORT and DFSORT's ICETOOL, I'd suggest reading through "z/OS DFSORT: Getting Started". It's an excellent tutorial, with lots of examples, that will show you how to use DFSORT, DFSORT's ICETOOL and DFSORT Symbols. You can access it online, along with all of the other DFSORT books, from:

www.ibm.com/support/docview.wss?rs=114&uid=isg3T7000080
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
PostPosted: Tue Jul 31, 2012 2:03 pm
 Reply with quote
Thanks Skolusu...I will go thru them.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DFSORT/ICETOOL

 

Similar Topics
Topic Forum Replies
No new posts Compare 2 files and retrive records f... DFSORT/ICETOOL 3
No new posts Compare 2 files(F1 & F2) and writ... JCL & VSAM 8
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts Write line by line from two files DFSORT/ICETOOL 7
No new posts Compare only first records of the fil... SYNCSORT 7
Search our Forums:

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