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 UNLOAD instead of SELECT with predicates

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

New User


Joined: 18 Jul 2003
Posts: 39
Location: delhi

PostPosted: Thu Sep 24, 2009 2:02 pm    Post subject: Using UNLOAD instead of SELECT with predicates
Reply with quote

Hi,

Iam currently extracting records from TABLE1 into a flat file using the following SQL:

Code:
SELECT             ABC.* FROM TABLE1 ABC                     
                   WHERE ABC.COL1 NOT IN                     
                        (SELECT DEF.COL1 FROM TABLE2 DEF     
                   UNION                                     
                   SELECT  DEF.COL2 FROM TABLE2 DEF           
                   WHERE                                     
                         DEF.COL2 <> DEF.COL1);               


Here, ABC.COL1, DEF.COL1 and DEF.COL2 have the same data type and length.

Though I think the same is not possible using UNLOAD but still I was wondering if is there a way to implement the same using UNLOAD ? I tried to go thru the manuals but was unable to find a way out to achieve the same. Request you to please provide your inputs for the same.

Thanks in advance !
Back to top
View user's profile Send private message

Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Thu Sep 24, 2009 2:08 pm    Post subject:
Reply with quote

Hi Tarun,
What problem did you faced while unloading, which unload tool/utility are you using for this ?
Back to top
View user's profile Send private message
tarun_bhardwaj

New User


Joined: 18 Jul 2003
Posts: 39
Location: delhi

PostPosted: Thu Sep 24, 2009 2:10 pm    Post subject:
Reply with quote

I have not tried UNLOADing it since Iam not aware of the sytax for such a situation. Iam just trying to get an idea whether such a thing is possible using UNLOAD or not.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Sep 24, 2009 2:45 pm    Post subject:
Reply with quote

DSNTIAUL ? google some, this would be the tool to use.

Depending on the sizes of table1 table2 and available indexes following sql could be a lot faster. Not saying it will be so, but maybe worth a try.

Code:
SELECT             ABC.* FROM TABLE1 ABC                     
                   WHERE not exists (select 1 from table2 DEF where ABC.COL1 = DEF.COL1)
                     and not exists (select 1 from table2 def where ABC.COL1 = DEF.COL2 and  DEF.COL2 <> DEF.COL1);               
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Thu Sep 24, 2009 3:10 pm    Post subject:
Reply with quote

Hi GuyC,
Will this query able to do the same as removing the duplicate and as well as sorting the data?
or you are thinking one more step to remove the duplicate and sorting the input data.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Sep 24, 2009 3:21 pm    Post subject:
Reply with quote

both queries are functional the same :
only select rows from table 1 which (don't exist / are not in ) table2 with same COL1 or (if table2.col2 <> table2. col1 with col1 = col2 )

No sorting or removing of duplicates (in the result set) is done in any of the SQL.

any order your rows are returned are purely based on the chosen accespath and could randomly, suddenly change .
If you want a specific order , you'll have to add ORDER BY.

The query shouldn't have duplicates if table1 has a primary key.

if any sorting, duplicate removal has to done , maybe it is faster to do it in a sort step after DSNTIAUL. it depends on the specific case.
I would test both (if I knew enough about sort parms).
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Sep 24, 2009 3:53 pm    Post subject:
Reply with quote

now that i reread it :
the second criteria can be simplified
tab1.col1 can not be = tab2.col1 (first part )
or
tab1.col1 can not be = tab2.col2 (second part) the test on tab2.col1 <> tab2.col2 is redundant because these rows would get eliminated in the first part
(basic decision table simplification)

so the sql would be even easier :
Code:
SELECT             ABC.* FROM TABLE1 ABC                     
                   WHERE not exists (select 1 from table2 DEF where ABC.COL1 = DEF.COL1)
                     and not exists (select 1 from table2 def where ABC.COL1 = DEF.COL2)
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Thu Sep 24, 2009 3:57 pm    Post subject:
Reply with quote

I got it , Thanks icon_smile.gif
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Sep 24, 2009 4:27 pm    Post subject:
Reply with quote

Hello Tarun,

I'm not sure but through out your replies you've used unload in CAPS as UNLOAD. Is that some in house utility?

If DSNTIAUL is your choice, search on that in the Forum, there are enough working example aroud to get you started. Or google DSNTIAUL, as GuyC has said.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Sep 24, 2009 4:28 pm    Post subject:
Reply with quote

I just got it: DSNTIAUL example program (with example).
Back to top
View user's profile Send private message
tarun_bhardwaj

New User


Joined: 18 Jul 2003
Posts: 39
Location: delhi

PostPosted: Thu Sep 24, 2009 5:30 pm    Post subject:
Reply with quote

Hi All,

Thanks for your replies. Actually this is not what Iam looking for. I should have been more precise in drafting my question. Apologies for that !

Background:
My intention is to unload a partitioned tablespace so that each of the unloaded flat files can be processed in parallel streams. The SQL that I had specified earlier takes a lot of time so we decided to unload the rows into a flat file based on the partitions of that tablespace which satisfy the conditions mentioned in my SQL.

In the Utility Reference Guide, I flipped thru the notes on UNLOAD utility. The 'UNLOAD TABLESPACE' has a "WHEN" instead of a "WHERE" clause and we can have the predicates to compare the column with a constant but I could not come across an example where in the predicates we could compare a column with another column of a different table.

So just to summarize, I want to unload different partitions of a tablespace based on the criteria mentioned in my SQL.

Thanks in advance and sprry for the confusion !
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Sep 24, 2009 6:05 pm    Post subject:
Reply with quote

AFAIK you are right : you can not use complex criteria.

If you have (or create) a partitioned result table you might want to try crossloading :

Code:
EXEC SQL
  DECLARE C1 CURSOR FOR SELECT * FROM table1
  WHERE EMPNO <= '099999' and not exists ....
ENDEXEC
EXEC SQL
  DECLARE C2 CURSOR FOR SELECT * FROM table1
   WHERE EMPNO > '099999' AND EMPNO <= '199999' and not exists ....
ENDEXEC
EXEC SQL
  DECLARE C3 CURSOR FOR SELECT * FROM table1
  WHERE EMPNO > '199999' AND EMPNO <= '299999' and not exists ....
ENDEXEC
EXEC SQL
  DECLARE C4 CURSOR FOR SELECT * FROM Table1  WHERE EMPNO > '299999' AND EMPNO <= '999999' and not exists ....
ENDEXEC
LOAD DATA
  INTO TABLE table2 PART 1 REPLACE INCURSOR(C1)
  INTO TABLE table2 PART 2 REPLACE INCURSOR(C2)
  INTO TABLE table2 PART 3 REPLACE INCURSOR(C3)
  INTO TABLE table2 PART 4 REPLACE INCURSOR(C4)


I think you can have complex where clauses for this.
But the performance of the query will probably still be bad.
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 Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Best IMS DB Unload Utility jjabez10 IMS DB/DC 12 Thu Sep 22, 2016 11:15 pm
No new posts DB2 Unload format options Susanta DB2 2 Fri Aug 12, 2016 5:42 pm
No new posts DXT unload utility - DVRE0000 mistah kurtz DB2 1 Thu Jul 07, 2016 4:56 pm
No new posts Select first 2 group data vice_versa DFSORT/ICETOOL 10 Mon Jun 13, 2016 2:21 pm


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