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
 

 

Delete statement using TS scan even after defining index

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

New User


Joined: 10 Aug 2006
Posts: 5

PostPosted: Tue Dec 19, 2006 1:02 pm    Post subject: Delete statement using TS scan even after defining index
Reply with quote

Hi all,

I have a delete statement in one of my programs,
DELETE * from table 1
where user = :host variable

I have defined an index on the field "user". But when the program runs, it uses a tablespace scan and as a result it is taking a lot of time.

The explain report also says that this query is using table space scan. I am not able to identify the issue.

Please help.

Thanks
Rakesh
Back to top
View user's profile Send private message

MFRASHEED

Active User


Joined: 14 Jun 2005
Posts: 186
Location: USA

PostPosted: Tue Dec 19, 2006 10:18 pm    Post subject: Re: Delete statement using TS scan even after defining index
Reply with quote

How many rows qualify for this delete? if large number of rows qualify then that explains.
Back to top
View user's profile Send private message
r_rakesh_r

New User


Joined: 10 Aug 2006
Posts: 5

PostPosted: Wed Dec 20, 2006 1:15 pm    Post subject:
Reply with quote

Even if the number of records qualifying is Zero, it is taking a lot of time.
Back to top
View user's profile Send private message
ravi17s
Warnings : 1

New User


Joined: 15 Aug 2003
Posts: 57

PostPosted: Wed Dec 20, 2006 6:24 pm    Post subject: Re: Delete statement using TS scan even after defining index
Reply with quote

When was the Last Runstats done on these Table Space.

If the statistics are not upto date,it affects the Access Path


Thx,
Ravi
Back to top
View user's profile Send private message
r_rakesh_r

New User


Joined: 10 Aug 2006
Posts: 5

PostPosted: Thu Dec 21, 2006 4:48 am    Post subject: Re: Delete statement using TS scan even after defining index
Reply with quote

I have run REORG, RUNSTATS, REBIND.

RUNSTATS say that the query is using Index but still using Table space scan.
Back to top
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Thu Dec 21, 2006 5:28 am    Post subject: Re: Delete statement using TS scan even after defining index
Reply with quote

Hello r_rakesh_r,

We experienced a problem, somewhat similar to what you are describing, where the Query should be using an Index Scan but was using in fact a tablespace scan. After some time pulling our hair we turned it over to the DBAs and some Brilliant DBA has us put in ?WHERE KEY = KEY?. ??????? It worked. Please don?t ask me why??

Try

Code:

DELETE * from table 1
WHERE USER = USER
and user = :host variable 


Dave
Back to top
View user's profile Send private message
kvivek

New User


Joined: 09 May 2005
Posts: 51
Location: Singapore

PostPosted: Thu Dec 21, 2006 11:18 am    Post subject:
Reply with quote

Quote:
Even if the number of records qualifying is Zero, it is taking a lot of time.


We had the similar issue.

1) Index field in the where class is spaces across the entire table (Like user field in your query)
2) There are no matching records for the query.

For the above reasons each query went into the entire table scan even though index has been defined in the particular field.

Once the index field is populated with valid values in the table and after REORG, RUNSTATS, REBIND index has been picked.

Regards,
Vivek
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 Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts JCL to delete component in a package sundaram.naveen Compuware & Other Tools 14 Tue Nov 29, 2016 6:21 pm
No new posts -913/-911 Deadlock during UPDATE stat... NoSleep319 DB2 5 Fri Nov 18, 2016 12:37 am
No new posts How to delete second instance from Fl... Gunapala CN DFSORT/ICETOOL 6 Tue Oct 18, 2016 11:42 pm
No new posts COBOL DB2 - CALL statement - high CPU... TS70363 DB2 15 Sun Sep 11, 2016 6:07 am


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