I was recently writing some DB2 SQL queries, and was seeing some DB2 error codes returned when attempting to run those queries. Typically I lookup the error codes on the DB2 InfoCenter; however, I recently just discovered a significantly quicker way to determine what the cryptic DB2 error codes are trying to say without using InfoCenter.
To retrieve the short message for an DB2 SQL error code, simply run the following query:
VALUES (SYSPROC.SQLERRM (-206))
Running the above query with the applicable error code will return you a result similar to the following:
1 -------------------------------------------------------------- SQL0206N "" is not valid in the context where it is used.
You can also retrieve a long description of the error message if you want further information, by running the following query:
VALUES (SYSPROC.SQLERRM ('SQL0206', '', '', 'en_US', 0))
Running the above query with the applicable error code will return you a result similar to the following:
SQL0206N "<name>" is not valid in the context where it is used.
Explanation:
This error can occur in the following cases:
* For an INSERT or UPDATE statement, the specified column is not a
column of the table, or view that was specified as the object of the
insert or update.
* For a SELECT or DELETE statement, the specified column is not a
column of any of the tables or views identified in a FROM clause in
the statement.
* For an assignment statement, the reference name does not resolve to
the name of a column or variable.
* For an ORDER BY clause, the specified column is a correlated column
reference in a subselect, which is not allowed.
* For a CREATE TRIGGER, CREATE METHOD, CREATE FUNCTION or CREATE
PROCEDURE statement:
* The reference "<name>" does not resolve to the name of a column,
local variable or transition variable.
* The condition name "<name>" specified in the SIGNAL statement has
not been declared.
* For a CREATE TRIGGER statement:
* A reference is made to a column of the subject table without using
an OLD or NEW correlation name.
* The left hand side of an assignment in the SET transition-variable
statement in the triggered action specifies an old transition
variable where only a new transition variable is supported.
* For a CREATE FUNCTION statement with a PREDICATES clause:
* The RETURN statement of the SQL function references a variable
that is not a parameter or other variable that is in the scope of
the RETURN statement.
* The FILTER USING clause references a variable that is not a
parameter name or an expression name in the WHEN clause.
* The search target in an index exploitation rule does not match
some parameter name of the function that is being created.
* A search argument in an index exploitation rule does not match
either an expression name in the EXPRESSION AS clause or a
parameter name of the function being created.
* For a CREATE INDEX EXTENSION statement, the RANGE THROUGH clause or
the FILTER USING clause references a variable that is not a parameter
name that can be used in the clause.
The statement cannot be processed.
User response:
Verify that the names are specified correctly in the SQL statement. For
a SELECT statement, ensure that all the required tables are named in the
FROM clause. For a subselect in an ORDER BY clause, ensure that there
are no correlated column references. If a correlation name is used for a
table, verify that subsequent references use the correlation name and
not the table name.
For a CREATE TRIGGER statement, ensure that only new transition
variables are specified on the left hand side of assignments in the SET
transition-variable statement and that any reference to columns of the
subject table have a correlation name specified.
For a fullselect embedded in XQuery using the db2-fn:sqlquery function,
a reference within the fullselect must be one of the following: a column
in the context of the fullselect, a global variable, or a parameter
passed to the new SQL context using an additional argument of the
db2-fn:sqlquery function.
sqlcode: -206
sqlstate: 42703
