Performing SQL Administrative Tasks › Specifying Plan Options in a Source Member

Specifying Plan Options in a Source Member

The plan is controlled by options which you can specify or allow to default. The values you assign to the options determine how the plan processes the SQL statements and controls certain aspects of the environment.

The options you specify in your source member build the CA Datacom/DB access plan containing the SQL statements you place in a source member. This access plan is temporary and is deleted after the SQL statements are executed. The plan only continues to exist if the system abends before the plan is deleted. The access plan contains:

• Information required by CA Datacom/DB about your source member.
• Each embedded SQL statement in your source member.

Note: The plan options you specify in your source member have no affect on SQL statements submitted through CA Dataquery.

The plan options you code in a source member must appear before your SQL statements. The following example shows the format:

 =>
 =>
 =>
 
 -------------------------------------------------------------------------------
 Interactive SQL Service Facility                                       SQLMAINT
 
                                 Source Panel                               S01S
            EDIT     Member: $DDSQL    Output Line Limit:  01000
                     Person: JONES
             Current Authid: JONES
                Description: CREATE TABLE DEPTTBL
 -------------------------------------------------------------------------------
 ====== ========================== T O P =======================================
 000001 *$dbsqlopt prty=9;
 ...... create table depttbl
 ......  (deptno char(2) not null,
 ......   deptname char(24) not null,
 ......   mgrnbr char(6) not null,
 ......   admdept char(2),
 ......   unique (deptno, mgrnbr));
 PF1=HELP         PF2=END         PF3=SPLIT         PF4=PROCESS
 PF5=TOP          PF6=BOTTOM      PF7=BACKWARD      PF8=FORWARD
 PF9=EXECUTE      PF10=LEFT       PF11=RIGHT        PF12=ALTERNATE

When you code the options in your source member, you can enter the options in any order, but the following rules apply:

• The options must be the first statement in your source member and must begin with *$DBSQLOPT.
• No option can be continued from one line to the next, that is to say, the option keyword and assigned value must be coded on the same line.
• Each option must be separated from another by one space.
• Each option you code must be in the option-name=value format.
• The last option keyed must be followed by a semicolon (;).

All options which you enter in the source member are edited. The default value for an option is used if you do not specify the option in your member.

If you enter the option keyword, but do not specify a value after the equal sign or if an invalid value is specified for an option or the option itself is misspelled, you receive error code DDOL000008 -- PLAN OPTION(S) IN ERROR.