Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Adding DATE column to a table.

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

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Tue Jan 13, 2009 10:52 pm    Post subject: Adding DATE column to a table.
Reply with quote

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
View user's profile Send private message

Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Tue Jan 13, 2009 10:54 pm    Post subject:
Reply with quote

Here are some related topics which I found here in this forum.

http://ibmmainframes.com/viewtopic.php?t=29212&highlight=recompile
http://ibmmainframes.com/viewtopic.php?t=13844&highlight=recompile
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2285
Location: @my desk

PostPosted: Fri Jan 16, 2009 11:59 pm    Post subject:
Reply with quote

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
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 Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts Compare yesterday's date to the one o... migusd SYNCSORT 11 Fri Sep 22, 2017 11:35 pm
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts Validate the Date girishb2 DFSORT/ICETOOL 9 Tue Sep 19, 2017 1:12 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us