DB2 Database Fundamentals

A routine (such as a stored procedure or user-defined function) is an executable database object containing logic that is run using SQL CALL or SELECT. Tables, views, and packages are metadata or structural components that define data or store compiled code but are not executable.

When using an alias to update a table, the user must have UPDATE privileges on the underlying Table itself, not on the alias or individual columns. DB2 checks privileges on the base object that the alias references. The alias is simply an alternative name for the table and doesn't require separate privileges.

A stored procedure is beneficial when a remote client performs multiple database actions (e.g., inserts into two tables), as it runs entirely on the server to reduce network traffic. Simple conversions use functions, change tracking uses triggers, and unique IDs are generated via sequences.

A DB2 Sequence can generate numeric values with any increment value, including 1000. Sequences are independent database objects designed specifically for generating unique numeric values. Identity columns are tied to specific tables, table functions return result sets, and INSTEAD OF triggers modify insert operations but don't generate incremental values.

CREATE UNIQUE INDEX idx_partno ON parts(part_no) is correct because it creates a unique index on part_no (ensuring all part numbers are different) and orders by part_no by default. It uses the least storage by indexing only the required column. Options B, C, and D incorrectly include part_name in the index or order by the wrong column, increasing storage without meeting the requirements.

A Check constraint can enforce that the value of one column never exceeds another by using a condition like 'column1 <= column2'. Check constraints validate row data against user-defined conditions. Range, Referential, and Informational are not standard DB2 constraint types - Referential integrity is enforced through foreign key constraints.

DELETE FROM T03 is the correct SQL syntax to delete all rows from a table. Options A and C incorrectly use DELETE * which is invalid syntax - DELETE operates on rows, not columns. Option B incorrectly uses DELETE ALL which is not standard SQL syntax.

In DB2, renaming a column must be done using the RENAME COLUMN statement rather than ALTER TABLE. Conversely, adding new columns, dropping check constraints, and changing VARCHAR length are standard operations that are supported directly via the ALTER TABLE statement.

A foreign key constraint ensures that when inserting a row into TABLE2, the value for IDCOL2 must match an existing value in the referenced primary key IDCOL1 of TABLE1. This is referential integrity - preventing orphaned references. Option A is incorrect because IDCOL1 can be updated if cascade rules are defined. Option C describes the opposite behavior. Option D is incorrect - joins don't automatically add conditions.

DECIMAL(10, 3) can store values up to 9999999.999 (10 total digits, 3 after decimal) and supports arithmetic operations. INTEGER doesn't support decimals, REAL is floating-point with potential precision loss, and NUMERIC(7, 3) only supports up to 9999.999 which is insufficient for the required range.

An alias is a publicly referenced object name in DB2 that requires no privileges to use. Any user can reference an alias, as authorization checks are deferred to the underlying tables or views when SQL statements are compiled. View, Table, and Package require explicit access privileges.

With CACHE 10 and ORDER specified, the sequence allocates values in a thread-safe manner. CONNECTION1 gets 1 (first call), CONNECTION2 gets 2 (second call from different connection), then CONNECTION1 gets 3 (third call overall). The ORDER clause guarantees strict ordering even with multiple connections. Option A (2) would be correct only for the second call, and options C and D incorrectly assume larger jumps or connection-specific caching.

In a DB2 data sharing environment, sequence caches are allocated per member. Connection 1 caches the first 5 values (5 to 25) and uses 5 and 10. Connection 2 caches the next 5 values (30 to 50) and uses 30. Connection 3 caches 55 to 75, returning 55.

The CATALOG TCPIP NODE command syntax is CATALOG TCPIP NODE REMOTE SERVER . The local node name (mydb2srv) is an alias you create - it is not the remote hostname. Option B correctly specifies mydb2srv as the local node name and myhost as the remote system. Options A and C incorrectly use the remote hostname as the local node name. Option D incorrectly uses the IP address instead of the port number for the SERVER parameter.

In DB2, stored procedures are database objects that are created within a schema. A schema is a namespace that organizes related database objects including tables, views, routines (stored procedures and functions), and other objects. When you create a stored procedure without explicitly specifying a schema, it is created in the default schema for the user. Options A (Alias), C (Package), and D (Routine Space) are not containers for stored procedures.

This question contains two separate questions - the first about INSERT success count and another about query results. For the INSERT question: View v1 has a filter (col1 > 10) but no CHECK OPTION, so INSERT INTO v1 VALUES(5) succeeds despite violating the filter. View v2 uses WITH CASCADED CHECK OPTION, which means the INSERT must satisfy both v2's filter and all underlying views' filters, so INSERT INTO v2 VALUES(5) fails. View v3 inherits the check option from v2, so INSERT INTO v3 VALUES(20) succeeds (meets both conditions) but INSERT INTO v3 VALUES(100) fails (violates v1's > 10 constraint). Thus exactly 2 INSERTs succeed.

XML columns in DB2 store XML documents as parsed hierarchical structures. The beauty of XML columns is their flexibility - they can accommodate any XML structure without requiring schema changes when new parent-child relationships are added. This is a key advantage over relational columns which would require ALTER TABLE statements. Options A, B, and D are all true about XML columns: they support both SQL and XQUERY retrieval, and allow direct access to XML document portions without reading the entire document.

To solve this question, the user needs to know the different datatypes that can be used to store binary data, such as images, in a DB2 table.

Now, let's go through each option and explain why it is right or wrong:

A. GRAPHIC: This datatype is used to store fixed-length character data in Unicode format. It is not appropriate for storing binary data such as images.

B. BINARY: This datatype is used to store binary data in a fixed-length format. It is a valid option for storing images, but it does not provide compression capabilities.

C. IMAGE: This datatype is used to store large objects in a compressed format. It is an option for storing images, but it is not recommended as it has been deprecated in recent versions of DB2.

D. BLOB: This datatype is used to store large binary objects in a compressed format. It is the recommended option for storing images in a DB2 table as it provides compression capabilities and can handle large objects.

Therefore, the correct answer is:

The Answer is: D. BLOB

A CHECK constraint is used to enforce domain integrity by validating that column values satisfy a specified condition. To ensure an INTEGER column contains only positive values, you would define a CHECK constraint such as CHECK (column_name > 0). Option A (Unique) ensures only that all values are different but doesn't restrict them to positive values. Option C (Referential) is for foreign key relationships with other tables. Option D (Informational) is not a standard constraint type in DB2.

To find rows that exist in T1 but not in T2, you need a set difference operation. In SQL, the EXCEPT operator returns all rows from the first query that are not present in the second query. Option B is correct: SELECT * FROM T1 EXCEPT SELECT * FROM T2 will return only the rows from T1 that don't have matching rows in T2. Option A uses MINUS which is Oracle syntax, not DB2. Option C is invalid syntax - UNION and EXCEPT cannot be combined that way. Option D is incorrect because NOT EXISTS requires a correlated subquery, not a standalone SELECT.