DB2 Lock Contention Questions
- What is lock contention?
Answer: To maintain the integrity of DB2 objects the DBD permits access to only on object at a time.
Lock contention happens if several objects are required by contending application processes simultaneously.
- What is SPUFI?
Answer: SPUFI stands for SQL processing using file input.
It is the DB2 interactive menu-driven tool used by developers to create database objects.
- What is the significance of DB2 free space and what parameters control it?
Answer: The two parameters used in the CREATE statement are the PCTFREE which specifies the percentage of free space for each page and FREEPAGE which indicates the number of pages to be loaded with data between each free page.
Free space allows room for the insertion of new rows.
- What is a NULL value? What are the pros and cons of using NULLS?
Answer: A NULL value takes up one byte of storage and indicates that a value is not present as opposed to a space or zero value. It is the DB2 equivalent of TBD on an organizational chart and often correctly portrays a business situation.
Unfortunately, it requires extra coding for an application program to handle this situation.
- What is a synonym? How is it used?
Answer: A synonym is used to reference a table or view by another name.
The other name can then be written in the application code pointing to test tables in the development stage and to production entities when the code is migrated. The synonym is linked to the AUTHID that created it.
- What is an alias and how does it differ from a synonym?
Answer: An alias is an alternative to a synonym, designed for a distributed environment to avoid having to use the location qualifier of a table or view. The alias is not dropped when the table is dropped.
- What is a LIKE table and how is it created?
Answer: A LIKE table is created by using the LIKE parameter in a CREATE table statement. LIKE tables are typically created for a test environment from the production environment.
- If the base table underlying a view is restructured, eg. attributes are added, does the application code accessing the view need to be redone?
The table and its view are created anew, but the programs accessing the view do not need to be changed if the view and attributes accessed remain the same.
- Under what circumstances will DB2 allow an SQL statement to update more than one primary key value at a time?
Answer: Never. Such processing could produce duplicate values violating entity integrity.Primary keys must be updated one at a time.
- What is the cascade rule and how does it relate to deletions made with a subselect.
Answer: The cascade rule will not allow deletions based on a subselect that references the same table from whichthe deletions are being made.