(CR401890) Replace the syntax for express transfer in the section titled “Syntax.” The properties have changed, and a new option called alter table supplements the insert, replace, and truncate options.
The following is the required syntax for a bulk copy and express transfer statement:
transfer [with report]
{to | from} ‘secondaryname userid password’;
with {insert | replace | truncate | alter} into tablename;
sourceselectstatement
where:
transfer must begin all transfer statements.
with report is an optional phrase specified in the first line of the transfer statement. It instructs the access service to return processing information to the client application.
This information is returned as a result set consisting of a VARCHAR column and a single row. The row contains the number of rows transferred, rejected, and modified during processing.
{to | from} indicates the direction of the transfer:
to specifies that the data is transferred from the primary database to the secondary database.
from specifies that the data is transferred from the secondary database to the primary database.
secondaryname userid password is a three-part character string that provides the information needed to connect to the secondary database:
secondaryname is the name of the secondary database and must be recorded in the following DirectConnect files:
For bulk copy transfer, in the interfaces file (for UNIX), or in the sql.ini file (for Windows.)
For express transfer, the secondaryname must match a data source name (DSN) in the ODBC system information file.
userid and password must be valid on the secondary database. If the password is NULL, you can substitute an asterisk for password and it will be corrected to a NULL when sent to the secondary connection. Exactly three tokens are sent to the secondary connection.
All of the elements of the character string must be enclosed in single or double quotes in the order shown.
with {insert | replace | truncate} into specifies whether the data is appended onto the target table (insert) or the existing data is deleted and replaced (replace or truncate).
When transferring data to Adaptive Server, the truncate option causes transfer to issue a truncate, rather than a delete against the target table. For other target databases, delete and truncate are equivalent.
[with alter} into invokes a UDB command that disables logging for the transaction and will truncate the table. (For DB2 UDB only).
Using this transfer syntax must be done with many precautions.
Errors will render the table useless and must be handled according
to IBM’s restore procedures. Read the IBM documentation
pertaining to the ALTER TABLE command and its ACTIVATE
NOT LOGGED INITIALLY option. If an error occurs during
the transfer, the table will be rendered unusable and the entire
transaction will not be logged.
tablename specifies the table into which data is inserted or replaced. The table must already exist because the transfer statement does not create a new one in the target database.
sourceselectstatement specifies a SQL statement that is executed against the source database to produce the result set used in the transfer.
This statement can be any statement the source database will accept, including stored procedures. SQL transformation is not performed on the sourceselectstatement. It must be in the source database SQL dialect.
