DB21034E when creating stored procedure in DB2
While I was trying to create a stored procedure in my DB2 Express-C windows installation, I got the following error message:
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:SQL0219N The required Explain table "SCHEMA.EXPLAIN_INSTANCE" does not exist. LINE NUMBER=16. SQLSTATE=42704
At first, this error message didn’t make much sense. Why would DB2 need to use the explain tables if I’m not running this command in the explain mode? Even doing a db2 set current explain mode no didn’t seem to help. But, creating the explain tables would solve the problem (you can create the explain tables by hand by runing db2 -tvf ~/SQLLIB/MISC/EXPLAIN.DDL). If creating the explain tables would solve the problem, it was because somehow, DB2 was running in the explain mode when creating the stored procedures.
Investigating the DB2 registry variables (db2set -all) I noticed the existence of the variable:
[i] DB2_SQLROUTINE_PREPOPTS=explain yes
A search for this term on DB2 documentation and the answer was found. The variable DB2_SQLROUTINE_PREPOPTS is used to specify the precompile and binding options for store procedures. After knowing that was the problem, the fix is easy. Just update the registry variable running db2set DB2_SQLROUTINE_PREPOPTS="explain no".
Note: Since this is an instance level variable, the changes will only make effect after you restart your DB2 instance. Just run db2stop and db2start and you’re good to go!
Popularity: 7% [?]
Related Entries:
December 29th, 2009 at 11:01 pm
hey, your post
January 19th, 2011 at 10:33 pm
Мне все понравилось, только если бы еще денег на блоге дали или конкурс провели, было бы вообще отлично.
March 15th, 2011 at 11:33 am
Gaming is what we have been doing since we were young. It has become a genuine part of our lives especially our childhood. And apparently, it what keeps us sane and intact as it relieves stress and become a prime leisure. Online car games are rampant variations of the games we have loved. As the years progress through the new millennium, more free online games are shown off in the World Wide Web. And as technology flourished through the years, the evidence of progressive online gaming developing catered a wider scope. More probabilities are entertained and more needs are catered. What happens now is that people are torn between what is proper to have and what is not proper to own but will be better to have.
October 15th, 2011 at 9:17 am
Whats up! I just wish to give a huge thumbs up for the good information you might have right here on this post. I will likely be coming back to your weblog for extra soon.