This week's book giveaway is in the OCPJP forum.
We're giving away four copies of OCA/OCP Java SE 7 Programmer I & II Study Guide and have Kathy Sierra & Bert Bates on-line!
See this thread for details.
The moose likes Product and Other Certifications and the fly likes DB2 700 and 701 - Test Sample Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of OCA/OCP Java SE 7 Programmer I & II Study Guide this week in the OCPJP forum!
JavaRanch » Java Forums » Certification » Product and Other Certifications
Bookmark "DB2 700 and 701 - Test Sample" Watch "DB2 700 and 701 - Test Sample" New topic
Author

DB2 700 and 701 - Test Sample

Dan Zhang
Greenhorn

Joined: Feb 17, 2005
Posts: 9
Last week, I passed DB2-700(92%) and DB2-701(76%)

I plan to prepare for DB2-703.Where can I find Test Sample or Mocks. THX.!!

DB2 UDB Test Sample 700

To set up a client that can access DB2 UDB through DB2 Connect Enterprise Edition, which of the following is the minimum software client that must be installed?
A.DB2 Runtime Client
B.DB2 Personal Edition
C.DB2 Administration Client
D.DB2 Application Development Client
A

A client application on OS/390 or OS/400 must access a DB2 server on AIX. At a minimum, which of the following products is required to provide DRDA Application Server functionality on the DB2 server?
A.DB2 Connect Enterprise Edition
B.DB2 UDB Workgroup Server Edition
C.DB2 Connect Enterprise Edition and DB2 UDB Workgroup Server Edition
D.DB2 Connect Enterprise Edition and DB2 UDB Enterprise Server Edition}
B

A developer is building a Windows embedded SQL application that will access DB2 UDB for OS/390 or OS/400 servers. Which of the following products is required to be installed on the Windows system in order to build the application?
A.DB2 UDB Personal Edition
B.DB2 Connect Personal Edition
C.DB2 Personal Developer's Edition
D.DB2 UDB Workgroup Server Edition
C

Which of the following tools can be used to identify inefficient SQL statements without executing them?
A.QMF大机
B.Task Center
C.Visual Explain
D.Development Center
C

Which of the following tools maintains a history of all executed statements/commands for the current session within the tool?
A.Journal
B.SQL Assist
C.Health Center
D.Command Center
D

Which of the following products is designed for analyzing data with more than two dimensions?
A.DB2 OLAP Server
B.DB2 Warehouse Manager
C.DB2 Relational Connect
D.DB2 Data Links Manager
A

Which of the following tools is used to create and build stored procedures?
A.SQL Assist
B.Task Center
C.Development Center
D.Replication Center
C

Which of the following tools is used to create subscription sets 复制预约and add subscription-set members to subscription sets?
A.Journal
B.License Center
C.Replication Center
D.Development Center
C

Which two of the following types of storage management methods are supported by DB2 OLAP Server?
A.Object
B.Network
C.Relational
D.Hierachical
E.Multi-dimensional
CE

Which of the following products can be used to perform a dictionary-based search?
A.DB2 XML Extender
B.DB2 AVI Extender
C.DB2 Text Extender
D.DB2 Spatial Extender
C

Which of the following extenders allows data to be presented in a three-dimensional format?
A.DB2 AVI Extender
B.DB2 XML Extender
C.DB2 Text Extender
D.DB2 Spatial Extender
D
When establishing client-server communication, which two of the following can be used to verify passwords?
A.Catalog Tables
B.Access Control List
C.Application Programs
D.DRDA Application Server
E.Client Operating System
DE

Given the following users and groups with no privileges on table t1:
GroupA GroupB
------ ------
user1 user4
user2 user5
user3
Which of the following commands gives all users in the above groups the ability to create a view on table t1?
A.GRANT SELECT ON TABLE t1 TO ALL
B.GRANT SELECT ON TABLE t1 TO PUBLIC
C.GRANT REFERENCES ON TABLE t1 TO ALL
D.GRANT SELECT ON TABLE t1 TO USER GroupA, GroupB
B
A table called EMPLOYEE has the following columns:
NAME
DEPARTMENT
PHONE_NUMBER
Which of the following will allow USER1 to modify the PHONE_NUMBER column?
A.GRANT INDEX (phone_number) ON TABLE employee TO user1
B.GRANT ALTER (phone_number) ON TABLE employee TO user1
C.GRANT UPDATE (phone_number) ON TABLE employee TO user1
D.GRANT REFERENCES (phone_number) ON TABLE employee TO user1
C

An administrator issues:
GRANT ALL PRIVILEGES ON TABLE appl.tab1 TO user1 WITH GRANT OPTION
Which of the following statements is USER1 authorized to execute?
A.GRANT DROP ON TABLE appl.tab1 TO user8
B.GRANT OWNER ON TABLE appl.tab1 TO user8
C.GRANT INSERT ON TABLE appl.tab1 TO user8
D.GRANT CONTROL ON TABLE appl.tab1 TO user8
C
A user defined function named F.FOO has an input parameter of an integer. USER4 executes the following SQL statement:
SELECT col1, col2 FROM t.tab1 WHERE f.foo(col1) < 6;
Which of the following statements grants USER4 the privilege needed to be able to execute the user defined function?
A.GRANT USE ON FUNCTION f.foo(INT) TO user4
B.GRANT SELECT ON FUNCTION f.foo(INT) TO user4
C.GRANT EXECUTE ON FUNCTION f.foo(INT) TO user4
D.GRANT REFERENCES ON FUNCTION f.foo(INT) TO user4
C

Which of the following will grant just DML operations on table T.T4 to all users?
A.GRANT ALL PRIVILEGES ON t.t4 TO PUBLIC
B.GRANT ALL PRIVILEGES ON t.t4 TO ALL USERS
C.GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE t.t4 to PUBLIC
D.GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE t.t4 TO ALL USERS
C

What does the following statement do?
GRANT REFERENCES(col1) ON TABLE t.t1 TO user7
A.Gives USER7 the ability to refer to column COL1 of table T.T1 in views or select statements
B.Gives USER7 the ability to refer to column COL1 of table T.T1 on user-defined function calls
C.Gives USER7 the ability to define referential integrity on table T.T1 using column COL1 as the parent key
D.Gives USER7 the ability to define referential integrity on table T.T1 using column COL1 as the foreign key
C

Communications is being manually established from a Windows 2000 client through a DB2 Connect gateway to a DB2 host system. Which of the following does NOT have to be cataloged on the gateway?
A.The client
B.The node where the data source resides
C.The data source on the DB2 database server
D.The Database Connection Service (DCS) database
A

A database administrator has supplied the following information:
� Protocol: TCP/IP
� Port Number: 446
� Host Name: ZEUS
� Database Name: SAMPLE
� Database Server Platform: OS/400
Which are the appropriate commands to set up the ability to connect to the database?
A.CATALOG TCPIP NODE zeus REMOTE zeus SERVER 446 OSTYPE os400 DATABASE sample;
B.CATALOG TCPIP DATABASE sample REMOTE zeus SERVER 446 OSTYPE os400;
CATALOG DATABASE dcssam AS sample AT NODE zeus AUTHENTICATION dcs;
C.CATALOG TCPIP NODE zeus REMOTE zeus SERVER 446 OSTYPE os400;
CATALOG DCS分布式连接服务 DB dcssam AS sample;
CATALOG DATABASE dcssam AS sample AT NODE zeus AUTHENTICATION dcs;
D.CATALOG TCPIP NODE sample REMOTE sample SERVER 446 OSTYPE os400;
CATALOG DCS DB sample AS dcssam;
CATALOG DATABASE dcssam AS sample AT NODE zeus AUTHENTICATION dcs;
C

Given the following:
CREATE TABLE tab1 (c1 char(3) WITH DEFAULT NULL, c2 INTEGER);
INSERT INTO tab1(c2) VALUES (345);
What will be the result of the following statement if issued from the Command Line Processor?
SELECT * FROM tab1;
A.C1 C2
--- -----------
0 record(s) selected.
B.C1 C2
--- -----------
123 345
1 record(s) selected.
C.C1 C2
--- -----------
345
1 record(s) selected.
D.C1 C2
--- -----------
- 345
1 record(s) selected.
D


IBM Certified Database Administrator - DB2 V8.1 for Linux UNIX and Windows <br />IBM Certified Database Associate � DB2 Universal Database V8.1 Family<br />IBM Certified Solution Developer - WebSphere Studio, V5.0<br />IBM Certified Associate Developer - WebSphere Studio, V5.0<br />IBM certification XML and related technologies<br />Sun Certified Web Component Developer for the Java 2 Platform<br />Sun Certified Programmer for the Java 2 Platform, Standard Edition 1.4
Dan Zhang
Greenhorn

Joined: Feb 17, 2005
Posts: 9
DB2 UDB Test Sample 701

1.To allow incoming connections via TCP/IP to an instance, which of the following must be set on the server?
A.The node directory should have an entry for the instance.
B.The value of the DB2COMM registry variable for the instance should include TCPIP.
C.The value of the AUTHENTICATION parameter for the instance should be set to SERVER.
D.The value of the SVCENAME parameter for the instance should be set to the TCP/IP host name of this machine.
B

2.DB2 Enterprise Server Edition (ESE) is running on Linux and needs to validate the userids and passwords on the z/OS server for the DB2 clients connecting to DB2 for z/OS. Which of the following authentication levels satisfies this while providing authentication for other DB2 clients at the DB2 ESE server?
A.DCS
B.DRDA
C.HOST
D.CLIENT
E.SERVER
A
3.To permit all users from any DB2 UDB client to connect to the database, which of the following is required in the database's instance?
A.Set trust_clntauth = Client, trust_allclnts=No
B.Set trust_clntauth = Server, trust_allclnts=No
C.Set trust_clntauth = Client, trust_allclnts=Yes
D.Set trust_clntauth = Server, trust_allclnts=Yes
C
4.In which of the following locations must userids and passwords be defined if using authentication SERVER?
A.The node directory on the server
B.The system catalog on the server
C.The PASSWORD table in the database
D.The operating system on the server
D
5.Given an application with the embedded static SQL statement:
INSERT INTO admin.payroll (employee, salary) VALUES ("Becky Smith",80000)
Which of the following privileges must a user hold to run the application?
A.ALTER on the table
B.INSERT on the table
C.DBADM on the database
D.EXECUTE on the package
D
6.AUTHENTICATION=SERVER_ENCRYPT allows DB2 to encrypt which of the following?
A.data
B.userid
C.password
D.userid and password
C
7.Which of the following is required to use the IMPORT utility to import data into a table?
A.SYSCTRL authority
B.LOAD authority on the table
C.ALTER privilege on the table
D.IMPORT authority on the table
E.INSERT privilege on the table
E
8.Which two of the following identify which users have SYSCTRL authority?
A.The node directory
B.The system catalog
C.The database configuration
D.The operating system security
E.The database manager configuration
DE
9.Which of the following can be done by a user who is granted the CONTROL privilege on an INDEX?
A.Drop the index
B.Alter the index
C.Add columns to the index
D.Create an index extension on the index
A
10.Which of the following actions will occur when issuing the command FORCE APPLICATION ALL?
A.No new database connections are allowed
B.Uncommitted units of work are committed
C.Uncommitted units of work are rolled back
D.Disconnect warning messages are sent to connected users
C
11.Which of the following must be set to restrict clients from being able to discover any DB2 instances on a server?
A.DISCOVER_DB parameter to DISABLE
B.DISCOVER_INST parameter to DISABLE on a DB2 instance
C.DB2 Administration Server configuration parameter SEARCH to DISABLE
D.DB2 Administration Server configuration parameter DISCOVER to DISABLE
D
12.When scheduled using the Task Center, all of the following actions can be specified to take place upon completion or failure of the task, EXCEPT:
A.Create a new task.
B.Send an email or page.
C.Cancel other scheduled tasks.
D.Re-run the task that just completed.
A
13.Given the following notification message:
*
2002-02-05-03.14.38.559911 Instance b2inst1 Node:000
PID:89198(db2agent (DB2PROD ))
Appid:*LOCAL.db2inst1.020205091435
buffer pool services sqlbStartPoolsErrorHandling Probe:39
ADM6080E The tablespace "ID" (ID "3"), was put OFFLINE and in
ROLLFORWARD_PENDING. Tablespace state is 0x"00004080". 1

*
Which database does this message apply to?
A.ID
B.DB2PROD
C.db2inst1
D.db2agent
B
14.Given the following command:
CREATE DATABASE inventory
CATALOG TABLESPACE
MANAGED BY SYSTEM USING ('path1','path2')
EXTENTSIZE 16 PREFETCHSIZE 32
USER TABLESPACE
MANAGED BY SYSTEM USING ('patha')
TEMPORARY TABLESPACE
MANAGED BY DATABASE USING ( FILE 'filea' 1000)
WITH "EXTENTSIZE 8"
With which of the following buffer pools is the catalog table space associated?
A.IBMUSERBP
B.IBMTEMPBP
C.IBMCATALOGBP
D.IBMDEFAULTBP
D
15.Given the following command:
CREATE DATABASE inventory
CATALOG TABLESPACE
MANAGED BY SYSTEM USING ('path1','path2')
EXTENTSIZE 16 PREFETCHSIZE 32
USER TABLESPACE
MANAGED BY SYSTEM USING ('patha')
TEMPORARY TABLESPACE
MANAGED BY DATABASE USING ( FILE 'filea' 1000)
WITH "EXTENTSIZE 8"
How many bufferpools are in the inventory database?
A.0
B.1
C.2
D.3
E.4
B
16.Given the following statements:
CREATE TABLE T1 (COL1 INT NOT NULL PRIMARY KEY, COL2 CHAR, COL3 CLOB (40K), COL4 VARCHAR(10));

CREATE UNIQUE INDEX IND_1 ON T1 (COL1, COL2) INCLUDE (COL3) ALLOW REVERSE SCANS;
The CREATE UNIQUE INDEX statement will fail because:
A.LOB columns cannot be used in an index.
B.A unique index cannot include an existing primary key.
C.Reverse scans are not supported on multi-column indexes.
D.INCLUDE columns are not supported in indexes that support REVERSE SCANS.
A
17.Given the following DDL statements:
CREATE TABLE person OF person_t
(REF IS OID USER GENERATED)
CREATE TABLE emp OF emp_t UNDER person
INHERIT SELECT PRIVILEGES
CREATE TABLE student OF student_t UNDER person
INHERIT SELECT PRIVILEGES
Which of the following will drop all tables associated with the hierarchy?
A.Dropping the PERSON table
B.Dropping the table hierarchy called PERSON
C.Deleting all rows from the person hierarchy
D.Dropping all columns from the subtables STUDENT and EMP
B
18.Which two of the following objects can be changed with an ALTER statement?
A.Index
B.Trigger
C.Procedure
D.Constraint
E.Table space
DE
19.Given the following command:
CREATE TABLESPACE tbs1
MANAGED BY SYSTEM USING ( 'path1' )
EXTENTSIZE 16 PREFETCHSIZE 32
In a single partition database, how can the amount of storage available to the table space tbs1 be increased?
A.Extend the container
B.Add a container to tbs1
C.Free up disk space on path1
D.Increase the extent size of the table space
C
20.Which of the following kinds of table spaces allows LOBs to use the filesystem cache?
A.An SMS table space
B.A user temporary table space
C.A system temporary table space
D.A DMS table space created with raw devices
A
Dan Zhang
Greenhorn

Joined: Feb 17, 2005
Posts: 9
21.Which of the following allows index data to be stored on separate devices from table data?
A.DMS table spaces
B.SMS table spaces
C.The table space containers defined on different devices
D.A DMS table space for the index data and an SMS table space for the user data
A


22.Given the following statement:
CREATE TABLESPACE dms1 MANAGED BY DATABASE USING
(FILE 'dms01' 1024K)
How many pages will be created for the table space?
A.128
B.256
C.512
D.1024
B
23.Given that there are two containers in table space TS1 and the following statement:
ALTER TABLESPACE ts1
REDUCE (ALL 100 M)
Which of the following describes the result of the statement?
A.The total size of table space TS1 will be reduced to 100 MB.
B.Each container in table space TS1 will be reduced in size to 100 MB.
C.Each container in table space TS1 will be reduced in size by 100 MB.
D.Each container in table space TS1 that has no data will be reduced in size to 100 MB.
E.Each container in table space TS1 that has no data will be reduced in size by 100 MB.
C

24.A DBA has created three related backup tasks in the Task Center. Which of the following tools can be used to edit the tasks?
A.Journal
B.Command Center
C.Control Center
D.Development Center
B
25.Given the following table definitions:
CANDIDATE
---------
CandidateName CHAR(20) NOT NULL
CandidateID INTEGER NOT NULL
Address CHAR(100) NOT NULL
CandidatePhoto BLOB(1M)

TEST_TAKEN
----------
TestName CHAR(50) NOT NULL
TestNumber INTEGER NOT NULL
TestScore INTEGER NOT NULL
CandidateID INTEGER NOT NULL
And the following information:
� Candidate IDs are unique in the CANDIDATE table
� Requirement to select all candidate IDs who have taken test number 701
Assuming no other indexes, which of the following indexes should be created to support the requirement?
A.Primary key on candidate (CandidateID) allowing reverse scans
B.Unique index on candidate (CandidateID) disallowing reverse scans
C.Clustered index on test_taken (CandidateID) allowing reverse scans
D.Index on test_taken (CandidateID, TestNumber) allowing reverse scans
D
26.Which of the following is a benefit of a clustering index?
A.Data compression is allowed in the table
B.Disk space is saved when adding rows to the table
C.Uniqueness of key columns is guaranteed in the table
D.Rows are stored with equal key values close to each other in the table
D





27.What is the purpose for creating a primary key on a table?
A.To ensure duplicate key values are entered
B.To support creation of a table check constraint
C.To support referential integrity between tables
D.To provide free space on each index page for new data
C

28.Given the following table definition:
CREATE TABLE class_list
(studentId INTEGER,
grade INTEGER CONSTRAINT chk CHECK (grade <= 100) NOT ENFORCED
)
and the statement:
INSERT INTO class_list VALUES (98, 100), (123,123), (101, 98)
How many records will be retrieved by the following statement?
SELECT * FROM class_list
A.0
B.1
C.2
D.3
D

29.Which of the following DB2 objects allows multiple users to access data in a table with each user only being able to access certain portions of the data?
A.Views
B.Summary Tables
C.Dimension Tables
D.Table Constraints
A
Dan Zhang
Greenhorn

Joined: Feb 17, 2005
Posts: 9
Hi All,
Could you please provide info on "IBM DB2 703 Exam" Sample Questions/Mock test?
Bala Ram
Greenhorn

Joined: Jan 04, 2006
Posts: 11
Dear Dan Zhang,

I am working in Mainframe platform. I am planning to take IBM-DB2-701 DBA certification. Right now i have no idea abt this exam. Can you please suggest me on
1. How to prepare for this Exam?
2. If possible, send across the material.

Thanks
Bala
Francivalb Lima
Ranch Hand

Joined: Feb 27, 2005
Posts: 42
DB2 UDB 703 - Test Sample

1. Which of the following static SQL statements can use a host variable?

A. FETCH
B. ROLLBACK
C. DROP INDEX
D. ALTER TABLE

2. Which of the following JDBC interfaces contains methods that are called to determine whether or not cursors persist across COMMIT boundaries?

A. ResultSet
B. Statement
C. DatabaseMetaData
D. ResultSetMetaData

3. Which of the following JDBC objects can be used to determine the SQLSTATE if execution of a query fails?

A. Statement
B. ResultSet
C. SQLException
D. ResultSetMetaData

4. Which of the following can be used to access a DB2 database from a workstation that does not have any DB2 code installed?

A. Trigger
B. Java Applet
C. SQL Routines
D. External Stored Procedure

5. Which of the following programming interfaces can be used to program static queries from a Java application?

A. CLI
B. JTA
C. JDBC
D. SQLJ
E. OLEDB

6. Given a cursor C1 on table T1. For every row fetched from T1, open a cursor C2 on table T2. For every row fetched from T2, update column C1 in table T2 and issue a COMMIT.

Which of the following must define cursor C1?

A. DECLARE c1 CURSOR WITH HOLD FOR SELECT * FROM t1
B. DECLARE c1 CURSOR WITH RETURN FOR SELECT * FROM t1

C. DECLARE c1 CURSOR FOR SELECT * FROM t1 FOR UPDATE OF t2
D. DECLARE c1 CURSOR FOR SELECT * FROM t1 FOR UPDATE OF c1


7. Given an ODBC/CLI program with a single connection, two threads and the following actions:

Thread 1: INSERT INTO mytab VALUES (1)
Thread 2: INSERT INTO mytab VALUES (2)
Thread 1: INSERT INTO mytab VALUES (3)
Thread 1: ROLLBACK
Thread 2: INSERT INTO mytab VALUES (4)
Thread 1: COMMIT

Assuming that AUTOCOMMIT is ON, how many records will be successfully inserted into the table MYTAB?

A. 1
B. 2
C. 3
D. 4

8. Given a table that has columns defined:
SMALLINT_COLUMN SMALLINT NOT NULL
VARCHAR_COLUMN VARCHAR(20)

Which of the following statements is used to retrieve rows from the table and allows the second column to be checked for NULL values?

A. FETCH * INTO :hv1, :hv2
B. FETCH CURSOR1 INTO :hv1, :hv2
C. FETCH * INTO :hv1, :hv2 :hv2ind
D. FETCH CURSOR1 INTO :hv1, :hv2 :hv2ind

9. Given the following TWO SQL stored procedures:
Procedure 1:
CREATE PROCEDURE myStorproc(p1 int, p2 char)
language sql
specific spintchar
BEGIN
IF ( P1 = 0 OR P1 = 1 ) THEN UPDATE DUMMY SET COL1 = INT(P2);
END IF ;
END
Procedure 2:
CREATE PROCEDURE myStorproc(p1 int)
language sql
specific spint
BEGIN
IF ( P1 = 0 OR P1 = 1 ) THEN UPDATE DUMMY SET COL1 = P1 ;
END IF ;
END

Which of the following statements will remove Procedure 1 from the database?

A. DROP PROCEDURE myStorproc (p1, p2)
B. DROP PROCEDURE myStorproc (int, char)
C. DROP SPECIFIC PROCEDURE spintchar (p1, p2)
D. DROP SPECIFIC PROCEDURE spintchar (int, char)
E. DROP SPECIFIC PROCEDURE myStorproc (int, char)

10. During which of the following are SQL statements optimized when using DB2 CLI?

A. cursor open
B. precompile phase
C. application binding
D. statement preparation

11. Given the following table and trigger definition:

TABLE: DEPTINFO
DeptID DeptTitle DeptCount
====== ========= =========
1 SALES 5
2 MKTG 2
3 DEVELOPMENT 17

CREATE TRIGGER notify
AFTER UPDATE OF deptid,deptcount ON deptinfo
REFERENCING OLD AS o NEW AS n
FOR EACH ROW DB2SQL MODE
WHEN(o.DeptCount=0 OR n.DeptId<4)
BEGIN
INSERT INTO notifylog VALUES(CURRENT TIMESTAMP,'Check on Dept Changes');
END

How many rows will be inserted into the NOTIFYLOG table as a result of the trigger activations caused by the successful execution of the following set of SQL statements?

UPDATE DeptInfo SET DeptCount = DeptCount+1
UPDATE DeptInfo SET DeptTitle = 'MARKETING' WHERE DeptId=2
UPDATE DeptInfo SET DeptCount = 10 WHERE DeptID=1

A. 2
B. 3
C. 4
D. 5


12. Given the following embedded SQL pseudocode:
Start Program
EXEC SQL BEGIN DECLARE SECTION
USERA CHARACTER (8)
USERB CHARACTER (8)
PW CHARACTER (8)
COLVAL CHARACTER (16)
EXEC SQL END DECLARE SECTION

EXEC SQL INCLUDE SQLCA
EXEC SQL WHENEVER SQLERROR GOTO ERRCHK

(program logic)
(:usera contains the string "usera")
( wa contains a valid password)
(:userb now contains the string "userb")
( wb contains a valid password)

EXEC SQL CONNECT TO samplea USER :usera USING wa
EXEC SQL SELECT col1, col2 FROM tablea

(program logic to retrieve results)

EXEC SQL COMMIT

(more program logic)

EXEC SQL CONNECT TO sampleb USER :userb USING wb
EXEC SQL SELECT col1, col2 FROM tablex
EXEC SQL DELETE FROM tablea WHERE col1= :colval //1st delete
EXEC SQL COMMIT

(more program logic)

EXEC SQL SET CONNECTION samplea
EXEC SQL DELETE from tablea where col1= :colval //2nd delete
EXEC SQL COMMIT

EXEC SQL CONNECT RESET
ERRCHK

(check error information in SQLCA)
(Cleanup)

End Program

What table will be updated in "samplea" if the second DELETE completes successfully?

A. usera.tablex
B. userb.tablea
C. usera.tablea
D. userb.tablex


13. Which of the following ODBC/CLI statement handle attributes permits an application to change the memory address of all bound parameter markers without making any ODBC/CLI API calls?

A. SQL_ATTR_PARAM_BIND_TYPE
B. SQL_ATTR_PARAM_STATUS_PTR
C. SQL_ATTR_PARAM_OPERATION_PTR
D. SQL_ATTR_PARAM_BIND_OFFSET_PTR

14. Given a table created using the statement:
CREATE TABLE abc.stuff (i INT)

A user called XYZ is to be enabled to access data from table ABC.STUFF using an implicit schema.

Assuming the necessary privileges have been granted, which of the following statements issued by user ABC will provide this result?

A. CREATE ALIAS stuff FOR abc.stuff
B. CREATE VIEW abc.stuff FOR xyz.stuff
C. CREATE ALIAS abc.stuff FOR xyz.stuff
D. CREATE VIEW xyz.stuff AS SELECT i FROM abc.stuff


15. Which of the following is required to successfully execute a DB2 Call Level Interface (CLI) application?

A. The user must have EXECUTE privilege on the application.
B. The developer must have EXECUTE privilege on the application.
C. The user must have sufficient privileges on referenced tables.
D. The developer must have sufficient privileges on referenced tables.

16. USERA has authority to access all tables in a database. Which DB2 application development interface should be used to restrict USERA from accessing tables with payroll information during application runtime?

A. CLI
B. JDBC
C. Embedded static SQL
D. Embedded dynamic SQL

17. Given the following:
EXEC SQL BEGIN DECLARE SECTION;
char var1[20];
short var2;
EXEC SQL END DECLARE SECTION;

Which two of the following examples correctly demonstrate the use of host variables within an SQL statement?


(Choose 2)

A. EXEC SQL CONNECT TO var1
B. EXEC SQL FETCH c1 INTO :var1 :var2
C. EXEC SQL UPDATE name_column INTO :var1
D. EXEC SQL SELECT name_column INTO :var1 FROM T1
E. EXEC SQL SELECT name_column INTO :var1, :var2 FROM T1

18. Given that any referenced database objects exist and all host variables have been declared appropriately.

Which two of the following lines of embedded SQL code can be successfully precompiled into the appropriate host-language source?

(Choose 2)

A. EXEC SQL FETCH c1 INTO :var1 :var2 :var3;
B. #sql iter = { SELECT col1 FROM tab1 WHERE col2=:var1 ;
C. SQL UPDATE tab1 SET col1 = :var1 :var2 WHERE col2 = :var3;
D. #sql exec { SELECT col1, col2 FROM tab1 WHERE col2 = :var1 ;
E. EXEC SQL OPEN c1; if (SQLCODE >= 0) EXEC SQL FETCH c1 INTO :var1 :var2;

19. Which of the following is a required parameter for running db2profc?

A. userid
B. server name
C. prep options
D. profile name

20. Assume that the following SQL statements have been successfully issued:
CONNECT TO db1 USER user1 USING pw1
DECLARE GLOBAL TEMPORARY TABLE temp1 (val INTEGER) ...

Which of the following statements will successfully insert a row into the temporary table TEMP1?

A. INSERT INTO db1.temp1 VALUES (100)
B. INSERT INTO user1.temp1 VALUES (100)
C. INSERT INTO session.temp1 VALUES (100)
D. INSERT INTO temporary.temp1 VALUES (100)

21. Given the following table:
CREATE TABLE employee
(name CHAR(10), salary DEC)
INSERT INTO employee (name, salary) VALUES ('SMITH', 30000)
INSERT INTO employee (name) VALUES ('JONES')
INSERT INTO employee (name, salary) VALUES ('ALI', 35000)

Which of the following statements will retrieve the lowest computed value?

A. SELECT SUM(SALARY)/COUNT(*) FROM EMPLOYEE
B. SELECT MIN(SALARY) FROM EMPLOYEE
C. SELECT AVG(SALARY) FROM EMPLOYEE
D. SELECT SUM(SALARY) FROM EMPLOYEE

22. Given the following code from an SQLJ source file:

Connection con = DriverManager.getConnection(url);
DefaultContext ctx = new DefaultContext(con);
DefaultContext.setDefaultContext(ctx);
If an UPDATE statement is issued using the ctx context, which of the following SQLJ methods should be used to obtain the number of rows modified by the UPDATE statement?

A. ctx.rowsUpdated()
B. ctx.SQLRowCount()
C. ctx.getUpdateCount()
D. con.getUpdateCount()

23. An SQLJ application needs to connect to a remote data source SAMPLE using a userid and password. Which of the following will establish the connection?

A. #sql con={CONNECT TO SAMPLE :userid assword
B. getConnection("jdbc b2:SAMPLE",userid,password)
C. getConnection("jdbc b2:SAMPLE:userid assword")
D. #sql con={CONNECT TO SAMPLE USER :userid USING assword

24. Which of the following CLI/ODBC functions will return the number of rows affected by an INSERT, UPDATE or a DELETE statement?

A. SQLNumRows()
B. SQLRowCount()
C. SQLRowsChanged()
D. SQLUpdateCount()

25. Given the SQL statement:
WITH management (name,totalpay,department,location) AS
(
SELECT a.name, (a.salary * :bonusrate), b.deptname, b.location
FROM staff a, org b
WHERE a.dept=b.deptnumb AND a.job='Mgr'
)
SELECT * FROM management WHERE totalpay > :threshold

Which of the following is the above an example of?

A. Derived view
B. Derived table
C. Scalar subselect
D. Common table expression


26. Which of the following must be used by an application to retrieve multiple rows?

A. SQLCA
B. CURSOR
C. SELECT INTO statement
D. COMPOUND SQL statement


MCP: Security, MCSA, MCAD Charter, MCSD, MCSD.NET, MCDBA<br />SCJP 1.4, SCMAD 1.0, SCJA 1.0, ICDA/ICAD - DB2 UDB V8.1<br />ICSD - WMQ V5.3
Dan Zhang
Greenhorn

Joined: Feb 17, 2005
Posts: 9
Thanks Francivalb Lima !!

That's very helpful.

-----------
Hi Bala Ram:

Maybe I can help you a little.
I am a college student in China. I have no any working experience on DB2. I want to gain some database certifications, so I learn DB2.

I just study DB2 2 months and read some books earnest.

1) DB2� Universal Database� v8 for Linux�, UNIX�, and Windows� Database Administration Certification Guide, 5th Edition (2 times)
2)IBM DB2-701 tutorial (1 time)
3)A Chinese Book of DB2 (I forget the name. 1 time)
4)A Chinese flash demo from IBM's Chinese web. (1 time)
5)Using DB2-UDB everyday.
6)Last week , I did some sample tests.

I don't think it is difficult for people who are prepared.I can pass this book by email. If you know Chinese I also can pass the flash demo.

Good Luck!

My email:
Bsspirit@gmail.com
Selvaraja Adikesavan
Greenhorn

Joined: Sep 13, 2006
Posts: 1
Hi All,
I am planning to take DB2 UDB V8.1 Test 700. And I am preparing with Db2 UDB V8.1 Test 700 Study guide by Roger Sanders. Could you suggest me some material for free mock exams?

Thanks.
louie zhang
Greenhorn

Joined: Oct 18, 2006
Posts: 1
hi Zhang Dan,
thanks for your posting.
I just passed 700 and 701. I am planing to try 703. Looks like they are quite different. 703 seems only focus on developement, no admin question will be raised in that test. Am I right?


IBM Websphere Application Server V6.0 Network Deployment, Core Administration<br />IBM Webspere App Serv, V5, Advanced Multiplatform Admin<br />IBM Webspere App Serv, V5, Admin<br />IBM Certified Database Admininstrator-DB2 UDB V8.1 <br />IBM Certified Database Associate-DB2 UDB 8.1 Family<br />Sun Certified Web Component Developer for the J2EE Platform<br />Sun Certified Java 2 Programmer<br />CISCO Certified Network Associate
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: DB2 700 and 701 - Test Sample