LB 2.0.1: liquibase.exception.DatabaseException using java.sql.Types.NUMERIC columns against DB2 9.7

General Discussion
1

Hi all,

we tried to run following create table script using Liquibase 2.0.1 against a DB2 9.7


<?xml version="1.0" encoding="UTF-8"?>

<databaseChangeLog xmlns=“http://www.liquibase.org/xml/ns/dbchangelog”

):

DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=NUMBER, DRIVER=4.7.85

[updateDatabase] liquibase.exception.DatabaseException:

[…]

 

This DB2 error means that the generated SQL by Liquibase is wrong, the SQL data type should have been NUMERIC instead of NUMBER.


Running this script against a MSSQL 2005, or an Oracle 11g R1 works fine.


Is this a bug in LB, or is there something wrong with our script? 


kind regards,

Frank

2

Nathan can correct me if I am wrong, but it looks like the DB2TypeConverter does not override the getNumberType() method from the AbstractTypeConverter.  As a result, the DB2TypeConverter will return NUMBER instead of NUMERIC. 

That also seems to match your comment about MSSQL, because that class has overriden the method to use NUMERIC instead of NUMBER but the OracleTypeConverter doesn’t override the getNumberType() method, which is a bit confusing…

3

Yes, it should have had getNumberType overridden. I just committed a fix someone had suggested of using DECFLOAT(16) for DB2 since DB2 does not have a “NUMBER” type which is the default.  If you create a custom extension (liquibase.org/extensions) which extends DB2TypeConverter and overrides getNumber() like you see in the current master branch, that should work for you. 


Nathan

4

Yup, I  got the same error. I am not the expert in DBs, but all DB2 documentation I could found had NUMERIC, so this is probably a huge bug. I totally forgot to fill the issue, because I am using custom TypeConverter anyway.

5

It looks like a problem with liquibase. I committed a fix for it that will be in the next version


Nathan

6

I think it was fixed in 2.0.3. 

Nathan

7

Hi Frank

8

Hi Frank

9

Hi,

I just tested the LB 2.03 release concerning the DB2 error of above: unfortunately same result. Frank

10

Hi,

we tried LB 2.0.5. in these days. Now java.sql.Types.NUMERIC is mapped on DECFLOAT(16) (you mentioned it a while ago), which conflicts with possible precision predicates: e.g. java.sql.Types.NUMERIC (11,2) becomes now DECFLOAT(16)(11,2) that ends - of course!- in a syntax error. 


Sorry for recognizing the impact of DECFLOAT(16) so late! 


There is a data type NUMERIC(n) in DB2 at leat since V8 which would do better…

kind regards,

Frank

11

Hi there,

we now tested this case with LB 3.0.7. Now java.sql.Types.NUMERIC is mapped on DECIMAL, which seems to work. There are no syntax errors any longer and the build is successful now.


Best regards,
Martin