DB2 SQL Errors Codes and Error Messages and Warnings from Error -407 to -409

SQLServerF1

Error: DB2 SQL Error: SQLCODE=-407, SQLSTATE=23502, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER=
AN UPDATE, INSERT, OR SET VALUE
IS NULL, BUT THE OBJECT COLUMN
column-name CANNOT CONTAIN NULL
VALUES
Explanation: One of the following conditions
occurred:
v A null insert or update value was specified for a
column defined as NOT NULL.
v A SET transition variable statement specified a NULL
value for column defined as NOT NULL without the
WITH DEFAULT clause in the table definition.
v The update or insert value was DEFAULT, but the
object column was declared as NOT NULL without
WITH DEFAULT in the table definition.
Consequently:
– A default value of NULL cannot be inserted into
that column.
– An update cannot set default values of NULL in
that column.
– A SET transition variable statement in a trigger
cannot set default values of NULL in that column.
v The column name list for the INSERT statement
omits a column declared NOT NULL and without
WITH DEFAULT in the table definition.
v The view for the INSERT statement omits a column
declared NOT NULL and without WITH DEFAULT
in the base table definition.
v A null insert value was specified for a ROWID
column.
System action: The statement cannot be executed. The
‘column-name’ might be returned in the SQLCA,
depending on the syntax of the SQL statement in which
the error was detected.
Programmer response: Examine the definition of the
object table to determine which columns of the table
have the NOT NULL attribute or have a type of
ROWID, and correct the SQL statement accordingly.
SQLSTATE: 23502

Error: DB2 SQL Error: SQLCODE=-408, SQLSTATE=42821, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER=
THE VALUE IS NOT COMPATIBLE
WITH THE DATA TYPE OF ITS
TARGET. TARGET NAME IS name
Explanation: A statement failed required assignment
rule checking.
name The name of the target of the assignment.
The data type that is to be assigned is incompatible
with the declared data type of the assignment target.
Both data types must be:
v Numeric
v Character (see restriction)
v Graphic (see restriction)
v Dates or character (see restriction)
v Times or character (see restriction)
v Timestamps or character (see restriction)
v Row IDs
v The same distinct types
v XML
v Result set locator
v Table locator
Restrictions:
v Dates, times, and timestamps cannot be assigned to a
character column that has a field procedure.
v Character and graphic data types are compatible
when using Unicode.
System action: The statement cannot be processed.
Programmer response: Examine the current definition
for the object table, procedure, user-defined function,
host variable, or SQL variable, and ensure that the
variable, constant, or result of an expression that is
assigned to the object has the proper data type. In some
cases, you can convert the value to the proper data
type by using a function such as CHAR or DECIMAL.
If the target is a result set locator, the application might
have tried to explicitly assign an invalid locator value
to a result set locator. Use the ASSOCIATE LOCATORS
statement to get a valid locator value for a result set
into a locator variable. Then, reference the result set
locator in an ALLOCATE CURSOR statement.
SQLSTATE: 42821

Error: DB2 SQL Error: SQLCODE=-409, SQLSTATE=42607, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER=
INVALID OPERAND OF A COUNT
FUNCTION
Explanation: The operand of the COUNT or
COUNT_BIG function in the statement violates SQL
syntax. A common error is a column name or other
expression without DISTINCT.
System action: The statement cannot be executed.
Programmer response: Correct the syntax and
resubmit the statement. Refer to Chapter 3 of SQL
Reference for information about the proper form for the
operands of a COUNT or COUNT_BIG function.
SQLSTATE: 42607

Above are list of DB2 SQL Errors and Warnings from Error -407 to -409 received while performing certain operation against DB2 Database or related products.

SQLCODE – Regardless of whether the application program provides an SQLCA or a stand-alone variable, SQLCODE is set by DB2 after each SQL statement is
executed. DB2 conforms to the ISO/ANSI SQL standard as follows:
If SQLCODE = 0, execution was successful.
If SQLCODE > 0, execution was successful with a warning.
If SQLCODE < 0, execution was not successful.
SQLCODE = 100, “no data” was found. For example, a FETCH statement returned no data because the cursor was positioned after the last row of the result table.

SQLSTATE – SQLSTATE is also set by DB2 after the execution of each SQL statement. Thus, application programs can check the execution of SQL statements by testing SQLSTATE instead of SQLCODE.

Hope this was helpful.

Thanks,
SQLServerF1 Team
Information about DB2 SQL Error Codes and Error Messages on Windows, Linux and Z/OS Operating Systems.

 

Leave a Reply

Your email address will not be published. Required fields are marked *