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
 

 

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: 2180
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: 2180
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: 2180
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 DB2 query Using Recursion, Converting... smilewithashu2 DB2 1 Tue Jan 03, 2017 12:50 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts TOT & DATE parameter in ICEMAN hemanthj642 DFSORT/ICETOOL 4 Mon Nov 14, 2016 5:19 am
No new posts SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm


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