sysadmin sqt_dump

Dumps the transaction cache for an inbound queue or a DSI queue.

Syntax

sysadmin sqt_dump_queue {, q_number | server[, database]}, 
        q_type, reader 
        [, {open | closed | read}] 
        [, num_cmds]
        [, {L0 | L1 | L2 | L3}]
        [, {RSSD | client | “log” | file_name}]

Parameters

q_number | server[, database] – Identifies the inbound queue or the DSI queue. Use either q_number or server[, database] to specify the queue number. You can use admin who, admin who, sqm, and admin who, sqt to identify the queue number.
q_type – The queue type of the stable queue. Values are 0 for outbound queues and 1 for inbound queues. Use admin who, admin who, sqm, and admin who, sqt to identify the queue type.
reader – Identifies the reader you want to dump the stable queue for. This parameter applies to features that require multiple readers, such as warm standby applications. You can get the reader number from admin sqm_readers or from admin who, sqt. If you are not using multiple readers, enter “0” for the reader.
open – Dumps only open transactions. If you use this option, insert a comma between q_type and the open flag.
closed – Dumps all the committed transactions found in the SQT cache.
read – Dumps all restored read transactions found in the SQT cache.
num_cmds – Specifies the number of commands to dump. Setting num_cmds to -1 dumps all of the commands in the SQT cache.
L0 – Dumps the all of the SQT cache’s content. This is the default behavior if L0, L1, L2, or L3 is not specified.
L1 – Dumps only the begin and end commands of the transactions found in the SQT cache.
L2 – Dumps the begin and end commands of the SQT cache transactions together with a shortened version of all other commands in the transactions.
L3 – Dumps everything in the cache. Except for SQL statements, all other commands are printed as comments. You can only use L3 when you use the file_name option or the sysadmin dump_file command to specify an alternate log file. You cannot use L3 with RSSD or client option.
RSSD – Forces the output to system tables in the RSSD.
client – Forces the output to the client issuing the command.
"log" – Forces the output to the Replication Server log file.
file_name – Forces the output into the alternate log file specified by file_name. The alternate log file can also be set using the sysadmin dump_file command. The location of this file is recorded in the Replication Server log.

Examples

Example 1 – Dumps all restored transactions in queue 103:1 from the transaction cache:
```
sysadmin sqt_dump_queue, 103, 1, 0
```
Example 2 – Dumps all restored transactions in the inbound queue for SYDNEY_DS.pubs2 from the transaction cache into the Replication Server log:
```
sysadmin sqt_dump_queue, SYDNEY_DS, pubs2, 1, 0
```
Example 3 – Dumps all restored open transactions in queue 103:1 from the transaction cache into the Replication Server log:
```
sysadmin sqt_dump_queue, 103,1, 0, open
```
Example 4 – Dumps all restored closed transactions in queue 103:1 from the transaction cache into the Replication Server log:
```
sysadmin sqt_dump_queue, 103,1, 0, closed
```
Example 5 – Dumps all restored read transactions in queue 103:1 from the transaction cache into the Replication Server log:
```
sysadmin sqt_dump_queue, 103,1, 0, read
```
Example 6 – Dumps the first 10 commands of restored transactions in queue 103:1 from the transaction cache into the Replication Server log:
```
sysadmin sqt_dump_queue, 103,1, 0, 10
```
Example 7 – Dumps the begin and end commands of all restored transactions in queue 103:1 from the transaction cache into the Replication Server log:
```
sysadmin sqt_dump_queue, 103,1, 0, L1
```
Example 8 – Dumps all restored transactions in queue 103:1 from the transaction cache into the Replication Server log. All the commands are truncated at 100 characters:
```
sysadmin sqt_dump_queue, 103,1, 0, L2
```
Example 9 – Dumps all restored transactions in queue 103:1 from the transaction cache into the SYDNEY_RS.log file:
```
sysadmin sqt_dump_queue, 103,1, 0, L3, SYDNEY_RS.log
```
Example 10 – Dumps all restored transactions in queue 103:1 from the transaction log into the RSSD:
```
sysadmin sqt_dump_queue, 103,1, 0, RSSD
```
Example 11 – Dumps all restored transactions in queue 103:1 from the transaction log to the client:
```
sysadmin sqt_dump_queue, 103,1, 0, client
```

Usage

Before using sysadmin sqt_dump_queue, execute admin who, sqt to make sure the transaction cache for the database exists.
This command dumps all the statements of transactions in the transaction cache.
sysadmin sqt_dump_queue dumps transaction statements into one of these:
- Replication Server log
- Alternate log file
- RSSD
- Client issuing the command
To dump transactions into the RSSD or client, the last argument of sysadmin sqt_dump_queue must be RSSD or client.

If an alternative log file for dumping transactions is specified through the sysadmin dump_file command or through the file_name option, the output goes into the alternative dump file.

If the RSSD or client option is not specified, or the log option is specified, output goes into the Replication Server log.
The output from the sysadmin sqt_dump_queue indicates the state of transactions in the transaction cache as open, closed, or read. Open transactions are transactions that do not have a commit yet. Closed transactions have a commit but have not been completely read out yet. Read transactions have been completely read out but have not been deleted yet.
You can modify the cache size by setting the configuration parameter, sqt_max_cache_size.

Permissions

sysadmin sqt_dump_queue requires “sa” permission.