DB2 9.7 SQL Procedure Developer (C2090-545) Free Practice Test

Question 1

Given the following SQL:

Which statement is incorrect?

A. The cursor declaration requires the WITH RETURN TO CALLER clause in order to return a result set.

B. The procedure declaration requires the DYNAMIC RESULT SETS 1 clause in order to return a result set.

C. The cursor declaration requires the WITH RETURN TO CLIENT clause in order to return a result set.

D. The cursor declaration requires the WITH RETURN clause in order to return a result set.

Correct Answer: B

Question 2

For which types of servers can you use IBM Data Studio to create and manage SQL procedures?

A. DB2 for LUW, DB2 for z/OS, SQL Server

B. DB2 for LUW, DB2 for z/OS, MySQL

C. DB2 for LUW, DB2 for z/OS, Sybase

D. DB2 for LUW, DB2 for z/OS, Oracle

Correct Answer: D

Question 3

In a SQL Procedure, rows_fetched is an integer variable and curEmp is a cursor variable of SELECT statement on a table. After opening the cursor curemp, which of the statement will determine the number of rows fetched?

A. SET rows_fetched = curEmp%CURSOR_ROWCOUNT

B. SET rows_fetched = ROWCOUNT(curemp)

C. SET rows_fetched = curEmp%ROWCOUNT

D. SET rows_fetched = CURSOR_ROWCOUNT(curemp)

Correct Answer: D

Question 4

Given the following DDL:

what will the SALARY be?

A. salary * 1.03

B. salary * 1.05

C. salary * 1.50

D. salary * 1.10

Correct Answer: C

Question 5

What is a reason to use SQL procedures in DB2?

A. to move business logic rules into the database

B. to reduce code sharing

C. to eliminate the need for testing

D. to use different programming languages

Correct Answer: A

Question 6

What values are generated each time an SQL statement is executed?

A. EXCEPTION and SQLCODE

B. SQLSTATE and SQLCODE

C. SQLERRN and SQLMSG

D. SQLSTATE and EXCEPTION

Correct Answer: B

Question 7

Which two statements are true when working with triggers? (Choose two.)

A. Triggers can be used to enforce data integrity rules.

B. Triggers can be used on Created Temporary Tables.

C. Triggers can be altered.

D. Triggers can be used with both tables and views.

E. Triggers can be used on Materialized Query Tables.

Correct Answer: A,D

Question 8

Which statement will create global variable?

A. PUBLISH VARIABLE glob_emp_sal DECIMAL(9,2)

B. DECLARE GLOBAL VARIABLE glob_emp_sal DECIMAL(9,2)

C. CREATE VARIABLE glob_emp_sal ANCHORE TO employee.salary

D. DECLARE GLOBAL VARIABLE glob_emp_sal ANCHORE TO employee.salary

Correct Answer: C

Question 9

Which declaration is not valid?

A. DECLARE var1 INTEGER DEFAULT;

B. DECLARE var1 CHAR;

C. DECLARE var1 VARCHAR(20);

D. DECLARE var1 DECIMAL(9,2);

Correct Answer: A

Question 10

The CREATE PROCEDURE statement shown below was executed against a database called MYDB.
CREATE PROCEDURE myschema.proc1(IN p1 INTEGER, OUT p2 CHAR(4), OUT p3 SMALLINT)
BEGIN
SET p2 = 'abc';
END
Which two CREATE PROCEDURE statements, when executed against the same database, will succeed? (Choose two.)

A. CREATE PROCEDURE myschema.proc1(IN p1 CHAR(4), OUT p2 INTEGER)
BEGIN
SET p2 = 123;
END

B. CREATE PROCEDURE myschema.proc1(IN p1 CHAR(4), OUT p2 INTEGER, OUT p3
SMALLINT)
BEGIN
SET p2 = 123;
END

C. CREATE PROCEDURE myschema.proc1(IN p1 INTEGER, OUT p2 CHAR(4), OUT p3
CHAR(4))
BEGIN
SET p2 = 'abc';
END

D. CREATE PROCEDURE myschema.proc1(IN p1 NUMBER, OUT p2 NUMBER, OUT p3 NUMBER) BEGIN SET p2 = 'abc'; END

E. CREATE PROCEDURE otherschema.proc1(IN p1 CHAR(4), OUT p2 CHAR(4), OUT p3 CHAR(4)) BEGIN SET p2 = 'abc'; END

Correct Answer: A,E

Welcome to TestSimulate

IBM DB2 9.7 SQL Procedure Developer (C2090-545) Free Practice Test