NEW QUESTION 1
Which of the following can be defined on the basis of a query?

• A. An alias
• B. A base table
• C. An accelerator-only table
• D. A materialized query table

Answer: D

NEW QUESTION 2
Given the following sequence of events:
* 1) COL1 in SCHEMA.TABLE has a value of 10
* 2) Update COL1 in SCHEMA.TABLE to 20
* 3)SetSAVEPOINTA
* 4) Update COL1 in SCHEMA.TABLE to 30
* 5)SetSAVEPOINTB
After issuing the statement: ROLLBACK TO SAVEPOINT
What will be the value in COL1 of the SCHEMA.TABLE?

• A. 10
• B. 20
• C. 30
• D. An -880 SQLCODE will be issued when the statement is executed

Answer: D

NEW QUESTION 3
Which type of lock is obtained when using a 'WITH UR1 clause on an SQL SELECT statement in a data-sharing environment?

• A. U-lock
• B. S-lock
• C. P-lock
• D. No lock is obtained

Answer: B

NEW QUESTION 4
User1 creates table user1 .customers in a partitioned-by-growth (PBG) table space DB1.TS1
Which of the following operations CANNOT be performed by user1 with implicit owner privileges on the table?

• A. Run the LOAD utility to load data
• B. Create a VIEW on the customers table
• C. Run the REORG utility to reorganize the data
• D. Issue an SQL LOCK TABLE statement against the customers table

Answer: A

NEW QUESTION 5
We want to increase the salary for each employee (EMP) in the department (WORKDEPT) D11 and we want to see the new salaries(SALARY) for each of them. Which single SQL statement will do this?

• A. A
• B. B
• C. C
• D. D

Answer: D

NEW QUESTION 6
Consider the following scenario:
User1 creates table USER1.TABLE1 in DATABASE1 User1 grants ALL PRIVILEGES on TABLE1 to User2 User2 grants ALL PRIVILEGES on TABLE1 to User3 User1 revokes ALL PRIVILEGES on TABLE1 to User2 User3 has DBCTRL authority on DATABASE1
Is User3 able to retrieve rows from TABLE1?

• A. No
• B. Yes
• C. No, if the revoke used INCLUDING DEPENDENT PRIVILEGES
• D. Yes, if User3 was granted DBCTRL prior to the creation of table USER1.TABLE1

Answer: B

NEW QUESTION 7
Which statement about an application-period temporal table is TRUE?

• A. It has SYSTEM_TIME defined
• B. It has an end column defined using WITH DEFAULT NULL
• C. It must have the index clause BUSINESS_TIME WITHOUT OVERLAPS
• D. It has a begin column defined as TIMESTAMP(6) NOT NULL or DATE NOT NULL

Answer: C

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
You want to see all messages that were generated by one specific DB2 subsystem such as log switches and timeout messages. Which job log would show all of these?

• A. MSTR
• B. DBM1
• C. DIST
• D. IRLM

Answer: A

NEW QUESTION 10
Given the following table and index definitions, what type of index will be created?

• A. Non-partitioned index
• B. Non-partitioned secondary index
• C. Data-partitioned secondary index
• D. Data-partitioned partitioning index

Answer: C

NEW QUESTION 11
Assume DELETE triggers exist on the employee table. Which of the following statements will delete all the rows in the employee table and ignore any DELETE triggers?

• A. TRUNCATE employee;
• B. DELETE FROM employee;
• C. DELETE FROM employee WHERE…;
• D. TRUNCATE employee RESTRICT WHEN DELETE TRIGGERS;

Answer: B

NEW QUESTION 12
Given the following SQL statement:
GRANT DELETE ON TABLE customer TO used; Which of the following statements is TRUE?

• A. User1 can only remove the customer table
• B. User1 can only remove rows from the customer table
• C. User1 can remove the customer table and grant the privilege to remove the customer table to other users
• D. User1 can remove rows from the customer table and can grant the privilege to remove rows from the customer table to other users

Answer: C

NEW QUESTION 13
Which of the following utilities or utility control statements require datasets for execution?

• A. LISTDEF
• B. OPTIONS
• C. TEMPLATE
• D. CHECK LOB

Answer: A

NEW QUESTION 14
Which of the following is NOT a valid trigger?

• A. MERGE
• B. INSERT
• C. UPDATE
• D. DELETE

Answer: A

NEW QUESTION 15
How can a user-defined function (UDF) be invoked?

• A. Via an SQL SELECT statement
• B. Via a CALL statement using SPUFI
• C. Directly via a RUN PROGRAM command
• D. Via a CALL statement from within an application program

Answer: B

NEW QUESTION 16
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(CURRENTTIMESTAMP(10))l6l2)t,0')l11l") FROM SYSIBM.SYSDUMMY1;
• D. SELECT TIMESTAMPDIFF(64. CHAR( CURRENT TIMESTAMP - TIMESTAMP('2021-01-01-00.00.00'))) FROM SYSIBM.SYSDUMMY1;

Answer: A

NEW QUESTION 17
An application program is running much longer than expected. The PLAN_TABLE shows the following access path information:
ACCESSTYPE = 'R' PREFETCH = 'S'
What access path does this represent?

• A. IN-list access
• B. Direct row access
• C. Dynamic prefetch access
• D. Table space scan access

Answer: D

NEW QUESTION 18
The metadata for which of the following objects is NOT stored in the DB2 catalog table SYSIBM.SYSTABLES?

• A. A view
• B. A temporal table
• C. A created global temporary table
• D. A declared global temporary table

Answer: B

NEW QUESTION 19
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 20
Which of the following is TRUE for using LOAD on a partition-by-growth table space?

• A. Data can only be loaded at the partition level
• B. Data can only be loaded at the table space level
• C. Data can only be loaded using a clustering index
• D. Data can be loaded at either the table space level or the partition level

Answer: A

NEW QUESTION 21
Which DB2 command displays the status information about DB2 database MYDB and displays the status of all objects in this database?

• A. -DISPLAY DATABASE(MYDB) OVERVIEW
• B. -DISPLAY DATABASE(MYDB) SPACENAM(*)
• C. -DISPLAY DATABASE(MYDB) SPACENAM(') RES
• D. -DISPLAY DATABASE(MYDB) SPACENAMO ACTIVE

Answer: D

NEW QUESTION 22
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 23
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 24
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 25
COL1 is defined as CHAR(05) and contains CHAR(‘12345’). Which of the following expressions returns the digits 12345 for further calculation?

• A. HEX(COL1)
• B. DEC(COL1)
• C. PACK(COL1)
• D. DIGITS(COL1)

Answer: B

NEW QUESTION 26
......