Blog
DB2utor

Categories

July 19, 2011

SQL Optimization Hints: What's New

Last week I gave a quick introduction of DB2 SQL optimization hints and noted that DB2 10 brings some key enhancements in this area. Now I'll get into the specifics of what's new.

Prior to DB2 10, the QUERYNO clause was used to associate a query to the related hints. However, because programming changes could potentially alter QUERYNO, errors often occurred. To deal with this problem, programmers began using QUERYNO on the SQL statement. Assigning a fixed value eliminated the possibility of numbers changing due to coding changes. This work-around is effective provided you own the code, but in the case of vendor products, it's generally not an option.

But in the latest release, DB2 addresses the QUERYNO issue by providing statement-level optimization hints -- aka instance-based statement hints. With this method, hints are enforced based on the statement text for the entire DB2 subsystem. This is why they're also referred to as "instance-based" hints. Statement-level hints are stored in the DB2 catalog in a set of tables known as the access path repository.

Use BIND QUERY  LOOKUP(NO) to populate the access path repository. This command reads the statement text, default schema and a set of bind options from every row of DSN_USERQUERY_TABLE, as well as system-level access path hint information from correlated PLAN_TABLE rows. BIND QUERY then inserts the pertinent data into the SYSIBM.SYSQUERY, SYSIBM.SYSQUERYPLAN and SYSIBM.SYSQUERYOPTS catalog tables.

When using statement-level hints, follow these steps:

1)     BIND the package with EXPLAIN(YES) to populate a user PLAN_TABLE with system-level access path hints.

2)     Populate user table DSN_USERQUERY_TABLE with the query text. Use install member SDSNSAMP(DSNTESH) to create the access path repository tables. The best way to populate this table with the statement is to use an INSERT with a select against the SYSIBM.SYSPACKSTMT catalog table.

In this sample, insert statement 264 is used for package name rowset found in collection coltr05_collid.

INSERT INTO DSN_USERQUERY_TABLE          

( QUERYNO, SCHEMA, HINT_SCOPE, QUERY_TEXT, USERFILTER, OTHER_OPTIONS 

 , COLLECTION, PACKAGE, VERSION , REOPT, STARJOIN, MAX_PAR_DEGREE       

 , DEF_CURR_DEGREE, SJTABLES, OTHER_PARMS

)                                        

SELECT PS.STMTNO                         

     , 'COLTR05' , 1                     

     , PS.STATEMENT, '',''               

     , PS.COLLID, PS.NAME, PS.VERSION    

     , '', '', -1                        

     , '', -1, ''                        

FROM SYSIBM.SYSPACKSTMT PS               

WHERE PS.COLLID = 'COLTR05_COLLID'       

  AND PS.NAME = 'ROWSET'                 

  AND PS.VERSION = 'MYVERSION_1'         

  AND PS.STMTNO = 264;

 

3)    Run a new BIND QUERY LOOKUP(NO) command. This will insert hints into the DSN_USERQUERY_TABLE repository based on the statements in the PLAN_TABLE. 

4)     Use BIND QUERY LOOKUP(YES) to verify you have a row in the access repository.  Insert the SQL statement into the DSN_USERQUERY_TABLE and after  executing BIND QUERY LOOKUP(YES) the QUERYID column will be set.

5)     The FREE_QUERY command is used to remove the hint from the access path repository.

As with optimization hints in general, once a statement-level optimization hint is created, DB2 validates and tries to enforce the hints. Hints for static SQL statements are validated and applied when you rebind the package that contains the statements. Hints for dynamic SQL statements are validated and enforced when the statements are prepared.

Once the access-path repository is populated, you should remove the statement from DSN_USERQUERY_TABLE to avoid replacing hints on subsequent BIND QUERY commands. 

Hints are created or replaced for every row in DSN_USERQUERY_TABLE when BIND_QUERY is issued. Consequently, changes to PLAN_TABLE data might result in the replacement of existing hints of either type if old rows remain in the DSN_USERQUERY_TABLE and BIND_QUERY is issued.

 

*updated 7/20/11