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.

You may also like...

2 Responses

  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. Christer Engde says:

    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.

Leave a Reply

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