C2090-616 Online Practice Questions and Answers

Which constraint can be used to ensure that identical values are never stored in a set of columns in a table?

A. NOT NULL

B. Foreign key

C. Primary key

D. Informational constraint

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

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

What is the purpose of a role?

A. To define exactly what a specific user can and cannot do within a particular database once they have been authenticated.

B. To group a collection of users together so that they can be simultaneously granted and revoked specific authorities and privileges.

C. To group a collection of privileges together so that they can be simultaneously granted to and revoked from multiple users, groups, or other roles.

D. To assign one or more users that need to run local DB2 applications and tools on a particular server to the DB2ADMNS or the DB2USERS group.

The following statements are executed:

GRANT CREATETAB, DATAACCESS, ON DATABASE TO ROLE role A;

GRANT CREATETAB, BINDADD ON DATABASE TO ROLE role B;

GRANT ROLE role A to USER db2inst3;

GRANT ROLE role B to USER db2inst3;

Which of the following is TRUE if this statement is executed later?

REVOKE ROLE roleB FROM USER db2inst3;

A. This statement will succeed and DB2 INST3 will still be able to create in the database.

B. This statement will succeed and DB2 INST3 only retains DATAACCESS privileges on the database.

C. This statement will fail as CREATETAB is granted to two different roles and must be revoked at the same time.

D. This statement will succeed with a warning since CREATETAB cannot be totally revoked. DB2INST3 will retain both roles.

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

A portion of your database tables are used for OLTP-type processing and another portion are used for more analytical queries. There is a small subset of tables that are used in both of these usage patterns. What could you use to derive the analytic improvements offered by BLU technology in such an environment?

A. Shadow tables

B. Range-partitioned tables

C. Materialized query tables

D. Multidimensional clustering tables

Which of the following is designed for continuous availability, increased throughput of many concurrent short queries, and easy scalability?

A. DB2 HADR

B. DB2 pureScale

C. DB2 Express-C

D. DS2 Database Partitioning Feature

Consider the following SQL statement:

SELECT workdept, empno, lastname, salary,

RANK() OVER (PARTITION BY workdept ORDER BY salary) AS rank_salary

FROM employee

ORDER BY workdept, lastname

What does the RANK() OVER (PARTITION BY workdept ORDER BY salary) clause do?

A. It ranks the rows in each WORKDEPT by SALARY.

B. It groups all rows by WORKDEPT, then ranks all rows in the complete result set by SALARY.

C. It generates a blank output line after each group of rows with the same WORKDEPT, and ranks these groups by SALARY.

D. It tells the optimizer to physically partition the EMPLOYEE table by the WORKDEPT column for runtime execution, and ranks each WORKDEPT by SALARY.

The DDL statement is used to create table T1: CREATE TABLEt1 (

c1 INTEGER,

c2 INTEGER NOT NULL,

c1 DECIMAL(11,2),

c4 TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP

)

Which of the following INSERT statements will execute successfully?

A. INSERT INTO t1 VALUES (100)

B. INSERT INTO t1 (c1, c2) VALUES (100)

C. INSERT INTO t1 (c1, c3) VALUES (`100', `100.00')

D. INSERT INTO t1 (c2, c3) VALUES (100, 100.00), (101, 101)

The application owners say they need to be able to query a table and previous versions of that table. Which type of table is appropriate to satisfy this requirement?

A. Shadow Table

B. Temporal Table

C. Range-Partitioned Table

D. Multidimensional Clustered Table

Which of the following statements will successfully create a table that scores index data in a different table space than the table data?

A. CREATE TABLE t1 (c1 INT, c2 INT);

B. CREATE TABLE t1 (c1 INT, c2 INT) IN TS1 INDEX IN TS2;

C. CREATE TABLE t1 (c1 INT, c2 INT) IN TS1 INDEX IN TS1;

D. CREATE TABLE t1 (c1 INT, c2 INT) DATA IN TS1 INDEX IN TS2;

How can you tell what privileges on a table or view have been granted to a user? (Choose two.)

A. db2look with the -x option

B. db2pd with the -auth option

C. Query the SYSCAT.DBAUTH view

D. Query the SYSCAT.TABLES view

E. Query the SYSCAT.TABAUTH view

Which of the following authorities is sufficient for connecting to a database?

A. SQLADM

B. CONNECT

C. DATAACESS

D. ACCESSCTRL

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