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
 

 

Sample code: UNLOAD TABLE SPACE with INNER JOIN

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
dgokulakrishnan

New User


Joined: 01 Jun 2011
Posts: 5
Location: Chennai

PostPosted: Fri Nov 04, 2011 11:10 am    Post subject: Sample code: UNLOAD TABLE SPACE with INNER JOIN
Reply with quote

Dear Chaps,


I would like to Unload data from two different DB2 tables by using the UNLOAD TABLESPACE Utiltiy. Is there any way to JOIN two tables using the UNLOAD Utility?.

Tables are resides in two different Table Space.
Back to top
View user's profile Send private message

gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Nov 04, 2011 11:26 am    Post subject:
Reply with quote

You can unload the data in 2 different files... then use sort to join them...
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Nov 04, 2011 12:57 pm    Post subject:
Reply with quote

Hi Gokula,

In my current shop, they do this extensively using BMC Unload. Just check what is available at your place.

Thanks,
Sushanth
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1534
Location: Andromeda Galaxy

PostPosted: Fri Nov 04, 2011 1:21 pm    Post subject:
Reply with quote

Is it something that you wanted to do it only with UNLOAD utility??

I would suggest make use of DSNTIAUL rather UNLOAD utility because if your job fails inbetween while using UNLOAD then your table might lock the table which might be a problem
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Nov 04, 2011 1:45 pm    Post subject:
Reply with quote

sushanth bobby wrote:
Hi Gokula,

In my current shop, they do this extensively using BMC Unload. Just check what is available at your place.

Thanks,
Sushanth


Hi Sushanth,

Even we have BMC installed in our shop...

How do we join two tables in different tablespaces in BMC? What is the option in BMC?
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Nov 04, 2011 2:47 pm    Post subject:
Reply with quote

Hi Gylbharat,

Below is the skeleton, try it out.
Code:
UNLOAD DIRECT NO   
SELECT columns1,columns2
FROM TABLE1, TABLE2
WHERE Table1.z = Table2.z
WITH UR;

DIRECT NO must be specified, which in the background uses DB2 Dynamic SQL to process the SELECT statement.

Thanks,
Sushanth
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Nov 04, 2011 2:59 pm    Post subject:
Reply with quote

Ok... Thanks sushanth...

Can you also give me a sample JCL?
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Nov 04, 2011 3:12 pm    Post subject:
Reply with quote

Gylbharat,

You shop has it means, you should be having the JCL, anyhow here is the sample
Code:
//UNLOAD01 EXEC PGM=ADUUMAIN,REGION=0M,
// PARM=(DEDR,'ADUXM01','NEW ',,'MSGLEVEL(1)')
//*********************************************************************
//         INCLUDE MEMBER=&BMCLIBR
//UTPRINT  DD SYSOUT=* 
//SYSPRINT DD SYSOUT=* 
//SYSOUT   DD SYSOUT=* 
//SYSUDUMP DD SYSOUT=C 
//SYSCNTL  DD DUMMY
//SYSREC01 DD DSN=ADU.EXAMPLE1.SYSREC01,
// DISP=(,CATLG),SPACE=(TRK,(1,1)),UNIT=SYSDA
//SYSIN DD DD DISP=SHR,DSN=HLQ.TEST.JCL(BMCUL)


Thanks,
Sushanth
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Nov 04, 2011 3:41 pm    Post subject:
Reply with quote

Thanks Sushanth....
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 -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Data replication from multiple Db2 ta... kishpra DB2 1 Mon Mar 27, 2017 9:58 pm
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 Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts IMS region inactive - User abend code... gthmrj IMS DB/DC 5 Tue Mar 07, 2017 12:29 pm
No new posts CA Workload Automation event code help vinu78 CA Products 10 Wed Mar 01, 2017 10:05 am


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