Tags

, , , ,

ACTION

(REPLACE)

The object replaces an existing one with the same identifier, and a new entry replaces the old one in the catalog table SYSPLAN or SYSPACKAGE. If no object with the given identifier already exists, the bind process creates the new object and a new entry.

(ADD)

Adds a new object, but does not replace an existing one. If the object name already exists in the catalog, the bind fails. If the bind fails for any reason, the bind process does not produce a new package or plan and makes no entry in the catalog.

CURRENTDATA

(YES)

Specifies that currency is required for read-only and ambiguous cursors. DB2 acquires page or row locks to ensure data currency. Block fetching for distributed, ambiguous cursors is inhibited.

(NO)

Specifies that currency is not required for read-only and ambiguous cursors. Block fetching for distributed, ambiguous cursors is allowed.

EXPLAIN

(NO)

Provides no EXPLAIN information.

(YES)

Inserts information in the tables populated by EXPLAIN. If owner.PLAN_TABLE does not exist at bind time, the value of the option VALIDATE determines the success of the bind operation.

ISOLATION

(RR)

Repeatable read. Ensures that:

  • Your application does not read a row that another process has changed until that process releases that row.
  • Other processes do not change a row that your application reads until your application commits or terminates.

(RS)

Read stability. Ensures that:

  • Your application does not read a row that another process has changed until that process releases that row.
  • Other processes do not change a row that satisfies the application’s search condition until your application commits or terminates. It does allow other application processes to insert a row, or to change a row that did not originally satisfy the search condition.

If the server does not support RS, it uses RR.

(CS)

Cursor stability. Ensures, like repeatable read, that your application does not read a row that another process changes until that process releases that row. Unlike repeatable read, cursor stability does not prevent other applications from changing rows that your application reads before your program commits or terminates.

(UR)

Uncommitted read. Unlike repeatable read and cursor stability, does not ensure anything. With the exception of LOB data, uncommitted read avoids acquiring locks on data and allows:

  • Other processes change any row your application reads during the unit of work.
  • Your application read any row that another process has changed, even if the process has not committed the row.

You can use this option only with a read-only operation: SELECT, SELECT INTO, or FETCH using a read-only cursor. If you specify ISOLATION(UR) for any other operation, DB2 uses ISOLATION(CS) for that operation.

(NC)

No commit. Used on packages that are bound to certain servers other than DB2 for OS/390 and z/OS. DB2 for OS/390 and z/OS does not support NC. If the server does not support this isolation level, it uses UR.

 

OWNER 

Determines the authorization ID of the owner of the object (plan or package). The owner must have the privileges required to execute the SQL statements contained in the object.

PACKAGE

Determines what package or packages to bind or rebind.

QUALIFIER

Determines the implicit qualifier for unqualified names of tables, views, indexes, and aliases contained in the plan or package.

RELEASE

Determines when to release resources that a program uses, either at each commit point or when the program terminates.

(COMMIT)

Releases resources at each commit point.

(DEALLOCATE)

Releases resources only when the program terminates.

 

VALIDATE

Determines whether to recheck, at run time, errors of the type “OBJECT NOT FOUND” and “NOT AUTHORIZED” found during bind or rebind. The option has no effect if all objects and needed privileges exist.

(RUN)

Indicated that if not all objects or privileges exist at bind time, the process issues warning messages, but the bind succeeds. DB2 checks existence and authorization again at run time for SQL statements that failed those checks during bind. The checks use the authorization ID of the plan or package owner.

(BIND)

Indicates that if not all objects or needed privileges exist at bind time, the process issues error messages, and does not bind or rebind the plan or package,

 

FLAG

Determines what messages to display.

(I)

All informational, warning, error, and completion messages

(W)

Only warning, error, and completion messages

(E)

Only error and completion messages

(C)

Only completion messages

NODEFER(PREPARE)

Determines whether to defer preparation for dynamic SQL statements that refer to remote objects, or to prepare them immediately. If you defer preparation, the dynamic statement prepares when DB2 first encounters a statement of the type EXECUTE, OPEN, or DESCRIBE that refers to the dynamic statement.

NODEFER(PREPARE)

Does not defer preparation.

DEFER(PREPARE)

Defers preparation.

 

DEGREE

Determines whether to attempt to run a query using parallel processing to maximize performance.

(1)

Prohibits parallel processing.

(ANY)

Allows parallel processing.

 

Important SQL CODES and ABEND CODES
SORT JOIN – TO JOIN TWO FILES BASED ON A KEY
KNOW YOUR MAINFRAME
REXX – INITIAL SETUP
DB2 SQL Query to read COMP (COBOL) data stored in CHAR column
DB2 Performance – Using SET vs SYSDUMMY1 table
DB2 Performace – Predicates Processing Order
DB2 External Stored Procedures
DB2 UPDATE QUERY – UPDATE TABLE1 FROM TABLE2 DATA
DB2 PERFORMANCE ISSUE (When using EXISTS)
HOW DOES DB2 INTERNALLY (PHYSICALLY) STORE THE DATA
HOW DO YOU FIND WHO HAS ACTUALLY LOADED THE DB2 TABLE RECENTLY
TERMINATE A STOPPED DB2 UTILITY
SELECT UNIQUE RECORDS USING “GROUP BY”
DB2 and SQL INTERVIEW QUESTIONS
DB2 TIPS
Optimistic Locking vs. Pessimistic Locking
DB2 BIND OPTIONS and ISOLATION
Is SCHEMA name necessary for DYNAMIC QUERY
DB2 SQL – REPLACE CHARACTERS WITH ACCENTS (NON ENGLISH ALPHABETS)
Advertisements