You can specify values for plan options on the REBIND PLAN panel or in a source member with a $DBSQLOPT statement. When you do not specify a value, the value specified for the existing plan remains in effect.
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.
You can use this option to limit the computer resources that can be used for each execution of the following statements in the plan:
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 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-PREP 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. You must set the limit for the most expensive statement in the plan.
A value of zero means no limit.
Note: Beginning in r10, 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.
0—524287
(in CA Datacom Datadictionary only) 1000
Use MSG-PREP and MSG-EXEC fields on the REBIND PLAN Panel to specify the same options as the PLNMSGPE= option. MSG-PREP specifies the messages returned during preparation of the plan and MSG-EXEC specifies the messages returned during execution of the plan. Specify N for no messages, S for summary messages, or D for detail or full messages.
D, N, S
(in CA Datacom Datadictionary only) 0
Specifies when the plan and User Requirements Table are closed.
If you specify T, the plan and User Requirements Table close when the transaction ends, that is to say, an SQL COMMIT WORK or ROLLBACK WORK statement, a CA Datacom/DB LOGCP, LOGCR or LOGTB command, or a CA Datacom CICS Services DEQUE.
We recommend the T option for a CICS environment. See OPEN/CLOSE Efficiency for CICS-related information with regard to the SQL Preprocessor's PLNCLOSE= option We also recommend PLNCLOSE=T for procedures. The T option gives the most flexibility because:
The R option is the most efficient, however, because:
If you specify R, the plan and User Requirements Table close when the run unit ends or when a CA Datacom/DB CLOSE command is issued. A PLNCLOSE=R plan may be rebound or precompiled without closing it, as long as it is not currently being executed by any transactions. The R option is recommended for batch programs.
T or R
(in CA Datacom Datadictionary only) T
Specifies the format of the date when the plan was precompiled. See the following chart for the formats:
|
Entry |
Format |
Description |
|---|---|---|
|
0 |
|
retain existing format specification |
|
1 |
yyyy-mm-dd |
ISO - International Standards Organization |
|
2 |
mm/dd/yyyy |
USA - IBM USA Standard |
|
3 |
dd.mm.yyyy |
EUR - IBM European Standard |
|
4 |
yyyy-mm-dd |
JIS - Japanese Industrial Standard |
0, 1, 2, 3, 4
(in CA Datacom Datadictionary only) 0
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.
When you specify U, no locks are acquired for rows accessed for read-only purposes. Your application can access rows that have been updated by another unit of recovery, but the changes have not been committed.
When the value is C, for cursor stability, a unit of recovery holds locks only on its uncommitted changes and the current row of each of its cursors. This isolation level provides a high degree of concurrency.
Note: If you are doing updates, deletes or inserts on tables, the value must be C. Also note that if you specify ANSI or FIPS for the SQLMODE= option, ISOLEVEL= must have the value of C.
To acquire exclusive control of a table, see LOCK TABLE.
U or C
(in CA Datacom Datadictionary only) C
Specifies the join optimization sequence. Specify M 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. Specify P to change to normal join optimization.
M or P
(in CA Datacom Datadictionary only) P
Specifies the messages returned during preparation and execution of the plan. Use the MSG-PREP and MSG-EXEC fields on the REBIND PLAN Panel to assign this value.
Specify N for no messages, S for summary messages, or D for detail or full messages. Enter a combination of letters, that is to say, SD or NS. If an S or a D is specified in the PLNMSGPE= option, the message is automatically displayed in the output member by the Interactive SQL Service Facility.
DD, DN, DS, ND, NN, NS, SD, SN, SS
(in CA Datacom Datadictionary only) NN
Specifies the format of the time when the plan was precompiled. See the following chart for the formats.
|
Entry |
Format |
Description |
|---|---|---|
|
0 |
|
retain existing format specification |
|
1 |
hh.mm.ss |
ISO - International Standards organization |
|
2 |
hh:mm AM or PM |
USA - IBM USA Standard |
|
3 |
hh.mm.ss |
EUR - IBM European Standard |
|
4 |
hh:mm:ss |
JIS - Japanese Industrial Standard |
0, 1, 2, 3, 4
(in CA Datacom Datadictionary only) 0
Use PLNWKSP= only at the direction of CA Support. Specifies an increase in the amount of work space used at plan execution time.
0—128
(in CA Datacom Datadictionary only) 0
Specifies the priority of the SQL requests from the plan within the MUF. Use the PRIORITY field on the REBIND PLAN Panel to specify this option.
The lowest priority is 1, while 15 is the highest priority. If you need more information about specifying a priority, see your Database Administrator.
1—15
(in CA Datacom Datadictionary only) 7
Specifies the mode in which to process the program.
If you specify ANSI or FIPS, your program is processed in ANSI or FIPS mode, which means all your SQL statements must be coded according to ANSI or FIPS standards.
Names for tables, columns, views, synonyms and cursors must be 1 to 18 characters in length if SQLMODE=ANSI or SQLMODE=FIPS.
If ANSI or FIPS is specified for the mode, the PLNISOLA=U option is not allowed. PLNISOLA= must be C when SQLMODE=ANSI or SQLMODE=FIPS.
If you specify DATACOM, your program is processed in extended mode, which means CA 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 all modes.
Note: The SQLMODE MUF startup option must be set to DATACOM before this plan option is effective. If the SQLMODE MUF startup option is set to ANSI, this plan option is overridden and all SQL statements must comply with ANSI standards. If the SQLMODE MUF startup option is set to FIPS, this plan option is overridden and all SQL statements must comply with FIPS standards. See the Database Administrator for information on the value assigned to the SQLMODE Multi-User startup option.
ANSI, DATACOM, FIPS
(in CA Datacom Datadictionary only) DATACOM
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 CA 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.
Note: Do not specify nonzero values for both TIMEMIN= and TIMESEC=. You can specify one or the other, not both.
If you are using CA Datacom STAR for distributed processing, see CA Datacom STAR documentation before specifying this option.
0—120
(in CA Datacom Datadictionary only) 0
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 CA 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.
Note: Do not specify nonzero values for both TIMEMIN= and TIMESEC=. You can specify one or the other, not both.
If you are using CA Datacom STAR for distributed processing, see CA Datacom STAR documentation before specifying this option.
0—120
(in CA Datacom Datadictionary only) 120
|
Copyright © 2014 CA.
All rights reserved.
|
|