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:

db2look -d dbname -e -a -l -o db2look.ddl -td "#"

The resulting file can then be executed like this:

db2 -td# -vf db2look.ddl >db2look.ddl.out

Notice the -td syntax is different in db2look syntax and when executing the resulting file. But it works, and deals with triggers quite nicely.

The syntax above generates ddl for all objects in the database, but in this case, I had to manually pull out the syntax I needed. Mostly because previous versions taught me not to trust db2look’s filtering options.

Ember Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 557

3 Comments

  1. Thanks for the tip Ember! Very helpful with extracting DDLs with functions, stored procedures and the like. Have used this here. Not sure if anyone uses federation or not, but I’ve found there is a particular order that I need to use those parms in. In my case I use the following:

    db2look -d -a -l -wrapper -i -w -e -o -td “@”

  2. Any way to sort the output to get it more deterministic between runs? I am using it to compare different instances of the same database.

  3. Quite handy. Saved me from the clutches of the offshore IBM support. (I dropped off enough for a coffee or two to thank you as well.)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.