Multiple small fixes to 3.5.x

Liquibase Development
1

Hi,

I made multiple small fixes to the current 3.5.x HEAD including:
* Add better support for DB2 z/OS

Have fun,
Markus

2

Ok, great!
Then I have to clean up a little bit and send new pull requests.
Markus

3

Yes, always interested in fixes. Just send pull requests at github.com/liquibase/liquibase

Nathan

4

Thanks, I’ll take a look at them.

Nathan

5

Hi Marleen,

I saw that some changes on the DB2 i-Series support were checked in already.

Have to revise my changes. Will post the effort here.

Cheers,

Markus

6

Hi,

I have now created 4 separate pull requests:

* Determine the DB2 data server type correctly

* Make CSV files created by LiquiBase readable by Liquibase

* Handle TimeStamps with nano second precision correctly

* Fix a NullPointerException in DiffToChangeLog.sortMissingObjects

I have more things to follow up, like using the right tables for reverse engineering DB2 z/OS or unloading compressed CSV files, but I need those pull requests to be integrated first.

Cheers,

Markus

7

Hello Markus,

I am very interested in your fixes for DB2 z/OS, because I encountered the same problems with querying the system tables and the incorrect detection of the z/OS version.

Are you making any progress on the pull requests?

Best regards, Marleen

8

I merged the pull request into liquibase/master. Let me know if you find any problems with the changes.

Nathan

9

Hi,

created the following pull request:

* Add reverse engineering support for DB2 z/OS

@Markus

10

Hi,

@Marleen:

 I forgot, but there is one more problem with reverse engineering DB2 z/OS databases. LiquiBase is using wherever possible the JDBC DatabaseMetaData methods (like getTables, getSchemas, etc.) to read the database definition. The DB2 JDBC driver maps this method calls to stored procedures on the host.

Here comes the tricky part. DB2 z/OS has the zparm DESCSTAT set to NO by default. See Application programming defaults panel 1: DSNTIPF. Problem: When DESCRIBE FOR STATIC is set to NO, the JDBC driver cannot determine the column names of ResultSets returned by stored procedures => the columns of the ResultSet returned by DatabaseMetaData.getTables() do not have the name “TABLE_CAT”, “TABLE_SCHEM”, “TABLE_NAME”, etc., but “1”, “2”, “3”, etc.

Therefore the following does not work: row.getString(“TABLE_CAT”).

There are 3 solutions:

  1. Set DESCSTAT to YES. I do not know the reasons, but the host administrators are very shy about that. We were not possible to convince them.
  2. Patch LiquiBase to use column numbers, not column names. This would be perfectly legal with the JDBC standard, but it's a lot work. 
  3. Create a wrapping DB2 JDBC driver, that injects the column names on calls to DatabaseMetaData methods. This is what we did. If you have the same problems, I could provide you that driver.

Cheers,

Markus

11

Hi Nathan,

I added another commit to the last pull request:

Prevent using an alias in a WHERE clause

Using an alias in the where clause is not supported in older (or all?) DB2 versions.

Cheers,

Markus

12

Great! Will test tomorrow with the liquibase/master branch.

13

Hello Markus and Nathan,

I have downloaded the latest Liquibase code and tested the changes by Markus.

The changes that are needed for creating a snapshot are OK: I can now create a snapshot of my DB2 z/OS database, which I could not do before. Thank you for the changes Markus!

Fortunately I did not have that problem mentioned above with the JDBC DatabaseMetadata methods, in my case they return the columnnames correctly.  I am using a v11 DB2 z/OS here, maybe the default changed? Or our admins did set the DESCSTAT parameter to true...

I did find a problem with the FindForeignKeyConstraintsGeneratorDB2 changes: the base tablename/columnname are switched with the foreign tablename/columnname, so they are returned in the wrong fields. Changing the first part of the statement like

  1. SELECT FK.TBNAME  as basetablename,        FK.COLNAME    as basecolumnname,        PK.TBNAME  as foreigntablename,        PK.NAME as foreigncolumnname,        R.RELNAME  as constraintname

should solve the problem. (When you look at the where statement, PK is used to get the primary key of the referenced/foreign table...)

I did not test the changes in GetViewDefinitionGeneratorDB2 and SequenceSnapshotGenerator, because my current database model does not have views or sequences.

Best regards, Marleen

14

Hi Marleen,

thanks for that great catch. Creating a pull request with the fixed Markus