Help

 

DSNXRSFN

DSNXRSFN is part of the Data Manager component of DB2 and is involved in run-time processing of SELECT statement functions.

Hints

This module appears when the built-in functions SUM, AVG, MIN, or MAX are used.

The point at which column functions are evaluated is shown in the PLAN_TABLE column COLUMN_FN_EVAL.

  • S indicates that the column function is evaluated during a sort.

  • R indicates that the function is evaluated while the data is being read.

  • A blank indicates that the function is evaluated after retrieval and sort.

  • R indicates a more efficient process than S. S is preferable to a blank.

Columns that are frequently accessed through built-in functions are good candidates for indexing, since the presence of an index will allow DB2 to evaluate a function without access to the table's data pages and the use of a clustering index may limit the access to a single index page as indicated by a plan table ACCESSTYPE of "I1".

Including a column function in a statement that invokes a sort is likely to result in evaluation after retrieval and sort, unless an index is used to avoid the sort. See the module help for DSNITLPE for recommendations on avoiding sorts.

Avoid the use of COUNT (*) to determine the existence of at least one row when you do not need the actual number of qualifying rows. Consider recoding this query as a singleton SELECT and test the SQLCODE. If the SQLCODE is 0, a single row qualifies. If the SQLCODE is -811, more than one row qualifies.

If the query is a join, all set functions must be on the last table joined for the function to be evaluated at data retrieval.

Reference Sources

IBM Db2 for OS/390, Messages and Codes, GC26-8979

IBM Db2 for OS/390, Application Programming and SQL Guide, SC26-8958

IBM Db2 for OS/390, Administration Guide, SC26-8957