Refer my part-1 if you miss it.
VALIDATION TIME: This is either RUN/BIND. It is to check authorization at BIND time or RUN time.
RESOURCE RELEASE TIME: COMMIT >>all the resources will be realease after commit, except Cursor Withhold. We can also use DEALLOCATE, but, meaning is the same.
EXPLAIN: Yes/NO ….> to get Explain info for an SQL or not
REOPTIMIZE FOR INPUT VARS: ALWAYS/YES/NONE
Always means it determines access path both at Bind time and execution time. “Yes” is valid only, when we put ‘Yes’ to DEFER PREPARE.
DEFER PREPARE: YES ..>it is for dynamic SQL statements. It defers preparing dynamic SQL statements till it encounters OPEN,DESCRIBE,EXECUTE statements.
KEEP DYN SQL PAST COMMIT: Yes…> it keeps dynamic sql statements even after commit. So no need to prepare SQL statements.
OPTIMIZATION HINT: user-id …> if you mention it DB2 will use access paths as given in the table owner.PLAN_NAME. DB2 searches for HINT-ID in the PLAN_NAME table. If you keep it blank then DB2 will take care of it.
DYNAMIC RULES: RUN/BIND…> to apply Bind rules for Dynamic SQL statements.
SQL RULES: DB2 or STD..> either to apply DB2 rules/SQL rules
DISCONNECT: EXPLICIT/AUTOMATIC/CONDITIONAL
This is to end all remote connections. Explicit-means to end all external connection of release-pend state, when in COMMIT/ROLLBACK
AUTOMATIC: To end all remote connections
CONDITIONAL: To end al remote connections when there are
no cursors with WITH HOLD.
I have given very popular BIND options. Still there are many. These are fine if you are a developer.
PS: A remote connection that is not going to be used will keep in the “Release-Pending” status.
