Transferring data

CSAINDX demonstrates how to transfer data between DB2 and Adaptive Server Enterprise. In this case, DB2, the primary database is the source, and Adaptive Server Enterprise, the secondary database, is the target. Therefore, the TRANSFER statement requires the format:

TRANSFER TO 'SQLServername user password'

WITH REPLACE INTO 'SQLServer_tablename'

@SQL REQUEST

In the sample program, the SQL request is actually an RSP called SAMP04C, which contains several SQL requests. CSAINDX codes the TRANSFER in the WORKING-STORAGE SECTION within 01 SQL-BUFFER-CMDS:

03 TRANSFER-STMT.
     05 FILLER             PIC X(12) VALUE
     'TRANSFER TO '.
     05 FILLER             PIC X(01) VALUE QUOTE.
     05 FILLER             PIC X(15) VALUE
     'trex ssuid sspw'.
     05 FILLER             PIC X(01) VALUE QUOTE.
     05 FILLER             PIC X(02) VALUE '; '.
     05 FILLER             PIC X(26) VALUE
     'WITH REPLACE INTO samp04in'.
     05 FILLER             PIC X(02) VALUE '; '.
     05 FILLER             PIC X(30) VALUE
     'USE PROCEDURE SAMP04C 00200 '.
     05 FILLER             PIC X(01) VALUE SPACES.

Your SQL statements cannot exceed the length you assign SQL-REQUEST in the LINKAGE SECTION.

You can also transfer data in the other direction—from Adaptive Server Enterprise to a supported database through DirectConnect. For details on the syntax and use of the TRANSFER statement, see the DirectConnect documentation.

After preparing the TRANSFER statement, defining the SQL request buffer and initializing the SPAREA, CSAINDX defines the attach, execution, and detach routines for the transfer in 5000-TRANSFER-PROCESS:

*****************************************************************
 * CONTROL THE PROCESS OF ATTACH, EXEC, DETATCH FOR TRANSFER.
 * 1) 1ST ATTACH TO DIRECTConnect CHECKS IF IT'S ALIVE AND WELL.
 * 2) 2ND ATTACH TO SQL SERVER - RUNS S.P. TO DELETE INDEXES.
 * 3) 3RD ATTACH TO DIRECTConnect RUNS TRANSFER FROM RSP TO SYBASE SQL SERVER.
 * 4) 4TH ATTACH TO SQL SERVER - RUNS S.P. TO RE-CREATE INDEXES.
 *****************************************************************

5000-TRANSFER-PROCESS.

    PERFORM 5100-WRITE-RUN-COUNT       THRU 5100-EXIT.

    PERFORM 5600-ATTACH-TO-DIRECTConnect THRU 5600-EXIT.
     PERFORM 5800-CALL-DETACH           THRU 5800-EXIT.

    PERFORM 5700-ATTACH-TO-SYBASE BASE SQL SERVER THRU 5700-EXIT.
     PERFORM 5200-LOAD-DEL-INDEX-STMT   THRU 5200-EXIT.
     PERFORM 5500-CALL-REQEXEC          THRU 5500-EXIT.
     PERFORM 5800-CALL-DETACH         THRU 5800-EXIT.

    PERFORM 5600-ATTACH-TO-DIRECTConnect THRU 5600-EXIT.
     PERFORM 5300-LOAD-TRANSFER-STMT  THRU 5300-EXIT.
     PERFORM 5500-CALL-REQEXEC        THRU 5500-EXIT.
     PERFORM 5800-CALL-DETACH         THRU 5800-EXIT.

    PERFORM 5700-ATTACH-TO-SYBASE BASE SQL SERVER THRU 5700-EXIT.
     PERFORM 5400-LOAD-CRE-INDEX-STMT THRU 5400-EXIT.
     PERFORM 5500-CALL-REQEXEC        THRU 5500-EXIT.
     PERFORM 5800-CALL-DETACH         THRU 5800-EXIT.

5000-EXIT.
     EXIT.

You can see that CSAINDX loads the TRANSFER request into the buffer and executes it.

5300-LOAD-STMT

*****************************************************************
 * LOAD THE TRANSFER STATEMENT INTO THE SQL BUFFER FOR THE DIRECTConnect
 *****************************************************************

5300-LOAD-TRANSFER-STMT.

    MOVE TRANSFER-STMT                            TO SQL-REQUEST.
     MOVE LENGTH OF TRANSFER-STMT                  TO SQL-LENGTH.
     PERFORM 5500-CALL-REQEXEC                            THRU 5500-EXIT.

5300-EXIT.
     EXIT.