The SAVE statement saves statements in the replica for propagation to the master. The SAVE statement is applicable only in advanced replication configurations. It can only be issued on the replica databases.
The statements of a transaction that need to be propagated to the master database must be explicitly saved to the transaction queue of the replica database. Adding a SAVE statement before the transaction statements does this.
Only master users are allowed to save statements. When the saved statements are executed on the master, they must be executed using the appropriate access rights of a user on the master. The saved statements are executed in the master database using the access rights of the master user that was active in the replica when the statement was saved. If a user in the replica was mapped to a user in the master, the SAVE statement uses the access rights of the user in the master.
The default behavior for error handling with transaction propagation is that a failed transaction halts execution of the message; this aborts thecurrently-executing transaction and prevents execution of any subsequent transactions that are in that same message. However, you can choose a different error-handling behavior.
NO CHECK means that the statement is not prepared in the replica. This option is useful if the command would not make sense on the replica. For example, if the SQL command calls a stored procedure that exists on the master but not on the replica, then you do not want the replica to try to prepare the statement. If you use this option, then the statement can not have parameter markers.
IGNORE_ERRORS means that if a statement fails while executing on the master, then the failed statement is ignored and the transaction is aborted. However, only the transaction, not the entire message, is aborted. The master continues executing the message, resuming with the first transaction after the failed one.
LOG_ERRORS means that if a statement failed while executing on the master, then the failed statement is ignored and the current transaction is aborted. The failed transaction’s statements are saved in SYS_SYNC_RECEIVED_STMTS system table for later execution or investigation. The failed transactions can be examined using SYNC_FAILED_MESSAGES system view and they can be re-executed from there using MESSAGE <msg_id> FROM REPLICA <replica_name> RESTART -statement.
Note As with the IGNORE_ERROR option, aborting the transaction does not abort the entire message. The master continues executing the message, resuming with the first transaction after the failed one.
FAIL_ERRORS means that if a statement fails, the master stops executing the message. This is the default behavior.
AUTOSAVE means that the statement is executed in the master and automatically saved for further propagation if the master is also a replica to some other master (that is, a middle-tier node).
AUTOSAVEONLY means that the statement is NOT executed in the master but instead is automatically saved for further propagation if the master is also a replica to some other master (that is, a middle-tier node).
Return values
Error code
Description
25001
Internal error
Master database has exceeded the database size limit required to save the statement.
25003
Cannot save SAVE statements
25070
Statement can be saved only for one master in transaction.
Examples
SAVE INSERT INTO mytbl (col1, col2) VALUES ('calvin', 'hobbes')
SAVE CALL SP_UPDATE_MYTBL('calvin_1', 'hobbes')
SAVE CALL SP_DELETE_MYTBL('calvin')
SAVE NO CHECK IGNORE_ERRORS insert into mytab values(1,2)