IBM C2090-616 Online Practice Questions and Exam Preparation

IBM C2090-616 Online Questions & Answers

• Question 1:

  Which locking event occurs when two applications lock rows that are needed by the other, such that neither application can continue executing?

  A. Deadlock
  B. Lock timeout
  C. Lock escalation
  D. Lock conversion
  A. Deadlock

• Question 2:

  Consider the following SQL statement:

  SELECT workdept, empno, lastname, salary,

  RANK() OVER (PARTITION BY workdept ORDER BY salary) AS rank_salary

  FROM employee

  ORDER BY workdept, lastname

  What does the RANK() OVER (PARTITION BY workdept ORDER BY salary) clause do?

  A. It ranks the rows in each WORKDEPT by SALARY.
  B. It groups all rows by WORKDEPT, then ranks all rows in the complete result set by SALARY.
  C. It generates a blank output line after each group of rows with the same WORKDEPT, and ranks these groups by SALARY.
  D. It tells the optimizer to physically partition the EMPLOYEE table by the WORKDEPT column for runtime execution, and ranks each WORKDEPT by SALARY.
  B. It groups all rows by WORKDEPT, then ranks all rows in the complete result set by SALARY.

• Question 3:

  The DDL statement is used to create table T1:

  CREATE TABLEt1 (

  c1 INTEGER,

  c2 INTEGER NOT NULL,

  c1 DECIMAL(11,2),

  c4 TIMESTAMP WITH DEFAULT CURRENT TIMESTAMP

  )

  Which of the following INSERT statements will execute successfully?

  A. INSERT INTO t1 VALUES (100)
  B. INSERT INTO t1 (c1, c2) VALUES (100)
  C. INSERT INTO t1 (c1, c3) VALUES (`100', `100.00')
  D. INSERT INTO t1 (c2, c3) VALUES (100, 100.00), (101, 101)
  B. INSERT INTO t1 (c1, c2) VALUES (100)

• Question 4:

  Which of the following DB2 objects in an efficient way of representing data without the need to maintain it and requires no permanent storage?

  A. View
  B. Alias
  C. Table
  D. Schema
  A. View

• Question 5:

  Which of the following SQL statements will remove all rows from the table T1? (Choose two.)

  A. DELETE FROM t1
  B. DELETE* FROM t1
  C. DELETE* TABLE t1
  D. TRUNCATE TABLE t1 IMMEDIATE
  E. TRUNCATE TABLE t1
  A. DELETE FROM t1
  B. DELETE* FROM t1

• Question 6:

  Which DB2 object is associated with PREVIOUS VALUE and NEXT VALUE expressions?

  A. Sequence
  B. Row Identifier
  C. Identity Column
  D. Insert time clustering table
  A. Sequence

• Question 7:

  What is the maximum length allowed for a VARCHAR2 data type when the VARCHAR2_COMPAT database configuration parameter is set to ON?

  A. 32672 bytes
  B. 65344 bytes
  C. 98016 bytes
  D. 130688 bytes
  A. 32672 bytes

• Question 8:

  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
  B. The CREATE SOURCED PROCEDURE statement defines a procedure that is based on another procedure in a federated database

• Question 9:

  Which of the following is TRUE regarding user switching in a trusted context?

  A. You cannot switch user id in a trusted context.
  B. You can issue a switch-user request within a transaction.
  C. The new user must have the connect-privilege on the database for it to succeed.
  D. No password is ever required for the new user as an authentication is established in the trusted connection.
  D. No password is ever required for the new user as an authentication is established in the trusted connection.

• Question 10:

  Which operation typically requires a Share (S) lock?

  A. INSERT
  B. SELECT
  C. DELETE
  D. UPDATE
  D. UPDATE