Log Translation utility (dbtran)

With the Log Translation utility, you can translate a transaction log into a .sql command file.

You access the Log Translation utility at the command prompt, using the dbtran command. This is useful for incorporating into batch or command files.

Syntax

Running against a transaction log.

dbtran [	options ] transaction-log [ SQL-file ]

Running against a database server.

dbtran [ options ]

Parameters

Option	Description
–a	Include uncommitted transactions
–c "keyword=value; …"	Supply database connection parameters—cannot be used with a transaction log name
–d	Display output in chronological order
–ek key	Specify encryption key
–ep	Prompt for encryption key
–f	Output only since the last checkpoint
–g	Include audit records in output
–ir offset1,offset2	Include only the portion of the log between the two specified offsets
–is source,…	Include only rows originating from the specified sources
–it user.table,…	Include only operations on specified tables by specifying a comma-delimited list of user.table
–j date/time	Output from the last checkpoint prior to the given time
–m	Specify transaction logs directory (requires `-n` option)
–n filename	Output SQL file, when used against a database server
–o filename	Log output messages to a file
–q	Run quietly, do not print messages
–r	Remove uncommitted transactions (default)
–rsu username,…	Override default Replication Server user names
–s	Produce ANSI standard SQL UPDATE transactions
–sr	Generate SQL Remote comments
–t	Include trigger-generated transactions in output
–u userid,…	Translate transactions for listed users only
–x userid,…	Exclude transactions for listed users
–y	Replace file without confirmation
–z	Include trigger-generated transactions as comments only
Transaction-log	Log file to be translated—cannot be used together with `–c` or `-n`
SQL-file	Output file containing the translated information—for use with transaction-log only

Usage

The dbtran utility takes the information in a transaction log and places it as a set of SQL statements and comments into an output file. The utility can be run in the following ways:

Against a database server Run in this way, the utility is a standard client application. It connects to the database server using the connection string specified following the -c option, and places output in a file specified with the -n option. DBA authority is required to run in this way.

The following command translates log information from the server asiqdemo and places the output in a file named asiqdemo.sql.

dbtran -c "eng=asiqdemo;dbn=asiqdemo;uid=DBA;pwd=SQL" -n asiqdemo.sql

Against a transaction log file Run in this way, the utility acts directly against a transaction log file. You should protect your transaction log file from general access if you wish to prevent users from having the capability of running this statement.
```
dbtran asiqdemo.log asiqdemo.sql
```

When the dbtran utility runs, it displays the earliest log offset in the transaction log. This can be an effective method for determining the order in which multiple log files were generated.

Exit codes are 0 (success) or non-zero (failure).

For more information about the Log translation utility options, see “Options”.

This utility accepts @filename parameters. For more information, see “Server command-line switches”.

Options

Include uncommitted transactions (–a) The transaction log contains any changes made before the most recent COMMIT by any transaction. Changes made after the most recent commit are not present in the transaction log.

Connection string (–c) When running the utility against a database server, this parameter specifies the connection string.

DBA authority is required to run dbtran.

For a description of the connection parameters, see “Connection parameters” in Chapter 4, “Connection and Communication Parameters” of the Sybase IQ System Administration Guide.

Display output in chronological order (–d) Transactions are displayed in order from earliest to latest. This feature is provided primarily for use when auditing database activity: the output of this command should not be applied against a database.

Specify encryption key (–ek) This option allows you to specify the encryption key for strongly encrypted databases directly in the command. If you have a strongly encrypted database, you must provide the encryption key to use the database or transaction log in any way.

For strongly encrypted databases, you must specify either -ek or -ep, but not both. The command will fail if you do not specify a key for a strongly encrypted database.

If you are running against a database server (using the -c option), make sure you specify the key using a connection parameter, not using the -ek option. For example, the following command gets the transaction log information about database enc.db from engine sample, and saves its output in log.sql.

dbtran -n log.sql -c eng=sample;dbf=enc.db;uid=dba;pwd=sql;dbkey=mykey

Prompt for encryption key (–ep) This option allows you to specify in the command that you want to be prompted for the encryption key. This option causes a dialog box to appear, in which you enter the encryption key. It provides an extra measure of security by never allowing the encryption key to be seen in clear text.

For strongly encrypted databases, you must specify either -ek or -ep, but not both. The command will fail if you do not specify a key for a strongly encrypted database.

If you are running against a database server (using the -c option), make sure you specify the key using a connection parameter, not using the -ep option. For example, the following command gets the transaction log information about database enc.db from engine sample, and saves its output in log.sql.

dbtran -n log.sql -c eng=sample;dbf=enc.db;uid=dba;pwd=sql;dbkey=mykey

Output from last checkpoint only (–f) Only transactions that were completed since the last checkpoint are output.

Include audit information (–g) If the AUDITING database option is turned on, auditing information is added to the transaction log. You can include this information as comments in the output file using this option.

For more information, see “AUDITING option [database]” in Chapter 2, “Database Options” of the Sybase IQ Reference Manual.

Include rows from specified sources (–is) Isolate operations on rows that have been modified by operations from one or more of the following sources, specified as a comma-separated list:

All All rows. This is the default setting.

SQLRemote Include only rows that were modified using SQL Remote. You can also use the short form SR.

RepServer Include only rows that were modified using the Replication Agent (LTM) and Replication Server. You can also use the short form RS.

Local Include only rows that are not replicated.

Include offset range (–ir) Isolate a portion of the transaction log between two specified offsets.

Include specified tables (–it) Isolate those operations on the specified, comma-separated list of tables. Each table should be specified as owner.table.

Output from the last checkpoint prior to a given date (–j) Only transactions from the most recent checkpoint prior to the given date and/or time are translated. The user-provided argument can be a date, time or date and time enclosed in quotes. If the time is omitted, the time is assumed to be the beginning of the day. If the date is omitted, the current day is assumed. The following is an acceptable format for the date and time: “YY/MMM/DD HH:NN”.

Transaction logs directory (–m) Use this option to specify a directory that contains transaction logs. This option must be used in conjunction with the -n option.

Output file (–n) When you run the dbtran utility against a database server, use this option to specify the output file that holds the SQL statements.

Log output messages to file (–o) Write output messages to the named file.

Operate quietly (–q) Do not display output messages. This option is available only when you run this utility from the command prompt.

Do not include uncommitted transactions (–r) Remove any transactions that were not committed. This is the default behavior.

Override Replication Server user names (–rsu) By default, the -is option assumes the default Replication Server user names of dbmaint and sa. You can override this assumption using the -rsu option with a comma-separated list of user names.

Generate ANSI standard SQL UPDATE (–s) If the option is not used, and there is no primary key or unique index on a table, the Translation utility generates UPDATE statements with a non-standard FIRST keyword in case of duplicate rows. If the option is used, the FIRST keyword is omitted for compatibility with the SQL standard.

Generate SQL Remote comments (–sr) Place generated comments in the output file describing how SQL Remote distributes operations to remote sites.

Include transactions generated by triggers (–t) By default, actions carried out by triggers are not included in the command file. If the matching trigger is in place in the database, when the command file is run against the database, the trigger will carry out the actions automatically. Trigger actions should be included if the matching trigger does not exist in the database against which the command file is to be run.

Output transactions for listed users only (–u) This option allows you to limit the output from the transaction log to include only specified users.

Output transactions except for listed users (–x) This option allows you to limit the output from the transaction log to exclude specified users.

Operate without confirming actions (–y) Choosing this option automatically replaces existing command file(s) without prompting you for confirmation.

Include transactions generated by triggers as comments only (–z) Transactions that were generated by triggers will be included only as comments in the output file.