Preprocessor Input and Output

Regardless of the mode you use, the following files must be available when you invoke the COBOL preprocessor, as shown in Figure 2-2:

source file: a file containing the source code for the COBOL ALLBASE/SQL program or subprogram with embedded SQL commands for a DBEnvironment. The formal file designator for this input file is:
SQLIN
ALLBASE/SQL message catalog: a file containing preprocessor messages and ALLBASE/SQL error and warning messages. The formal file designator for the message catalog is as follows, with xxx being the numeric representation of the current native language:
SQLCTxxx.PUB.SYS

When you run the preprocessor in full preprocessing mode, also ensure that the DBEnvironment accessed by the program or subprogram is available.

As Figure 2-2 points out, the COBOL preprocessor creates the following temporary output files:

modified source file: a file containing a modified version of the source code in SQLIN. The formal file designator for this file is:
SQLOUT
After you use the preprocessor in full preprocessing mode, you use SQLOUT and the following two include files as input files for the COBOL compiler, as shown in Figure 2-4.
include files: files containing definitions of variables and constants used by COBOL statements the preprocessor inserts into SQLOUT. The formal file designators for these files are:
SQLVAR SQLCONST
ALLBASE/SQL message file: a file containing the preprocessor banner, error and warning messages, and other messages. The formal file designator for this file is:
SQLMSG
installable module file: a file containing a copy of the module created by the preprocessor. The formal file designator for this file is:
SQLMOD

When you run the preprocessor in full preprocessing mode, the preprocessor also stores a module in the DBEnvironment accessed by your program. The module is used at run time to execute DBEnvironment operations.

If you want to preprocess several ALLBASE/SQL application programs in the same group and account and compile and link the programs later, or you plan to compile a preprocessed program during a future session, you should do the following for each program:

Before running the preprocessor, equate SQLIN to the name of the file containing the application you want to preprocess:
:FILE SQLIN = InFile
After running the preprocessor, save and rename the output files if you do not want them overwritten. For example:

   :SAVE SQLOUT

   :RENAME SQLOUT, OutFile

   :SAVE SQLMOD

   :RENAME SQLMOD, ModFile

   :SAVE SQLVAR

   :RENAME SQLVAR, VarFile

   :SAVE SQLCONST

   :RENAME SQLCONST, ConstFile

When you are ready to compile the program, you must equate the include file names to their standard ALLBASE/SQL names. See "Preprocessor Generated Include Files" in this chapter for more information.

Figure 2-2 COBOL Preprocessor Input and Output

Figure 2-3 Compiling Preprocessor Output

Source File

The source file (SQLIN) must be an ASCII file (numbered or unnumbered) that contains at a minimum the following statements:



   IDENTIFICATION DIVISION.

   PROGRAM-ID    ProgramName.

   AnyStatement.

When parsing SQLIN, the COBOL preprocessor ignores COBOL statements and COBOL compiler directives in SQLIN except $SET, $IF, and $INCLUDE. Only the following information is parsed by the COBOL preprocessor:

The PROGRAM-ID. Unless you specify a module name in the preprocessor invocation line, the preprocessor uses the PROGRAM-ID to name the module it stores. A module name can contain as many as 20 characters and must follow the rules governing ALLBASE/SQL basic names (given in the ALLBASE/SQL Reference Manual).
Statements found between the prefix EXEC SQL and the suffix END-EXEC. These statements follow the rules given in Chapter 3 for how and where to embed SQL statements.
Statements found between the BEGIN DECLARE SECTION and END DECLARE SECTION commands. These commands delimit a declare section which contains COBOL data description entries for the host variables used in the program. Host variables are described in Chapter 4.

Figure 2-5 illustrates an SQLIN file containing a sample program using the following SQL commands highlighted by shading in the figure:

   INCLUDE SQLCA

   BEGIN DECLARE SECTION

   END DECLARE SECTION

   WHENEVER

   CONNECT

   BEGIN WORK

   COMMIT WORK

   SELECT

   SQLEXPLAIN

As the runtime dialog in Figure 2-4 illustrates, the program begins a DBE session for PartsDBE, the sample DBEnvironment. It prompts the user for a part number, then displays information about the part from the table PURCHDB.PARTS. Warning and error conditions are handled with WHENEVER and SQLEXPLAIN commands with the exception of explicit error checking after the SELECT command. The program continues to prompt for a part number until a serious error is encountered or until the user enters a slash (/).

Figure 2-4 Runtime Dialog of Program COBEX2

 

 :RUN COBEX2P

 Program to SELECT specified rows from the Parts Table - COBEX2



 Event List:

  Connect to PartsDBE

  Begin Work

  SELECT specified Part Number from Parts Table until user enters "/"

  Commit Work

  Disconnect from PartsDBE



 Connect to PartsDBE



 Enter Part Number within Parts Table or "/" to STOP> 1243-P-01

 SELECT PartNumber, PartName, SalesPrice

 Begin Work



 Part Number not found!

 Commit Work



 Enter Part Number within Parts Table or "/" to STOP> 1323-D-01

 SELECT PartNumber, PartName, SalesPrice

 Begin Work

 Commit Work



 Part Number:  1323-D-01

 Part Name:    Floppy Diskette Drive

 Sales Price:         $200.00



 Enter Part Number within Parts Table or "/" to STOP> 1823-PT-01

 SELECT PartNumber, PartName, SalesPrice

 Begin Work

 Commit Work



 Part Number:  1823-PT-01

 Part Name:    Graphics Printer

 Sales Price:         $450.00



 Enter Part Number within Parts Table or "/" to STOP> /



 END OF PROGRAM

Figure 2-5 Program COBEX2

 

   * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

   * Program COBEX2:                                             *

   * This program illustrates the use of SQL's SELECT command to *

   * retrieve one row at a time.                                 *

   * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *



    IDENTIFICATION DIVISION.



    PROGRAM-ID.             COBEX2.

    AUTHOR.                 HP TRAINING

    INSTALLATION.           HP.

    DATE-WRITTEN.           17 JULY 1987.

    DATE-COMPILED.          17 JULY 1987.

    REMARKS.                SQL'S SELECT WITH WHENEVER COMMAND.



    ENVIRONMENT DIVISION.

    CONFIGURATION SECTION.

    SOURCE-COMPUTER.        HP-3000.

    OBJECT-COMPUTER.        HP-3000.

    SPECIAL-NAMES.          CONSOLE IS TERMINAL-INPUT.



    INPUT-OUTPUT SECTION.



    FILE-CONTROL.

    SELECT CRT ASSIGN TO "$STDLIST".



    DATA DIVISION.



    FILE SECTION.

    FD CRT.

    01  PROMPT                  PIC X(34).

   $PAGE

    WORKING-STORAGE SECTION.





    EXEC SQL INCLUDE SQLCA END-EXEC.



   * * * * * *   BEGIN HOST VARIABLE DECLARATIONS  * * * * * * *

    EXEC SQL BEGIN DECLARE SECTION END-EXEC.

    01  PARTNUMBER              PIC X(16).

    01  PARTNAME                PIC X(30).

    01  SALESPRICE              PIC S9(8)V99 COMP-3.

    01  SALESPRICEIND           SQLIND.

    01  SQLMESSAGE              PIC X(132).

    EXEC SQL END DECLARE SECTION END-EXEC.

   * * * * * *   END OF HOST VARIABLE DECLARATIONS * * * * * * *



 

    77  DONE-FLAG              PIC X(01)  VALUE 'N'.

	88  NOT-DONE           VALUE 'N'.

	88  DONE               VALUE 'Y'.



    77  ABORT-FLAG             PIC X(01)  VALUE 'N'.

	88  NOT-STOP           VALUE 'N'.

	88  ABORT              VALUE 'Y'.



    01  DEADLOCK               PIC S9(9) COMP VALUE -14024.



    01  RESPONSE.

	05  RESPONSE-PREFIX    PIC X(01) VALUE SPACE.

	05  FILLER             PIC X(15) VALUE SPACES.



    01  DOLLARS                 PIC $$$,$$$,$$$.99.

   $PAGE

    PROCEDURE DIVISION.



    A100-MAIN.



	DISPLAY "Program to SELECT specified rows from "

		"the Parts Table - COBEX2".

	DISPLAY " ".

	DISPLAY "Event List:".

	DISPLAY "  Connect to PartsDBE".

	DISPLAY "  Begin Work".

	DISPLAY "  SELECT specified Part Number from the "

		"Parts Table until user enters '/' ".

	DISPLAY "  Commit Work".

	DISPLAY "  Disconnect from PartsDBE".

	DISPLAY " ".



	OPEN OUTPUT CRT.



	PERFORM A200-CONNECT-DBENVIRONMENT  THRU  A200-EXIT.



	PERFORM B100-SELECT-DATA THRU B100-EXIT

		UNTIL DONE.



	PERFORM A500-TERMINATE-PROGRAM THRU  A500-EXIT.



    A100-EXIT.

	EXIT.





    A200-CONNECT-DBENVIRONMENT.



	EXEC SQL

	     WHENEVER SQLERROR

	     GO TO S300-SERIOUS-ERROR

	END-EXEC.



	DISPLAY "Connect to PartsDBE".

	EXEC SQL CONNECT TO 'PartsDBE' END-EXEC.



    A200-EXIT.

	EXIT.



    A300-BEGIN-TRANSACTION.



	DISPLAY "Begin Work".

	EXEC SQL

	     BEGIN WORK

	END-EXEC.



    A300-EXIT.

	EXIT.



    A400-END-TRANSACTION.



	DISPLAY "Commit Work".

	EXEC SQL

	     COMMIT WORK

	END-EXEC.



    A400-EXIT.

	EXIT.



    A500-TERMINATE-PROGRAM.



	EXEC SQL

	     RELEASE

	END-EXEC.



	STOP RUN.



    A500-EXIT.

	EXIT.

   $PAGE



    B100-SELECT-DATA.



	MOVE SPACES TO RESPONSE.

	MOVE "Enter Part Number or '/' to STOP> "

	     TO PROMPT.

	WRITE PROMPT AFTER ADVANCING 1 LINE.

	ACCEPT RESPONSE.



	IF  RESPONSE-PREFIX = "/"

	    MOVE "Y" TO DONE-FLAG

	    GO TO B100-EXIT

	ELSE

	    MOVE RESPONSE TO PARTNUMBER.



	EXEC SQL

	     WHENEVER SQLERROR

	     GO TO S400-SQL-ERROR

	END-EXEC.



	EXEC SQL

	     WHENEVER SQLWARNING

	     GO TO S500-SQL-WARNING

	END-EXEC.



	EXEC SQL

	     WHENEVER NOT FOUND

	     GO TO S600-NOT-FOUND

	END-EXEC.



	DISPLAY "SELECT PartNumber, PartName and SalesPrice".



	PERFORM A300-BEGIN-TRANSACTION THRU A300-EXIT.



	EXEC SQL

	     SELECT  PARTNUMBER, PARTNAME, SALESPRICE

	       INTO :PARTNUMBER,

		    :PARTNAME,

		    :SALESPRICE :SALESPRICEIND

	       FROM  PURCHDB.PARTS

	      WHERE  PARTNUMBER = :PARTNUMBER

	END-EXEC.



	PERFORM A400-END-TRANSACTION THRU A400-EXIT.

	PERFORM B200-DISPLAY-ROW THRU B200-EXIT.



    B100-EXIT.

	EXIT.



    B200-DISPLAY-ROW.



	DISPLAY " ".

	DISPLAY "  Part Number:  " PARTNUMBER.

	DISPLAY "  Part Name:    " PARTNAME.



	IF  SALESPRICEIND < 0

	    DISPLAY "  Sales Price is NULL"

	ELSE

	    MOVE SALESPRICE  TO  DOLLARS

	    DISPLAY "  Sales Price:  " DOLLARS.



    B200-EXIT.

	EXIT.



   $PAGE

    S100-STATUS-CHECK.



	IF  SQLCODE  <  DEADLOCK

	    MOVE 'Y' TO ABORT-FLAG.



	PERFORM S200-SQL-EXPLAIN THRU S200-EXIT

		UNTIL SQLCODE = 0.



    S100-EXIT.

	EXIT.



    S200-SQL-EXPLAIN.



	EXEC SQL

	     SQLEXPLAIN  :SQLMESSAGE

	END-EXEC.



	DISPLAY SQLMESSAGE.



    S200-EXIT.

	EXIT.



    S300-SERIOUS-ERROR.



	PERFORM S100-STATUS-CHECK  THRU  S100-EXIT.

	PERFORM A500-TERMINATE-PROGRAM  THRU  A500-EXIT.



    S300-EXIT.

	EXIT.







    S400-SQL-ERROR.



	PERFORM S100-STATUS-CHECK THRU S100-EXIT.



	IF  ABORT-FLAG = 'Y'

	    PERFORM A500-TERMINATE-PROGRAM

	ELSE

	    PERFORM A400-END-TRANSACTION THRU A400-EXIT

	    GO TO B100-EXIT.



    S400-EXIT.

	EXIT.



    S500-SQL-WARNING.



	DISPLAY "SQL WARNING has occurred.  The following row "

		"of data may not be valid:".



	PERFORM B200-DISPLAY-ROW THRU B200-EXIT.



	PERFORM A400-END-TRANSACTION  THRU A400-EXIT.



	GO TO B100-EXIT.



    S500-EXIT.

	EXIT.



    S600-NOT-FOUND.



	DISPLAY " ".

	DISPLAY "Part Number not found!".



	PERFORM A400-END-TRANSACTION THRU A400-EXIT.



	GO TO B100-EXIT.



    S600-EXIT.

	EXIT.

Output File Attributes

The COBOL preprocessor output files are temporary files. When the SQLIN illustrated in Figure 2-5 is preprocessed, the attributes of the output files created are as follows:



   :listftemp,2



   TEMPORARY FILES FOR SOMEUSER.SOMEACCT,SOMEGRP



   ACCOUNT=  SOMEACCT    GROUP=  SOMEGRP



   FILENAME  CODE  ------------LOGICAL RECORD-----------  ----SPACE----

                     SIZE  TYP        EOF      LIMIT R/B  SECTORS #X MX

   SQLCONST           80B  FA          39       2048   1      128  8 10 (TEMP)

   SQLMOD            250W  FB           3       1023   1      208  2 10 (TEMP)

   SQLMSG             80B  FA          14       1023   1       96  6  8 (TEMP)

   SQLOUT             80B  FA         417      10000   1      320 11 10 (TEMP)

   SQLVAR             80B  FA          11       2048   1      128  7 10 (TEMP)

Modified Source File

As the COBOL preprocessor parses SQLIN, it copies lines from SQLIN and any file(s) included from SQLIN into SQLOUT, comments out embedded SQL commands, and inserts information around each embedded SQL command. Figure 2-6 illustrates the SQLOUT generated for the SQLIN pictured in Figure 2-5. In both preprocessing modes, the COBOL preprocessor:

Inserts an * in column 7 on each line containing an embedded SQL command to comment out the SQL command for the COBOL compiler.
Places any punctuation you place after an embedded command on the line following the last line generated for the embedded command. Note, that the period following the INCLUDE SQLCA command in SQLIN is in the same column, but on a different line in SQLOUT. In SQLOUT the period is on the line following the last line generated by the preprocessor for the INCLUDE SQLCA command.
Inserts two $INCLUDE COBOL compiler directives after the WORKING-STORAGE SECTION label. During compilation, the directives reference the include files: SQLCONST and SQLVAR.
Inserts a "Start SQL Preprocessor" comment before and an End SQL Preprocessor comment after code it modifies.

In full preprocessing mode, the preprocessor also:

Generates a COBOL declaration of the SQLCA following the INCLUDE SQLCA command.
Generates COBOL sentences providing conditional instructions following SQL commands encountered after one of the following SQL commands: WHENEVER SQLERROR, WHENEVER SQLWARNING, and WHENEVER NOT FOUND.
Generates COBOL sentences that call ALLBASE/SQL external procedures at run time. These calls reference the module stored by the preprocessor in the DBEnvironment for execution at run time. Parameters used by these external calls are defined in SQLVAR and SQLCONST.
Inserts a "Start Inserted Statements" comment before generated information.




	CAUTION: Although you can access SQLOUT, SQLVAR, and SQLCONST with an editor, you should never change the information generated by the COBOL preprocessor. Your DBEnvironment could be damaged at run time if preprocessor generated statements are altered.

If you need to change statements in SQLOUT, make the changes to SQLIN, re-preprocess SQLIN, and re-compile the output files before putting the application program into production.

Figure 2-6 Modified Source File for Program COBEX2

      * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

      * Program COBEX2:                                             *

      * This program illustrates the use of SQL's SELECT command to *

      * retrieve one row at a time.                                 *

      * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

       IDENTIFICATION DIVISION.



       PROGRAM-ID.             COBEX2.

       AUTHOR.                 HP TRAINING

       INSTALLATION.           HP.

       DATE-WRITTEN.           17 JULY 1987.

       DATE-COMPILED.          17 JULY 1987.

       REMARKS.                SQL'S SELECT WITH WHENEVER COMMAND.



       ENVIRONMENT DIVISION.

       CONFIGURATION SECTION.

       SOURCE-COMPUTER.        HP-3000.

       OBJECT-COMPUTER.        HP-3000.

       SPECIAL-NAMES.          CONSOLE IS TERMINAL-INPUT.



       INPUT-OUTPUT SECTION.



       FILE-CONTROL.

           SELECT CRT ASSIGN TO "$STDLIST".



       DATA DIVISION.



       FILE SECTION.

       FD CRT.

       01  PROMPT                  PIC X(34).

      $PAGE

       WORKING-STORAGE SECTION.



      {{**** Start SQL Preprocessor ****\

      $INCLUDE SQLCONST\

      $INCLUDE SQLVAR\

      **** End SQL Preprocessor   ****}}



      {{**** Start SQL Preprocessor ****\

      *EXEC SQL INCLUDE SQLCA END-EXEC.\

      **** Start Inserted Statements ****\

       01  SQLCA.\

           05  SQLCAID       PIC X(8).\

           05  SQLCABC       PIC S9(9) COMP SYNC.\

           05  SQLCODE       PIC S9(9) COMP SYNC.\

           05  SQLERRM.\

               49  SQLERRML  PIC S9(9) COMP SYNC.\

               49  SQLERRMC  PIC X(256).}}

{{           05  SQLERRP       PIC X(8).\

           05  SQLERRD       OCCURS 6 TIMES\

                             PIC S9(9) COMP SYNC.\

           05  SQLWARN.\

               10  SQLWARN0  PIC X(1).\

               10  SQLWARN1  PIC X(1).\

               10  SQLWARN2  PIC X(1).\

               10  SQLWARN3  PIC X(1).\

               10  SQLWARN4  PIC X(1).\

               10  SQLWARN5  PIC X(1).\

               10  SQLWARN6  PIC X(1).\

               10  SQLWARN7  PIC X(1).\

           05  SQLEXT1       PIC X(4).\

           05  SQLEXT2       PIC X(4).\

      **** End SQL Preprocessor   ****}}





      * * * * * *   BEGIN HOST VARIABLE DECLARATIONS  * * * * * * *



      {{**** Start SQL Preprocessor ****\

      *EXEC SQL BEGIN DECLARE SECTION END-EXEC.\

      **** End SQL Preprocessor   ****}}



       01  PARTNUMBER              PIC X(16).

       01  PARTNAME                PIC X(30).

       01  SALESPRICE              PIC S9(8)V99 COMP-3.

       01  SALESPRICEIND           PIC S9(4) COMP.

       01  SQLMESSAGE              PIC X(132).



      {{**** Start SQL Preprocessor ****\

      *EXEC SQL END DECLARE SECTION END-EXEC.\

      **** End SQL Preprocessor   ****}}



      * * * * * *   END OF HOST VARIABLE DECLARATIONS * * * * * * *



       77  DONE-FLAG              PIC X(01)  VALUE 'N'.

           88  NOT-DONE           VALUE 'N'.

           88  DONE               VALUE 'Y'.



       77  ABORT-FLAG             PIC X(01)  VALUE 'N'.

           88  NOT-STOP           VALUE 'N'.

           88  ABORT              VALUE 'Y'.



       01  DEADLOCK               PIC S9(9) COMP VALUE -14024.



       01  RESPONSE.

           05  RESPONSE-PREFIX    PIC X(01) VALUE SPACE.

           05  FILLER             PIC X(15) VALUE SPACES.



       01  DOLLARS                 PIC $$$,$$$,$$$.99.

      $PAGE

       PROCEDURE DIVISION.



       A100-MAIN.



           DISPLAY "Program to SELECT specified rows from "

                   "the Parts Table - COBEX2".

           DISPLAY " ".

           DISPLAY "Event List:".

           DISPLAY "  Connect to PartsDBE".

           DISPLAY "  Begin Work".

           DISPLAY "  SELECT specified Part Number from the "

                   "Parts Table until user enters '/' ".

           DISPLAY "  Commit Work".

           DISPLAY "  Disconnect from PartsDBE".

           DISPLAY " ".



           OPEN OUTPUT CRT.



           PERFORM A200-CONNECT-DBENVIRONMENT  THRU  A200-EXIT.



           PERFORM B100-SELECT-DATA THRU B100-EXIT

                   UNTIL DONE.



           PERFORM A500-TERMINATE-PROGRAM THRU  A500-EXIT.



       A100-EXIT.

           EXIT.



       A200-CONNECT-DBENVIRONMENT.





      {{**** Start SQL Preprocessor ****\

      *    EXEC SQL\

      *         WHENEVER SQLERROR\

      *         GO TO S300-SERIOUS-ERROR\

      *    END-EXEC\

      **** Start Inserted Statements ****\

           CONTINUE\

      **** End SQL Preprocessor   ****}}

                   .



           DISPLAY "Connect to PartsDBE".



      {{**** Start SQL Preprocessor ****\

      *    EXEC SQL CONNECT TO 'PartsDBE' END-EXEC\

      **** Start Inserted Statements ****}}

           {{MOVE 264 TO SQLCONLEN\

           CALL "SQLXCONO" USING SQLCA, SQLCONLEN, SQLCONST1\

           IF SQLCODE IS NEGATIVE\

             GO TO S300-SERIOUS-ERROR\

           END-IF\

      **** End SQL Preprocessor   ****}}

                                                  .



       A200-EXIT.

           EXIT.



       A300-BEGIN-TRANSACTION.



           DISPLAY "Begin Work".



      {{**** Start SQL Preprocessor ****\

      *    EXEC SQL\

      *         BEGIN WORK\

      *    END-EXEC\

      **** Start Inserted Statements ****\

           MOVE 16 TO SQLCONLEN\

           CALL "SQLXCONO" USING SQLCA, SQLCONLEN, SQLCONST2\

           IF SQLCODE IS NEGATIVE\

             GO TO S300-SERIOUS-ERROR\

           END-IF\

      **** End SQL Preprocessor   ****}}

                   .



       A300-EXIT.

           EXIT.



       A400-END-TRANSACTION.



           DISPLAY "Commit Work".



      {{**** Start SQL Preprocessor ****\

      *    EXEC SQL\

      *         COMMIT WORK\

      *    END-EXEC\

      **** Start Inserted Statements ****\

           MOVE 8 TO SQLCONLEN\

           CALL "SQLXCONO" USING SQLCA, SQLCONLEN, SQLCONST3\

           IF SQLCODE IS NEGATIVE\

             GO TO S300-SERIOUS-ERROR\

           END-IF\

      **** End SQL Preprocessor   ****}}

 

 





       A400-EXIT.

           EXIT.



       A500-TERMINATE-PROGRAM.









      {{**** Start SQL Preprocessor ****\

      *    EXEC SQL\

      *         RELEASE\

      *    END-EXEC\

      **** Start Inserted Statements ****\

           MOVE 56 TO SQLCONLEN\

           CALL "SQLXCONO" USING SQLCA, SQLCONLEN, SQLCONST4\

           IF SQLCODE IS NEGATIVE\

             GO TO S300-SERIOUS-ERROR\

           END-IF\

      **** End SQL Preprocessor   ****}}

                   .



           STOP RUN.



       A500-EXIT.

           EXIT.

      $PAGE

       B100-SELECT-DATA.



           MOVE SPACES TO RESPONSE.



           MOVE "Enter Part Number or '/' to STOP> "

                TO PROMPT.

           WRITE PROMPT AFTER ADVANCING 1 LINE.

           ACCEPT RESPONSE.



           IF  RESPONSE-PREFIX = "/"

               MOVE "Y" TO DONE-FLAG

               GO TO B100-EXIT

           ELSE

               MOVE RESPONSE TO PARTNUMBER.





      {{**** Start SQL Preprocessor ****\

      *    EXEC SQL\

      *         WHENEVER SQLERROR\

      *         GO TO S400-SQL-ERROR\

      *    END-EXEC\

      **** Start Inserted Statements ****\

           CONTINUE\

      **** End SQL Preprocessor   ****}}



      {{**** Start SQL Preprocessor ****\

      *    EXEC SQL\

      *         WHENEVER SQLWARNING\

      *         GO TO S500-SQL-WARNING\

      *    END-EXEC\

      **** Start Inserted Statements ****\

           CONTINUE\

      **** End SQL Preprocessor   ****}}





      {{**** Start SQL Preprocessor ****\

      *    EXEC SQL\

      *         WHENEVER NOT FOUND\

      *         GO TO S600-NOT-FOUND\

      *    END-EXEC\

      **** Start Inserted Statements ****\

           CONTINUE\

      **** End SQL Preprocessor   ****}}

                   

           DISPLAY "SELECT PartNumber, PartName and SalesPrice".



           PERFORM A300-BEGIN-TRANSACTION THRU A300-EXIT.



      {{**** Start SQL Preprocessor ****\

      *    EXEC SQL\

      *         SELECT  PARTNUMBER, PARTNAME, SALESPRICE\

      *           INTO :PARTNUMBER,\

      *                :PARTNAME,\

      *                :SALESPRICE :SALESPRICEIND\

      *           FROM  PURCHDB.PARTS\

      *          WHERE  PARTNUMBER = :PARTNUMBER\

      *    END-EXEC\

      **** Start Inserted Statements ****\

           MOVE PARTNUMBER\

             TO SQLREC1-FIELD1}}



 

           {{MOVE 1 TO SQLSECNUM\

           MOVE 16 TO SQLINLEN\

           MOVE 54 TO SQLOUTLEN\

           CALL "SQLXFETO" USING SQLCA, SQLOWNER, SQLMODNAME,\

             SQLSECNUM, SQLTEMPV, SQLINLEN, SQLOUTLEN, SQLTRUE\

           IF SQLCODE IS ZERO\

             MOVE SQLREC2-FIELD1\

               TO PARTNUMBER\

             MOVE SQLREC2-FIELD2\

               TO PARTNAME\

             MOVE SQLREC2-FIELD3-IND\

               TO SALESPRICEIND\

             IF SQLREC2-FIELD3-IND IS NOT NEGATIVE\

               MOVE SQLREC2-FIELD3\

                 TO SALESPRICE\

             END-IF\

             IF SQLWARN0 IS EQUAL TO "W"\

               GO TO S500-SQL-WARNING\

             END-IF\

           ELSE\

             IF SQLCODE IS EQUAL TO 100\

               GO TO S600-NOT-FOUND\

             END-IF\

             IF SQLCODE IS NEGATIVE\

               GO TO S400-SQL-ERROR\

             END-IF\

             CONTINUE\

           END-IF\

      **** End SQL Preprocessor   ****}}



           PERFORM A400-END-TRANSACTION THRU A400-EXIT.



           PERFORM B200-DISPLAY-ROW THRU B200-EXIT.



       B100-EXIT.

           EXIT.



       B200-DISPLAY-ROW.



           DISPLAY " ".

           DISPLAY "  Part Number:  " PARTNUMBER.

           DISPLAY "  Part Name:    " PARTNAME.



           IF  SALESPRICEIND < 0

               DISPLAY "  Sales Price is NULL"

           ELSE

               MOVE SALESPRICE  TO  DOLLARS

               DISPLAY "  Sales Price:  " DOLLARS.



       B200-EXIT.

           EXIT.

      $PAGE

       S100-STATUS-CHECK.



           IF  SQLCODE  <  DEADLOCK

               MOVE 'Y' TO ABORT-FLAG.



           PERFORM S200-SQL-EXPLAIN THRU S200-EXIT

                   UNTIL SQLCODE = 0.



       S100-EXIT.

           EXIT.



       S200-SQL-EXPLAIN.





      {{**** Start SQL Preprocessor ****\

      *    EXEC SQL\

      *         SQLEXPLAIN  :SQLMESSAGE\

      *    END-EXEC\

      **** Start Inserted Statements ****\

           MOVE SPACES TO SQLREC4\

           MOVE 132 TO SQLINLEN\

           CALL "SQLXPLNO" USING SQLCA, SQLTEMPV, SQLINLEN,\

             SQLFALSE\

           MOVE SQLREC4-FIELD1\

             TO SQLMESSAGE\

      **** End SQL Preprocessor   ****}}

                   .



           DISPLAY SQLMESSAGE.



       S200-EXIT.

           EXIT.



       S300-SERIOUS-ERROR.



           PERFORM S100-STATUS-CHECK  THRU  S100-EXIT.

           PERFORM A500-TERMINATE-PROGRAM  THRU  A500-EXIT.



       S300-EXIT.

           EXIT.



       S400-SQL-ERROR.



           PERFORM S100-STATUS-CHECK THRU S100-EXIT.



           IF  ABORT-FLAG = 'Y'

               PERFORM A500-TERMINATE-PROGRAM

           ELSE

               PERFORM A400-END-TRANSACTION THRU A400-EXIT

               GO TO B100-EXIT.



       S400-EXIT.

           EXIT.



       S500-SQL-WARNING.



           DISPLAY "SQL WARNING has occurred.  The following row "

                   "of data may not be valid:".



           PERFORM B200-DISPLAY-ROW THRU B200-EXIT.



           PERFORM A400-END-TRANSACTION  THRU A400-EXIT.



           GO TO B100-EXIT.



       S500-EXIT.

           EXIT.



       S600-NOT-FOUND.



           DISPLAY " ".

           DISPLAY "Part Number not found!".



           PERFORM A400-END-TRANSACTION THRU A400-EXIT.



           GO TO B100-EXIT.



       S600-EXIT.

           EXIT.

Preprocessor Generated Include Files

SQLCONST and SQLVAR are preprocessor generated include files which contain declarations for variables and constants referenced in preprocessor generated sentences in SQLOUT. Figure 2-7 and Figure 2-8 illustrate, respectively, the SQLCONST and SQLVAR files that correspond to the SQLOUT file in Figure 2-6. Note that the preprocessor inserts the following two COBOL compiler directives to reference SQLCONST and SQLVAR:

   $INCLUDE SQLCONST

   $INCLUDE SQLVAR

These two directives are always inserted into the WORKING-STORAGE SECTION.

Even if you use file equations to redirect the include files, the preprocessor still inserts the same $INCLUDE directives. Therefore when you compile preprocessor output, ensure that the preprocess-time file equations are in effect so the correct include files are compiled:

   :FILE SQLCONST=MYCONST

   :FILE SQLVAR=MYVAR

   :FILE SQLIN=MYPROG

   :FILE SQLOUT=MYSQLPRG



    .  Then the COBOL preprocessor is invoked in full preprocessing mode.

    .  Later, when then COBOL compiler is invoked, the following

    .  file equations must be in effect:



   :FILE SQLCONST=MYCONST

   :FILE SQLVAR=MYVAR

   :COB85XL MYSQLPRG, $NEWPASS, $NULL

Figure 2-7 Sample Constant Include File

01  SQLCONST PIC X.

01  SQLCONST1.

 05 SQL0 PIC X(36) VALUE "(4)5061727473444245202020202020".

 05 SQL1 PIC X(36) VALUE "202020202020202020202020202020202020".

 05 SQL2 PIC X(36) VALUE "202020202020202020202020202020202020".

 05 SQL3 PIC X(36) VALUE "202020202020202020202020202020202020".

 05 SQL4 PIC X(36) VALUE "202020202020202020202020202020202020".

 05 SQL5 PIC X(36) VALUE "202020202020202020202020202020202020".

 05 SQL6 PIC X(36) VALUE "202020202020202020202020202020202020".

 05 SQL7 PIC X(12) VALUE "202020202020".

01  SQLCONST2.

 05 SQL0 PIC X(16) VALUE "00A6007F00110061".

e  SQLCONST3.

 05 SQL0 PIC X(8) VALUE "00A10000".

01  SQLCONST4.

 05 SQL0 PIC X(36) VALUE 00AF00002020202020202020202020202020".

 05 SQL1 PIC X(12) VALUE "202020202020".

01  SQLTEMPV PIC X(132) VALUE " ".

Figure 2-8 Sample Variable Include File

       01  SQLREC1 REDEFINES SQLTEMPV.

           05  SQLREC1-FIELD1      PIC X(16).

       01  SQLREC2 REDEFINES SQLTEMPV.

           05  SQLREC2-FIELD1      PIC X(16).

           05  SQLREC2-FIELD2      PIC X(30).

           05  SQLREC2-FIELD3      PIC S9(8)V9(2) COMP-3.

           05  SQLREC2-FIELD3-IND  PIC S9(4) COMP.

       01  SQLREC3 REDEFINES SQLTEMPV.

           05  FILLER      PIC X(108).

       01  SQLREC4 REDEFINES SQLTEMPV.

           05  SQLREC4-FIELD1      PIC X(132).

COBOL COPY Statement Support

ALLBASE/SQL now supports the COBOL COPY statement. The preprocessor scans your source code and inserts the indicated copylib modules into the preprocessed code.

The REPLACING clause, if specified, is expanded during compilation (not during preprocessing).

Two new compiler directives are used in your source code to set and unset the COPY statement feature. These are shown in the table below.

Table 2-1 Compiler Directives for Implementing the COBOL COPY Statement

Directive	How Used
$SQL COPY	Turns on ALLBASE/SQL COPY statement processing.
$SQL NOCOPY	Turns off ALLBASE/SQL COPY statement processing.

You can use the directives at any point in your source code. Perhaps your application has many COPY statements, some of which reference modules containing ALLBASE/SQL commands. If you want only ALLBASE/SQL copy code expanded in your preprocessor listing, delimit the appropriate COPY statements with the $SQL COPY and $SQL NOCOPY directives. If you want all copy code expanded at preprocessing time, put the $SQL COPY statement at the beginning of your file. When you do not use these compiler directives, COPY statements are processed at compile time. This is appropriate when your copy code modules do not contain ALLBASE/SQL commands.




	NOTE: Insertion of copy text into the preprocessor output file may cause the current file limit for SQLOUT to be exceeded.

The following sections are presented in this section:

Using the COPY Statement with ALLBASE/SQL.
COPY Statement Code Example.

Using the COPY Statement with ALLBASE/SQL

COPY statement syntax and a complete explanation of its use in COBOL is found in chapter 13 of the HP COBOL II/XL Reference Manual.

No syntactical differences exist between COBOL and ALLBASE/SQL implementation of the COPY statement. However, you should be aware of the following specifics:

The reserved word NOLIST can be used to suppress printing the contents of the copylib module in the compiler listing.
Any ALLBASE/SQL commands within a copy file will be preprocessed, but the REPLACING phrase will have no effect on them.
The COPY statement cannot be used within an ALLBASE/SQL command.

COPY Statement Code Example

Suppose you want to copy a generic error checking routine into your application. The routine is located in a module named ERRORCPY in the errorlib library. You embed the following COBOL COPY statement in your source code:

    

         $SQL COPY

          COPY ERRORCPY OF ERRORLIB

         $SQL NOCOPY

The preprocessed output file will be as follows. (Note that ALLBASE/SQL commands within the copy file have been expanded just as they would have been if the code had been a part of the main source file.)

         **** Start SQL Preprocessor ****

         *SQL COPY                                                                 

         **** End SQL Preprocessor   ****



         **** Start SQL Preprocessor ****

         *copy ERRORCPY.

         **** Start insertion of text from: ERRORCPY

          S100-STATUS-CHECK.                                               ERRORCPY

                                                                           ERRORCPY

              IF  SQLCODE  <  DEADLOCK                                     ERRORCPY

                  MOVE 'Y' TO ABORT-FLAG.                                  ERRORCPY

                                                                           ERRORCPY

              PERFORM S200-SQL-EXPLAIN THRU S200-EXIT                      ERRORCPY

                      UNTIL SQLCODE = 0.                                   ERRORCPY

                                                                           ERRORCPY

          S100-EXIT.                                                       ERRORCPY

              EXIT.                                                        ERRORCPY

                                                                           ERRORCPY

          S200-SQL-EXPLAIN.                                                ERRORCPY

                                                                           ERRORCPY

                                                                           ERRORCPY

         **** Start SQL Preprocessor ****                                  

         *    EXEC SQL                                                    

         *         SQLEXPLAIN  :SQLMESSAGE                                

         *    END-EXEC                                                     

         **** Start Inserted Statements ****                               

              MOVE SPACES TO SQLREC2                                       

              MOVE 2 TO SQLINLEN                                           

              CALL SQLXCBL USING SQLXPLN, SQLCA, SQLTEMPV, SQLINLEN,       

                SQLFALSE                                                   

              MOVE SQLREC2-FIELD1                                          

                TO SQLMESSAGE                                              

         **** End SQL Preprocessor   ****                                  

                      .                                                    ERRORCPY

                                                                           ERRORCPY

              DISPLAY SQLMESSAGE.                                          ERRORCPY

                                                                           ERRORCPY

          S200-EXIT.                                                       ERRORCPY

              EXIT.                                                        ERRORCPY

         **** End insertion of text from: ERRORCPY

         **** End SQL Preprocessor   ****

                                                                                   

         **** Start SQL Preprocessor ****

         *SQL NOCOPY                                                               

         **** End SQL Preprocessor   ****

$SET and $IF Statement Support

ALLBASE/SQL supports the COBOL SET and IF compiler directives. If you want to preprocess only certain parts of your source code to send to the COBOL compiler, you can set up to ten switches to either ON or OFF. You can then test a flag for ON by testing whether it evaluates to a boolean value of true. If the switch evaluates to true, source records are sent to the compiler, beginning with the first one following the IF statement, and continuing until another IF statement evaluates to false.

The SET statement is used to turn a switch off and on. Up to ten named software switches of the form Xn are available, where n is an integer in the range of 0 through 9. The SET statement has the following syntax:

$SET [Xn={ ON OFF } [,Xr={ ON OFF }] ...]

$IF [Xn={ ON OFF }]

Initially, all compilation switches are set to OFF.

A SET statement can appear anywhere in the source text. IF the SET statement is used without parameters in the form of SET, all switches are set to OFF.

The IF statement interrogates any of the ten compilations switches. If the condition specified in the IF statement evaluates to true, source records are sent to the compiler. An appearance of an IF statement always terminates the influence of any previous IF statement.

An IF statement that appears without parameters has the same effect as an IF satement that evaluates to true.

When an IF statement evaluates to false, no source records are sent to the compiler until an IF statement evaluates to true is encountered.

Code Example

Suppose you want to conditionally preprocess some parts of your source code and send it to the compiler, and not preprocess other parts of your source code, you could use SET and IF statements in your source code, as follows:

   $SET X1=ON,X3=ON

      .

      .

      .

   $IF X1=ON

   $COMMENT Since X1 is ON, continue sending records to the compiler.

      .

      .

      .

   $IF X3=OFF

   $COMMENT This $IF statement cancels the preceding one. Since X3 is &

   $        set to ON, do not send the following records to the compiler.

   $SET X2=ON

   $CONTROL NOLIST.

   $COMMENT Note that the SET and $CONTROL statements are ignored, &

   $        since the previous IF statement was false.

      .

      .

      .

   $IF

   $COMMENT Previous IF statement conditions are terminated, and &

   $        preprocessing and compilation resume.

Considerations When Using $SET and $IF

SET and IF statement syntax and a complete explanation of their use in COBOL is found in chapter 13 of the HP COBOL II/XL Reference Manual.

No syntactical differences exist between COBOL and ALLBASE/SQL implementation of the SET and IF statements. However, you should be aware of the following specifics:

If any other SET or CONTROL statements are encountered in the source records that are being passed over as a result of the IF, they are ignored by the preprocessor and are not sent to the compiler.
EDIT, PAGE, and TITLE statements within a range of source statements being ignored by the preprocessor are executed.
The operations of merging of a text and master source file, and copying of a merged file to a new file are unaffected by IF statements.
Use the CONTROL preprocessor statement specifying the NOMIXED parameter when you do not want to list source records not sent to the compiler.

ALLBASE/SQL Message File

Messages placed in the ALLBASE/SQL message file (SQLMSG) come from the ALLBASE/SQL message catalog. The formal file designator for the message catalog is:

   SQLCTxxx.PUB.SYS

where xxx is the numerical value for the current language. If this catalog cannot be opened, ALLBASE/SQL looks for the default NATIVE-3000 message catalog:

   SQLCT000.PUB.SYS

If the default catalog cannot be opened, ALLBASE/SQL returns an error message saying that the catalog file is not available. If the NATIVE-3000 catalog is available, the user sees a warning message indicating that the default catalog is being used. SQLMSG messages contain four parts:

A banner:

      

                                              WED, OCT 25, 1991,  1:38 PM

     HP36216-E1.02            COBOL Preprocessor/3000          ALLBASE/SQL

     (C) COPYRIGHT HEWLETT-PACKARD CO. 1982,1983,1984,1985,1986,1987,1988,

     1989,1990,1991. ALL RIGHTS RESERVED.

A summary of the preprocessor invocation conditions:



     SQLIN                = COBEX2.SOMEGRP.SOMEACCT

     DBEnvironment        = partsdbe

     Module Name          = COBEX2

Warnings and errors encountered during preprocessing:

      

         39          01 PARTNUMBER               PIC X(16) COMP.

                                                         |

   ******  Syntax error in host variable declaration.   (DBERR 10932)

                             .

                             .

                             .

     There are errors.  No sections stored.

A summary of the results of preprocessing:



      2 ERRORS    0 WARNINGS

     END OF PREPROCESSING.

When you equate SQLMSG to $STDLIST, all these messages appear at the terminal during your session or in the job stream listing. When SQLMSG is not equated to $STDLIST, parts 1 and 4 are still sent to $STDLIST, and all parts appear in the file equated to SQLMSG:

    

   :FILE SQLMSG=MyMsg;Rec=-80,16,f,Ascii

   :FILE SQLIN=COBEX2

   :RUN PSQLCOB.PUB.SYS;INFO="PartsDBE (DROP)"

                                             WED, OCT 25, 1991,  1:38 PM

   HP36216-02A.E1.02          COBOL Preprocessor/3000        ALLBASE/SQL

   (C) COPYRIGHT HEWLETT-PACKARD CO. 1982,1983,1984,1985,1986,1987,1988,

   1989,1990,1991. ALL RIGHTS RESERVED.



    1 ERRORS    0 WARNINGS

   END OF PREPROCESSING.



   PROGRAM TERMINATED IN AN ERROR STATE.  (CIERR 976)

If you want to keep the message file, you should save the file you equate to SQLMSG. It is created as a temporary file.

As illustrated in Figure 2-9, a line number is often provided in SQLMSG. This line number references the line in SQLIN containing the command in question. A message accompanied by a number may also appear. You can refer to the ALLBASE/SQL Message Manual for additional information on the exception condition when these numbered messages appear.

Figure 2-9 Sample SQLMSG Showing Error



:EDITOR

HP32501A.07.20 EDIT/3000 FRI, OCT 27, 1991, 10:20 AM

(C) HEWLETT-PACKARD CO. 1990

/T SQLMSG;L ALL UNN

FILE UNNUMBERED

              .

              .



SQLIN                = COBEX2.SOMEGRP.SOMEACCT

DBEnvironment        = partsdbe

Module Name          = COBEX2



       SELCT PARTNUMBER, PARTNAME, SALESPRICE INTO :PARTNUMBER, :PARTNAME,

       :SALESPRICE :SALESPRICEIND FROM PURCHDB.PARTS WHERE PARTNUMBER =

       :PARTNUMBER ;



******  ALLBASE/SQL errors (DBERR 10977)

******  in SQL statement ending in line 176

***     <1001> Syntax error.  (DBERR 1001)



There are errors.  No sections stored.



 1 ERRORS   0 WARNINGS

END OF PREPROCESSING.

As Figure 2-11 illustrates, the preprocessor can terminate with a warning message. Although a section is stored for the semantically incorrect command, the section is marked as invalid and will not execute at run time if it cannot be validated.

Figure 2-10 Sample SQLMSG Showing Warning



:EDITOR

HP32501A.07.20 EDIT/3000 FRI, OCT 27 1991, 10:20 AM

(C) HEWLETT-PACKARD CO. 1990

/T SQLMSG;L ALL UNN

FILE UNNUMBERED

           .

           .

           .

SQLIN                = COBEX2.SOMEGRP.SOMEACCT

DBEnvironment        = partsdbe

Module Name          = COBEX2



       SELECT PARNUMBER, PARTNAME, SALESPRICE INTO :PARTNUMBER, :PARTNAME,

       :SALESPRICE :SALESPRICEIND FROM PURCHDB.PARTS WHERE PARTNUMBER =

       :PARTNUMBER ;



******  ALLBASE/SQL warnings   (DBWARN 10602)

******  in SQL statement ending in line 176

***  Column PARNUMBER not found.  (DBERR 2211)



  1 Sections stored in DBEnvironment.



 0 ERRORS   1 WARNINGS

END OF PREPROCESSING

Installable Module File

When the COBOL preprocessor stores a module in the system catalog of a DBEnvironment at preprocessing time, it places a copy of the module in an installable module file. By default the installable module file is named SQLMOD. The module in this file can be installed into a DBEnvironment different from the DBEnvironment accessed at preprocessing time by using the INSTALL command in ISQL. For example:



   :RUN PSQLCBL.PUB.SYS;INFO="DBEnvironmentName&

   (MODULE(InstalledModuleName)DROP)"



        If you want to preserve SQLMOD after preprocessing,

        you must keep it as a permanent file.  Rename SQLMOD

        after making it permanent:



   :SAVE SQLMOD

   :RENAME SQLMOD,MYMOD



        Before invoking ISQL to install this module file,

        you may have to transport it and its related

        program file to the machine containing the target

        DBEnvironment.  After all the files are restored on the

        target machine, you invoke ISQL on the machine containing

        the target DBEnvironment:



   :ISQL



        In order to install the module, you need CONNECT

        or DBA authority in the target DBEnvironment:



   isql=> CONNECT TO 'PARTSDBE.SomeGrp.SomeAcct';

   isql=> INSTALL;



   File name> MYMOD.SOMEGRP.SOMEACCT;

   Name of module in this file:  JOANN@SOMEACCT.COBEX2

   Number of sections installed:  1

   COMMIT WORK to save to DBEnvironment.



   isql=> COMMIT WORK;

   isql=>

Stored Sections

In full preprocessing mode, the preprocessor stores a section for each embedded command except:

   BEGIN DECLARE SECTION           INCLUDE

   BEGIN WORK                      OPEN

   CLOSE                           PREPARE

   COMMIT WORK                     RELEASE

   CONNECT                         ROLLBACK WORK

   DECLARE CURSOR                  SAVEPOINT

   DELETE WHERE CURRENT            START DBE

   DESCRIBE                        STOP DBE

   END DECLARE SECTION             SQLEXPLAIN

   EXECUTE                         TERMINATE USER

   EXECUTE IMMEDIATE               UPDATE WHERE CURRENT

   FETCH                           WHENEVER

The commands listed above either require no authorization to execute or are executed based on information contained in the compilable preprocessor output files.

When the preprocessor stores a section, it actually stores what is known as an input tree and a run tree. The input tree consists of an uncompiled command. The run tree is the compiled, executable form of the command.

If a section is valid at run time, ALLBASE/SQL executes the appropriate run tree when the SQL command is encountered in the application program. If a section is invalid, ALLBASE/SQL determines whether the objects referenced in the sections exist and whether current authorization criteria are satisfied. When an invalid section can be validated, ALLBASE/SQL dynamically recompiles the input tree to create an executable run tree and executes the command. When a section cannot be validated, the command is not executed, and an error condition is returned to the program.

There are three types of sections:

Sections for executing the SELECT command associated with a DECLARE CURSOR command.
Sections for executing the SELECT command associated with a CREATE VIEW command.
Sections for all other commands for which the preprocessor stores a section.

Figure 2-11 illustrates the kinds of information in the system catalog that describe each type of stored section. The query result illustrated was extracted from the system view named SYSTEM.SECTION by using ISQL. The columns in Figure 2-11 have the following meanings:

NAME: This column contains the name of the module to which a section belongs. You specify a module name when you invoke the preprocessor; the module name is by default the PROGRAM-ID.
OWNER: This column identifies the owner of the module. You specify an owner name when you invoke the preprocessor; the owner name is by default the log-on UserName@AccountName associated with the preprocessing session.
DBEFILESET: This column indicates the DBEFileSet with which DBEFiles housing the section are associated.
SECTION: This column gives the section number. Each section associated with a module is assigned a number by the preprocessor as it parses the related SQL command at preprocessing time.

TYPE: This column identifies the type of section:

	   1 = SELECT associated with a cursor

	   2 = SELECT defining a view

	   0 = All other sections

VALID: This column identifies whether a section is valid or invalid:
0 = invalid 1 = valid

Figure 2-11 Information in SYSTEM.SECTION on Stored Sections

isql=>SELECT NAME,OWNER,DBEFILESET,SECTION,TYPE,VALID FROM SYSTEM.SECTION;



SELECT NAME,OWNER,DBEFILESET,SECTION,TYPE,VALID FROM SYSTEM.SECTION;

----------------------------------------------------------------------

NAME              |OWNER        |DBEFILESET   |SECTION  |TYPE  |VALID

----------------------------------------------------------------------

TABLE             |SYSTEM       |SYSTEM       |        0|     2|     0

COLUMN            |SYSTEM       |SYSTEM       |        0|     2|     0

INDEX             |SYSTEM       |SYSTEM       |        0|     2|     0

SECTION           |SYSTEM       |SYSTEM       |        0|     2|     0

DBEFILESET        |SYSTEM       |SYSTEM       |        0|     2|     0

DBEFILE           |SYSTEM       |SYSTEM       |        0|     2|     0

SPECAUTH          |SYSTEM       |SYSTEM       |        0|     2|     0

TABAUTH           |SYSTEM       |SYSTEM       |        0|     2|     0

COLAUTH           |SYSTEM       |SYSTEM       |        0|     2|     0

MODAUTH           |SYSTEM       |SYSTEM       |        0|     2|     0

GROUP             |SYSTEM       |SYSTEM       |        0|     2|     0

VIEWDEF           |SYSTEM       |SYSTEM       |        0|     2|     0

HASH              |SYSTEM       |SYSTEM       |        0|     2|     0

CONSTRAINT        |SYSTEM       |SYSTEM       |        0|     2|     0

CONSTRAINTCOL     |SYSTEM       |SYSTEM       |        0|     2|     0

CONSTRAINTINDEX   |SYSTEM       |SYSTEM       |        0|     2|     0

COLDEFAULT        |SYSTEM       |SYSTEM       |        0|     2|     0

TEMPSPACE         |SYSTEM       |SYSTEM       |        0|     2|     0

PARTINFO          |PURCHDB      |SYSTEM       |        0|     2|     0

VENDORSTATISTICS  |PURCHDB      |SYSTEM       |        0|     2|     0

COBEX2            |KAREN@THOMAS |SYSTEM       |        1|     0|     1

EXP11             |KAREN@THOMAS |SYSTEM       |        1|     1|     1

EXP11             |KAREN@THOMAS |SYSTEM       |        2|     0|     1

----------------------------------------------------------------------

Number of rows selected is 16.

U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] <n>,or e[nd]>

The first eleven rows in this query result describe the sections stored for the system views. The next two rows describe the two views in the sample database: PURCHDB.PARTINFO and PURCHDB.VENDORSTATISTICS. Views are always stored as invalid sections, because the run tree is always generated at run time.

The remaining rows describe sections associated with two preprocessed programs. COBEX2 contains only one section, for executing the SELECT command in the program illustrated in Figure 2-6. EXP11 contains two sections, one for executing the SELECT command associated with a DECLARE CURSOR command and one for executing a FETCH command.

Stored sections remain in the system catalog until they are deleted with the DROP MODULE command or by invoking the preprocessor with the DROP option:



   isql=> DROP MODULE COBEX2;



               or



   :RUN PSQLCOB.PUB.SYS;INFO="PartsDBE (MODULE(COBEX2) DROP)"

Stored sections are marked invalid when:

The UPDATE STATISTICS command is executed.
Tables accessed in the program are dropped, altered, or assigned new owners.
Indexes or DBEFileSets related to tables accessed in the program are changed.
Module owner authorization changes occur that affect the execution of embedded commands.

When an invalid section is validated at run time, the validated section is committed when the program issues a COMMIT WORK command. If a COMMIT WORK command is not executed, ALLBASE/SQL must revalidate the section again the next time the program is executed. For this reason, you should embed COMMIT WORK commands even following SELECT commands, since the COMMIT WORK command may be needed even when data is not changed by a program.