DB2 Database Management

Declared temporary tables (DGTTs) are private to a session and are primarily used to store intermediate query results during complex processing, automatically disappearing when the session terminates.

A maximum of 12 triggers can be defined on a single table in a database, covering combinations of INSERT, UPDATE, DELETE operations with BEFORE and AFTER timing. This limit exists due to the finite combinations of trigger events and timing options. Options A, B, and C do not represent the actual trigger limit.

Opening a cursor does not guarantee that DB2 will immediately fetch and build the entire result set in memory. Rows are often retrieved incrementally as the application executes FETCH statements.

Under Cursor Stability isolation level, a lock is held only while the cursor remains on the current row. When the application deletes that row, the lock must be released as the row no longer exists for the cursor to point to. Option A describes normal cursor movement, option C and D involve operations by other applications which would require different locking behaviors.

DB2 SQL error code -805 indicates that the program name specified in the PLAN is not found in the package list. This error occurs when the DB2 system cannot locate the required program package during execution. Options A, B, and D represent different DB2 SQL errors unrelated to missing program names.

The B37 abend code during SPUFI (SQL Processor Using File Input) execution indicates insufficient disk space to allocate the required dataset. This is a space-related error, not a data integrity or query result issue. Options B, C, and D describe different types of issues that would produce different abend codes.

When an application holding a Share (S) lock on a row attempts to update that row, DB2 automatically attempts to convert the existing row-level Share lock into an Update (U) or Exclusive (X) lock to perform the modification.

A UNIQUE constraint ensures that a column does not accept NULL values and enforces uniqueness, making it eligible to be referenced by a foreign key from another table. CHECK constraints validate values against a condition, DEFAULT constraints provide default values, and informational constraints are not enforced. Option A is for validation rules, option B provides default values, and option D represents non-enforced constraints.

To create a table in a database, a user must be granted the CREATETAB privilege. This specific privilege allows the user to define new tables within their schema or database.

To store values up to 9999999.999 (7 digits before decimal, 3 after), DECIMAL(10,3) provides the needed precision with 10 total digits and 3 decimal places. INTEGER has no decimals, REAL is approximate floating-point, and NUMERIC(7,3) only allows 4 digits before the decimal (9999.999 max), insufficient for the required range. Options A, B, and C cannot represent the full range of required values.

Task Center is the IBM DB2 tool that allows you to schedule and automate maintenance tasks including table reorganization operations. Control Center and Command Center are monitoring/management interfaces but don't handle automation. Command Line Processor requires manual execution of commands.

COUNT() is the correct SQL aggregate function syntax to count all rows in a table. The asterisk must be in parentheses. COUNT * (A) is invalid syntax, COUNT[] (C) uses wrong brackets, and COUNT(ALL) (D) is not the standard way to count rows.

The definition of col2 contains WITH DEFAULT NONE, which is invalid syntax in DB2. If you specify NOT NULL, you cannot combine it with WITH DEFAULT NONE in this manner, causing the CREATE TABLE statement to fail.

When opening a cursor, DB2 does not necessarily materialize the entire result set immediately. Depending on the query design, optimization path, and cursor type, DB2 may fetch rows dynamically as the application requests them.

Under the Cursor Stability (CS) isolation level, DB2 acquires a lock on the row currently positioned by the cursor. When the application deletes the row, the lock is held until a commit or rollback occurs to ensure transaction integrity.

SQL code -805 in DB2 indicates that the program name specified in the plan is not found or not bound. This occurs when there's a mismatch between the plan and the actual program. Code -810 is a field error, -801 is a data type conversion error, and -825 is a deadlock/timeout error.

B37 abend during SPUFI (SQL Processor Using File Input) occurs when the output dataset runs out of space. This is a disk space allocation issue, not a data or query problem. The error message specifically indicates that the dataset needs more space allocated.

In DB2 lock escalation, a Share lock held on a row is automatically converted (promoted) to an Update lock when the same application needs to update that row. The lock doesn't need to be released first, nor does it escalate to a table-level lock. This automatic conversion is part of DB2's lock management system.

A Unique constraint ensures all values in a column are distinct and allows the column to be referenced by a foreign key. While Unique constraints allow NULLs by default, when combined with NOT NULL (implied by 'will not accept NULL'), it creates a key suitable for foreign key references. Check constraints validate values, Default constraints set initial values, and Informational constraints are for documentation only.

CREATETAB is the specific DB2 privilege required to create tables. ADMIN privileges are broader than necessary and not specifically required. 'No Privileges' is incorrect, and USERTAB is not a valid DB2 privilege name.