Call Level Interface Guide and Reference

Using Compound SQL

Compound SQL allows multiple statements to be grouped into a executable single block. This block of statements, together with any input parameter values, can then be executed in a single continuous stream, reducing the execution time and network traffic. Compound SQL is most often used to efficiently execute a series of INSERT, UPDATE and DELETE statements.

Any SQL statement that can be prepared dynamically, other than a query, can be executed as a statement inside a compound statement. Statements within a Compound SQL statement are referred to as sub-statements. Compound SQL does not guarantee the order in which the sub-statements are executed, therefore there must be no dependency between the statements.

Compound SQL statements cannot be nested. The authorization ID of the Compound SQL statement must be the appropriate authorization on all the individual sub-statements contained within the Compound SQL statement.

Compound SQL is supported when connected to DB2 Universal Database, or in DRDA environments with DB2 Connect V 2.3 or higher.

ATOMIC and NOT ATOMIC Compound SQL

A Compound SQL statement block is specified by surrounding the sub-statements by a BEGIN COMPOUND statement and an END COMPOUND statement. The BEGIN COMPOUND syntax is shown below:

>>-BEGIN COMPOUND--+-ATOMIC-----+---STATIC--+----------------------------------+->
                   '-NOT ATOMIC-'           '-STOP AFTER FIRST--?--STATEMENTS--'
 
>--------------------------------------------------------------><
 

ATOMIC

Specifies that, if any of the sub-statements within the Compound SQL statement fails, then all changes made to the database by any of the sub-statements are undone. ATOMIC is not supported in DRDA environments.

NOT ATOMIC

Specifies that, regardless of the failure of any sub-statements, the Compound SQL statement will not undo any changes made to the database by the other sub-statements.

STATIC

Specifies that input variables for all sub-statements retain their original value. If the same variable is set by more than one sub-statement, the value of that variable following the Compound SQL statement is the value set by the last sub-statement.

STOP AFTER FIRST ? STATEMENTS

Specifies that only a certain number of sub-statements are to be executed. If this clause is omitted, all the sub-statements are executed.

The END COMPOUND syntax is shown below:

>>-END COMPOUND---+--------+-----------------------------------><
                  '-COMMIT-'
 

Specifying the COMMIT option will commit all the sub-statements if they executed successfully. The COMMIT applies to the current transaction, including statements that precede the compound statement. If COMMIT is specified, and the connection is a coordinated distributed connection (SQL_COORDINATED_TRANS), an error will be returned (SQLSTATE of 25000).

If the COMMIT option is not specified after END COMPOUND, the sub-statements will not be committed unless the application is operating under auto-commit mode, in which case the commit will be issued at the END COMPOUND. For information on the auto-commit mode, refer to Commit or Rollback.

Figure 15 shows the general sequence of function calls required to execute a compound SQL statement. Note that:

• SQLPrepare() and SQLExecute() can be used in place of SQLExecDirect().
• The BEGIN COMPOUND and END COMPOUND statements are executed with the same statement handle.
• Each sub-statement must have its own statement handle.
• All statement handles must belong to the same connection, and have the same isolation level.
• The sub-statements should be, but do not need to be, prepared before the BEGIN COMPOUND statement, especially in DRDA environments where some optimization may be possible to reduce network flow.
• The statement handles must remain allocated until the END COMPOUND statement is executed.
• The only functions that may be called using the statement handles allocated for the compound sub-statements are:
  • SQLAllocHandle()
  • SQLBindParameter()
  • SQLBindFileToParam()
  • SQLParamData()
  • SQLPutData()
  • SQLExecDirect(), SQLPrepare(), SQLExecute()
• SQLTransact() cannot be called for the same connection, or any connect requests between BEGIN and END COMPOUND.
• The sub-statements may be executed in any order.
• SQLRowCount() (or SQLGetSQLCA()) can be called using the same statement handle as the BEGIN, END COMPOUND statement to get an aggregate count of the rows affected.

Figure 15. Compound SQL

Compound SQL Error Handling

If the compound statement is ATOMIC and the END COMPOUND SQLExecDirect() call returns:

• SQL_SUCCESS - all the sub-statements executed without any warnings or errors.
• SQL_SUCCESS_WITH_INFO - all the sub-statements executed successfully with one or more warnings. Call SQLError() to obtain generic diagnostic information, or call SQLGetSQLCA() to obtain the SQLCA for the entire compound SQL statement. The statement handle used for SQLError() or SQLGetSQLCA() must the same one used to process the BEGIN, END COMPOUND SQL.

  Most of the information in the SQLCA reflects values set by the database server when it processed the last sub-statement, such as the SQLCODE and SQLSTATE. If one or more error occurred and none of these are of a serious nature, the SQLERRMC field in the SQLCA will contain information on up to a maximum of seven of these errors.

• SQL_NO_DATA_FOUND - a BEGIN, END COMPOUND was executed without any sub-statements, or none of the sub-statement affected any rows.
• SQL_ERROR - one or more sub-statements failed, and all sub-statements were rolled back.

If the compound statement is NOT ATOMIC and the END COMPOUND SQLExecDirect() call returns:

• SQL_SUCCESS - all sub-statements executed without any errors.
• SQL_SUCCESS_WITH_INFO - the COMPOUND statement executed with one or more warnings. One or more sub-statements have returned an warning. Call SQLError() or SQLGetSQLCA() to receive additional information on the information on the warnings.
• SQL_NO_DATA_FOUND - a BEGIN, END COMPOUND was executed without any sub-statements, or none of the sub-statement affected any rows.
• SQL_ERROR - the COMPOUND statement failed. At least one sub-statement returned an error, examine the SQLCA to determine which statement(s) failed.

Compound SQL Example

The following example executes a compound statement consisting of 4 sub-statements to insert rows into a new AWARDS table.

/* ... */
    SQLCHAR * stmt[] = {
 
        "INSERT INTO awards (id, award) "
        "SELECT id, 'Sales Merit' from staff "
        "WHERE job = 'Sales' AND (comm/100 > years)",
 
        "INSERT INTO awards (id, award) "
        "SELECT id, 'Clerk Merit' from staff "
        "WHERE job = 'Clerk' AND (comm/50 > years)",
 
        "INSERT INTO awards (id, award) "
        "SELECT id, 'Best ' concat job FROM STAFF "
        "WHERE comm = (SELECT max(comm) FROM staff WHERE job = 'Clerk')",
 
        "INSERT INTO awards (id, award) "
        "SELECT id, 'Best ' concat job FROM STAFF "
        "WHERE comm = (SELECT max(comm) FROM STAFF WHERE job = 'Sales')",
 
    } ;
 
    SQLINTEGER i ;
 
/* ... */
    /* Prepare 4 substatements */
    for ( i = 1; i < 4; i++ ) {
        rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &cmhstmt[i] ) ;
        CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
        rc = SQLPrepare( cmhstmt[i], stmt[i], SQL_NTS ) ;
        CHECK_HANDLE( SQL_HANDLE_STMT, cmhstmt[i], rc ) ;
    }
 
    rc = SQLExecDirect( hstmt,
                        ( SQLCHAR * ) "BEGIN COMPOUND NOT ATOMIC STATIC",
                        SQL_NTS
                      ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    /* Execute 4 substatements */
    for ( i = 1; i < 4; i++ ) {
        rc = SQLExecute( cmhstmt[i] ) ;
        CHECK_HANDLE( SQL_HANDLE_STMT, cmhstmt[i], rc ) ;
    }
 
    /* Execute the COMPOUND statement (of 4 sub-statements) */
    printf( "Executing the COMPOUND statement (of 4 sub-statements)\n" ) ;
 
    rc = SQLExecDirect( hstmt,
                        ( SQLCHAR * ) "END COMPOUND COMMIT",
                        SQL_NTS
                      ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    rc = SQLFreeHandle( SQL_HANDLE_STMT, hstmt ) ;
    CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
 
    for ( i = 1; i < 4; i++ ) {
        rc = SQLFreeHandle( SQL_HANDLE_STMT, cmhstmt[i] ) ;
        CHECK_HANDLE( SQL_HANDLE_STMT, cmhstmt[i], rc ) ;
    }