Using EXPLAININPUTSCHEMA

At the beginning of this year, IBM introduced a new bind query parameter, EXPLAININPUTSCHEMA.

Available for DB2 for z/OS 10.0, this parameter is designed to simplify the task of populating EXPLAIN tables that are required for command input. The parameter allows DBAs to create these tables under a separate schema.

Prior to this enhancement, customers had to delete most existing rows in the plan_table while maintaining only those rows needed to be used as input into the BIND QUERY command. Plan_tables normally contain the access path for many different programs, so having to delete this history was a real problem for many who rely on this information.

To take advantage of this enhancement, you need to create a new plan_table using a new schema name. For example, to create a plan_table under schema BINDQ and then copy rows from your existing plan_table to be used as input into BIND QUERY, you'd issue this statement:

BIND QUERY EXPLAININPUTSCHEMA(’BINDQ’)

With this change, the BIND QUERY LOOKUP(YES) must change to recognize the new table. The format to use BINDQ schema is:

BIND QUERY LOOKUP(YES) EXPLAININPUTSCHEMA(‘BINDQ’)

Read more about this parameter in the DB2 10 for z/OS Command Reference.