000-610 - DB2 10.1 Fundamentals
Go back to IBM
Given the SQL statement: CREATE ALIAS total_sales FOR sales Which statement is valid?
SALES can be the name of an existing alias.
Which database object provides an alternative way of describing data that exists in one or more base tables?
If no isolation level is specified, what is the default isolation level used?
Cursor Stability (CS)
Which statement about bitemporal tables is valid?
When data in a bitemporal table is updated, a row is added to it's associated history table.
Which operation normally does NOT require an exclusive lock?
What is the purpose of the Query Tuner?
To provide recommendations and analysis for tuning a single query.
Which statement is true regarding constraints?
Informational constraints tell DB2 what rules the data conforms to, but the rules are not enforced.
Which two operations are allowed in the body of an SQL scalar user-defined function? (Choose two.)
SQL control statements.
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?
Uncommitted Read (UR)
What is the primary function of an Online Transaction Processing (OLTP) workload?
To make changes to a small number of records within a single transaction.
Which SQL statement will allow user USER1 to create a view on a table named EMPLOYEE?
GRANT SELECT ON TABLE employee TO user1
Which command will delete all rows from a table without generating log records?
If the following command is executed: CREATE DATABASE test What is the page size (in kilobytes) of the database?
Which statement will prevent concurrent application processes from performing anything other than read-only operations against a table named TAB1?
LOCK TABLE tab1 IN SHARE MODE
Which command is used to back out a subset of database changes that have been made within a unit of work?
ROLLBACK TO SAVEPOINT
Which two DB2 objects can a view be derived from? (Choose two.)
What isolation level prevents dirty reads, nonrepeatable reads, and phantoms?
Repeatable read (RR)
Which SQL statement will give user USER1 the ability to assign a comment to a sequence named MYSEQ?
GRANT ALTER ON SEQUENCE myseq TO user1
Which statement about application-period temporal tables is true?
Is based on explicitly-supplied timestamps that define the time periods during which data is valid.
What is the minimum product required for DB2 connectivity across all platforms?
DB2 Connect Personal Edition
By default, a column defined as a TIMESTAMP includes which attributes?
6 fractional digits and no time zone.
Which DB2 object is used to provide a logical grouping of other database objects?
Which method for restricting data access relies on a SQL search condition that describes what set of rows a user has access to?
Row and column access control
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?
SELECT * FROM employee WHERE (hiredate < '1999-01-01' AND salary < 40000) OR (education = 'University')
If a user has been granted SYSCTRL authority, which activities can they perform?
Create and drop databases
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?
When a COMMIT statement is executed, what happens?
The current transaction is terminated and a new transaction boundary is started.
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?
The data in the table is deleted and the table is implicitly dropped.
Which action needs to be performed in order to complete the definition of an application-period temporal table?
A BUSINESS_TIME period must be specified in a CREATE or ALTER of the table.
Which protocol provides applications the ability to connect to and update multiple DB2 databases within the same transaction?
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?
Uncommitted Read (UR)
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 are INSTEAD OF triggers used for?
To perform insert, update, and delete operations against complex views.
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?
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 of the following privileges allow a user to update the comment on a sequence?
Which SQL statement will create a DB2 object that can be used to store numerical data as Canadian currency?
CREATE DISTINCT TYPE canadian_dollar AS DECIMAL (9,3) WITH COMPARISONS
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?
SELECT empno, hiredate, salary FROM employee WHERE hiredate < '1998-01-01' AND salary < 35000
What is the purpose of a role?
To group a collection of privileges together so that they can be simultaneously granted to and revoked from multiple users.
What functionality allows users to perform a UNION operation between a DB2 table and an Oracle view?
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?
SELECT job, MIN(salary), MAX(salary) FROM employee GROUP BY job ORDER BY job;
Which SQL statement should be used to select the minimum and maximum salaries (SALARY), by job code (JOB), from a table EMPLOYEE?
SELECT job, MIN(salary), MAX(salary) FROM employee GROUP BY job;
Which type of lock allows the lock owner and all concurrent applications to read, but not update, the locked data?
Which DB2 object can be used to improve the execution performance of qualified SELECT statements?
Materialized Query Table
Which statement about INSERT operations is true?
Inserted values must satisfy the conditions of any check constraints defined on the table.
Which product replaced the Control Center used in versions prior to DB2 v10?
IBM Data Studio
Which object is used to cache table and index data as it is read from disk?
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?
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?
When is an INTENT EXCLUSIVE (IX) lock required?
When a transaction intends to read or change data.