SQLIMSSTATE values and common error codes
IMS produces SQLIMSSTATE values when it works as an application server or requester.
The SQLIMSSTATE values are based on the SQLSTATE specifications contained in the standards: ISO/IEC 9075:1992, Database Language SQL and ANSI X3.135-1992 Database Language SQL.
SQLIMSSTATE values are returned to the application in the last five bytes of the SQLIMSCA. Each five-character value is a return code that indicates the outcome of the most recently executed SQL statement.
SQLIMSSTATE values are designed so that application programs can test for specific errors or classes of errors. The first character of an SQLIMSSTATE value indicates whether the SQL statement was executed successfully or unsuccessfully (equal to or not equal to zero, respectively).
SQLIMSSTATE values are comprised of a two-character class code value, followed by a three-character subclass code value. Class code values represent classes of successful and unsuccessful execution conditions. An application might define classes beginning with the characters '7' through '9' or 'I' through 'Z' and subclasses for any class beginning with the characters 'I' through 'Z'.
The following table identifies the SQLIMSSTATE class codes. Subsequent tables describe the subcodes for each class code.
| Class code | Meaning |
|---|---|
| 01 | Warning |
| 07 | Dynamic SQL Error |
| 22 | Data Exception |
| 23 | Constraint Violation |
| 24 | Invalid Cursor State |
| 26 | Invalid SQL Statement Identifier |
| 42 | Syntax Error or Access Rule Violation |
| 54 | SQL or Product Limit Exceeded |
| 56 | Miscellaneous SQL or Product Error |
| 57 | Resource Not Available or Operator Intervention |
| 58 | System Error |
| SQLIMSSTATE value | Meaning |
|---|---|
| 01004 | The value of a string was truncated when assigned to another string data type with a shorter length. |
| 07001 | The number of variables is not correct for the number of parameter markers. |
| 07003 | The statement identified in the EXECUTE statement is a select-statement, or is not in a prepared state. |
| 07005 | The statement name of the cursor identifies a prepared statement that cannot be associated with a cursor. |
| 22001 | Character data, right truncation occurred; for example, an update or insert value is a string that is too long for the column, or a datetime value cannot be assigned to a variable, because it is too small. |
| 22002 | A null value, or the absence of an indicator parameter was detected; for example, the null value cannot be assigned to a variable, because no indicator variable is specified. |
| 22003 | A numeric value is out of range. |
| 22018 | An argument to a numeric field cannot be a string value. |
| 23502 | An insert or update value is null, but the column cannot contain null values. |
| 23503 | The insert or update value of a foreign key is invalid. |
| 23504 | A foreign key cannot be updated. |
| 23600 | Specification of multiple PCBs in the same SQL query is invalid. |
| 23601 | Segments on different hierarchical paths cannot be joined. |
| 23602 | The segment length exceeds the specified length. |
| 23603 | The number of fields or segments in the statement is over the maximum. |
| 23604 | z/OS® XML parser API returned an error. |
| 23605 | Duplicate field names are invalid. |
| 23609 | The column is not of a valid data type for use in the function. |
| 23610 | The function does not support the specified data type. |
| 23611 | A field value did not conform to the format of the field data type. |
| 23612 | ORDER BY with an aggregate field is not supported. |
| 23613 | The field cannot be updated because it is overlapped by another field with a different data type. |
| 23614 | The specified field is not valid or was not found. |
| 24501 | The identified cursor is not open. |
| 24502 | The cursor identified in an OPEN statement is already open. |
| 24506 | The statement identified in the PREPARE is the statement of an open cursor. |
| 26501 | The statement identified does not exist. |
| 42000 | The identified field is not a sensitive field. |
| 42201 | Search filters for a segment in a WHERE clause must be sequential. |
| 42601 | A character, token, or clause is invalid or missing. |
| 42602 | A character that is invalid in a name has been detected. |
| 42604 | An invalid numeric or string constant has been detected. |
| 42605 | The number of arguments specified for a function is invalid. |
| 42606 | An invalid hexadecimal constant has been detected. |
| 42612 | The statement string is an SQL statement that is not acceptable in the context in which it is presented. |
| 42613 | Clauses are mutually exclusive. |
| 42614 | A duplicate keyword or clause is invalid. |
| 42615 | An invalid internal destination was detected. |
| 42622 | A name or label is too long. |
| 42702 | A column reference is ambiguous, because of duplicate names. |
| 42703 | An undefined column or parameter name was detected. |
| 42704 | A named object is not valid or was not found. |
| 42707 | A column name in ORDER BY does not identify a column of the result table. |
| 42711 | A duplicate column name was detected in the object definition or ALTER TABLE statement. |
| 42713 | A duplicate object was detected in a list or is the same as an existing object. |
| 42803 | A column reference in the identified clause is invalid |
| 42804 | The result expressions in a CASE expression are not compatible. |
| 42806 | A value cannot be assigned to a variable, because the data types are not compatible. |
| 42810 | A base table is not identified in a FOREIGN KEY clause. |
| 42811 | The number of columns specified is not the same as the number of columns in the SELECT clause. |
| 42815 | The data type, length, scale, value, or CCSID is invalid. |
| 42821 | A data type for an assignment to a column or variable is not compatible with the data type. |
| 42889 | The table already has a primary key. |
| 42895 | For static SQL, an input variable cannot be used, because its data type is not compatible with the parameter of a procedure or user-defined function. |
| 428B7 | A number specified in an SQL statement is out of the valid range. |
| 54001 | The statement is too long or too complex. |
| 54002 | A string constant is too long. |
| 54004 | The statement has too many table names or too many items in a SELECT or INSERT list. |
| 54025 | The table description exceeds the maximum size of the object descriptor. |
| 54035 | An internal object limit exceeded. |
| 56074 | Aggregation, function, and expression are not supported. |
| 56075 | The DISTINCT and ALL keywords are not supported. |
| 57001 | The table is unavailable, because it does not have a primary index. |
| 57006 | The object cannot be created, because a DROP or CREATE is pending. |
| 57013 | A non-database resource is not available. This will affect the successful execution of subsequent statements. |
| 58030 | IMS encountered an error and returned a PCB status code or an AIB return code and a reason code. |
