Has anyone managed to load any stored procs through Liquibase in to DB2

Hi folks,

I am trying to use Liquibase to load stored procedures (sprocs) in to a DB2 Database, no matter what the sproc code looks like I seem to get weird errors from DB2 suggesting syntactic errors in the sproc code, usually something like the following:

 com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=P1: 

I have tested the correctness of the sproc code by loading them in to DB2 directly and can confirm that they all load without error, they all execute once loaded too so I’m confident that their code is correct.

Interestingly (or perhaps rather worryingly) I have discovered that I get the same error as with Liquibase when I try to load the sprocs through some other means that uses the IBM DB2 JDBC driver (such as DBVisualiser). This is leading me to believe that there is some fundamental issue with the IBM DB2 JDBC driver (I’m using db2jcc.jar v9.7.5). I’m currently wondering if it is somehow generating invalid statements when it is asked to create a sproc

Also interesting is that I note that I can load functions, tables, indexes etc just fine, the issue seems to be isolated to sprocs only.

I have tried loading the sprocs using the and elements but get the same errors using both approaches. have also experimented with stripComments as some of the sprocs contain comments but this doesn’t seem to have any effect on the errors.

Has anyone managed to successfully load a DB2 sproc using Liquibase? I’m on the verge of giving up which I really don’t want to do as our current process for managing physical schemata is a manual human operated process with is giving us no end of bother!

Grateful for any help!

Cheers,

Edd

I am having same problem, but I think I narrowed the issue to String assignment and concatenation using pipe (||) character. I am still experimenting with this process, if I succeed, I will let you know. If you have already successfully migrated a procedure in DB2 let me know what worked.

Hi Kumar,

Great work, thanks for this. I haven’t progressed the issue at all so would be very interested in your findings.

Cheers,

Edd

Any one has any clue why JDBC driver is having issue when loading procedure.


Please check your SP for special characters embedded in string assignment like --,*,; etc. Liqibase doesn’t like those characters in SP.

What is the error you gettin?

Also make sure you are using the tag. That ensures that liquibase does not try to split it into separate statements.


Nathan