************************************************************************* 
      ** 
      ** Source File Name = tload.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: to show the usage of LOAD and QUIESCE APIs 
      ** 
      ** APIs : 
      **        EXPORT                          sqlgexpr 
      **        QUIESCE TABLESPACES FOR TABLE   sqlgvqdp 
      **        LOAD                            sqlgload 
      ** 
      ** 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. "tload".

       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).
           EXEC SQL END DECLARE SECTION END-EXEC.

       77 errloc              pic x(80).
       77 stmt                pic x(254).
       77 impstmt             pic x(254).

       77 rc                  pic s9(9) comp-5.

      * Variables for IMPORT/EXPORT APIs 
       77 msgfile-x           pic x(10) value "EXPMSG.TXT".
       77 msgfile-x-len       pic 9(4) comp-5 value 10.
       77 fileformat          pic x(3) value "DEL".
       77 fileformat-len      pic 9(4) comp-5 value 3.

      * Variables for the QUIESCE TABLESPACES FOR TABLE API 
       77 tname-len     pic s9(4) comp-5 value +9.
       77 quiesce-mode  pic s9(4) comp-5 value +2.
       77 tname         pic x(19) value "loadtable".

      * Variables for the LOAD API 
       77 lclfile-len   pic s9(4) comp-5 value +7.
       77 lclfile       pic x(7)  value "LOADMSG".
       77 rmtfile-len   pic s9(4) comp-5 value +8.
       77 rmtfile       pic x(8)  value "RLOADMSG".
       77 null-ind      pic s9(9) comp-5.

       01 datafile-len1          pic 9(4)  comp-5 value 12.

       01 datafile-ixf.
           05 datafile-len       pic 9(9)  comp-5 value 12.
           05 datafile           pic x(12) value "EXPTABLE.IXF".

       01 work-dir-ixf.
           05 work-dir-len       pic 9(9)  comp-5 value 1.
           05 work-dir-path      pic x(12) value ".".

       01 copy-dir-ixf.
           05 copy-dir-len       pic 9(9)  comp-5 value 1.
           05 copy-dir-path      pic x(12) value ".".

       01 lob-dir-ixf.
           05 lob-dir-len        pic 9(9)  comp-5 value 1.
           05 lob-dir-path       pic x(12) value ".".

       01 datafile-list.
           05 SQL-MEDIA-TYPE         PIC X.
           05 SQL-FILLER             PIC X(3).
           05 SQL-SESSIONS           PIC S9(9) COMP-5.
           05 SQL-TARGET.
               10 SQL-MEDIA          USAGE IS POINTER.
               10 SQL-VENDOR         REDEFINES SQL-MEDIA
                                     USAGE IS POINTER.
               10 SQL-LOCATION       REDEFINES SQL-MEDIA
                                     USAGE IS POINTER.
               10 FILLER             REDEFINES SQL-MEDIA
                                     PIC X(4).

       01 work-dir.
           05 SQL-MEDIA-TYPE         PIC X.
           05 SQL-FILLER             PIC X(3).
           05 SQL-SESSIONS           PIC S9(9) COMP-5.
           05 SQL-TARGET.
               10 SQL-MEDIA          USAGE IS POINTER.
               10 SQL-VENDOR         REDEFINES SQL-MEDIA
                                     USAGE IS POINTER.
               10 SQL-LOCATION       REDEFINES SQL-MEDIA
                                     USAGE IS POINTER.
               10 FILLER             REDEFINES SQL-MEDIA
                                     PIC X(4).

       01 copy-target.
           05 SQL-MEDIA-TYPE         PIC X.
           05 SQL-FILLER             PIC X(3).
           05 SQL-SESSIONS           PIC S9(9) COMP-5.
           05 SQL-TARGET.
               10 SQL-MEDIA          USAGE IS POINTER.
               10 SQL-VENDOR         REDEFINES SQL-MEDIA
                                     USAGE IS POINTER.
               10 SQL-LOCATION       REDEFINES SQL-MEDIA
                                     USAGE IS POINTER.
               10 FILLER             REDEFINES SQL-MEDIA
                                     PIC X(4).

       01 lobpaths.
           05 SQL-MEDIA-TYPE         PIC X.
           05 SQL-FILLER             PIC X(3).
           05 SQL-SESSIONS           PIC S9(9) COMP-5.
           05 SQL-TARGET.
               10 SQL-MEDIA          USAGE IS POINTER.
               10 SQL-VENDOR         REDEFINES SQL-MEDIA
                                     USAGE IS POINTER.
               10 SQL-LOCATION       REDEFINES SQL-MEDIA
                                     USAGE IS POINTER.
               10 FILLER             REDEFINES SQL-MEDIA
                                     PIC X(4).

      * COMMON Variables among the above APIs 
       77 reserved-x    pic s9(9) comp-5 value 0.
       77 msgfile-len   pic s9(4) comp-5.
       77 filetype-len  pic s9(4) comp-5 value 3.
       77 msgfile       pic x(1025).
       77 filetype      pic x(3)  value "IXF".
       77 callerac      pic s9(4) comp-5.

       Procedure Division.
       tload Section.
           display "Sample COBOL program: TLOAD".

           display "Enter your user id (default none): " 
                with no advancing.
           accept userid.

           if userid = spaces
             EXEC SQL CONNECT TO sample END-EXEC
           else
             display "Enter your password : " with no advancing
             accept passwd-name.

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

           display " ".

      * need to preset the size of structure field and counts 
           move "select id,name from staff" to stmt.
           move "insert into imptable (id,name)" to impstmt.
           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 25                to SQL-TCOLSTRG-LEN.
           move SQL-METH-D        to SQL-DCOLMETH.

           move 0                 to SQL-FILETMOD-LEN of SQL-FILETMOD.

      * 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 id and name from staff table into file: ",
                   datafile.

      ********************* 
      * EXPORT API called * 
      ********************* 
           call "sqlgexpr" using
                                 by value     datafile-len1
                                 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.

      * drop the table just in case it already exists 
           move "drop table loadtable" to statement.
           EXEC SQL EXECUTE IMMEDIATE :statement END-EXEC.
           EXEC SQL COMMIT END-EXEC.

      * create the table loadtable 
            EXEC SQL CREATE TABLE loadtable (num char(6),
                 format varchar(10))
                END-EXEC.
           move "CREATE TABLE loadtable" to errloc.
           call "checkerr" using SQLCA errloc.
           EXEC SQL COMMIT END-EXEC.
           move "COMMIT the CREATE TABLE" to errloc.
           call "checkerr" using SQLCA errloc.

      ********************************* 
      * QUIESCE TABLESPACES FOR TABLE * 
      ********************************* 
           move SQLU-QUIESCEMODE-EXCLUSIVE to quiesce-mode.
           call "sqlgvqdp" using
                                 by value tname-len
                                 by reference   tname
                                 by value       quiesce-mode
                                 by reference   reserved-x
                                 by reference   sqlca
                           returning rc.

           move "QUIESCE TABLESPACES FOR TABLE" to errloc.
           call "checkerr" using SQLCA errloc.

           display "Tablespace for loadtable has been quiesced.".

      ******** 
      * LOAD * 
      ******** 

      * initialize variables for the Load API 
           move SQLU-SERVER-LOCATION
                            to SQL-MEDIA-TYPE of datafile-list.
           move +1          to SQL-SESSIONS   of datafile-list.

      * set the pointer 
           set SQL-MEDIA of datafile-list
                            to address of datafile-len.

           move SQLU-INITIAL to callerac.

           move +0          to SQL-SESSIONS   of work-dir.
           move SQLU-LOCAL-MEDIA
                            to SQL-MEDIA-TYPE of work-dir.
           set SQL-LOCATION of work-dir to address of work-dir-len.

           move +0          to SQL-SESSIONS   of copy-target.
           move SQLU-LOCAL-MEDIA
                            to SQL-MEDIA-TYPE of copy-target.
           set SQL-LOCATION of work-dir to address of copy-dir-len.

           move +0          to SQL-SESSIONS   of lobpaths.
           move SQLU-LOCAL-MEDIA
                            to SQL-MEDIA-TYPE of lobpaths.
           set SQL-LOCATION of lobpaths to address of lob-dir-len.

           move +0 to   null-ind.
           move +0 to  reserved-x.

           initialize SQLULOAD-IN.
           move SQLULOAD-IN-SIZE to SQL-SIZE-OF-STRUCT   of SQLULOAD-IN.
           move +0               to SQL-SAVECNT          of SQLULOAD-IN.
           move +0               to SQL-RESTARTCOUNT     of SQLULOAD-IN.
           move +0               to SQL-ROWCNT           of SQLULOAD-IN.
           move +0               to SQL-WARNINGCNT       of SQLULOAD-IN.
           move +0               to SQL-DATA-BUFFER-SIZE of SQLULOAD-IN.
           move +0               to SQL-SORT-BUFFER-SIZE of SQLULOAD-IN.
           move +0               to SQL-HOLD-QUIESCE     of SQLULOAD-IN.
           move " "              to SQL-RESTARTPHASE     of SQLULOAD-IN.
           move SQLU-STATS-NONE  to SQL-STATSOPT         of SQLULOAD-IN.

           move SQL-METH-D       to SQL-DCOLMETH         of SQL-DCOLDATA.

           initialize SQLULOAD-OUT.
           move SQLULOAD-OUT-SIZE to
                              SQL-SIZE-OF-STRUCT of  SQLULOAD-out.

           move "insert into loadtable " to impstmt.
           move impstmt                  to SQL-TCOLSTRG-DATA.
           move 21                       to SQL-TCOLSTRG-LEN.
           move SQL-DEL                  to filetype.

      * Call LOAD API 
           call "sqlgload" using
                                 by value       filetype-len
                                 by value       lclfile-len
                                 by value       rmtfile-len
                                 by reference   datafile-list
                                 by reference   lobpaths
                                 by reference   SQL-DCOLDATA
                                 by reference   SQL-TCOLSTRG
                                 by reference   filetype
                                 by reference   SQL-FILETMOD
                                 by reference   lclfile
                                 by reference   rmtfile
                                 by value       callerac
                                 by reference   SQLULOAD-IN
                                 by reference   SQLULOAD-OUT
                                 by reference   work-dir
                                 by reference   copy-target
                                 by reference   null-ind
                                 by reference   reserved-x
                                 by reference   sqlca
                           returning rc.
           move "LOAD" to errloc.
           call "checkerr" using SQLCA errloc.

           display "Load of table LOADTABLE is complete.".
           display "Rows loaded: ", SQL-ROWS-LOADED.
           display "Rows committed: ", SQL-ROWS-COMMITTED.

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

       End-tload. stop run.