C2090-320 Online Practice Questions and Answers

Which package level privileges does the user smith have when the following statement is executed? GRANT ALL ON PACKAGE clct1.pkg1 TO smith;

A. GRANT, COPY, BIND, RUN

B. BIND, EXECUTE, COPY, RUN

C. CREATE, COPY, RUN, EXECUTE

D. COPY, BINDADD, SQL, EXECUTE

You want to see all messages that were generated by one specific DB2 subsystem such as log switches and timeout messages. Which job log would show all of these?

A. MSTR

B. DBM1

C. DIST

D. IRLM

Which SQL statement can be used to provide diagnostic information about the last SQL statement that was executed?

A. GET DIAGNOSTICS

B. DESCRIBE OUTPUT

C. DECLARE STATEMENT

D. SET CURRENT DEBUG MODE

A summary table can be easily maintained using which of the following?

A. An auxiliary table

B. A partitioned table

C. A bi-temporal table

D. A materialized query table

Which of the following SELECT statements returns a different data value than the other SQL statements?

A. SELECT MONTH (CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1;

B. SELECT EXTRACT(MONTH FROM CURRENT TIMESTAMP) FROM SYSIBM.SYSDUMMY1;

C. SELECT LPAD(LTRIM(SUBSTR(CHAR(CURRENT TIMESTAMP(10)),6,2),'0'),11,'') FROM SYSIBM.SYSDUMMY1;

D. SELECT TIMESTAMPDIFF(64, CHAR(CURRENT TIMESTAMP ?TIMESTAMP(`2016-01-0100.00.00'))) FROM SYSIBM.SYSDUMMY1;

Which isolation level is described by the following?

The ISOLATION(..) option allows maximum concurrency with data integrity. Under the ISOLATION(..) option, a transaction holds locks only on its uncommitted changes and on the current row of each of its cursors.

A. CS

B. UR

C. RR

D. RS

Given the following table:

Which of the following INSERT statements will be successful?

A. INSERT INTO EMPSAMP (empno, name, salary, deptno, level, hiretype, hiredate) VALUES(2,'Mary Smith', 35000.00, 11, `Associate', `existing','05-06-2016');

B. INSERT INTO EMPSAMP (`Mary Smith', 35000.00, 11, `Associate', `existing','05-06-2016'); C. INSERT INTO EMPSAMP VALUES(2,'Mary Smith', 35000.00, 11, `Associate', `existing','05-06-2016');

D. INSERT INTO EMPSAMP (name, salary, deptno, level) VALUES(`Mary Smith', 35000.00, 11, `Associate');

Which of the following is NOT a valid lock size in DB2?

A. Table

B. Partition

C. Database

D. Table space

When you add an XML column to a table by using an ALTER TABLE statement, which of the following are TRUE? (Choose two.)

A. DB2 implicitly creates an XML table space

B. DB2 implicitly creates an AUX table to store the XML data

C. If this is the first XML column you created for the table, DB2 implicitly creates a unique index on the XML column

D. If this is the first XML column you created for the table, DB2 implicitly creates a ROWID column on the base table

E. If this is the first XML column you created for the table, DB2 implicitly creates a BIGINT node ID column on the base table

When the following SQL statement is executed, user smith will NOT be able to do which of the following?

REVOKE USE OF STOGROUP stogr1 FROM smith;

A. CREATE TRIGGER ...;

B. CREATE TABLE ... IN db1.ts1;

C. CREATE DATABASE ... STOGROUP stogr1;

D. CREATE TABLESPACE ... USING STOGROUP stogr1;