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
- 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 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
- 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. 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
