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
 

 

column addition to a table - existing plan/package state?

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

New User


Joined: 10 Mar 2006
Posts: 26

PostPosted: Tue Aug 18, 2009 5:18 pm    Post subject: column addition to a table - existing plan/package state?
Reply with quote

I want to add a column to a particular table. This table is being referenced by a no. of plans/packages. On addition of the column, will the packages get invalidated?
Back to top
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Tue Aug 18, 2009 5:48 pm    Post subject:
Reply with quote

Kanisha,

If the data type of the column is time,date or timestamp then plans & packages will be invalidated.

Sushanth
Back to top
View user's profile Send private message
kanisha_prabha

New User


Joined: 10 Mar 2006
Posts: 26

PostPosted: Tue Aug 18, 2009 6:09 pm    Post subject:
Reply with quote

Thanks Sushanth.
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 186
Location: chennai

PostPosted: Fri Aug 21, 2009 5:19 pm    Post subject: Reply to: column addition to a table - existing plan/package
Reply with quote

Hi Sushanth

How the plans and packages will be invalidated when the added column in the table is having data types of date, time or timestamp which is already bound with plan or package?

I didn't got any error of plan or package invalidation after adding a column with data type date.

Can you please post a replay?

Regards
Raghu
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Fri Aug 21, 2009 5:50 pm    Post subject:
Reply with quote

Quote:
I didn't got any error of plan or package invalidation after adding a column with data type date.


did you attempt to execute any of the plans/programs?

you will not get a 'plan is invalidated because of change in table'.

it is your responsibility to know that when you alter a table, add new columns of certain datatypes, any plans involving the altered table will be invalidated.

why? the documentation says so.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Fri Aug 21, 2009 6:54 pm    Post subject:
Reply with quote

Hi Raghu,

Previously i didn't do a test. Now, i did a test like the following,

Code:
SELECT NAME,COLLID,OWNER,VALID 
FROM "SYSIBM".SYSPACKAGE       
WHERE NAME='DB2MULSE'           ;


NAME               COLLID             OWNER              VALID 
DB2MULSE           BOBC               BOBB               Y     



 ALTER   TABLE SHANDB.EMPLOYEE 
         ADD TRIAL DATE         
             ;                 

SELECT NAME,COLLID,OWNER,VALID 
FROM "SYSIBM".SYSPACKAGE       
WHERE NAME='DB2MULSE'           ;


NAME               COLLID             OWNER              VALID 
DB2MULSE           BOBC               BOBB               A     



Catalog indicated an "A"lter statement was executed. But DB2, didn't invalidate it.

As dino mentioned, the documentation says
"Plans and packages are not invalidated unless the new column is a TIME, TIMESTAMP, or DATE."

Now, i am wondering why the documentation said that ?hmmm...

Sushanth
Back to top
View user's profile Send private message
Raghu navaikulam

Active User


Joined: 27 Sep 2008
Posts: 186
Location: chennai

PostPosted: Mon Aug 24, 2009 1:05 pm    Post subject: Reply to: column addition to a table - existing plan/package
Reply with quote

Hi Sushanth

Thanks for your comment.
After reading through your replay
Quote:
If the data type of the column is time,date or timestamp then plans & packages will be invalidated.

I did not find anythig wrong if the datatype of the column being added is DATE,TIME or TIMESTAMP. So I tested with these datatypes and run the same program wihtout doing another BIND. It works fine for me.
Anyway thanks for your replay.

Regards
Raghu
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Aug 24, 2009 7:12 pm    Post subject:
Reply with quote

Hello,

Quote:
So I tested with these datatypes and run the same program wihtout doing another BIND. It works fine for me.
Which means that "you got away with it". . . If you work with IBM software long enough, you will discover that when direction is provided (i.e. "If the data type of the column is time,date or timestamp then plans & packages will be invalidated.", it is a bad decision to do otherwise.

It is not a game or a contest whereby we should try to "get around" the direction provided. Keep in mind that without warning, this could be activated as it is already documented. . .

fwiw. . .
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Aug 24, 2009 7:32 pm    Post subject:
Reply with quote

D.sch,

Quote:
Keep in mind that without warning, this could be activated

Is this like a system setting ?

Sushanth
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Mon Aug 24, 2009 7:56 pm    Post subject:
Reply with quote

Quote:
Is this like a system setting ?


more like crud rising to the top.
Back to top
View user's profile Send private message
Arun Raj

Moderator


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

PostPosted: Mon Aug 24, 2009 8:15 pm    Post subject:
Reply with quote

Hi all,

Does this previous topic add something in here??

http://www.ibmmainframes.com/viewtopic.php?t=37319&highlight=package
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Mon Aug 24, 2009 8:24 pm    Post subject: Reply to: column addition to a table - existing plan/package
Reply with quote

I just stepped over this
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.apsg/db2z_rebindapplication.htm

the Automatic Rebinding content is quite interesting

especially for

Quote:
... A few common situations in which DB2 marks a plan or package as invalid are:

When a package is dropped
When a plan depends on the execute privilege of a package that is dropped
When a table, index, or view on which the plan or package depends is dropped
When the authorization of the owner to access any of those objects is revoked
When the authorization to execute a stored procedure is revoked from a plan or package owner, and the plan or package uses the CALL procedure-name form of the CALL statement to call the stored procedure
When a table on which the plan or package depends is altered to add a TIME, TIMESTAMP, or DATE column
When a table is altered to add a self-referencing constraint or a constraint with a delete rule of SET NULL or CASCADE
When the limit key value of a partitioned index on which the plan or package depends is altered
When the definition of an index on which the plan or package depends is altered from NOT PADDED to PADDED
When the definition of an index on which the plan or package depends is altered from PADDED to NOT PADDED
When the AUDIT attribute of a table on which the plan or package depends is altered
When the length attribute of a CHAR, VARCHAR, GRAPHIC, VARGRAPHIC, BINARY, or VARBINARY column in a table on which the plan or package depends is altered
When the data type, precision, or scale of a column in a table on which the plan or package depends is altered
When a plan or package depends on a view that DB2 cannot regenerate after a column in the underlying table is altered
When a created temporary table on which the plan or package depends is altered to add a column
When a user-defined function on which the plan or package depends is altered
When a column is renamed in a table on which a plan or package is dependent
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Aug 24, 2009 9:03 pm    Post subject:
Reply with quote

Hello,

Quote:
Is this like a system setting ?
No. The underlying code (not some setting/parameter) could be changed to enforce what is already documented.
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 JCL to delete component in a package sundaram.naveen Compuware & Other Tools 14 Tue Nov 29, 2016 6:21 pm
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. Checking wether a string starts from ... sundaram.naveen CLIST & REXX 9 Thu Oct 06, 2016 11:56 am
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Add system date inside of a PS file a... pramitdas DFSORT/ICETOOL 5 Sun Aug 28, 2016 12:06 am


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