BINDING and PLAN

saurabhgemini10 · New User Joined: 15 Sep 2008 Posts: 5 Location: India

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

birdy K · New User Joined: 05 Mar 2008 Posts: 72 Location: chennai

Bind is related with SQL statements in the program. Not with the records in the table. Corrections are welcome.

dbzTHEdinosauer · Posted: Mon Sep 15, 2008 4:42 pm

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.

birdy K · New User Joined: 05 Mar 2008 Posts: 72 Location: chennai

Hi dick,

Can you tell what is runstats?

zero · New User Joined: 01 Dec 2007 Posts: 21 Location: Hyderabad

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,
...