DB2 and Oracle Database Systems Quiz

In NOARCHIVELOG mode, Oracle cannot perform hot/inconsistent backups because there are no archived redo logs to recover transactions. Only consistent (cold) backups are possible where the database is cleanly shut down. ARCHIVELOG mode allows both consistent and inconsistent backups.

V$PARAMETER displays the current parameter values actively in use by the Oracle instance, including modified values that differ from defaults. It shows the effective runtime values, not just defaults or file contents.

In NOARCHIVELOG mode, you can only perform consistent backups (cold backups) while the database is shut down cleanly. Hot or online backups are not possible because redo logs are not archived.

AUD$ is the audit trail table in Oracle that can be modified, typically for maintenance purposes like truncating old audit records. Other SYS tables like COL$ and ROW$ are data dictionary tables that should never be directly modified.

An index scan occurs when DB2 accesses and searches the index structure itself to locate the requested rows, rather than scanning the entire tablespace.

SPUFI (SQL Processor Using File Input) is a DB2 facility that executes SQL statements from a sequential file. It reads SQL statements from a file, processes them, and returns the results, making it useful for batch SQL execution and testing.

A composite key is a key that consists of two or more columns used together to uniquely identify a row in a table. Unlike a simple key that uses a single column, composite keys combine multiple columns to ensure uniqueness when no single column can uniquely identify records.

In DB2, locks can be obtained at the tablespace level, which is a logical grouping of tables. Locking at the tablespace level provides concurrency control for data managed within that tablespace. Options like directory, bufferpool, and column are not lockable objects in DB2.

The COLLECTION parameter in DB2 precompile/bind specifies the schema (qualifier) name for the package. It groups related packages together under a common schema identifier, helping organize and manage packages within the database.

The WITH HOLD option in cursor declaration maintains the cursor position across transaction boundaries. When a transaction commits, a WITH HOLD cursor remains open and positioned, allowing continued fetching without repositioning. FOR FETCH is not a valid DB2 cursor option.

The DB2 precompiler processes embedded SQL statements in host language source code and replaces them with CALL statements to DB2 runtime services. This transformation allows the host language compiler to process the code without needing to understand SQL syntax directly.

The isolation level determines how locked data is isolated from other processes and is specified during the BIND or REBIND process of a package or plan.

SQLCODE -803 in DB2 indicates a duplicate key violation, which occurs when an INSERT or UPDATE tries to create a duplicate value in a column with a unique constraint or index. This error specifically signals that the inserted data violates uniqueness requirements.

A DB2 package is an object containing optimized access paths for a single DBRM that has been bound independently of other modules.

A Database Request Module (DBRM) contains the SQL statements extracted from the source program by the precompiler, along with some additional control information.

DB2 uses the IRLM (Intelligent Resource Lock Manager) for managing locks and controlling concurrent access to data. IRLM is a specialized component that handles lock requests, deadlock detection, and resource serialization across the DB2 subsystem.

A Plan in DB2 is an executable module containing the access path logic determined by the DB2 optimizer. When you bind a plan, DB2 generates optimized access paths for SQL statements, which are stored in the plan and executed at runtime.

The FREE command in DB2 removes (drops/deletes) an existing plan from the system. Once freed, the plan is no longer available and must be rebound if needed again. The terms drop and delete are effectively equivalent in this context.

The DB2 BIND process can produce either a package (from a DBRM) or an application plan (directly from DBRMs or by grouping packages), making both options valid.