IBM DB2 manual 221, Example 5-6 Check and set integrity on our sample database

These are the most common areas requiring your attention:

￿Remove obsolete buffer pools and table spaces:

When a database is created using the CREATE DATABASE command, a set of default buffer pools and table spaces are created. If these default objects are obsolete, you should explicitly add commands to remove them.

￿DDL statements for stored procedures and functions:

The DDL statements for stored procedures and functions are out of place and must be moved after the DDL statements for the objects used by the stored procedures and functions, which usually are tables and views. Be aware that DDL statements for stored procedures and functions might also require some rearrangement to align with the dependences among them.

￿DDL statements for aliases:

These DDL statements are located before the DDL statements for the schemas and tables referenced in the aliases. This will only lead to a warning, but any non-existing schema referenced in the alias will be created automatically. This has two problems. First of all, the schema might be created with the wrong authentication. Secondly, it will lead to an error when the schema is created in a later DDL statement.

￿DDL statements for schemas:

This DDL statement is located at the end of the output. It must be moved to the top before the DDLs for alias or tables. This will ensure that the schema will not be created implicitly.

￿Object dependencies:

db2look does not preserve the object dependencies, without modification on the generated DDLs, the table created might be left in integrity pending mode, which mean that the tables are not accessible. An example of this case is the summary table in our sample database. In Example 5-6we show how to check if any tables are in integrity pending mode by issuing a query against the system catalog and how to correct the problem.

Example 5-6 Check and set integrity on our sample database

// Issue a query against the system catalog

SELECT tabname FROM syscat.tables WHERE access_mode = ‘N’

//The output looks like this

TABNAME

-----------------------

ADEFUSR

1 record(s) selected.

Chapter 5. Deploying pre-configured databases 221