Storing transfer information

This section describes the spt_TableTransfer and the monTableTransfer tables.

The results of table transfers are stored in, and retrieved from, the spt_TableTransfer table. The results of successful transfers retrieved from the table specified for this command are used as the defaults for subsequent transfers. For example, if you issue this command (including row and column separators):

transfer table mytable for csv

The next time you transfer table mytable, the transfer command uses for csv and the same row and column separators by default.

Each database has its own version of spt_TableTransfer. The table stores history only for table transfers for tables in its same database that are marked for incremental transfer.

The max transfer history configuration parameter controls how many transfer history entries Adaptive Server retains in the spt_TableTransfer table in each database. See Chapter 5, “Setting Configuration Parameters,” in the System Administration Guide, Volume 1.

Database owners use sp_setup_table_transfer to create the spt_TableTransfer table. sp_setup_table_transfer takes no parameters, and works in the current database, If spt_TableTransfer does not exist, sp_setup_table_transfer creates it.

The spt_TableTransfer table stores historical information about both successful and failed transfers. It does not store information for transfers currently in progress.

spt_TableTransfer is a user table, rather than a system table. It is not created when you create Adaptive Server, but Adaptive Server automatically creates it in any database that has tables that are eligible for transfer, if you do not use sp_setup_transfer_table to manually create it (creating it manually may allow you to avoid unexpected errors that can occur when Adaptive Server creates the table automatically).

sp_help reports incremental transfer as a table attribute.

The columns in spt_TableTransfer are:

Column	Datatype	Description
end_code	unsigned smallint not null	The transfer’s ending status. 0 – success error code – failure
id	int not null	The object ID of the transferred table.
ts_floor	bigint not null	The beginning transaction timestamp.
ts_ceiling	bigint not null	The transaction timestamp after which rows are uncommitted, and therefore not transferred.
time_begin	datetime not null	The date and time when the transfer began The time Adaptive Server began to setup the command if the transfer fails before implementation. Otherwise, this is the time at which the command sent the first data to the output file.
time_end	datetime not null	The date and time when the transfer ended If the transfer command fails, this is the time of failure. Otherwise, this is the time at which the command finishes sending data and closes the file
row_count	bigint not null	The number of rows transferred.
byte_count	bigint not null	The number of bytes written.
sequence_id	int not null	A number that tracks this transfer, unique to each transfer of a table.
col_order	tinyint not null	A number representing the column order of the output: 1 – id 2 – offset 3 – name 4 – name_utf8
output_to	tinyint not null	A number representing the output format: 1 – ase 2 – bcp 3 – csv 4 – iq
tracking_id	int null	An optional customer-supplied tracking ID. If you do not use with tracking_id = nnn, this column is null.
pathname	varchar (512) null	The output file name
row_sep	varchar (64) null	The row separator string used for for csv.
col_sep	varchar(64) null	The column separator used for for csv.

The monTableTransfer monitoring table provides:

Historical transfer information for tables whose information Adaptive Server currently holds in memory. This is true for any table accessed since Adaptive Server’s most recent restart, unless you have not configured Adaptive Server memory large enough to hold all current table information
Information about transfers currently in progress and for completed transfers for tables whose information Adaptive Server holds in memory. This includes information for tables:
- Marked for incremental transfer
- That were involved in at least one transfer since you restarted Adaptive Server.
- Whose descriptions you have not used for other tables (monTableTransfer does not search through every database looking for every table that was ever transferred; monTableTransfer restricts itself to an active set of tables it continues to track since their most recent transfer.

See monTableTransfer in the Reference Manual: Tables.