avoid 'create tablespace' for DB2 on zOS, allow an encoding parameter

[wemu]

Hallo

we've tweaked around to get flyway running on our DB2 on zOS environment. We have noticed some things that might be bugs (but frankly: I'm not sure).

The migrate goal tries to create the versions table and a tablespace: see createMetaDataTable.sql.

we have removed the create tablespace statement (since the other scripts do not contain that either) and changed the create table statement as follows (our complete createMetaDataTable.sql):

set current sqlid = '${schema}';


CREATE TABLE "${schema}"."${table}" (
    "version_rank" INT NOT NULL,
    "installed_rank" INT NOT NULL,
    "version" VARCHAR(50) NOT NULL,
    "description" VARCHAR(200) NOT NULL,
    "type" VARCHAR(20) NOT NULL,
    "script" VARCHAR(1000) NOT NULL,
    "checksum" INT,
    "installed_by" VARCHAR(100) NOT NULL,
    "installed_on" TIMESTAMP NOT NULL WITH DEFAULT,
    "execution_time" INT NOT NULL,
    "success" SMALLINT NOT NULL,
    CONSTRAINT "${table}_s" CHECK ("success" in(0,1))
)
IN database "${schema}"
  CCSID EBCDIC
;

-- Indekser og constraints
CREATE UNIQUE INDEX "${schema}"."${table}_VPK_IDX" ON ${schema}."${table}" ("version" ASC);
ALTER TABLE "${schema}"."${table}" ADD CONSTRAINT "${table}_PK" PRIMARY KEY ("version");

CREATE INDEX "${schema}"."${table}_VR_IDX" ON "${schema}"."${table}" ("version_rank");
CREATE INDEX "${schema}"."${table}_IR_IDX" ON "${schema}"."${table}" ("installed_rank");
CREATE INDEX "${schema}"."${table}_S_IDX" ON "${schema}"."${table}" ("success");

Notice: we use CCSID EBCDIC instead of CCSID UNICODE (flyway version 3.2.1). That might needs to be a placeholder as well (for our database it's mandatory to use EBCDIC). And there is a IN database at the end.

The DB2 zOS Version and driver we use is:

DB2
DSN11015
IBM Data Server Driver for JDBC and SQLJ
4.17.29

Thanks a lot!

[axelfontaine]

I am a bit reluctant about having EBCDIC as the default encoding.

@cteig Thoughts about this and the other changes?

[cteig]

Hi,
Sorry, for the late response!

I have just learned that EBCDIC is the default encoding for z/OS and is the most used in DB2 z/OS. My customer also want this change, so I think it is a good idea to change this.

About the table space, IBM recommends not more than one table per table space because many DB2 utilities operate at the level of the table space. For example, backup and restore work at the table space level. It is possible to let DB2 implicitly create a table space if we have CREATE TABLE statement that does not specify an existing table space. But then the table space will be assigned to the default database and default storage group. I think it is better to set the database to ${schema}. But storage group should be changed to default (USING STOGROUP SYSDEFLT), or we need a new parameter for the storage group.

If we decide to have more parameters I guess it should be done together with this: #865

I can change the code (and documentation) for CCSID and STOGROUP (use default) this week, and make a pull request.

Christine

[wemu]

Hello

No problem. Since we were able to work around our issue by patching the createMetaDataTable.sql for db2zos we are not blocked. But it would be nice to work with the original flyway version.

I've also talked to one of our db2zos administrators. She was a bit confused about the queries that run when we use flyway with a db2zos instance.

I don't know much about db2zos. Some concepts seem to be quite different. Compared to the Oracle version of createMetaDataTable.sql it seems weird to create a tablespace. For us creating tablespaces is an infrastructure task that is done once by a db admin. So it would be consequent to also don't do that for db2zos (since its not done by flyway for Oracle).

We also had some discussions on how to figure out if those scripts need to run. The query for db2zos:

@Override
protected boolean doExists() throws SQLException {
    return jdbcTemplate.queryForInt("SELECT COUNT(*) FROM sysibm.sysdatabase WHERE name=?", name) > 0;
}

does not necessarily return something in our case. The sysdba's had to insert or create a database in there to hint flyway everything is ok. A schema seems to be auto-created with a create table schema.table. I don't know exactly what had to be done in that sysdatabase table to have flyway recognize an existing database / schema - I think here the concepts between db2zos and oracle differ quite a bit. So not sure what would be an elegant solution.

Our DB admin suggested these queries for creating a tablespace / table:

CREATE TABLESPACE SDBVERS
IN "${schema}"
USING STOGROUP SYSDEFLT PRIQTY  1000 SECQTY 10000 ERASE NO FREEPAGE 3 PCTFREE 10 
SEGSIZE 4
BUFFERPOOL BP0
LOCKSIZE ROW
LOCKMAX SYSTEM
CLOSE NO
COMPRESS YES
CCSID EBCDIC
;

CREATE TABLE "${schema}"."${table}" (
"version_rank" INT NOT NULL,
"installed_rank" INT NOT NULL,
"version" VARCHAR(50) NOT NULL,
"description" VARCHAR(200) NOT NULL,
"type" VARCHAR(20) NOT NULL,
"script" VARCHAR(1000) NOT NULL,
"checksum" INT,
"installed_by" VARCHAR(100) NOT NULL,
"installed_on" TIMESTAMP NOT NULL WITH DEFAULT,
"execution_time" INT NOT NULL,
"success" SMALLINT NOT NULL,
CONSTRAINT "${table}_s" CHECK ("success" in(0,1))
)
IN "${schema}".SDBVERS
CCSID EBCDIC
;

or to not create a tablespace (but from what you write above that seems not a wise idea):

CREATE TABLE "${schema}"."${table}" (
"version_rank" INT NOT NULL,
"installed_rank" INT NOT NULL,
"version" VARCHAR(50) NOT NULL,
"description" VARCHAR(200) NOT NULL,
"type" VARCHAR(20) NOT NULL,
"script" VARCHAR(1000) NOT NULL,
"checksum" INT,
"installed_by" VARCHAR(100) NOT NULL,
"installed_on" TIMESTAMP NOT NULL WITH DEFAULT,
"execution_time" INT NOT NULL,
"success" SMALLINT NOT NULL,
CONSTRAINT "${table}_s" CHECK ("success" in(0,1))
)
IN  database "${schema}"
CCSID EBCDIC
;

There might be a default tablespace for a user - I don't really know :-/

Thanks! :)

[cteig]

Hi,
I have looked through the settings for "create tablespace" and here is my proposal. I have tried to follow standard as much as possible. Please tell me if some of them stop you from using the original flyway version.

Since EBCDIC is the standard encoding for zOS I also plan to update CCDIC for the schema version table.

CREATE TABLESPACE SDBVERS
IN "${schema}"
USING STOGROUP SYSDEFLT PRIQTY -1 SECQTY -1 ERASE NO FREEPAGE 3 PCTFREE 10 DEFINE YES TRACKMOD YES
SEGSIZE 4
BUFFERPOOL BP0
LOCKSIZE PAGE
LOCKMAX SYSTEM
CLOSE YES
COMPRESS YES
CCSID EBCDIC
;

Christine

[wemu]

Sorry for my late reply.
I could check with our DB/2 z/OS responsible. I'm not very familiar with that system.

To be able to use the default flyway depends on where this statement will end up. The createMetaDataTable.sql is the wrong place for us.