If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > I can't build the SQL procedure after I call it in the CLP,why?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 74
Arrow I can't build the SQL procedure after I call it in the CLP,why?

HI, all

I am developing the SQL stored procedure using SPB(Stored Procedure Builder) on windows 2000 and DB2 v7.1. I found that I can't build the sql procedure again after I call it in the CLP.

I have finished to build the SQL stored procedure and then I call it in the CLP(Command Line Processor). But I found that the result is error, so I modified the SQL stored procedure using SPB and then builded it. The messages of SPB is:


DB2ADMIN.PROC3 - Failed to drop the stored procedure.

DB2ADMIN.PROC3 - [IBM][CLI Driver][DB2/NT] SQL0970N The system attempted to write to a read-only file. SQLSTATE=55009



DB2ADMIN.PROC3 - Create stored procedure returns -454.

DB2ADMIN.PROC3: [IBM][CLI Driver][DB2/NT] SQL0454N The signature provided in the definition for routine "PROC3" matches the signature of some other routine that already exists in the schema or for the type. SQLSTATE=42723



DB2ADMIN.PROC3 - Build failed.

DB2ADMIN.PROC3 - Changes rolled back.


Who can tell me how to resolve this problem?
Reply With Quote
  #2 (permalink)  
Old
Super Moderator
 
Join Date: Aug 2001
Location: UK
Posts: 4,649
Re: I can't build the SQL procedure after I call it in the CLP,why?

App Dev Guide Says


"
For stored procedures, the keyword KEEPDARI has the default value yes. This keeps the stored procedure process alive. If you are developing a stored procedure, you may want to test loading the same stored procedure library a number of times. This default setting may interfere with reloading the library. Its best to change the value of this keyword to no while developing stored procedures, and then change it back to yes when you are ready to load the final version of your stored procedure.
"

Also, have a read through the technote

http://www-3.ibm.com/cgi-bin/db2www/...d2w&fn=1006542


Have a nice day

Sathyaram

Quote:
Originally posted by zhouhaiming
HI, all

I am developing the SQL stored procedure using SPB(Stored Procedure Builder) on windows 2000 and DB2 v7.1. I found that I can't build the sql procedure again after I call it in the CLP.

I have finished to build the SQL stored procedure and then I call it in the CLP(Command Line Processor). But I found that the result is error, so I modified the SQL stored procedure using SPB and then builded it. The messages of SPB is:


DB2ADMIN.PROC3 - Failed to drop the stored procedure.

DB2ADMIN.PROC3 - [IBM][CLI Driver][DB2/NT] SQL0970N The system attempted to write to a read-only file. SQLSTATE=55009



DB2ADMIN.PROC3 - Create stored procedure returns -454.

DB2ADMIN.PROC3: [IBM][CLI Driver][DB2/NT] SQL0454N The signature provided in the definition for routine "PROC3" matches the signature of some other routine that already exists in the schema or for the type. SQLSTATE=42723



DB2ADMIN.PROC3 - Build failed.

DB2ADMIN.PROC3 - Changes rolled back.


Who can tell me how to resolve this problem?
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 74
Thank you for your help. I have resolved this problem.
Reply With Quote
  #4 (permalink)  
Old
Super Moderator
 
Join Date: Aug 2001
Location: UK
Posts: 4,649
Please can you brief on how you resolved the problem ...

Cheers

Sathyaram

Quote:
Originally posted by zhouhaiming
Thank you for your help. I have resolved this problem.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 74
1 db2cmd
2 db2 update database manager configuration using KEEPDARI NO
or db2 update dbm cfg using KEEPDARI NO
3 db2stop force
4 db2start
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On