IBM c2090-616 practice test

DB2 11.1 Fundamentals for LUW Exam

Last exam update: Nov 14 ,2024
Page 1 out of 4. Viewing questions 1-15 out of 63

Question 1

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
Answer:

A

User Votes:
A
50%
B
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

Question 2

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
Answer:

A

User Votes:
A
50%
B
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

Question 3

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
Answer:

B

User Votes:
A
50%
B
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

Question 4

Which of the following features can provide significant benefits in storage savings, performance of
analytic workloads, and time to value?

  • A. pureScale
  • B. auto reorg
  • C. BLU acceleration
  • D. Self tuning memory manager
Answer:

C

User Votes:
A
50%
B
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

Question 5

Which of the following isolation levels most reduces concurrency and is most likely to cause lock
escalations?

  • A. Read Stability
  • B. Cursor Stability
  • C. Repeatable Read
  • D. Uncommitted Read
Answer:

C

User Votes:
A
50%
B
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

Question 6

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
Answer:

D

User Votes:
A
50%
B
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

Question 7

The current user wants to produce a list of all user privileges she has been granted. Which table or
view can be queried to produce the desired result?

  • A. SYSCAT.DBAUTH
  • B. SYSIBMADM.GRANTS
  • C. SYSIBMADM.PRIVILEGES
  • D. SYSCAT.USER_TAB_PRIVS
Answer:

C

User Votes:
A
50%
B
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

Question 8

Which of the following is TRUE about DB2 OLAP functions?

  • A. They return scalar values and not array values
  • B. There are no DB2 monitor elements specific to OLAP functions
  • C. OLAP functions are not supported in a common table expression (CTE)
  • D. OLAP aggregation functions are not supported with column organized tables
Answer:

C

User Votes:
A
50%
B
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

Question 9

The application owners say they need to be able to query a table and previous versions of that table.
Which type of table is appropriate to satisfy this requirement?

  • A. Shadow Table
  • B. Temporal Table
  • C. Range-Partitioned Table
  • D. Multidimensional Clustered Table
Answer:

B

User Votes:
A
50%
B
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

Question 10

A portion of your database tables are used for OLTP-type processing and another portion are used for
more analytical queries. There is a small subset of tables that are used in both of these usage
patterns. What could you use to derive the analytic improvements offered by BLU technology in such
an environment?

  • A. Shadow tables
  • B. Range-partitioned tables
  • C. Materialized query tables
  • D. Multidimensional clustering tables
Answer:

A

User Votes:
A
50%
B
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

Question 11

Consider the following query and the resulting set of data:
SELECT empno, lastname FROM emp WHERE empno BETWEEN 000020 and 000070
Result:

If the query below is executed, how many rows will be returned?
SELECT empno FROM emp WHERE empno > 000020 AND empno 000070

  • A. 0
  • B. 1
  • C. 3
  • D. 5
Answer:

C

User Votes:
A
50%
B
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

Question 12

If the LOCKSIZE for a table is changed from TABLE to ROW, which of the following is TRUE?

  • A. Concurrency may be improved
  • B. Lock escalation will be prevented
  • C. Query performance will be reduced
  • D. The number of locks for the same workload will be reduced
Answer:

A

User Votes:
A
50%
B
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

Question 13

Which of the following SQL statements will update columns C1 to DB2 and C2 to 11 if C3 is 2016?

  • A. UPDATE t1 c1 = ‘DB2, c2’ = 11
  • B. UPDATE t1 SET c1 = ‘DB2, c2’ = 11
  • C. UPDATE t1 c1 = ‘DB2’, c2 = 11 WHERE c3 = 2016
  • D. UPDATE t1 SET (c1, c2) = (‘DB2’, 11) = WHERE c3 = 2016
Answer:

D

User Votes:
A
50%
B
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

Question 14

: 40
Which of the following set operators can be used to find all rows that are common in 2 queries?

  • A. UNION
  • B. UNION ALL
  • C. EXCEPT ALL
  • D. INTERSECT ALL
Answer:

D

User Votes:
A
50%
B
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

Question 15

Which optional NPS compatibility features will be activated by executing the following? (Choose
two.)
SET SQL_COMPAT=NPS

  • A. YEARSBETWEEN
  • B. BINARY and VARBINARY data types
  • C. Double-dot notation to specify a database object
  • D. OFFSET x ROWS FETCH FIRST y ROWS ONLY
  • E. TRANSLATE (char-string-exp, from-string-exp, to-string-exp)
Answer:

C,E

User Votes:
A
50%
B
50%
C
50%
D
50%
E
50%
Discussions
vote your answer:
A
B
C
D
E
0 / 1000
To page 2