Question & Answer
Question
Answer
[How to create Explain tables in Db2 9.1 or earlier]
$ db2 connect to [database]
$ db2 -tf ~/sqllib/misc/EXPLAIN.DDL
[Windows]
> db2 connect to [database]
> db2 -tf "%DB2PATH%\MISC\EXPLAIN.DDL"
[How to remove Explain tables in Db2 9.1 or earlier]
[How to create Explain tables in Db2 9.5 or later]
In Db2 9.5 or later, you can still create Explain tables as same ways as 9.1, but another method is available. Db2 produced SYSINSTALLOBJECTS proceure to create Explain tables.
The 3rd argument of the SYSINTALLOBJECTS procedure specifies the table space where the Explain table is created. The 4th argument specifies the schema of the Explain tables. If NULL or null string was specified, Explain tables is created in SYSTOOLSPACE table space with SYSTOOLS schema. If SYSTOOLSPACE does not exist, it's created automatically.
In the following example, Explain tables is created in SYSTOOLSPACE table space with the schema of connecting user ID.
$ db2 connect to [database]
$ db2 "call SYSPROC.SYSINSTALLOBJECTS( 'EXPLAIN', 'C' , '', CURRENT USER )"
You can remove Explain tables by using SYSINSTALLOBJECTS procedure. Specify the schema of Explain tables to be dropped in 4th parameter. If NULL or null string was specified, Explain tables with SYSTOOLS schema is removed.
In the following example, Explain tables, which have schema of connecting user ID, is removed.
$ db2 connect to [database]
$ db2 "call SYSPROC.SYSINSTALLOBJECTS( 'EXPLAIN', 'D', '', CURRENT USER )"
-
How to maintain contents of Explain tables
If you want to delete unnecessary rows in Explain tables, you delete rows in EXPLAIN_INSTANCE table. Between Explain tables, referential integrity with CASCADE option is defined. All the related rows in other Explain tables are automatically deleted.
Therefore, if you want to delete all data in Explain tables, delete all rows from EXPLAIN_INSTANCE table. In the following example, you can delete all data in Explain tables for current schema.$ db2 connect to [database] $ db2 delete from EXPLAIN_INSTANCE
NOTE: You might want to specify table schema to delete Explain information for specific Explain schema.
If there are too many rows in Explain tables, the delete statement can fail with "transaction log for the database is full" error (SQL0964C). In this case, refer the technote [Db2] How to solve transaction log for the databases is full (SQL0964C) -
Upgrading Explain tables
The configuration of Explain table can vary depending on the version of Db2.
If you want to keep the contents of Explain tables taken in older version, you should run db2exmig command to migrate existing Explain table configuration.
Upgrading explain tables
Explain tables and the organization of explain information
SYSINSTALLOBJECTS procedure
Was this topic helpful?
Document Information
Modified date:
07 April 2023
UID
ibm11074771