IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Db2 v10 - Package invalidated on alter table for null column


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
apvardhini

New User


Joined: 23 Apr 2009
Posts: 20
Location: India

PostPosted: Tue Oct 28, 2014 12:56 pm
Reply with quote

Hi All,

We are facing this strange issue. We are in Db2 v10 and are using Native SQl stored procedures. Whenever we Alter a nullable column, all our stored proc packages are getting invalidated. The BIND PACKAGE with DEPLOY option does not help either nor the REBIND command. The Stored procs work only if we drop and recreate them. Can someone help me understand whats happening as the references I have seen says the package should not be invalidated if a nullable column is altered (All alter column is for increasing the size of VARCHAR columns).
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2567
Location: NYC,USA

PostPosted: Tue Oct 28, 2014 10:16 pm
Reply with quote

Have you looked here?
www-01.ibm.com/support/docview.wss?uid=swg1PK21362

did you try and correct the values before rebind?
Back to top
View user's profile Send private message
apvardhini

New User


Joined: 23 Apr 2009
Posts: 20
Location: India

PostPosted: Tue Nov 04, 2014 1:57 pm
Reply with quote

Yes.. I have, but my scenario is slightly different.

As per redbooks, the packages would also be invalidated when we change the length attribute of a CHAR or VARCHAR column.

So a REBIND should resolve an issue but in my case, only DROP and CREATE works...
Back to top
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 747
Location: Pennsylvania

PostPosted: Tue Nov 04, 2014 6:28 pm
Reply with quote

I believe the situation is this.

With a Cobol program, the Package is created when the Bind is done.
The DBRM and Load module come together and the Package is created.

With a Stored Procedure, there is no DBRM or LOAD module. No Bind is performed.
The Package is created with the CREATE statement.

Quote:
So a REBIND should resolve an issue but in my case, only DROP and CREATE works...


The first part of is statement is not correct. The second part is correct.

Just do the Drop and Create.
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1206
Location: Bangalore,India

PostPosted: Wed Nov 05, 2014 3:53 pm
Reply with quote

Hi There,

After rebinding please try stop and start the procedure.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Can we Insert duplicates in Primary U... DB2 2
No new posts Need to read duplicate rows from tabl... DB2 3
No new posts COnvert a column with mix of hex ,cha... DB2 5
No new posts VSAM Alter Command - Cylinder Extent ... JCL & VSAM 3
No new posts Hex Table COBOL Programming 2
Search our Forums:

Back to Top