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
 

 

Using subselect in BMCLOAD

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

New User


Joined: 08 May 2006
Posts: 16

PostPosted: Mon Nov 22, 2010 2:16 pm    Post subject: Using subselect in BMCLOAD
Reply with quote

I have to build a load card that tries to load the data from FILE1 to TABLE1, but it has to lookup another TABLE2 as a subselect. I found that a WHEN clause can be used to selectively load data into the TABLE1

LOAD DATA INDDN SYSREC RESUME YES LOG NO COPY YES
EBCDIC CCSID(285,65534,65534)
IFDISCARDS 2
FORMAT CSV TERMINATEDBY '|'
INTO TABLE XYZ.TABLE1
WHEN (1:1) NOT = ‘A’
(
COL_NO1 POSITION(*)
,COL_NO2 POSITION(*)
)

Is there a way to plug a subselect into this WHEN clause?

like say WHEN EXISTS (SELECT 1 FROM XYZ.TABLE2 WHERE COL_NO3 = COL_2).... (I tried it, but it failed)

Just wanted to know whether there is any option to do that in BMCLOAD...

thanks v much
Back to top
View user's profile Send private message

expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8593
Location: Back in jolly old England

PostPosted: Mon Nov 22, 2010 3:49 pm    Post subject:
Reply with quote

And exactly what has this topic have to do with DFSORT / ICETOOL.
Hardly showing any initiative is it when you can't even post in the correct forum.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Nov 23, 2010 10:02 am    Post subject:
Reply with quote

aravindunlimited,

I think you misunderstood the 'WHEN' clause used in the LOAD statement. Its basically used for filtering the input dataset when loading. And i am not sure for how it works in BMC.

Can i know why are you checking in TABLE2, does TABLE1 and TABLE2 are referentially related. If so, just load the TABLE1 and it will go into check-pending and move all the orphan rows to the exception tables using CHECK DATA utiltiy.

To get really good reply's, you got to tell the why's.

Sushanth
Back to top
View user's profile Send private message
aravindunlimited

New User


Joined: 08 May 2006
Posts: 16

PostPosted: Tue Nov 30, 2010 9:00 am    Post subject:
Reply with quote

Sorry for posting this in the wrong forum, I guess once posted I cannot change the forum. Will make sure this is taken care of.

My requirement was to load a set of data from a file into TABLE1 where that record should be present in another table TABLE2 as well (But there is no referential integrity), this process has to be automated.

I was little over ambitious, and wanted to tryout such a thing directly in BMCLOAD had there been a facility for this. As of now I have simplified the design and am doing the 'election' process ahead of load.

Thanks for all the replies
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 difference between subselect and nest... udaya_1986 DB2 1 Sun May 11, 2014 10:23 am
No new posts Subselect within a Left Outer Join cvishu DB2 12 Mon May 16, 2011 5:49 pm
No new posts DB2 Unload utility - BMCLOAD amitava DB2 2 Thu Jul 13, 2006 11:23 am


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