IBM IBM Certifications C2090-320 Questions & Answers

• Question 1:

  Which SQL statement supports an insert or an update in a single SQL statement?

  A. MERGE

  B. UPSERT

  C. UPDATE or INSERT INTO ...

  D. INSERT INTO table ... ON DUPLICATE KEY ...

  Correct Answer: A

  Reference: https://stackoverflow.com/questions/330241/does-db2-have-an-insert-or-update-statement

• Question 2:

  Which statement about an application-period temporal table is TRUE?

  A. It has SYSTEM_TIME defined

  B. It has an end column defined using WITH DEFAULT NULL

  C. It must have the index clause BUSINESS_TIME WITHOUT OVERLAPS

  D. It has a begin column defined as TIMESTAMP(6) NOT NULL or DATE NOT NULL

  Correct Answer: D

  Reference: https://www.ibm.com/developerworks/data/library/techarticle/dm-1410temporal-tables-db2zos/ index.html

• Question 3:

  Which of the following statements will create an application-period temporal table?

  

  A. B. C. D.

  Correct Answer: D

• Question 4:

  Which catalog table contains the information about definition changes for an object that have not yet been applied by the REORG utility?

  A. SYSIBM.SYSCHECKDEP

  B. SYSIBM.SYSPENDINGDDL

  C. SYSIBM.SYSDEPENDENCIES

  D. SYSIBM.SYSPENDINGOBJECTS

  Correct Answer: B

• Question 5:

  Which type of constraint is NOT enforced by DB2 during SQL processing?

  A. Check

  B. Referential

  C. Primary key

  D. Informational referential

  Correct Answer: D

  Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/ com.ibm.db2.luw.admin.dbobj.doc/doc/c0020149.html

• Question 6:

  Which SQL statement will retrieve the department number (WORKDEPT) and the highest salary (SALARY) for each department in a table named EMPLOYEE and arrange the result set in ascending order by department?

  A. SELECT workdept, salary FROM employee GROUP BY workdept ORDER BY workdept;

  B. SELECT workdept, MAX(salary) FROM employee ORDER BY workdept GROUP BY 2;

  C. SELECT workdept, MAX(salary) FROM employee ORDER BY workdept;

  D. SELECT workdept, MAX(salary) FROM employee GROUP BY workdept ORDER BY 1;

  Correct Answer: B

  Reference: https://www.ibm.com/support/knowledgecenter/SSPT3X_4.2.0/ com.ibm.swg.im.infosphere.biginsights.commsql.doc/doc/r0059224.html

• Question 7:

  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

  Correct Answer: C

  Reference: https://books.google.com.pk/books?id=ravEAgAAQBAJandpg=PA301andlpg=PA301anddq=db2 +automatically+maintains+historical+versions+of+data+over+several +yearsandsource=blandots=eMUVegTHYYandsig=ACfU3U0s1wYpk3CCUdmUpXWXB4TV9OYfdwandhl=enandsa= Xandredir_esc=y#v=onepageandq=db2%20automatically%20maintains%20historical%20versions%20of% 20data%20over%20several% 20yearsandf=false

• Question 8:

  Given the following sequence of events:

  1) COL1 in SCHEMA.TABLE has a value of 10 2)Update COL1 in SCHEMA.TABLE to 20 3) Set SAVEPOINT A 4) Update COL1 in SCHEMA.TABLE to 30 5) Set SAVEPOINT B After issuing the statement: ROLLBACK TO SAVEPOINT

  What will be the value in COL1 of the SCHEMA.TABLE?

  A. 10

  B. 20

  C. 30

  D. An -880 SQLCODE will be issued when the statement is executed

  Correct Answer: C

• Question 9:

  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;

  Correct Answer: D

• Question 10:

  Which type of lock is obtained when using a `WITH UR' clause on an SQL SELECT statement in a data-sharing environment?

  A. U-lock

  B. S-lock

  C. P-lock

  D. No lock is obtained

  Correct Answer: D