DB2 Interview Questions - III for Refresh

1.What are the disadvantages of PAGE level lock?

High resource utilization, if large updates are to be done.

2. Can I use LOCK TABLE on a View?
No. To lock a VIEW, take lock on the underlying tables.

3. What is ALTER?
SQL command used to change the definition of DB2 Objects.

4. What is ACQUIRE/RELEASE in BIND?

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

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

6. In SPUFI suppose you want to select max of 1000 rows, but the select returns only 200 rows. What are the 2 sql codes that are returned

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

7. When is the access path determined for dynamic SQL?

At run time, when PREPARE statement is issued.

8. 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 happens?

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

9.How does DB2 store NULL physically?

As an extra byte prefix to the column value. Physically the Null prefix is hex’00’ if the value is present and Hex ‘FF’ if it is not.

10.What is RUNSTATS?

A DB2 utility used to collect statistics about the data values in tables which can be used by the optimizer to decide the access path. It also collects statistics used for space management. These statistics are store in DB2 catalog tables.

11. When will you chose to run RUNSTATS?

After a load, or after mass updates, inserts, deletes or after REORG.

12. After Runstats what you should run to make db2 program more efficient.

Do a Rebind of all programs. You can run IKJEFT01 with SYSTSIN as
DSN SYSTEM(DBTG)
REBIND PACKAGE(package-name.*) OWNER()
END

13. Give some example of statistics collected during RUNSTATS?

# of rows in the table
% of rows in clustering sequence
# of distinct values of indexed column
# of rows moved to a nearby/far way page due to row length increase

14. What is REORG? When is it used?

REORG reorganizes data on physical storage to re cluster rows, positioning overflowed rows in their.
proper sequence, to reclaim space, to restore free space. It is used after heavy updates, inserts and delete activity and after segments of a segmented tablespace have become fragmented.

15. What is IMAGECOPY?

It is a full backup of a DB2 table which can be used in recovery.