| |
Home |
![[Table of Contents]](/dwicons/b_toc.gif)
|
Access Service User's Guide DirectConnect for AS/400 |
![[-]](/dwicons/i_colpse.gif)
| Chapter 11 Using Destination-Template Transfer |
|
| 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.Col1Col2
The following figure shows replacement with numeric and character values. This figure assumes that is numeric and is character.
Figure 11-1: Replacement of values during destination-template transfer
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 is 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.
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, except inside quoted strings. The access service recognizes the question mark only as a qualifier.
The following table defines valid datatype qualifiers.
Placeholder/ Qualifier | Definition |
?C | Character data enclosed in quotes |
?N | Numeric data, not quoted |
?D | A standard format Adaptive Server datetime data enclosed in quotes: 'MON DD YYYY hh:mm:ss.nnn' |
?d | Standard format for AS/400 date in quotes: 'mm/dd/yyyy' |
?T | Standard format AS/400 TIMESTAMP data enclosed in quotes: 'YYYY-MM-DD-hh.mm.ss.nnnnnn' |
?t | Standard format for AS/400 time in quotes: 'hh:mm:ss' |
?X | Standard format Adaptive Server hexadecimal data (for example, 0xffee), used for transferring binary data to Adaptive Server |
?x | Standard format AS/400 hexadecimal data (for example, X'FFEE'), used for transferring binary data to AS/400 |
?y | Standard format for Teradata date in quotes: 'yy/mm/dd' |
?O | Standard format for Oracle hexadecimal data, used for transferring binary data to Oracle |
Table 11-2 shows the effects of qualifiers ?C, ?N, ?D, and ?T on Open Server datatypes.
Open Server Datatype | Default | Qualifiers (in bold) | |||
?C | ?N | ?D | ?T | ||
CS_CHAR CS_VARCHAR CS_TEXT | ?C | Quote | No quote | If the source is ISO TIMESTAMP, it is converted to 'Mon dd yyyy hh:mm:ss:nnn'. If the source is ISO DATE, it converts to 'Mon dd yy'. If the source is ISO TIME, it converts to 'Mon dd yy hh:mm:ss' (using the value from the DefaultDate property as the date portion of the value). Otherwise, it is enclosed in quotes and copied as is. Convert to Open Server datetime string, quote | If the source is ISO DATE or TIME, the DefaultDate and DefaultTime property values are used to fill in missing information. Convert to ISO TIMESTAMP, quote |
CS_BIT, CS_INT1 CS_INT2 CS_ INT4 CS_REAL CS_FLOAT | ?N | Convert to char, quote | Convert to char, no quote | n/a | n/a |
CS_MONEY CS_MONEY4 CS_DECIMAL | ?N | Convert to char, quote | Convert to char, no quote | n/a | n/a |
CS_DATETIME CS_DATETIME4 | ?D | Using the ?C or ? qualifier from Adaptive Server datetime columns to AS/400 char or varchar columns causes the transferred value to lose precision. Use one of the following workarounds: - Use destination-template with the ?T qualifier. - Use bulk transfer to transfer from Adaptive Server datetime columns to AS/400 timestamp columns. - Use the Adaptive Server convert function in the sourceselectstatement to format the datetime data. 'MON DD YYYY hh:mm' [AM or PM] | n/a | 'MMM DD YYYY hh:mm:ss:nnn' | 'YYYY-MM-DD-hh.mm.ss.nnnnnn' |
CS_BINARY CS_VARBINARY CS_IMAGE | ?X or ?x | Convert to hex, quote | n/a | n/a | n/a |
Table 11-3 shows the effects of qualifiers ?y, ?d, ?t, and ?x on Open Server datatypes.
?y | ?d | ?t | ?x | ||
CS_CHAR CS_VARCHAR CS_TEXT | ?C | If the source is ISO DATE, TIME, or TIMESTAMP, it converts to 'yy/mm/dd'. Otherwise, it is enclosed in quotes and copied as is. quote | If the source is ISO DATE, TIME, or TIMESTAMP, it converts to 'mm/dd/yy'. Otherwise, it is enclosed in quotes and copied as is. Quote | If the source is ISO DATE, TIME, or TIMESTAMP, it converts to 'hh:mm:ss'. Otherwise, it is enclosed in quotes and copied as is. Quote | Convert to hex; leading 'X, trailing 'f |
CS_BIT, CS_INT1 CS_INT2 CS_INT4 CS_REAL CS_FLOAT CS_MONEY CS_MONEY4 CS_DECIMAL | ?N | n/a | n/a | n/a | n/a |
CS_DATETIME CS_DATETIME4 | ?D | 'yy/mm/dd' | 'yy/mm/dd' | 'hh:mm:ss' | n/a |
CS_BINARY CS_VARBINARY CS_IMAGE | ?X or ?x | n/a | n/a | n/a | Convert to hex; leading 'X, trailing 'f |
Table 11-4 shows the effects of qualifiers ?X and ?O on Open Server datatypes
?X | ?O | ||||
CS_CHAR CS_VARCHAR CS_TEXT | Convert to hex; leading 0x, no quote | n/a | |||
CS_BIT, CS_ INT1 CS_INT2 CS_INT4 CS_REAL CS_FLOAT CS_MONEY CS_MONEY4 CS_DECIMAL | ?N | n/a | n/a | ||
CS_DATETIME CS_DATETIME4 | ?D | n/a | n/a | ||
CS_BINARY CS_VARBINARY CS_IMAGE | ?X or ?x | If the target database is AS/400, ?x converts the data to the standard AS/400 hex format (a quoted hex number with a leading X). Convert to hex; leading 0x, no quote | n/a |
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.
Qualifier | Definition |
yy | Last two digits of year |
yyyy | All four digits of year |
mm | Month or minute (recognized by context) |
mon | Three-character month abbreviation: Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec |
dd | Day |
hh | Hour |
ss | Seconds |
mmm | Milliseconds |
mmmmmm or uuuuuu | Microseconds |
am or pm | Indicates that you want an am or pm designator included. The actual designator is inserted appropriate to the value. |
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.
Transfer Components | Primary Database | Secondary Database |
Database | AS400 | Adaptive Server |
User ID | as4user | ssuser |
Password | as4pass | sspass |
Service Name | as4 | ss |
Table Names | as4table1, as4table2, as4table3 | sstable1, sstable2, sstable3 |
Column Names | as4col1, as4col2, as4col3,... | sscol1, sscol2, sscol3,... |
Task: Transfer data from certain columns in the AS/400 table named as4table1 to append to the Adaptive Server table named sstable3. Note the specified datatype qualifiers in the sourceselectstatement.
c:>isql -Sas4 -Uas4user -Pas4pass
1>transfer to 'ss ssuser sspass';
2>select as4col1, as4col3, as4col2, as4col6
3>from as4table1;
4>insert into sstable3 (sscol1,sscol3,sscol2,sscol6) values (?C, ?D, ?C, ?N);
5>go
Result: Data is selected from four columns (ascol1, ascol3, ascol2, ascol6) in the AS/400 table called as4table1 and inserted into the Adaptive 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 an Adaptive Server table and delete the corresponding rows in an AS/400 table.
c:>isql -Sas4 -Uas4user -Pas4pass
1>transfer from 'ss ssuser sspass';
2>select sscol1 from sstable1
3>where action="drop";
4>delete from as4table3
5>where as4col1=?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 AS/400 table as4table3, by matching the values with the corresponding AS/400 column as4col1. At this point, you can use an insert statement similar to Example #1 to replace the deleted rows in the AS/400 table with the data from the Adaptive Server table.
Task: Update the AS/400 table with data from a similar Adaptive Server table.
c:>isql -Sas4 -Uas4user -Pas4pass
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 as4table3
6>set as4col1=?, as4col2=?, as4col3=?
7>where as4col4 = ? ;
8>go
Result: Four Adaptive 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 AS/400 table called as4table3.
Task: Process orders in AS/400 using a stored procedure. The orders were entered in Adaptive Server. Note that the datatype qualifiers are not specified.
c:>isql -Sas4 -Uas4user -Pas4pass
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 Adaptive Server table called sstable1, and the stored procedure named ordrproc is executed against the AS/400 to process the information.
The following figure shows how the transfer from statement updates four columns in the AS/400 table called as4table3 with data selected from four similar Adaptive 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.
Figure 11-2: Data flow in a destination-template transfer from statement
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, the AS/400.
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.
The following figure shows the transfer to statement appending data from certain columns in the AS/400 table named as4table1 to Adaptive 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.
Figure 11-3: Data flow in a destination-template transfer to statement
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 Adaptive 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.
|
|