Fork me on GitHub

DB2 Notes

DB Type acronym for configurations: DB2

Specifying tablespace for user tables and Obevo audit tables

Note the following if you follow the convention of explicitly defining the tablespace for tables in your SQL commands, e.g.

CREATE TABLE mytable ( ... ) IN mytablespace
  • To specify the tablespace for user tables, simply define it in your SQL. You can parameterize that value across environments as needed using the tokenization feature
  • Note that Obevo can automatically remove such clauses for translations to unit test databases; for more information, see here
  • To specify the tablespapce for the Obevo-managed tables (e.g. ARTIFACTDEPLOYMENT), specify the defaultTablespace attribute in your environment configs, e.g. <dbEnvironment name="dev1" defaultTablespace="mydevtablespace" ...>
  • A token ${defaultTablespace} will be available to use if you leverage this attribute, e.g. CREATE TABLE mytable ( ... ) IN ${defaultTablespace}

DB2 Reorg Detection and Support

Obevo supports the handling of reorgs in 2 places:

  • If any SQL is executed and returns the DB2 reorg-pending error (-668), Obevo will detect this and automatically fire off the reorg statement (you can disable the automatic firing of the reorg in system-config.xml; see section below)
  • At the end of a deployment, Obevo will check if any tables are pending reorg using the query below, and if any are detected, it will show the results to you and fire the reorg (unless you’ve disabled the automatic firing of the reorg, in which case you’ll just see the list of affected tables)
SELECT TABSCHEMA, TABNAME, NUM_REORG_REC_ALTERS, REORG_PENDING
FROM SYSIBMADM.ADMINTABINFO
WHERE TABSCHEMA IN ('schema1','schema2') AND REORG_PENDING = 'Y'

Both use cases need to be supported as it can be possible to modify a table that is pending reorg without incurring the error (the error is only incurred if you try to use the data in it), and so you need a check after the fact.

DB2 Invalid Object Detection and Recompilation

Existing objects can become invalid if a dependent object is modified in certain ways; DB2 terms these as invalid objects. Obevo can detect if such objects exist after your deployment and recompile them if possible.

This behavioral is configurable - see section below.

Configuring the DB2 post-deploy and reorg behaviors

The following parameters are available to configure:

Property Default Value Description
reorgCheckEnabled true If true, will execute the query to find all tables that require reorg. This property does not determine whether the reorg is executed
autoReorgEnabled true If true, will execute a reorg automatically if detected during deployment or the post-deployment step. If reorgCheckEnabled==false, then no reorgs will run during the post-deploy step, regardless of the autoReorgEnabled value
invalidObjectCheckEnabled true If true, will execute the invalid-object check and recompilation in the post-deployment step.

You have the choice to configure the behaviors above either at the dbSystemConfig or dbEnvironment levels, e.g.

<dbSystemConfig autoReorgEnabled="false" ...>   <!-- Set the default value here -->
    <!-- ... -->
    <environments>
        <dbEnvironment name="env1" ... />  <!-- inherits the default value from the top level -->
        <dbEnvironment name="env2" autoReorgEnabled="true" ... />  <!-- overrides the default value -->

“Invalid operation: Lob is closed” Exception

If you receive this exception, you should add the progressiveStreaming=false value to your JDBC url, per below. More details here

jdbc:db2://localhost:50000/dbdeploy:progressiveStreaming=false;

We have only seen this in the DB2 Express environment so far. If you run into this in your own standard environment, please raise an issue on Github.

DB2 Error Message Handling

Error messages in DB2 will appear somewhat as follows:

Caused by: com.ibm.db2.jcc.b.SqlException: DB2 SQL error: SQLCODE: -750, SQLSTATE: 42986, SQLERRMC: null

If you see such an exception, first search via the SQLCODE for a hint of the problem.

For the example above, do a search like “db2 error code 750” to find the issue. This usually leads to the DB2 error code reference page.

If the error message does not help you, then reach to your database administrators or contact the Obevo team.