DB2 Database Fundamentals and SQL

DECIMAL(10,3) provides 7 digits before the decimal and 3 after (9999999.999), which matches the requirement. INTEGER has no decimals, REAL uses floating-point with potential precision loss, and NUMERIC(7,3) only allows 4 digits before the decimal (9999.999). The DECIMAL type ensures precise arithmetic operations.

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 CREATE TABLE statement will fail at COL2 because 'WITH DEFAULT NONE' is not valid DB2 syntax. The DEFAULT clause requires a specific value (like a constant, CURRENT DATE, or NULL). 'NONE' is not recognized as a valid default value. The other column definitions are syntactically correct.

The SYSMAINT (System Maintenance) authority is a system-level authority configured within the DB2 Database Manager configuration file (dbm cfg). Privileges like CONNECT, CONTROL, or EXECUTE are database-level privileges granted via SQL.

Repeatable Read (RR) isolation level locks all rows that are scanned to build the result set, preventing other transactions from modifying those rows. Uncommitted Read (UR) doesn't lock rows, Cursor Stability (CS) locks only the current row, and Read Stability (RS) locks only rows that are actually returned, not all scanned rows.

In COBOL-DB2 programming, a null indicator variable must be declared as a 16-bit signed integer. The correct picture clause for this is S9(4) COMP (or S9(4) BINARY), which allocates a 2-byte binary storage area.

In DB2, a SYNONYM is a private alternative name for a table or view that is automatically dropped when its underlying table or tablespace is dropped. An ALIAS is a public object that remains even if the target is dropped.

In relational databases, a schema is a logical container or collection of named database objects, such as tables, views, indexes, and triggers, associated with a particular database user.

A scalar user-defined function in DB2 can be evaluated using a VALUES statement or embedded directly inside a projection list of a SELECT query. It cannot be executed using CALL, which is strictly for stored procedures.

In SQL LIKE pattern matching, the underscore (_) wildcard character is used to represent exactly one single character. The percent sign (%) is used to represent zero or more characters.

To retrieve data from multiple tables, SQL provides several mechanisms: Joins combine columns from different tables; Unions combine rows from different queries; and Nested SELECT statements query tables hierarchically. Thus, all of these methods can be used.

In embedded SQL (SQL within host programs like C, Java, COBOL), UNION operations must be used within a CURSOR declaration. This is because embedded SQL requires explicit result set handling mechanisms - a cursor provides the necessary interface to navigate through the UNION result set row by row. Without a cursor, the host program cannot properly retrieve and process the combined data from the UNION.

Views are virtual tables that provide restricted or alternate access to underlying data. Their purposes include: restricting user access to specific rows/columns (security), providing alternate representations of data (simplification), and with CHECK OPTION ensuring inserted rows conform to the view definition. However, producing an action as a result of a change is what TRIGGERS do - they execute code in response to data modification events (INSERT, UPDATE, DELETE). Views are passive queries; triggers are active procedures.

Repeatable Read is the strictest standard isolation level that prevents phantom reads by locking all rows scanned during query execution. Unlike Read Stability (which locks only rows actually returned) or Cursor Stability (which locks only the current row), Repeatable Read locks the entire row set used to build the result. This ensures that if the same query is re-executed within the transaction, it returns identical results because no other transaction can modify, insert, or delete rows that would affect that result set.

Declared temporary tables are specifically designed to store intermediate or transient results during complex processing operations. Unlike regular tables, they exist only for the duration of a session or transaction and are automatically dropped afterward. They are not meant for backup (use backup utilities), load staging (use external staging tables), or sharing between applications (use regular tables or shared schemas). Their purpose is to hold partial results during multi-step data transformations, complex calculations, or query processing.

Identity values generated during an insert operation are not rolled back if the transaction fails or is rolled back. This behavior prevents serialization bottlenecks and ensures high performance, though it can leave gaps in the sequence.

DB2 host variables cannot be declared inside a standard COBOL copybook if they are to be processed by the DB2 precompiler, because the precompiler processes host variable declarations before expanding standard COBOL copybooks.

The scalar function degf_to_c can be invoked inline within a SELECT statement projection or using a VALUES statement to evaluate the expression. It cannot be invoked using CALL, which is reserved for stored procedures, making 'Both a and b' the correct choice.