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: 20% [?]
Related Entries:
