The
sqlData system variable is a structured
record that contains a number of globally available fields. The record
has the following structure:
record sqlData type basicRecord
10 sqlca hex(272);
20 * hex(24);
20 sqlcode int;
20 * hex(4);
20 sqlerrmc char(70);
20 * hex(16);
20 sqlerrd int[6];
20 sqlwarn char(1)[11];
20 sqlstate char(5);
end
The same variables can also be found in their previous location
in the sysVar library. The variables in sysVar, however, have a scope
limited to the current program. For example, if programA calls programB
and both programs access a relational database, each program has its
own copy of sysVar.sqlData. The values
set in programB are not available after you return to programA. For
this reason, fully qualify the name of any of these variables that
you use, such as sqlLib.sqlData.sqlcode.
The record contains the following individual fields:
- sqlcode
- Contains a status code after your program accesses a relational
database.
- sqlerrmc
- The error message associated with sqlcode.
- sqlerrd
- These six INT variables provide the following diagnostic information:
- sqlerrd[1]
- If the SQL connection is invoked and successful, this contains
the maximum expected difference in length of mixed character data
(CHAR data types) when converted to the database code page from the
application code page. A value of 0 or 1 indicates no expansion; a
value greater than 1 indicates a possible expansion in length; a negative
value indicates a possible contraction. Upon successful return from
an SQL procedure, contains the return status value from the SQL procedure.
- sqlerrd[2]
- If the SQL connection is invoked and successful, this contains
the maximum expected difference in length of mixed character data
(CHAR data types) when converted to the application code page from
the database code page. A value of 0 or 1 indicates no expansion;
a value greater than 1 indicates a possible expansion in length; a
negative value indicates a possible contraction. If the SQLCA results
from a NOT ATOMIC compound SQL statement that encountered one or more
errors, the value is set to the number of statements that failed.
- sqlerrd[3]
- If PREPARE is invoked and successful, this field (or this element
in the array) contains an estimate of the number of rows to be returned.
After INSERT, UPDATE, DELETE, or MERGE, it contains the actual number
of rows that qualified for the operation. If compound SQL is invoked,
it contains an accumulation of all sub-statement rows. If CONNECT
is invoked, contains 1 if the database can be updated, or 2 if the
database is read only.
- If the OPEN statement is invoked, and the cursor contains SQL
data change statements, this field contains the sum of the number
of rows that qualified for the embedded insert, update, delete, or
merge operations.
- If CREATE PROCEDURE for an SQL procedure is invoked, and an error
is encountered when parsing the SQL procedure body, contains the line
number where the error was encountered. The sixth byte of sqlca must
be "L" for this to be a valid line number.
- sqlerrd[4]
- If PREPARE is invoked and successful , this contains a relative
cost estimate of the resources required to process the statement.
If compound SQL is invoked, it contains a count of the number of successful
sub-statements. If CONNECT is invoked, it contains one of the following
codes:
- 0 for a one-phase commit from a down-level client
- 1 for a one-phase commit
- 2 for a one-phase, read-only commit
- 3 for a two-phase commit
- sqlerrd[5]
- Contains the total number of rows deleted, inserted, or updated
as a result of both of the following actions:
- The enforcement of constraints after a successful delete operation.
- The processing of triggered SQL statements from an activated trigger.
If compound SQL is invoked, this field (or this element in the
array) contains an accumulation of the number of such rows for all
sub-statements. In some cases, when an error is encountered, this
field contains a negative value that is an internal error pointer.
- If CONNECT is invoked, sqlerrd[5] contains
one of the following authentication type values:
- 0
- Server authentication
- 1
- Client authentication
- 2
- Authentication using DB2 Connect™
- 4
- SERVER_ENCRYPT authentication
- 5
- Authentication using DB2
Connect with encryption
- 7
- KERBEROS authentication
- 8
- KRB_SERVER_ENCRYPT authentication
- 9
- GSSPLUGIN authentication
- 10
- GSS_SERVER_ENCRYPT authentication
- 255
- Unspecified authentication
- sqlerrd[6]
- For a partitioned database, contains the number of the partition
that encountered the error or warning. If no errors or warnings were
encountered, this field contains the partition number of the coordinator
node. The number in this field is the same as that specified for the
partition in the db2nodes.cfg file.
- sqlwarn
- A set of 11 warning indicators. If compound SQL is invoked, each
indicator contains an accumulation of the warning indicators set for
all sub-statements. This array contains the following indicators:
- sqlwarn[1]
- Contains one of the following values:
- [blank]
- All other indicators are blank.
- W
- At least one other indicator is not blank.
- sqlwarn[2]
- Contains one of the following values:
- A
- The CONNECT or ATTACH was successful, and the authorization name
for the connection is longer than 8 bytes.
- N
- The null terminator was truncated.
- P
- The PREPARE statement relative cost estimate stored in sqlerrd[4]
exceeded the value that can be stored in an INTEGER or was less than
1, and either the CURRENT EXPLAIN MODE or the CURRENT EXPLAIN SNAPSHOT
special register is set to a value other than NO.
- W
- The value of a string column was truncated when assigned to a
host variable.
- sqlwarn[3]
- Contains W if null values were eliminated from the argument of
a function. Also contains W if the last SQL I/O operation caused the
database manager to truncate character data fields because of insufficient
space in the host variables for the program. You can use logical expressions
to test whether the values in specific host variables were truncated.
For more information, see the references to trunc in
"Logical expressions for SQL records."
- When the host variable is a number, no truncation warning is given.
Fractional parts of a number are truncated with no indication. When
you use a DB2® database, if the
non-fractional part of a number does not fit into a user variable,
the database manager returns -304 in sqlcode.
- sqlwarn[4]
- Contains one of the following values:
- W
- The number of columns is not equal to the number of host variables.
- Z
- The number of result set locators specified in the ASSOCIATE LOCATORS
statement is less than the number of result sets returned by a procedure.
- sqlwarn[5]
- Contains W if a prepared UPDATE or DELETE statement does not include
a WHERE clause.
- sqlwarn[6]
- Reserved for future use.
- sqlwarn[7]
- Contains W if the result of a date calculation was adjusted to
avoid an impossible date.
- sqlwarn[8]
- If CONNECT is invoked and successful, contains E if the DYN_QUERY_MGMT
database configuration parameter is enabled. When you use a DB2 database, this field (or this
element in the array) contains W if an adjustment was made to correct
a result that was not valid from an arithmetic operation on date or
time values.
- sqlwarn[9]
- Contains W if a character that could not be converted was replaced
with a substitution character.
- sqlwarn[10]
- Contains W if arithmetic expressions with errors were ignored
during column function processing.
- sqlwarn[11]
- Contains W if there was a conversion error when converting a character
data value in one of the fields in the SQLCA.
- sqlstate
- A return code that indicates the outcome of the most recent SQL
statement.
Value saved across segmented converse?
NO