C2090-320 Online Practice Questions and Answers

Your current authorization ID is DBA. Only one of your secondary authorization IDs, DB2, has the privilege to create an index DB2.AAA on a table. The appropriate SQL statement starts with "CREATE INDEX AAA ON...". Which of the following statements must precede the CREATE statement so that you can create the index?

A. SET CURRENT SQLID = `DB2';

B. SET CURRENT RULES = `DB2';

C. No preceding statement is needed

D. SET CURRENT SCHEMA = `DB2';

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 of the following statements about a table with a LOB column is TRUE?

A. It doesn't need an auxiliary table if it has an inline LOB

B. It must also have a ROWID column and an auxiliary table

C. It doesn't need a ROWID column (implicit or explicit) if a LOB locator will be used

D. It must be in a partitioned by growth (PBG) universal table space with a ROWID column

Which catalog table provides information about the validity of a bound package?

A. SYSIBM.SYSPACKLIST

B. SYSIBM.SYSPACKAGE

C. SYSIBM.SYSPACKDEP

D. SYSIBM.SYSPACKAUTH

Which of the following are advantages of DB2 for z/OS data sharing over a standalone DB2 subsystem implementation? (Choose two.)

A. Reduced active logs

B. Reduced storage use

C. Higher transaction rates

D. Reduced lock contention

E. Increased data availability

Which statement is TRUE regarding catalog table SYSIBM.SYSROUTINES?

A. Contains scheduled tasks for the administrative scheduler

B. Contains information about UDFs, BIFs and stored procedures

C. Contains information about bound packages

D. Contains information about UDFs and stored procedures

Which command displays the DB2 catalog level and migration mode?

A. DSNC DISPLAY

B. MODIFY irImproc, STATUS

C. -DISPLAY GROUP DETAIL

D. -DISPLAY DATABASE (DSNDB06)

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');

An external stored procedure is invoked using a CALL :hv (host variable). Which special register identifies the location of the procedure?

A. CURRENT PATH

B. CURRENT SCHEMA

C. CURRENT PACKAGESET

D. CURRENT ROUTINE VERSION

Select the SQL statement which will list the employee(empno) and the project numbers(projno) from the EMPACT table that has an employee(empno) whose salary(salary) is in the top three salaries for all employees from the EMP table.

A. SELECT empno, projno FROM empact ep WHERE ep.empno IN (SELECT em.empno FROM emp em ORDER BY salary DESC) FETCH FIRST 3 ROWS ONLY;

B. SELECT empno, projno FROM empact WHERE empno IN (SELECT empno FROM emp FETCH FIRST 3 ROWS ONLY);

C. SELECT ep.empno, projno FROM empact ep WHERE ep.empno IN (SELECT em.empno FROM emp em ORDER BY salary FETCH FIRST 3 ROWS ONLY);

D. SELECT ep.empno, projno FROM empact ep WHERE ep.empno IN (SELECT em.empno FROM emp em ORDER BY salary DESC FETCH FIRST 3 ROWS ONLY);

Which two of the following functions are synonyms of one another? (Choose two.)

A. CLOB

B. VALUE

C. IFNULL

D. DBCLOB

E. COALESCE

Which protocol provides applications the ability to connect to a server at another location and execute packages that have been previously bound at that server?

A. CLI

B. JDBC

C. DRDA

D. ODBC

Which of the following statements is TRUE when issuing an SQL ROLLBACK (without SAVEPOINT) statement?

A. All held LOB locators are retained

B. All locks that are implicitly acquired during the unit of recovery are retained

C. All rows and all logical work files of every created temporary table of the application process are deleted

D. All cursors are closed, all prepared statements are retained, and any cursors that are associated with the prepared statements are invalidated

You need to add the employee picture to the employee table. Which of the following is the appropriate built-in data type for storing images?

A. BLOB

B. CLOB

C. DBCLOB

D. VARGRAPHIC

Which of the following automatically maintains historical versions of data over several years?

A. A partitioned-by-range table

B. A partitioned-by-growth table

C. A system-period temporal table

D. An application-period temporal table