************************************************************************* 
      ** 
      ** Source File Name = tabspace.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: 
      **        an example showing how to use the TABLESPACE APIs 
      **        in order to: 
      **                - obtain tablespace information using lower level 
      **                  service APIs (OPEN, FETCH, CLOSE) 
      **                - obtain tablespace information using higher level 
      **                  APIs (TABLESPACE QUERY, SINGLE TABLESPACE QUERY) 
      ** 
      ** APIs USED: 
      **        TABLESPACE QUERY                sqlgmtsq 
      **        SINGLE TABLESPACE QUERY         sqlgstpq 
      **        OPEN TABLESPACE QUERY           sqlgotsq 
      **        FETCH TABLESPACE QUERY          sqlgftpq 
      **        CLOSE TABLESPACE QUERY          sqlgctsq 
      **        GET TABLESPACE STATISTICS       sqlggtss 
      **        DEREFERENCE ADDRESS             sqlgdref 
      **        FREE MEMORY                     sqlgfmem 
      ** 
      ** 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. "tabspace".

       Data Division.
       Working-Storage Section.

           copy "sql.cbl".
           copy "sqlca.cbl".
           copy "sqlenv.cbl".
           copy "sqlutil.cbl".
           copy "sqlutbsp.cbl".

           EXEC SQL BEGIN DECLARE SECTION END-EXEC.
       01 dbname            pic x(8).
       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.

      * Local Variables 
       77 rc            pic s9(9) comp-5.
       77 quotient      pic s9(9) comp-5.
       77 flags         pic s9(9) comp-5.
       77 idx           pic s9(9) comp-5.
       77 errloc        pic x(80).

      * Variables for the OPEN TABLESPACE QUERY API 
       77 option        pic s9(9) comp-5.
       77 num-ts        pic s9(9) comp-5.
       77 max-ts        pic s9(9) comp-5.
       77 ts-count      pic s9(9) comp-5.

      * Variables for GET TABLESPACE STATISTICS API 
       77 tbs-id        pic s9(9) comp-5.

      * Variables for TABLESPACE QUERY API 
       77 tbs-ptr       pic s9(9) comp-5.
       77 tbs-p-ptr     pic s9(9) comp-5.

       Procedure Division.
       Main Section.
           display "Sample COBOL program: tabspace.sqb".

      * Get database connection information. 
           display "Enter in the database name : " with no advancing.
           accept dbname.

           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.

      * Passwords in a CONNECT statement must be entered in a VARCHAR format 
      * with the length of the input string. 
           inspect passwd-name tallying passwd-length for characters
              before initial " ".

           EXEC SQL CONNECT TO :dbname USER :userid USING :passwd
               END-EXEC.
           move "CONNECT TO" to errloc.
           call "checkerr" using SQLCA errloc.

           display " ".
           display "Using the following APIs :".
           display "   OPEN TABLESPACE QUERY".
           display "   FETCH TABLESPACE QUERY".
           display "   CLOSE TABLESPACE QUERY".
           display "==========================".

           move SQLB-OPEN-TBS-ALL to option.
 
      ************************* 
      * OPEN TABLESPACE QUERY * 
      ************************* 
           call "sqlgotsq" using
                                 by reference sqlca
                                 by value     option
                                 by reference num-ts
                           returning rc.
           move "OPEN TABLESPACE QUERY" to errloc.
           call "checkerr" using SQLCA errloc.

           move 1      to max-ts.
           move num-ts to ts-count.
           move 0      to idx.

      * The FETCH API is called for each tablespace information record reported 
      * by the open API, with the "max-ts" set to 1.  This is done due to not 
      * being able to allocate memory for the number of records, "num-ts", 
      * returned by the OPEN TABLESPACE QUERY API. 
           perform Fetch-Loop thru End-Fetch-Loop
              until idx greater than or equal to ts-count.

      ************************** 
      * CLOSE TABLESPACE QUERY * 
      ************************** 
           call "sqlgctsq" using
                                 by reference sqlca
                           returning rc.
           move "CLOSE TABLESPACE QUERY" to errloc.
           call "checkerr" using SQLCA errloc.

      *************************** 
      * SINGLE TABLESPACE QUERY * 
      *************************** 
           display " ".
           display "Using the following APIs :".
           display "   SINGLE TABLESPACE QUERY".
           display "==========================".
           move 1 to tbs-id.
           call "sqlgstpq" using
                                 by reference sqlca
                                 by value     tbs-id
                                 by reference SQLB-TBSPQRY-DATA
                                 by value     SQLB-RESERVED1
                           returning rc.
           move "SINGLE TABLESPACE QUERY" to errloc.
           call "checkerr" using SQLCA errloc.

           call "sqlggtss" using
                                 by reference sqlca
                                 by value     tbs-id
                                 by reference SQLB-TBS-STATS
                           returning rc.
           move "GET TABLESPACE STATISTICS" to errloc.
           call "checkerr" using SQLCA errloc.

           perform Print-Tablespace-Info.

      ******************** 
      * TABLESPACE QUERY * 
      ******************** 
           display " ".
           display "Using the following APIs :".
           display "   TABLESPACE QUERY".
           display "==========================".
           call "sqlgmtsq" using
                                 by reference sqlca
                                 by reference ts-count
                                 by reference tbs-p-ptr
                                 by value     SQLB-RESERVED1
                                 by value     SQLB-RESERVED2
                           returning rc.
           move "TABLESPACE QUERY" to errloc.
           call "checkerr" using SQLCA errloc.

           call "sqlgdref" using
                                 by value     4
                                 by reference tbs-ptr
                                 by reference tbs-p-ptr
                           returning rc.

           move 0 to idx.
           perform TSQ-Print-Loop thru End-TSQ-Print-Loop
              until idx greater than or equal to ts-count.

      *************** 
      * FREE MEMORY * 
      *************** 
           call "sqlgfmem" using
                                 by reference sqlca
                                 by value     tbs-p-ptr
                           returning rc.
           move "FREE MEMORY" to errloc.
           call "checkerr" using SQLCA errloc.

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

       End-Main. exit.

       End-Prog.
           stop run.

       Fetch-Loop Section.
      ************************** 
      * FETCH TABLESPACE QUERY * 
      ************************** 
           move SQLB-TBSPQRY-DATA-ID to SQL-TBSPQVER.
           call "sqlgftpq" using
                                 by reference sqlca
                                 by value     max-ts
                                 by reference SQLB-TBSPQRY-DATA
                                 by reference num-ts
                           returning rc.
           move "FETCH TABLESPACE QUERY" to errloc.
           call "checkerr" using SQLCA errloc.

      ***************************** 
      * GET TABLESPACE STATISTICS * 
      ***************************** 
           move SQL-ID of SQLB-TBSPQRY-DATA to tbs-id.
           call "sqlggtss" using
                                 by reference sqlca
                                 by value     tbs-id
                                 by reference SQLB-TBS-STATS
                           returning rc.
           move "GET TABLESPACE STATISTICS" to errloc.
           call "checkerr" using SQLCA errloc.

           perform Print-Tablespace-Info.
           add 1 to idx.
       End-Fetch-Loop. exit.

       TSQ-Print-Loop Section.
      ******************************** 
      * Get TABLESPACE QUERY Results * 
      ******************************** 
           call "sqlgdref" using
                                 by value SQLB-TBSPQRY-DATA-SIZE
                                 by reference SQLB-TBSPQRY-DATA
                                 by reference tbs-ptr
                           returning rc.

           move SQL-ID of SQLB-TBSPQRY-DATA to tbs-id.

           call "sqlggtss" using
                                 by reference sqlca
                                 by value     tbs-id
                                 by reference SQLB-TBS-STATS
                           returning rc.
           move "GET TABLESPACE STATISTICS" to errloc.
           call "checkerr" using SQLCA errloc.

           perform Print-Tablespace-Info.
           add SQLB-TBSPQRY-DATA-SIZE to tbs-ptr.
           add 1 to idx.
       End-TSQ-Print-Loop. exit.

      ************************* 
      * PRINT TABLESPACE INFO * 
      ************************* 
       Print-Tablespace-Info Section.
           display "Tablespace ID            = " SQL-ID
                                    of SQLB-TBSPQRY-DATA.
           display "Tablespace Name          = " 
                   SQL-NAME of SQLB-TBSPQRY-DATA
                   (1:SQL-NAME-LEN of SQLB-TBSPQRY-DATA).

           divide SQL-FLAGS of SQLB-TBSPQRY-DATA by 16
                  giving quotient remainder flags.
           display "Type                     = " with no advancing.
           evaluate flags
             when SQLB-TBS-SMS
                   display "System managed space"
             when SQLB-TBS-DMS
                   display "Database managed space"
             when other 
                   display "Unknown"
             end-evaluate.

           divide SQL-FLAGS of SQLB-TBSPQRY-DATA by 16
                   giving flags.
           multiply flags by 16 giving flags.
           display "Contents                 = " with no advancing.
           evaluate flags
              when SQLB-TBS-ANY
                   display "regular contents"
              when SQLB-TBS-LONG
                   display "long field data"
              when SQLB-TBS-TMP
                   display "temp data"
              when other 
                   display "Unknown: " flags
            end-evaluate.

           display "State                    = " with no advancing.
           evaluate SQL-TBS-STATE of SQLB-TBSPQRY-DATA
              when SQLB-NORMAL
                 display "Normal"
              when SQLB-QUIESCED-SHARE
                 display "Quiesced: SHARE"
              when SQLB-QUIESCED-UPDATE
                 display "Quiesced: UPDATE"
              when SQLB-QUIESCED-EXCLUSIVE
                 display "Quiesced: EXCLUSIVE"
              when SQLB-LOAD-PENDING
                 display "Load pending"
              when SQLB-DELETE-PENDING
                 display "Delete pending"
              when SQLB-BACKUP-PENDING
                 display "Backup pending"
              when SQLB-ROLLFORWARD-IN-PROGRESS
                 display "Roll forward in progress"
              when SQLB-ROLLFORWARD-PENDING
                 display "Roll forward pending"
              when SQLB-RESTORE-PENDING
                 display "Restore pending"
              when SQLB-DISABLE-PENDING
                 display "Disable pending"
              when SQLB-REORG-IN-PROGRESS
                 display "Reorg in progress"
              when SQLB-BACKUP-IN-PROGRESS
                 display "Backup in progress"
              when SQLB-STORDEF-PENDING
                 display "storage must be defined"
              when SQLB-RESTORE-IN-PROGRESS
                 display "Restore in progress"
              when SQLB-STORDEF-ALLOWED
                 display "storage may be defined"
              when SQLB-STORDEF-FINAL-VERSION
                 display "storDef is in 'final' state"
              when SQLB-STORDEF-CHANGED
                 display "storDef was changed prior to rollforward"
              when SQLB-REBAL-IN-PROGRESS
                 display "dms rebalancer is active"
              when SQLB-PSTAT-DELETION
                 display "TBS deletion in progress"
              when SQLB-PSTAT-CREATION
                 display "TBS creation in progress"
              when other
                 display "UNKNOWN"
           end-evaluate.

           display "Tablespace Statistics".
           display "total pages              = " SQL-TOTAL-PAGES
                                                 of SQLB-TBS-STATS.
           display "useable pages            = " SQL-USEABLE-PAGES
                                                 of SQLB-TBS-STATS.
           display "used pages               = " SQL-USED-PAGES
                                                 of SQLB-TBS-STATS.
           display "free pages               = " SQL-FREE-PAGES
                                                 of SQLB-TBS-STATS.
           display "high water mark          = " SQL-HIGH-WATER-MARK
                                                 of SQLB-TBS-STATS.
           display " ".

       End-Print-Tablespace-Info. exit.