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: 2727
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: 762
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: 1208
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 Rebind issue - Package DB2 2
No new posts Create table from List CLIST & REXX 8
No new posts Map VSAM Dataset to a Table/Tablespace DB2 2
No new posts COBOL Table data to a flat file with ... COBOL Programming 8
No new posts "Output" internal table (OD... COBOL Programming 0
Search our Forums:

Back to Top