Roger and Sanders
Please read the questions given at the end of every chapter of the IBM DB2 8.1 Family Fundamentals Certification book by Roger and Sanders.
Some More
Questions on UDB DB2 V7.1.
1. What is the role of RUNSTATS?
2. What is SAVEPOINTS?
3. Index can be changed using ALTER command (TRUE/FALSE)
4. Which among these is not a DDL statement
a) CREATE b) ALTER c) DROP d) DECLARE e) None of the above
5. Length/datatype of the column can be changed using ALTER TABLE command (TRUE/FALSE)
6. DATE variable MM-DD-YYYY externally a string of Char(10)(10 bytes) , it occupies same storage in DB2 (TRUE/FALSE)
7. NULL values occupies extra byte (TRUE/FALSE)
8. CREATE DATABASE is it a SQL statement or DB2 command
9. What is the role of RuntimeSupervisor?
10. ALTER is not allowed on which if these objects
11. a)table b)View c)Index d)Bufferpool e)View f)Stored Procedures
· What are the 2 mathematical principles on which SQL is based on?
· What is a predicate ? Give some examples
· Which of these is the last clause in SQL a)ORDER BY b)GROUP BY c)BETWEEN
· What is Cartesian Product?
· Which of these is not a Column Function a)Group By b) Max c) AVG d) Count e)SUBSTR
· The HAVING caluse is equivalent to the WHERE clause for groups and vector functions(TRUE/FALSE)
· In SQL, the percent sign character(%) is a substitute for zero or more characters(TRUE/FALSE)
· _ character substitutes one and only one character(TRUE/FALSE)
· DECLARE TABLE is an executbale statement(TRUE/FALSE)
· DECLARE TABLE is mandatory to have in programming language(TRUE/FALSE)
· A correlated sub-query is a query in which the sub query references values of the outer SELECT(TRUE/FALSE)
· It is always mandatory to provide the column namnes in the INSERT statement(TRUE/FALSE)
· Multiple rows cab be inserted into a table with single INSERT st(TRUE/FALSE)
· DECLARE TABLE is an executbale statement(TRUE/FALSE)
· DECLARE TABLE is mandatory to have in programming language(TRUE/FALSE)
· A correlated sub-query is a query in which the sub query references values of the outer SELECT(TRUE/FALSE)
· It is always mandatory to provide the column namnes in the INSERT statement(TRUE/FALSE)
· Multiple rows cab be inserted into a table with single INSERT st(TRUE/FALSE)
What are the various concurrency problems?
What are the various isolation levels to overcome problems due to concurrent access of the same data?
Is it possible to acquire row level locking using an SQL statement?
LOCK TABLE is the only SQL control mechanism provided by DB2(TRUE/FALSE)
EXCLUSIVE lock mode will not allow update as well reading of any of the locked data(TRUE/FALSE)
Uncommitted read applications do not gurantee a repeatable read(TRUE/FALSE)
Phantom read problem is due to which of these Add/update/delete ?
Which of the Islolation level eliminates lost update, phantom read or unrepeatable read? (uncomitted read , cursor stability , read stability , repeatable raed)
What are the various operating systems which support DB2 famliy?
Which of these DB2 package is used for personal digital assistanants? (Enterprise Edition , WorkGropu Edition , Personal Edition Edition , Every place)
Satellite Edition can be installed only on Windows(TRUE/FALSE)
Control Center is graphical based tool used for administration of DB2(TRUE/FALSE)
What is the tool used for SQL statements , DB2 commands , to view the result?
What is the use of tool Command Line Processor?
What is the default isolation level provided by DB2 to avoid concurrency problem?
What is Lock Escalation?
Which of these is not part of control center (License Center, alert Center , Journal , Client configuration Assistant) ?
What is the use of Visual Explain
Journal allows to monitor pending jobs, job histories, review results(TRUE/FALSE)
What is Data Warehousing?
What is OLAP?
What are the two default instances created while installing DB2 enterprise edition?
What is the command used to see all DB2 registry values?
The DAS instance must run on DB2 server to monitor the server(TRUE/FALSE)
What are the commands to start and stop the DAS instance?
Which of the following database configuration parameters affects when lock escalation will occur?
A. NUMLOCKS
B. MAXLOCKS
C. LOCKTIMEOUT
D. LOCKTHRESH
Which of the following releases a lock that is held by an application using the Cursor Stability isolation level?
A. If the cursor accessing the row is moved to the next row.
B. If the cursor accessing the row is used to update the row.
C. If the cursor’s current row is deleted by the application.
D. If the cursor’s current row needs to be updated by another application.
Given the requirement of providing a read-only database, applications accessing the database should be run with which of the following isolation levels in order to allow for the most read concurrency?
A. Repeatable Read
B. Read Stability
C. Cursor Stability
D. Uncommitted Read
An update lock gets released by an application using the Repeatable Read isolation level during which of the following?
A. If the cursor accessing the row is closed.
B. If the cursor accessing the row is moved to the next row.
C. If the transaction is committed.
D. If the transaction changes are made with an UPDATE statement rather than an
UPDATE CURRENT OF statement.
Which of the following processing can occur when a transaction using the Uncommitted Read isolation level scans through the same table multiple times?
A. Uncommitted changes made by other processes can be accessed.
B. Uncommitted changes made by other processes can be updated.
C. Rows in a result data set can be updated and those updates can be changed by other processes from one scan to the next.
D. Rows in a result data set can be updated and those updates can be changed by other processes from one scan to the next.
Which of the following isolation levels is most likely to acquire a table level lock during an index scan?
A. Repeatable Read
B. Read Stability
C. Cursor Stability
D. Uncommitted Read
Which of the following tools can be used to catalog a database?
A. Visual Explain
B. Alert Center
C. Journal
D. Client Configuration Assistant
Which of the following tools can not be used to catalog a database?
A. Control Center
B. SQL Assist
C. Client Configuration Assistant
D. Command Line Processor
Which of the following products is allowed to access other DB2 servers, but cannot accept requests from other remote clients?
A. DB2 Universal Database Personal Edition
B. DB2 Universal Database Workgroup Edition
C. DB2 Universal Database Enterprise Edition
D. DB2 Universal Database Enterprise-Extended Edition
Which of the following products must be installed in order to build an application on AIX that will access a DB2 for OS/390 database?
A. DB2 Universal Database Enterprise Edition
B. DB2 Personal Developer’s Edition
C. DB2 Universal Developer’s Edition
D. DB2 Universal Database Enterprise Edition and DB2 Connect Enterprise Edition
Which of the following DB2 components provides resource contention management for large-scale data warehouses?
A. Warehouse Manager
B. Spatial Extender
C. Query Patroller
D. OLAP Server
Which of the following products contains both a DB2 server and a set of tools for creating applications that interact with DB2 databases?
A. DB2 Universal Database Workgroup Edition
B. DB2 Personal Developer’s Edition
C. DB2 Universal Database Enterprise Edition
D. DB2 Universal Developer’s Edition
A client application on OS/390 must access a DB2 server on Windows, UNIX, or OS/2. At a minimum, which of the following must be installed on the DB2 server workstation?
A. DB2 Connect Enterprise Edition
B. DB2 Universal Database Enterprise Edition
C. DB2 Universal Database Workgroup Edition and DB2 Connect Enterprise Edition
D. DB2 Universal Database Enterprise Edition and DB2 Connect Enterprise Edition
In which of the following tools is the threshold value for an alert situation defined?
A. Performance Monitor
B. Control Center
C. Alert Center
D. Journal
What is the main function of DB2 Connect?
A. APPC Gateway
B. DRDA Application Requestor
C. TCP/IP Gateway
D. DRDA Application Server
Which of the following processes does DB2 Warehouse Manager not perform?
A. Query
B. Loading
C. Extraction
D. Transformation
Which of the following tools allows the entering of an SQL statement, execution of the statement, and then checking to see how it was optimized?
A. Control Center
B. Script Center
C. Visual Explain
D. Command Center
IBM DB2 8.1 Family Fundamentals Certification Sample Questions
1. Which of the following delete rules will not allow a row to be deleted from the parent table if a row with the corresponding key value still exists in the child table? Answer:C
DELETE
CASCADE
RESTRICT
SET NULL
2. Given the following tables:
NAMES
Name Number
Wayne Gretzky 99
Jaromir Jagr 68
Bobby Orr 4
Bobby Hull 23
Brett Hull 16
Mario Lemieux 66
Steve Yzerman 19
Claude Lemieux 19
Mark Messier 11
Mats Sundin 13
POINTS
Name Points
Wayne Gretzky 244
Jaromir Jagr 168
Bobby Orr 129
Bobby Hull 93
Brett Hull 121
Mario Lemieux 189
PIM
Name PIM
Mats Sundin 14
Jaromir Jagr 18
Bobby Orr 12
Mark Messier 32
Brett Hull 66
Mario Lemieux 23
Joe Sakic 94
Which of the following statements will display the player?s Names, numbers, points and PIM for all players with an entry in all three tables? Answer:A
SELECT names.name, names.number, points.points, pim.pim FROM names INNER JOIN points ON names.name=points.name INNER JOIN pim ON pim.name=names.name
SELECT names.name, names.number, points.points, pim.pim FROM names OUTER JOIN points ON names.name=points.name OUTER JOIN pim ON pim.name=names.name
SELECT names.name, names.number, points.points, pim.pim FROM names LEFT OUTER JOIN points ON names.name=points.name LEFT OUTER JOIN pim ON pim.name=names.name
SELECT names.name, names.number, points.points, pim.pim FROM names RIGHT OUTER JOIN points ON names.name=points.name RIGHT OUTER JOIN pim ON pim.name=names.name
3. Which two of the following authorities can create a database? Answer: B,D
DBADM
SYSADM
DBCTRL
SYSCTRL
SYSMAINT
4. Which of the following isolation levels is most likely to acquire a table level lock during an index scan? Answer:B
Read Stability
Repeatable Read
Cursor Stability
Uncommitted Read
5. Which of the following DB2 components allows reference to Oracle and DB2 databases in a single query? Answer:C
DB2 Query Patroller
DB2 Warehouse Manager
DB2 Relational Connect
DB2 Connect Enterprise Edition
6. For which of the following database objects can locks NOT be obtained?
A row
A table
A column
An index key
7. Which of the following products can be used to store image data in a DB2 database? Answer:C
Net.Data
Net Search
DB2 AVI Extenders
DB2 XML Extenders
DB2 Text Extenders
8. Given, CREATE TABLE t1 (c1 CHAR(4) NOT NULL). Which of the following can be inserted into this table?
4
NULL
'abc'
'abcde'
9. Given the table:
STAFF
ID LASTNAME
1 Jones
2 Smith
When issuing the query "SELECT * FROM staff", the row return order will be based on which of the following? Answer:A
An ambiguous order
The primary key order
The order that the rows were inserted into the table
The values for the ID column, then the LASTNAME column
10. Which of the following products is required to be installed in order to build an application on AIX which will access a DB2 UDB for OS/390 database? Answer:C
DB2 Connect Personal Edition
DB2 Personal Developer's Edition
DB2 Universal Developer's Edition
DB2 Universal Database Workgroup Edition
11. Given the tables:
COUNTRY
ID NAME PERSON CITIES
1 Argentina 1 10
2 Canada 2 20
3 Cuba 2 10
4 Germany 1 0
5 France 7 5
STAFF
ID LASTNAME
1 Jones
2 Smith
Which of the following statements removes the rows from the COUNTRY table that have PERSONS in the STAFF table? Answer:C
DELETE FROM country WHERE id IN (SELECT id FROM staff)
DELETE FROM country WHERE id IN (SELECT person FROM staff)
DELETE FROM country WHERE person IN (SELECT id FROM staff)
DELETE FROM country WHERE person IN (SELECT person FROM staff)
12. Given the tables:
EMPLOYEE DEPT
emp_num emp_name dept dept_id dept_name
1 Adams 1 1 Planning
2 Jones 1 2 Support
3 Smith 2
4 Williams 1
and the statement:
ALTER TABLE employee
ADD FOREIGN KEY (dept) REFERENCES (dept_id)
ON DELETE CASCADE
How many units of work will be needed to process this statement:
DELETE FROM dept WHERE dept_id=1AA Answer:B
0
1
2
3
4
5
13. Which of the following CANNOT have an auto commit setting? Answer:A
Embedded SQL
The Command Center
The Command Line Processor
The DB2 Call Level Interface
14. Which of the following utilities would you run to order data and reclaim space from deleted rows in a table: Answer:A
reorg
db2look
db2move
runstats
15. Which of the following processes is NOT performed by DB2 Warehouse Manager? Answer:A
Query
Loading
Extraction
Transformation
16. A client application on OS/390 must access a DB2 server on Unix, Windows or OS/2. At a minimum, which of the following is required to be the DB2 server machine? Answer:B
DB2 Connect Enterprise Edition
DB2 Universal Database Enterprise Edition
DB2 Connect and DB2 Universal Database Workgroup Edition
DB2 Connect and DB2 Universal Database Enterprise Edition
17. If a table is defined with a check constraint for one or more columns, which of the following will perform the data validation after the table is loaded with the load utility. Answer:E
Reorg
Check
Runstats
Image Copy
Set Integrity
18. Given the requirement of providing a read-only database, applications accessing the database should be run with which of the following isolation levels to allow for the most read concurrency?
Answer:D
Read stability
Repeatable read
Cursor stability
Uncommitted read
19. If, for a given table, the Control Center does not show the choice Generate DDL, which of the following describes the reason?
Answer:A
The table is a system object.
The table is a summary table.
The table is in LOAD PENDING.
The table is a replicated table.
The table was created by a different user.
20. Which of the following processing can occur for a unit of work using an isolation level of Uncommitted Read and scanning through the table more than once within the unit of work? Answer: A
Access uncommitted changes made by other processes
Update uncommitted changes made by other processes
Update rows of a return set and have those updates changed by other processes from one scan to the next
Update rows of a return set and have those updates committed by other processes from one scan to the next
21. Given the table:
STAFF
ID LASTNAME
1 Jones
2 Smith
3
Which of the following statements removes all rows from the table where there is a NULL value for LASTNAME?
DELETE FROM staff WHERE lastname IS NULL
DELETE FROM staff WHERE lastname = ?NULL?
DELETE ALL FROM staff WHERE lastname IS NULL
DELETE ALL FROM staff WHERE lastname = ?NULL?
22. An update lock gets released by an application using the repeatable read isolation level during which of the following? Answer:B
If the cursor accessing the row is closed.
If the transaction issues a ROLLBACK statement.
If the cursor accessing the row is moved to the next row.
If the transaction changes are made via an UPDATE statement.
23. Given the requirements to store names, employee numbers, and when the employees were hired, which of the following DB2 data types CANNOT be used to contain the day the employee was hired?
CLOB
TIME
VARCHAR
TIMESTAMP
24. Given the successfully executed embedded SQL:
INSERT INTO staff VALUES (1, 'Colbert', 'Dorchester', 1)
COMMIT
INSERT INTO staff VALUES (6, 'Anders', 'Cary', 6)
INSERT INTO staff VALUES (3, 'Gaylord', 'Geneva', 8)
ROLLBACK WORK
Which of the following indicates the number of new rows that would be in the STAFF table?
0
1
2
3
25. The purpose of the USE privilege is to:
query data in a table.
load data into a table.
create tables within a table space.
create table spaces within a database.
26. Given the two table definitions:
ORG
deptnumb INTEGER
deptname CHAR(30)
manager INTEGER
division CHAR(30)
location CHAR(30)
STAFF
id INTEGER
name CHAR(30)
dept INTEGER
job CHAR(20)
years INTEGER
salary DECIMAL(10,2)
comm DECIMAL(10,2)
Which of the following statements will display each department, alphabetically by name, and the name of the manager of the department? Answer :c
SELECT a.deptname, b.name FROM org a, staff b WHERE a.manager=b.id
SELECT a.deptname, b.name FROM org a, staff b WHERE b.manager=a.id
SELECT a.deptname, b.name FROM org a, staff b WHERE a.manager=b.id GROUP BY a.deptname, b.name
SELECT a.deptname, b.name FROM org a, staff b, WHERE b.manager=a.id GROUP BY a.deptname, b.name
27. A declared temporary table is used for which of the following purposes:
backup purposes
storing intermediate results
staging area for the load utility
sharing result sets between applications
28. Which of the following database authorities is required to add a new package?
BINDADD
CREATETAB
CREATEPKG
PACKAGEADD
29. Given the statement:
CREATE VIEW v1 AS SELECT c1 FROM t1 WHERE c1='a'
WITH CHECK OPTION
Which of the following SQL statements will insert data into the table?
INSERT INTO v1 VALUES (a)
INSERT INTO v1 VALUES (b)
INSERT INTO v1 VALUES ('b')
INSERT INTO v1 VALUES ('a')
INSERT INTO v1 VALUES ('ab')
30. The table STOCK has the following columns definitions:
type CHAR (1)
status CHAR(1)
quantity INTEGER
price DEC (7,2)
Items are indicated to be out of stock by setting STATUS to NULL and QUANTITY and PRICE to zero.
31. Which of the following statements updates the STOCK table to indicate that all the items except for those with TYPE of "S" are temporarily out of stock? Answer:B
UPDATE stock SET status='NULL', quantity=0, price=0 WHERE type <> 'S'
UPDATE stock SET (status, quantity, price) = (NULL, 0, 0) WHERE type <> 'S'
UPDATE stock SET (status, quantity, price) = (?NULL?, 0, 0) WHERE type <>'S'
UPDATE stock SET status = NULL, SET quantity=0, SET price = 0 WHERE type <>'S'
32. How many indexes will be created by the following statement?
Create table mytab
(
Col1 int not null primary key,
Col2 char(64),
Col3 char(32),
Col4 int not null,
constraint c4 unique (Col4,Col1)
) Answer :C
0
1
2
3
4
33. Cataloging a remote database is: Answer :B
Performed on a PC or UNIX machine to identify the server the DB2 database manager is on.
Performed on a PC or UNIX machine to identify the DB2 database to users and applications.
Never performed in DB2, as only one database per node is allowed, so cataloging a node automatically catalogs the database at that node.
Performed on a PC or UNIX machine to open the catalogs in the DB2 database and present a user with a list of all accessible tables in that database.
34. Given the statements and operations:
"CREATE TABLE t1 (c1 CHAR(1))"
Six rows are inserted with values of: a, b, c, d, e and f
"SET CONSTRAINTS FOR t1 OFF"
"ALTER TABLE t1 ADD CONSTRAINT con1 CHECK (c1 ='a')"
"SET CONSTRAINTS FOR t1 IMMEDIATE CHECKED FOR EXCEPTION IN t1 USE t1exp"
Which of the following describes what happens to the rows with values of b, c, d, e and f? Answer:C
deleted from T1 only
deleted from T1 and written into the t1exp file
deleted from T1 and inserted into the table t1exp
deleted from T1 and written into the db2diag.log file
deleted from T1 and inserted into the table syscat.checks
35. Given the transaction:
"CREATE TABLE t1 (id INTEGER,CONSTRAINT chkid CHECK (id<100))" mgrno =" m.empno" workdept =" deptno" mgrno =" m.empno" workdept =" deptno" mgrno =" m.empno" workdept =" deptno" mgrno =" m.empno" workdept =" deptno" c1="'a'" manager="a.id" manager="a.id" manager="b.id" manager="b.id"> 200))
CREATE VIEW v1 AS SELECT a, b, c FROM tab1 WHERE a > 250
Which of the following statements will fail?
INSERT INTO v1 VALUES (250, 2, 3)
INSERT INTO v1 VALUES (300, 2, 3)
INSERT INTO tab1 VALUES (200, 2, 3)
INSERT INTO tab1 VALUES (250, 2, 3)
60. Given a SELECT statement that has a GROUP BY clause. The HAVING clause uses the same syntax as which other clause?
WHERE
UNION
SUBQUERY
ORDER BY
61. A table called EMPLOYEE has the following columns: name, department, and phone_number. Which of the following can limit read access to the phone_number column?
Using a view to access the table
Using a referential constraint on the table
Revoking access from the phone_number column
Defining a table check constraint on the table
62. Given the following SQL statement:
GRANT EXECUTE ON PACKAGE proc1 TO usera WITH GRANT OPTION
Which two of the following describe what USERA is allowed to do?
Execute SQL statements in package PROC1
Grant any privilege on package PROC1 to other users
Grant bind privilege on package PROC1 to other users
Grant execute privilege on package PROC1 to other users
Access all of the tables referenced in package PROC1 from any program
63. Given the tables:
COUNTRY
ID NAME PERSON CITIES
1 Argentina 1 10
2 Canada 2 20
3 Cuba 2 10
4 Germany 1 0
5 France 7 5
STAFF
ID LASTNAME
1 Jones
2 Smith
How many rows would be inserted into the STAFF table using the following statement?
INSERT INTO staff
SELECT person, 'Greyson'
FROM country WHERE person > 1
0
1
2
3
5
64. Given the tables:
COUNTRY
ID NAME PERSON CITIES
1 Argentina 1 10
2 Canada 2 20
3 Cuba 2 10
4 Germany 1 0
5 France 7 5
STAFF
ID LASTNAME
1 Jones
2 Smith
How many rows would be returned using the following statement?
SELECT * FROM staff, country
0
2
5
7
10
65. Given the following statements:
CREATE TABLE tab1 (c1 CHAR(3) WITH DEFAULT '123',c2 INTEGER);
INSERT INTO tab1(c2) VALUES (123);
Which will be the result of the following statement when issued from the Command Line Processor?
SELECT * FROM tab1;
C1 C2
--- -----------
0 record(s) selected.
C1 C2
--- -----------123 123
1 record(s) selected.
C1 C2
--- -----------
123
1 record(s) selected.
C1 C2
--- -----------
- 123
1 record(s) selected
66. Which of the following can be used to store images in a DB2 database?
DB2 AVI Extender
DB2 XML Extender
DB2 Test Extender
DB2 Spatial Extender
67. A table called EMPLOYEE has the following columns: name, department, and phone_number. Which of the following can limit read access to the phone_number column?
Using a view to access the table
Using a referential constraint on the table
Revoking access from the phone_number column
Defining a table check constraint on the table
68. Given the requirement of providing a read-only database, applications accessing the database should be run with which of the following isolation levels to allow for the most read concurrency?
Read stability
Repeatable read
Cursor stability
Uncommitted read
69. If, for a given table, the Control Center does not show the choice Generate DDL, which of the following describes the reason?
The table is a system object.
The table is a summary table.
The table is in LOAD PENDING.
The table is a replicated table.
The table was created by a different user.
70. An update lock gets released by an application using the repeatable read isolation level during which of the following?
If the cursor accessing the row is closed.
If the transaction issues a ROLLBACK statement.
If the cursor accessing the row is moved to the next row.
If the transaction changes are made via an UPDATE statement.
71. Given the requirements to store names, employee numbers, and when the employees were hired, which of the following DB2 data types CANNOT be used to contain the day the employee was hired?
CLOB
TIME
VARCHAR
TIMESTAMP
72. Given the successfully executed embedded SQL:
INSERT INTO staff VALUES (1, 'Colbert', 'Dorchester', 1)
COMMIT
INSERT INTO staff VALUES (6, 'Anders', 'Cary', 6)
INSERT INTO staff VALUES (3, 'Gaylord', 'Geneva', 8)
ROLLBACK WORK
Which of the following indicates the number of new rows that would be in the STAFF table?
0
1
2
3
73. Which of the Following is to call Stored Procedures?
CALL P (ABC)
CALL P (‘ABC’)
CALL PROCEDURE P (‘ABC’)
CALL P (“ABC”)
74. Which of the following SQL data types should be used to store small binary image?
CLOB
BLOB
DBCLOB
GRAPHIC
VARCHAR
75. CREATE DISTINCT TYPE kph AS INTEGER WITH COMPARISONS
CREATE DISTINCT TYPE mph AS INTEGER WITH COMPARISONS
CREATE TABLE speed_limits
(route_num SMALLINT,
canada_sl KPH NOT NULL,
us_sl MPH NOT NULL)
SELECT route_num FROM speed_limits WHERE canada_sl > 80
SELECT route_num FROM speed_limits WHERE canada_sl > kph
SELECT route_num FROM speed_limits WHERE canada_sl > us_sl
SELECT route_num FROM speed_limits WHERE canada_sl > kph(80)
76. Which tool is used to run stored procedures
development Centre
tool Centre
command Centre
Control Centre
77. The column C1 in T1 is char(3), It has both uppercase and lowercase letters. To get all strings containing 'ABC' in C1 which option is used
WHERE C1 = 'ABC'
WHERE C1 = UCASE ('ABC')
WHERE C1 = IGNORE_CASE('ABC')
WHERE C1 = 'ABC' with option case insensitive
