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.

Lead Database Administrator
Ember is always curious and thrives on change. Working in IT provides a lot of that change, but after 18 years developing a top-level expertise on Db2 for mid-range servers and more than 7 years blogging about it, Ember is hungry for new challenges and looks to expand her skill set to the Data Engineering role for Data Science. With in-depth SQL and RDBMS knowledge, Ember shares both posts about her core skill set and her journey into Data Science. 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

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.