DB2 Stored Procedures and Oracle Database Fundamentals

Composite primary keys (primary keys consisting of more than one column) cannot be declared at the column level; they must be declared using table constraint syntax.

SUBSTR is the Oracle SQL function that extracts a portion of a string based on position and length parameters (SUBSTR(string, start, length)). INSTR finds the POSITION of a substring within a string, not the substring itself. SUBSTRING is not a valid Oracle function (MySQL uses SUBSTRING). POS is not a standard Oracle function. SUBSTR is the correct answer for extracting string portions.

ROWID access is fastest because ROWID contains the physical disk address of the row, enabling direct retrieval without traversing indexes. Primary key and unique index access require index traversal to find the ROWID first, then row access. Full table scan reads all blocks, making it slowest for single-row retrieval. ROWID is essentially a direct pointer to the row's location on disk.

If a user creates a database object (like a table) without specifying a TABLESPACE clause, Oracle automatically stores the segment in the default tablespace assigned to that user.

To join N tables without Cartesian products, you need N-1 join conditions. For 4 tables, you need 3 conditions. Each condition links two tables together. With 3 conditions for 4 tables, you create a chain: T1-T2, T2-T3, T3-T4. Fewer than 3 conditions would leave some tables disconnected, creating Cartesian products between disconnected sets. The minimum is n-1 conditions to connect n tables.

In DB2, declared global temporary tables (DGTTs) require a USER TEMPORARY tablespace to be defined in the database so that the temporary table's instantiated rows can be stored.

Stored procedures simplify code management, provide controlled access to database objects, and improve performance by reducing network traffic. However, DB2 stored procedures can be written in multiple languages (SQL, Java, C, etc.), not just SQL - making statement D false.

GET DIAGNOSTICS is a SQL statement used to retrieve diagnostic information about the most recently executed SQL statement, including error codes, warnings, and exception conditions. It does not analyze db2diag logs, return user IDs, or provide access plans.

The procedure creates a cursor but never opens it, and the SET statement only modifies the input parameter. The procedure returns status 0 (success) but no result set rows because the cursor was never opened. The calculation 10*10+10=110 only affects the local parameter variable.

The script drops a procedure and creates a new one that contains a SELECT statement. Since it modifies database objects (DROP and CREATE), it requires MODIFIES SQL DATA clause. The other options are for less restrictive operations.

User-defined SQLSTATE values must be in the range 70000-99999 as per SQL standard. Option 71000 falls within this valid user-defined range, while the other options are either reserved for specific implementations or outside the valid range.

When DYNAMIC RESULT SETS is set to 3 but only 2 cursors are defined, the procedure executes successfully. The DYNAMIC RESULT SETS clause declares the maximum number of result sets that can be returned, and having fewer cursors than the maximum is perfectly valid.

In DB2 SQL, CURRENT_SCHEMA is a valid special register that returns the current schema name. CURRENT_CLIENT_ACCT, CURRENT_DATETIME, and CURRENT_PARTITION are not standard DB2 special registers (though similar registers like CURRENT_DATE, CURRENT_TIME exist with different names).

In DB2 SQL PL, if a local variable is declared without an explicit default value using the DEFAULT clause, its initial value is automatically set to NULL.

In DB2 LUW, BIGINT, DECFLOAT, and XML are valid built-in data types. DATETIME is not a valid DB2 LUW data type; instead, DB2 uses TIMESTAMP and DATE to represent date and time values.

Two procedures are created with the same NAME (testproc) but different SPECIFIC names (testproc and testp). To drop them unambiguously, you must use the SPECIFIC identifier. Option C uses DROP SPECIFIC PROCEDURE testproc (drops first by specific name) followed by DROP PROCEDURE testproc (drops second by regular name since no other procedure named testproc exists after first drop). Options A and B reference non-existent specific name 'testp' without SPECIFIC keyword. Option D uses incorrect syntax with parameter types.

The procedure inserts 'A', sets SAVEPOINT1, then inserts 'B', 'C', and 'D' with subsequent savepoints. Rolling back to SAVEPOINT1 removes 'B', 'C', and 'D', leaving only 'A'. After the COMMIT, 'E' is inserted, resulting in two rows: 'A' and 'E'.

IBM Data Studio is the primary integrated development environment (IDE) used for developing, debugging, and managing DB2 stored procedures. Other tools like Control Center and Visual Explain do not support stored procedure debugging.