MAINFRAME > Tips & Techniques > Systems Management

Tools Extract DB2 Statistics and DDL From the DB2 Catalog

IBM offers two free catalog management tools for database administrators

IBM offers two free catalog management tools for database administrators

Database administrators (DBAs) spend much of their time creating and maintaining DB2* objects like tables, views and indexes. As new projects come up, the DBA usually builds a new set of tables to allow the new project to work without interfering with existing development. The challenge is coming up with the correct Data Definition Language (DDL) to build the DB2 objects needed for the project.

One low-cost technique is to maintain files with all of the DDL necessary to build a complete copy of your production environment in a test system. As DB2 objects are added or modified, these changes are maintained in the file. However, these files may become obsolete if they haven’t been maintained correctly. The only way to know you have all of the DDL needed to replicate an environment is to build the DDL based on the DB2 catalog.

There are some very good catalog-management tools on the market that can read the catalog and build the needed DDL to replicate any given DB2 object. These tools can also compare two different copies of an object and generate the needed DDL to synchronize them. These tools are expensive and are usually only found in larger companies that can justify the cost. The good news is IBM provides a few free catalog-management tools. If you’re supporting DB2 for Linux*, UNIX*, Windows* (LUW), then you’ll want to use the DB2LOOK tool; those using z/OS* will want to use the new Optimization Service Center (OSC).

Let’s briefly review what both tools can do for you.


DB2LOOK has been delivered with DB2 for LUW for years. It extracts DDL statements used to reproduce database objects of a production database for testing purposes. You can also copy over the production statistics, which are reused by DB2 to determine an access path to the data. See the “DB2 Command Reference Guide” for more information on the syntax and use of DB2LOOK command.

Some DBAs have tried to use the DB2LOOK command to extract DDL from DB2 on z/OS. You can use DB2LOOK to extract DDL for tables, indexes and views from DB2 on z/OS. But, DB2LOOK won’t extract information about the Database or Tablespace, because these types of objects don’t exist with DB2 for LUW. DB2LOOK can generate many DB2 objects, including:

  • tables
  • views
  • automatic summary tables (AST)
  • aliases
  • indexes
  • triggers
  • sequences
  • user-defined distinct types
  • primary key, referential integrity and check constraints
  • user-defined structured types, function, methods and transforms
  • wrappers
  • servers
  • user mappings
  • nicknames
  • type mappings
  • function templates and mappings
  • index specifications and stored procedures.
With OSC, the effort required to gather the DDL needed to replicate a set of DB2 tables is just a few clicks away.

Troy L. Coleman is an IBM-certified Certified database administrator, specializing in DB2 9 for z/OS and Linux, UNIX and Windows. Troy can be reached at

comments powered by Disqus



2019 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Optimal Service Delivery

Overcome eight key challenges and reduce costs


An Accurate Benchmark Is Important

Advice for the Lazy Administrator

Steps you can take to avoid late nights and system frights.

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
Mainframe News Sign Up Today! Past News Letters