Preparing DB2 Application Plans › Defining Application Plans › How to Create a New Plan Definition › Parameters Fill-in

Parameters Fill-in

Issue the PARAMETERS command or press PF4/16 to display the parameters fill-in for the current plan. This fill-in lets you set DB2 BIND command options and allows programs in this plan to execute SQL in dynamic mode. The current settings display as one-character codes that you can change by overtyping.

=>
=>
=>
----------------------------------------------------------------------------
IDEAL: PLAN PARAMETERS       PLA IQPLANA                  SYS: DOC   FILL-IN
                        PLAN  PARAMETERS                                     

Dynamic SQL?    Y (Y=Yes, N=No)                                              

Bind Options:                                                                
Name:  IQPLANA
Owner: ________        Qualifier: ________

Action:    R (R=Replace, A=Add)                                         
Retain:    Y (Y=Yes, N=No)                                              
Validate:  R (R=Run, B=Bind)                                            
Isolation: R (R=Repeatable Read, C=Cursor Stability)                    
Flag:      I (I=InformationalWarningErrorCompletion,                 
              W=WarningErrorCompletion, E=ErrorCompletion,           
              C=Completion only)                                        
Acquire    U (U=Use, A=Allocate)                                        
Release    C (C=Commit, D=Deallocate)                                   
Explain    N (Y=YES, N=NO)                                              
Prepare:   _ (NODefer or Defer)

Cachesize: ____ (0 - 4096)
Currentdata: _  (Yes or No)
Currentserver: ________ (Location name)
Enable/Disable:
_________________________________________________________________________
_________________________________________________________________________
_________________________________________________________________________

The following list provides an explanation of the fields on the preceding screen:

Dynamic SQL?

Specifies whether this plan includes any programs executing SQL in dynamic mode. (This is a CA Ideal setting, where all other settings on this fill-in are DB2 BIND command options.)

• N-Application cannot execute SQL in dynamic mode.
• Y-Application can execute SQL in dynamic mode.

Note: It is suggested that you set this option to Y, which allows dynamic SQL to change and test one program using dynamic SQL as part of an otherwise static application without having to rebind the plan.

Setting this option to Y includes the DBRM provided with CA Ideal to handle SQL in dynamic mode.

If you specify that the plan includes dynamic SQL in this fill-in, you can override this and require that all programs be static by using the SET RUN SQL STATIC command. See the Command Reference Guide for complete syntax.

Name

Specifies name of the plan in plan identification fill-in screen.

Owner

Specifies authorization ID of the owner of the plan. The owner must have the privileges required to execute the statements contained in the plan.

Qualifier

Specifies the authorization ID, which is the qualifier for all unqualified DB2 objects accessed by this plan. An authorization ID specified at runtime can override this value in an ASSIGN AUTH command.

Action

Specifies whether this application plan is new or replaces an old plan.

• R-Replace old plan
• A-Add new plan

Retain

Indicates whether those users with authority to bind or execute the existing plan keep that authority over the replaced plan.

• Y-Retain authority
• N-Do not retain authority

Validate

Indicates when full validity checking is performed.

• R-At runtime
• B-At bind time

Isolation

Specifies the isolation of this application from others.

• R-Repeatable read
• C-Cursor stability

Flag

Indicates the lowest le4vel of messages to show.

• I- Informational, warning, error, and completion
• W-Warning, error, and completion
• E-Error and completion
• C-Completion only

Acquire

Indicates when you want the system to acquire the resources that your program used.

• U-Open table spaces and acquire locks when your application first accesses them.
• A-Acquire them when the application plan is allocated.

Release

Indicates when you want the system to release the resources that your program used.

• C-Release resources at each commit point.
• D-Release them when the application terminates.

Explain

Indicates whether to include the explain option.

• Y-Include the explain option.
• N-Do not include the explain option.

Prepare

Specifies when the PREPARE for an SQL statement that refers to a remote object takes place.

• N-PREPARE takes place in real time.
• D-PREPARE occurs when the first EXECUTE, OPEN, or DESCRIBE for the statement is issued.

Cachesize

Indicates the size of the authorization cache to acquire for the plan. The authorization cache is used at runtime to store the names of users authorized to run the plan.

• 0-4096-Size in bytes for the authorization cache.

Currentdata

Specifies the data currency required for ambiguous cursors opened at remote locations. Data is considered current if the data in your host structure is identical to the data in the base table.

• Y-Data currency is required for ambiguous cursors.
• N-Data currency is not required for ambiguous cursors.

Currentserver

Specifies a connection to a location before the plan is run. The specified location receives all SQL requests until the application issues the SQL statement CONNECT TO or CONNECT RESET.

Enable/Disable

These keywords are mutually exclusive. ENABLE lists the system connection types that are enabled to use the plan. DISABLE lists the connection types that are disabled for the plan. You can use one to three lines to enter the value for this parameter.