DB2 SQL Errors Codes and Error Messages and Warnings from Error -639 to -643

SQLServerF1

Error: DB2 SQL Error: SQLCODE=-639, SQLSTATE: 56027, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER=
A NULLABLE COLUMN OF A
FOREIGN KEY WITH A DELETE RULE
OF SET NULL CANNOT BE A
COLUMN OF THE KEY OF A
PARTITIONED INDEX
Explanation: A partition key of the clustering index
cannot be updated. Therefore, a foreign key column
with a delete rule of SET NULL cannot be a column of
a partition key if that column is nullable. If this error
occurs for an ALTER TABLE operation, the foreign key
cannot be created. If this error occurs for a CREATE
INDEX operation, the index cannot be created.
System action: The statement cannot be executed.
Programmer response: Review the delete rule of the
referential constraint and the partition keys for the
index. Do one of the following:
v If the operation in error was CREATE INDEX, either
change the index partition key definition or drop and
redefine the referential constraint with a different
delete rule.
v If the operation in error was ALTER TABLE, change
the referential delete rule so that all nullable index
keys are not part of the foreign keys.
SQLSTATE: 56027

Error: DB2 SQL Error: SQLCODE=-640, SQLSTATE: 56089, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER=
LOCKSIZE ROW CANNOT BE
SPECIFIED BECAUSE TABLE IN THIS
TABLESPACE HAS TYPE 1 INDEX
Explanation: If LOCKSIZE ROW is specified for a
table space, all indexes on tables in the table space
must be type 2 indexes. The following SQL statement
identifies all the type 1 indexes:
SELECT I.CREATOR, I.NAME
FROM SYSIBM.SYSINDEXES I,
SYSIBM.SYSTABLES T
WHERE INDEXTYPE = ‘ ‘
AND T.TSNAME = ‘table_space_name’
AND T.DBNAMe = ‘database_name’
AND T.CREATOR = I.TBCREATOR
AND T.NAME = I.TBNAME;
where ‘table_space_name’ is the name of the table space
that is to be altered; ‘database_name’ is the name of the
database that contains the table space.
System action: The statement cannot be executed.
Programmer response: Since the LOCKSIZE ROW on
the table space and the type 1 indexes conflict, either
use the ALTER INDEX statement to convert all type 1
indexes to type 2 indexes or use another LOCKSIZE
option.
SQLSTATE: 56089

Error: DB2 SQL Error: SQLCODE=-643, SQLSTATE: 54024, SQLERRMC=TBSPACEID=, TABLEID=, COLNO=, DRIVER=
A CHECK CONSTRAINT OR THE
VALUE OF AN EXPRESSION FOR A
COLUMN OF AN INDEX EXCEEDS
THE MAXIMUM ALLOWABLE
LENGTH KEY EXPRESSION
Explanation: The definition of a check constraint or
expression of an index column exceeds the maximum
allowable length:
v A check constraint exceeded 3800 UTF-8 bytes.
Redundant blank spaces are excluded from this limit.
v A value for an expression for an index exceeded 4000
UTF-8 bytes.
System action: The CREATE TABLE or ALTER TABLE
statement failed. The statement cannot be executed.
Programmer response: Change the constraint or
expression so that it does not exceed the maximum
allowable limit. If it is a check constraint, rewrite the
check constraint definition so that it is less than 3800
UTF-8 bytes. You might need to divide a the check
constraint into two or more smaller check constraints.
SQLSTATE: 54024

Above are list of DB2 SQL Errors and Warnings from Error -639 to-643 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 *