load transaction

Syntax

load tran[saction] database_name
	from [compress::]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes, 
		dumpvolume = volume_name,
		file = file_name]
	[stripe on [compress::]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes,
		dumpvolume = volume_name,
		file = file_name]
	[[stripe on [compress::]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes, 
		dumpvolume = volume_name,
		file = file_name]]...]
	[with {
		density = density_value, 
		blocksize = number_bytes, 
		compression, 
		dumpvolume = volume_name,
		file = file_name,
		[dismount | nodismount],
		[nounload | unload],
		notify = {client | operator_console}
		}]]

load tran[saction] database_name
	from [compress::]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes, 
		dumpvolume = volume_name,
		file = file_name]
	[stripe on [compress::]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes,
		dumpvolume = volume_name,
		file = file_name]
	[[stripe on [compress::]stripe_device
		[at backup_server_name]
		[density = density_value, 
		blocksize = number_bytes, 
		dumpvolume = volume_name,
		file = file_name]]...]
	[with {
		density = density_value, 
		blocksize = number_bytes, 
		compression, 
		dumpvolume = volume_name,
		file = file_name,
		[dismount | nodismount],
		[nounload | unload],
		listonly [= full],
		headeronly,
		notify = {client | operator_console}
		until_time = datetime}]]

load tran[saction] database_name
	from dump_device
	[[stripe on stripe_device] ... ]

load transaction database_name 
	from syb_tsm::[[-S source_sever_name][-D source_database_name]
		::]object_name [blocksize = number_bytes]
	[stripe on syb_tsm::[[-S source_sever_name]
		[-D source_database_name]::]object_name
		[blocksize = number_bytes]]
	[[stripe on syb_tsm::[[-S source_sever_name]
		[-D source_database_name]::]object_name
		[blocksize = number_bytes]]...]
	[with {
		blocksize = number_bytes,
		passwd = password,
		listonly [= full],
		headeronly,
		notify = {client | operator_console},
		until_time = datetime
		} ]

Parameters

• database_name – is the name of the database to receive data from a dumped backup copy of the transaction log. The log segment of the receiving database must be at least as large as the log segment of the dumped database. The database name can be specified as a literal, a local variable, or a parameter of a stored procedure. For archive databases, database_name is the archive database into which you are loading the transaction log.
• compress:: – invokes the decompression of the archived transaction log. See Backing Up and Restoring User Databases in the System Administration Guide for more information about the compress option.
  Note: You should use the native "compression = compress_level" option over the older "compress::compression_level" option. If you use the native option for dump database, you do not need to use "compress::compression_level" when loading your database.
• from stripe_device – is the name of the dump device from which you are loading the transaction log. For a list of supported dump devices, see the SAP ASE installation and configuration guides.
• at backup_server_name – is the name of a remote Backup Server running on the machine to which the dump device is attached. For platforms that use interfaces files, the backup_server_name must appear in the interfaces file.
• from dump_device – is the local disk transaction log dump.
• density = density_value – overrides the default density for a tape device. This option is ignored.
• blocksize = number_bytes – overrides the default block size for a dump device. If you specify a block size on UNIX systems, it should be identical to that used to make the dump.
• dumpvolume = volume_name – is the volume name field of the ANSI tape label. load transaction checks this label when the tape is opened and generates an error message if the wrong volume is loaded.
• file = file_name – is the name of a particular database dump on the tape volume. If you did not record the dump file names when you made the dump, use listonly to display information about all the dump files.
• stripe on stripe_device – is an additional dump device. You can use up to 32 devices, including the device named in the to stripe_device clause. The Backup Server loads data from all devices concurrently, reducing the time and the number of volume changes required.
• compression – indicates that the log you are loading was compressed to a file on a remote server. You do not need to specify the compression level for load transaction.

  The with compression option differs from the compress option, which you use to load a compressed log from a local file.

  Note: You should use the native "compression = compress_level" option over the older "compress::compression_level" option. If you use the native option for dump database, you do not need to use "compress::compression_level" when loading your database.
• dismount | nodismount – (on platforms that support logical dismount) determines whether tapes remain mounted. By default, all tapes used for a load are dismounted when the load completes. Use nodismount to keep tapes available for additional loads or dumps.
• nounload | unload – determines whether tapes rewind after the load completes. By default, tapes do not rewind, allowing you to make additional loads from the same tape volume. Specify unload for the last dump file to be loaded from a multidump volume. This rewinds and unloads the tape when the load completes.
• listonly [= full] – displays information about all the dump files on a tape volume, but does not load the transaction log . listonly identifies the database and device, the date and time the dump was made, and the date and time it can be overwritten. listonly = full provides additional details about the dump. Both reports are sorted by ANSI tape label.

  After listing the files on a volume, the Backup Server sends a volume change request. The operator can either mount another tape volume or terminate the list operation for all dump devices.

  In the current implementation, listonly overrides headeronly.

  Warning!  Do not use load transaction with listonly on 1/4-inch cartridge tape.
• headeronly – displays header information for a single dump file, but does not load the database . headeronly displays information about the first file on the tape unless you use the file = file_name option to specify another file name. The dump header indicates:

  • Type of dump (database or transaction log)

  • Database ID

  • File name

  • Date the dump was made

  • Character set

  • Sort order

  • Page count

  • Next object ID

  • Checkpoint location in the log

  • Location of the oldest begin transaction record

  • Old and new sequence dates

• notify = {client | operator_console} – overrides the default message destination.

  • On operating systems that offer an operator terminal feature, volume change messages are always sent to the operator terminal on the machine on which the Backup Server is running. Use client to route other Backup Server messages to the terminal session that initiated the dump database.

  • On operating systems (such as UNIX) that do not offer an operator terminal feature, messages are sent to the client that initiated the dump database. Use operator_console to route messages to the terminal on which the Backup Server is running.

• until_time – loads the transaction log up to a specified time in the transaction log. Only transactions committed before the specified time are saved to the database.
• syb_tsm – is the keyword that invokes the libsyb_tsm.so module that enables communication between Backup Server and TSM.
• object_name – is the name of the backup object on TSM server.
• -S source_server_name – specifies the name of the source SAP ASE server when it is not the same as the target SAP ASE server. This parameter is required when the target server for the load operation is different from the source server used for the dump operation.
• -D source_database_name – specifies the name of the source database when it is not the same as the target database. This parameter is required when the target database for the load operation is different from the source database used for the dump operation.

Examples

• Example 1 – Loads the transaction log for the database pubs2 tape:

  load transaction pubs2 
      from "/dev/nrmt0"

• Example 2 – Loads the transaction log for the pubs2 database, using the Backup Server REMOTE_BKP_SERVER:

  load transaction pubs2
          from "/dev/nrmt4" at REMOTE_BKP_SERVER
      stripe on "/dev/nrmt5" at REMOTE_BKP_SERVER
      stripe on "/dev/nrmt0" at REMOTE_BKP_SERVER

• Example 3 – Loads the transaction log for pubs2, up to March 20, 2008, at 10:51:43:866 a.m:

  load transaction pubs2
      from "/dev/ntmt0"
      with until_time = "mar 20, 2008 10:51:43:866am"

• Example 4 – Loads transactions from the TSM backup object “demo2.1” to the testdb database. The source and target databases are the same. See dump transaction for information:

  load transaction testdb from "syb_tsm::demo2.1"

• Example 5 – Loads transactions from the TSM backup object “obj1.1” when the target database (pubs2) is different from the source database (testdb):

  load transaction pubs2 from "syb_tsm::
      -D testdb::obj1.1"

Usage

• If you use sp_hidetext followed by a cross-platform dump and load, you must manually drop and re-create all hidden objects.

• The listonly and headeronly options display information about the dump files without loading them.

• Dumps and loads are performed through Backup Server.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for load transaction differ based on your granular permissions settings.

Auditing

Values in event and extrainfo columns of sysaudits are: