Monday, October 28, 2013

CHAPTER 6 (CONCURRENCY) DB2 9 Exam 730 certification Dumps


730 CERTIFICATION GUIDE

Q. Which of the following isolation levels will lock all rows scanned to build a result data set?
A. Uncommitted Read
B. Cursor Stability
C. Read Stability
D. Repeatable Read


Q.Application A holds an Exclusive lock on table TAB1 and needs to acquire an Exclusive lock on table TAB2. Application B holds an Exclusive lock on table TAB2 and needs to acquire an Exclusive lock on table TAB1. If lock timeout is set to -1 and both applications are using the Read Stability isolation level, which of the following will occur?
A. Applications A and B will cause a deadlock situation
B. Application B will read the copy of table TAB1 that was loaded into memory when Application A first read it
C. Application B will read the data in table TAB1 and see uncommitted changes made by Application A
D. Application B will be placed in a lock-wait state until Application A releases its lock


Q.A transaction using the Read Stability isolation level scans the same table multiple times before it terminates. Which of the following can occur within this transaction's processing?
A. Uncommitted changes made by other transactions can be seen from one scan to the next.
B. Rows removed by other transactions that appeared in one scan will no longer appear in subsequent scans.
C. Rows added by other transactions that did not appear in one scan can be seen in subsequent scans.
D. Rows that have been updated can be changed by other transactions from one scan to the next.


Q.Two applications have created a deadlock cycle in the locking subsystem. If lock timeout is set to 30 and both applications were started at the same time, what action will the deadlock detector take when it "wakes up" and discovers the deadlock?
A. It will randomly pick an application and rollback its current transaction
B. It will rollback the current transactions of both applications
C. It will wait 30 seconds, then rollback the current transactions of both applications if the deadlock has not been resolved
D. It will go back to sleep for 30 seconds, then if the deadlock still exists, it will randomly pick an application and rollback its current transaction


Q.Application A is running under the Repeatable Read isolation level and holds an Update lock on table TAB1. Application B wants to query table TAB1 and cannot wait for Application A to release its lock. Which isolation level should Application B run under to achieve this objective?
A. Repeatable Read
B. Read Stability
C. Cursor Stability
D. Uncommitted Read


Q.Which of the following DB2 UDB isolation levels will only lock rows during read processing if another transaction tries to drop the table the rows are being read from?
A. Repeatable Read
B. Read Stability
C. Cursor Stability
D. Uncommitted Read


Q.Application A holds a lock on a row in table TAB1. If lock timeout is set to 20, what will happen when Application B attempts to acquire a compatible lock on the same row?
A. Application B will acquire the lock it needs
B. Application A will be rolled back if it still holds its lock after 20 seconds have elapsed
C. Application B will be rolled back if Application A still holds its lock after 20 seconds have elapsed
D. Both applications will be rolled back if Application A still holds its lock after 20 seconds have elapsed


Q.To which of the following resources can a lock NOT be applied?
A. Table spaces
B. Buffer pools
C. Tables
D. Rows
 

Q.Which of the following causes a lock that is being held by an application using the Cursor Stability isolation level to be released?
A. The cursor is moved to another row
B. The row the cursor is on is deleted by the application
C. The row the cursor is on is deleted by another application
D. The row the cursor is on needs to be updated by another application

 
Q.Which of the following modes, when used with the LOCK TABLE statement, will cause the DB2 Database Manager to acquire a table-level lock that prevents other concurrent transactions from accessing data stored in the table while the owning transaction is active?
A. SHARE MODE
B. ISOLATED MODE
C. EXCLUSIVE MODE
D. RESTRICT MODE


Q.An application has acquired a Share lock on a row in a table and now wishes to update the row. Which of the following statements is true?
A. The application must release the row-level Share lock it holds and acquire an Update lock on the row
B. The application must release the row-level Share lock it holds and acquire an Update lock on the table
C. The row-level Share lock will automatically be converted to a row-level Update lock
D. The row-level Share lock will automatically be escalated to a table-level Update lock







Q.Application A wants to read a subset of rows from table TAB1 multiple times. Which of the following isolation levels should Application A use to prevent other users from making modifications and additions to table TAB1 that will affect the subset of rows read?
A. Repeatable Read
B. Read Stability
C. Cursor Stability
D. Uncommitted Read


Q.Which of the following resources can be explicitly locked?
A.Row
B.Page
C.Table
D.Column

Q.Application A issues the following SQL statements within in a single transaction using the Uncommitted Read isolation level:
SELECT * FROM employee WHERE deptno='A00';
UPDATE employee SET salary = salary * 1.1 WHERE deptno='A00';
As long as the transaction has not been committed, which of the following is true for all other applications not running under the Uncommitted Read isolation level?

A.They can read and make changes to data for any employee in department A00.
B.They can read but not make changes to data for any employee in department A00.
C.They must wait until the transaction is committed before accessing data of any employee in department A00.
D.They must wait until application A disconnects before accessing data of any employee in department A00.

Q. Which of the following best describes how locks are used in DB2?
A.To maintain control of updated rows for commit processing
B.To ensure only committed changes are altered by another application
C.To allow two applications to update the same row of data simultaneously
D.To prevent multiple applications from accessing the same data simultaneously

Q. If an application issues the same query more than once in the same Unit of Work, which isolation level will not permit this application to see additional rows inserted by other applications?

 A.Read Stability (RS)
B.Repeatable Read (RR)
C.Uncommitted Read (UR)
C.Cursor Stability (CS)

Q. If DB2 detects a deadlock between application A and application B (each waiting for a lock held by the other one), what action will DB2 take?

A.Rollback the transaction in both applications.
B.Rollback the transaction in one of the two applications.
C.Decrease the lock timeout value for both applications, thereby causing a lock timeout situation.
D.Send a message to let the administrator know of the situation and decide which application or applications should be terminated.

Q. How does DB2 protect the integrity of indexes when data is updated?
A.Locks are acquired on the data.
B.Locks are acquired on index keys.
C.Locks are acquired on index pages.
D.Locks are acquired on index pointers.

Q. Application A currently holds an exclusive lock on a single row and application B tries to access that row. If lock timeout is set to -1 and both applications are using isolation level RS, which of the following will occur?

A.Applications A and B will cause a deadlock situation.
B.Application B will read the previous version of the row.
C.Application B will read the row and see uncommitted changes made by application A.
D.Application B will be placed in a lock-wait state until application A releases its lock.


Q. A table contains a list of all seats available at a football stadium. A seat consists of a section number, a seat number, and whether or not the seat has been assigned. A ticket agent working at the box office generates a list of all unassigned seats. When the agent refreshes the list, it should only change if another agent assigns one or more unassigned seats. Which of the following is the best isolation level to use for this application?
A. Repeatable Read
B. Read Stability
C. Cursor Stability
D. Uncommitted Read

CHAPTER 5 (WORKING WITH DB2 TABLE,VIEWS,AND INDEXES) DB2 9 Exam 730 certification Dumps

Q.Which of the following is a valid DB2 data type?
A. NUMBER
B. INTERVAL
C. BYTE
D. NUM

Q.Which of the following DB2 data types does NOT have a fixed length?
A. INT
B. CHAR
C. XML
D. DOUBLE



Q.Which of the following is the best statement to use to create a user-defined data type that can be used to store currency values?
A. CREATE DISTINCT TYPE currency AS NUMERIC(7,2)
B. CREATE DISTINCT TYPE currency AS SMALLINT
C. CREATE DISTINCT TYPE currency AS BIGINT
D. CREATE DISTINCT TYPE currency AS DOUBLE

Q.Which of the following DB2 data types can be used to store 1000 MB of single-byte character data?
A. BLOB
B. CLOB
C. DBCLOB
D. GRAPHIC

Q.Which of the following DB2 data types can NOT be used to create an identity column?
A. SMALLINT
B. INTEGER
C. NUMERIC
D. DOUBLE


Q.Which of the following strings can NOT be inserted into an XML column using XMLPARSE()?
A. "<employee />"
B. "<name>John Doe</name>"
C. "<?xml version='1.0' encoding='UTF-8' ?>"
D. "<p></p>"

 

Q.Given the requirements to store employee names, employee numbers, and when employees were hired, which of the following built-in data types CANNOT be used to store the day an employee was hired?
A. Character Large Object
B. Time
C. Varying-Length Character String
D. Timestamp
 

Q.Given the requirements to store customer names, billing addresses, and telephone numbers, which of the following would be the best way to define the telephone number column for a table if all customers were located in the same country?
PHONE    CHAR(15)
PHONE    VARCHAR(15)
PHONE    LONG VARCHAR
PHONE    CLOB(1K)


Q. Which of the following strings can NOT be inserted into an XML column using XMLPARSE()?
A. "<employee />"
B. "<name>John Doe</name>"
C. "<?xml version='1.0' encoding='UTF-8' ?>"
D. "<p></p>"


Q. Which two of the following are optional and do not have to be specified when creating a table?
A. Table name
B. Column name
C. Default constraint
D. Column data type
E. NOT NULL constraint


Q.Which of the following is a NOT a valid reason for defining a view on a table?
A. Restrict users' access to a subset of table data
B. Ensure that rows inserted remain within the scope of a definition
C. Produce an action as a result of a change to a table
D. Provide users with an alternate view of table data


Q.Given the following CREATE TABLE statement:
CREATE TABLE table2 LIKE table1
Which two of the following will NOT occur when the statement is executed?
A. TABLE2 will have the same column names and column data types as TABLE1
B. TABLE2 will have the same column defaults as TABLE1
C. TABLE2 will have the same nullability characteristics as TABLE1
D. TABLE2 will have the same indexes as TABLE1.
E. TABLE2 will have the same referential constraints as TABLE1


Q.  If the following SQL statements are executed:
CREATE TABLE tab1 (id SMALLINT NOT NULL PRIMARY KEY,
                   name  VARCHAR(25));

CREATE TABLE tab2 (empid   SMALLINT,
                   weekno  SMALLINT,
                   payamt  DECIMAL(6,2),
    CONSTRAINT const1 FOREIGN KEY (empid)
        REFERENCES taba(id) ON UPDATE NO ACTION);


Which of the following statements is true?
A. Only values that exist in the ID column of table TAB1 are allowed to be inserted in the EMPID column of table TAB2
B. The updating of values in the ID column of table TAB1 is not allowed
C. Only values that do not already exist in the ID column of table TAB1 are allowed to be inserted in the EMPID column of table TAB2
D. When values that exist in the ID column of table TAB1 are updated, corresponding values in the EMPID column of table TAB2 are updated as well

CHAPTER 4 (WORKING WITH DB2 USING SQL AND XQUERY) DB2 9 Exam 730 certification Dumps

730 CERTIFICATION GUIDE

Q.Given the following two tables:
         NAMES
-----------------------------
NAME                 NUMBER
----------                -------
Wayne Gretzky        99
Jaromir Jagr             68
Bobby Orr                 4
Bobby Hull              23
Mario Lemieux        66

        POINTS
-----------------------------
NAME                 POINTS
----------                 ------
Wayne Gretzky        244
Bobby Orr               129
Brett Hull                 121
Mario Lemieux        189
Joe Sakic                    94
How many rows would be returned using the following statement?
SELECT name FROM names, points
A. 0
B. 5
C. 10
D. 25

Q.Given the following CREATE TABLE statement:
CREATE TABLE EMPLOYEE
 (EMPNO       CHAR(3) NOT NULL,
  FIRSTNAME   CHAR(20) NOT NULL,
  MIDINIT     CHAR(1),
  LASTNAME    CHAR(20) NOT NULL,
  SALARY      DECIMAL(10, 2))
Which of the following will retrieve the rows that have a missing value in the MIDINIT column?
A. SELECT * FROM employee WHERE midinit = ' '
B. SELECT * FROM employee WHERE midinit = NULL
C. SELECT * FROM employee WHERE midinit = " "
D. SELECT * FROM employee WHERE midinit IS NULL

Q.Given the following two tables:
       TAB1
----------------------
COL_1         COL_2
-----         -----
A             10
B             12
C             14

       TAB2
----------------------
COL_A         COL_B
-----         -----
A             21
C             23
D             25
Assuming the following results are desired:
COL_1            COL_2            COL_A            COL_B
A                    10                        A                21
B                    12                         -                -
C                    14                        C                23
-                -                                D                25
Which of the following joins will produce the desired results?
A. SELECT * FROM tab1 INNER JOIN tab2 ON col_1 = col_a
B. SELECT * FROM tab1 LEFT OUTER JOIN tab2 ON col_1 = col_a
C. SELECT * FROM tab1 RIGHT OUTER JOIN tab2 ON col_1 = col_a
D. SELECT * FROM tab1 FULL OUTER JOIN tab2 ON col_1 = col_a

Q.If the following SQL statements are executed in the order shown:
CREATE TABLE table1 (c1 INTEGER, c2 INTEGER);
INSERT INTO table1 VALUES (123, 456);
UPDATE table1 SET c1 = NULL;
What will be the result of the following statement?
SELECT * FROM table1;
A.
C1    C2
---   ---
123   456
1 record(s) selected.
B.
C1    C2
---   ---
NULL  456
1 record(s) selected.
C.   
C1    C2
---   ---
-     456
1 record(s) selected.

D.
C1    C2
---   ---
0     456
1 record(s) selected.
Q.Given the following table:
TAB1

COL1         COL2
-----        -----
A            10
B            20
C            30
A            10
D            40
C            30
Assuming the following results are desired:
TAB1

COL1         COL2
-----        -----
A            10
B            20
C            30
D            40
Which of the following statements will produce the desired results?
A. SELECT UNIQUE * FROM tab1
B. SELECT DISTINCT * FROM tab1
C. SELECT UNIQUE(*) FROM tab1
D. SELECT DISTINCT(*) FROM tab1

Q.Assuming table TAB1 contains 100 rows, which of the following queries will return only half of the rows available?
A. SELECT * FROM tab1 FIND FIRST 50 ROWS
B. SELECT * FROM tab1 FETCH FIRST 50 ROWS ONLY
C. SELECT * FROM tab1 WHILE ROW_NUM < 50
D. SELECT * FROM tab1 MAXROWS 50

Q.Given the following two tables:
EMPLOYEE

ID NAME           DEPTID
-- ---------------- ---
01 Mick Jagger     10
02 Keith Richards  20
03 Ronnie Wood     20
04 Charlie Watts   20
05 Bill Wyman      30
06 Brian Jones    -

DEPARTMENT

ID DEPTNAME
-- ------------------
10 Executive Staff
20 Sales
30 Marketing
40 Engineering
50 Human Resources
Which two of the following queries will display the employee name and department name for all employees that are in Sales?
A.    SELECT e.name, d.deptname
FROM employee e, department d
WHERE e.deptid = d.id AND d.id = '20'

B.    SELECT e.name, d.deptname
FROM employee e FULL OUTER JOIN department d
ON e.deptid = d.id
WHERE d.id = '20'
C.    SELECT e.name, d.deptname
FROM employee e RIGHT OUTER JOIN department d
ON e.deptid = d.id
WHERE d.id = '20'
D.    SELECT e.name, d.deptname
FROM employee e LEFT OUTER JOIN department d
ON e.deptid = d.id
WHERE d.id = '20'
E.    SELECT e.name, d.deptname
FROM employee e INNER JOIN department d
ON e.deptid = d.id
WHERE d.id = '20'


Q.Given the following queries:
SELECT c1 FROM tab1;
SELECT c1 FROM tab2;
Which of the following set operators can be used to produce a result data set that contains only records that are not found in the result data set produced by each query after duplicate rows have been eliminated?
A. UNION
B. INTERSECT
C. EXCEPT
D. MERGE

Q.Given the following two tables:
NAMES

NAME                   NUMBER
----------                  -------
Wayne Gretzky          99
Jaromir Jagr               68
Bobby Orr                  4
Bobby Hull               23
Brett Hull                  16
Mario Lemieux         66
Mark Messier           11

POINTS

NAME                   POINTS
----------                  ------
Wayne Gretzky       244
Jaromir Jagr           168
Bobby Orr              129
Brett Hull               121
Mario Lemieux      189
Joe Sakic                 94
Which of the following statements will display the player name, number, and points for all players that have scored points?
A. SELECT p.name, n.number, p.points FROM names n INNER JOIN points p ON n.name = p.name
B. SELECT p.name, n.number, p.points FROM names n LEFT OUTER JOIN points p ON n.name = p.name
C. SELECT p.name, n.number, p.points FROM names n RIGHT OUTER JOIN points p ON n.name = p.name
D. SELECT p.name, n.number, p.points FROM names n FULL OUTER JOIN points p ON n.name = p.name

Q.Which of the following is a valid wildcard character in a LIKE clause of a SELECT statement?
A. %
B. *
C. ?
D. \

Q.Given the following tables:
YEAR_2006

EMPID  NAME
-----  ---------------
1      Jagger, Mick
2      Richards, Keith
3      Wood, Ronnie
4      Watts, Charlie
5      Jones, Darryl
6      Leavell, Chuck

YEAR_1962

EMPID  NAME
-----  ---------------
1      Jagger, Mick
2      Richards, Keith
3      Jones, Brian
4      Wyman, Bill
5      Watts, Charlie
6      Stewart, Ian
If the following SQL statement is executed, how many rows will be returned?
SELECT name FROM year_2007
UNION ALL
SELECT name FROM year_1962
A. 6
B. 9
C. 10
D. 12

Q.Given the following table definition:
SALES
--------------------------------------
INVOICE_NO          CHAR(20) NOT NULL
SALES_DATE          DATE
SALES_PERSON        VARCHAR(25)
REGION              CHAR(20)
SALES_AMT           DECIMAL(9,2)
Which of the following queries will return SALES information, sorted by SALES_PERSON, from A to Z, and SALES_DATE, from most recent to earliest?
A. SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales SORT BY sales_person, sales_date DESC
B. SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales SORT BY sales_person DESC, sales_date
C. SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales ORDER BY sales_person, sales_date DESC
D. SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales ORDER BY sales_person DESC, sales_date

Q.Given the following statement:
SELECT hyear, AVG(salary)
FROM (SELECT YEAR(hiredate) AS hyear, salary
              FROM employee WHERE salary > 30000)
GROUP BY hyear
Which of the following describes the result if this statement is executed?
A. The statement will return the year and average salary for all employees that have a salary greater than $30,000, sorted by year.
B. The statement will return the year and average salary for all employees hired within a given year that have a salary greater than $30,000.C. The statement will return the year and average salary for all years that every employee hired had a salary greater than $30,000.
D. The statement will return the year and average salary for all years that any employee had a salary greater than $30,000.

Q.Which two of the following statements are true about the HAVING clause?
A. The HAVING clause is used in place of the WHERE clause.
B. The HAVING clause uses the same syntax as the WHERE clause.
C. The HAVING clause can only be used with the GROUP BY clause.
D. The HAVING clause accepts wildcards.
E. The HAVING clause uses the same syntax as the IN clause.

Q.Given the following table definitions:
DEPARTMENT
--------------------------
DEPTNO       CHAR(3)
DEPTNAME     CHAR(30)
MGRNO        INTEGER
ADMRDEPT     CHAR(3)

EMPLOYEE
--------------------------
EMPNO        INTEGER
FIRSTNAME    CHAR(30)
MIDINIT      CHAR
LASTNAME     CHAR(30)
WORKDEPT     CHAR(3)
Which of the following statements will list every employee number and last name, along with the employee number and last name of their manager, including employees that have not been assigned to a manager?
A. SELECT e.empno, e.lastname, m.empno, m.lastname FROM employee e LEFT INNER JOIN department INNER JOIN employee m ON mgrno=m.empno ON e.workdept=deptno
B. SELECT e.empno, e.lastname, m.empno, m.lastname FROM employee e LEFT OUTER JOIN department INNER JOIN employee m ON mgrno=m.empno ON e.workdept=deptno
C. SELECT e.empno, e.lastname, m.empno, m.lastname FROM employee e RIGHT OUTER JOIN department INNER JOIN employee m ON mgrno=m.empno ON e.workdept=deptno
D. SELECT e.empno, e.lastname, m.empno, m.lastname FROM employee e RIGHT INNER JOIN department INNER JOIN employee m ON mgrno=m.empno ON e.workdept=deptno

Q.Given the following table:
EMPLOYEE

EMPID     NAME            INSTRUMENT
---       ---------------         -------------------
1         Jagger, Mick           01
2         Richards, Keith      02
3         Wood, Ronnie        02
4         Watts, Charlie        03
5         Jones, Darryl         04
6         Leavell, Chuck      05
If the following query is executed:
SELECT name,
   CASE WHEN instrument = '01' THEN 'HARMONICA'
     WHEN instrument = '02' THEN 'GUITAR'
     WHEN instrument = '03' THEN 'DRUMS'
     ELSE 'UNKNOWN'
   END AS instrument
FROM employee
What will be the results?
A.
NAME                INSTRUMENT
---------------        --------
Jagger, Mick       HARMONICA
Richards, Keith   GUITAR
Wood, Ronnie     GUITAR
Watts, Charlie     DRUMS
Jones, Darryl       ERROR
Leavell, Chuck    ERROR
B.
NAME                 INSTRUMENT
------------------     --------
Jagger, Mick        HARMONICA
Richards, Keith    GUITAR
Wood, Ronnie      GUITAR
Watts, Charlie     DRUMS
Jones, Darryl      04
Leavell, Chuck   05
C.   
NAME               INSTRUMENT
------------------ --------
Jagger, Mick       HARMONICA
Richards, Keith    GUITAR
Wood, Ronnie       GUITAR
Watts, Charlie     DRUMS
Jones, Darryl      UNKNOWN
Leavell, Chuck     UNKNOWN

D.   
NAME               INSTRUMENT
------------------ --------
Jagger, Mick       HARMONICA
Richards, Keith    GUITAR
Wood, Ronnie       GUITAR
Watts, Charlie     DRUMS
Jones, Darryl      -
Leavell, Chuck     -

Q.Given the following UPDATE statement:
UPDATE employees SET workdept =
   (SELECT deptno FROM department WHERE deptno = 'A01')
    WHERE workdept IS NULL
Which of the following describes the result if this statement is executed?
A. The statement will fail because an UPDATE statement cannot contain a subquery.
B. The statement will only succeed if the data retrieved by the subquery does not contain multiple records.
C. The statement will succeed; if the data retrieved by the subquery contains multiple records, only the first record will be used to perform the update.
D. The statement will only succeed if every record in the EMPLOYEES table has a null value in the WORKDEPT column.

Q.Given the following table:
CURRENT_EMPLOYEES
--------------------------
EMPID        INTEGER NOT NULL
NAME         CHAR(20)
SALARY       DECIMAL(10,2)

PAST_EMPLOYEES
--------------------------
EMPID        INTEGER NOT NULL
NAME         CHAR(20)
SALARY       DECIMAL(10,2)
Assuming both tables contain data, which of the following statements will NOT successfully add data to table CURRENT_EMPLOYEES?
A. INSERT INTO current_employees (empid) VALUES (10)
B. INSERT INTO current_employees VALUES (10, 'JAGGER', 85000.00)
C. INSERT INTO current_employees SELECT empid, name, salary FROM past_employees WHERE empid = 20
D. INSERT INTO current_employees (name, salary) VALUES (SELECT name, salary FROM past_employees WHERE empid = 20)

Q.Given the following table definition:
SALES
--------------------------
SALES_DATE       DATE
SALES_PERSON     CHAR(20)
REGION           CHAR(20)
SALES            INTEGER
Which of the following SQL statements will remove all rows that had a SALES_DATE in the year 1995?
A. DELETE * FROM sales WHERE YEAR(sales_date) = 1995
B. DELETE FROM sales WHERE YEAR(sales_date) = 1995
C. DROP * FROM sales WHERE YEAR(sales_date) = 1995
D. DROP FROM sales WHERE YEAR(sales_date) = 1995

Q.Given the following table definition:
EMPLOYESS
--------------------------
EMP ID             INTEGER
NAME              CHAR(20)
DEPT               CHAR(10)
SALARY          DECIMAL (10, 2)
COMMISSION DECIMAL (8, 2)
Assuming the DEPT column contains the values ‘ADMIN’, ‘PRODUCTION’, and ‘SALES’, which of the following statements will produce a result data set in which all ADMIN department employees are grouped together, all PRODUCTION department employees are grouped together, and all SALES department employees are grouped together?
A. SELECT name, dept FROM employees ORDER BY dept
B. SELECT name, dept FROM employees GROUP BY dept
C. SELECT name, dept FROM employees GROUP BY ROLLUP (dept)
D. SELECT name, dept FROM employees GROUP BY CUBE (dept)

Q.The following SQL statement:
DELETE FROM tab1 WHERE CURRENT OF csr1 WITH RR
Is used to perform which type of delete operation?
A. Positioned
B. Searched
C. Embedded
D. Dynamic 


Q.Given the following data:
TAB1

C1    C2
--      ---
200  abc
250  abc
150  def
300  ghi
175  def
If the following query is executed:
WITH subset (col1, col2) AS
    (SELECT c1, c2 FROM tab1 WHERE c1 > 150)
SELECT col2, SUM(col1) AS col1_sum
  FROM subset
  GROUP BY col2
  ORDER BY col2
Which of the following result data sets will be produced?
A.  
COL2      COL1_SUM
----          --------
abc         200
abc         250
def         175
ghi         300
4 record(s) selected.
B.  
COL2     COL1_SUM
----         --------
abc         450
def         175
ghi         300
3 record(s) selected.

C.  
COL2     COL1_SUM
----          --------
abc         450
def         325
ghi         300
3 record(s) selected.
D.  
COL2     COL1_SUM
----         --------
abc         450
abc         450
def         175
def         175
ghi         300
5 record(s) selected.


Q.Given the following table definitions:
TABLE1
-----------------------------------
ID                   INT
NAME           CHAR(30)
PERSON       INT
CITIES          INT


TABLE2
-----------------------------------
ID                      INT
LASTNAME    CHAR(30)


Which of the following statements will remove all rows in table TABLE1 that have matching PERSONs in table TABLE2?
A. DELETE FROM table1 WHERE id IN (SELECT id FROM table2)
B. DELETE FROM table1 WHERE id IN (SELECT person FROM table2)
C. DELETE FROM table1 WHERE person IN (SELECT id FROM table2)
D. DELETE FROM table1 WHERE person IN (SELECT person FROM table2)


Q.Given the following two tables:
NAMES


NAME                NUMBER
-----------             --------------

Wayne Gretzky     99
Jaromir Jagr          68
Bobby Orr              4
Bobby Hull           23
Brett Hull             16
Mario Lemieux    66
Mark Messier      11

POINTS

NAME                   POINTS
-----------                --------------
Wayne Gretzky      244
Jaromir Jagr           168
Bobby Orr              129
Brett Hull               121
Mario Lemieux      189
Joe Sakic                94

Which of the following statements will display the player name, number, and points for all players that have scored points?
A. SELECT p.name, n.number, p.points FROM names n INNER JOIN points p ON n.name = p.name
B. SELECT p.name, n.number, p.points FROM names n LEFT OUTER JOIN points p ON n.name = p.name
C. SELECT p.name, n.number, p.points FROM names n RIGHT OUTER JOIN points p ON n.name = p.name
D. SELECT p.name, n.number, p.points FROM names n FULL OUTER JOIN points p ON n.name = p.name


Q.Given the following table definitions:
EMPLOYEES
------------------------------------------------
EMPID                          INTEGER
NAME                          CHAR(20)
DEPTID                        CHAR(3)
SALARY                      DECIMAL(10,2)
COMMISSION             DECIMAL(8,2)
DEPARTMENTS
------------------------------------------------
DEPTNO                      INTEGER
DEPTNAME                CHAR(20)
Which of the following statements will produce a result data set that satisfies all of these conditions:
> Displays the total number of employees in each department
>> Displays the corresponding department name for each department ID
>> Sorted by department employee count, from greatest to least


A. SELECT *, COUNT(empno) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2 DESC
B. SELECT deptname, COUNT(empno) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2 DESC
C. SELECT deptname, COUNT(empno) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2 ASC
D. SELECT deptname, COUNT(*) FROM departments, employees WHERE deptid = deptno GROUP BY deptname ORDER BY 2


Q.Given the following table:
CURRENT_EMPLOYEES
--------------------------------------
EMPID INTEGER NOT NULL
NAME CHAR(20)
SALARY DECIMAL(10,2)
PAST_EMPLOYEES
--------------------------------------
EMPID INTEGER NOT NULL
NAME CHAR(20)
SALARY DECIMAL(10,2)
Assuming both tables contain data, which of the following statements will NOT successfully add data to table CURRENT_EMPLOYEES?
A. INSERT INTO current_employees (empid) VALUES (10)
B. INSERT INTO current_employees VALUES (10, ‘JAGGER’, 85000.00)
C. INSERT INTO current_employees SELECT empid, name, salary FROM past_employees WHERE empid = 20
D. INSERT INTO current_employees (name, salary) VALUES (SELECT name, salary FROM past_employees WHERE empid = 20)


Q.Given the following table:
STOCK
--------------------------
CATEGORY        CHAR(1)
PARTNO              CHAR(12)
DESCRIPTION   VARCHAR(40)
QUANTITY        INTEGER
PRICE                 DEC(7,2)
If items are indicated to be out of stock by setting DESCRIPTION to NULL and QUANTITY and PRICE to zero, which of the following statements updates the STOCK table to indicate that all items except those with CATEGORY of ‘S’ are temporarily out of stock?
A. UPDATE stock SET description = ’NULL’, quantity = 0, price = 0 WHERE category ‘S’
B. UPDATE stock SET description = NULL, SET quantity = 0, SET price = 0 WHERE category ‘S’
C. UPDATE stock SET (description, quantity, price) = (‘null’, 0, 0) WHERE category ‘S’
D. UPDATE stock SET (description, quantity, price) = (NULL, 0, 0) WHERE category ‘S’


Q.Given the following SQL statements:
CREATE TABLE tab1 (col1 INTEGER)
INSERT INTO tab1 VALUES (NULL)
INSERT INTO tab1 VALUES (1)
CREATE TABLE tab2 (col2 INTEGER)
INSERT INTO tab2 VALUES (NULL)
INSERT INTO tab2 VALUES (1)
INSERT INTO tab2 VALUES (2)
What will be the result when the following statement is executed?
SELECT * FROM tab1 WHERE col1 IN (SELECT col2 FROM tab2)
A.
COL1
----
1
1 record(s) selected.


B.
COL1
----
NULL
1
2 record(s) selected.

C.
COL1
----
-
1
2 record(s) selected.

D.
COL1
----
-
1 record(s) selected.


Q.Given the following table definition:
SALES
---------------------------------------------
INVOICE_NO           CHAR(20) NOT NULL
SALES_DATE          DATE
SALES_PERSON     CHAR(20)
REGION                   CHAR(20)
SALES                      INTEGER
If the following SELECT statement is executed, which of the following describes the order of the rows in the result data set produced?
SELECT * FROM sales
A. The rows are sorted by INVOICE_NO in ascending order.
B. The rows are sorted by INVOICE_NO in descending order.
C. The rows are ordered based on when they were inserted into the table.
D. The rows are not sorted in any particular order.


Q.Given the following tables:
YEAR_2006
EMPID NAME
---------------------------------
1                Jagger, Mick
2                Richards, Keith
3                Wood, Ronnie
4                Watts, Charlie
5                Jones, Darryl
6                Leavell, Chuck
YEAR_1962
EMPID NAME
---------------------------------
1                Jagger, Mick
2                Richards, Keith
3                Jones, Brian
4                Wyman, Bill
5                Chapman, Tony
6                Stewart, Ian
If the following SQL statement is executed, how many rows will be returned?
SELECT name FROM year_2006
UNION
SELECT name FROM year_1962
A. 0
B. 6
C. 10
D. 12


Q.Which of the following best describes a unit of work?
A. It is a recoverable sequence of operations whose point of consistency is established when a connection to a database has been established or when a mechanism known as a savepoint is created.
B. It is a recoverable sequence of operations whose current point of consistency can be determined by querying the system catalog tables.
C. It is a recoverable sequence of operations whose point of consistency is established when an executable SQL statement is processed after a connection to a database has been established or a previous transaction has been terminated.
D. It is a recoverable sequence of operations whose point of consistency is only established if a mechanism known as a savepoint is created.


Q.Given the following set of statements:
CREATE TABLE tab1 (col1 INTEGER, col2 CHAR(20));
COMMIT;
INSERT INTO tab1 VALUES (123, 'Red');
INSERT INTO tab1 VALUES (456, 'Yellow');
SAVEPOINT s1 ON ROLLBACK RETAIN CURSORS;
DELETE FROM tab1 WHERE col1 = 123;
INSERT INTO tab1 VALUES (789, 'Blue');
ROLLBACK TO SAVEPOINT s1;
INSERT INTO tab1 VALUES (789, 'Green');
UPDATE tab1 SET col2 = NULL WHERE col1 = 789;
COMMIT;
Which of the following records would be returned by the following statement?
SELECT * FROM tab1
A.
COL1     COL2
----     -------
123      Red
456      Yellow
2 record(s) selected.
B.
COL1     COL2
----     ------
456      Yellow
1 record(s) selected.

C.
COL1     COL2
----     -----
123      Red
456      Yellow
789      -
3 record(s) selected.


D.
COL1     COL2
----     ------
123      Red
456      Yellow
789      Green
3 record(s) selected.


Q. 
Given the following table:
TAB1

COL1         COL2
-----        -----
A            10
B            20
C            30
D            40
E            50
And the following SQL statements:
DECLARE c1 CURSOR WITH HOLD FOR
SELECT * FROM tab1 ORDER BY col_1;
OPEN c1;
FETCH c1;
FETCH c1;
FETCH c1;
COMMIT;
FETCH c1;
CLOSE c1;
FETCH c1;
Which of the following is the last value obtained for COL_2?
A. 20
B. 30
C. 40
D. 50


Q.A stored procedure has been created with the following statement:
CREATE PROCEDURE proc1 (IN var1 VARCHAR(10), OUT rc INTEGER)
SPECIFIC myproc LANGUAGE SQL …
What is the correct way to invoke this procedure from the command line processor (CLP)?
A. CALL proc1 ('SALES', ?)
B. CALL myproc ('SALES', ?)
C. CALL proc1 (SALES, ?)
D. RUN proc1 (SALES, ?)


Q.Given the following table:
TEMP_DATA

TEMP     DATE
-----         -----
45           12/25/2006
51           12/26/2006
67           12/27/2006
72           12/28/2006
34           12/29/2006
42           12/30/2006
And the following SQL statement:
CREATE FUNCTION degf_to_c (temp INTEGER)
   RETURNS INTEGER
   LANGUAGE SQL
   CONTAINS SQL
   NO EXTERNAL ACTION
   DETERMINISTIC
   BEGIN ATOMIC
      DECLARE newtemp INTEGER;
      SET newtemp = temp - 32;
      SET newtemp = newtemp * 5;
      RETURN newtemp / 9;
   END
Which two of the following SQL statements illustrate the proper way to invoke the scalar function DEGF_TO_C?
A. VALUES degf_to_c(32)
B. SELECT date, degf_to_c(temp) AS temp_c FROM temp_data
C. CALL degf_to_c(32)
D. SELECT * FROM TABLE(degf_to_c(temp)) AS temp_c
E. VALUES degf_to_c(32) AS temp_c

CHAPTER 3 (WORKING WITH DATABASE&DATABASE OBJECT) DB2 9 Exam 730 certification Dumps

Q.While attempting to connect to a database stored on an iSeries server from a Windows client, the following message was displayed:
SQL1013N The database alias name or database name "TEST_DB" could not be found.

Which of the following actions can be used to help determine why this message was displayed?
A. Execute the LIST REMOTE DATABASES command on the server; look for an entry for the TEST_DB database
B. Execute the LIST DCS DIRECTORY command on the server; look for an entry for the TEST_DB database
C. Execute the LIST REMOTE DATABASES command on the client; look for an entry for the TEST_DB database
D. Execute the LIST DCS DIRECTORY command on the client; look for an entry for the TEST_DB database

Q.A database named TEST_DB resides on a z/OS system and listens on port 446. The TCP/IP address for this system is 192.168.10.20 and the TCP/IP host name is MYHOST. Which of the following commands is required to make this database accessible to a Linux client?
A. CATALOG TCPIP NODE zos_srvr REMOTE myhost SERVER 192.168.10.20;
CATALOG DATABASE zos_db AS test_db AT NODE zos_srvr;
CATALOG DCS DATABASE zos_db AS test_db;
B. CATALOG TCPIP NODE zos_srvr REMOTE myhost SERVER 192.168.10.20;
CATALOG DCS DATABASE zos_db AS test_db AT NODE zos_srvr;
C. CATALOG TCPIP NODE zos_srvr REMOTE myhost SERVER 446;
CATALOG DCS DATABASE zos_db AS test_db AT NODE zos_srvr;
D. CATALOG TCPIP NODE zos_srvr REMOTE myhost SERVER 446;
CATALOG DATABASE zos_db AS test_db AT NODE zos_srvr;
CATALOG DCS DATABASE zos_db AS test_db;


Q.Which of the following statements will catalog the database MYDB on the node MYNODE and assign it the alias MYNEWDB?
A. CATALOG DATABASE mynewdb AT NODE mynode
B. CATALOG DATABASE mynewdb AS mydb AT NODE mynode
C. CATALOG DATABASE mydb AT NODE mynode
D. CATALOG DATABASE mydb AS mynewdb AT NODE mynode

Q.Which of the following tools can NOT be used to catalog a database?
A. Control Center
B. SQL Assist
C. Configuration Assistant
D. Command Line Processor

Q.Which of the following tools can NOT be used to catalog a database?
A. Visual Explain
B. Alert Center
C. Journal
D. Configuration Assistant

Q.In which of the following scenarios would a stored procedure be beneficial?
A. An application running on a remote client needs to be able to convert degrees Celsius to degrees Fahrenheit and vice versa
B. An application running on a remote client needs to collect three input values, perform a calculation using the values provided, and store the input data, along with the results of the calculation in two different base tables
C. An application running on a remote client needs to track every modification made to a table that contains sensitive data
D. An application running on a remote client needs to ensure that every new employee that joins the company is assigned a unique, sequential employee number

Q.CREATE TABLE orders
     (order_num      INTEGER NOT NULL,
       buyer_name    VARCHAR(35),
       amount        NUMERIC(5,2));

CREATE UNIQUE INDEX idx_orderno ON orders(order_num);
Which of the following describes the resulting behavior?
A. Every ORDER_NUM value entered must be unique; whenever the ORDERS table is queried rows should be displayed in order of increasing ORDER_NUM values
B. Every ORDER_NUM value entered must be unique; whenever the ORDERS table is queried rows will be displayed in no particular order
C. Duplicate ORDER_NUM values are allowed; no other index can be created for the ORDERS table that reference the ORDER_NUM column
D. Every ORDER_NUM value entered must be unique; no other index can be created for the ORDERS table that reference the ORDER_NUM column

Q.An alias can be an alternate name for which two of the following DB2 objects?
A. Sequence
B. Trigger
C. View
D. Schema
E. Table

Q.Which of the following events will NOT cause a trigger to be activated?
A. A select operation
B. An insert operation
C. An update operation
D. A delete operation

Q.If a view named V1 is created in such a way that it references every column in a table named EMPLOYEE except a column named SALARY, which of the following is NOT an accurate statement?
A. View V1 can be used in the same context as the EMPLOYEE table for all data retrieval operations that do not acquire SALARY information
B. View V1 can be used as a data source for other views
C. View V1 does not have to reside in the same schema as the EMPLOYEE table
D. All data, except SALARY data that is stored in the EMPLOYEE table is copied to the physical location associated with view V1
 
Q.Which of the following would NOT provide access to data stored in table TABLE1 using the name T1?
A. An alias named T1 that references table TABLE1
B. A view named T1 that references table TABLE1
C. A schema named T1 that references table TABLE1
D. An alias named T1 that references a view named V1 that references table TABLE1

Q.Which of the following DB2 objects can be referenced by an INSERT statement to generate values for a column?
A. Sequence
B. Identity column
C. Trigger
D. Table function

Q.A sequence was created with the DDL statement shown below:
CREATE SEQUENCE my_seq START WITH 10 INCREMENT BY 10 CACHE 10
User USER1 successfully executes the following statements in the order shown:
VALUES NEXT VALUE FOR my_seq INTO :hvar;
VALUES NEXT VALUE FOR my_seq INTO :hvar;
User USER2 successfully executes the following statements in the order shown:
ALTER SEQUENCE my_seq RESTART WITH 5 INCREMENT BY 5 CACHE 5;
VALUES NEXT VALUE FOR my_seq INTO :hvar;
After users USER1 and USER2 are finished, user USER3 executes the following query:
SELECT NEXT VALUE FOR my_seq FROM sysibm.sysdummy1
What value will be returned by the query?
A. 5
B. 10
C. 20
D. 30

Q.Given the following statements:
CREATE TABLE tab1 (c1 INTEGER, c2 CHAR(5));
CREATE VIEW view1 AS SELECT c1, c2 FROM tab1 WHERE c1 < 100;
CREATE VIEW view2 AS SELECT c1, c2 FROM view1
        WITH CASCADED CHECK OPTION;
Which of the following INSERT statements will fail to execute?
A. INSERT INTO view2 VALUES(50, 'abc')
B. INSERT INTO view1 VALUES (100, 'abc')
C. INSERT INTO view2 VALUES(150, 'abc')
D. INSERT INTO view1 VALUES(100, 'abc')

Q.Given the following statements:
CREATE TABLE t1 (c1 INTEGER, c2 CHAR(5));
CREATE TABLE t1audit (user VARCHAR(20), date DATE, action
VARCHAR(20));

CREATE TRIGGER trig1 AFTER INSERT ON t1
FOR EACH ROW
MODE DB2SQL
INSERT INTO t1audit VALUES (CURRENT USER, CURRENT DATE,
'Insert');
If user USER1 executes the following statements:
INSERT INTO t1 VALUES (1, 'abc');
INSERT INTO t1 (c1) VALUES (2);
UPDATE t1 SET c2 = 'ghi' WHERE c1 = 1;
How many new records will be written to the database?
A. 0
B. 2
C. 3
D. 4

Q.Which of the following is NOT an attribute of Declared Global Temporary Tables (DGTTs)?
A. Each application that defines a DGTT has its own instance of the DGTT
B. Two different applications cannot create DGTTs that have the same name
C. DGTTs can only be used by the application that creates them, and only for the life of the application
D. Data stored in a DGTT can exist across transaction boundaries

Q.Which of the following is an accurate statement about packages?
A. Packages provide a logical grouping of database objects.
B. Packages contain control structures that are considered the bound form for SQL statements
C. Packages describe the objects in a DB2 database and their relationship to each other
D. Packages may be used during query optimization to improve the performance for a subset of SELECT queries

Q.Given the following information:
Protocol: TCP/IP
Port Number: 5000
Host Name: DB_SERVER
Database Name: TEST_DB
Database Server Platform: Linux
Which of the following will allow a client to access the database stored on the server?
A. CATALOG DATABASE test_db AS test_db REMOTE TCPIP SERVER db_server PORT 5000 OSTYPE LINUX;
B. CATALOG TCPIP NODE 5000 REMOTE SERVER db_server OSTYPE LINUX;
CATALOG DATABASE test_db AS test_db AT NODE db_server AUTHENTICATION SERVER;
C. CATALOG TCPIP NODE db_server REMOTE db_server SERVER 5000 OSTYPE LINUX;
CATALOG DATABASE test_db AS test_db AT NODE db_server AUTHENTICATION SERVER;

D. CATALOG TCPIP NODE db_server REMOTE db_server PORT 5000 OSTYPE LINUX;
CATALOG DATABASE test_db AS test_db AT NODE db_server AUTHENTICATION SERVER;

Q.A declared temporary table is used for which of the following purposes?
A. Backup purposes
B. Storing intermediate results
C. Staging area for load operations
D. Sharing result data sets between applications

Q.Which of the following DB2 objects is NOT considered executable using SQL?
A. Routine
B. Function
C. Procedure
D. Trigger

Q.Which of the following is NOT an accurate statement about views?
A. Views are publicly referenced names and no special authority or privilege is needed to use them.
B. Views can be used to restrict access to columns in a base table that contain sensitive data
C. Views can be used to store queries that multiple applications execute on a regular basis in a database
D. Views support INSTEAD OF triggers

Q.Which of the following SQL statements can be used to create a DB2 object to store numerical data as EURO data?
A. CREATE NICKNAME euro FOR DECIMAL (9,3)
B. CREATE ALIAS euro FOR DECIMAL (9,3)
C. CREATE DISTINCT TYPE euro AS DECIMAL (9,3)
D. CREATE DATA TYPE euro AS DECIMAL (9,3)




Sunday, October 27, 2013

CHAPTER 2 (SECURITY) DB2 9 Exam 730 certification Dumps

Q.Which of the following is NOT a valid method of authentication that can be used by DB2 9?
A.SERVER
B.SERVER_ENCRYPT
C.CLIENT
D.DCS


Q.In a client-server environment, which two of the following can be used to verify passwords?
A. System Catalog
B. User ID/password file
C. Client Operating System
D. Communications layer
E. Application Server

 
Q. A table named DEPARTMENT has the following columns:
DEPT_ID
DEPT_NAME
MANAGER
AVG_SALARY
Which of the following is the best way to prevent most users from viewing AVG_SALARY data?
A. Encrypt the table's data
B. Create a view that does not contain the AVG_SALARY column
C. Revoke SELECT access for the AVG_SALARY column from users who should not see AVG_SALARY data
D. Store AVG_SALARY data in a separate table and grant SELECT privilege for that table to the appropriate users

Q.Which authority or privilege is granted by the DB2 Database Manager configuration file?
A. CONNECT
B. CONTROL
C. SYSMAINT
D. EXECUTE


Q.Which two of the following authorities allow a user to create a new database?
A. SYSADMN
B. SYSCTRL

C. SYSMAINT
D. DBADM
E. CREATEDB


Q..Assuming USER1 has no authorities or privileges, which of the following will allow USER1 to create a view named VIEW1 that references two tables named TAB1 and TAB2?
A. CREATEIN privilege on the database
B. REFERENCES privilege on TAB1 and TAB2
C. CREATE_TAB privilege on the database
D. SELECT privilege on TAB1 and TAB2


Q.Which of the following will allow user USER1 to change the comment associated with a table named TABLE1?
A. GRANT UPDATE ON TABLE table1 TO user1
B. GRANT CONTROL ON TABLE table1 TO user1
C. GRANT ALTER ON TABLE table1 TO user1
D. GRANT REFERENCES ON TABLE table1 TO user1


Q.A table called DEPARTMENT has the following columns:
DEPT_ID
DEPT_NAME
MANAGER
Which of the following statements will ONLY allow user USER1 to modify the DEPT_NAME column?
A. GRANT ALTER ON TABLE department TO user1
B. GRANT ALTER (dept_name) ON TABLE department TO user1
C. GRANT UPDATE ON TABLE department TO user1
D. GRANT UPDATE (dept_name) ON TABLE department TO user1


Q.An index named EMPID_X exists for a table named EMPLOYEE. Which of the following will allow user USER1 to drop the EMPID_X index?
A. GRANT DROP ON INDEX empid_x TO user1
B. GRANT DELETE ON INDEX empid_x TO user1
C. GRANT INDEX ON TABLE employee TO user1
D. GRANT CONTROL ON INDEX empid_x TO user1


Q.On which two of the following database objects may the SELECT privilege be controlled?
A. Sequence
B. Nickname
C. Schema
D. View
E. Index


Q.User USER1 wants to utilize an alias to remove rows from a table. Assuming USER1 has no authorities or privileges, which of the following privileges are needed?
A. DELETE privilege on the table
B. DELETE privilege on the alias
C. DELETE privilege on the alias; REFERENCES privilege on the table
D. REFERENCES privilege on the alias; DELETE privilege on the table


Q. user USER1 holds CONTROL privilage on table TABLE1. Which two of the following statements is user USER1 allowed to execute.
A.GRANT CONTROL ON table1 TO user2.
B. GRANT LOAD ON table1 TO user2
C. GRANT INSERT,UPDATE ON table1 TO user2 WITH GRANT OPTION.
D. GRANT BINDADD ON table1 TO PUBLIC
E. GRANT ALL PRIVILEGES on table1 TO PUBLIC

Q.A user wishing to invoke an SQL stored procedure that queries a table must have which of the following privileges?
A. CALL privilege on the procedure; SELECT privilege on the table
B. CALL privilege on the procedure; REFERENCES privilege on the table
C. EXECUTE privilege on the procedure; SELECT privilege on the table
D. EXECUTE privilege on the procedure; REFERENCES privilege on the table


Q.After the following SQL statement is executed:
GRANT ALL PRIVILEGES ON TABLE employee TO USER user1
Assuming user USER1 has no other authorities or privileges, which of the following actions is user USER1 allowed to perform?
A. Drop an index on the EMPLOYEE table
B. Grant all privileges on the EMPLOYEE table to other users
C. Alter the table definition
D. Drop the EMPLOYEE table
 

Q.Which two of the following privileges is required in order to use a package?
A. BINDADD
B. BIND
C. CONNECT
D. EXECUTE

E. USE 

Q. Which of the following statements allows user USER1 to take the ability to create packages in a database named SAMPLE away from user USER2?
A. REVOKE CONNECT ON DATABASE FROM user2
B. REVOKE CREATETAB ON DATABASE FROM user2
C. REVOKE BIND ON DATABASE FROM user2
D. REVOKE BINDADD ON DATABASE FROM user2


Q.Which of the following will provide user USER1 and all members of the group GROUP1 with the ability to perform DML, but no other operations on table TABLE1?
A. GRANT INSERT, UPDATE, DELETE, SELECT ON TABLE table1 TO user1 AND group1
B. GRANT INSERT, UPDATE, DELETE, SELECT ON TABLE table1 TO USER user1, GROUP group1
C. GRANT ALL PRIVILEGES EXCEPT ALTER, INDEX, REFERENCES ON TABLE table1 TO USER user1, GROUP group1
D. GRANT CONTROL ON TABLE table1 TO user1 AND group1 


Q.What does the following statement do?
GRANT REFERENCES (col1, col2) ON TABLE table1 TO user1 WITH
GRANT OPTION
A. Gives user USER1 the ability to refer to COL1 and COL2 of table TABLE1 in queries, along with the ability to give this authority to other users and groups.
B. Gives user USER1 the ability to refer to COL1 and COL2 of table TABLE1 in views, along with the ability to give this authority to other users and groups.
C. Gives user USER1 the ability to define a referential constraint on table TABLE1 using columns COL1 and COL2 as the parent key of the constraint.
D. Gives user USER1 the ability to define a referential constraint on table TABLE1 using columns COL1 and COL2 as the foreign key of the constraint. 


Q.User USER1 is the owner of TABLE1. Assuming user USER1 only holds privileges for TABLE1, which of the following is the best way to remove all privileges user USER1 holds?
A. REVOKE CONTROL ON table1 FROM user1
B. REVOKE ALL PRIVILEGES ON table1 FROM user1
C. REVOKE CONTROL ON table1 FROM user1;
REVOKE ALL PRIVILEGES ON table1 FROM user1;
D. REVOKE CONTROL, ALL PRIVILEGES ON table1 FROM user1 


Q.User USER1 has the privileges needed to invoke a stored procedure named GEN_RESUME. User USER2 needs to be able to call the procedure - user USER1 and all members of the group PUBLIC should no longer be allowed to call the procedure. Which of the following statement(s) can be used to accomplish this?
A. GRANT EXECUTE ON ROUTINE gen_resume TO user2 EXCLUDE user1, PUBLIC
B. GRANT EXECUTE ON PROCEDURE gen_resume TO user2;
REVOKE EXECUTE ON PROCEDURE gen_resume FROM user1, PUBLIC;
C. GRANT CALL ON ROUTINE gen_resume TO user2 EXCLUDE user1 PUBLIC
D. GRANT CALL ON PROCEDURE gen_resume TO user2;
REVOKE CALL ON PROCEDURE gen_resume FROM user1, PUBLIC;
 

Q.A view named V.VIEW1 is based on a table named T.TABLE1. A user with DBADM authority issues the following statement:
GRANT INSERT ON v.view1 TO user1 WITH GRANT OPTION
Which of the following statements is USER1 authorized to execute?
A. GRANT INSERT ON t.table1 TO user2
B. GRANT CONTROL ON v.view1 TO user2
C. GRANT ALL PRIVILEGES ON v.view1 TO user2
D. GRANT INSERT ON v.view1 TO user2 


Q.What does the following statement do?
GRANT ALTER ON SEQUENCE gen_empid TO user1 WITH GRANT OPTION
A. Gives USER1 the ability to change the comment associated with a sequence named GEN_EMPID, along with the ability to give this CONTROL authority for the sequence to other users and groups.
B. Gives USER1 the ability to change the values returned by the PREVIOUS_VALUE and NEXT_VALUE expressions associated with a sequence named GEN_EMPID, along with the ability to give CONTROL authority for the sequence to other users and groups.
C. Gives USER1 the ability to change the comment associated with a sequence named GEN_EMPID, along with the ability to give this authority to other users and groups.
D. Gives USER1 the ability to change the values returned by the PREVIOUS_VALUE and
 

Wednesday, October 23, 2013

DB2 locking - Locks and lock modes

 Read about DB2 locking here 
We know about the 4 isolation levels in DB2, namely RR,RS,CS,UR. One thing which all these isolation level have in common is Locking. Locks in Databases serves the same purpose as they do in our house or cars.They Decide who can or can not access a resource like Table, Table Space,Rows etc. The DB2 Database manager imposes locks to prohibit "owning" transaction from accessing uncommitted data written by other application, and also to prevent other transactions to make any change to the currently owned rows.
Data Base manager locks have these 4 basic attributes:
1. SIZE -  It indicates the physical size of the portion of data resource that is being locked.
Possible sizes are : Table space, Table, Partition, pages.
2. MODE -  Type of access allowed for lock owner as well as the type of access permitted for concurrent users of the locked object
3. OBJECT -  Resources being locked. The only type of explicitly lockable objects are tables. The database manager also imposes locks on other type of resources such as Rows,tables, and Table spaces.
4. DURATION - The length of time a lock is held. LOCK DURATIONS are affected by Isolation level.
ex:  LOCK acquired by for RR(repeatable Read) transaction is likely to be more than lock acquired by CS transaction.
 

1.SIZE:The DB2 resources on which lock can be places are as below in ascending order of their sizes.
From Highest (Most data locked)  to lowest( least data locked),the most common lock sizes are
A. TS
B. TABLE.
C.PAGE
D.ROW

Point to Note: Locks can be taken at any level in the locking hierarchy without taking a lock at the lower level. However, locks cannot be taken at the lower levels without a compatible higher-level lock also being taken. For example, DB2 can take a table space lock without taking any other lock, but DB2 cannot take a page lock without securing TS lock.
One common batch abend involving DB2 locking is  like below:
DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = Tables-space-name
DSNUGASU - IRLM LOCK REQUEST FAILED,
IRLM RETURN CODE = X'00000008'
IRLM REASON CODE = X'2000'


This happens because one job was trying to acquire a DB2 resource, but that is being locked by other job. IRLM sets a timeout period, after that it terminates the execution and job fails with above mentioned error-code. DBA can provide information about the resource being used and the offending job what is causing the problem.Once that job is complete, we can restart our job from abend step. 

2.Lock Modes:
Lock Modes  at  Table level are as follows:
IS : INTENT SHARE:  The Lock owner can read any data in the table if an S lock can be obtained on the target rows or page.
IX: INTENT EXCLUSIVE: The lock owner can read or change any data in the table provided an X lock can be obtained on rows or pages to be changed and a U or S lock can be obtained on rows or pages to be read.
S : SHARE :  The lock owner can read any data in the table and will not obtain row or page locks
U: UPDATE:  The lock owner can read any data in the table and may UPDATE OR CHANGE data if an X lock on the table is obtained. No page or row locks are obtained.
X:EXCLUSIVE: The lock owner can read or update data in the table. Row or Page locks are not obtained. 
ROW or PAGE lock modes: 
S : SHARE : The Row or Page is being READ by one application and is available for READ-ONLY by other applications.
U:UPDATE :  Row or page is currently READ by one application and it might get changed by that application itself.  U lock will support cursors that are opened with FOR UPDATE OF clause.
X: EXCLUSIVE:  The Row or PAGE is being changed by application and IS NOT available for other application, except those that permit UNCOMMITTED READ.

Indexes are not locked, because their serialization is controlled by latches and concurrency is controlled by data locking. 

Skip Locked Data
The SKIPPED LOCKED DATA option lets a transaction skip rows that are incompatibly locked by other transactions. This option can help improve the performance of some applications by eliminating lock wait time. You should use this technique only for applications that don't require unavailable or uncommitted data. Transactions using this option will not read or modify data that is unavailable, uncommitted, or held by locks.
We can specify the SKIP LOCKED DATA option in a SELECT, SELECT INTO, PREPARE, searched UPDATE, or searched
DELETE statement. You can also use it with the UNLOAD utility. The option works with isolation level CS or RS. It is ignored for isolation levels UR and RR.
The next example returns a count of only the rows that are not uncommitted (if no index exists on CUST_NO).
SELECT COUNT(*)
FROM CUSTOMER
WHERE CUST_NO >= 500
SKIP LOCKED DATA