DB2 Bind Options – And Their Correct Meaning

During DB2 BIND it validates SQL statements and write equivalent CALL statements. This is also called pre-compilation. Here is my first set of options of part-1 if you miss it.

DB2 BIND Options

  • 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 you need 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. If you are a developer the above list is good to know.

PS: A remote connection that is not going to be used will keep in the “Release-Pending” status.

Related Posts

Author: Srini

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