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
 

 

BINDING and PLAN

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

New User


Joined: 15 Sep 2008
Posts: 5
Location: India

PostPosted: Mon Sep 15, 2008 4:34 pm    Post subject: BINDING and PLAN
Reply with quote

Suppose at the Plan and BIND time there were 1 million records in the EMP table.After binding all 1 million records are deleted and table is loaded with diffrent 1 million records.
Will this effect the plan?? if yes the how?
Do we need to bind it again??
How this will effect the execution time??

Saurabh
Back to top
View user's profile Send private message

birdy K

New User


Joined: 05 Mar 2008
Posts: 72
Location: chennai

PostPosted: Mon Sep 15, 2008 4:40 pm    Post subject:
Reply with quote

Bind is related with SQL statements in the program. Not with the records in the table. Corrections are welcome.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Mon Sep 15, 2008 4:42 pm    Post subject:
Reply with quote

look at the plan table generated before the deletion, and after the deletion.

yeah, gotta bind it again against the new runstats - you do run runstats?.

There are too many variables for someone to start guessing.
Back to top
View user's profile Send private message
birdy K

New User


Joined: 05 Mar 2008
Posts: 72
Location: chennai

PostPosted: Mon Sep 15, 2008 6:11 pm    Post subject:
Reply with quote

Hi dick,

Can you tell what is runstats?
Back to top
View user's profile Send private message
zero

New User


Joined: 01 Dec 2007
Posts: 21
Location: Hyderabad

PostPosted: Fri Sep 19, 2008 1:51 pm    Post subject: Reply to: BINDING and PLAN
Reply with quote

As Birdy said, Bind is related with SQL statements in the program. Not with the records in the table.

DB2 has a special set of tables called 'catalog Tables' which contain Information related to all the tables, tablespaces, etc that exist in the Database. The catalog tables are updated when some particular utilities are run.

RUNSTATS is one such utility that gathers all the information about a tablespace and tables in that tablespace and updates some catalog tables.
The information that RUNSTATS gathers is No. of records in the table (this is called Table Cardinality), No. of Distinct values in a column (this is called Column Cardinality) in addition to some storage related information.

BINDing is the process that converts the SQL statements in a program to Executable form. Actually, there can be many ways to fetch result for a SQL query. the Shortest possible was is selected during binding. Shortest means the way that takes the less time, uses less resources. This is called 'Access path'. DB2 optimizer is responsible for this.

optimizer selects a path/way based on the information available in the DB2 catalog. So, it's very important to keep the catalog tables updated by running RUNSTATS and other utilities. we will be given the correct result even when Catalog tables are not updated. But, this is not efficient and Takes much time.

Now, coming to your query, Initially we had 1 million records and ran the RUNSTATS utility. So, all the updated info is available in the catalog tables.
Now, we bound the program. till now, it is ok.

Now, if we delete all the 1 million records and inserts some other 1 million records, we can have two cases :

a) we did not run the RUNSTATS :
so, old catalog info is used and Query is not efficient.

b) we ran the RUNSTATS :
so, when the query is actually executed, it takes the updated info. and runs fine.

So, we don't have to bind even if data in table changes.

Any corrections are welcome,
...
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 -419 SQL error when binding senthamizh DB2 2 Sun Jun 12, 2016 8:20 pm
No new posts Program and its corresponding plan vickey_dw DB2 4 Thu Apr 07, 2016 9:27 pm
No new posts Should we Rebind Plan if no SQL changes sappy_mf DB2 2 Thu Mar 03, 2016 2:13 pm
No new posts What's plan, package, bind ? jackzhang75 DB2 2 Sun Feb 28, 2016 7:29 pm
No new posts DB2 plan for DSNTEP2 / DSNTEP4 Mainak_Dalal DB2 3 Thu Jan 21, 2016 1:08 am


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