BULK ON and BULK OFF are not executable statements. They can only be embedded in a host language.
With any SQL error, a nonzero SQLCODE is returned. The SQLERRD[3] field of the SQLCA indicates the first statement to fail in the bulk interval. You can also terminate the interval with a COMMIT. The COMMIT in this case is not executed, and you can choose to re-issue the COMMIT (not recommended) or ROLLBACK the entire bulk upload interval.
SQLCA information is loaded after each SQL call. The status of the SQLCA is indicated by the SQLERRD[3] field:
SQLERRD[3] = -1
Inactive SQLCA. A pseudo-SQLCA is being sent to the client because the client application is in the middle of a bulk upload interval.
SQLERRD[3] = 0
Active SQLCA. Indicates a successful return of all SQLCAs for all SQL statements in the bulk upload interval. Along with the SQLCODE, shows the status of the bulk process.
SQLERRD[3] > 0
Active SQLCA. An error has occurred in this bulk interval. SQLERRD[3] is indicating the number of SQL statements that qualify for failure based on the first SQL statement failure. SQLCODE shows the SQL error for the failing SQL statement.
In the following scenario the Bulk Interval is set to a value of 10, with the third SQL statement in error:
SQLERRD[3] = 8
Indicates that eight SQL statements (the third through the tenth) are in doubt (with the first two being all right).
SQLCODE = -551
Indicates that an authorization error occurred on the third (or failed) SQL statement.
You could choose to ROLLBACK this bulk process, and re-execute (using BULK OFF) to monitor each failure.
The following steps are recommended for turning off bulk upload mode, and checking for errors:
EXEC SQL BULK OFF EXEC SQL COMMIT
Finally, test the SQLCODE value for errors. If errors are found, issue a ROLLBACK.
Description
The BULK ON command optimizes communications between the mainframe and XDB Link. BULK ON sets internal indicators allowing XDB Link to operate in Bulk Upload mode -- whereby INSERT, UPDATE and DELETE statements are sent to the mainframe as usual, but the SQLCA response buffers are not sent back to the XDB Link until a bulk interval has been reached. This reduction in the number of SQLCA responses optimizes upload performance by reducing the number of LU6.2 line turn-around events associated with the upload procedure.
The bulk interval for the upload is determined by the Upload Pacing Limit value specified in the Location Configuration dialog box in the Gateway Profile Utility or by a COMMIT statement. If, for example, the interval is set to 10, then every tenth SQL upload statement (such as INSERT, UPDATE and DELETE) contains an active SQLCA indicating the true SQLCODE for the bulk process.
Bulk Upload mode can be deactivated by issuing a BULK OFF statement, which terminates Bulk Upload mode for all subsequent transactions. If an SQL error has occurred in the bulk upload interval, it is returned in the next live SQLCA. A live SQLCA (reporting the true status of the bulk interval) is returned either at the end of a bulk interval or with the next statement following a BULK OFF command.
A live SQLCA is also returned if a SELECT statement is inadvertently issued in the middle of a bulk upload process. It must be noted that this SQLCA is reporting the status of the bulk interval, and not the SELECT statement.