where the optional opt-using specification has the syntax:
USING(variable_list)
where variable_list contains a list of procedure variables or parameters separated by a comma. These variables are input parameters for the SQL statement. The SQL input parameters are marked with the standard question mark syntax in the prepare statement. If the SQL statement has no input parameters, the USING specification is ignored.
The optional opt_into specification has the syntax
INTO(variable_list)
where variable_list contains the variables that the column values of the SQL SELECT statement are stored into. The INTO specification is effective only for SQL SELECT statements.
After the execution of UPDATE, INSERT and DELETE statements an additional variable is available to check the result of the statement. Variable SQLROWCOUNT contains the number of rows affected by the last statement.
Using transactions
The following statement is used to terminate transactions:
EXECSQL {COMMIT | ROLLBACK} WORK
The following statement is used to control the type of transactions:
EXECSQLSETTRANSACTION {READONLY | READWRITE}
Closing and dropping cursors
When you are finished using a cursor, you can either CLOSE the cursor or CLOSE and DROP the cursor.
If you are likely to reuse the cursor and want to improve performance, you should only CLOSE the cursor. When you close a cursor, all the memory allocated during the execute phase is released but the cursor is kept in prepared state.
Dropping a cursor frees all allocated resources. The next time you use the dropped cursor, it needs to prepared.
Checking for errors
The result of each EXEC SQL statement executed inside a procedure body is stored into the variable SQLSUCCESS. This variable is automatically generated for every procedure. If the previous SQL statement was successful, a value one is stored into SQLSUCCESS. After a failed SQL statement, a value zero is stored into SQLSUCCESS.
is used to decrease the need for IF NOT SQLSUCCESS THEN tests after every executed SQL statement in a procedure. When this statement is included in a stored procedure all return values of executed statements are checked for errors. If statement execution returns an error, the procedure is automatically aborted. Optionally the transaction can be rolled back.
The error string of latest failed SQL statement is stored into variable SQLERRSTR.