The following suggestions introduce the techniques of using the catalog statistics to influence the choice of access methods made by the database manager. For more information see Tuning Queries with Several Tables.
If you have DBA authority, you are allowed to update the statistical values stored in the catalog tables. See Table 8 for a description of the columns in question. This ability lets you create a model of your production system on a smaller test system. You can then use the EXPLAIN statement to determine how your production tables would be accessed for some set of SQL statements. In the same way, you can model a future production system by making assumptions about the size and nature of the database structure.
To create a model of your production system, the same database structure must be in place on your test system. This means the same dbspaces, tables, indexes, referential constraints, and so on must be defined on the test system. You can then modify the statistics in your test system catalog tables to be identical to those in the production system. The optimizer will choose the same paths to access your tables on the test system as it would on the production system.
After you have a model of the production system established, you can discover how the optimizer will react to changes in the database structure, such as adding new indexes by updating the catalog table statistics. Using EXPLAIN will tell you whether (and how) a new index would be used for a particular SQL statement, and how it impacts the expected costs of executing that statement. With this information, you can decide whether you should add the index to the production system.
Similarly, you can now use your test system to discover how rewriting an SQL statement into an alternate form affects the path chosen and the estimated cost for executing the statement.
If you supply the COLCOUNT value for an index column without running UPDATE STATISTICS, you should also supply HIGH2KEY and LOW2KEY for the index. If the data is not uniformly distributed you should also supply the additional values in the SYSCOLSTATS table. These columns are defined as CHAR, so an UPDATE statement must provide a character or hexadecimal value. Although the columns have a length of 12, only 8 bytes of information should be stored. Entering a character value is quite straightforward--SET LOW2KEY = 'ALAS', for instance. But to enter a numeric, date, or time value you must use the hexadecimal value of the DB2 Server for VSE & VM internal format. To determine the proper hexadecimal data to use for these data types, create a table with columns of the required types and insert the values you want to use for HIGH2KEY and LOW2KEY into it. Then display the internal format of these values by using the HEX column function in the select list. For example:
SELECT HEX(column_name) FROM ...
Be sure to allow for a NULL indicator in keys that allow NULLS by making the first character '00'X. If values being set are less than 8 bytes long (including the '00'X NULL indicator byte) pad them on the right with '00'X bytes.
If the NPAGES column of SYSTEM.SYSDBSPACES is updated (to allow testing of the access plan generation) and then an ACQUIRE DBSPACE command attempts to acquire this dbspace, an error message may result. Updating NPAGES does not actually change the size of a dbspace, it changes the information supplied to the optimizer used in access plan generation.