DB2 Basics: db2look

I was a bit shocked when I searched my own blog for entries on db2look and came up with nothing. While it’s not a complicated tool, db2look is an essential tool.

What db2look Can Do

db2look is a tool provided with DB2. db2look’s primary purposes are:

  • Mimic Statistics – To generate the SQL to update statistics with the idea that they will be run in one database to match statistics in another database.
  • Generate SQL – To generate all or part of the SQL required to re-create a database.

The tool is generally referred to as a mimicking tool – generating the SQL to mimic something from one database in another database. It can be used for a single table, a schema, or the whole database. There are a wide range of options to specify exactly how deep you want to go with the mimicking.

IBM DB2 Info Center page on db2look

Authorization Required

For much of what db2look does, you’ll just need select on the system catalog tables, which in many cases may be granted to PUBLIC by default. For some options, you may need one of the following:

  • EXECUTE privilege on the ADMIN_GET_STORAGE_PATHS table function

You’ll also need connect authorization on the database, as db2look establishes its own database connection. If no one has ever run db2look on that databse before, you may have to also bind the db2look packages (which db2look may try to do automatically), and therefore may need bindadd permission.

db2look Syntax

The db2look syntax is not difficult. You can run db2look either for a local database or over a remote connection – though it doesn’t play nice across versions sometimes if the local and remote versions are significantly different. You always have to provide db2look with a database name using the -d option. If connecting to a remote database, you will also have to provide a user name and password.

In DB2 10.1 and DB2 10.5 a couple of new options have been added – the -createdb option will also generate the create database statement needed to create a new, identical database. The -printdbcfg option will print out the database configuration statements as well. These were added to 10.1 in a fixpack, so are only available if you either created the database on a more recent fixpack, or if you issued the db2updv10 command after a fixpack or version upgrade (and you should have).

The -e option is what you want if you’re extracting SQL to re-create objects or the database. You can optionally specify tables or schemas to limit the output. You may want to use the -x or -xd options to also generate grant statements for the objects you’re extracting the SQL for. If your intention is to mimick the whole database, the -l option is useful to also generate the SQL to create things like tablespaces and bufferpools. If part of what you’re extracting includes triggers or SQL stored procedures, you may want to use the -td <delimiter> option to specify a non-standard statement delimiter (something other than ;).

The -m option is what you want to generate the update statements to mimic statistics.

The -o option is used to specify an output file name.

There are certainly other options you may want to consider using in specific situations.

When to use db2look

There are a number of scenarios where db2look is useful.

  • Generating the syntax to create a single table in one environment that already exists in another environment.
  • Generating the syntax to compare an object in one environment to an object in another environment.
  • As part of a disaster recovery plan. If you regularly generate a full db2look, you’re prepared to re-create objects that may have been incorrectly dropped or changed, or in a worst-case scenario to create an empty version of your database.
  • Generating the syntax from a production environment that can be used to create a non-production or development environment.
  • Generating the syntax to move all objects in a schema from one database to another (object definition only)
  • Generating the syntax to re-create objects as a backout plan for object changes
  • Along with db2move, db2look can be used to move a DB2 database from one environment to another when a backup/restore is not possible (such as between DB2 versions that are more than 2 versions apart or between differing OS platforms)
  • Generating the syntax to recreate everything in the database, but then altering the output file before it is executed to make significant structure changes (though admin_move_table is now often used for these within the same database)

db2look Caveats and Warnings

db2look does not extract the data within the database in any way. You cannot use it as a replacement for backup/restore. Also be aware that db2look output generated on some newer versions may not directly work on older versions – clauses like organize by row work on DB2 10.5, but not on any older versions.

In some older versions, the options to specify a table or schema were not very reliable. This may have been as far back as DB2 version 7, but it is easy to make a mistake and not get the SQL you thought you were getting. Always review the output file before relying on it – especially in situations where you are relying on having the data.

db2look does not have a significant performance impact, especially considering the normally infrequent basis when it is used. It does not affect database availability to generate the file, but if you run the generated file somewhere, that can have drastic impacts on database structure – with the right set of options, the file output will drop objects.

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: 554


  1. db2look does also not generate the correct ddl for incremental refresh mqt’s

    the generated ddl when executed does not allow for incremental refreshes of the mqt’s

  2. Is there an “equivalent” of db2look that shows you the syntax for database and instance creation by which existing databases and instances were created?

    • There are options on db2look for most of the database components using the -createdb, -printdbcfg, and -l options. I don’t think there’s the same for instances, but there are not so many options on instance creation.

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.