|
View previous topic :: View next topic
|
| Author |
Message |
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Hi all,
One of my friends has been recently working on the impact analysis of adding a DATE column to an existing DB2 table. We have a few COBOL programs accessing this table. I was under the impression that a recompilation/rebind was needed only in case of a INSERT/SELECT * coded in the program. But the DBA suggested that "since it is DATE column, we need to recompile all the programs or else these packages will be invalidated".
Also I found this in DB2 V8 Administration Manual.
| Quote: |
Adding a new column to a table
When you use ALTER TABLE to add a new column to a table, the new column becomes the rightmost column of the table |and |the table space is placed in an advisory REORG-pending (AREO*) state. The physical records are not actually changed until values are inserted in the new column.
Plans and packages are not invalidated unless the new column is a TIME, TIMESTAMP, or DATE. However, to use the new column in a program, you need to modify and recompile the program and bind the plan or package again. You might also need to modify any program containing a static SQL statement SELECT *, which returns the new column after the plan or package is rebound. You must also modify any INSERT statement not containing a column list. |
Could anybody help me to understand why "package invalidation" occurs only when a DATE/TIME/TIMESTAMP column is added to a table. |
|
| Back to top |
|
 |
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
Hi all,
On further analysis, here are some facts which I came across.
DB2 Application Programming and SQL Guide lists out a few common situations in which DB2 marks a plan or package as invalid. And adding TIME, TIMESTAMP, or DATE column is just one of them. Also Whether a plan or package is valid is recorded in column VALID of catalog tables SYSPLAN and SYSPACKAGE.
See the topic Automatic Rebinding for more details. |
|
| Back to top |
|
|
|
|
