C2090-616 Online Practice Questions and Answers

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

Shadow tables are a special kind of Materialized Query Table, but what makes them different? (Choose two.)

A. Shadow tables cannot be queried directly

B. Shadow tables do not store their data on disk

C. Shadow tables must be organized by column

D. Shadow tables must be maintained by replication

E. You cannot join shadow tables to other kinds of tables

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 statements regarding locking is TRUE?

A. The same lock modes apply to tables, rows, or blocks

B. Lock conversion can take place implicitly as a query executes

C. Changing the mode of a lock that is already held is called lock escalation

D. Lock conversion is not necessary if an X lock is required and an S or U lock is currently held

DB2 offers compatibility features through the DB2_COMPATIBILITY_VECTOR registry variable or the SQL_COMPAT global variable that improve application compatibility with which of the following? (Choose two.)

A. Oracle

B. Sybase

C. Netezza

D. MongoDB

E. Microsoft SQL Server

Which of the following is TRUE about DB2 OLAP functions?

A. They return scalar values and not array values

B. There are no DB2 monitor elements specific to OLAP functions

C. OLAP functions are not supported in a common table expression (CTE)

D. OLAP aggregation functions are not supported with column organized tables

If you are already connected to a database, what does running the following statement do? Db2 CONNECT

A. Prompts you for the database name or alias

B. Returns information about the current server

C. Prompts you for the authentication information

D. Returns an error, because you provided no database or alias

Given the following statement:

DECLARE GLOBAL TEMPORARY TABLE TEMP1 (

ID INTEGER NOT NULL,

ITEM CHAR(30),

AMOUNT DECIMAL (10,2)

ON COMMIT DELETE ROWS NOT LOGGED;

Which of the following statements are TRUE? (Choose two.)

A. Temp1 table is accessed under schema SESSION

B. Temp1 definition is available for use by other sessions

C. At the end of a transaction, data is deleted automatically

D. Definition of the table Temp1 is stored in database catalog

E. Rows inserted by one session are visible in other sessions

Which of the following SQL statements will update columns C1 to DB2 and C2 to 11 if C3 is 2016?

A. UPDATE t1 c1 = `DB2, c2' = 11

B. UPDATE t1 SET c1 = `DB2, c2' = 11

C. UPDATE t1 c1 = `DB2', c2 = 11 WHERE c3 = 2016

D. UPDATE t1 SET (c1, c2) = (`DB2', 11) = WHERE c3 = 2016