RETVLCFK in macro DSN6SPRM

Specifies whether the VARCHAR column is to be retrieved from a padded index. Valid values are YES or NO. The data sharing scope of this parameter is GROUP.

If you choose NO, DB2® might retrieve the non-padded value if it needs to return the non-padded value or needs the non-padded value for predicate evaluation.

If you choose YES, better performance might result because of index-only access. However, the data that is retrieved from the index is padded with blanks to the maximum length of the column. This might result in an incorrect output because predicates are applied to the padded value rather then the non-padded value, which can cause different results in some cases. Also, use of the ENCRYPT function can result in an error because of the similar issues with functions being applied to the padded value rather than the non-padded value.

Important: Applications must be able to handle the padding blanks. If your application is sensitive to these blanks, keep the default value of NO or consider using non-padded indexes. You must rebind plans and packages to enable the change.

Recommendation: Accept the default value of NO. Do not set RETVLCFK to YES unless you have tested all of your applications to make sure that none of them have any predicates that are affected by trailing blanks, none of them use the ENCRYPT function or any other function, such as the LENGTH function, that are affected by trailing blanks, and that the application logic is not affected by trailing blanks.

Use NOT PADDED indexes if you want the index to use less space, you want to allow index-only retrieval with variable characters, and you do not want the incompatible retrieval of the full column width or incorrect results.

This subsystem parameter is deprecated in DB2 10.