DB2i fails due to BOOLEAN column in lock table

I’m experimenting with liquibase and DB2i (GitHub - liquibase/liquibase-db2i: Liquibase DB2 for iSeries support). I’m doing a very simple update (to create a table), but it fails with:

liquibase.exception.DatabaseException: [SQL0204] BOOLEAN in *LIBL type *SQLUDT not found. [Failed SQL: (-204) CREATE TABLE “LBDBTEST”.DATABASECHANGELOGLOCK (ID INTEGER NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DBCHGLOGLOCK PRIMARY KEY (ID))]

It is on this line of code in StandardLockService.init():

            executor.execute(new CreateDatabaseChangeLogLockTableStatement());

I see further down in the init() method there are some checks for supportsBooleanDataType() (which I added to DB2iDatabase on my local copy of the extension, to return ‘false’), but it doesn’t get that far because of the above fail.

Any suggestions? Is this a bug in the init() method?

Hi @leebreisacher ,
Welcome to the community!
Make sure the database is created correctly on DB2.

can you provide some informations?

  • operating system, db2 version
  • tablespace, bufferpool, pagesize, temptablespaces and liquibase property example without real db path, username and password.

I using liquibase with db2 and i can confirm you it’s working well and i’ll try to help you making it works.

Kind regards,
Costin

1 Like

Thanks much! tbh, I am not an IBMi expert. I just created an empty library and thought that might work. I could use some advice on how to properly create an empty database library on the i.

Also, I am executing liquibase from a java program using APIs, but I do not see how to configure things – is there an API for pointing at a liquibase.properties file? Or are there APIs where I can set various properties programatically? The API docs for liquibase are rather thin – or maybe there is better documentation/examples somewhere that I have not found?

Thanks,

Lee

I just noticed that you (Costin) might be talking about DB2 on z, whereas I’m talking about DB2 on i (which is quite different). It does indeed appear that DB2 on IBMi does not support BOOLEAN columns, so I think there is indeed a bug in liquibase when it tries to create the LOCK table on DB2 on IBMi.

Lee

Whoa. Looking much deeper (obviously I’m quite new to liquibase), I see that this kind of thing should already be handled via the clever LiquibaseDataType mechanism. But I think there still might be a subtle bug deep in liquibase.datatype.core.BooleanType.toDatabaseDataType(Database). This line:
} else if (database.getClass().isAssignableFrom(DB2Database.class)) {
I think should be:
} else if (DB2Database.class.isAssignableFrom(database.getClass())) {

Lee

Hey @leebreisacher ,

If you think that you found a bug, first let me thank you for digging in. We appreciate your time. Could you file a bug on it?

Thanks,

Ronak