|
View previous topic :: View next topic
|
| Author |
Message |
kanisha_prabha
New User
Joined: 10 Mar 2006
Posts: 26
|
|
|
|
| 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 |
|
 |
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Kanisha,
If the data type of the column is time,date or timestamp then plans & packages will be invalidated.
Sushanth |
|
| Back to top |
|
|
kanisha_prabha
New User
Joined: 10 Mar 2006
Posts: 26
|
|
|
|
| Thanks Sushanth. |
|
| Back to top |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| 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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
Raghu navaikulam
Active User
Joined: 27 Sep 2008
Posts: 193
Location: chennai
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
D.sch,
| Quote: |
| Keep in mind that without warning, this could be activated |
Is this like a system setting ?
Sushanth |
|
| Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Quote: |
| Is this like a system setting ? |
more like crud rising to the top. |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10873
Location: italy
|
|
|
|
I just stepped over this
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
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 |
|
|
|
|
