DB2 Database Administration and Privileges

As the table owner, USER1 holds both CONTROL privilege (ownership-level authority) AND ALL PRIVILEGES (individual rights like SELECT, INSERT, etc.). Removing only CONTROL or only ALL PRIVILEGES would leave some privileges intact. Option C correctly revokes both - CONTROL removes ownership-level rights, and ALL PRIVILEGES removes any granted individual privileges.

The DB2 Database Manager configuration file (dbm cfg) is used to set instance-level authorities including SYSMAINT, SYSCTRL, and SYSADM. CONNECT, CONTROL, and EXECUTE are database or object-level privileges granted through SQL statements, not configuration file settings. SYSMAINT is a system maintenance authority defined at the instance level.

Specifying column names within parentheses after the UPDATE keyword restricts the update privilege strictly to the named columns. A generic UPDATE privilege allows modifications to all columns in the table, whereas the ALTER privilege is meant for structural changes rather than changing existing table data.

To drop an index in DB2, a user must have CONTROL privilege on the index (or table) or administrative authority. Granting CONTROL on the index specifically provides this administrative right. The INDEX privilege on a table only allows creating new indexes, not dropping existing ones.

The REFERENCES privilege allows a user to define a referential constraint (foreign key) referencing the specified columns of the parent table. Specifying a parent key is the primary function of this privilege, rather than enabling queries or views (which require SELECT) or defining columns as foreign keys.

WITH GRANT OPTION allows the grantee (USER1) to grant the same privilege to others. USER1 received INSERT on V.VIEW1 with GRANT OPTION, so USER1 can grant INSERT on V.VIEW1 to USER2. USER1 cannot grant privileges on the underlying table (T.TABLE1), cannot grant CONTROL (different privilege), and cannot grant ALL PRIVILEGES (never received it).

To solve this question, the user needs to know the basics of database management and the concept of privileges.

Option A: REVOKE DROP ON T1 FROM PUBLIC - This option revokes the privilege to drop (delete) the entire table T1. It doesn't address the issue of adding rows. Therefore, this option is incorrect.

Option B: REVOKE UPDATE ON T1 FROM PUBLIC - This option revokes the privilege to update existing rows in the table T1. This means that the user cannot change the data in the rows that already exist in the table. It does not address the issue of adding rows. Therefore, this option is incorrect.

Option C: REVOKE DELETE ON T1 FROM PUBLIC - This option revokes the privilege to delete rows from the table T1. This means that the user cannot remove rows from the table. However, the user can still add rows to the table. Therefore, this option is correct.

Option D: REVOKE CONTROL ON T1 FROM PUBLIC - This option revokes all privileges from the PUBLIC on table T1, including the ability to add rows. Therefore, this option is incorrect.

The Answer is: C

The CATALOG TCPIP NODE command requires: a local node name (what you're naming it locally), the remote hostname, and the port. Option B is correct: 'mydb2srv' is the local node name, 'myhost' is the remote hostname, and 446 is the port. Options A and C use 'myhost' as the node name (incorrect), and D uses an IP address instead of hostname for the SERVER parameter (should be port number).

CLIENT authentication means the server trusts the client to authenticate users. In this mode, the client operating system verifies passwords, not DB2. The server accepts whatever user identity the client claims after the OS has authenticated them. Options A, B, and C imply mechanisms that would bypass or duplicate the OS authentication.

The Developer Work Bench is the integrated development environment for DB2 V9 that supports creating and debugging SQL stored procedures. Control Center is for administration. Development Center was from earlier versions. Stored Procedure Builder was a separate tool that was deprecated in favor of Developer Work Bench.

The Journal is a DB2 tool that maintains a historical log of tasks performed, database changes made, messages generated, and notifications received. Task Center is for scheduling tasks. Control Center provides general administration. Activity Monitor shows current activity, not historical information.

SQL Performance Monitor is the tool specifically designed for analyzing database operations against DB2 for i5/OS. Visual Explain shows access plans. Activity Monitor is for real-time monitoring. DB2 Performance Monitor was the tool for other DB2 platforms, not i5/OS.

To invoke a User Defined Function, a user needs EXECUTE privilege on the function. CALL is used for invoking stored procedures in some database systems. USAGE is typically for domains, sequences, or types. REFERENCES is for foreign key constraints. EXECUTE is the correct privilege for UDFs in DB2.

OLTP (Online Transaction Processing) systems are designed to handle day-to-day real-time operations and maintain current, up-to-date data. Unlike OLAP systems which store historical data for analysis, OLTP systems focus on current transactional data. The data age is 'Current' because OLTP systems process transactions as they occur in real-time, not projections or historical records.

The SELECT privilege can be granted on tables and views, allowing users to retrieve data from them. Sequences use the USAGE privilege to control access, schemas use privileges like CREATEIN or ALTERIN, and groups are authorization entities rather than database objects that receive privileges.

The DB2 Geodetic Extender uses a Round Earth model (spherical model) for spatial calculations rather than flat-earth approximations. This is essential for accurate geographic and geodetic computations over long distances where Earth's curvature matters. Geodetic coordinate systems account for the Earth's spherical shape, unlike planar projections that distort distance and area measurements.

In DB2, the Task Center is the graphical administration tool used to organize, schedule, and run tasks such as database backups, scripts, and utilities, while the Journal tracks the execution status and history of those tasks.

The DB2 XML Extender is specifically designed to handle XML documents within DB2 databases, including shredding (parsing and decomposing) XML into relational columns. 'Shredding' refers to the process of breaking XML documents into relational data that can be stored and queried in database tables. The other extenders serve different purposes: AVI for audio/video, Text for text search, and Spatial for geospatial data.

The DB2 Configuration Advisor recommends optimal values for database configuration parameters and buffer pools based on system resources and user inputs. It does not design indexes, write SQL, or generate DDL.