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


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


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.


Author: Srini

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