NEW QUESTION 1
Which authorizations can change the value of the subsystem parameter SYSADM using the –SET SYSPARM command? (Choose two.)

• A. SYSADM
• B. SECADM
• C. SYSCTRL
• D. Installation SYSADM
• E. Installation SYSOPR

Answer: DE

NEW QUESTION 2
When the following SQL statement is executed, the user SMITH is able to manage all objects in the DB2 subsystem except for which of the following?
GRANT DBADM ON SYSTEM TO smith;

• A. Tables
• B. Traces
• C. Sequences
• D. Security Objects

Answer: A

NEW QUESTION 3
Which characteristic best describes a created global temporary table?

• A. Inserts are logged
• B. Rows are locked when they exist in the table
• C. They have permanent, shareable descriptions
• D. Access to rows can be shared across multiple processes

Answer: C

NEW QUESTION 4
Which of the following are contained in the PLAN_TABLE? (Choose two.)

• A. RUNSTATS recommendations
• B. Access to LOB values, which are stored separately from the base table
• C. Access to parent or dependent tables needed to enforce referential constraints
• D. Whether an index access or table space scan is used for each access to a table
• E. The join methods and types that are used, and the order in which DB2 joins the tables

Answer: DE

NEW QUESTION 5
Which of the following statements about a system-period temporal table is TRUE?

• A. Rows can be archived into a history table which is always read only
• B. Every row has a pair of timestamps which is set and managed by the application
• C. AN SQL query for auditing can be run against current data or history data, but not both
• D. The table contains only the current active rows

Answer: B

Explanation:
In a system-period temporal table, every row has a pair of timestamps, which are set by the DB2 system and recorded in the begin_column and end_column columns. DB2 uses the SYSTEM_TIME period to audit the data changes. It provides the application with the database managed row begin and row end timestamp maintenance, providing non-overlapping time periods with no gaps in time.

NEW QUESTION 6
When you add an XML column to a table by using an ALTER TABLE statement, which of the following are TRUE? (Choose two)

• A. DB2 implicitly creates an XML table space
• B. DB2 implicitly creates an AUX table to store the XML data
• C. If this is the first XML column you created for the table, DB2 implicitly creates a unique index on the XML column
• D. If this is the first XML column you created for the table, DB2 implicitly creates a ROWID column on the base table
• E. If this is the first XML column you created for the table, DB2 implicitly creates a BIGINT node ID column on the base table

Answer: DE

NEW QUESTION 7
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;

Answer: B

NEW QUESTION 8
A view is considered read only if which of the following is TRUE?

• A. The first EROM clause identifies only one table
• B. The outer fullselect contains an ORDER BY clause
• C. The first SELECT clause specifies the DISTINCT option
• D. The fullselect includes a column of a base table that is defined as implicitly hidden

Answer: B

NEW QUESTION 9
A clone table can only be created in which of the following?

• A. A simple table space
• B. A universal table space
• C. An auxiliary table space
• D. A classic segmented table space

Answer: B

NEW QUESTION 10
Which of the following is a characteristic of a stand-alone utility?

• A. It can only be executed using JCL
• B. It can only be executed by a user with SYSADM
• C. It can only be executed if no other utilities are running
• D. It can only be executed in a non-data sharing environment

Answer: A

NEW QUESTION 11
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 ....

Answer: D

NEW QUESTION 12
If a scrollable cursor is INSENSITIVE, which of the following applies?

• A. The cursor is read only
• B. WITH ROWSET POSITIONING is not allowed
• C. The select-statement must not contain an SQL data change statement
• D. The cursor has sensitivity to changes that are made to the database after the result table is materialized

Answer: A

NEW QUESTION 13
In a unique referential constraint, which type of key does a foreign key reference?

• A. Child key
• B. Collation key
• C. Parent key
• D. Secondary key

Answer: C

NEW QUESTION 14
Which catalog table provides information about the validity of a bound package?

• A. SYSIBM.SYSPACKLIST
• B. SYSIBM.SYSPACKAGE
• C. SYSIBM.SYSPACKDEP
• D. SYSIBM.SYSPACKAUTH

Answer: A

NEW QUESTION 15
Which component of the program preparation produces a DBRM (Database request Module)?

• A. Compiler
• B. Linkage Editor
• C. Bind command
• D. Precompiler/Coprocessor

Answer: D

NEW QUESTION 16
Which privilege will user smith be able to grant to another user on table emp when the following SQL is executed?
GRANT ALL ON TABLE emp TO smith WITH GRANT OPTION;

• A. BIND
• B. REORG
• C. TRIGGER
• D. EXECUTE

Answer: D

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

• A. Check
• B. Referential
• C. Primary key
• D. Informational referential

Answer: D

NEW QUESTION 18
Which SQL statement can be used to provide diagnostic information about the last SQL statement that was executed?

• A. GET DIAGNOSTICS
• B. DESCRIBE OUTPUT
• C. DECLARE STATEMENT
• D. SET CURRENT DEBUG MODE

Answer: A

NEW QUESTION 19
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

Answer: C

NEW QUESTION 20
Which command displays the DB2 catalog level and migration mode?

• A. DSNC DISPLAY
• B. MODIFY irlmproc,STATUS
• C. DISPLAY GROUP DETAIL
• D. DISPLAY DATABASE (DSNDB06)

Answer: B

NEW QUESTION 21
Which of the following statements concerning a bi-temporal table is TRUE?

• A. It provides business history, but is not useful for audit purposes
• B. It is defined with both an application period and a system period
• C. It is useful for audit purposes, but is not useful for business history
• D. It contains data reflecting two different times – current time and one previous time

Answer: B

NEW QUESTION 22
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

Answer: AC

NEW QUESTION 23
......