MESSAGE GET REPLY

solidDB Help : solidDB reference : SQL: Statements : MESSAGE : MESSAGE GET REPLY

MESSAGE GET REPLY

Note Applies to only solidDB Advanced Replication configurations.

MESSAGE unique‑message‑name GET REPLY
   [TIMEOUT {FOREVER | seconds}]
   [COMMITBLOCK block‑size‑in‑rows]
   [{OPTIMISTIC | PESSIMISTIC}]
   [NO EXECUTE]

Access requirements

In replica: Valid master user or SYS_SYNC_ADMIN_ROLE

Usage

Use the MESSAGE GET REPLY statement to request a message from the master database (if a reply to a sent message has not been received by the MESSAGE FORWARD statement).

The MESSAGE GET REPLY statement can be executed only in replica databases.

If a reply message fails due to a communication error (and you are not using the COMMITBLOCK keyword), you can explicitly use the MESSAGE GET REPLY to resend the message.

Note When working with messages, autocommit mode must be switched off.

Parameters, clauses, keywords, and variables

▪ TIMEOUT: Defines how long the replica database waits for the reply message. If the message is not received within the timeout period, try executing the MESSAGE GET REPLY statement again and set the timeout to a larger value or FOREVER.

▪ COMMITBLOCK: Indicates the number of rows that are processed before the data is committed. If the reply message contains refreshes of large publications, include the COMMITBLOCK keyword in order to commit the data in more than one transaction and ensure good performance for the operation. It is recommended that there are no on-line users accessing the database when the COMMITBLOCK keyword is used.

Note If the execution of a reply message (that uses the COMMITBLOCK keyword) fails in the replica database, it cannot be re-executed. The failed message must be deleted from the replica database and refreshed from the master database.

▪ OPTIMISTIC | PESSIMISTIC: Include the PESSIMISTIC keyword so that the MESSAGE GET REPLY statement uses table-level pessimistic locking when it is initially executed (see Pessimistic and optimistic concurrency controls). If the PESSIMISTIC mode is specified, all other concurrent access to the table is blocked until the synchronization message has completed. If you use OPTIMISTIC mode, the MESSAGE GET REPLY statement might fail due to a concurrency conflict.

When a transaction acquires an exclusive lock to a table, the General.TableLockWaitTimeout parameter determines the wait period of the transaction after which the exclusive or shared lock is released, see General section.

▪ NO EXECUTE: Specify that, when the reply message reaches the master database, it is only read and stored for later execution. Otherwise, the reply message is downloaded from the master and executed in the same statement. Using NO EXECUTE reduces bottlenecks in communication lines by allowing reply messages for later execution in different transactions.

Result set

MESSAGE GET REPLY returns a result set table. The returned result set is the same as with the statement MESSAGE EXECUTE.

The columns of the result set are as follows:

Column name	Description
Partno	Message part number
Type	The type of result set row. Possible types are: 0: Message part start 1: This type is not in use 2: The message was a propagation message and the status of that operation is stored in the return message 3: Task 4: Subscription task 5: Type of refresh (FULL or INCREMENTAL) 6: MESSAGE DELETE status
Masterid	Master ID
Msgid	Message ID
Errcode	Message error code. Zero if successful.
Errstr	Message error string. NULL is successful.
Insertcount	Number of inserted rows to replica. Type=3: Total number of insert Type=4: Row inserts restored from replica history to replica base table Type=5: Insert operations received from master
Deletecount	Type = 3: Total number of deletes Type = 4: Row deletes restored from replica base table Type = 5: Delete operations received from master
Bytecount	Size of message in bytes. Indicated in result received from statement MESSAGE END. Otherwise 0.
Info	Information of the current task. Type = 0: then Message name Type = 3: Publication name Type = 4: Table name Type = 5: FULL/INCREMENTAL

Return values

Fatal errors in transaction propagation abort the message and return an error code to the replica. To propagate the aborted message, you need to correct the fatal errors and restart the message by using the MESSAGE FROM REPLICA EXECUTE statement, see MESSAGE FROM REPLICA EXECUTE.

If a REFRESH fails in the master database, an error message about the failed REFRESH is added to the result set. Other parts of the message are executed normally. The failed REFRESH must be refreshed from the master database in a separate synchronization message.

If a REFRESH (that is, the execution of the reply message) fails in the replica database, the message is still available in the replica database and can be restarted with the MESSAGE EXECUTE statement, see MESSAGE EXECUTE.

Return values from replica

Error code	Description
13XXX	Table level error
13124	User id num not found This message is generated, for example, if the user has been dropped.
10006	Concurrency conflict (simultaneous other operation)
13047	No privilege for operation
13056	Insert not allowed for pseudo column
21XXX	Communication error
25005	Message message‑name is already active
25013	Message name message‑name not found
25018	Illegal message state In the replica, the message can only be executed using the MESSAGE GET REPLY statement if the message is forwarded to the master.
25024	Master not defined
25026	Not a valid master user
25031	Transaction is active, operation failed
25035	Message message‑name is in use. A user is currently creating or forwarding this message.
25036	Publication publication‑name not found or publication version mismatch
25040	User id user‑id is not found While executing a message reply, an attempt to map a master user to a local replica id failed.
25041	Subscription to publication publication‑name not found
25048	Publication publication‑name request info not found
25054	Table table‑name is not set for synchronization history
25056	Autocommit not allowed
25057	Already registered to master master‑name
25060	Column column‑name does not exist on publication publication‑name resultset in table table‑name

Return values from master database

Error code	Description
13XXX	Table level error
13124	User id num not found This message is generated, for example, if the user has been dropped.
25012	Message reply timed out
25016	Message not found, replica id replica‑id, message id message‑id
25043	Reply message is too long (size‑of‑messages bytes). Maximum is set to max‑message‑size bytes.
25056	Autocommit not allowed