IBM IBM Certifications C2090-320 Questions & Answers

• Question 41:

  For a partition-by-range table, which data types can be used to define the key columns that are used to partition the data? (Choose two.)

  A. XML

  B. BINARY

  C. DECIMAL

  D. VARCHAR

  E. DECFLOAT

  Correct Answer: CD

• Question 42:

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

  Correct Answer: A

• Question 43:

  To ensure only positive values are entered into a column, you add a constraint on the SALARY column of a previously populated table. If DB2 cannot guarantee the existing values in the column are positive, it will place the table space or partition that contains the table into which of the following states?

  A. CHECK PENDING

  B. REORG PENDING

  C. REBUILD PENDING

  D. ADVISORY REORG PENDING

  Correct Answer: A

  Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/ugref/src/tpc/ db2z_checkpendingstatus.html

• Question 44:

  DB2 native SQL procedures execute in which DB2 address space?

  A. IRLM

  B. DIST

  C. DBM1

  D. WLM stored procedure

  Correct Answer: C

  Reference: http://robertsdb2blog.blogspot.com/2012/12/db2-for-zos-clearing-up-native-sql.html

• Question 45:

  Using the SQL statement DECLARE GLOBAL TEMPORARY TABLE (DGTT) which of the following is TRUE?

  A. A DGTT is only stored in memory

  B. The qualifier for a DGTT is SESSION

  C. The DGTT can be specified in a referential constraint D. All privileges associated with the DGTT are stored in the DB2 catalog

  Correct Answer: B

  Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/sqlref/src/tpc/ db2z_sql_declareglobaltemptable.html

• Question 46:

  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

  Correct Answer: A

  Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/perf/src/tpc/ db2z_isocs.html

• Question 47:

  Why would an SQL statement receive an SQLCODE of +100? (Choose two.)

  A. The result of a SELECT INTO statement was an empty table

  B. A rowset FETCH statement encountered one or more error conditions

  C. The statement references an object that is not defined in the DB2 subsystem

  D. An error occurred during the insertion of a row into an optional explanation table

  E. A FETCH statement was executed when the cursor was positioned after the last row of the result table

  Correct Answer: AB

  Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/codes/src/tpc/p100.html

• Question 48:

  What is the purpose of an auxiliary table?

  A. To hold the data for a column that is defined in a base table

  B. To hold only XML data

  C. To hold data temporarily

  D. To store rows that are deleted from another table

  Correct Answer: A

  Reference: https://help.sap.com/doc/saphelp_pd1655_data/16.5.5/en-US/c7/ cbf3e96e1b1014a434a1a425666017/content.htm?no_cache=true

• Question 49:

  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)

  Correct Answer: C

• Question 50:

  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;

  Correct Answer: D