The Secret Of IBM C2090-320 Study Guide

NEW QUESTION 1
Given the following:

ALTER TABLE POLICY_INFO_AET ENABLE ARCHIVE USE POLICY_INFO_ARC: SET SYSIBMADM.MOVE_TO_ARCHIVE = T; ***
Which of the following occurs automatically when rows are deleted from POLICY_INFO_AET?

• A. The deleted rows are moved from POLICY_INFO_AET table to PaOLICY_INFO_ARC table
• B. The logically deleted rows are removed from POLICY_INFO_ARC table during the REORG
• C. The deleted rows are removed from both POLICY_INFO_AET and POLICY_INFO_ARC tables
• D. The deleted rows are moved from POLICY_INFO_AET table to POLICY_INFO_ARC table during theREORG

Answer: D

NEW QUESTION 2
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 3
Given the following table:

Which of the following INSERT statements will be successful?

• A. INSERT INTO EMPSAMP (empno.name, salary, deptno, level,hiretyp
• B. hiredate) VALUES(2,'Mary Smith', 35000.00, 11, 'Associate'. 'existing705-06-2021');
• C. INSERT INTO EMPSAMP ('Mary Smith', 35000.00. 11, 'Associate', 'existing705-06-2021');
• D. INSERT INTO EMPSAMP VALUES(2,'Mary Smith', 35000.00, 11, 'Associate'. 'existing'05-06-2021');
• E. INSERT INTO EMPSAMP (name, salary, deptno, level) VALUES('Mary Smith', 35000.00, 11, 'Associate'):

Answer: B

NEW QUESTION 4
Using the SQL statement DECLARE GLOBAL TEMPORARY TABLE (DGTT) which of the following is TRUE?

• A. A DGTT is only stored in memory
• B. The qualifier for a DGTT is SESSION
• C. The DGTT can be specified in a referential constraint
• D. All privileges associated with the DGTT are stored in the DB2 catalog

Answer: B

NEW QUESTION 5
Which of the following elements is affected by a ROLLBACK statement?

• A. LOB table spaces
• B. Changes in cursor positioning
• C. Acquisition and release of locks
• D. Caching of rolled back statements

Answer: A

NEW QUESTION 6
Which type of index will cause DB2 to store the data in a given physical order?

• A. UNIQUE
• B. PRIMARY
• C. CLUSTER
• D. PARTITIONED

Answer: D

NEW QUESTION 7
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 8
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 9
Which of the following is NOT a valid lock size in DB2?

• A. Table
• B. Partition
• C. Database
• D. Table space

Answer: C

NEW QUESTION 10
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 11
The following warning message is issued when the last available active log data set is 5% full: DSNJ110E - LAST COPY n ACTIVE LOG DATA SET IS nnn PERCENT FULL
What will happen if you ignore this message?

• A. DB2 will shut down if it reaches the 100 % full condition
• B. DB2 will overwrite the oldest archive log without archiving
• C. DB2 will stop any activity that requires log writes if it reaches the 100 % full condition
• D. DB2 will automatically allocate more active log data sets as long as there are still less than 93 of them

Answer: A

NEW QUESTION 12
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 13
Select the SQL statement which will list the employee(empno) and the project numbers(projno) from the EMPACT table that has an employee(empno) whose salary(salary) is in the top three salaries for all employees from the EMP table.
A)

B)

C)

D)

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

Answer: D

NEW QUESTION 14
Which of the following statements about a table with a LOB column is TRUE?

• A. It doesn’t need an auxiliary table if it has an inline LOB
• B. It must also have a ROWID column and an auxiliary table
• C. It doesn’t need a ROWID column (implicit or explicit) if a LOB locator will be used
• D. It must be in a partitioned by growth (PBG) universal table space with a ROWID column

Answer: B

NEW QUESTION 15
Given the tables:

Which SQL statement would join the tables on product number (prod#) and provide a table of what products (product), if any, the parts (part) are used? Limit the result to only parts where suppliers (supplier) do not start with the letter A.

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

Answer: A

NEW QUESTION 16
Which of the following are valid objects created using DDL? (Choose two)

• A. PLAN
• B. PACKAGE
• C. DATABASE
• D. SEQUENCE
• E. COLLECTION

Answer: DE

NEW QUESTION 17
What are two major encoding schemes? (Choose two.)

• A. UNIX
• B. Linux
• C. CCSID
• D. Unicode
• E. EBCDIC

Answer: DE

NEW QUESTION 18
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 19
Which of the following utilities CANNOT be executed from the DB2 utilities panel in DB2I?

• A. COPY
• B. REORG
• C. STOSPACE
• D. BACKUP SYSTEM

Answer: D

NEW QUESTION 20
The following definition for COL1 is given by this statement: CREATE TABLE… (COL1 BINARY (05))
Which of the following statements will successfully insert data into the table?

• A. INSERT INTO… (COL1) VALUES(HEX(‘12345’));
• B. INSERT INTO… (COL1) VALUES(BLOB(‘12345’));
• C. INSERT INTO… (COL1) VALUES(CHAR(‘12345’));
• D. INSERT INTO… (COL1) VALUES(BINARY(‘123456’));

Answer: C

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