Home

	Expand Search

Access Service User's Guide DirectConnect

Chapter 11 Using Destination-Template Transfer

Chapter 11

Using Destination-Template Transfer

This chapter describes the destination-template transfer statement and covers the following topics:

• General description of Destination-Template Transfer

• Syntax for Destination-Template transfer statements

• Destination-Template datatype qualifiers

• Special Date and Time qualifiers

• Processing Destination-Template transfer from Statements

• Processing Destination-Template transfer to statements

General description of Destination-Template Transfer

The destination-template transfer statement allows you to perform operations that do more than transfer data. You can use destination-template transfer statements to:

• Create a full-image copy (insert)

• Replace existing rows (replace or truncate)

• Create an incremental copy (insert)

• Modify column values (update or delete)

• Perform structural modifications (create or alter)

• Change database permissions (grant or revoke)

• Execute remote stored procedures (execute or use)

• Execute any other arbitrary SQL statement

When you use the destination-template transfer statement, you can feed a result set into a template, and then force the template to perform certain operations.

During a destination-template transfer, the access service inserts the data values it retrieves with the sourceselectstatement from the source database into the destination-template SQL clause. This clause contains one question mark (?) for each column in the result set of the sourceselectstatement. Each value from the result set is substituted for the corresponding question mark in the template on a row-by-row basis. The access service then executes the resulting statement against the target database.

The following figure shows replacement with numeric and character values. This figure assumes that Col1 is numeric and Col2 is character.

Syntax for Destination-Template transfer statements

The syntax for a destination-template transfer statement is as follows:

transfer [with report]

{to | from} 'secondaryname
userid password';

sourceselectstatement;

destinationtemplatestatement

where:

• transfer is the command to initiate a data transfer.

• with report is an optional phrase specified in the first line of the transfer statement that instructs the access service to return processing information to the client application.

  This information is returned as a result set that consists of one VARCHAR column and one row. The row contains the number of rows transferred, rejected, or modified during processing.

• {to | from} indicates the direction of the transfer:

  • to specifies that the data transfer originates from the primary database and sends the data to the secondary database, which can be another access service.

  • from specifies that the data transfer originates from the secondary database and moves data to the primary database.

• 'secondaryname userid password' is a character string, enclosed in single or double quotes in the order shown, that provides the information needed to connect to the secondary database:

  • secondaryname is the name used to identify the secondary database.

  • userid and password must be valid on the secondary database. You can substitute an asterisk for password, if a password is not specified.

• sourceselectstatement specifies a SQL statement that is executed against the source database to produce the result set that will be used in the transfer. This statement can be of any complexity acceptable to the source database, including stored procedures. SQL transformation is not performed on the sourceselectstatement. It must be in the source database's SQL syntax.

• destinationtemplatestatement is a SQL statement or any statement that is valid for the target database environment where it executes. SQL transformation is not performed on the destinationtemplatestatement. It must be in the destination database's SQL syntax. This statement can include question marks as placeholders for the data values that will be inserted. It can also include qualifiers to specify datatypes for the question mark placeholders in the destinationtemplatestatement. For information on these qualifiers, see "Destination-Template datatype qualifiers" .

To increase processing efficiency, you can batch destination-templates together for processing. Use the TransferBatch property or set statement to specify how many templates to batch.

Destination-Template datatype qualifiers

Qualifiers tell the access service how to format data that is inserted for a placeholder. If you do not supply a qualifier, the access service applies default transformations.

Qualification is required for date and time values. You can use the ?T, ?t, ?D, and ?d qualifiers for dates, or you can create a custom qualifier using the special qualifiers described in Table 11-5 .

In destination-template transfers, you cannot use the question mark as a literal. The access service recognizes the question mark only as a qualifier.

The following table defines valid datatype qualifiers.

Table 11-2 , Table 11-3 , and Table 11-4 show the effects of qualifiers on Open Server datatypes.

The following table shows the effects of qualifiers on Open Server datatypes.

The following table shows the effects of qualifiers on Open Server datatypes.

Special Date and Time qualifiers

You can combine special date and time qualifiers to construct the date or time format that the target database requires.

Follow these rules for special qualifiers:

• Enter special characters in either uppercase or lowercase.

• Separate special characters by any arbitrary character, such as a hyphen, slash, or space. Any unrecognized character is copied to the target as it is.

• Enclose special characters in single or double quotes because the resulting value is passed to the target as a character string.

• Qualifiers can contain a null terminated string. The string is limited only by the buffer size (1k).

The following table shows qualifier definitions.

Examples

This section contains examples of destination-template transfer statements executed through ISQL. The following table shows the database and user information used in the examples.

Task: Transfer data from certain columns in the DB2 table named db2table1 to append to the SQL Server table named sstable3. Note the specified datatype qualifiers in the sourceselectstatement.

c:>isql -Sdb2 -Udb2user -Pdb2pass

1>transfer to 'ss ssuser sspass';

2>select db2col1, db2col3, db2col2, db2col6

3>from db2table1;

4>insert into sstable3 (sscol1,sscol3,sscol2,sscol6) values (?C, ?D, ?C, ?N);

5>go

Result: Data is selected from four columns(db2col1, db2col3, db2col2, db2col6) in the DB2 table called db2table1and inserted into the SQL Server table called sstable3. Qualifiers (the characters that follow each question mark) are used to ensure that the source values are correctly converted into the target table datatypes. In this example, the template contains four question marks. Therefore, the result set obtained by the sourceselectstatement cannot contain more than four columns or the transfer fails.

Task: Select marked rows from a SQL Server table and delete the corresponding rows in a DB2 table.

c:>isql -Sdb2 -Udb2user -Pdb2pass

1>transfer from 'ss ssuser sspass';

2>select sscol1 from sstable1

3>where action="drop";

4>delete from db2table3

5>where db2col1=?C ;

6>go

Result: Certain rows (those with action set to drop) are selected from sstable1. The column sscol1 is then used to specify the rows to delete in the DB2 table db2table3, by matching the values with the corresponding DB2 column db2col1. At this point, you can use an insert statement similar to example #1 to replace the deleted rows in the DB2 table with the data from the SQL Server table.

Task: Update the DB2 table with data from a similar SQL Server table.

c:>isql -Sdb2 -Udb2user -Pdb2pass

1>transfer from 'ss ssuser sspass';

2>select sscol1, sscol2, sscol3, sscol4

3>from sstable1

4>where sscol5 > '12/31/1994 24.0000';

5>update db2table3

6>set db2col1=?, db2col2=?, db2col3=?

7>where db2col4 = ? ;

8>go

Result: Four SQL Server columns are selected when sscol5, a datetime value, is greater than a specified date and time ('12/31/24.0000'). The fourth column is the key that specifies the rows to be updated in the DB2 table called dbtable3.

Task: Process orders in DB2 using an RSP. The orders were entered in SQL Server. Note that the datatype qualifiers are not specified.

c:>isql -Sdb2 -Udb2user -Pdb2pass

1>transfer from 'ss ssuser sspass';

2>select sscol1, sscol2, sscol3

3>from sstable1;

4>use procedure ordrproc ?, ?, ? ;

5>go

Result: The order information is selected from columns in the SQL Server table called sstable1, and the RSP named ordrproc is executed against DB2 to process the information.

Processing Destination-Template transfer from Statements

The following figure shows how the transfer from statementupdates four columns in the DB2 table called db2table3 with data selected from four similar SQL Server columns. Whether the update occurs depends on a comparison with sscol5, a datetime value that must be greater than '12/31/1994 23.59.000'. The fourth column is the key that specifies the rows targeted for the update.

The following steps describe the data flow process:

• The access service executes the sourceselectstatement against the secondary database and retrieves the column descriptions.

• The access service inserts the first row of data resulting from the sourceselectstatement into one destination-template, where it substitutes data for the question marks and performs any needed conversion. Then, the access service inserts the next row into a second template and performs the same operations as before. After it inserts all the rows, the access service batches the templates together.

• The access service executes the request templates against the primary database, in this case, DB2.

• The access service substitutes the next n rows and executes another request. It repeats this process until all the rows finish processing. The value of n is established by the current setting of the TransferBatch property.

• One of the following occurs:

  • If TransactionMode is set to short, the access service issues a commit after each batch finishes processing, regardless of any conversion errors.

  • If TransactionMode is set to long, the access service issues a commit after the entire transfer processes, regardless of any conversion errors.

Processing Destination-Template transfer to statements

The following figure shows the transfer to statement appending data from certain columns in the DB2 table named db2table1 to SQL Server table sstable3. The four qualifiers behind the question marks require that the result set from the sourceselectstatement contains four columns; otherwise, the transfer fails.

The following steps describe the data flow process:

• The access service issues the sourceselectstatement against the primary database and retrieves the column descriptions.

• The access service receives the results of the sourceselectstatement and converts them to the pre-defined SQL Server datatypes. These datatypes do not necessarily match the datatypes of the columns in the target table.

• The access service inserts the first row of data resulting from the sourceselectstatement into one destination-template, where it substitutes data for the question marks and performs any needed conversion. Then, the access service inserts the next row into a second template and performs the same operations as before. After it inserts all the rows, the access service batches the templates together.

• The access service issues the request against the secondary database.

• The access service substitutes the next n rows into the destinationtemplatestatement and issues another request against the secondary database. It continues until all rows are processed.

• One of the following occurs:

  • If TransactionMode is set to short, the access service issues a commit after each batch finishes processing, regardless of any conversion errors.

  • If TransactionMode is set to long, the access service issues a commit after the entire transfer finishes processing, regardless of any conversion errors.