Executing a QUICK EXPLAIN and receives a SQL-206 message
SQL error in PLAN PPAMxxxx in DBRM [email protected] statement 1,149
SSID: ssid DB2 VERSION: V12R1M0
DSNT408I SQLCODE = -206, ERROR: FUNCTION_LVL IS NOT VALID IN THE
CONTEXT WHERE IT IS USED
DSNT418I SQLSTATE = 42703 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXORSO SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -100 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFF9C' X'00000000' X'00000000'
X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC
Release : 20.0
Component : Plan Analyzer for DB2 for z/OS
RC/Query for DB2 for z/OS
The user is using an existing DSN_STATEMNT_TABLE table that is pre-Db2 12 and did not include
the new FUNCTION_LVL column in Db2 12.
The -206 on column FUNCTION_LVL of the DSN_STATEMNT_TABLE table indicates that either:
1. The user is using an existing DSN_STATEMNT_TABLE table that is pre-Db2 12 (FUNCTION_LVL is a new DSN_STATEMNT_TABLE TABLE column in Db2 12)
2. The DSN_STATEMNT_TABLE is being AUTO CREATED by Plan Analyzer and you do not have the PTF's installed for the support of this column.
Check if the user is using an existing DSN_STATEMNT_TABLE table and does that table contain this FUNCTION_LVL column.
If there is an existing DSN_STATEMNT_TABLE that is pre-Db2 12 then it will need to be recreated according to Db2 12 requirements.
This can be done in Plan Analyzer using menu item : CT Create Explain/Input Tables.
Discovered that the DSN_STATEMNT_TABLE table was incorrect and after adding the FUNCTION_LVL column the Quick Explain completed successfully.