************************************************************************* 
      ** 
      ** Source File Name = expsamp.sqb 
      ** 
      ** Licensed Materials - Property of IBM 
      ** 
      ** (C) COPYRIGHT International Business Machines Corp. 1995, 2000  
      ** All Rights Reserved. 
      ** 
      ** US Government Users Restricted Rights - Use, duplication or 
      ** disclosure restricted by GSA ADP Schedule Contract with IBM Corp. 
      ** 
      ** 
      ** PURPOSE: 
      **   This program is an example of how APIs are implemented in order to 
      **   export and import tables and table data to DRDA database. 
      **   The order of the program is as follows 
      **       - export a table to an IXF file format 
      **       - import the IXF file to a DB2 DRDA database table 
      **   This program needs the embedded SQL calls in order to connect to 
      **   an existing database, then to create a temporary table to work with. 
      ** 
      **    APIs USED : 
      **            IMPORT TO             sqlgimpr 
      **            EXPORT                sqlgexpr 
      ** 
      **   For DRDA database imports, the data file format MUST be of the IXF 
      **   form.  The IMPORT command must be issued an "INSERT" statement 
      ** 
      ** For more information about these samples see the README file. 
      ** 
      ** For more information on Programming in COBOL, see the: 
      **    -  "Programming in COBOL" section of the Application Development Guide. 
      ** 
      ** For more information on Building COBOL Applications, see the: 
      **    - "Building COBOL Applications" section of the Application Building Guide. 
      ** 
      ** For more information on the SQL language see the SQL Reference. 
      ** 
      ************************************************************************* 

       Identification Division.
       Program-ID. "expsamp".

       Data Division.
       Working-Storage Section.

           copy "sqlenv.cbl".
           copy "sqlca.cbl".
           copy "sqlutil.cbl".

           EXEC SQL BEGIN DECLARE SECTION END-EXEC.

       77 statement         pic x(254).

       01 userid            pic x(8).
       01 passwd.
         49 passwd-length   pic s9(4) comp-5 value 0.
         49 passwd-name     pic x(18).
       77 drdadb            pic x(8).
       01 drdaid            pic x(8).
       01 drdapwd.
         49 drdapwd-length  pic s9(4) comp-5 value 0.
         49 drdapwd-name    pic x(18).

           EXEC SQL END DECLARE SECTION END-EXEC.

       77 errloc              pic x(80).
       77 rc                  pic s9(9) comp-5.
       77 stmt                pic x(254) value "select * from staff". 
       77 impstmt             pic x(254) value "create into impstaff".
       77 stmt2               pic x(254) value "select * from org". 
       77 impstmt2            pic x(254) value "create into imporg".
       77 datafile            pic x(10) value "EXPTBL.IXF".
       77 datafile-len        pic 9(4) comp-5 value 10.
       77 datafile2           pic x(10) value "EXPTB2.IXF".
       77 datafile2-len       pic 9(4) comp-5 value 10.
       77 msgfile-x           pic x(10) value "EXPMSG.TXT".
       77 msgfile-x-len       pic 9(4) comp-5 value 10.
       77 msgfile-m           pic x(10) value "IMPMSG.TXT".
       77 msgfile-m-len       pic 9(4) comp-5 value 10.
       77 fileformat          pic x(3) value "IXF".
       77 fileformat-len      pic 9(4) comp-5 value 3.

       Procedure Division.
       expsamp Section.
           display "Sample COBOL program: EXPSAMP".

           display "Enter your user id : " with no advancing.
           accept userid.

           display "Enter your password : " with no advancing.
           accept passwd-name.

           inspect passwd-name tallying passwd-length for characters
              before initial " ".

           display "Enter the DRDA database name : " with no advancing.
           accept drdadb.

           display "Enter the DRDA username : " with no advancing.
           accept drdaid.

           display "Enter the DRDA password : " with no advancing.
           accept drdapwd-name.

           inspect drdapwd-name tallying drdapwd-length for characters
              before initial " ".
           display " ".

      * need to preset the size of structure field and counts 
           move SQLUEXPT-OUT-SIZE to SQL-SIZE-OF-UEXPT-OUT.
           move 0                 to SQL-COMMITCNT.
           move 0                 to SQL-RESTARTCNT.
           move stmt              to SQL-TCOLSTRG-DATA.
           move 254               to SQL-TCOLSTRG-LEN.
           move SQL-METH-D        to SQL-DCOLMETH.

      * connecting to SAMPLE database. 
           EXEC SQL CONNECT TO sample USER :userid USING :passwd
                END-EXEC. 
           move "CONNECT TO SAMPLE" to errloc.
           call "checkerr" using SQLCA errloc.

           display "exporting STAFF table into file : ", datafile.
      ********************* 
      * EXPORT API called * 
      ********************* 
           call "sqlgexpr" using
                                 by value     datafile-len
                                 by value     fileformat-len
                                 by value     msgfile-x-len
                                 by reference datafile
                                 by value     0
                                 by value     0
                                 by reference SQL-DCOLDATA
                                 by reference SQL-TCOLSTRG
                                 by reference fileformat
                                 by reference SQL-FILETMOD
                                 by reference msgfile-x
                                 by value     SQLU-INITIAL
                                 by reference SQL-UEXPT-OUT
                                 by value     0
                                 by reference sqlca
                              returning rc.
           move "exporting table" to errloc.
           call "checkerr" using SQLCA errloc.

           display "rows exported : ", SQL-ROWSEXPORTED.

           move stmt2             to SQL-TCOLSTRG-DATA.
           display "exporting ORG table into file : ", datafile2.
      ********************* 
      * EXPORT API called * 
      ********************* 
           call "sqlgexpr" using
                                 by value     datafile2-len
                                 by value     fileformat-len
                                 by value     msgfile-x-len
                                 by reference datafile2
                                 by value     0
                                 by value     0
                                 by reference SQL-DCOLDATA
                                 by reference SQL-TCOLSTRG
                                 by reference fileformat
                                 by reference SQL-FILETMOD
                                 by reference msgfile-x
                                 by value     SQLU-INITIAL
                                 by reference SQL-UEXPT-OUT
                                 by value     0
                                 by reference sqlca
                              returning rc.
           move "exporting table" to errloc.
           call "checkerr" using SQLCA errloc.

           display "rows exported : ", SQL-ROWSEXPORTED.

           display "connect to DRDA".
           EXEC SQL CONNECT TO :drdadb USER :drdaid USING :drdapwd
                END-EXEC.
           move "CONNECT TO DRDA DB" to errloc.
           call "checkerr" using SQLCA errloc.

      * drop tables before creating them, just in case they already exist 
           move "drop table impstaff" to statement.
           EXEC SQL EXECUTE IMMEDIATE :statement END-EXEC.
           move "drop table imporg" to statement.
           EXEC SQL EXECUTE IMMEDIATE :statement END-EXEC.
           move "DROP index SAMPLE.TESTIND" to statement.
           EXEC SQL EXECUTE IMMEDIATE :statement END-EXEC;
           move "COMMIT the DROP TABLE" to errloc.
           EXEC SQL COMMIT END-EXEC.
           move "DROP TABLE" to errloc.
           call "checkerr" using SQLCA errloc.

      * create a temporary tables to import into 
      *    display "create a temporary table 'impstaff' to import into". 
      *    EXEC SQL CREATE TABLE impstaff (id smallint, name varchar(9), 
      *      dept smallint, job char(5), years smallint, salary 
      *      decimal(7,2),comm decimal(7,2)) END-EXEC. 
      *    move "create table impstaff" to errloc. 
      *    call "checkerr" using SQLCA errloc. 

      *    display "create a temporary table 'imporg' to import into". 
      *    EXEC SQL CREATE TABLE imporg (deptnumb smallint, deptname 
      *      varchar(14), manager smallint, division varchar(10), location 
      *      varchar(13)) END-EXEC. 
      *    move "create table imporg" to errloc. 
      *    call "checkerr" using SQLCA errloc. 

      *     EXEC SQL COMMIT END-EXEC. 
      *     move "COMMIT the CREATE TABLE" to errloc. 
      *     call "checkerr" using SQLCA errloc. 

      * need to preset the size of structure field and counts 
           move SQLUIMPT-IN-SIZE  to SQL-SIZE-OF-UIMPT-IN.
           move SQLUIMPT-OUT-SIZE to SQL-SIZE-OF-UIMPT-OUT.
           move 0                 to SQL-COMMITCNT.
           move 0                 to SQL-RESTARTCNT.
           move impstmt           to SQL-TCOLSTRG-DATA.
           move 254               to SQL-TCOLSTRG-LEN.
           move SQL-METH-D        to SQL-DCOLMETH.
           display "importing the file ", datafile, " into 'impstaff'".
      ********************* 
      * IMPORT API called * 
      ********************* 
           call "sqlgimpr" using
                                 by value     datafile-len
                                 by value     fileformat-len
                                 by value     msgfile-m-len
                                 by reference datafile
                                 by value     0
                                 by reference SQL-DCOLDATA
                                 by reference SQL-TCOLSTRG
                                 by reference fileformat
                                 by reference SQL-FILETMOD
                                 by reference msgfile-m
                                 by value     SQLU-INITIAL
                                 by reference SQL-UIMPT-IN
                                 by reference SQL-UIMPT-OUT
                                 by value     0
                                 by value     0
                                 by reference sqlca
                             returning rc.
           move "importing table" to errloc.
           call "checkerr" using SQLCA errloc.

           display "rows imported : ", SQL-ROWSINSERTED.
           display "rows committed : ", SQL-ROWSCOMMITTED.

           move impstmt2          to SQL-TCOLSTRG-DATA.
           display "importing the file ", datafile2, " into 'imporg'".
      ********************* 
      * IMPORT API called * 
      ********************* 
           call "sqlgimpr" using
                                 by value     datafile2-len
                                 by value     fileformat-len
                                 by value     msgfile-m-len
                                 by reference datafile2
                                 by value     0
                                 by reference SQL-DCOLDATA
                                 by reference SQL-TCOLSTRG
                                 by reference fileformat
                                 by reference SQL-FILETMOD
                                 by reference msgfile-m
                                 by value     SQLU-INITIAL
                                 by reference SQL-UIMPT-IN
                                 by reference SQL-UIMPT-OUT
                                 by value     0
                                 by value     0
                                 by reference sqlca
                             returning rc.
           move "importing table" to errloc.
           call "checkerr" using SQLCA errloc.

           display "rows imported : ", SQL-ROWSINSERTED.
           display "rows committed : ", SQL-ROWSCOMMITTED.

           EXEC SQL CONNECT RESET END-EXEC.
           move "CONNECT RESET" to errloc.
           call "checkerr" using SQLCA errloc.

       End-expsamp. stop run.