Schema create/drop in DB2 zOS

[alistair-RG]

Currently the driver can't create or drop schemas directly (see DB2ZSchema.java )

If you define defaultSchema or schemas then:

"Flyway will automatically attempt to create and clean this schema first. If Flyway automatically created it, then it will be dropped when cleaning."

Why does it matter ?

If you want to put the schema history table in a specific schema (that doesn't yet exist) then Flyway is unable to do this

What are the alternatives ?

• As a workaround you could manually create the schema in SQL and get Flyway to run this before everything else using the initSQL configuration as a one-off

Related information

• Creating a schema by using the schema processor
• The DB2 LUW version has some simple equivalent functionality - DB2Schema.java so this may be suitable to borrow from.
• The original Flyway code to support this was just a pass through rather than raising an exception "No CREATE SCHEMA necessary as schemas are dynamic in DB2 z/OS" but we don't know the truth or value of this anymore

[alistair-RG]

@gahoekstraibm 🙏

[gahoekstraibm]

Hi @alistair-RG , in DB2 for z/OS, a schema is not something you can create or drop. It is just used as a qualifier (comparable to a namespace) for named database objects such as tables, indexes etc. See https://www.ibm.com/docs/en/zos-basic-skills?topic=structures-db2-schemas-schema-qualifiers.

Creating a new flyway "schema" works without explicitly creating the database schema. So what you wrote under "Why does this matter" is not valid.

There may be some z/OS security pre-conditions though, to allow you to create/manage objects under some schema-qualifier or database:

• Schema authorization IDs need privileges to create and manage objects in the corresponding database and to use the relevant storage groups and buffer pools
• To create stored procedures, you need CREATE IN COLLECTION and BINDADD privileges for the schema authorization IDs
• The database admin user used to connect to the DB2 database must be able to change the current authorization ID to the schema authorization IDs. This means that the database admin user should be member of the corresponding schema security groups (for all flyway schemas), unless a DB2 sysadmin user is used as install/admin user.
• To be able to perform REORGs or INDEX REBUILDs, the database admin user used to connect to the DB2 database must have authorizations to run REORG or REBUILD utilities via SYSPROC.DSNUTILU

[alistair-RG]

Ah - thanks for that.

Do you think the exception in the doCreate method is too enthusiastic and it ought to just be a log ? For example SQLite does it this way

[gahoekstraibm]

Well, I've never hit these exceptions, even when starting on a blank environment or after calling flyway clean.
Are these DB2ZSchema.doCreate/doDrop operations called by Flyway?

Note that DB2ZSchema.doExists will return true whenever the configured database exists (this database should have been created by a DBA before starting to deploy schema's)

But if you prefer, we could change it into a log statement like done for SQLite, indeed.