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, the member 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 sqlcaid 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, the member 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.
The member 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,
the member 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?
YES