DB2

Given the following DDL statement: CREATE TABLE sales (item CHAR(20)); If a DBA wishes to
increase the size of the ITEM column, which SQL statement needs to be used?

Which DB2 object can be used to improve the execution performance of qualified SELECT statements?

When is it appropriate to use a sequence?

Which clause should be included in a ALTER TABLE statement to update a table definition with a new column?

Which object is a stored procedure defined into?

Which action needs to be performed in order to complete the definition of an application-period temporal table?

What functionality allows users to perform a UNION operation between a DB2 table and an Oracle view?

You have a business need to query DB2 10 and DB2 9 databases and you want to write an application that can run on most platforms unchanged. Which interface would you use to achieve your goal?

Which DB2 object is used to provide a logical grouping of other database objects?

What are two valid objects that can be created in DB2? (Choose two.)

Which type of temporal table can be used to store only time-sensitive data?

When an index is created for a table, where is the metadata for that index stored?

Which product is used to customize execution environments for the purpose of controlling system resources so that one department or service class does not overwhelm the system?

What type of mechanism is a simple token value that is used to refer to a much bigger large object (LOB)?

What type of large object (LOB) is used to store LOB data together with the formatted rows on data pages, instead of in a separate LOB storage object?

What is the purpose of the Query Tuner?

Which tool allows users to connect to a DB2 database using a wizard?

Which two activities indicate the need for a data warehouse? (Choose two.)

What is the primary function of an Online Transaction Processing (OLTP) workload?

Which isolation level offers the greatest protection of data but provides the least amount of concurrency?

What factor influences lock escalation?

Which type of lock allows the lock owner and all concurrent applications to read, but not update, the locked data?

Application APP_A is performing updates to table TAB1 using the cursor stability (CS) isolation level. If application APP_B wants to retrieve all rows from table TAB1 without waiting for application APP_A to finish making updates, what isolation level must application APP_B use?

Which statement will prevent concurrent application processes from performing anything other than read-only operations against a table named TAB1?

When is an INTENT EXCLUSIVE (IX) lock required?

What is the act of exchanging one lock an application holds on a resource for a more restrictive lock on the same resource known as?

What isolation level prevents dirty reads, nonrepeatable reads, and phantoms?

Which operation normally does NOT require an exclusive lock?

When a COMMIT statement is executed, what happens?

Which command is used to back out a subset of database changes that have been made within a unit of work?

An SQL function designed to convert temperatures from Fahrenheit to Celsius was created as follows:
 
 CREATE FUNCTION conv_temp(IN temp_f FLOAT)
  RETURNS INTEGER
  NO EXTERNAL ACTOIN
  SPECIFIC convert_ftoc
  RETURN INT((temp_f-32)/1.8)
 
How can this function be used to convert average temperature (AVG_TEMP) data stored in a table called CLIMATE_INFO?

If a table named MY_TAB contains 100 rows and the following statement is executed:
 
 DELETE FROM 
  (SELECT * FROM my_tab
   ORDER BY col1 DESC
   FETCH FIRST 5 ROWS ONLY) AS tmp;
 
What will happen?

Given an EMPLOYEES table and a SALES table, a user wants to produce a list of all employees and their associated revenue, even if no revenue exists. Which SQL statement will produce the desired list?

Which two operations are allowed in the body of an SQL scalar user-defined function? (Choose two.)

Which command will delete all rows from a table without generating log records?

Which function can be used to obtain values from XML documents that are to be inserted into one or more tables?

User USER1 wants to retrieve records from a table named EMPLOYEE that satisfy at least one of the following criteria:
-The employee's hire date (HIREDATE) is before 1999 and the employee's salary (SALARY) is less than $40,000.00 a year. 
-The employee has attended university
Which SQL statement will accomplish this?

Which SQL statement will retrieve the employee number (EMPNO), hire date (HIREDATE), and salary (SALARY) for each employee from a table named EMPLOYEE who was hired before 1998 and earns a salary of less than $35,000.00 per year?

If the following result set is desired:
 
 LASTNAME    FIRSTNAME   SALARY      JOB
 --------    ---------   ------      ---
 HAAS        CHRISTINE   152750.00   PRES
 KWAN        SALLY       98250.00    MANAGER
 PULASKI     EVA         96170.00    MANAGER
 THOMPSON    MICHAEL     94250.00    MANAGER
 HENDERSON   EILEEN      89750.00    MANAGER
 
Which SQL statement must be executed?

Which statement about INSERT operations is true?

Which statement about application-period temporal tables is true?

Which SQL statement should be used to select the minimum and maximum salaries (SALARY), by job code (JOB), from a table EMPLOYEE?

In a trusted context environment, if a switch request is made with an authorization ID that is not allowed on a trusted connection, what state is the connection placed in?

If a user has been granted SYSCTRL authority, which activities can they perform?

What is the purpose of a role?

Which SQL statement will give user USER1 the ability to assign a comment to a sequence named MYSEQ?

A column mask that is to be used for row and column access control (RCAC) was created with the DISABLE option specified. What must be done if this mask is to be used to restrict access to data stored
in a table named EMPLOYEE?

User USER1 holds both DBADM and SECADM authority and you want to separate database
administration activities from security administration tasks. What authority must you have in order to revoke SECADM authority from user USER1 and assign it to someone else?

A table named EMPLOYEE was created as follows:
 
 CREATE TABLE employee
  (emp_id INTEGER NOT NULL PRIMARY KEY,
   f_name VARCHAR(30),
   l_name VARCHAR(40),
   ssn    CHAR(9) NOT NULL)
 
Which action will prevent unauthorized users from accessing SSN data?

Which SQL statement will allow user USER1 to create a view on a table named EMPLOYEE?

A new user named USER1 needs to retrieve information from a database named MYDB. What
authority must be granted to user USER1?

Which privilege is required to invoke a user defined function?

Which statement is true about an index that is used to support a UNIQUE constraint?

Which statement regarding triggers is true?

A table named STATES was created as follows:
 
 CREATE TABLE states
  (state_cd     CHAR(2) NOT NULL,
   state_desc   CHAR(20));
 CREATE UNIQUE INDEX indx1 ON states (state_cd);
 
What will happen?

When an application using a temporary table terminates or disconnects from the database
unexpectedly, what happens to the temporary table and any data stored in it?

When a user-defined function (UDF) is created and no schema is specified, what schema is used to store the UDF?

Which statement about triggers is true?

Which two statements are true about foreign key constraints? (Choose two.)

What is the primary purpose of a view?

By default, a column defined as a TIMESTAMP includes which attributes?

Which is NOT a valid reason for creating an index?

Which type of constraint can be used to ensure that an INTEGER column in a table will never be assigned more than one record that contains a NULL value?

Which statement is true regarding constraints?

By default, where is the data stored for a column defined with an XML data type?

Which statement about BEFORE triggers is FALSE?

What is used in conjunction with a foreign key to define a relationship between two tables?

Which case will require a sequence to be dropped and recreated instead of being modified by the ALTER SEQUENCE statement?

Given the following statement:
GRANT DELETE ON TABLE sales TO User1 WITH GRANT OPTION;
Which statement is correct?

Which method for restricting data access relies on a SQL search condition that describes what set of rows a user has access to?

Which SQL statement will take the ability to create tables in a table space named MYTBSP away from user USER1?

In a trusted context environment, if a switch request is made with an authorization ID that is not allowed on a trusted connection, what state is the connection placed in?

When is a mask used to limit access to data in a table?

What privilege is required to create a view on a table that you are not the owner of?

After creating several new indexes and executing RUNSTATS, user USER1 wants to re-create a
package that is stored in the database so it will take advantage of the new indexes. Which
authority/privilege must user USER1 be granted in order to re-create the package?

Which SQL statement will give a user named USER1 the ability to run an embedded SQL application named BENEFITS that calls a package named CORPDATA.PKGA?

Which of the following privileges allow a user to update the comment on a sequence?

Which statement is valid about the Row and Column Access Control (RCAC) feature?

Which type of table should you use if you want to define specific time periods when data is valid?

Which SQL statement will create a DB2 object that can be used to store numerical data as Canadian currency?

Which object can be enabled for compression?

What is the minimum product required for DB2 connectivity across all platforms?

Given the SQL statement: CREATE ALIAS total_sales FOR sales Which statement is valid?

Which DB2 object limits the user's ability to retrieve data from a table by defining a SQL statement in the object?

A table named EMPLOYEES was created as follows:
 
 CREATE TABLE employees(
   emp_no       VARCHAR(4)  NOT NULL,
   emp_name     VARCHAR(50) NOT NULL,
   emp_email    VARCHAR(30) NOT NULL)
 Then,the following INSERT statements were executed:
   INSERT INTO employees VALUES ('1A2B','Joe Smith','[email protected]');
   INSERT INTO employees VALUES ('1122','Lisa Carter','');
   INSERT INTO employees VALUES ('1A2B','John Doe','[email protected]');
 If the following query is executed:
   SELECT count(*) FROM employees;
 
What will be the results?

Which two DB2 objects can a view be derived from? (Choose two.)

Which protocol provides applications the ability to connect to and update multiple DB2 databases within the same transaction?

Which statement about bitemporal tables is valid?

Which object can a buffer pool be assigned to?

Which DB2 object is associated with PREVIOUS VALUE and NEXT VALUE expressions?

What needs to be defined in order to track changes made to a system-period temporal table over
time?

Which object is used to cache table and index data as it is read from disk?

Which statement about large object (LOB) locators is true?

Which DB2 product is a database cluster solution for non-mainframe platforms and is best suited for Online Transaction Processing (OLTP) workloads?

What type of large object (LOB) is used to store LOB data together with the formatted rows on data pages, instead of in a separate LOB storage object?

Which product replaced the Control Center used in versions prior to DB2 v10?

Which DB2 product is NOT suitable for very large data warehouse applications?

Which platform supports DB2 pureScale environments?

Which tool allows users to connect to a DB2 database using a wizard?

A table named SALES contains a record for every sales transaction a company processes. A user wishes to see the number of transactions that are made by each salesman. Which SQL statement will produce the desired results?

Which statement about roll back operations is correct?

When a COMMIT statement is executed, what happens?

Which SQL statement would be used to retrieve only salaries that are greater than $100,000.00 from a table named EMPLOYEE?

Which SQL statement should be used to retrieve the minimum and maximum salaries (SALARY) for each job code (JOB), sorted by job code, from a table named EMPLOYEE?

If a table named MY_TAB contains 100 rows and the following statement is executed:
 
 UPDATE
  (SELECT * FROM my_tab
   ORDER BY col1 DESC
   FETCH FIRST 10 ROWS ONLY) AS temp
   SET col3 = 99;
 
How many rows will be updated?

An SQL function named DEPT_INFO was created as follows:
 
 CREATE FUNCTION dept_info()
  RETURNS TABLE(dept_id INTEGER,dept_name VARCHAR(20))
  LANGUAGE SQL
  READS SQL DATA
  BEGIN ATOMIC
  RETURN
    SELECT deptno,deptname FROM department WHERE deptname LIKE 'S%';
  END
 
What is the correct way to use this function in a query?

Which type of stored procedure is not associated with an external application program?

Which SQL statement will retrieve the department number (WORKDEPT) and average departmental
salary (SALARY) for all departments in a table named EMPLOYEE and arrange the result set in
ascending order by average departmental salary?

Which statement about system-period temporal tables is true?

A table named DEPARTMENT contains the following data:
 
 DEPTNO     DEPTNAME          MGRNO      ADMRDEPT
 -----      --------          -----      --------
 A01        PLANNING          000020     A00
 B02        BRANCH OFFICE 1   -          E01
 C22        BRANCH OFFICE 2   -          E01
 D22        BRANCH OFFICE 3   -          E01
 E22        BRANCH OFFICE 4   -          E01
 If a user attempts to execute the following statement
 UPDATE employee SET workdept=
   (SELECT deptno FROM department WHERE admrdept LIKE 'E%')
 WHERE workdept IS NULL;
 
What will happen?

Which statement about UPDATE processing is FALSE?

Given the following statements:
 
 CREATE TABLE employee(empid INTEGER,info XML);
 INSERT INTO employee VALUES(1,
 '<employeesinfo>
    <name>Joe Smith</name>
    <department name="DB2">
     <team>Development</team>
    </department>
  </employeeinfo>');
 If the following XQuery statement is executed:
  XQUERY
  for $info in deb2-fn:xmlcolumn('EMPLOYEE.INFO')/employeeinfo
  return $info/name
 
What will be the result?

A table named STATES has the following columns: STATE_CD, REGION_CD, and STATE_NAME.
Which SQL statement will return the number of states in each region, ordered by number of states?

When a user-defined function (UDF) is created and no schema is specified, what schema is used to store the UDF?

What is a function of a table check constraint?

Which statement about foreign keys is true?

Which type of table is best suited for recording results from application data manipulation that is not required to be persistent?

Which statement regarding referential integrity constraints is true?

Which database object provides an alternative way of describing data that exists in one or more base tables?

The WITH CHECK OPTION clause of the CREATE VIEW statement allows you to do which of the
following?

Which type of constraint can be used to tell the DB2 Optimizer that a parent-child relationship exists between two tables, but that the relationship is not enforced?

Which event will NOT cause a trigger to be fired?

What is the primary purpose for using the INCLUDE clause with a CREATE UNIQUE INDEX
statement?

What are INSTEAD OF triggers used for?

User USER1 wants to store part numbers and part descriptions in a table named PARTS; user
USER1 also wants to ensure that any comparisons of part descriptions that are made will be done so in binary, regardless of the database collating sequence used. Which statement should user USER1 execute to create the desired table?

What mechanism is typically used to automatically update other tables, generate or transform values for inserted or updated rows, and perform tasks such as issuing alerts?

Which data type should be used to store data in a column that represents money and accurately returns a two position scale?

User USER1 wants to define a required relationship between two tables named TAB1 and TAB2 in such a way that whenever a record is deleted from table TAB1, any related records are deleted from table TAB2. What must user USER1 do to accomplish this?

Which statement about a deadlock is true?

What takes place when a process accesses a data object on which it already holds a lock, and the access mode requires a more restrictive lock than the one currently held?

What is the act of releasing a large number of row-level locks that an application holds on a single table to acquire a table-level lock known as?

When is an INTENT EXCLUSIVE (IX) lock required?

The EXCLUSIVE MODE option of the LOCK TABLE statement is used to prevent which of the
following?

What factor influences lock conversion/promotion?

Application APP_A is performing updates to table TAB1 using the read stability (RS) isolation level. Which isolation level will allow application APP_B to retrieve all rows from table TAB1 immediately, rather than have to wait until application APP_A has finished making updates?

If no isolation level is specified, what is the default isolation level used?

What isolation level prevents dirty reads, nonrepeatable reads, and phantoms?