DB2 11.1 Fundamentals for LUW v1.0
Question 1
Given the following statement:
DECLARE GLOBAL TEMPORARY TABLE TEMP1 (
ID INTEGER NOT NULL,
ITEM CHAR(30),
AMOUNT DECIMAL (10,2)
ON COMMIT DELETE ROWS NOT LOGGED;
Which of the following statements are TRUE? (Choose two.)
- A. Temp1 table is accessed under schema SESSION
- B. Temp1 definition is available for use by other sessions
- C. At the end of a transaction, data is deleted automatically
- D. Definition of the table Temp1 is stored in database catalog
- E. Rows inserted by one session are visible in other sessions
Answer : DE
Question 2
Which method for restricting data access relies on security labels and security policies to control what data a user can access?
- A. Authentication
- B. Authorities and privileges
- C. Label-based access control
- D. Row and column access control
Answer : C
Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.sec.doc/doc/c0021114.html
Question 3
Which of the following compatibility features is always active, regardless of the settings for the DB2_COMPATIBILITY_VECTOR registry variable or the
SQL_COMPAT global variable?
- A. Implicit casting
- B. Double-dot notation
- C. Use of bitmap indexes
- D. Selecting from the DUAL table
Answer : D
Reference: https://www.ibm.com/support/knowledgecenter/en/SSCRJT_5.0.3/com.ibm.swg.im.bigsql.doc/doc/c_compat_oracle.html
Question 4
Which of the following is fundamental to BLU acceleration?
- A. pureScale
- B. Column organized tables
- C. Multidimensional clustering
- D. Purchasing new hardware that is compatible with BLU acceleration and compression
Answer : B
Reference: http://www.redbooks.ibm.com/abstracts/tips1204.html?Open
Question 5
A multi-partition database environment using BLU acceleration is designed to meet which of the following requirements? (Choose two.)
- A. Easy scalability
- B. Management of very large data sets
- C. Continuously available dataset solution
- D. Increased throughput of many concurrent short running queries
- E. Faster response time of long-running or complex queries with I/O spread across many computers
Answer : BE
Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.licensing.doc/doc/c0054843.html
Question 6
Which DB2 product required to allow connections from DB2 LUW to DB2 on z/OS?
- A. DB2 Connect
- B. DB2 Runtime Client
- C. DB2 Data Server Client
- D. DB2 Enterprise Server Edition
Answer : A
Reference: http://www.db2dean.com/Previous/DB2Connect.html
Question 7
DB2 offers compatibility features through the DB2_COMPATIBILITY_VECTOR registry variable or the SQL_COMPAT global variable that improve application compatibility with which of the following? (Choose two.)
- A. Oracle
- B. Sybase
- C. Netezza
- D. MongoDB
- E. Microsoft SQL Server
Answer : AB
Reference: https://www.ibm.com/support/knowledgecenter/mk/SSEPGG_11.1.0/com.ibm.db2.luw.apdv.porting.doc/doc/r0052867.html
Question 8
What is the primary use case for creating an index with the RANDOM option on a table?
- A. Maximize performance with OLTP random key queries
- B. Maximize performance when inserting into an expression based index containing the RAND() function
- C. Minimize contention due to ascending key or descending key inserts, especially in a pureScale environment
- D. Minimize hashing skew on a table in a multiple database partition environment, especially with column organized tables C
Answer : Explanation
Question 9
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 : CE
Reference: https://www.google.com/url?
sa=t&rct=j&q=&esrc=s&source=web&cd=2&cad=rja&uact=8&ved=2ahUKEwiWvqq07PncAhVIqxoKHVZdCqAQFjABegQICRAC&url=https%3A%2F%
2Fwww.ibm.com%2Fdeveloperworks%2Fcommunity%2Ffiles%2Fform%2Fanonymous%2Fapi%2Flibrary%2F89c92396-429f-440a-a682-b0139921a14f%
2Fdocument%2Fd01b0ccd-cbd9-43f8-9347-033750ea533c%2Fmedia%2FSTLDUG1606-LUW-3-DB2%252011.1%2520LUW%2520Overview-
Cialini.pdf&usg=AOvVaw1G55BpetB6pwzWNYZMgOqO
Question 10
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 : B
Question 11
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 : C
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 A
Answer : Explanation
Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/perf/src/tpc/db2z_recommend4concurrency.html
Question 13
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
Question 14
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
Reference: https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0061623.html
Question 15
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
Reference: https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&ved=2ahUKEwjznbPf5_vcAhWhApoKHV-
ZBpoQFjAAegQIAhAC&url=https%3A%2F%2Fwww.ibm.com%2Fdeveloperworks%2Fcommunity%2Ffiles%2Fform%2Fanonymous%2Fapi%2Flibrary%
2F260f3b12-8adf-46ab-931a-4719de7a812a%2Fdocument%2F902fbb89-41be-4cb5-bb29-d301294a76b3%2Fmedia&usg=AOvVaw1VsAkxdi9s8wQ33oSXjKzP