1. Which of the following is NOT true?
  2. Structured Query Language is the standard language for manipulating data in a Relational Database management System.
  3. Database Management Systems act as interface used to communicate with databases.
  4. The Hierarchical data model organizes data tables with columns and rows
  5. DB2 is a Database Management System
  1. When creating a DB2 database, which of the following tables spaces is NOT automatically created?
  2. User table space
  3. User temporary table space
  4. Catalog table space
  5. System temporary table space
  1. Given the following table definition:

SALES

SALES_DATEDATE

DALES_PERSONCHAR(20)

REGIONCHAR(20)

SALESINTEGER

Which of the following SQL statements will remove all sales that occurred in 1995?

  1. DELETE * FROM sales WHERE YEAR(sales_date) = 1995
  2. DELETE FROM sales WHERE YEAR(sales_date) =1995
  3. REMOVE * FROM sales WHERE YEAR(sales_date) = 1995
  4. REMOVE FROM sales WHERE YEAR(sales_date) = 1995
  1. Which of the following is a reason for using databases to store data INSTEAD of the many alternatives?
  2. Databases store data in an unstructured way for faster insert performance.
  3. Databases are cheaper to use than other alternatives.
  4. Databases are an intrinsic functionality of most Operating Systems, thus its widely available to applications.
  5. Databases allow multiple users to handle and change the data without losing data and its consistency.
  1. Consider a Stored Procedure with the following definition:

CREATE OR REPLACE PROCEDURE proc1 (p1 INTEGER, OUT p2 VARCHAR(20))

BEGIN

---

END

@

Using DB2 CLP, which of the following commands would successfully execute procedure PROC1?

  1. CALL proc1 (1, DEFAULT)
  2. CALL proc1 (1,?)
  3. CALL proc1( 1 )
  4. None of the above
  1. Which of the following programming languages can be used to develop applications that require access to DB2 database?
  2. JAVA
  3. PHP
  4. C#
  5. All of the above
  1. Application A is running under 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?
  2. REPEATABLE READ
  3. READ STABILITY
  4. CURSOR STABILITY
  5. UNCOMMITTED READ
  1. Consider the following scenario. We have a table, table_1, which has only one column storing an integer value. The following statements are executed successfully in the orders shown:

CREATE SEQUENCE my_seq START WITH 1 INCREMENT BY 1 CACHE 5

INSERT INTO table_1 VALUES (NEXT VALUE FOR my_seq);

INSERT INTO table_1 VALUES (NEXT VALUE FOR my_seq);

After a db2 terminate and reconnection to the database, the following statement was issued:

INSERT INTO table_1 VALUES (NEXT VALUE FOR my_seq);

SELECT * FROM table_1

What value will be the last value returned by the query?

  1. 1
  2. 2
  3. 3
  4. 6
  1. Consider table TB1 defined as below:

CREATE TABLE TB1 (

C1 INTEGER PRIMARY KEY,

C2 VARCHAR(10))

Considering the following statements are successfully executed in CLP with auto-commit disabled, how many rows are INSERTED in table TB1.

INSERT INTO TB1 VALUES (1, ‘JOHN’)

INSERT INTO TB1 VALUES (1, ‘JOHN’)

COMMIT

INSERT INTO TB1 VALUES (1, ‘JOHN’)

ROLLBACK

  1. 0
  2. 1
  3. 2
  4. 3
  1. Which of the following is true about Relational Databases and the Relational Data Model?
  2. A table does not allow duplicates and a relation does.
  3. A domain is the smallest unit of data.
  4. A column is the equivalent to a tuple.
  5. A domain is the set of some possible values for a specific field.
  1. Considering the SQL statement below, which option best describes what ABS is:

UPDATE tb1 SET col1 = ABS(col2)

  1. Stored Procedure
  2. Built-in function
  3. Trigger
  4. User Defined Type
  1. Which of the following statements is FALSE about transactions?
  2. Operations inside a transaction are executed in parallel.
  3. Only 1 COMMIT statement can be executed inside the same transaction.
  4. Transactions guarantee atomicity of a group of operations.
  5. A ROLLBACK OR COMMIT statement finalizes a transaction.
  1. Which of the following is true about Static SQL?
  2. It takes advantage of the table statistics at runtime.
  3. The Structured of the SQL statement must be completely specified at the pre-compilation phase.
  4. It is compiled and executed by an application at run-time.
  5. Static SQL does not exist.
  1. Which of the following privileges permits a user to update the comments associated with a table?
  2. ALTER
  3. CONTROL
  4. CREATEIN
  5. REFERENCES
  1. Which of the following statement is FALSE regarding BUFFER POOLS?
  2. Buffer pools work as a cache, storing data that is read from table spaces.
  3. Pages in a Buffer Pool can be 4k, 8k, 16k and 32k in size.
  4. A database can only have one Buffer Pool.
  5. The size of a Buffer Pool can be automatically resized by STMM.
  1. If circular logging is used the following types of backup are allowed.
  2. Online full backup
  3. Offline full backup
  4. Online incremental backup
  5. Offline incremental backup
  1. Which of the following events will NOT cause a trigger to be activated?
  2. DELETE
  3. SELECT
  4. INSERT
  5. UPDATE
  1. Which of the following tools will give you the best performance to import large amounts of data into DB2 table?
  2. IMPORT
  3. LOAD
  4. FAST IMPORT
  5. BULK INSERT
  1. Given following two tables :

TAB1

------

COL_1COL_2

------

A 10

B 12

C 14

TAB2

------

COL_ACOL_B

------

A 21

C 23

D25

Assuming the following results are desired:

COL_1 COL_2 COL_A COL_B

A 10 A 21

B 12 - -

C 14 C 23

Which of the following joins will produce the desired results?

  1. SELECT * FROM tab1 INNER JOIN tab ON col_1 = col_a
  2. SELECT * FROM tab1 LEFT OUTER JOIN tab2 ON col_1 = col_a
  3. SELECT * FROM tab1 RIGHT OUTER JOIN tab2 ON col_1 = col_a
  4. SELECT * FROM tab1 FULL OUTER JOIN tab2 ON col_1 = col_a
  1. What are the advantages of using Stored Procedures?
  2. Reduce network traffic
  3. Access to features that exist only on the server
  4. Enforcement of the business rules
  5. All of the above
  1. Which of the following is true about Table Spaces in DB2?
  2. A Table Space is a file managed by the operating system where DB2 can store data.
  3. A Table Space is always associated with a buffer pool.
  4. A Table Space to store user’s temporary table data is automatically created when a database is created.
  5. A Table Space can be managed by the database (DB2) or by an external application.
  1. Buffer pools are associated with table spaces so that they can increase performance by caching data in memory. Which of the following is true?
  2. A Table Space can only be associated with a buffer pool that has the same page size.
  3. A table space can only be associated with a buffer pool that has a smaller or same page size.
  4. A table space can only be associated with a buffer pool that has a larger or same page size.
  5. A table space can only be associated with a buffer pool that larger page size.
  1. Which of the following is NOT a DML statement?
  2. SELECT
  3. INSERT
  4. ALTER
  5. UPDATE
  1. Which of the following options best describes a domain found in the table below?

CREATE TABLE EMPLOYEE (

ID INTEGER NOT NULL,

NAME VARCHAR(30) NOT NULL,

EXTENSION INTEGER NOT NULL,

MANAGER VARCHAR(30) NOT NULL

PRIMARY KEY (ID));

TERMINATE;

IDNAMEEXTENSIONMANAGER

1 John S 53412 Y

2 Susan P 54123 N

3 Jennifer L 51234N

  1. MANAGER DOMAIN = (N)
  2. NAME DOMAIN = (Set of all Possible Names)
  3. EXTENSION DOMAIN = (53412)
  4. ID DOMAIN = (123)
  1. Assuming that you are currently connected to a DB2 database, which of the following commands will terminate your connection?
  2. db2 connect reset
  3. db2 connect end
  4. db2 connect kill
  5. db2 connect finalize
  1. When does authentication takes place?
  2. Just before DB2 executes a SQL statement sent by a client application.
  3. When the user connects to the database
  4. When the user disconnects from a database
  5. It is part of DB2 SERVER startup process
  1. Which of the following tools can be used to develop stored procedures?
  2. DB2 CLP
  3. DB2 Command Editor
  4. IBM Data Studio
  5. All of the above
  1. Which of the following is NOT a Feature pack that can add extra functionality to a DB2 server?
  2. Advanced Access Control
  3. Storage Optimization
  4. Performance Optimization
  5. PureXML
  1. Giving the following create table statement:

CREATE TABLE student_marks

(student_name CHAR(20) NOT NULL,

Student_number INTEGER NOT NULL,

math INTEGER,

physINTEGER,

eng1Integer)

Which of the following statements will retrieve the student’s name who does NOT have a

phys mark?

  1. SELECT student_name FROM student_marks WHERE phys =’ ‘
  2. SELECT student_name FROM students_marks WHERE phys =’NULL’
  3. SELECT student_name FROM students_marks WHERE phys =0
  4. SELECT student_name FROM student_marks WHERE phys is NULL
  1. Given the following tables:

CURRENT_EMPLOYEES

------

EMPID INTEGER NOT NULL

NAMECHAR(20)

SALARYDECIMAL(10,2)

ENABLEDCHAR(1)

PAST_EMPLOYEES

------

EMPIDINTEGER NOT NULL

NAMECHAR (20)

SALARYDECIMAL (10,2)

Assuming PAST_EMPLOYEES is empty and CURRENT_EMPLOYEES is populated, which of the following statements will copy all past employees (enabled column is ‘N’) FROM CURRENT_EMPLOYEES to table PAST_EMPLOYEES?

  1. INSERT INTO past_employees VALUES (current_employees WHERE ENABLED =’N’)
  2. INSERT INTO past_employees (SELECT empid, name , salary FROM current_employees)
  3. INSERT INTO past_employees (SELECT * FROM current_employees WHERE ENABLED =’N’
  4. INSERT INTO past_employees(SELECT empid, name , salary FROM current_employees WHERE ENABLED in (‘N’))
  1. Which of the following is a characteristic of High Availability?
  2. It allows compression of Data
  3. It allows definition of a policy to guarantee quality of service to applications.
  4. It allows replication of data to a stand by server that can take over in case of failure.
  5. It allows high performance of a B2 server by distributing workload across several machines.
  1. In DB2 9.7, which of the following functions can be used to create tables from XML documents?
  2. XMLTABLE
  3. TABLEXML
  4. XML2RELATIONAL
  5. XML2TABLE
  1. Which of the following is NOT a SQL/XML function in DB2 9.7?
  2. XMLQUERY
  3. XMLEXISTS
  4. EXISTS
  5. XMLCAST
  1. Which of the following is NOT true about keys in the relational model?
  2. Primary keys are defined by a subset of attributes of a relation.
  3. Primary keys uniquely identify each tuple of the relation.
  4. Primary keys define the relationship between two tables.
  5. A relation can have multiple foreign keys.
  1. What is the default isolation level in BD2 9.7 for Linux, Unix and Windows for new databases?
  2. Cursor Stability
  3. Cursor Stability with Currently Committed Semantics
  4. Uncommitted Read
  5. None of the above
  1. Which of the following statements revoke access to SAMPLE database from user TOM?
  2. REVOKE CONNECT ON sample FROM USER tom
  3. REVOKE ACCESS FROM USER tom ON DATABASE
  4. REVOKE ACCESS FROM USER tom ON sample]
  5. REVOKE CONNECT ON DATABASE FROM USER tom
  1. The authentication method in a DB2 server is defined within:
  2. The database configuration
  3. The system’s catalog
  4. The DB2 registry
  5. The database manager configuration
  1. which of the following options can be used to check the minimum point in time (PIT) for a table space restore?
  2. LIST TABLESPACES SHOW ALL
  3. RESTORE DATABASE SAMPLE TABLEPSPACE (MYTBSP) FROM/tbsbkp
  4. RESTORE DATABASE SAMPLE TABLESPACE (MYTBSP) ONLINE FROM/tbsbkp
  5. GET SNAPSHOT FOR TABLESPACE ON <database>
  1. Which of the following is characteristics difference between Cursor Stability and Currently Committed?
  2. Reader blocks Reader
  3. Reader blocks Writer
  4. Writer blocks Reader
  5. Writer blocks Writer
  1. two tables are created by the following statement

CREATE TABLE color

( c_id INTEGER

NOT NULL PRIMARY KEY.

C_desc CHAR(20))

CREATE TABLE object

( o_id CHAR(2),

o_desc CHAR(20).

c_idINTEGER

REFERENCES COLOR (c_id)

ON DELETE CASCADE)

Which option correctly describes the change on two tables after the statement below is executed;

DELETE FROM color WHERE c_id = 2

  1. Only rows in table COLOR can be affected
  2. Only rows I table OBJECT can be affected
  3. Rows in both color and object with c_id = 2 will be deleted
  4. No row will be deleted
  1. Given the following table;

GUESTS

------

nameage

------

Jenny34

Rodney36

Oliver21

Angie42

Tom28

Vincenzo25

What names would be displayed as the result of the following SQL query

SELECT name FROM GUESTS WHERE name LIKE ‘%o_’

  1. Oliver, Rodney, Tom, Vincenzo
  2. Rodney, Tom
  3. Tom
  4. Oliver, Rodney, Tom
  1. Which of the following is NOT true Relational Databases?
  2. Each column contains a specific type of information
  3. Columns are where the individual pieces of information are stored
  4. A primary key can be composed of only one attribute
  5. Foreign keys are an attribute in one relation whose values match a primary key of another relation.
  1. Which Data Model was created with the focus of providing better data independence?
  2. Semi-structured
  3. Hierarchical
  4. Relational
  5. Entity-Relationship
  1. Given the options below, which of the following statements can remove a table from a database?
  2. REMOVE
  3. DELETE
  4. DEL
  5. DROP
  1. Your company has asked you to create a database to store and organize data about the structure of the company. You want to store branch and employees data as well data on the products that each employee in your company deals with. In order to reduce redundancy, how many tables should you create? Consider that your model should be tailored to reduce the possible number of NULL values in the table(s).
  2. 1
  3. 2
  4. 3
  5. 4
  1. which of the following tools would you need to execute SQL statements against as DB2 database from command line?
  2. DB2 Health Center
  3. DB2 Command Line Processor
  4. DB2 Command Line Advisor
  5. DB2 Control Center
  1. in embedded SQL, which of the following is an example of how to establish a connection to the SAMPLE database in DB2/
  2. EXEC SQL CONNECT TO SAMPLE
  3. DB2 CONNECT TO DB SAMPLE
  4. EXEC CONNECT TO SAMPLE
  5. EXEC DB2 CONNECT TO DATABASE SAMPLE
  6. If secondary log files are to be allocated until the unit of work commits or storage is exhausted, which type of logging is used?
  7. Circular logging
  8. Archival logging
  9. Infinite logging
  10. It cannot be done in DB2
  1. Which of the following statements is TRUE?
  2. The owner of the table does not automatically receive CONTROL privilege for that table.
  3. The owner of the table automatically receives CONTROL privilege, but does not automatically receive all other table privileges available for that table.
  4. If the CONTROL privilege is revoked from the table owner, all other privileges that were automatically granted to the owner when the table was created are automatically revoked.
  5. If the CONTROL privilege is revoked from the table owner, all other privileges that were automatically granted to the owner when the table was created need to be explicitly revoked.
  1. Which of the following installation packages does NOT contain the GUI administration tools?
  2. DB2 Data Server Enterprise Edition
  3. DB2 Data Server Express-C Edition
  4. IBM Data Server Client
  5. IBM Data Server Runtime
  1. which of the following privileges permits a user to create objects within a schema?
  2. CONTROL
  3. ALTER
  4. REFERENCES
  5. CREATEIN
  1. Assume a table which contains the following columns:

EMP_ID

EMP_NAME

PHONE

EMAIL

SALARY

Which of the following is the simplest way to restrict users from viewing SALARY information, while still allowing then to see the other values?

  1. Encrypt the tables data
  2. Create a view that does not contain the SALARY column Grant access to the view and

revoke access from the original table

  1. revoke SELECT access for the SALARY column from users who should not see SALARY data
  2. Store SALARY data in a separate table and grant SELECT privilege for that to the

appropriate users

  1. Whichof the following is true about Relational Databases?
  2. A column can store values of different data types
  3. A table consists of columns and rows.
  4. Rows in the same table can have a different set of columns.
  5. Rows are also known as fields of table.
  1. given the following two tables:

TAB1

------

COL_1COL-2

------

A10

B12

C14

TAB2

------

COL_ACOL_B

------

A21

C23

D25

Assuming the following results are desired:

COL_1COL_2COL_ACOL_B

A10A21

B12__

C14C23

__D25

Which of the following joins will produce the desired results?

  1. SELECT * FROM tab1 INNER JOIN tab2 ON col_1 = col_a
  2. SELECT * FROM tab1 LEFT OUTER JOIN tab2 ON col_1 = col_a
  3. SELECT * FROM tab1 RIGHT OUTER JOIN tab2 ON col_1 = col_a
  4. SELECT * FROM tab1 FULL OUTER JOIN tab2 ON col_1 = col_a
  1. Which Data Model was created with the focus of providing better data independence?
  2. Relational
  3. Hierarchical
  4. Semantic
  5. Network
  1. Assuming that you are currently connected to TESTDB, which of the following will allow the view of the database settings with details?
  2. Db2 get db cfg with detail
  3. Db2 get db cfg show detail
  4. Db2 get dbm cfg with detail
  5. Db2 get dbm cfg show detail
  1. Which of the following is true about columns?
  2. Each column consists of one or more records.
  3. Columns are where the individuals pieces of information are stored for each record
  4. Column must be designated a specific data type
  5. Columns are also known as records
  1. What is a Trusted Context?
  2. It is a special area in a buffer pool that can be written only by a selected set of users.
  3. It is a DB2 capability that allows application to change users without breaking the connection to the database.
  4. It is a DB2 capability that allows users to establish a connection to the database without providing use name or password.
  5. It is a type container in a table space that allows faster I/O operations.
  1. What is the purpose of a DB2 Access Pan?
  2. SQL DEVLOPERS CAN DEFINE Access Plans to tell DB2 the best way to retrieve the data from SQL query
  3. Describes the order of operations to access data necessary to execute a SQL or XQuery statement
  4. To replicate data between a DB2 database and relational databases from other vendors
  5. To visually construct complex DML statements and examine the results of their execution

1.C 2.B 3. B 4. D 5. B 6.A 7. D 8.D 9.C 10.D 11.B 12.B 13.B 14.A 15. C 16.B

17.B 18.B 19.B 20.D 21.B 22.A 23.C 24.B 25.A 26.B 27.C 28.D 29.D 30.D

31.C 32.A 33.C 34.C 35.B 36.A 37.D 38.B 39.D 40.A 41.C 42.B 43.C 44.D

45.C 46.B 47.B 48.C 49.D 50.D 51.D 52.B 53.B 54.D 55.A 56.B 57.C 58.B

59.B