Quick Tip: db2look and Triggers

Posted by

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 is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, and is now pivoting to focus on learning MySQL. Ember shares both posts about her core skill set and her journey learning MySQL. Ember lives in Denver and work from home

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 to Christer Engde Cancel 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.