Warnings in SQLSTATE

When the database server executes an SQL statement successfully, but encounters a warning condition, it sets the class code of SQLSTATE to "01". The subclass code then indicates the cause of the warning. This warning can be either of the following types:
  • An ANSI or X/Open warning message has a subclass code in the range "000" to "006".

    The CLASS_ORIGIN and SUBCLASS_ORIGIN exception fields of the diagnostics area have a value of "ISO 9075" to indicate ANSI or X/Open as the source of the warning.

  • A warning message specific to HCL OneDB™ has a subclass code in the range "I01" to "I11" (see SQL statements that set a warning specific to HCL OneDB for a given condition).

    The CLASS_ORIGIN and SUBCLASS_ORIGIN exception fields of the diagnostics area have a value of "IX000" to indicate an exception, which is specific to HCL OneDB, as the source of the warning.

  • A user-defined warning message from a user-defined routine has a subclass code of "U01".

    The CLASS_ORIGIN and SUBCLASS_ORIGIN exception fields of the diagnostics area have a value of "U0001" to indicate a user-defined routine as the source of the warning.

The following table lists the warning messages specific to HCL OneDB and the SQL statements and conditions that generate the warning.
Table 1. SQL statements that set a warning specific to HCL OneDB for a given condition
Warning value SQL statement Warning condition
"01I01" CONNECT

CREATE DATABASE

DATABASE

SET CONNECTION

Your application opened a database that uses transactions.
"01I03" CONNECT

CREATE DATABASE

DATABASE

SET CONNECTION

Your application opened an ANSI-compliant database.
"01I04" CONNECT

CREATE DATABASE

DATABASE

SET CONNECTION

Your application opened a database that the HCL OneDB manages.
"01I05" CONNECT

CREATE DATABASE

DATABASE

SET CONNECTION

Your application opened a database that is on a host database server that requires float-to-decimal conversion for FLOAT columns (or smallfloat-to-decimal conversions for SMALLFLOAT columns).
"01I06" All statements The statement executed contains the HCL OneDB extension to SQL (only when the DBANSIWARN environment variable is set).
"01I07" PREPARE

DESCRIBE

A prepared UPDATE or DELETE statement has no WHERE clause. The operation affects all rows of the table.
"01I09" FETCH

SELECT...INTO

EXECUTE...INTO

The number of items in the select list does not equal the number of host variables in the INTO clause.
"01I10" CONNECT

CREATE DATABASE

DATABASE

SET CONNECTION

The database server is currently running in secondary mode. The database server is a secondary server in a data-replication pair; therefore, the database server is available only for read operations.
"01I11" Other statements (when your application activates the DATASKIP feature) A data fragment (a dbspace) was skipped during query processing.

To check for a warning, your code only needs to verify the first two characters of SQLSTATE. However, to identify the particular warning, you need to examine the subclass code. You might also want to use the GET DIAGNOSTICS statement to obtain the warning message from the MESSAGE_TEXT field.

For example, the following block of code determines what database a CONNECT statement has opened.
int trans_db, ansi_db, online_db = 0;
;

msg = "CONNECT stmt";
EXEC SQL connect to 'stores7';
if(!strncmp(SQLSTATE, "02", 2)) /* < 0 is an error */
   err_chk(msg);
if (!strncmp(SQLSTATE, "01", 2))
   {
   if (!strncmp(SQLSTATE[2], "I01", 3))
      trans_db = 1;
   if (!strncmp(SQLSTATE[2], "I03", 3)) 
      ansi_db = 1;
   if (!strncmp(SQLSTATE[2], "I04", 3)) 
      online_db = 1;
   }

The preceding code fragment checks SQLSTATE with the system strncmp() function. The getdiag sample program (Guide to the getdiag.ec file) uses the sqlstate_err() function to check the success of an SQL statement by comparing the first two characters of SQLSTATE with the string "01". For more information about the values of SQLSTATE that the CONNECT, CREATE DATABASE, DATABASE, and SET CONNECTION statements set, see Determine features of the database server.