41.How do you do the EXPLAIN of a dynamic SQL statement?

• Use SPUFI or QMF to EXPLAIN the dynamic SQL statement
• Include EXPLAIN command in the embedded dynamic SQL statements

42.How do you simulate the EXPLAIN of an embedded SQL statement in SPUFI/QMF? Give an example with a host variable in WHERE clause.)

Use a question mark in place of a host variable ( or an unknown value ). e.g.
SELECT EMP_NAME
FROM EMP
WHERE EMP_SALARY > ?

43.What are the isolation levels possible ?

CS: Cursor Stability
RR: Repeatable Read

44.What is the difference between CS and RR isolation levels?

CS: Releases the lock on a page after use
RR: Retains all locks acquired till end of transaction

45.Where do you specify them ?

ISOLATION LEVEL is a parameter for the bind process.

46.When do you specify the isolation level? How?

During the BIND process. ISOLATION ( CS/RR )...
I use CS and update a page. Will the lock be released after I am done with that page?
No.

47.What are the various locking levels available?

PAGE, TABLE, TABLESPACE

48.How does DB2 determine what lock-size to use?

• Based on the lock-size given while creating the tablespace
• Programmer can direct the DB2 what lock-size to use
• If lock-size ANY is specified, DB2 usually chooses a lock-size of PAGE

49.What is lock escalation?

Promoting a PAGE lock-size to table or tablespace lock-size when a transaction has acquired more locks than specified in NUMLKTS. Locks should be taken on objects in single tablespace for escalation to occur.

50.What are the various locks available?

SHARE
EXCLUSIVE
UPDATE

51.Can I use LOCK TABLE on a view?

No. To lock a view, take lock on the underlying tables.

52.What is ALTER ?

SQL command used to change the definition of DB2 objects.

53.What is ACQUIRE/RELEASE in BIND?

Determine the point at which DB2 acquires or releases locks against table and tablespaces, including intent locks.

54.What else is there in the PLAN apart from the access path?

PLAN has the executable code for the SQL statements in the host program

55.What happens to the PLAN if index used by it is dropped?

Plan is marked as invalid. The next time the plan is accessed, it is rebound.

56.What are the advantages of using a PACKAGE?

• Avoid having to bind a large number of DBRM members into a plan
• Avoid cost of a large bind
• Avoid the entire transaction being unavailable during bind and automatic rebind of a plan
• Minimize fallback complexities if changes result in an error

57.What is a collection?

a user defined name that is the anchor for packages. It has not physical existence. Main usage is to group packages. In SPUFI suppose you want to select max. of 1000 rows , but the select returns only 200 rows.

58.What are the 2 sqlcodes that are returned?

100 ( for successful completion of the query ), 0 (for successful COMMIT if AUTOCOMMIT is set to Yes).

59.Suppose I have a program which uses a dynamic SQL and it has been performing well till now. Off late, I find that the performance has deteriorated. What happened?

Probably RUN STATS is not done and the program is using a wrong index due to incorrect stats.
Probably RUNSTATS is done and optimizer has chosen a wrong access path based on the latest statistics.

60.How does DB2 store NULL physically?

as an extra-byte prefix to the column value. physically, the nul prefix is Hex ’00’ if the value is present and Hex ‘FF’ if it is not.