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:
- The DDL to recreate the database.
- 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:
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.
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.
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:
Now, let’s look at an example of generating the UPDATE DDATABASE CFG commands for the mydb database configuration parameters:
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.
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