Skip to content
View All / Long awaited gems in db2look!

Long awaited gems in db2look!

Iqbal Goralwalla

Iqbal Goralwalla

February 4, 2015

db2look is a great utility which is commonly used by DB2 DBAs to extracts the DDL statements from a source database that can then be used recreate the database objects in a target database. Now, I have always wished for two features in db2look that would make it a “complete” utility. The ability to generate:

  1. The DDL to recreate the database.
  2. Update statements for database configuration  parameters.

Whenever I have used db2look to recreate a test database from a production database, I’m sure I have been heard muttering “why do I have to figure out how the database was created?” And after the database objects have been created, the muttering would be “and now I have to manually update the database configuration parameters.” I can hear resonance as you read this.

Well, good things come to those who are patient! In DB2 10.5 FP4 and DB2 10.1, FP4, db2look has new options that cater for my long awaited wishes:

  • -createdb generates the CREATE DATABASE command that was used to create the source database.
  • -printdbcfg generates UPDATE DDATABASE CFG commands for the database configuration parameters.

Let’s look at an example of generating the CREATE DATABASE command for my test database, mydb:

db2look Image 1

The contents of the file mydb_create.sql are given below and show that the database was created with automatic storage with a single storage path /home/db2i1054.

db2look image 2

Now let’s see what happens if we had altered the database at some point by adding a storage path. We expect this change to be captured by db2look.

db2look image 3

A snippet of the contents of the file mydb_create.sql is given below and indeed shows the additional storage path that was added after the database was created:

db2look image 4

Now, let’s look at an example of generating the UPDATE DDATABASE CFG commands for the mydb database configuration parameters:

db2look image 5

A snippet of the contents of the file mydb_dbcfg.sql is given below and shows the UPDATE DB CFG commands. Nice. As seen in the comments below, for the parameters that support the AUTOMATIC value, you may want to add AUTOMATIC at the end of the generated UPDATE DB CFG command.

db2look image 6

Note that UPDATE DB CFG statements are not generated for the following parameters:

  • PAGE_AGE_TRGT_MCR
  • DFT_TABLE_ORG
  • STRING_UNITS
  • NCHAR_MAPPING
  • EXTENDED_ROW_SZ

CONNECT_PROC