• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

db2 - 703 certification

 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi all!
Iam planning to take up db2 - 703 certification by jan 31st . Can anyone post the sample questions?

Regards,
Sivagami
 
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
sivagami gurusamy
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi Francivalb Lima
Thank u very much for your timely help

Regards,
Sivagami
 
Bartender
Posts: 3903
43
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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



What is the correct answer ? C , I guess :roll:
 
He got surgery to replace his foot with a pig. He said it was because of this tiny ad:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic