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
 

 

Access Path Anomaly.

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

New User


Joined: 16 Sep 2009
Posts: 15
Location: Hyd,Ind

PostPosted: Wed Jun 22, 2011 4:04 pm    Post subject: Access Path Anomaly.
Reply with quote

Hi,

Am trying to execute a dynamic query, in development the query is getting timed out while in uat its getting executed successfully.

The only difference that i can see in development is below-

TNAME
------+-------
DSNWFQB(10)

and

ACCESSTYPE
------+----
R

Inspite of

TNAME
------+-------
tablename

and

ACCESSTYPE
------+----
I

in UAT.

Also, the indexes being picked up are different in these two environment.
Is there any way I can sync the access paths.
Any help is appreciated.

Thanks,
Gopal.
Back to top
View user's profile Send private message

Dsingh29

Active User


Joined: 16 Dec 2008
Posts: 132
Location: IBM

PostPosted: Wed Jun 22, 2011 4:29 pm    Post subject: Reply to: Access Path Anomaly.
Reply with quote

I dont think access paths can be made that way as you want to, as the environments are totally different. Therefore, access paths are meant to be different.
Moreover, the other reason is UAT env. may be getting more resources then the development env. (which is true in most shops). Try to optimise query, I would suggest.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Jun 22, 2011 4:40 pm    Post subject:
Reply with quote

Gopal,

Its doing an Tablespace scan in development and in UAT its using an index.

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

Active User


Joined: 16 Dec 2008
Posts: 132
Location: IBM

PostPosted: Wed Jun 22, 2011 4:46 pm    Post subject:
Reply with quote

R - Table space scan. This is the worst type of access.
Back to top
View user's profile Send private message
Gopal Tripathi

New User


Joined: 16 Sep 2009
Posts: 15
Location: Hyd,Ind

PostPosted: Wed Jun 22, 2011 5:16 pm    Post subject: Reply to: Access Path Anomaly.
Reply with quote

Hi,

I agree as R for Tablespace scan and I for Indexed access.Can you please elaborate per se
"optimising the query".

Thanks,
Gopal.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Jun 22, 2011 5:35 pm    Post subject:
Reply with quote

Gopal,

In the UAT plan table check the ACCESSNAME and findout if that index is available in DEVL.

Quote:
"optimising the query"

You did not post the query.

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

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Jun 22, 2011 8:43 pm    Post subject:
Reply with quote

(Table space scan is not always the worst accesspath)

Tablescans are likely picked when runstats aren't run and db2 thinks the table is really small.
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 641
Location: Whitby, ON, Canada

PostPosted: Wed Jun 22, 2011 9:37 pm    Post subject:
Reply with quote

GuyC wrote:
(Table space scan is not always the worst accesspath)

Tablescans are likely picked when runstats aren't run and db2 thinks the table is really small.
Or when you run runstats on an empty table.
Back to top
View user's profile Send private message
Gopal Tripathi

New User


Joined: 16 Sep 2009
Posts: 15
Location: Hyd,Ind

PostPosted: Thu Jun 23, 2011 6:16 pm    Post subject:
Reply with quote

Hi,

I am unable to post the query as such, but the only thing that I see is the DEV and UAT both are set up as similar(All objects created similarly).

The stats are updated, I see no reason for optimiser to pick different access path, as the query is getting time out in one and running successfully in another.

Thanks,
Gopal.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Jun 23, 2011 6:31 pm    Post subject:
Reply with quote

GT,

How many rows are there in UAT and DEV ?
What is the SQLCODE you are getting, Post the error message ?

Without query and error message, this post wont go anywhere ?

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

New User


Joined: 16 Sep 2009
Posts: 15
Location: Hyd,Ind

PostPosted: Thu Jun 23, 2011 6:35 pm    Post subject:
Reply with quote

In addition to it,

NOTE:
The subqueries are indicated by a row in the PLAN_TABLE having TNAME="DSNWFQB(nn)", where 'nn' is the query block number associated with the subquery, and TABLETYPE='S'. In PLAN_TABLE, the PARENT_PLANNO column corresponds to the plan number in the parent query block where the correlated subquery is invoked for correlated subqueries. For non-correlated subqueries it corresponds to the plan number in the parent query block that represents the work file for the subquery.

As per the explain outout in development-

TNAME
------+-------
DSNWFQB(10)

With reference to above explanation and corresponding to the query block no 10 the respective table(access name) have two different indexes A and B in both the environments. In UAT the index A being picked up in UAT and index B being picked up in DEV.

Thanks,
Gopal.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Jun 23, 2011 8:23 pm    Post subject:
Reply with quote

If all objects are the same & the stats are similar, you're done.
There isn't much else that can influence the optimizer, except a few zparms.

Stats are more then just #rows : filterfactors, clusterratios, nlevels,...
With OSC you can generate SQL statements that would copy all relevant statistics.
if you copy the statistics from UAT and apply them in DEV and then you still get a different accesspath, only then we have to go look further.
Gopal Tripathi wrote:
I see no reason for optimiser to pick different access path

Basically what I'm saying is : "just because you don't see it, doesn't mean it isn't there."
Back to top
View user's profile Send private message
gylbharat

Active Member


Joined: 31 Jul 2009
Posts: 565
Location: Bangalore

PostPosted: Fri Jun 24, 2011 11:00 am    Post subject:
Reply with quote

The value of RID Pool in ZPARMS also is dependent -

"The RID Pool was an enhancement added with DB2 V2.2, and is used whenever the Optimizer determines that List Prefetch (LP) and/or Multiple Index Access (MIAP) will be used as the most efficient Access Path for you SQL request. The size of the RID Pool is specified in the ZPARM, and it can expand dynamically up to its maximum amount. The number in the ZPARM specifies the maximum number of blocks for the pool, and each block is 16K. Let's look at how we determine how much space may be necessary to use MIAP or LP. a RID (Row ID) is 4 bytes. If DB2 must sort 1 million RIDs, this is 4 Megabytes - times two, because you need twice the amount of space to be able to perform the sort. This is now 8 Megabytes. One user may not use more than 50% of the RID Pool, so the actual space necessary is 16 Megabytes. There are several causes for LP or MIAP to fail dynamically, and the failure can be an expensive process since DB2 must then restart the access using a different access path (usually a tablespace scan). "
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Jun 24, 2011 1:50 pm    Post subject:
Reply with quote

gylbharat wrote:
The value of RID Pool in ZPARMS also is dependent

No, not for the Optimizer.
During (re-)bind MAXRBLK is not considered, so in the explain (the chosen access path) it has no influence.
During execution, MAXRBLK can have an impact when 50% is reached and this is reported by RDS LIMIT REACHED.
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 IMS DC screen Access sivasaras IMS DB/DC 3 Fri Nov 18, 2016 6:38 pm
No new posts Access SQLCA fields inside a stored p... gangapd DB2 4 Thu Oct 27, 2016 10:20 am
No new posts RACF profile access vasanthz All Other Mainframe Topics 11 Fri Sep 23, 2016 5:51 am
No new posts How to perform Digest Access Authenti... vasanthz All Other Mainframe Topics 0 Tue Mar 15, 2016 4:47 pm
This topic is locked: you cannot edit posts or make replies. Find your career path at RBS (technol... Express Mainframe Jobs 0 Thu Feb 04, 2016 11:45 pm


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