Monday, June 2, 2014

Improved db2look in DB2 to mimic database environments

Some of the advertised improvements in the recent DB2 10.1 Fixpack 4 apply to the long existing tool db2look. Two new options have been added: "-createdb" and "-printdbcfg". The first is used to generates the CREATE DATABASE command and its options, the second to generate statements to reapply the database configuration.

As it is new, I wanted to test it myself. First I created a database "lt" (as in "Look Test") with non-standard options. Next was to invoke db2look:

db2look -d lt -createdb -printdbcfg -o lt.out
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: HLOESER
-- Output is sent to file: lt.out
-- Binding package automatically ...
-- Bind is successful
-- Binding package automatically ...
-- Bind is successful


The generated output file starts with the usual environment and version information, then follows the section to recreate the database:

--------------------------------------------------------
-- Generate CREATE DATABASE command
--------------------------------------------------------

CREATE DATABASE LT
        AUTOMATIC STORAGE NO
        USING CODESET ISO8859-1 TERRITORY de
        COLLATE USING IDENTITY
        PAGESIZE 8192
        DFT_EXTENT_SZ 32


...

;

As you can see, I didn't use automatic storage, used a local, non-Unicode codepage and German territory, an identity collation and 8 kByte pages. Thereafter follow the parameters for the catalog, temporary, and user  tablespaces (not shown). After the database creation is completed, the next is the CONNECT statement:

CONNECT TO LT;



Once the database connection is established, another new section starts. It reapplies the database configuration:

--------------------------------------------------------
-- Generate UPDATE DB CFG commands
--------------------------------------------------------

-- The db2look command generates the UPDATE DB CFG statements
-- to replicate the database configuration parameters based on
-- the current values in the source database.
-- For the configuration parameters which support AUTOMATIC,
-- you need to add AUTOMATIC to the end
-- if you want the DB2 database to automatically adjust them.

--UPDATE DB CFG FOR LT USING ALT_COLLATE      ;

UPDATE DB CFG FOR LT USING STMT_CONC          OFF ;

UPDATE DB CFG FOR LT USING DISCOVER_DB        ENABLE ;

UPDATE DB CFG FOR LT USING DFT_QUERYOPT       5 ;
UPDATE DB CFG FOR LT USING DFT_DEGREE         1 ;

...



Right now the enhancements are only available in the just recently released fixpack of DB2 10.1. As with other improvements, I would expect it to be available for the newer DB2 10.5 release soon.

No comments:

LinkWithin

Related Posts with Thumbnails