DB2 Upgrade Detail: Upgrading Event Monitor Tables

I am a huge fan of always running a locking event monitor, and also using other event monitors when appropriate. This means that most databases I support have at least one event monitor, whether it is active or not. With some version changes, the structure of tables that event monitors write to are changed. This means that as you upgrade DB2, you must also run a command to upgrade your event monitors.

Continue reading »

Using DB2’s ADMIN_MOVE_TABLE to Move Tables from One Tablespace to Another

What is ADMIN_MOVE_TABLE

ADMIN_MOVE_TABLE is an administrative stored procedure introduced in DB2 9.7. The intent is to provide a tool that can be used to perform an online move of a table, while transactions are still occurring against the table. Moves can be used to change what tablespace a table is in, convert a table to MDC, change the table name, perform several changes that would normally require reorgs, reduce the size of a column, and perform other changes. In the early fixpacks, it was not much more than any DB2 DBA could have written themselves. It uses triggers against the source table to track changes in a staging table while moving the data to a new table. Once the new table is populated, then the data tracked by the triggers is replayed against the target table and a table-level exclusive lock is obtained on the source table for a short period to make the switch (rename).

Continue reading »

Quick Tip: db2look and Triggers

Just a quick tip today. I’ve been dealing quite a lot with triggers lately, and found the -td option on db2look very useful. I hadn’t had cause to use it before, but it has now become part of my default syntax. In my case, I was moving 963 triggers from a development system to production, and db2look without the -td option doesn’t work all that well for triggers. Now I use this syntax:

Continue reading »

SQL1042C on Use of A System Table Function

This post is specific to DB2 for UNIX or Linux. SQL1042C can have a number of causes and be received as an error from a number of different commands. The IBM Support Portal does a good job of coming up with the issues and solutions. And I have it in the back of my head that when I get this error, I need a db2iupdt – which is non trivial since it requires the instance to be down and root access to run.

Continue reading »

Altering database objects in Commerce databases

Like many vended applications, Commerce creates the whole schema of it’s database and this is largely something you don’t mess with. There are, however, some changes that developers or administrators may make. My suggestion is that if you don’t know if a particular modification is supported by Commerce, you call IBM support and ask them. Then if you end up having issues you can reference the PMR/conversation to ensure they don’t blame you for the problems.

Continue reading »