Database and SQL

When an application holding a Share lock needs to update a row, DB2 automatically converts (promotes) the row-level Share lock to an Update lock. The application does not need to explicitly release and reacquire locks - lock conversion is handled transparently by the database manager. Option D is incorrect because escalation is to table-level locks, not row-level Update locks.

Views are used to restrict access to specific rows or columns (A), simplify complex queries (D), and ensure data integrity through CHECK OPTION (B). Producing an action in response to table changes is the purpose of triggers, not views. Views are passive queries - they select data but cannot execute procedural logic in response to changes.

DB2 does not necessarily fetch all rows when opening a cursor. The behavior depends on cursor type and optimization - with a declared cursor, rows may be fetched one at a time as needed (FETCH operations), not all at once. The result set is materialized progressively, not entirely at cursor open time. This approach improves performance and reduces memory usage for large result sets.

Scalar functions in DB2 can be invoked using VALUES clause (A) for direct function calls returning a single value, or within a SELECT statement (B) to apply the function to each row. Option C (CALL) is used for stored procedures, not scalar functions. Option D is correct because both A and B demonstrate proper scalar function invocation syntax.

In DB2, a SYNONYM is automatically dropped when its underlying table or tablespace is dropped. An ALIAS, however, is preserved even if the referenced table is dropped.

A schema is a collection of named database objects such as tables, views, and indexes. It provides a logical grouping and namespace for these objects. Tables are individual objects within a schema, not collections themselves. An alias is just an alternate name for an object.

B37 abend is a specific error code in mainframe systems indicating a DASD space issue - the dataset or work file has run out of space during SPUFI (SQL Processor Using File Input) execution. Data issues or missing records would result in different error codes like SQLCODEs, not B37.

Data can be retrieved from multiple tables using Joins (combining columns), Unions (combining rows), or Nested SELECT queries (subqueries). Therefore, all of the listed methods are correct.

COL2 is the definition that will cause failure because VARCHAR columns cannot have DEFAULT NONE in DB2. The DEFAULT clause for VARCHAR must specify a valid string value or be omitted entirely. COL1, COL3, and COL4 definitions are syntactically valid.

A UNIQUE constraint ensures a column will not accept NULL values (in most DBMS implementations) and creates an index that can be referenced by a foreign key. CHECK constraints only validate data values, DEFAULT constraints provide fallback values, and INFORMATIONAL is not a standard constraint type.

In DB2 and similar SQL databases, the specific privilege required to create a table is CREATETAB. General administrative privileges are not strictly necessary, and USERTAB is not a valid privilege.

UNION must be declared within a CURSOR in embedded SQL because embedded SQL requires declared cursors for result set processing. This is a restriction specific to embedded SQL environments where result sets must be explicitly defined.

SELECT COUNT() is the correct SQL syntax for counting all rows in a table. Option A is missing parentheses, C uses incorrect brackets, and D is not valid syntax for counting all rows. COUNT() counts all rows including NULLs.

The RUN command in SQL*Plus executes the SQL statements stored in the current buffer. It processes the SQL command and displays the output. The other options (jump, walk, sleep) are not valid SQL*Plus commands.