Tuesday, 4 December 2012

SQL20201N The install, replace or remove of "DB2INST1.UUIDUDFJAR" failed as the jar name is invalid. SQLSTATE=46002

I kept seeing this: -

SQL20201N  The install, replace or remove of "DB2INST1.UUIDUDFJAR" failed as 
the jar name is invalid.  SQLSTATE=46002


when running this: -

$ db2 "call sqlj.install_jar('file:/home/db2inst1/UUIDUDF.jar', 'UUIDUDFJAR')"

when attempting to register a new Java user-defined-function in DB2 UDB 9.7.0.6.

Having checked the path, permissions etc. for my Java Jar file - /home/db2inst1/UUIDUDF.jar - I eventually Google'd the error :-)

I found this: -


which said, in part: -

SQL20201

The example for SQL20201 uses the same INSERT stored procedure used in the SQL4306 example above to illustrate a SQL20201 problem. SQL20201 can occur:

• When you attempt to drop and recreate the stored procedure, but did not remove the JAR file before calling sqlj.install_jar again.
• When you attempt to remove the JAR file with an invalid JAR ID.

Listing 30. SQL20201 example 1: Error occurs when installing the JAR file on Windows
                
D:\>db2 drop procedure INSERT
DB20000I  The SQL command completed successfully.

D:\>db2 call sqlj.install_jar("file:///D:\INSERT.jar", 'INSERTJAR')
SQL20201N  The install, replace or remove of "CWYLAW  .INSERTJAR" failed as
the jar name is invalid.  SQLSTATE=46002

For the first situation, you will have to call sqlj.remove_jar first before you call sqlj.install_jar to install the JAR file again. Or you can simply call sqlj.replace_jar if you want to replace the JAR file with updated class files.

I did as suggested: -

$ db2 "call sqlj.remove_jar('UUIDUDF.jar')"

and, voila, was then able to install the JAR.

Nice :-)

Thanks also to this developerWorks post - Generating universally unique identifiers (UUID) - which advised how to create the UDF in the first instance :-)

No comments: