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
 

 

Multiple files in JOIN

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DFSORT/ICETOOL
View previous topic :: :: View next topic  
Author Message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Fri Jul 27, 2012 4:18 pm    Post subject: Multiple files in JOIN
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Fri Jul 27, 2012 5:08 pm    Post subject: Reply to: Multiple files in JOIN
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    Post subject:
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

Site Director


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

PostPosted: Fri Jul 27, 2012 7:14 pm    Post subject:
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    Post subject:
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

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7223

PostPosted: Fri Jul 27, 2012 10:29 pm    Post subject: Reply to: Multiple files in JOIN
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    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Mon Jul 30, 2012 4:21 pm    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Mon Jul 30, 2012 9:31 pm    Post subject:
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

Site Director


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

PostPosted: Mon Jul 30, 2012 9:33 pm    Post subject:
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    Post subject:
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:

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

Senior Member


Joined: 21 Sep 2010
Posts: 1609
Location: NY,USA

PostPosted: Tue Jul 31, 2012 2:03 pm    Post subject:
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    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 High CPU consumption Job using IAM fi... aswinir JCL & VSAM 8 Thu Dec 01, 2016 8:28 pm
No new posts Match or compare two files in VB Format anatol DFSORT/ICETOOL 14 Thu Nov 03, 2016 7:41 pm
No new posts How can we have the varying lenth of ... Gunapala CN DFSORT/ICETOOL 6 Fri Oct 14, 2016 7:31 pm
This topic is locked: you cannot edit posts or make replies. How to use 2 input files in control c... Gunapala CN DFSORT/ICETOOL 23 Thu Oct 13, 2016 3:42 pm
No new posts Adding records from two files into on... shiitiizz SYNCSORT 4 Mon Sep 19, 2016 8:41 pm


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