Chapter 8: SQL Option Preprocessor

The SQL Option Preprocessor uses a type of preprocessor interface called an External Compiler Module (ECM) interface which is tightly integrated with the Compiler and debugger. The ECM only gets passed statements that begin with EXEC SQL.

Migration Considerations

If you are migrating from either the Classic Workbench product or Mainframe Express version 1.1, the syntax for invoking the SQL Option Preprocessor has changed from:

p($xdbdir\xdb) any-additional-preprocessor-directives end-p

to:

XDB(any-additional-preprocessor-options)

For example, the following command to compile program TEST1 with preprocessor options VALIDATE and FILLSYSCAT goes from:

cobol test1 p($xdbdir\xdb) validate fillsyscat endp gnt;

to:

cobol test1 xdb(validate fillsyscat) gnt;

This change will affect all users who compile programs using batch files.

If you are migrating from Mainframe Express version 1.1 and compile programs using Mainframe Express Projects under the IDE, you may only to have to make changes if you specify additional preprocessor options other than VALIDATE.

If you do not specify any additional preprocessor options, then you can re-compile the project with no changes. The new preprocessor will automatically be used.

The code generated by the new preprocessor is functionally the same as the old preprocessor so programs compiled with Mainframe Express version 1.1 do not need to be re-compiled .

Ambiguous References

If you define a COBOL variable with the same name multiple times and do not fully qualify the host variable name, the SQL preprocessor may, depending on where the definitions are, flag the host variable as an ambiguous reference with the SQL error:

COBSQ0408S <variable-name> is non-unique and should be qualified

The SQL preprocessor will also flag as an error a qualified name if it is not at the correct level of qualification. For example if host variables were defined using the following structure:

 01 EMP-TABLE. 
     03 ENO PIC S9(4) COMP.
     03 LNAME PIC X(10).
     03 FNAME PIC X(10).
     03 EMP-ADDRESS.
         05 STREET PIC X(20).
         05 CITY PIC X(15).
         05 ST PIC XX.
         05 ZIP PIC X(5).
     03 DEPT PIC X(4).
     03 PAYRATE PIC S9(13)V99 COMP-3.
     03 COM PIC S9V99 COMP-3.

The following SQL statement has valid host variable qualification:

EXEC SQL 
    SELECT STREET, CITY 
        INTO :EMP-ADDRESS.STREET, :EMP-ADDRESS.CITY
        FROM EMPLOYEE 
        WHERE ENO = :EMP-TABLE.ENO
END-EXEC

The following SQL statement has invalid host variable qualification and variables EMP-TABLE.STREET and EMP-TABLE.CITY will be flagged as errors:

EXEC SQL 
    SELECT STREET, CITY 
        INTO :EMP-TABLE.STREET, :EMP-TABLE.CITY    
        FROM EMPLOYEE 
        WHERE ENO = :EMP-TABLE.ENOEND-EXEC

Debug Files

If an error occurs when compiling a program that requires technical support, your support representative might ask you to provide additional debug files to help in determining the cause of the problem. You obtain these debug files by specifying extra DB2 Compiler directives. You might want to specify some of these directives to help in your own debugging efforts. The directives are:

In the example listed above, to compile the same program and generate two of the debug files, the command line entry would be:

Cobol test1 xdb(validate fillsyscat ctrace) chkecm(trace) gnt;

Maximum Number of SQL Statements

The preprocessor defines variables for each EXEC SQL statement in working storage. As it is a one-pass process, it has to estimate how much space it will need, and pre-allocates storage for 750 EXEC SQL statements. If your program gets the following error:

COBSQ0299s Internal EXEC SQL statement table overflow - use option MAXSQL to override default.

Use the preprocessor option MAXSQL to increase the maximum number of SQL statements the program can handle.

Setting Additional Options Under IDE

When you create a new project or add a new program to an existing project, a scan is done to determine if EXEC SQL statements are in the program. If they are, the XDB directive is automatically set for that program. You can set additional SQL Option preprocessor options by selecting Build Settings for that program and selecting the Preproc tab and clicking on the EXEC SQL More... button. Once all the preprocessor options have been set, just select "Compile" to compile a program.

SQL Option Preprocessor Options

This section describes the options to the compiler directive, XDB. Certain precompiler options, such as DB2, are a default value. Most SQL Option preprocessor options have an alternate (or negative) syntax whereby the condition established by the option can be turned off. The default syntax for each option is underlined. SQL Option options are not case sensitive. The SQL Preprocessor options are:

For a list of obsolete SQL Option preprocessor options, see the section Obsolete SQL Option Preprocessor Options. For a list of SQL Option preprocessor error messages, see the topic SQL Option Preprocessor Error Messages

Obsolete SQL Option Preprocessor Options

The following SQL Option preprocessor options are obsolete and are ignored. They are only supported to provide backward compatibility:

• ANIMCOPY
• ANS74
• ANS85
• CCSMAP
• C-FLAG
• CHECK
• CHECKERR
• CHKENGINE
• CICSIBM
• CICSSUB
• CICSXCTL
• COPYEXT
• DATADIVCOPY
• DEBUG
• DISPLAY
• EBCDIC
• ERRQ
• EXP-COPY-PROC
• FORBIT
• FORBITVC
• LIST
• LOGOFF
• LOGOFFMAIN
• LOGOFFRUN
• LOGOFFSUB
• LOGON
• NOPRECOMPILE
• PREPROCESS
• WHENEVER-PERIOD
• XDB
• XDBCPY
• XDBCURS

Translating Negative SQLCA Codes into DB2 Errors

If DB2 returns a negative SQLCA code, you have to convert this into a DB2 error code. The table below shows you how to do this:

For example, suppose DB2 returns the SQLCODE -1667. You need to:

• Convert -1667 to the absolute value 1667
• Subtract 1650:

  1667 - 1650 = 17

• Prefix the resulting value with N.

In this example, the result of the operations is the code N17, which indicates that the local session table is full.

Copyright © 2006 Micro Focus (IP) Ltd. All rights reserved.