Messages Reference

SQL0400 - SQL0499

SQL0401N The data types of the operands for the operation "<operator>" are not compatible.

Cause: The operation "<operator>" appearing within the SQL statement has a mixture of numeric and nonnumeric operands, or the operation operands are not compatible.

DataJoiner users: this data type violation can be at the data source or at DataJoiner.

Some DataJoiner data sources do not provide the appropriate values for "<operator>". In these cases the message token will have the following format: "<data source>:UNKNOWN", indicating that the actual value for the specified data source is unknown.

The statement cannot be processed.

Action: Check all operand data types to ensure that they are comparable and compatible with the statement usage.

If all the SQL statement operands are correct and accessing a view, check the data types of all the view operands.

DataJoiner users: if the reason is unknown, isolate the problem to the data source failing the request (see the Problem Determination Guide for procedures to follow to identify the failing data source) and examine the data type restrictions for that data source.

sqlcode: -401

sqlstate: 42818

SQL0402N The data type of an operand of an arithmetic function or operation "<operator>" is not numeric.

Cause: A nonnumeric operand is specified for the arithmetic function or operator "<operator>".

The statement cannot be processed.

Action: Correct the SQL statement syntax so all specified function or operator operands are numeric.

sqlcode: -402

sqlstate: 42819

SQL0403W The newly defined alias "<name>" resolved to the object "<name2>" which is currently undefined.

Cause: The alias <name> has been defined upon:

a table or view which currently is not defined in the database
another alias which resolves to a table or view which is not defined in the database.

The object <name2> is the undefined object. This object must exist before any SQL statement (other than CREATE ALIAS) can successfully use the newly created alias. The specified alias <name> is created.

An inoperative view is considered to be undefined for the purposes of creating an alias.

Action: Ensure that the undefined object <name2> is defined prior to using the newly created alias in an SQL statement (other than CREATE ALIAS).

sqlcode: +403

sqlstate: 01522

SQL0404N A string in the UPDATE or INSERT statement is too long for column "<name>".

Cause: An INSERT or UPDATE statement specifies a value that is longer than the maximum-length string that can be stored in the column indicated.

The statement cannot be processed.
Note: "<name>" may or may not be returned in SQLCA, depending on the INSERT or UPDATE statement syntax.

Action: Check the length of the object column and correct the program or SQL statement so the insert or update string does not exceed the maximum length.

sqlcode: -404

sqlstate: 22001

SQL0405N The numeric literal "<literal>" is not valid because its value is out of range.

Cause: The specified numeric literal is not in the acceptable range.

The proper ranges for SQL values are as follows:

For FLOAT values:
- 0
- In the positive range of +2.225E-307 to +1.79769E+308
- In the negative range of -1.79769E+308 to -2.225E-307.
-9999999999999999999999999999999. to 9999999999999999999999999999999. for DECIMAL values
-2147483647 to 2147483647 for INTEGER values
-32768 to +32767 for small integer (SMALLINT) values.

In certain contexts the literal may have further restrictions that result in this error. For more information, look up the statement or clause in the SQL Reference.

DataJoiner users: this range violation can be at the data source or at DataJoiner. The proper ranges for SQL values located in data source tables depend on the data source. To determine the correct range, see the appropriate documentation for the data source. Some DataJoiner data sources do not provide the appropriate values for "<literal>". In these cases the message token will have the following format: "<data source>:UNKNOWN", indicating that the actual value for the specified data source is unknown.

The statement cannot be processed.

Action: Reduce the literal value to the appropriate size.

sqlcode: -405

sqlstate: 42820

SQL0406N A numeric value in the UPDATE or INSERT statement is not within the range of its target column.

Cause: The value of a host variable or a numeric value calculated during processing of the UPDATE or INSERT SQL statement is outside the target column range. This problem may be caused by the values occurring in the object column, the SQL operation being performed on those values, or both.

The statement cannot be processed.

Action: See the explanation of message SQL0405 for ranges allowed for numeric data types.

NOTE: For system catalog updates, see the SQL Reference for valid ranges in various columns of updateable catalogs.

DataJoiner users: for the ranges of SQL data types, refer to the IBM DataJoiner Application Programming and SQL Reference Supplement.

sqlcode: -406

sqlstate: 22003

SQL0407N Assignment of a NULL value to a NOT NULL column "<name>" is not allowed.

Cause: One of the following occurred:

The update or insert value was NULL, but the object column was declared as NOT NULL in the table definition. Consequently:
- NULL values cannot be inserted into that column.
- An update cannot set values in that column to NULL.
- A SET transition-variable statement in a trigger cannot set values in that column to NULL.
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.
The column name list for the INSERT statement omits a column declared NOT NULL and without WITH DEFAULT in the table definition.
The view for the INSERT statement omits a column declared NOT NULL and without WITH DEFAULT in the base table definition.

DataJoiner users: this situation can be detected by DataJoiner or by the data source. Some DataJoiner data sources do not provide the appropriate values for "<name>". In these cases the message token will have the following format: "<data source>:UNKNOWN", indicating that the actual value for the specified data source is unknown.

The statement cannot be processed.
Note: Under some circumstances, the token "<name>" may not be filled in (sqlerrmc field of the SQLCA not filled in).

Action: Correct the SQL statement after examining the object table definition to determine which columns of the table have the NOT NULL attribute and do not have the WITH DEFAULT attribute.

sqlcode: -407

sqlstate: 23502

SQL0408N An UPDATE, INSERT, or SET transition-variable value is not compatible with the data type of its target column "<name>".

Cause: The data type of the value to be inserted into or set in the column "<name>" by an INSERT, UPDATE, or SET transition-variable statement is incompatible with the declared data type of that column. Both must be:

Numeric
Character
Graphic
Dates or character
Times or character
Timestamps or character
Datalinks
The same distinct types
Reference types where the target type of the value is a subtype of the target type of the column.

The statement cannot be processed.

Action: Examine the current definition for the object table. Ensure the host variable or literal value assigned to the specified column is the proper data type.

sqlcode: -408

sqlstate: 42821

SQL0409N The operand of a COUNT function is not valid.

Cause: As specified in the SQL statement, the operand of the COUNT function does not conform to the rules of SQL syntax. Only COUNT(*) and COUNT(DISTINCT column) are allowed.

The statement cannot be processed.

Action: Specify COUNT(*) or COUNT(DISTINCT column).

NOTE: This message is only applicable to versions of DB2 prior to Version 2.

sqlcode: -409

sqlstate: 42607

SQL0410N The floating point literal "<literal>" contains more than 30 characters.

Cause: The specified floating point literal is more than 30 characters in length, excluding leading zeros. A floating point literal has a maximum length of 30 characters.

The statement cannot be processed.

Action: Shorten the specified literal.

sqlcode: -410

sqlstate: 42820

SQL0412N The SELECT clause of a subquery specifies multiple columns.

Cause: In the context of the SQL statement, the subquery can have only one column specified in its SELECT clause.

The statement cannot be processed.

Action: Specify only one column in the SELECT clause of the subquery.

sqlcode: -412

sqlstate: 42823

SQL0413N Overflow occurred during numeric data type conversion.

Cause: During processing of the SQL statement, an overflow condition arose when converting from one numeric type to another. Numeric conversion is performed according to the standard rules of SQL.

DataJoiner users: numeric conversion can occur at DataJoiner, at data sources, or both.

The statement cannot be processed. No data was retrieved, updated, or deleted.

Action: Examine the syntax of the SQL statement to determine the cause of the error. If the problem is data-dependent, it may be necessary to examine the data processed at the time of the error.

sqlcode: -413

sqlstate: 22003

SQL0415N The data types of corresponding columns are not compatible in a fullselect that includes a set operator or in the multiple rows of a VALUES clause of an INSERT or fullselect.

Cause: There are various statements where this error may occur.

It may occur within a SELECT or VALUES statement that includes set operations (UNION, INTERSECT, or EXCEPT). The corresponding columns of the subselects or fullselects that make up the SELECT or VALUES statements are not compatible.
It may occur within an INSERT statement that is inserting multiple rows. In this case, the corresponding columns of the rows specified in the VALUES clause are not compatible.
It may occur within a SELECT or VALUES statement where the VALUES clause is used with multiple rows. In this case, the corresponding columns of the rows specified in the VALUES clause are not compatible.

The columns are incompatible for one of the following reasons:

Both columns are not characters
Both columns are not numbers
Both columns are not dates
Both columns are not times
Both columns are not timestamps
Both columns are not graphic
Both columns are not the same user-defined distinct type.

If the data type of a column is character, date, time, or timestamp the corresponding column may be a character string constant.

The statement cannot be processed.

Action: Correct the column names used in the SELECT statements or the expressions in the VALUES clause so that all corresponding columns are compatible types.

sqlcode: -415

sqlstate: 42825

SQL0416N You cannot specify a result column longer than 254 bytes in the SELECT or VALUES statements connected by a set operator other than UNION ALL.

Cause: One of the SELECT or VALUES statements connected by a set operator specifies a result column that is longer than 254 bytes. VARCHAR or VARGRAPHIC result columns longer than 254 bytes can be used only with the UNION ALL set operator.

The statement cannot be processed.

Action: Either use the UNION ALL operator instead of UNION, or remove the result columns longer than 254 bytes from the SELECT or VALUES statements.

sqlcode: -416

sqlstate: 42907

SQL0417N A statement string to be prepared contains parameter markers as the operands of the same operator.

Cause: The statement string specified as the object of a PREPARE or EXECUTE IMMEDIATE contains a predicate or expression in which parameter markers have been used as operands of the same operator without a CAST specification. For example:

    ? > ?

The statement cannot be processed.

Action: This syntax is not supported. Use a CAST specification to give at least one of the parameter markers a data type.

sqlcode: -417

sqlstate: 42609

SQL0418N A statement contains a use of a parameter marker that is not valid.

Cause: Untyped parameter markers cannot be used:

in a SELECT list
as the sole argument of a datetime arithmetic operation
in some cases as the sole argument of a scalar function

Parameter markers can never be used:

in a statement that is not a prepared statement
in the fullselect of a CREATE VIEW statement
in the triggered action of a CREATE TRIGGER statement

The statement cannot be processed.

Action: Correct the syntax of the statement. If untyped parameter markers are not allowed, use the CAST specification to give the parameter marker a data type.

sqlcode: -418

sqlstate: 42610

SQL0419N A decimal divide operation is not valid because the result would have a negative scale.

Cause: A specified decimal division is not valid because it will result in a negative scale.

The formula used internally to calculate the scale of the result for decimal division is:

Scale of result = 31 - np + ns - ds

where np is the precision of the numerator, ns is the scale of the numerator, and ds is the scale of the denominator.

DataJoiner users: the specified decimal division results in an invalid scale for that data source.

The statement cannot be processed.

Action: Examine and ensure the precision and scale of all columns that may participate in a decimal division. Note that an integer or small integer value may be converted to a decimal for this calculation.

sqlcode: -419

sqlstate: 42911

SQL0420N Invalid character found in a character string argument of the function "<function-name>".

Cause: The function "<function-name>" has a character string argument that contains a character that is not valid in a numeric SQL constant. The function may have been called as a result of using the CAST specification with "<function-name>" as the target data type. The function or data type used in the SQL statement may be a synonym for "<function-name>".

If a decimal character is specified in the DECIMAL function then that is the character that must be used in place of the default decimal character.

Action: Ensure that the character strings that are being converted to numeric types contain only characters that are valid in numeric SQL constants, using the decimal character, if specified.

sqlcode: -420

sqlstate: 22018

SQL0421N The operands of a set operator or a VALUES clause do not have the same number of columns.

Cause: The operands of a set operator such as UNION, EXCEPT, or INTERSECT must have the same number of columns. The rows in a VALUES clause must have the same number of columns.

The statement cannot be processed.

Action: Change the SQL statement so each operand, or each row of a VALUES clause, has exactly the same number of columns.

sqlcode: -421

sqlstate: 42826

SQL0423N LOB locator variable "<variable-position>" does not currently represent any value.

Cause: A locator variable is in error. Either it has not had a LOB value assigned to it, or the locator associated with the variable has been freed.

If "<variable-position>" is provided, it gives the ordinal position of the variable in error in the set of variables specified. Depending on when the error is detected, the database manager may not be able to determine "<variable-position>".

Instead of an ordinal position, "<variable-position>" may have the value "function-name RETURNS", which means that the locator value returned from the user-defined function identified by function-name is in error.

Action: Correct the program so that the LOB locator variables used in the SQL statement have valid LOB values before the statement is executed. A LOB value can be assigned to a locator variable by means of a SELECT INTO statement, a VALUES INTO statement, or a FETCH statement.

sqlcode: -423

sqlstate: 0F001

SQL0426N Dynamic commit invalid for application execution environment.

Cause: An application executing in a CONNECT TYPE 2 environment or Distributed Transaction Processing (DTP) environment such as CICS has attempted to execute an SQL dynamic COMMIT statement. The SQL dynamic COMMIT statement cannot be executed in this environment.

Action:

Use the commit statement provided by the DTP environment to perform the commit. For example, in a CICS environment this would be the CICS SYNCPOINT command.
If this statement was executed within a stored procedure, remove the statement entirely.

sqlcode: -426

sqlstate: 2D528

SQL0427N Dynamic rollback invalid for application execution environment.

Cause: An application executing in a CONNECT TYPE 2 environment or Distributed Transaction Processing (DTP) environment such as CICS has attempted to execute an SQL dynamic ROLLBACK statement. The SQL dynamic ROLLBACK statement cannot be executed in this environment.

Action:

Use the rollback statement provided by the DTP environment to perform the rollback. For example, in a CICS environment this would be the CICS SYNCPOINT ROLLBACK command.
If this statement was executed within a stored procedure, remove the statement entirely.

sqlcode: -427

sqlstate: 2D529

SQL0428N DISCONNECT cannot be issued if a connection it is directed against has executed SQL within the unit of work.

Cause: DISCONNECT will not be executed against a connection in the following cases:

If SQL has been issued against the connection within the unit of work.
If a WITH HOLD cursor is currently open for the connection.

Note that DISCONNECT ALL causes the DISCONNECT to be directed against all connections, so the request will fail if any of the connections violate the above restrictions.

Action: Possible actions:

Issue the DISCONNECT statement before any SQL statements have been issued to the database.
COMMIT or ROLLBACK the unit of work before issuing the DISCONNECT.
Close WITH HOLD cursors before committing a unit of work if a DISCONNECT is required for connections for which the cursors were open.

sqlcode: -428

sqlstate: 25501

SQL0429N The maximum number of concurrent LOB locators has been exceeded.

Cause: A maximum of 32,000 concurrent LOB locators per unit of work, is supported by DB2.

Action: Modify the program so that it requires fewer concurrent LOB locators and try again.

sqlcode: -429

sqlstate: 54028

SQL0430N User defined function "<function-name>" (specific name "<specific-name>") has abnormally terminated.

Cause: An abnormal termination has occurred while the named UDF was in control.

Action: The UDF needs to be fixed. Contact the author of the UDF or your database administrator. Until it is fixed, the UDF should not be used.

sqlcode: -430

sqlstate: 38503

SQL0431N User defined function "<function-name>" (specific name "<specific-name>") has been interrupted by the user.

Cause: A user/client interrupt has occurred while the named UDF was in control.

Action: This could indicate some problem in the UDF, such as an infinite loop or wait. If the problem persists, (i.e. the need to interrupt results in the same error condition), then contact the author of the UDF or your database administrator. Until the problem is fixed, the UDF should not be used.

sqlcode: -431

sqlstate: 38504

SQL0432N A parameter marker cannot have the user defined type name "<udt-name>".

Cause: A parameter marker in the statement has been determined as having the user-defined type "<udt-name>" based on the context in which it is used. A parameter marker cannot have a user-defined type as its data type unless it is part of an assignment (VALUES clause of INSERT or SET clause of UPDATE) or it is being explicitly cast to a user-defined distinct data type using the CAST specification.

The statement cannot be processed.

Action: Use an explicit cast to the user-defined distinct data type for the parameter marker or cast the columns that are user-defined distinct data types to their corresponding source data type.

sqlcode: -432

sqlstate: 42841

SQL0433N Value "<value>" is too long.

Cause: The value "<value>" required truncation by a system (built-in) cast or adjustment function, which was called to transform the value in some way. The truncation is not allowed where this value is used.

The value being transformed is one of the following:

an argument to a user defined function (UDF)
an input to the SET clause of an UPDATE statement
a value being INSERTed into a table
an input to a cast or adjustment function in some other context.
a recursively referenced column whose data type and length is determined by the initialization part of recursion and may grow in the iterative part of the recursion.

The statement has failed.

Action: If "<value>" is a literal string in the SQL statement, it is too long for its intended use.

If "<value>" is not a literal string, examine the SQL statement to determine where the transformation is taking place. Either the input to the transformation is too long, or the target is too short.

Correct the problem and rerun the statement.

sqlcode: -433

sqlstate: 22001

SQL0434W An unsupported value for clause "<clause>" has been replaced by the value "<value>".

Cause: The value that was specified for clause "<clause>" is not supported and has been replaced with the identified supported value "<value>".

Action: No change is required if the selected value is acceptable. Otherwise, specify a value that is valid for "<clause>".

sqlcode: +434

sqlstate: 01608

SQL0435N An invalid SQLSTATE "<sqlstate>" is specified in the function RAISE_ERROR.

Cause: The SQLSTATE specified in the RAISE_ERROR function does not conform to the rules for an application defined SQLSTATE.

Action: Correct the SQLSTATE specified in the function RAISE_ERROR. The SQLSTATE must be a character string containing exactly 5 characters. It must be of type CHAR defined with a length of 5, or type VARCHAR defined with a length of 5 or greater. The SQLSTATE value must follow the rules for application-defined SQLSTATEs as follows:

Each character must be from the set of digits ('0' through '9') or non-accented upper case letters ('A' through 'Z')
The SQLSTATE class (first two characters) cannot be '00', '01', or '02' since these are not error classes.
If the SQLSTATE class (first two characters) starts with the character '0' through '6' or 'A' through 'H', then the subclass (last three characters) must start with a character in the range 'I' through 'Z'
If the SQLSTATE class (first two characters) starts with the character '7', '8', '9' or 'I' though 'Z', then the subclass (last three characters) can be any of '0' through '9' or 'A' through 'Z'.

sqlcode: -435

sqlstate: 428B3

SQL0436N The terminating NULL character is missing from the C language NULL-terminated character string host variable.

Cause: The value of an input host variable code in the C programming language requires a NULL-terminator character at the end of the string.

The statement cannot be processed.

Action: Ensure that the value of the input host variable is terminated by the NULL-terminator character.

sqlcode: -436

sqlstate: 22024

SQL0437W Performance of this complex query may be sub-optimal. Reason code: "<reason-code>".

Cause: The statement may achieve sub-optimal performance since the complexity of the query requires resources that are not available or optimization boundary conditions were encountered. The following is a list of reason codes:

1: The join enumeration method was altered due to memory constraints
2: The join enumeration method was altered due to query complexity
3: Optimizer cost underflow
4: Optimizer cost overflow
5: Query optimization class was too low
6: Optimizer ignored an invalid statistic

The statement will be processed.

Action: One or more of the following:

Increase the size of the statement heap (stmtheap) in the database configuration file. (Reason code 1)
Break the statement up into less complex SQL statements. (Reason codes 1,2,3,4)
Ensure predicates do not over-specify the answer set (Reason code 3)
Change the current query optimization class to a lower value (Reason codes 1,2,4)
Issue Runstats for the tables involved in the query (Reason codes 3,4)
Change the current query optimization class to a higher value (Reason code 5)
Reissue RUNSTATS for both the tables involved in the query and their corresponding indexes, i.e. use the AND INDEXES ALL clause so that table and index statistics are consistent (Reason code 6)

sqlcode: +437

sqlstate: 01602

SQL0438N Application raised error with diagnostic text: "<text>".

Cause: This error occurred as a result of execution of the RAISE_ERROR function or the SIGNAL SQLSTATE statement in a trigger.

Action: See application documentation.

sqlcode: -438

sqlstate: application-defined

SQL0439N User defined function "<function-name>" is indirectly implemented by function "<source-function>" which resulted in error "<sqlcode>".

Cause: The function "<function-name>" was referenced in the user's statement. However, because the SOURCE clause was used in the definition of this function, it has turned out that function "<source-function>" actually implements the function. (It may be a direct or an indirect definition path from "<function-name>" to "<source-function>".) At compile time, the encapsulator (DB2 code which acts on behalf of a function) for "<source-function>" has returned the error identified by "<sqlcode>".

Action: The actual error situation needs to be understood better before corrective action can be taken. Look up the explanation for "<sqlcode>". If "<source-function>" is a built-in function, the "<sqlcode>" should indicate the problem, as in the case where a built-in function is directly referenced in the user's statement. If "<source-function>" is a user defined function, the message most likely indicates a problem with one of the arguments or with the result from the function.

Correct the problem and try again.

sqlcode: -439

sqlstate: 428A0

SQL0440N No function by the name "<function-name>" having compatible arguments was found in the function path.

Cause: This occurs in a reference to function "<function-name>", when the database manager cannot find a function it can use to implement the reference. There are several reasons why this could occur:

"<function-name>" was either incorrectly specified or does not exist in the database.
A qualified reference was made, and the qualifier was incorrectly specified.
The user's function path does not contain the schema to which the desired function belongs, and an unqualified reference was used.
The wrong number of arguments were included.
The right number of arguments were included, but the data types of one or more of the arguments is incorrect.
The function does not exist in the database with a create timestamp earlier than the time the package was bound (applies to static statements).

Action: Fix the problem and retry. This could involve catalog access, change to the statement, the addition of new functions, and/or change to the function path.

sqlcode: -440

sqlstate: 42884

SQL0441N Invalid use of DISTINCT with scalar function "<function-name>".

Cause: The keyword DISTINCT was detected within the parentheses in a reference to function "<function-name>", and the function has been resolved as a scalar function. Use of the keyword DISTINCT with a scalar function is invalid.

Action: If a scalar function is being used, then remove the keyword DISTINCT. It is invalid for a scalar function.

If a column function is being used, then there is a problem with function resolution. Check your function path to see if the desired function is in one of the schemas, and also check the SYSFUNCTIONS catalog for the spelling of the function name and the number and types of parameters.

Correct the error and try again.

sqlcode: -441

sqlstate: 42601

SQL0442N Error in referring to function "<function-name>". The maximum number of allowable arguments (90) has been exceeded.

Cause: Too many arguments were specified in the reference to function "<function-name>". The maximum allowable is 90.

Action: Correct the statement by ensuring that the correct number of arguments has been used and try again.

sqlcode: -442

sqlstate: 54023

SQL0443N User defined function "<function-name>" (specific name "<specific-name>") has returned an error SQLSTATE with diagnostic text "<text>".

Cause: An SQLSTATE of the form 38xxx was returned to DB2 by User Defined Function (UDF) "<function-name>" (specific name "<specific-name>"), along with message text "<text>".

Action: The user will need to understand the meaning of the error. See your Database Administrator, or the author of the UDF.

Errors that are detected by the IBM supplied functions in the SYSFUN schema all return the SQLSTATE 38552. The message text portion of the message is of the form:

SYSFUN:nn

where nn is a reason code meaning:

01: Numeric value out of range
02: Division by zero
03: Arithmetic overflow or underflow
04: Invalid date format
05: Invalid time format
06: Invalid timestamp format
07: Invalid character representation of a timestamp duration
08: Invalid interval type (must be one of 1, 2, 4, 8, 16, 32, 64, 128, 256)
09: String too long
10: Length or position in string function out of range
11: Invalid character representation of a floating point number

sqlcode: -443

sqlstate: 38xxx (the SQLSTATE returned by the UDF).

SQL0444N User defined function "<function-name>" (specific name "<specific-name>") is implemented with code in library or path "<library-or-path>", function "<function-code-id>" which cannot be accessed. Reason code: "<code>".

Cause: The DBMS is trying to access the body of the code that implements user defined function "<function-name>" (specific name "<specific-name>"), and cannot access it for the reason given by reason code "<code>" (the codes are listed below). The file implementing the function is identified by "<library-or-path>", and the function by "<function-code-id>".

(Note that these last two tokens may be truncated, due to limitations in the total token length which can be provided. If this happens, then the definition of the function in the catalogs may need to be accessed in order to determine the full library or path and function code id that were defined for the function.)

Action: Given for each reason code:

1

Path name "<library-or-path>" is longer than the maximum (255 bytes). Either the function definition needs to be changed to specify a shorter path, or the DB2 instance path name is too long. Look at the catalog definition to determine which is the case. It may be necessary to move the function body to a directory with a shorter path name.

2

The DB2 instance path name could not be retrieved from DB2. See your system administrator.

3

The path "<library-or-path>" could not be found. See the function creator or your database administrator. The function definition or the location of the function itself will need to be corrected.

4

The file in "<library-or-path>" could not be found. See the function creator or your database administrator. The function definition or the location of the function may need to be corrected, or the function may need to be re-linked.

In addition to the above, this reason code can result if the UDF requires a shared library or DLL, and the shared library cannot be located (using the concatenation of directories specified in the LIBPATH environment variable in UNIX-based systems, the PATH environment variable in INTEL systems). There can be multiple levels of this indirection involved, leading to this reason code. For example UDF body X can be found, and it needs shared library Y which can also be found. But Y needs Z, and Z cannot be located, and this will result in SQL0444N reason code 4.

5

There was insufficient memory to load the library containing the function. Contact the function creator or your database administrator to make sure the library was correctly linked. The system configuration may need to be changed to make more memory available to DB2.

6

The function "<function-code-id>" could not be found in the module named. See the function creator or your database administrator. The function definition or the function itself will need to be corrected.

7

The symbol given as the function name ("<function-code-id>") is not the name of a valid function in the named library. See the function creator or your database administrator. The function definition or the function itself will need to be corrected.

8

The "load" system function has failed, for other than the reasons given above. It may be that the module was not linked at all, or not linked correctly.

9

There was insufficient memory to resolve the function name "<function-code-id>" in the library identified in "<library-or-path>". Contact the function creator or your database administrator to make sure the library containing the function was correctly linked. The system configuration may need to be changed to make more memory available to DB2 Server.

10

The loadquery system call has failed. This can only happen on unix-based systems, and is a symptom that the database manager itself has not been installed correctly. Contact your system administrator.

11

The agent process is searching for a particular database manager function that should be in the libdb2.a library, and it cannot find it. This can only happen on unix-based systems, and is a symptom that the database manager is not correctly installed. Contact your system administrator.

15

Access has been denied. This can happen in Windows NT environments if the EXTERNAL NAME specification in the CREATE FUNCTION statement does not specify a full path, necessitating a search using the PATH environment variable. If the PATH is not valid, containing an invalid drive for example, this reason code can result.

other

An unidentified system failure has occurred. Note the code, and see your system administrator.

DataJoiner users: if this user defined function is strictly used to represent a remote user defined function (and thus no code is required to reside on DataJoiner), you may want to consider modifying the SQL statement or statistics to enable this function to be evaluated on the remote data source.

sqlcode: -444

sqlstate: 42724

SQL0445W Value "<value>" has been truncated.

Cause: The value "<value>" was truncated by a system (built-in) cast or adjustment function, which was called to transform the value in some way. This is a warning situation.

The value being transformed is the output of a user-defined function (UDF), and is being transformed because of a CAST FROM specification in the UDF definition, or because the UDF is sourced on another function and the result needed to be transformed.

Action: Ensure that the output is as expected and that the truncation has not caused any unexpected consequences.

sqlcode: +445

sqlstate: 01004

SQL0447W The statement contains redundant specifications involving the clause "<clause>".

Cause: The "<clause>" keyword was included more than once in the statement. This is a warning condition.

Action: If the redundancy was intentional, or if it is determined that no harm has resulted, then no response is necessary. The type of "harm" alluded to could include, for example, the omission of some other desired keyword.

sqlcode: +447

sqlstate: 01589

SQL0448N Error in CREATE statement for user defined function or stored procedure "<function-name>". The maximum number of allowable parameters (90 for user defined functions / 32767 for stored procedures) has been exceeded.

Cause: Too many parameters were specified in the CREATE statement for function or procedure "<function-name>".

Action: Change the CREATE statement to include fewer parameters.

sqlcode: -448

sqlstate: 54023

SQL0449N The CREATE for user defined function or stored procedure "<function-name>" contains an invalidly formatted library/function identification in the EXTERNAL NAME clause.

Cause: An error was found in the EXTERNAL NAME clause of the CREATE statement for user defined function (UDF) or stored procedure "<function-name>". The rules for the library/function identification are as follows:

The name takes the form '<a>!<b>' or '<a>'. No blanks are permitted within the single quotes. <a> is one of the following:

the full-path identification of a file (e.g. (on AIX) /u/slick/udfs/math or (on OS/2) d:\myfunc\math)
a file name (e.g. math) assumed to be in 'function' directory in the sqllib directory.

If <b> is omitted, the default is the entry point defined when the named file was linked. If <b> is present, it identifies the entry point (function) within <a> that will be invoked as the body of the UDF or stored procedure.

Action: Correct the problem and try again. A possible cause is the inclusion of a blank, or having the '!' at the beginning or end of the name.

sqlcode: -449

sqlstate: 42878

SQL0450N User-defined function "<function-name>" (specific name "<specific-name>") has generated a result value, SQLSTATE value, message text, or scratchpad which is too long.

Cause: Upon return from user-defined function (UDF) "<function-name>" (specific name "<specific-name>"), DB2 has detected that more bytes were returned than were allocated for one of the following:

the result value (based upon the function definition). There are several possible causes:
- Too many bytes were moved to the result buffer.
- The data type is one where the data value must be delimited by a null, such as VARCHAR(n), and the delimiting null was not within the range of the defined size.
- DB2 is expecting a two- or four- byte length value preceding the value, and this length exceeds the defined size of the result.
- A LOB locator is returned by the UDF, and the length of the LOB value associated with this locator exceeds the defined size of the result.
The definition of the result argument in the UDF must conform to the requirement for the data type. Refer to the Embedded SQL Programming Guide for more information.
the SQLSTATE value (6 bytes including the null terminator)
the message text (71 bytes including the null terminator).
the scratchpad content (100 bytes, plus 4 byte binary length preceding it).

This is not permitted.

Action: See your Database Administrator, or the author of the UDF.

sqlcode: -450

sqlstate: 39501

SQL0451N The "<data-item>" definition, in the CREATE for user defined function "<function-name>", contains a data type "<type>" that is not appropriate for a non-sourced function written in the given language.

Cause: An error was made in the "<data-item>" part of the CREATE statement for the user defined function (UDF) "<function-name>". The user's CREATE statement contained the invalid type "<type>", or it contained a user defined type (UDT) which is based on the invalid type "<type>".

"<data-item>" is a token that identifies the area of the problem in the CREATE statement. For example, "PARAMETER 2" or "RETURNS" or "CAST FROM".

Action: Determine which situation has occurred, and take corrective action. Possible corrective actions include:

Changing the function definition to a supported type, e.g. from DECIMAL to FLOAT. This may involve a change in the UDF body itself, and may also involve use of cast functions upon use of the function.
Creating a new (suitably based) user defined type, or changing the definition of an existing UDT.

sqlcode: -451

sqlstate: 42815

SQL0452N Unable to access the file referenced by host variable "<variable-position>". Reason code: "<reason-code>".

Cause: An error was encountered attempting to access or while accessing the file referenced by the "nth" host variable, where n = "<variable-position>", for the reason given by "<reason-code>". <variable-position> is set to 0 if the host variable position could not be determined. The possible reason codes are as follows:

01 - The file name length is invalid or the file name and/or the path has an invalid format.

02 - The file option is invalid. It must have one of the following values:

  SQL_FILE_READ      -read from an existing file
  SQL_FILE_CREATE    -create a new file for write
  SQL_FILE_OVERWRITE -overwrite an existing file.
                      If the file does not exist,
                      create the file.
  SQL_FILE_APPEND    -append to an existing file.
                      If the file does not exist,
                      create the file.

03 - The file cannot be found.
04 - The SQL_FILE_CREATE option was specified for a file with the same name as an existing file.
05 - Access to the file was denied. The user does not have permission to open the file.
06 - Access to the file was denied. The file is in use with incompatible modes. Files to be written to are opened in exclusive mode.
07 - Disk full was encountered while writing to the file.
08 - Unexpected end of file encountered while reading from the file.
09 - A media error was encountered while accessing the file.
10 - An incomplete or invalid multibyte character was encountered while reading from the file.
11 - An error was encountered while converting data from the file code page to the application's graphic character code page.

Action:

For reason code 01, correct the file name length, file name and/or path.

For reason code 02, specify a valid file option.

For reason code 03, ensure that the file specified exists before attempting to access the file.

For reason code 04, either delete the file if it is no longer required or specify a file name that does not currently exist.

For reason code 05, ensure that the user has access (correct file permissions) to the file.

For reason code 06, either use a different file or, if the file must be accessed, modify the application to ensure that the file is not accessed concurrently.

For reason code 07, delete unnecessary files to free up disk space or specify a file that resides on another drive/file system with sufficient disk space. Also, ensure that no operating system or user file size limits were reached. If your application code page uses a multibyte encoding scheme it is possible that only part of the last character was written, ensure that the file contains only fully formed characters.

For reason code 08, if a file is to be used for input, ensure that the file is not modified before the entire file has been read.

For reason code 09, correct all errors on the media where the file resides.

For reason code 10, ensure that the file contains valid multibyte characters based on the code page of the application or submit the request while running under the same code page as the contents of the file.

For reason code 11, ensure that character conversion support between the code page of the file, for example Japanese EUC, and the graphic code page of the application, for example UCS-2, is installed.

sqlcode: -452

sqlstate: 428A1

SQL0453N A problem has been identified with the RETURNS clause in the CREATE statement for user defined function "<function-name>".

Cause: A problem casting the result of user defined function (UDF) "<function-name>" has been identified. The CAST FROM data type is not castable to the RETURNS data type, and it should be. See the SQL Reference for details on casting between data types.

Action: Change the RETURNS or CAST FROM clause so that the CAST FROM data type is castable to the RETURNS data type.

sqlcode: -453

sqlstate: 42880

SQL0454N The signature provided in the CREATE statement for user defined function or stored procedure "<function-name>" matches the signature of some other function or procedure already existing in the schema.

Cause: The signature a function consists of the function name, the number of parameters defined for the function, and an ordered list of the types of the parameters (without regard to any parameters of the types).

The signature of a procedure consists of the procedure name and the number of parameters defined for the procedure (data types are not considered).

In this case, there is a function or procedure ("<function-name>") already in the schema having the same signature as the function or procedure being created.

The statement cannot be processed.

Action: Determine if the existing function or procedure already provides the functionality desired. If not, then the new function's or procedure's signature will have to be changed (e.g. change the function or procedure name).

sqlcode: -454

sqlstate: 42723

SQL0455N In the CREATE for user defined function or stored procedure "<function-name>", the schema name "<schema-name1>" provided for the SPECIFIC name does not match the schema name "<schema-name2>" of the function or procedure.

Cause: If the SPECIFIC name is specified as a two part name, the "<schema-name1>" portion must be the same as the "<schema-name2>" portion of the "<function-name>". Note that the "<schema-name2>" portion of "<function-name>" may have been specified directly or it may have defaulted to the authorization ID of the statement.

Action: Correct the statement and try again.

sqlcode: -455

sqlstate: 42882

SQL0456N In the CREATE of user defined function or stored procedure "<function-name>", the SPECIFIC name "<specific-name>" already exists in the schema.

Cause: The user has supplied an explicit SPECIFIC name "<specific-name>" in the CREATE statement for function or procedure "<function-name>", but this name already exists as the SPECIFIC name for a function or procedure in the schema.

Action: Choose a new SPECIFIC name and try again.

sqlcode: -456

sqlstate: 42710

SQL0457N A function, user-defined data type, or structured data type attribute cannot be called "<name>" since it is reserved for system use.

Cause: The user-defined function or user-defined data type cannot be created because a specified name is reserved for use by the system. Names that cannot be used for function names, distinct type names, structured type names, or attribute names include:

  "=","<",">",">=","<=","&=","&>",,"&<",
  "!=","!>","!<","<>", SOME, ANY, ALL, NOT,
  AND, OR, BETWEEN, NULL, LIKE, EXISTS, IN,
  UNIQUE, OVERLAPS, SIMILAR, and MATCH.

Action: Select a name for the function, user-defined data type, or structured data type attribute that is not reserved for system use.

sqlcode: -457

sqlstate: 42939

SQL0458N In a reference to function or stored procedure "<function-name>" by signature, a matching function or stored procedure could not be found.

Cause: In a reference to function or stored procedure "<function-name>" by signature, no matching function or stored procedure could be found. If a data type is used that can accept a parameter (e.g. CHAR(12)), then the type parameter is optional, i.e. you can specify the parameter (CHAR(12) or omit it (CHAR()). If you specify the parameter, then the DBMS will only accept an exact match on the data type AND the data type parameter. If you omit the parameter, then the DBMS will accept a match on data type only. The CHAR() syntax provides a way to say "don't care about data type parameters in finding a matching function".

Note also that in the DROP FUNCTION/PROCEDURE and COMMENT ON FUNCTION/PROCEDURE statements, an unqualified reference is qualified with the statement authorization ID, and this is the schema where the problem can be found. In the SOURCE clause of a CREATE function, the qualification comes from the current function path. In this case, there is no matching function in the entire path.

A function cannot be sourced on the COALESCE, NULLIF, NODENUMBER, PARTITION, TYPE_ID, TYPE_NAME, TYPE_SCHEMA, or VALUE built-in functions.

The statement cannot be processed.

Action: Possible responses include:

Changing the function path to include the correct schema.
Removing parameters from the specifications of data types.
Using a SPECIFIC name to refer to the function or procedure instead of a signature.

sqlcode: -458

sqlstate: 42883

SQL0461N A value with data type "<source-data-type>" cannot be CAST to type "<target-data-type>".

Cause: The statement contains a CAST with the first operand having a data type of "<source-data-type>" to be cast to the data type "<target-data-type>". This cast is not supported.

Action: Change the data type of either the source or target so that the cast is supported. For predefined data types these are documented in the SQL Reference. For a cast involving a user-defined distinct type, the cast can be between the base data type and the user-defined distinct type or from a data type that is promotable to the base data type to the user-defined distinct type.

sqlcode: -461

sqlstate: 42846

SQL0462W User defined function "<function-name>" (specific name "<specific-name>") has returned a warning SQLSTATE, with diagnostic text "<text>".

Cause: An SQLSTATE of the form 01Hxx was returned to DB2 by user defined function (UDF) "<function-name>" (with specific name "<specific-name>"), along with message text "<text>".

Action: The user will need to understand the meaning of the warning. See your database administrator, or the author of the UDF.

sqlcode: +462

sqlstate: 01Hxx

SQL0463N User defined function "<function-name>" (specific name "<specific-name>") has returned an invalid SQLSTATE "<state>", with diagnostic text "<text>".

Cause: The valid SQLSTATEs that a user defined function (UDF) can return are 38xxx (error), 38502 (error) and 01Hxx (warning). This user defined function "<function-name>" (specific name "<specific-name>") returned an invalid SQLSTATE "<state>", along with message text "<text>". The UDF is in error.

Action: The UDF will need to be corrected. See your database administrator, or the author of the UDF. The application significance of the bad SQLSTATE can also be learned from the UDF author.

sqlcode: -463

sqlstate: 39001

SQL0465N Unable to start, initialize, or communicate with fenced mode process. Reason code "<code>".

Cause: There is a system related problem involved with the running of a fenced mode UDF (User Defined Function). The exact nature of the problem is indicated by "<code>". This is NOT a user problem. Possible reason codes are:

UDF process errors

21:: Failure to initialize internal data or application data.
22:: Failure to register signal handlers.
23:: Failure to grant agent process the access permission for the REQUEST QUEUE.
24:: Failure to connect to the UDF process Shared Memory.
25:: Failure to open the REPLY QUEUE.
26:: Failure to write to the REPLY QUEUE.
27:: Failure to create the REQUEST QUEUE.
28:: Failure to read from the REQUEST QUEUE.
29:: UDF process dies.
30:: UDF process catches an USER INTERRUPT signal.
31:: Failure to unload UDF module.
32:: Failure to allocate storage for control blocks used in module loading/unloading.
33:: Failure to send SIGINT from agent process to UDF process.
34:: Failure to initialize OLE library.
40:: Internal error occurred in UDF process.

Agent process errors

41:: Failure to spawn UDF process.
42:: Failure to create the REPLY QUEUE.
43:: Failure to read from the REPLY QUEUE.
44:: Failure to open the REQUEST QUEUE.
45:: Failure to write to the REQUEST QUEUE.
47:: Failure to grant the UDF process the access permission to the UDFP shared memory set.
48:: Failure to grant the UDF process the access permission to the REPLY QUEUE.
49:: Failure to allocate storage for control blocks used in module loading/unloading.
50:: Agent process died while running UDF code or agent code.
51:: Agent process catches USER INTERRUPT while running un-fenced UDF code.
60:: Internal error occurred in UDF process.

Action: Contact your Database or System administrator.

sqlcode: -465

sqlstate: 58032

SQL0466W There is one or more result sets associated with a stored procedure.

Cause: This message is returned as a result of issuing a CALL SQL statement. It indicates that the stored procedure associated with the CALL SQL statement has one or more result sets associated with it.

Action: None required.

sqlcode: +466

sqlstate: 01610

SQL0467W Another result set exists for a stored procedure.

Cause: This message is returned as a result of closing a cursor. It indicates that another result set exists for a stored procedure.

Action: None required.

sqlcode: +467

sqlstate: 01611

SQL0473N A user defined data type having the same name as a system predefined type cannot be created.

Cause: The name of a data type to be created has an unqualified name that is the same as a system-predefined data type or is BOOLEAN. This is not allowed. Adding delimiters does not make the name valid.

The statement could not be processed.

Action: Correct the statement to use another identifier.

sqlcode: -473

sqlstate: 42918

SQL0475N The result type "<type-1>" of the SOURCE function cannot be cast to the RETURNS type "<type-2>" of the user defined function "<function-name>".

Cause: In order for the CREATE of a sourced user defined function (UDF) to be valid, the result type ("<type-1>") of the source function must be castable to the RETURNS type ("<type-2>") of the function being created. There is no supported cast between these data types. See the SQL Reference for details on casting between data types.

Action: Change the RETURNS data type or the SOURCE function identification so that the result type of the SOURCE function is castable to the RETURNS data type.

sqlcode: -475

sqlstate: 42866

SQL0476N Reference to function or stored procedure "<function-name>" was made without a signature, but the function or stored procedure is not unique in its schema.

Cause: References to a function or stored procedure without a signature is permitted, but the named function or store procedure "<function-name>" must be unique in its schema and is not.

Note that in the DROP FUNCTION/PROCEDURE and COMMENT ON FUNCTION/PROCEDURE statements, an unqualified reference is qualified with the statement authorization ID, and this is the schema where the problem can be found. In the SOURCE clause of a CREATE FUNCTION, the qualification comes from the current function path. In this case, the first schema in the path containing a function with this name had other functions by the same name.

DataJoiner users: if the statement is a CREATE FUNCTION MAPPING statement, this error indicates that an attempt was made to create a function mapping from one remote function to more than one local function.

Action: Correct the reference by one of the following:

completing the signature
using the SPECIFIC name of the desired function or procedure
changing the function path

and try again.

sqlcode: -476

sqlstate: 42725

SQL0478N The object type "<object-type1>" cannot be dropped because there is an object "<object-name>", of type "<object-type2>", which depends on it.

Cause: The base object, which is an object of type "<object-type1>" cannot be dropped because another object depends on it. There is a restrict dependency defined with objects of type "<object-type2>" (of which object "<object-name>" is an example).

It may be that the dependency is indirect. That is, the named object is dependent on another object which is dependent on the object being dropped.

For example:

   - function F1 is sourced on function F2
   - table V1 is defined using F1
   - an attempt to drop F2 will fail due to
     the direct dependency of F1 on F2 and the
     indirect dependency of V1 on F2

Action: Either do not drop this object or drop the dependent objects first.

sqlcode: -478

sqlstate: 42893

SQL0481N The GROUP BY clause contains "<element 1>" nested within "<element 2>".

Cause: The following types of nesting are not allowed within a GROUP BY clause:

CUBE within CUBE, ROLLUP, or GEL
ROLLUP within CUBE, ROLLUP, or GEL
() within CUBE, ROLLUP, or GEL
GROUPING SET within GROUPING SET, CUBE, ROLLUP, or GEL

where GEL represents the element shown as grouping-expression-list in the syntax diagram of the GROUP BY clause.

In some instances the value "---" will be shown for "<element 2>". In this case "---" represents one of CUBE, ROLLUP, GROUPING SET, or GEL.

The statement cannot be processed.

Action: Modify the GROUP BY clause to remove the nesting.

sqlcode: -481

sqlstate: 428B0

SQL0483N In the CREATE for user defined function "<function-name>" statement, the number of parameters does not match the number of parameters of the SOURCE function.

Cause: An attempt is being made to CREATE a user defined function "<function-name>" which is sourced on another function. One of the following situations has been identified:

The SOURCE clause uses a function-name (input parameter list) to identify the source function, and the number of types in the list is different from the number of parameters of the function being created.
The SOURCE clause uses different syntax to identify the source function, and the number of types of that function is different from the number of parameters of the function being created.

Action: The number of parameters for the SOURCE function and for the function being created must be the same. The identification of the SOURCE function needs to be changed, to

fix the input parameter list
correct the function name or function specific name to identify the proper function.

It is also possible that the function path needs to be corrected in order for correct function resolution to occur.

sqlcode: -483

sqlstate: 42885

SQL0486N The BOOLEAN data type is currently only supported internally.

Cause: One or more of the data types in the statement is BOOLEAN. This is not supported in the current version of DB2.

Action: Change the data type(s) then resubmit the statement.

sqlcode: -486

sqlstate: 42991

SQL0487N User defined function "<function-name>" (specific name "<specific-name>") attempted to execute an SQL statement.

Cause: The program used to implement the body of a user defined function is not allowed to execute SQL statements. This user defined function "<function-name>" (specific name "<specific-name>") contains SQL statements.

Action: Remove any SQL statements then recompile the program.

sqlcode: -487

sqlstate: 38502

SQL0489N The function "<function-name>" in a SELECT or VALUES list item has produced a BOOLEAN result.

Cause: The function "<function-name>" is defined for use as a predicate, returning a boolean result. Such a result is not valid in a select list.

The statement cannot be processed.

Action: Correct the function name or remove the use of the function.

sqlcode: -489

sqlstate: 42844

SQL0491N The CREATE statement for user defined function "<function-name>" must have a RETURNS clause, and either the EXTERNAL clause (with other required keywords) or the SOURCE clause.

Cause: A required clause is missing in the CREATE for function "<function-name>". If EXTERNAL was specified, the following must also be specified: LANGUAGE, PARAMETER STYLE, VARIANT or NOT VARIANT, NO SQL, and EXTERNAL ACTION or NO EXTERNAL ACTION.

Action: Add the missing clause, then try again.

sqlcode: -491

sqlstate: 42601

SQL0492N There is a problem in the CREATE for user defined function "<function-name>", with parameter number "<number>". It may involve a mismatch with a SOURCE function.

Cause: The parameter in position "<number>" of function "<function-name>" is in error and the CREATE cannot be performed. The parameter in position "<number>" of the source function is not castable to the corresponding parameter of the function being created.

Action: Possible corrections include:

Identify a different source function.
Change the data type of the parameter of the function being created so that the data type of the source function can be cast to this data type.

sqlcode: -492

sqlstate: 42879

SQL0493N User defined function "<function-name>" (specific name "<specific-name>") has returned a date, time or timestamp value which is syntactically or numerically invalid.

Cause: The body of user defined function (UDF) "<function-name>" (specific name "<specific-name>") has returned an invalid date, time or timestamp value.

An example of a syntactically invalid date value is '1994-12*25': the '*' should be a '-'. An example of a numerically invalid time value is '11.71.22': there is no 71st minute in the hour.

Action: The UDF needs fixing. See your DBA, or the author of the UDF.

sqlcode: -493

sqlstate: 22007

SQL0499N Cursor "<cursor-name>" has already been assigned to this or another result set from procedure "<procedure-name>".

Cause: An attempt was made to assign a cursor to a result set but multiple cursors have been allocated for procedure "<procedure-name>".

Action: Determine if the target result set was previously assigned to a cursor. If multiple cursors have been allocated for procedure "<procedure-name>" ensure that only one cursor is used to process the result sets of a stored procedure.

sqlcode: -499

sqlstate: 24516

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]