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 is always curious and thrives on change. Working in IT provides a lot of that change, but after 17 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 for XTIVIA, leading a team of Db2 DBAs.

2 comments

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: