DB2- Bind Options Part-2

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.

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.