CA Datacom/DB SQL Preprocessor Options

The options which you can specify before submitting a program with embedded SQL  control the Preprocessor.
datacom151
The options which you can specify before submitting a program with embedded SQL  control the Preprocessor.
The option values that you assign determine how the Preprocessor processes the SQL statements and control certain aspects of the application's environment.
The options that you specify build the
Datacom/DB
access plan for your program containing embedded SQL.
The Preprocessor options that you specify in your host program have no effect on SQL statements submitted through
Dataquery
or the
Datacom Datadictionary
Interactive SQL Service Facility.
The following topics are discussed on this page:
Datacom/DB
SQL Preprocessor Options Reference
You can specify the following options in each language so that the Preprocessor builds a plan for your program.
COBOL
PL/I
C
Assembler
APOST=
AUTHID=
AUTHID=
AUTHID=
AUTHID=
CBSIO=
CBSIO=
CBSIO=
CBSIO=
CHECKPLAN=
CHECKPLAN=
CHECKPLAN=
CHECKPLAN=
CHECKWHEN=
CHECKWHEN=
CHECKWHEN=
CHECKWHEN=
CHECKWHO=
CHECKWHO=
CHECKWHO=
CHECKWHO=
COBMODE=
DATE=
DATE=
DATE=
DATE=
DECPOINT=
DECPOINT=
DECPT=
DECPOINT=
DECPT=
GENSECTN=
GENSTOR=
GENSTOR=
GENINIT=
GENINIT=
INLINE=
ISOLEVEL=
ISOLEVEL=
ISOLEVEL=
ISOLEVEL=
ITYP=
ITYP=
ITYP=
ITYP=
LANGUAGE=
LANG=
LANGUAGE=
LANG=
LANGUAGE=
LANG=
MARGINS=
MARGINS=
MSG=
MSGEXEC=
MSGEXEC=
MSGEXEC=
MSGPREC=
MSGPREC=
MSGPREC=
OPT=
OPT=
OPT=
OPT=
PAGESZE=
PAGESZE=
PAGESZE=
PAGESZE=
PGMNAME=
PLANAME=
PLANAME=
PLANNAME=
PLANAME=
PLANNAME=
PLANAME=
PLANNAME=
PLNCLOSE=
PLNCLOSE=
PLNCLOSE=
PLNCLOSE=
PRTREXIT=
PRTREXIT=
PRTREXIT=
PRTREXIT=
PRTY=
PRTY=
PRTY=
PRTY=
QUOTE=
REFNTRY=
SAVEPLANSEC=
SAVEPLANSEC=
SAVEPLANSEC=
SAVEPLANSEC=
SMBR=
SMBR=
SMBR=
SQLMODE=
SQLMODE=
SQLMODE=
SQLMODE=
STRDELIM=
STRDELIM=
STRDLM=
STRINGDELIM=
STRDELIM=
STRDLM=
STRINGDELIM=
TIME=
TIME=
TIME=
TIME=
TIMEMIN=
TIMEMIN=
TIMEMIN=
TIMEMIN=
TIMESEC=
TIMESEC=
TIMESEC=
TIMESEC=
UCRPT=
UCRPT=
USRNTRY=
USRNTRY=
VIEWSEC=
VIEWSEC=
VIEWSEC=
VIEWSEC=
The following chart shows which combinations of CHECKPLAN=, CHECKWHEN=, and CHECKWHO options are valid in plan security. Reference this chart when studying their descriptions in Description of Options.
Plan Options
Values
CHECKWHO (B=BINDER, A=ACCESSOR)
B
B
B
B
A
A
A
A
CHECKWHEN (B=BIND, E=EXECUTE)
B
B
E
E
B
B
E
E
CHECKPLAN (N=NO, Y=YES)
N
Y
N
Y
N
Y
N
Y
ALLOWABLE COMBINATION?
(Y=YES, 1/2/3)
1
Y
1
2
3
3
Y
Y
Reason Codes
  1. Not allowed because with plan-level security off, anyone could run this plan, and the executor's table-level privileges would not be checked.
  2. Not currently supported.
  3. Not allowed because SQL does not know at bind-time whom the executors are.
Description of Options
  • APOST=
    (COBOL only.)
    Specifies if an apostrophe (') is the delimiting character for character string literals generated in the SQL Communication Area (SQLCA) and the SQL Work Area (SQLWA). This option is provided for compatibility with COBOL compilers which have a similar option.
    This option is mutually exclusive with the QUOTE= option, that is to say, if you specify APOST=, do not specify QUOTE= in the Preprocessor options. If neither APOST= or QUOTE= is specified, the Preprocessor uses the default of APOST=Y.
    • Valid Entries:
      Y (for yes)
    • Default Value:
      Y for z/OS environment
  • AUTHID=
    Specifies the program plan associated authorization ID.
    Any SQL objects (tables, views, synonyms) you create in your program are owned by this authorization ID unless you specifically qualify those objects with a different authorization ID within the program.
    The authorization ID name must be 1 to 18 characters.
    • Valid Entries:
      An authorization ID name of from 1 to 18 characters
    • Default Value:
      (No default)
  • CBSIO=
    Specifies an I/O limit interrupt value for all SQL commands that create a set. This option allows application environments to establish their own maximums in I/O and set processing relative to their own requirements.
    Use this option to limit the computer resources that can be used for each execution of the following statements in the plan:
    • OPEN CURSOR, FETCH CURSOR
    • SELECT INTO
    • INSERT, UPDATE, DELETE
    • CREATE INDEX, DROP INDEX, ALTER TABLE
    For cursors, the limit applies to the total resources used to OPEN and FETCH all rows of the cursor.
    A counter is incremented each time a different index or data block is accessed, and each time 100 rows are read. Execution is terminated, and SQL return code -137 is returned when this counter exceeds the limit.
    The value of the counter is reported in the Statistics and Diagnostics Area (PXX) at the end of each request to the Multi-User Facility (MUF) when any SQL traces are in effect.
    For cursor, SELECT INTO, INSERT, UPDATE, and DELETE, you can use the total estimated cost reported in the SYSADM.SYSMSG table when bind time optimization messages are requested with the MSG= plan option as a guide for setting the limit. For CREATE INDEX, DROP INDEX, and ALTER TABLE, estimate the limit as the number of bytes in the table divided by 2000. Set the limit for the most expensive statement in the plan.
    A value of 0 (zero) means no limit.
    Here is how the CBSIO plan option is calculated: to 500,000 is added the amount over 500,000 multiplied by 10,000. For example, given a value of 500,100, the calculation would be 500,000 + (100 * 10,000) = 500,000 + 1,000,000 = 1,500,000.
  • CHECKPLAN=
    This plan option allows the creator of a plan to specify whether that plan is secured.
    If CHECKPLAN=Y, any accessor ID which attempts to execute the plan must have the PLAN EXECUTE privilege for that plan.
    If CHECKPLAN=N, any accessor ID can execute the plan (table-level privileges, however, are still checked).
    For additional information about plan security, see GRANT and REVOKE. Also see the informaton about Plan Options in Plan Security.
    See the table of valid combinations of CHECKPLAN=, CHECKWHEN=, and CHECKWHO= presented previously.
    • Valid Entries:
      Y or N
    • Default Value:
      CHECKPLAN=N is the default only if the CHECKPLAN= parameter in the PLANSEC Multi-User startup option was
      not
      specified. If the CHECKPLAN= parameter in PLANSEC
      was
      specified, its value is the default here. For more information about Multi-User startup options, see Modifying MUF Startup Options.
  • CHECKWHEN=
    Specifies whether table-level privileges are checked at bind or runtime.
    If CHECKWHEN=BIND, then CHECKWHO=BINDER must be specified (it is impossible for SQL to know all potential executors). Similarly, if CHECKWHO=ACCESSOR, then CHECKWHEN=EXECUTE must be specified.
    For additional information about plan security, see GRANT and REVOKE. Also see the informaton about Plan Options in Plan Security.
    See the table of valid combinations of CHECKPLAN=, CHECKWHEN=, and CHECKWHO= presented previously.
    • Valid Entries:
      BIND or EXECUTE
    • Default Value:
      EXECUTE is the default only if the CHECKWHEN= parameter in the PLANSEC Multi-User startup option was
      not
      specified. If CHECKWHEN= in PLANSEC
      was
      specified, its value is the default here. For more information about Multi-User startup options, see Modifying MUF Startup Options.
  • CHECKWHO=
    Used to specify whether table-level privileges are checked at bind or execute time, and whether the access rights of the binder or the executor are checked. If CHECKWHO=BINDER, the only privilege an accessor ID needs to run that plan is the PLAN EXECUTE privilege (all required table-level privileges to execute the plan are checked using the binder's accessor-ID). Because the CHECKWHO=BINDER type of plan allows the binder to effectively grant temporary privileges to accessors who use the plan, the ability to create CHECKWHO=BINDER plans must be strictly controlled. To create a CHECKWHO=BINDER plan, you must possess the CHECKBINDER System Privilege.
    Because it is impossible for SQL to know all potential executors, specify CHECKWHO=BINDER if CHECKWHEN=BIND and CHECKWHEN=EXECUTE if CHECKWHO=ACCESSOR.
    For additional information about plan security, see GRANT and REVOKE. Also see the information about Plan Options in Plan Security.
    See the table of valid combinations of CHECKPLAN=, CHECKWHEN=, and CHECKWHO= on presented previously.
    • Valid Entries:
      ACCESSOR or BINDER
    • Default Value:
      ACCESSOR is the default only if the CHECKWHO= parameter in the PLANSEC Multi-User startup option was
      not
      specified. If the CHECKWHO= parameter in PLANSEC
      was
      specified, its value is the default here. For more information about Multi-User startup options, see Modifying MUF Startup Options.
  • COBMODE=
    (COBOL only.)
    Specifies the host language, either OS/VS COBOL or VS/COBOL II.
    COBOL II VS/COBOL II Release 3 or later is supported. The CMP2 option is not supported. When using nested programs all SQL statements and any host variables they reference must be within the first program, and all programs must have a DATA DIVISION, a PROCEDURE DIVISION, and a WORKING STORAGE section.
    • Valid Entries:
      OSVS, VSCOB2
    • Default Value:
      OSVS
  • DATE=
    Specifies the DATE output format as follows:
    Entry
    Format
    Description
    ISO
    yyyy-mm-dd
    International Standards Organization
    USA
    mm/dd/yyyy
    IBM USA standard
    EUR
    dd.mm.yyyy
    IBM European standard
    JIS
    yyyy-mm-dd
    Japanese Industrial Standard
    • Valid Entries:
      ISO, USA, EUR, JIS
    • Default Value:
      ISO is the default value specified in the DATE startup option of the MUF.
    DECPOINT=
    (COBOL, PL/I, and C only.)
    Specify C if you want a comma (,) to be the decimal point indicator in decimal, numeric, and floating-point literals.
    Specify P if you want a period (.) to be the decimal point indicator.
    If the comma is specified as the decimal point indicator, commas which are used as separators must be followed by a space, as in COBOL. Also, any comma followed by a space is interpreted as a separator, even if the comma is preceded by a numeric digit.
    • Valid Entries:
      C (for comma) or P (for period)
    • Default Value:
      P
  • DECPT=
    (PL/I only.)
    Same as DECPOINT=
  • GENSECTN=
    (COBOL only.)
    Specify if you want the Preprocessor to generate COBOL items in the WORKING-STORAGE SECTION or LOCAL-STORAGE SECTION of the program.
    If you specify W, the Preprocessor generates the items in the WORKING-STORAGE SECTION of the program.
    If you specify O (a letter O, not a zero) for GENSECTN=, data structures (SQLCAs, SQLWAs, SQLDAs), used when SQL statements are executed, are generated in a LOCAL-STORAGE section. Addresses of host variables are stored into these data structures, and VALUE clauses can be used.
    Memory for a LOCAL-STORAGE section is allocated and value clauses are executed each time a program is called, as opposed to once per run-unit for WORKING-STORAGE sections. LOCAL-STORAGE therefore provides a way to make programs re-entrant.
    When you use a LOCAL-STORAGE section:
    • The entire LOCAL-STORAGE SECTION syntax must appear on a single line.
    • The same rules apply to LOCAL-STORAGE that apply to WORKING-STORAGE, regarding required locations relative to other program sections.
      Datacom/DB
      does not edit the order of the LOCAL-STORAGE and WORKING-STORAGE sections relative to each other, nor does
      Datacom/DB
      edit for the number of WORKING-STORAGE or LOCAL-STORAGE sections. The compiler enforces any rules.
    • If GENSECTN=O, then a LOCAL-STORAGE section must exist, otherwise an error is generated.
    • Valid Entries:
      • W (Preprocessor generates COBOL items in the WORKING-STORAGE SECTION)
      • O (Preprocessor generates COBOL items in the LOCAL-STORAGE SECTION)
    • Default Value:
      W
  • GENSTOR=
    (PL/I and Assembler only.)
    Causes the necessary storage for SQL to be generated. GENSTOR= is valid only when specified in the source (the $DBSQLOPT statement).
    • Valid Entries:
      • TOP specifies the top of the source.
      • BOT specifies the end of the source.
      • HERE specifies after this point in the source.
    • Default Value:
      If not specified, generated storage is placed before the last END statement.
  • GENINIT=
    (PL/I and Assembler only.)
    Causes the initialization code to be generated after a specified point in the source. GENINIT= is valid only when specified in the source (the $DBSQLOPT statement).
    • Valid Entries:
      • TOP specifies the top of the source.
      • BOT specifies the end of the source.
      • HERE specifies after this point in the source.
    • Default Value:
      If not specified, generated is placed before the last END statement.
  • INLINE=
    (
    For Assembler
    ) Specifies the generation of control structures. That is, SQL Work Areas (SQLWAs) that are used with SQL statements.
    With INLINE=N, one set of SQLWAs is generated for each SQL statement. But with INLINE=Y, the Preprocessor generates one set of SQLWAs to use for all SQL statements. INLINE=Y generates more executed code but much less overall code, avoiding the addressability problems (that is, not enough base registers) that can occur when INLINE=N.
    Because the C Preprocessor uses the inline method exclusively, the C Preprocessor does not look at the INLINE= option. For that reason, an error can occur if you code the INLINE= option when using the C language. Therefore,
    when using C, the INLINE= option should not be coded.
  • ISOLEVEL
    Specifies the isolation level, or the degree to which a unit of recovery in your application is isolated from the updating operations of other units of recovery.
    If you specify U (for uncommitted data), no locks are acquired for any rows accessed. Your application can access rows that another unit of recovery updated, even though those changes may not have been committed. Because no locks are acquired, no updates, deletes, or inserts can be done by a unit of recovery operating in the U isolation level.
    If you specify C (for cursor stability), locks are acquired for all rows accessed. Your application therefore only accesses rows that contain committed data. For updateable cursors (those that have associated UPDATE WHERE CURRENT OF or DELETE WHERE CURRENT OF statements), exclusive locks are acquired. For read-only cursors, share locks are acquired. When a row is fetched, the lock on the previous row of the cursor is released unless the row was modified. All locks are released when the unit of recovery ends, at which time changes are either committed (COMMIT WORK) or rolled back (ROLLBACK WORK).
    In C isolation level, the current row of a cursor may or may not be locked while your application is accessing it, depending on whether the cursor is updateable. For updateable cursors, the current row of the cursor is always locked with an exclusive lock when your application fetches it. For read-only cursors, the SQL Facility may read ahead and transfer multiple rows from the MUF to your application's region. In this case, the current row of the cursor could possibly no longer be locked when your application fetches it. If your application requires the current row of a cursor to be locked, it must use an updateable cursor to fetch the row.
    If you specify R for repeatable read, data once seen by a transaction cannot be changed by another task while the first is still active. Also, other records cannot be added or updated if it would cause them to participate in the set of records seen by the first transaction.
    If SQL receives a blank or zero in the ISOLEVEL= specification from any of the SQL access methods, the default for the appropriate SQLMODE is automatically set. If an invalid ISOLEVEL= relative to the effective (that is, specified or defaulted) SQLMODE is explicitly set (nonblank and nonzero), an appropriate SQLCODE and error message is produced.
    If you specify ANSI or FIPS for the SQLMODE= option, you must also specify ISOLEVEL=C.
    To acquire exclusive control of a table, see LOCK TABLE Statement. Also see SQL Plan Options Special Topics.
    • Valid Entries:
      • U (No locks are acquired, no changes are allowed)
      • C (Locks are acquired, changes are allowed)
      • R (Locks are acquired, restricted changes are allowed)
    • Default Value:
      • C (For ANSI and FIPS SQLMODEs)
      • U (For all other SQLMODEs)
    The
    both
    parameter of the SQLOPTION Multi-User startup option controls whether you are allowed to mix the use of isolation level U and C plans under a single logical unit of work (LUW) for those LUWs not running any SQLMODE ANSI or FIPS plans. YES specified for the
    both
    option of SQLOPTION indicates that mixing is allowed.
  • ITYP=
    (z/VSE only)
    Specifies an optional file type for SQL INCLUDE files in z/VSE. The type is one letter.
    In COBOL, the ITYP= specification can be overridden by explicitly coding a file type on the INCLUDE statement.
  • LANGUAGE= or LANG=
    (PL/I, C, and Assembler only.)
    Specifies the source language to be processed. This option overrides the initial values for MARGINS= established at initialization.
    Specifying MARGINS= is not allowed if you are using the C language.
  • MARGINS=
    (PL/I and Assembler only.)
    Specifies the valid columns of the source record inclusively.
    Do not specify MARGINS= in the $DBSQLOPT statement (source code). Specify MARGINS= in the OPTIONS file or in the PARM= text on the EXEC statement of the JCL.
    In PL/I you can specify (start, end) or (left, right). For example: (s) or (s,e) or (,e). In Assembler, you can specify (start, end, continue) or (left, right, continue). For example: (s) or (s,e) or (,e) or (s,e,c).
    Values are merged with defaults, that is to say, if only one value is specified, the default is assigned to the nonspecified value.
    An example in PL/I: if (,71) is specified after LANGUAGE=, the result is (2,71) because 2 is the default for the start value. Or, if (5) is specified, the result is (5,72) because 72 is the default for the end value.
    An example in Assembler: if (,71) is specified, the result is (1,71) because 1 is the default for the start value. Or, if (5) is specified, the result is (5,71) because 71 is the default for the end value. The continuation field is the next column after the end (right) margin.
    • Valid Entries:
      1 -- 30, s < e, and width >= 70
    • Default Value:
      • PL/I defaults are:
        • 1 (start) 72 (end) if specified
          before
          LANGUAGE=
        • 2 (start) 72 (end) if specified
          after
          LANGUAGE=
      • Assembler defaults are:
        • 1 (start)
        • 71 (end)
        • 16 (continue)
  • MSG=
    (COBOL only.)
    Specifies the level of messages you want the SQL Optimizer to generate. Specify the optimization message in groups of two letters, for example MSG=xy, where S and D and N replace the x and y, and where:
    • x refers to precompile-time messages (these are included at the end of the Preprocessor Source Listing), and
    • y refers to messages generated by the Optimizer when the statement is executed (these messages can be retrieved from the SYSMSG table after the statement has been executed).
    For a description of the SYSMSG table, see SQL Query Optimization Messages.
    Messages for a plan are deleted when the plan is deleted. When you re-preprocess a program, the Preprocessor deletes the previous plan and therefore also its diagnostic messages.
    Valid combinations of S and D and N are given in Valid Entries below, where:
    • S specifies summary
    • D specifies detail
    • N specifies none
    • Valid Entries:
      SS, DD, SD, DS, NS, ND, DN, SN, NN
    • Default Value:
      NN
  • MSGEXEC=
    (PL/I, C, and Assembler only.)
    Refers to messages generated by the Optimizer when the statement is executed. These messages can be retrieved from the SYSMSG table after the statement has been executed. For a description of the SYSMSG table, see SQL Query Optimization Messages.
    • S specifies summary
    • D specifies detail
    • N specifies none
    Messages for a plan are deleted when the plan is deleted. When you re-preprocess a program, the Preprocessor deletes the previous plan and therefore also its diagnostic messages.
  • MSGPREC=
    (PL/I, C, and Assembler only.)
    Refers to precompile-time messages. These messages are included at the end of the Preprocessor Source Listing.
    • S specifies summary
    • D specifies detail
    • N specifies none
    • Valid Entries:
      S, D, N
    • Default Value:
      N
  • OPT=
    Specifies the join optimization mode. P specifies normal join optimization. Specify M (manual join order) if the normal join optimization is unacceptable and you want tables joined as they are listed in the FROM clause. This results in a nested loop join.
    Do not specify E; it is reserved for future use.
    • Valid Entries:
      P or M
    • Default Value:
      P
  • PAGESZE=
    Specifies the number of output lines per page on SYSPRINT.
    • Valid Entries:
      • For COBOL: 0 -- 120
      • For PL/I, C, and Assembler: 10 -- 255
    • Default Value:
      55
  • PGMNAME=
    This option enables the
    Datacom Datadictionary
    name of a program to be changed without requiring the program source itself to be altered. The program name of a procedure must match its external (or load module) name.
    The syntax is as follows: PGMNAME=name
    The name specified must be a
    Datacom Datadictionary
    entity-occurrence name. It overrides any PROGRAM-ID specified in COBOL. If the program is a procedure, this name must match both the generated load module name and the EXTERNAL NAME specified in the CREATE PROCEDURE statement.
    • Valid Entries:
      A valid name as described above
    • Default Value:
      (No default)
  • PLANAME=
    Specifies the name for your plan, a name that should be unique within your authorization ID. You can use MIXED strings in a plan name, that is, strings in which both Double-Byte Character Set (DBCS) and Single-Byte Character Set (SBCS) characters are used.
    The plan name must be 1 to 18 bytes in length. The first character of the plan name must be alphabetic (including Katakana symbols) or a Shift-Out character (when you are using MIXED strings). Shift-Out and Shift-In characters (used to delimit DBCS substrings) count toward the 18-byte length limit. If you specify more than 18 bytes for the name, the Preprocessor truncates your entry to the first 18 bytes.
    For more information about MIXED data and MIXED strings, see SQL Language Data Types .
    If you specify a name you used previously for a plan, you are, in essence, replacing that existing plan with a new plan. For example, you have an existing plan named PAYROLL. If you specify PAYROLL as the name of a new plan, this new plan replaces the previous plan named PAYROLL.
    If you do not specify a plan name, the Preprocessor uses the PROGRAM-ID specified in your program.
    • Valid Entries:
      A name 1 to 18 bytes long, first character alphabetic (including Katakana symbols) or a Shift-Out character (when you are using MIXED strings)
    • Default Value:
      In COBOL and PL/I, there is no default.
    • Plan Versioning - COBOL Only:
      You can generate plans that include a date/timestamp, YYMMDDHHMM
      [email protected]
      Plan name =
      program idYYMMDDHHMM
      • If the
        program id
        is less than 8 bytes, fill up to 8 bytes with '_'s
      • If the
        program id
        is greater than 8 bytes, truncate it to 8 and catenate the timestamp. You receive warning message DB21013W.
      Example for PROGRAM-ID.CDC100 compiled on 2015/04/15 at 11:45 am:
      CDC100__1504171145
      PLANAME=
      value
      @TIMESTAMP
      Plan name =
      value
      YYMMDDHHMM
      • If
        value
        is less than 8 bytes, fill up to 8 bytes with '_'s
      Example for PROGRAM-ID.CDC100 compiled on 2015/04/17 at 2:07 pm, but with [email protected]
      CDC100A_1504171407
      [email protected] is 10 bytes, the same as the generated timestamp, the value in PLANAME cannot be greater than 8 characters.
      Plans are not overwritten unless they are generated within the same minute. If so, the last one remains.
    In the listing for the COBOL program, the plan name is displayed as SQLCA-PLAN-NAME within the SQLCA block of the WORKING-STORAGE SECTION.
    As part of this process, a flag byte is set to x'02' in the SQL options block. This is also stored in the DDD. It can be accessed to find only those plans that have been added to the system using the @TIMESTAMP option.
  • PLANNAME=
    (PL/I and Assembler only.)
    Same as PLANAME= (see previous description).
  • PLNCLOSE=
    Specifies when the plan, and any User Requirements Tables (URTs) automatically opened by the SQL Manager, are closed.
    If you specify T, the plan, and any URTs automatically opened by the SQL Manager, close when the transaction ends (an SQL COMMIT WORK or ROLLBACK WORK statement, a
    Datacom/DB
    LOGCP, LOGCR or LOGTB command, or a
    Datacom
    CICS Services DEQUE).
    We recommend the T option for a CICS environment. We also recommend PLNCLOSE=T for procedures. We recommend the R option for batch programs.
    If you specify R, the plan, and any URTs automatically opened by the SQL Manager, close when the run unit ends, or when a
    Datacom/DB
    CLOSE command is issued. In a
    Datacom
    CICS Services environment, the run unit ends only when CICS is terminated or when the SQL URT (usually URT 20) is closed using the DBOC command. When ISOLEVEL=U is used, because no locks are acquired by the MUF (and therefore no DEQUE commands are issued by
    Datacom
    CICS Services), the plan and URTs are not closed at the end of each CICS transaction, even if PLNCLOSE=T. A PLNCLOSE=R plan can be preprocessed or rebound with DDOL or DBSRFPR before the run unit ends if no current unit of recovery has executed the plan.
    URTs opened on behalf of a plan with PLNCLOSE=R are not closed until the plan closes when the SQL=YES URT (default URT 020) is closed. If URTs accessing a database need to be closed to perform utility functions, you can close those SQL-generated URTs accessing a database by deleting (if you have the
    delete
    privilege) those rows in the SQL_STATUS_URT_INACTIVE
    virtual table
    (this
    virtual table
    is a view on the SQL_STATUS_URT table with the restriction that only URTs for the current run unit with zero users are selected). The following example query can be executed from any tool that uses SQL, where nnn is the database-ID to be closed. Any valid WHERE clause can be used, including no WHERE clause to close all URTs.
    DELETE FROM SYSADM.SQL_STATUS_URT_INACTIVE WHERE DBID = 'nnn';
    Closing these URTs does not keep them from being reopened. There can also be other URTs that are active, meaning they have one or more plans that have accessed the table in their current transaction. Additionally, other run units can also have URTs open for the database. Use the following query to see which URTs are open for a database:
    SELECT * FROM SYSADM.SQL_STATUS_URT WHERE DBID = 'nnn';
    For information about possible performance enhancement using the Least Recently Used (LRU) statement cache to disconnect the caching of plan statements from the control of the PLNCLOSE= option, see LRU Statement Cache in the.
    • Valid Entries:
      • T (close when transaction ends)
      • R (close when run unit ends)
    • Default Value:
      R
  • PROCSQLUSAGE=
    Is valid for COBOL, PL/I, Assembler, and C. If this option is specified, SQL prepares the program for execution as a procedure. This option is required for programs intended to run as procedures. It is prohibited for programs not run as procedures.
    All procedures must be preprocessed, even if there are no embedded SQL statements in a particular procedure.
    Use PROCSQLUSAGE= only as documented. If it is specified or omitted inappropriately, the program can fail in an unpredictable way before SQL has a chance to detect the error.
    To support procedure execution, the preprocessors add code to programs that specify PROCSQLUSAGE=, thus making proper use of the option critical. Therefore, make certain you have coded this option accurately.
    Datacom
    reserves the right to add edits at any time.
    Specifying NO means the procedure does not call
    Datacom
    SQL.
    Specifying CONTAINS means that the procedure calls
    Datacom
    SQL but contains no SELECT, SELECT INTO, preparations of dynamic-select, INSERT, UPDATE, or DELETE statements.
    Specifying READS means that the procedure contains a SELECT, SELECT INTO, or preparation of a dynamic-select statement, but does not contain INSERT, UPDATE, or DELETE statements.
    Specifying MODIFIES means that the procedure contains at least one INSERT, UPDATE, or DELETE statement.
    Datacom
    only checks for the existence of calls to
    Datacom
    SQL in the procedure at the time of the CREATE PROCEDURE statement. (An error is produced, however, if NO is specified in a program containing SQL, or vice versa.)
    Datacom
    requires the value specified to match the corresponding specification in the CREATE PROCEDURE statement.
    • Valid Entries:
      NO, CONTAINS, READS, or MODIFIES
    • Default Value:
      (No default)
  • PRTREXIT=load-module-name
    Specifying PRTREXIT= allows you to write a printer exit routine to print the output instead of allowing the Preprocessor to write to SYSPRINT. The load-module-name is the name of your printer exit routine. When your printer exit routine is called, the registers are as follows:
    • Register 1
      Address of parameter list as follows:
      Word 1 = x'00000014' Word 2 = AL1(length of print line) AL3(address of print line) Word 3 = Address of a 1-byte top-of-page indicator: if bit x'20' is on, top-of-page is requested
    • Register 13
      Address of a register save area which you must use to save and restore the
      Datacom/DB
      registers according to standard linkage conventions.
    • Register 14
      Address to return to inside
      Datacom/DB
      .
    • Register 15
      Address of the entry point of your printer exit.
    On return from your routine, the contents of all registers (except 15) should contain what they contained before the exit was called. Register 15 should contain 0 unless a failure occurred. The Preprocessor aborts processing if a nonzero register 15 is returned.
    Remember to concatenate the library containing your printer exit to the end of the Preprocessor load library concatenation in your JCL.
    If you do not specify a load-module-name (with PRTREXIT=), processing continues to write to SYSPRINT instead of calling the printer exit.
    • Valid Entries:
      A load-module-name of up to eight characters
    • Default Value:
      (No default)
  • PRTY=
    Specifies the priority of the SQL requests from the plan within the MUF. The lowest priority is 1, while 15 is the highest priority.
    If you need more information about specifying a priority, see your Database Administrator.
    • Valid Entries:
      1 -- 15
    • Default Value:
      7
  • QUOTE=
    (COBOL only.)
    Specifies if a quotation mark (") is the delimiting character for character literals generated in the SQL Communication Area (SQLCA) and the SQL Work Area (SQLWA). This option is provided for compatibility with COBOL compilers which have a similar option.
    This option is mutually exclusive with the APOST= option, that is to say, if you specify QUOTE=, do not specify APOST= in the Preprocessor options.
    If neither QUOTE= or APOST= is specified, the Preprocessor uses the default of QUOTE=Y for z/VSE environments.
    • Valid Entries:
      Y (for yes)
    • Default Value:
      Y for z/VSE environments
  • REFNTRY=
    (Assembler only.)
    See USRNTRY= later in this chapter.
  • SAVEPLANSEC=
    Use this option to specify whether to drop or not to drop security privileges granted on a PLAN when a program is re-preprocessed.
    SAVEPLANSEC=Y means PLAN privileges are not dropped and therefore do not have to be regranted after re-preprocessing a program.
    SAVEPLANSEC=N means PLAN privileges are dropped (revoked).
    • Valid Entries:
      Y or N
    • Default Value:
      N
  • SMBR=
    (PL/I, C, and Assembler only.)
    Specifies the member name and type for the source residing in a z/VSE library. The value specified must be of the form: name.type (for example, SMBR=ABC.P where ABC is the name and P the type).
    If SMBR= is not specified, the source is assumed to be on sequential disk. If you specify SMBR=, you must do so either in the execution parameters or the options file. If you specify SMBR= when the source is on a sequential disk file, an open error results.
    • Valid Entries:
      A valid member name and a type of length 1
    • Default Value:
      (No default)
  • SQLMODE=
    Specifies the mode in which to process the program. If you specify SQLMODE=DATACOM, your program is processed in extended mode, which means
    Datacom/DB
    extensions to the standards are allowed in your SQL statements. Names for tables, columns, views, synonyms and cursors can be 1 to 32 characters in length if SQLMODE=DATACOM. Authorization IDs and plan names must be 1 to 18 characters in extended mode.
    Specifying SQLMODE=DB2 allows you to use the
    Datacom/DB
    DB2 compatibility mode.
    Datacom
    DB2 Transparency is required to use the
    Datacom/DB
    DB2 mode. The DB2 compatibility mode allows you to use application programs written for IBM Db2.
    Datacom/DB
    recompiles and executes Db2 application programs against
    Datacom/DB
    tables without your having to change the source code of those programs. Plans created by the
    Datacom
    DB2 Transparency Bind program also execute in Db2 mode.
    In COBOL and PL/I you can specify SQLMODE=DB2A86 to use the
    Datacom/DB
    DB2 compatibility mode while conforming to ANSI 86 standards. In COBOL and PL/I, specify ANSI or FIPS for your program to be processed in ANSI or FIPS mode, which means all your SQL statements must be coded according to ANSI or FIPS standards. When ANSI or FIPS mode is specified, the ISOLEVEL=U option is not allowed. ISOLEVEL=C must be specified when SQLMODE=ANSI or SQLMODE=FIPS. Authorization IDs and plan names must be 1 to 18 characters in ANSI mode. Names for tables, columns, views, synonyms and cursors must be 1 to 18 characters in length if SQLMODE=ANSI or SQLMODE=FIPS.
    The SQLMODE MUF startup option must be set to DATACOM before this Preprocessor option is effective. If the SQLMODE MUF startup option is set to ANSI or FIPS, this Preprocessor option is overridden and all SQL statements must comply with ANSI or FIPS standards. See your Database Administrator for informaton about the value assigned to the SQLMODE Multi-User Facility startup option.
  • STRDELIM=
    (COBOL, PL/I, and C only.)
    Specifies whether you want the string delimiter, used to delimit character string literals in SQL statements, to be an apostrophe (') or a quotation mark (").
    The escape character, used to enclose delimited SQL identifiers, is the apostrophe if the string delimiter is the quotation mark, or the quotation mark if the string delimiter is the apostrophe. See Delimited SQL identifiers for more information about delimited SQL identifiers.
    Specify A for apostrophe or Q for quotation mark.
    • Valid Entries:
      A, Q
    • Default Value:
      A
  • STRDLM=
    (PL/I only.)
    Same as STRDELIM= (see previous description).
  • STRINGDELIM=
    (PL/I only.)
    Same as STRDELIM= (see previous description).
  • TIME=
    Specifies the TIME output format as follows:
    Entry
    Format
    Description
    ISO
    hh.mm.ss
    International Standards organization
    USA
    hh:mm AM or PM
    IBM USA standard
    EUR
    hh.mm.ss
    IBM European standard
    JIS
    hh:mm:ss
    Japanese Industrial Standard
    • Valid Entries:
      ISO, USA, EUR, JIS
    • Default Value:
      The default is the value specified in the TIME startup option of the MUF.
    ISO is the default of the Multi-User Facility's TIME startup option.
  • TIMEMIN=
    Specifies exclusive control wait time limit in minutes.
    This option allows a program to either wait or not wait for an explicit amount of time when another job is holding a requested record under exclusive control. If the specified time is exceeded, the application program receives a -117 value in the SQLCODE of the SQL Communication Area and a
    Datacom/DB
    61 return code to inform the user that the record was not available.
    Specifying a zero for both TIMEMIN= and TIMESEC= means that there is no time limit, and without a limit on the wait time, a
    wait forever
    condition is possible.
    TIMEMIN=0 and TIMESEC=1 means do not wait at all.
    Do not specify nonzero values for both TIMEMIN= and TIMESEC=.
    If you are using
    Datacom
    STAR for distributed processing, see
    Datacom
    STAR documentation before specifying this option.
    • Valid Entries:
      0 -- 120
    • Default Value:
      0
  • TIMESEC=
    Specifies exclusive control wait time limit in seconds.
    This option allows a program to either wait or not wait for an explicit amount of time when another job is holding a requested record under exclusive control. If the specified time is exceeded, the application program receives a -117 value in the SQLCODE of the SQL Communication Area and a
    Datacom/DB
    61 return code to inform the user that the record was not available.
    Specifying a zero for both TIMEMIN= and TIMESEC= means that there is no time limit, and without a limit on the wait time, a
    wait forever
    condition is possible.
    TIMESEC=1 and TIMEMIN=0 means do not wait at all.
    Do not specify nonzero values for both TIMEMIN= and TIMESEC=.
    If you are using
    Datacom
    STAR for distributed processing, see
    Datacom
    STAR documentation before specifying this option.
    • Valid Entries:
      0 -- 120
    • Default Value:
      0
  • UCRPT=
    (PL/I and Assembler only.)
    Specifies whether report should be uppercase only. The data is not affected.
    Do not specify UCRPT= in the $DBSQLOPT statement (source code). Specify UCRPT= in the OPTIONS file or in the PARM= text on the EXEC statement of the JCL. In PL/I, if this is the first option in the execution parameters, the entire report is in uppercase.
    In Assembler, if this is the first option in the option file, the report is uppercase, except for the initial title lines. To have everything in uppercase, the option should be coded in the execution parameters.
    • Valid Entries:
      Y, N
    • Default Value:
      N
  • USRNTRY=
    (COBOL and Assembler only.)
    The description of USRNTRY= differs in COBOL and Assembler.
    • COBOL Description:
      Use USRNTRY= in COBOL to specify the entry point in the COBOL program. The value you assign this option
      must match
      the value specified for the USRNTRY= parameter of the User Requirements Table.
      The default is DBMSCBL. If you have changed this in the User Requirements Table, you must enter the same entry point name as specified in the User Requirements Table. Specify NONE if no entry point is to be generated (used when an SQL program is called by another program).
      • Valid Entries:
        DBMSCBL or An entry point name consistent with COBOL naming conventions, or NONE.
      • Default Value:
        DBMSCBL
    • Assembler Description:
      USRNTRY= works with the REFNTRY= Preprocessor option to cause generation of an entry point that allows use of a single User Requirements Table with OPEN=DB for many separate programs. USRNTRY= and REFNTRY= are for Assembler batch mode only. Following is shown the generation:
      usrntry-name EQU refntry-name ENTRY usrntry-name
      Use USRNTRY= to specify the name of the generated entry point. USRNTRY= can be entered in two ways. If USRNTRY=NONE, no entry point is generated. If any other value is entered, that value is considered a valid name. This name is also the name in the URT USRNTRY= operand. If USRNTRY= is not specified, then a default name, SQLEXECE, can be used to generate the entry point.
      REFNTRY= is the name of a CSECT or ENTRY in the program being processed. This name represents the point where the program would get control when called.
      In DATACOM mode, USRNTRY= and REFNTRY= are not required. If REFNTRY= is coded, the entry point is generated. If USRNTRY= is not coded when REFNTRY= is, the default name is used. Specifying USRNTRY= without REFNTRY= causes an error.
      In DB2 mode, the entry point generation must be specified or explicitly suppressed. To specify generation, REFNTRY= must be coded, but in this case USRNTRY= is optional since the default name, SQLEXECE, is taken. To suppress generation, USRNTRY=NONE must be entered.
      • Valid Entries:
        A name of up to 8 characters or NONE for USRNTRY=, or for REFNTRY= a name of up to 8 characters
      • Default Value:
        SQLEXECE is the default for USRNTRY=, but for REFNTRY= there is no default
  • VIEWSEC=
    Whether view security is used for a particular plan is based on the value of the VIEWSEC= Preprocessor plan option. If VIEWSEC= is not specified, whether a plan uses view security is determined by the value of the view-security specification in the SQLOPTION Multi-User startup option. If neither VIEWSEC= nor the view-security specification in SQLOPTION is used, view security is not used for newly bound or rebound plans.
    Specify Y to indicate that view security is to be used during the execution of newly prepared and newly rebound plans.
    Specify N to indicate that view security is not to be used during the execution of newly prepared and newly rebound plans.
    The default for the VIEWSEC= Preprocessor option is the value of the view-security option in the SQLOPTION Multi-User startup option or N if no default was specified.
    Also note, the choice of security method is made at prepare-time rather than during execution. A choice of Y is rejected if view security has not been activated for the MUF using external security.
    • Valid Entries:
      Y or N
    • Default Value:
      Value of the view-security specification in the SQLOPTION Multi-User startup option, which itself defaults to N
    Rebound explicitly or automatically plans that do not have an explicit view security specification are caused by the value of the SQLOPTION view-security option to change security methods, if necessary, to match the specification. Be aware that the security method used by existing plans can be changed intentionally or inadvertently in this way.