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. 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 the DBD within the context of DB2?
Answer: In the context of DB2, DBD stands for Database Descriptor. It is an internal object that contains information about a specific database in the DB2 subsystem. Think of it as a blueprint or a map of the database. It stores details about the database's structure and objects, including:
- Tablespaces: Storage areas where the database's data resides.
- Tables: The actual tables within the database and their columns.
- Indexes: Structures that speed up data retrieval.
- Table Check Constraints: Rules that ensure data integrity.
- Referential Integrity Constraints: Rules that define relationships between tables.
Key points about DBDs:
- Unique per database: Each database within the DB2 system has exactly one corresponding DBD.
- Internal object: DBDs are not directly accessible to users; DB2 manages them internally.
- Critical for operations: DB2 uses DBDs for various operations, including data access, query optimization, and utilities.
- Dynamic updates: DB2 automatically updates the DBD whenever the corresponding database is created or modified.
Why are DBDs important?DBDs play a crucial role in DB2's operation:
- Efficiency: They help DB2 quickly locate and access database objects, improving performance.
- Data integrity: They store information about constraints, ensuring that data remains consistent and valid.
- Concurrency: They help manage concurrent access to the database, preventing conflicts between different users or applications.
Managing DBDs:
While users don't directly interact with DBDs, it's important to be aware of them, especially for large databases. Large DBDs can potentially impact performance. DB2 provides utilities to monitor and manage DBD size, such as the `MODIFY RECOVERY` utility, which can reclaim storage in the DBD. In summary, the DBD is a fundamental component of DB2 that stores essential information about a database's structure and objects. It plays a vital role in ensuring efficient data access, maintaining data integrity, and managing concurrency.
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?

Answer: No. 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.

What SQL commands are needed to restore an entire database from DB2 ?

Answer:
Restoring an entire database in IBM DB2 involves the RESTORE DATABASE command. The steps and commands required depend on whether the backup is taken locally or remotely and the level of granularity (e.g., full, incremental).
Here’s a general guide for restoring an entire database:
1. Prepare the Environment
  - Ensure that the database you want to restore does not exist in the system. If it exists, you may need to drop it:
```
DROP DATABASE database_name;
        
```
  - Ensure you have sufficient disk space and proper permissions for the restore operation.
  - Identify the location of the backup image file.
2. Restore the Database
  Use the RESTORE DATABASE command.
  
  Full Database Restore Command:
```
RESTORE DATABASE database_name
FROM backup_location
TAKEN AT timestamp
INTO target_database_name
WITH REPLACE
WITHOUT PROMPTING;
    
```
  - database_name: Name of the database in the backup.
  - backup_location: Path to the directory where the backup file resides.
  - timestamp: Timestamp of the backup (visible in backup history or the filename).
  - target_database_name: (Optional) Name of the database you want to create or overwrite.
  - WITH REPLACE: Forces overwrite of an existing database.
3. Example of a Full Restore
  Suppose you have a backup of the database MYDB in /backups taken on 202412140735. You can restore it as:
```
RESTORE DATABASE MYDB
FROM '/backups'
TAKEN AT 202412140735
INTO MYDB
WITH REPLACE
WITHOUT PROMPTING;
    
```
4. If the Backup is Incremental
  You need to restore the full backup first and then apply the incremental backups in sequence:
```
RESTORE DATABASE MYDB
FROM '/backups'
TAKEN AT 202412140735
WITHOUT PROMPTING;

RESTORE DATABASE MYDB
INCREMENTAL
FROM '/backups'
TAKEN AT 202412150800
WITHOUT PROMPTING;
    
```
5. Roll Forward the Logs
  After restoring, you may need to apply transaction logs if the database was enabled for archive logging. Use the ROLLFORWARD DATABASE command:
```
ROLLFORWARD DATABASE MYDB
TO END OF LOGS
AND COMPLETE;
    
```
  - This step ensures that all committed transactions are applied to the restored database.
6. Verify the Restore
  After restoring, connect to the database to ensure it was restored successfully:
```
CONNECT TO MYDB;
    
```
Notes:
- Use the LIST HISTORY BACKUP command to get a list of backups:
```
LIST HISTORY BACKUP ALL FOR database_name;
    
```
- For advanced options, refer to the IBM DB2 documentation on RESTORE DATABASE.