Top DB2 bind options you always need to give (2 of 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 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.

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.