C2090-616 Online Practice Questions and Answers

What happens when you run the following statement if MYUSER holds no privileges on table MYTABLE? REVOKE SELECT ON TABLE mytable FROM USER myuser

A. An error is returned

B. An entry is mane in SYSCAT.TABAUTH

C. The REVOKE statement succeeds with no effect

D. All privileges are granted to user MYUSER on MYTABLE

Below are the SQL statements a DBA is planning to execute on a database:

CREATE TABLE t1 (name VARCHAR (1));

INSERT INTO t1 VALUES (`A'), (`B'), (`C'), (`D');

ALTER TABLE t1 ALTER COLUMN name SET DATA TYPE INT;

What will be the outcome of the last statement?

A. The statement will fail due to variable length character error

B. The statement will fail due to an incompatible data type error

C. The statement will execute successfully and it deletes the character data

D. The statement will execute successfully and it internally converts the character data into ASCII numbers

What of the following is TRUE about triggers?

A. Triggers are activated by INSERT, UPDATE, DELETE and SELECT statements

B. Triggers can be used to perform data manipulation operations against complex views

C. Triggers can only be used to execute logic before or after appropriate SQL statements

D. Only one trigger for a particular event and activation time can be created for a particular table

Which three facilities are used to control authorization in DB2?

A. privileges, db2ssh, LBAC credentials

B. privileges, authority levels, certificates

C. db2ssh, trusted contexts, authority levels

D. privileges, authority levels, LBAC credentials

Which locking event occurs when two applications lock rows that are needed by the other, such that neither application can continue executing?

A. Deadlock

B. Lock timeout

C. Lock escalation

D. Lock conversion

Which of the following statements about stored procedures is TRUE?

A. Stored procedures can be written in SQL PL, but not in the PL/SQL dialect

B. The CREATE SOURCED PROCEDURE statement defines a procedure that is based on another procedure in a federated database

C. There are three types of stored procedures which can be deployed: SQL procedures, external procedures, and sourced procedures

D. Two identically-named procedures within a schema are permitted to have exactly the same number of parameters, but the parameters must have different data types assigned

Which of the following isolation levels most reduces concurrency and is most likely to cause lock escalations?

A. Read Stability

B. Cursor Stability

C. Repeatable Read

D. Uncommitted Read

Which of the following is TRUE about a COMMIT statement executed at the end of the T1 unit of work?

A. A ROLLBACK command executed just after the COMMIT command cancels that COMMIT.

B. Save points defined during transaction T1 are released only if there is a RELEASE SAVEPOINT command executed after the COMMIT command.

C. It finishes the current T1 unit of work and a new one is initiated, however it is possible to ROLLBACK to the latest SAVEPOINT before COMMIT.

D. It finishes the current T1 unit of work and a new one is initiated. All changes made by UPDATE and DELETE commands during unit of work T1 are committed.

Which of the following DB2 objects in an efficient way of representing data without the need to maintain it and requires no permanent storage?

A. View

B. Alias

C. Table

D. Schema

What is the primary use case for creating an index with the RANDOM option on a table?

A. Maximize performance with OLTP random key queries

B. Maximize performance when inserting into an expression based index containing the RAND() function

C. Minimize contention due to ascending key or descending key inserts, especially in a pureScale environment

D. Minimize hashing skew on a table in a multiple database partition environment, especially with column organized tables