DB2 Database Fundamentals Quiz

When an application holds a Share (S) lock on a row and wants to update it, DB2 automatically attempts to upgrade or convert the existing row-level Share lock to an Exclusive (X) or Update (U) lock. The application does not manually release it first.

A unique constraint ensures all values in a column are different and cannot be NULL. This makes it suitable for foreign key references since other tables need reliable, non-null values to establish relationships.

CREATETAB privilege specifically authorizes a user to create tables in a database. Without this privilege, users cannot execute CREATE TABLE statements regardless of other permissions they might have.

To store numeric values up to 9999999.999, the column needs to hold 7 digits before the decimal point and 3 digits after, requiring a total precision of 10 and scale of 3. Thus, DECIMAL(10,3) is the correct and most appropriate data type.

The Task Center provides scheduling and automation capabilities for database maintenance operations. It can automate repetitive tasks like table reorganization to run at specified times or intervals.

COUNT(*) is the correct SQL syntax to count all rows in a table. The parentheses are required for the COUNT function, and the asterisk indicates all rows should be counted regardless of NULL values.

The WITH DEFAULT NONE clause is invalid SQL syntax. DEFAULT requires a valid value or constraint like CURRENT DATE, a literal, or NULL. NONE is not a recognized default value specification, causing the CREATE TABLE to fail.

SYSMAINT is a system-level authority granted through the DB2 Database Manager configuration. It allows maintenance operations like backups and restores but does not include data access permissions like CONNECT or full administrative control.

Repeatable Read is the strictest isolation level that locks all rows scanned during query execution. This ensures that if the same query is run again, it returns the exact same result set without phantom reads.

In COBOL-DB2 programming, a null indicator variable must be declared as a 16-bit signed integer. The picture clause S9(4) COMP (or S9(4) BINARY) defines a 2-byte binary integer, which is the standard format for null indicators.

In DB2, an alias is defined on a table. If the underlying table or its tablespace is dropped, the alias is not automatically dropped; it remains but becomes invalid. Conversely, a synonym is automatically dropped when its underlying table is dropped.

A schema is a logical container or collection of named database objects, such as tables, views, and indexes, owned by a particular database user. Tables and aliases are individual objects within a schema, making them incorrect.

Most database systems impose a practical limit on the number of tables that can be joined in a single query. While the exact limit varies, 15 tables is a common constraint to prevent performance degradation.

DB2 does not materialize the entire result set when a cursor is opened. It uses deferred fetch operations, retrieving rows on-demand rather than all at once, which improves memory efficiency and performance.

A scalar user-defined function like degf_to_c can be executed inline within a SELECT statement or evaluated using a VALUES statement. It cannot be called using the CALL statement, which is reserved for stored procedures.

The underscore (_) is the SQL wildcard character that represents exactly one character in a LIKE pattern. This differs from % which matches zero or more characters.

To combine or retrieve data from multiple tables, SQL provides Joins (combining columns), Unions (combining rows), and Nested SELECT subqueries. Therefore, all of these options are valid methods.

In embedded SQL (SQL embedded in host languages like C or Java), UNION operations that return multiple rows typically require a cursor context to properly fetch and process the result set. This is because UNION combines multiple result sets and the cursor provides a mechanism to iterate through the combined rows. Other isolation levels either use fewer locks or release locks earlier.

Views are used to restrict access, simplify queries, or provide alternative representations of data. They are passive structures and cannot produce an action (such as executing code) in response to data changes; that is the role of a trigger.

Repeatable Read isolation level locks all rows that are scanned to build the result set, preventing phantom reads and ensuring that if the same query is executed again within the transaction, the same rows will be returned. Read Stability only locks individual rows as they are read, Cursor Stability locks only the current row position, and Uncommitted Read imposes minimal locking.