DB2 acquires a lock on every record being updated. DB2 has a call level interface (CLI) which is a C/C++ API for database usage. The LOCK TABLE command locks all records in a table until the transaction is COMMITed. You can set the LOCKTIMEOUT parameter. When a wait reaches the LOCKTIMEOUT value, a SQL0911 will be issued and the transaction rolled back.DB2 has a deadlock detector which randomly chooses one of the transactions causing the deadlock, and it rolls that transaction back. DB2 maintains a lock list. When the list gets full, it shall replace all row level locks with one table level lock. This is called Lock Escalation.
Here are some guidelines to avoid deadlock problems:
- COMMIT often
- Issue COMMITs even when you are only doing reads
- Specify FOR FETCH ONLY (also known as FOR READ ONLY)
- Perform any database changes near the end of your transaction
DB2 has a load utility. It only has the ability to insert data into a table, but not a view. This technique is faster than an import of data. It writes directly to the database. Constraint checking is skipped. However the load will reject any rows that do not comply with the table's column definitions. The db2look utility will generate the DDL for any given database object. The RUNSTATS utility updates statistics for tables and indexes.
The database transaction log keeps track of changes to database objects and data. The ROLLFORWARD command will replay the log for the database to catch back up after a crash. DB2 has the ability to copy a database by writing a copy of all data to disk. This is called "splitting a mirror". There is an INSPECT utility which shall check for data integrity problems and inconsistencies in the database.
