Wednesday, January 7, 2009

BIND PARAMETERS

Explanation for Bind parameters:


DSN SYSTEM (DSN)

BIND PLAN(CBI018P) -

MEMBER(CBI018P, CBISOFM) -

ACQUIRE(USE) - --> Acquires table space locks only when the application program bound to the plan first uses them.

RELEASE(COMMIT)- --> Releases the table locks once commit is issued.

OWNER(OWNER) -

QUALIFIER(NAME) --> Used to qualify Un-qualified objects(without any schema name) in the query.

ACTION(REPLACE)- --> It replaces existing PLAN with a new PLAN with same name. If you change the SQL statements or recompile a program, you should use BIND PLAN with the option ACTION(REPLACE).

RETAIN - --> Preserves EXECUTE privileges when you replace the plan using ACTION(REPLACE).

VALIDATE(BIND)- --> DB2 verifies the authorization on the objects involved at bind time

ISOLATION(CS) - --> DB2 uses Cursor Stability phenomenon for locking purposes.

DYNAMICRULES(BIND)- -->DB2 checks necessary authorization at bind time to execute dynamic SQL statements .

DBPROTOCOL(PRIVATE) -->DB2 to use DB2 private protocol access for accessing remote data that is specified with three-part names.

END

Sunday, January 4, 2009

DB2 Command - DISPLAY GROUP



The DISPLAY GROUP command can be used to check information about the DB2 subsystem. The above screenshot conveys the following information
DB2 subsystem name --> DSNS,
Version --> V8.1,
Subsystem is active or not,
System name is EGGQ,
and the mode in which it is .
MODE(C) --> compatibility mode,
MODE(E) --> Enable New function mode,
MODE(N) --> New function mode.

Friday, January 2, 2009

DB2 Command - DISPLAY BUFFERPOOL











The DISPLAY BUFFERPOOL command can be issued to display the current status and allocation information for each buffer pool.

The above screenshot conveys that the bufferpool BP0 has been assigned 16,000 pages, all of which have been allocated. No. of pages which are IN-USE/UPDATED(pages which are being updated or already updated which are not externalized ie. written into disk) is 537.Sequential access(consecutive data pages are fetched from the disk and stored in BP) is 80% and hence Random access(data pages are fetched randomly ) would be 20%.Information on write thresholds like Deferred write thresholds(DWTH) which is 50% and Vertical deferred write threshold(VDWTH) which is 10% can be checked.

For additional information-buffer-pool details, you can specify the DETAIL parameter. Using DETAIL(INTERVAL) produces buffer pool usage information since the last execution of DISPLAY BUFFERPOOL. To report on buffer pool usage since the pool was activated, specify DETAIL(*). In each case, DB2 will return detailed information on buffer-pool usage such as the number of GETPAGEs, prefetch usage, and synchronous reads. The detailed data returned after executing this command can be used for rudimentary buffer pool tuning. For example, you can monitor the read efficiency of each buffer pool using the following formula:

(Total GETPAGEs) / [ (SEQUENTIAL PREFETCH) +
(DYNAMIC PREFETCH) +

(SYNCHRONOUS READ)]

The higher the number, the better. Additionally, if buffer pool I/O is consistently high, you might consider adding pages to the buffer pool to handle more data.

Finally, you can gather even more information about your buffer pools using the LIST and LSTATS parameters. The LIST parameter lists the open tablespaces and indexes within the specified buffer pools; the LSTATS parameter lists statistics for the tablespaces and indexes reported by LIST. Statistical information is reset each time DISPLAY with LSTATS is issued, so the statistics are as of the last time LSTATS was issued.