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
 

 

Force tablespace scan on larger table

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

Active User


Joined: 29 Oct 2010
Posts: 187
Location: Toronto, ON, Canada

PostPosted: Tue Dec 21, 2010 3:10 am    Post subject: Force tablespace scan on larger table
Reply with quote

I have created an sql to be run once a day for a report. It will extract data from 5 tables using an inner join. Is there a way I can tell db2 which table to do a scan on?

Table A has 33M rows
Table B has 10M rows

Tables are joined using a single column. An index exists on both tables for the column.

The sql has extra predicates for each table so that it can't use index only. It will have to read a table page to satisfy it. Once the predicate for table A is satisfied the number of rows selected will be about 50K. This will mean a small subset of rows are needed from table B.

Table A is a logical child of table B. Therefore every row on A has a match on B.

An explain of my sql shows that db2 will do a tablespace scan on B and then join the results to table A. The predicate is coded in such a way that once this is done it will then end up selecting every row on table A anyway. Thus it will end up reading every page of both tables.

If I can tell db2 to scan table A it can then use the index to read only the matching rows on table B.

I looked in the sql reference guide but I couldn't see any syntax for this. Any suggestions? I can't add an index as this would impact the performance of the online programs.
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Dec 22, 2010 1:24 pm    Post subject:
Reply with quote

put ( or 0=1) on the extra criteria of table B
Verify :
- there is an index on B for the join key (which probably is there, because B is child of A).
- as many as possible fields of the index are specified in the join condition
-Statistics have run on both tables
Back to top
View user's profile Send private message
PeterHolland

Global Moderator


Joined: 27 Oct 2009
Posts: 2435
Location: Netherlands, Amstelveen

PostPosted: Wed Dec 22, 2010 2:51 pm    Post subject:
Reply with quote

http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.perf/db2z_db2simplifiesjoins.htm
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 Loading data to table gives wrong for... Raghu navaikulam DB2 19 Thu Jul 13, 2017 2:11 pm
No new posts unload data from table with lob columns farhad_evan DB2 1 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm


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