Message Reference

SQL0400 - SQL0499

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

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

Federated system users: this data type violation can be at the data source or at the federated server.

Some 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.

User Response: 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.

Federated system 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.

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

The statement cannot be processed.

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

Federated system 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 operators applied to that data source.

sqlcode: -402

sqlstate: 42819

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

Explanation: 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.

User Response: 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>".

Explanation: 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.

User Response: 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.

Explanation: 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.

Federated system users: this range violation can be at the data source or at the federated server. 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 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.

User Response: 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.

Explanation: 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.

User Response: 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.

sqlcode: -406

sqlstate: 22003

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

Explanation: 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.

If the value for "<name>" is of the form "TBSPACEID=n1, TABLEID=n2, COLNO=n3", then the column name from the SQL statement was not available when the error was issued. The values provided identify the tablespace, table, and column number of the base table that does not allow NULL value.

Federated system users: this situation can be detected by the federated server or by the data source. Some 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).

User Response: 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.

If the value for "<name>" is of the form "TBSPACEID=n1, TABLEID=n2, COLNO=n3", you can determine the table name and column name using the following query:

   SELECT C.TABSCHEMA, C.TABNAME, C.COLNAME 
      FROM SYSCAT.TABLES AS T,
           SYSCAT.COLUMNS AS C
      WHERE T.TBSPACEID = n1
      AND T.TABLEID = n2
      AND C.COLNO = n3
      AND C.TABSCHEMA = T.TABSCHEMA
      AND C.TABNAME = T.TABNAME

The table and column identified by this query may be the base table of a view for which the SQL statement failed.

sqlcode: -407

sqlstate: 23502

SQL0408N A value is not compatible with the data type of its assignment target. Target name is "<name>".

Explanation: The data type of the value to be assigned to the column, parameter, SQL variable, or transition variable by the SQL statement is incompatible with the declared data type of the assignment target. 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 same user-defined structured types. Or, the static type of the value must be a subtype of the static type (declared type) of the target.

The statement cannot be processed.

User Response: Examine the statement and possibly the target table or view to determine the target data type. Ensure the variable, expression, or literal value assigned has the proper data type for the assignment target.

sqlcode: -408

sqlstate: 42821

SQL0409N The operand of a COUNT function is not valid.

Explanation: 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.

User Response: 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.

Explanation: 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.

User Response: Shorten the specified literal.

sqlcode: -410

sqlstate: 42820

SQL0412N Multiple columns are returned from a subquery that is allowed only one column.

Explanation: In the context of the SQL statement, a fullselect is specified that can have only one column as a result.

The statement cannot be processed.

User Response: Specify only one column when only a scalar fullselect is allowed.

sqlcode: -412

sqlstate: 42823

SQL0413N Overflow occurred during numeric data type conversion.

Explanation: 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.

Federated system users: numeric conversion can occur at the federated server, at data sources, or both.

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

User Response: 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.

Explanation: 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.

User Response: 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.

Explanation: 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.

User Response: 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.

Explanation: 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.

User Response: 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.

Explanation: 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
in a query captured by DB2 Query Patroller

The statement cannot be processed.

User Response: 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.

Explanation: 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.

Federated system users: Decimal division can occur at the federated server, at data sources, or both. The specified decimal division results in an invalid scale for that data source.

The statement cannot be processed.

User Response: 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>".

Explanation: 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.

User Response: 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.

Explanation: 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.

User Response: 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.

Explanation: 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.

User Response: 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.

Explanation: 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.

Federated system users: SQL dynamic COMMIT statements cannot be executed during a pass-through session.

User Response:

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.

Federated system users: Either comment out the COMMIT statement or code it as a static statement. Then resubmit your program.

sqlcode: -426

sqlstate: 2D528

SQL0427N Dynamic rollback invalid for application execution environment.

Explanation: 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.

Federated system users: SQL dynamic ROLLBACK statements cannot be executed during a pass-through session.

User Response:

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.

Federated system users: Either comment out the ROLLBACK statement or code it as a static statement. Then resubmit your program.

sqlcode: -427

sqlstate: 2D529

SQL0428N The SQL statement is only allowed as the first statement in a unit of work.

Explanation: The SQL statement that was issued is required to execute before any other SQL statement that initiates a unit of work. Following are possible situations:

The SQL statement must be first in the unit of work and SQL has been issued against the connection within the unit of work.
The SQL statement must be first in the unit of work and a WITH HOLD cursor is currently open for the connection.

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

User Response: Issue a COMMIT or ROLLBACK prior to processing the SQL statement. If there are any WITH HOLD cursors, these will need to be closed. If the statement is SET INTEGRITY, remove the COMMIT THRESHOLD clause.

sqlcode: -428

sqlstate: 25001

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

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

User Response: 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.

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

User Response: 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.

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

User Response: 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 or reference target type name "<udt-name>".

Explanation: A parameter marker in the statement has been determined as having the user-defined type "<udt-name>" or a reference type with the target type "<udt-name>" based on the context in which it is used. A parameter marker cannot have a user-defined type or reference 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 or reference data type using the CAST specification.

The statement cannot be processed.

User Response: Use an explicit cast to the user-defined distinct data type or reference data type for the parameter marker. An alternative is to cast the columns that are user-defined distinct data types to their corresponding source data type or columns that are reference data types to their corresponding representation type.

sqlcode: -432

sqlstate: 42841

SQL0433N Value "<value>" is too long.

Explanation: 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.

User Response: 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>".

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

User Response: 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.

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

User Response: 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.

Explanation: 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.

User Response: 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>".

Explanation: 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.

User Response: 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>".

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

User Response: 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>".

Explanation: 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>".

User Response: 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.

Explanation: This occurs in a reference to function "<function-name>", where "<function-name>" can refer to a method, when the database manager cannot find a function or method 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 or method 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).
The mutator method corresponding to an attribute assignment used in an UPDATE statement could not be found. The data type of the new value for the attribute is not a data type that is the same or promotable to the data type of the attribute.

User Response: 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 keyword DISTINCT or ALL with function "<function-name>".

Explanation: There are several possible causes.

The keyword DISTINCT or ALL 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 or ALL with a scalar function is invalid.
The keyword DISTINCT is used in a column function for which it is not supported. These functions include COVARIANCE, CORRELATION and the linear regression functions (those that start with REGR).
The function was expected to be a column function supporting the keyword ALL or DISTINCT but the function it resolved to was not a column function.

User Response:

If a scalar function is being used, then remove the keyword DISTINCT or ALL. It is invalid for a scalar function.
If the function is a column function that does not support the DISTINCT or ALL keywords, then remove the keywords.
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 routine "<routine-name>". The maximum number of allowable arguments (90) has been exceeded.

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

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

sqlcode: -442

sqlstate: 54023

SQL0443N Routine "<routine-name>" (specific name "<specific-name>") has returned an error SQLSTATE with diagnostic text "<text>".

Explanation: An SQLSTATE of the form 38xxx was returned to DB2 by routine "<routine-name>" (specific name "<specific-name>"), along with message text "<text>". The routine could be a user-defined function or a user-defined method.

User Response: The user will need to understand the meaning of the error. See your Database Administrator, or the author of the routine.

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 routine).

SQL0444N Routine "<routine-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>".

Explanation: The DBMS is trying to access the body of the code that implements routine "<routine-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 routine 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 routine 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 routine.)

User Response: Given for each reason code:

1

Path name "<library-or-path>" is longer than the maximum (255 bytes). Either the routine 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 routine creator or your database administrator. The routine definition or the location of the routine itself will need to be corrected.

4

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

In OS/2 this reason code can arise if the UDF DLL name is longer than format (8.3). For example a name of "abcdefgh99.dll" has format (10.3) and would result in this message with reason code 4. The solution is to change the name to an acceptable form, for example "abcdef99.dll".

In addition to the above, this reason code can result if the routine 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, routine 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 or one or more symbols could not be resolved. Contact the routine creator or your database administrator to make sure that the library was correctly linked. All required libraries to resolve referenced symbols such as external functions must be available. If a lack of memory is determined then 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 routine creator or your database administrator. The routine 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 routine creator or your database administrator. The routine 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 routine 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 routine definition statement does not specify a full path, or the function does not exist in the instance_name\function directory, necessitating a search using the PATH environment variable. For example, If the PATH contains a LAN drive prior to the directory containing your function and the DB2 instance is running under the SYSTEM account, this reason code can result.

other:

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

If unable to diagnose the error using the information in this message, the diagnostic log file db2diag.log contains failure information which may help isolate the problem. It may be necessary to contact your system administrator for assistance.

Federated system users: if this user defined function is a function template (and thus no code is required to reside on the federated server), 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.

Explanation: 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 routine (user-defined function (UDF) or method), and is being transformed because of a CAST FROM specification in the routine definition, or because the UDF is sourced on another function and the result needed to be transformed.

User Response: 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>".

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

User Response: 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 defining routine "<routine-name>". The maximum number of allowable parameters (90 for user defined functions and methods, 32767 for stored procedures) has been exceeded.

Explanation: Too many parameters were specified when defining routine "<routine-name>". The routine definition statement may be a CREATE FUNCTION, CREATE PROCEDURE, CREATE TYPE (method definition), or ALTER TYPE (method definition).

User Response: Change the statement to include fewer parameters.

sqlcode: -448

sqlstate: 54023

SQL0449N The statement defining routine "<routine-name>" contains an invalidly formatted library/function identification in the EXTERNAL NAME clause.

Explanation: An error was found in the EXTERNAL NAME clause of the CREATE statement for user defined function (UDF), user-defined method, or stored procedure "<routine-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 (for example, (on AIX) /u/slick/udfs/math or (on OS/2) d:\myfunc\math)
a file name (for example, 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 routine.

User Response: 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 Routine "<routine-name>" (specific name "<specific-name>") has generated a result value, SQLSTATE value, message text, or scratchpad which is too long.

Explanation: Upon return from routine "<routine-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 routine 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 routine, 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 Routine must conform to the requirement for the data type. Refer to the Application Development 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 (length declared on CREATE FUNCTION).

This is not permitted.

This error will also be returned if the length field of the scratchpad is altered by the routine.

User Response: See your Database Administrator, or the author of the routine.

sqlcode: -450

sqlstate: 39501

SQL0451N The "<data-item>" definition, in the statement that defines routine "<routine-name>", contains a data type "<type>" that is not appropriate for a non-sourced routine written in the given language.

Explanation: An error was made in the "<data-item>" part of the statement defining routine "<routine-name>". The user's statement contained the invalid type "<type>", or it contained a user defined type (UDT) which is based on the invalid type "<type>". The routine definition may be a CREATE FUNCTION, CREATE PROCEDURE, CREATE TYPE (method definition), or ALTER TYPE (method definition).

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

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

Changing the routine definition to a supported type; for example, from DECIMAL to FLOAT. This may involve a change in the routine body itself, and may also involve use of cast functions upon use of the routine.
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>".

Explanation: 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.

User Response:

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 statement that defines the routine "<routine-name>".

Explanation: A problem casting the result of routine "<routine-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.

User Response: 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 definition for routine "<routine-name>" matches the signature of some other routine that already exists in the schema or for the type.

Explanation: The signature of 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 method consists of the method name, the subject type of the method, the number of parameters defined for the method, 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, either:

there is a function or procedure ("<routine-name>") already in the schema having the same signature as the function or procedure being created
there is a method ("<routine-name>") for the subject type having the same signature as the method specification being added or the method body being created

The statement cannot be processed.

User Response: Determine if the existing routine already provides the functionality desired. If not, then the signature of the routine will have to be changed; for example, by changing the routine name.

sqlcode: -454

sqlstate: 42723

SQL0455N In routine "<routine-name>", the schema name "<schema-name1>" provided for the SPECIFIC name does not match the schema name "<schema-name2>" of the routine.

Explanation: 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 "<routine-name>". Note that the "<schema-name2>" portion of "<routine-name>" may have been specified directly or it may have defaulted to the authorization ID of the statement. If the routine is a method, "<schema-name>" refers to the schema name of the subject type of the method.

User Response: Correct the statement and try again.

sqlcode: -455

sqlstate: 42882

SQL0456N In the definition of routine "<routine-name>", the SPECIFIC name "<specific-name>" already exists in the schema.

Explanation: The user has supplied an explicit SPECIFIC name "<specific-name>" in the definition of routine "<routine-name>", but this name already exists as the SPECIFIC name for a function, method, or procedure in the schema.

User Response: Choose a new SPECIFIC name and try again.

sqlcode: -456

sqlstate: 42710

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

Explanation: The user-defined function, method, user-defined data type, or structured 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.

User Response: Select a name for the function, method, 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 routine "<routine-name>" by signature, a matching routine could not be found.

Explanation: In a reference to function, method, or stored procedure "<routine-name>" by signature, no matching function, method, or stored procedure could be found.

If a data type is used that can accept a parameter, then the type parameter is optional. For example, for CHAR(12), you can either 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 tell the DBMS to ignore data type parameters when 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, RAISE_ERROR, TYPE_ID, TYPE_NAME, TYPE_SCHEMA, or VALUE built-in functions.

The statement cannot be processed.

User Response: 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>".

Explanation: 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.

User Response: 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 Routine "<routine-name>" (specific name "<specific-name>") has returned a warning SQLSTATE, with diagnostic text "<text>".

Explanation: An SQLSTATE of the form 01Hxx was returned to DB2 by routine "<routine-name>" (with specific name "<specific-name>"), along with message text "<text>".

User Response: The user will need to understand the meaning of the warning. See your database administrator, or the author of the routine.

sqlcode: +462

sqlstate: 01Hxx

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

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

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

sqlcode: -463

sqlstate: 39001

SQL0464W Procedure "<proc>" returned "<num>" query result sets, which exceeds the defined limit "<integer>".

Explanation: The stored procedure named by proc completed normally. However, the stored procedure exceeded the defined limit on the number of query result sets the procedure can return.

num: identifies the number of query result sets returned by the stored procedure.
integer: identifies the defined limit on the number of query result sets for the stored procedure.

Only the first integer query result sets are returned to the SQL program that issued the SQL CALL statement.

The possible causes are as follows: The stored procedure is unable to return num result sets due to the DRDA limitations imposed by the client. The DRDA client establishes this limit with the MAXRSLCNT DDM code point.

User Response: The SQL statement is successful. The SQLWARN9 field is set to 'Z'.

sqlcode: +464

sqlstate: 0100E

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

Explanation: There is a system related problem involved with the running of a fenced mode routine (user-defined function or method). The exact nature of the problem is indicated by "<code>". This is NOT a user problem. Possible reason codes are:

Routine 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 routine 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:: Routine process dies.
30:: Routine process catches an USER INTERRUPT signal.
31:: Failure to unload routine module.
32:: Failure to allocate storage for control blocks used in module loading/unloading.
33:: Failure to send SIGINT from agent process to routine process.
34:: Failure to initialize OLE library.
35:: Failure to initialize OLE DB Initialization Service Component.
40:: Internal error occurred in routine process.

Agent process errors

41:: Failure to spawn routine 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 routine process the access permission to the UDFP shared memory set.
48:: Failure to grant the routine 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 routine code or agent code.
51:: Agent process catches USER INTERRUPT while running un-fenced routine code.
60:: Internal error occurred in routine process.

User Response: Contact your Database or System administrator.

sqlcode: -465

sqlstate: 58032

SQL0466W The procedure "<procedure-name>" returns "<number-results>" result sets from the stored procedure.

Explanation: This message is returned as a result of issuing a CALL SQL statement. It indicates that the stored procedure "<procedure-name>" has "<number-results>" result sets associated with it.

The statement completed successfully.

User Response: None required.

sqlcode: +466

sqlstate: 0100C

SQL0467W The procedure "<procedure-name>" includes another result set. There are a total of "<number-results>" result sets.

Explanation: This message is returned as a result of closing a cursor. It indicates that another result set exists for stored procedure "<procedure-name>" and the cursor was re-opened on the next result set. There are a total of "<number-results>" result sets from the stored procedure.

The statement completed successfully.

User Response: None required. Fetches can proceed on to the next result set.

sqlcode: +467

sqlstate: 0100D

SQL0469N The parameter mode (IN, OUT, or INOUT) is not valid for a parameter in procedure "<procedure-name>" with specific name "<specific-name>" (parameter number "<number>", name "<parameter-name>").

Explanation: One of the following errors occurred:

a parameter in an SQL procedure is declared as OUT and is used as input in the procedure body
a parameter in an SQL procedure is declared as IN and is modified in the procedure body.

User Response: Change the attribute of the parameter to INOUT, or change the use of the parameter within the procedure.

sqlcode: -469

sqlstate: 42886

SQL0470N The user defined routine "<routine-name>" (specific name "<specific-name>") has a null value for argument "<argument>" that could not be passed.

Explanation: A routine has an input argument with a null value but was defined with a parameter style that does not pass null indicators, or the datatype of this parameter does not support null values.

User Response: If the routine is to be called with null values, ensure the parameter style and input types are capable of accepting a null value. For functions, the function could also be created with "RETURNS NULL ON NULL INPUT".

sqlcode: -470

sqlstate: 39004

SQL0471N Invocation of routine "<name>" failed due to reason "<reason-code>".

Explanation: A routine "<name>" was invoked on a DB2 Universal Database for OS/390 server. The routine invocation failed because of the condition described by DB2 reason code "<reason-code>".

The statement cannot be executed. A DSNX9xx message describing the error might be displayed on the MVS system console.

User Response: Consult the documentation for the DB2 Universal Database for OS/390 server and correct the condition described by the DB2 reason code.

sqlcode: -471

sqlstate: 55023

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

Explanation: 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.

User Response: 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>".

Explanation: 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.

User Response: 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 routine "<function-name>" was made without a signature, but the routine is not unique in its schema.

Explanation: 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. If the routine is a method, a reference without a signature is permitted but this named method must be unique for the data type.

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.

Federated system users: For a pass-through session, 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.

User Response: Correct the reference by one of the following:

completing the signature
using the SPECIFIC name of the desired routine
changing the SQL 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.

Explanation: 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

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

sqlcode: -478

sqlstate: 42893

SQL0480N The procedure "<procedure-name>" has not yet been called.

Explanation: Either the procedure identified in an ASSOCIATE LOCATORS statement has not yet been called within the application process, or the procedure has been called, but an explicit or implicit commit occurred before the statement.

User Response: Correct the statements so that the exact syntax used to specify the procedure name on the CALL statement is the same as that on the ASSOCIATE LOCATORS statement. If an unqualified name is used to CALL the procedure, the one-part name must also be used in the other statements. Reissue the statements.

sqlcode: -0480

sqlstate: 51030

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

Explanation: 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.

User Response: 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.

Explanation: 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.

User Response: 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.

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

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

sqlcode: -486

sqlstate: 42991

SQL0487N Routine "<routine-name>" (specific name "<specific-name>") attempted to execute an SQL statement.

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

User Response: Remove any SQL statements then recompile the program. Investigate the level of SQL allowed as specified on the statement defining the routine.

sqlcode: -487

sqlstate: 38001

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

Explanation: 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.

User Response: Correct the function name or remove the use of the function.

sqlcode: -489

sqlstate: 42844

SQL0491N The definition of routine "<routine-name>" must have a RETURNS clause, and one of: the EXTERNAL clause (with other required keywords); the RETURN statement; or the SOURCE clause.

Explanation: A required clause is missing in the definition of routine "<routine-name>". If EXTERNAL was specified, the following must also be specified: LANGUAGE, PARAMETER STYLE, DETERMINISTIC or NOT DETERMINISTIC, NO SQL, and EXTERNAL ACTION or NO EXTERNAL ACTION.

User Response: 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.

Explanation: 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.

User Response: 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 Routine "<routine-name>" (specific name "<specific-name>") has returned a date, time or timestamp value which is syntactically or numerically invalid.

Explanation: The body of user defined function (UDF) or method "<routine-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.

User Response: The routine needs fixing. See your DBA, or the author of the routine.

sqlcode: -493

sqlstate: 22007

SQL0495N Estimated processor cost of "<estimate-amount1>" processor seconds ("<estimate-amount2>" service units) in cost category "<cost-category>" exceeds a resource limit error threshold of "<limit-amount>" service units.

Explanation:

The prepare of a dynamic INSERT, UPDATE, DELETE, or SELECT SQL statement resulted in a cost estimate that exceeded the error threshold value specified in the resource limit specification table (RLST).

This error is also issued if DB2's cost category value was "B", and the default action specified in the RLF_CATEGORY_B column in the RLST is to issue an error.

estimate_amount1: The cost estimate (in processor seconds) if the prepared INSERT, UPDATE, DELETE or SELECT statement were to be executed.
estimate_amount2: The cost estimate (in service units) if the prepared INSERT, UPDATE, DELETE or SELECT statement were to be executed.
cost-category: DB2's cost-category for this SQL statement. The possible values are A or B.
limit-amount: The error threshold (in service units) specified in the RLFASUERR column of the RLST.

The prepare of the dynamic INSERT, UPDATE, DELETE, or SELECT statement was unsuccessful.

User Response: If this SQLCODE was returned because the cost category value is "B", it might be that the statement is using parameter markers or that some statistics are not available for the referenced tables and columns. Make sure the administrator has run the utility RUNSTATS on the referenced tables. It might also be that UDFs will be invoked when the statement is executed, or for INSERT, UPDATE, or DELETE statements that triggers are defined on the changed table. Check the DSN_STATEMNT_TABLE or the IFCID 22 record for this statement to find the reasons this SQL statement has been put in cost category "B". If the program cannot be changed, or if statistics cannot be obtained, ask the administrator to change the value in the RLF_CATEGORY_B column in the RLST to "Y" which allows the statement to execute or "W" which returns a warning instead of an error.

If the warning is caused by an SQL statement that is consuming too much processor resource, attempt to rewrite the statement to perform more efficiently. Another option is to ask the administrator to increase the error threshold value in the RLST.

sqlcode: -495

sqlstate: 57051

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

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

User Response: 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 ]