Top 10 preparation labs C2090-735 for candidates (31 to 40)

Cause all that matters here is passing the IBM C2090-735 exam. Cause all that you need is a high score of C2090-735 DB2 9.5 SQL Procedure Developer (C2090-735) exam. The only one thing you need to do is downloading Pass4sure C2090-735 exam study guides now. We will not let you down with our money-back guarantee.

2016 Jun C2090-735 rapidshare

Q31. Which CREATE TRIGGER statement is valid? 

A. CREATE TRIGGER test1 NO CASCADE AFTER INSERT ON employee REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL WHEN (n.bonus IS NULL) SET n.salary = n.salary + 2000 

B. CREATE TRIGGER test1 NO CASCADE BEFORE INSERT ON employee REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL WHEN (n.bonus IS NULL) SET n.salary = n.salary + 2000 

C. CREATE TRIGGER test1 AFTER DELETE ON employee REFERENCING OLD_TABLE AS oldemployee FOR EACH STATEMENT MODE DB2SQL DELETE FROM staff WHERE id IN (SELECT empno FROM oldemployee) D. CREATE TRIGGER test1 NO CASCASE BEFORE DELETE ON employee FOR EACH STATEMENT MODE DB2SQL DELETE FROM staff WHERE id IN (SELECT empno FROM oldemployee) 

Answer: B 


Q32. Which two statements describe characteristics of external functions? (Choose two.) 

A. External functions cannot return tables. 

B. All cursors opened within an external function should stay open until the database isquiesced. 

C. Dynamic allocations of memory in an external routine should be freed before theexternal routine returns. 

D. Scratchpads can be used to allocate dynamic memory required for multiple functioninvocations. 

E. Transactions can be terminated within external functions. 

Answer: C,D 


Q33. Click the Exhibit button. 

***MISSING EXHIBIT*** 

CREATE PROCEDURE p1 

BEGIN 

SAVEPOINT s1 ON ROLLBACK RETAIN CURSORS; 

CREATE TABLE t1 (c1 int); 

INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (2); INSERT INTO t1 VALUES (3); INSERT INTO t1 VALUES (4); INSERT INTO t1 VALUES (5); COMMIT; INSERT INTO t1 VALUES (6); INSERT INTO t1 VALUES (7); INSERT INTO t1 VALUES (8); INSERT INTO t1 VALUES (9); INSERT INTO t1 VALUES (10); ROLLBACK TO SAVEPOINT s1; RELEASE SAVEPOINT s1; END@ 

Referring to the exhibit, which two statements are correct? (Choose two.) 

A. After calling procedure P1, the total number of rows in table T1 will be 10. 

B. After calling procedure P1, the total number of rows in table T1 will be 5. 

C. After calling procedure P1, the table T1 will not exist. 

D. After calling procedure P1, there will be a runtime error stating that the transactioncannot be rolled back to S1 since table T1 was created successfully. 

E. It is necessary to declare SAVEPOINT with ON ROLLBACK RETAIN CURSORSeven though no cursors are used in the SQL procedure. 

Answer: B,E 


Q34. Click the Exhibit button. 

***MISSING EXHIBIT*** 

CREATE FUNCTION dept_employees (deptno CHAR(3)) RETURNS TABLE (empno CHAR(6), l_name VARCHAR(15), f_name VARCHAR(12)) LANGUAGE SQL READS SQL DATA RETURN SELECT empno, lastname AS l_name, firstnme AS f_name FROM employee WHERE employee.workdept = dept_employees.deptno 

A developer created a user-defined function using the statement shown in the exhibit. Now, the developer would like to see the access plan used by the query in the function.What are the steps needed to do this from a CLP script file? 

A. 1. Set the CURRENT EXPLAIN MODE special register to EXPLAIN. 

2. Execute the CREATE FUNCTION statement. 

3. Set the CURRENT EXPLAIN MODE special register to NO. 

4. Run the db2exfmt utility. 

B. 1. Set the CURRENT EXPLAIN MODE special register to EXPLAIN. 

2. Execute the query portion of the CREATE FUNCTION statement (replacedept_employees.deptno with a hard-coded value). 

3. Set the CURRENT EXPLAIN MODE special register to NO. 

4. Run the db2exfmt utility. 

C. 1. Set the CURRENT EXPLSNAP special register to EXPLAIN. 

2. Execute the CREATE FUNCTION statement. 

3. Set the CURRENT EXPLSNAP special register to NO. 

4. Run the db2exfmt utility. 

D. 1. Set the CURRENT EXPLSNAP special register to EXPLAIN. 

2. Execute the query portion of the CREATE FUNCTION statement (replace dept_employees.deptno with a hard-coded value). 

3. Set the CURRENT EXPLSNAP special register to NO. 

4. Run the db2exfmt utility. 

Answer: B 


Q35. Which three optional clauses can be used when creating an external function? (Choose three.) 

A. SCRATCHPAD 

B. NOTEPAD 

C. LANGUAGE 

D. EXTERNAL NAME 

E. DATABASEINFO 

Answer: A,C,D 


C2090-735 free draindumps

Leading C2090-735 practice test:

Q36. Click the Exhibit button. 

***MISSING EXHIBIT*** 

CREATE FUNCTION e_degree(edlevel SMALLINT) RETURNS CHAR(12) LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION BEGIN ATOMIC DECLARE degree CHAR(12); IF edlevel= 12 THEN SET degree = 'HIGH SCHOOL' ELSEIF edlevel = 14 THEN SET degree = 'ASSOCIATE' ELSEIF edlevel = 16 THEN 

SET degree = 'BACHELOR' 

ELSEIF edlevel = 18 THEN 

SET degree = 'MASTER' 

ELSEIF edlevel = 20 THEN 

SET degree = 'DOCTORATE' 

ELSE 

SET degree = 'UNKNOWN' 

END IF; 

RETURN degree; 

END 

A user-defined function was created using the statement shown in the exhbit. What are two valid ways to invoke this function from the DB2 Command Line Processor? (Choose two.) 

A. SELECT lastname, e_degree(edlevel) AS degree FROM employee 

B. SELECT * FROM e_degree(SMALLINT(18)) 

C. VALUES e_degree(18) 

D. VALUES e_degree(SMALLINT(18)) 

E. SELECT * FROM TABLE(e_degree(SMALLINT(18)) 

Answer: A,D 


Q37. Click the Exhibit button. 

***MISSING EXHIBIT*** 

CREATE PROCEDURE updatetest ( IN p_empname VARCHAR(30), IN p_empno CHAR(6), 

OUT p_sqlstate CHAR(5), 

OUT p_sqlcode INT ) 

BEGIN 

DECLARE SQLSTATE CHAR(5) DEFAULT '00000' 

DECLARE SQLCODE INT DEFAULT 0; 

DECLARE EXIT HANDLER FOR SQLEXCEPTION 

SELECT SQLSTATE, SQLCODE INTO p_sqlstate, p_sqlcode FROM 

sysibm.sysdummy1; 

VALUES (SQLSTATE, SQLCODE) INTO p_sqlstate, p_sqlcode; 

UPDATE employee SET empname = p_empname WHERE empno = p_empno; 

END 

If the procedure shown in the exhibit is invoked, and the UPDATE statement returns an 

SQL0100W "No row was found for FETCH, UPDATE or DELETE; or the result of a query is an empty table. SQLSTATE '02000'", which two situations will be true? (Choose two.) 

A. P_SQLSTATE will be set to '00000' and P_SQLCODE will be set to 0. 

B. P_SQLSTATE will be set to '02000' and P_SQLCODE will be set to 100. 

C. P_SQLSTATE will be set to '00000' and P_SQLCODE will be set to 100. 

D. The caller will receive an SQLCODE of 0. 

E. The caller will receive an SQLCODE of 100 

Answer: A,E 


Q38. Click the Exhibit button. 

***MISSING EXHIBIT*** 

Table INFO1 contains the following data: 

PERSON_NAME AGE HOBBY DOB 

John Smith 45 Reading 05/01/1963 John Brown 5 Skiing 07/01/1982 Sally John 50 Reading 04/21/1958 Rob Drake 15 VideoGames 02/01/1993 Betty Hoop 20 Shopping 04/11/1988 Bill Lee 29 Sailing 12/01/1978 Mary Pope 48 Shopping 04/21/1960 Procedure MOVE_DATA() is created as follows: CREATE PROCEDURE move_data() BEGIN FOR v1 AS SELECT person_name, age, hobby FROM info1 DO IF v1.age < 30 THEN INSERT INTO info2 VALUES(v1.person_name, v1.hobby ); END IF; END FOR; END 

How many rows will be in table INFO2 after the procedure MOVE_DATA shown in the exhibit is executed? 

A. 0 

B. 4 

C. 5 

D. 7 

Answer: B 


Q39. What will be the initial value of V_MAX in the declaration statement shown below? DECLARE v_max DECIMAL(9,2); 

A. 0.0 

B. 2 

C. 9 

D. NULL 

Answer: D 


Q40. Click the Exhibit button. 

***MISSING EXHIBIT*** 

CREATE PROCEDURE proc.test(IN month VARCHAR(12), OUT l_name 

VARCHAR(25)) 

LANGUAGE SQL 

BEGIN 

DECLARE b_month CHAR(12); 

DECLARE at_end INTEGER DEFAULT 0; 

DECLARE not_found CONDITION FOR SQLSTATE '02000' 

DECLARE c1 CURSOR FOR SELECT lastname, MONTHNAME(birthdate) 

FROM employee ORDER BY birthdate, lastname; 

DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1; 

OPEN c1; 

this_loop: LOOP 

FETCH c1 INTO l_name, b_month; 

IF at_end = 1 THEN 

LEAVE this_loop; 

ELSEIF UCASE(b_month) != UCASE(month) THEN 

ITERATE this_loop; 

END IF; END LOOP; CLOSE c1; END 

A developer attempted to create a procedure to determine the oldest employee celebrating a birthday in a particular month by executing the SQL statement shown in the exhibit. 

Tests show the procedure does not work as planned.What are two ways to make the procedure work as intended? (Choose two.) 

A. Add the statement ELSE RETURN; before the statement END IF;. 

B. Change the statement ELSEIFUCASE(b_month) != UCASE(month) THEN to ELSEIF UCASE(b_month) =UCASE(month) THEN. 

C. Add the statement ELSE BREAK; before the statement END IF;. 

D. Change the statement ELSEIFUCASE(b_month) != UCASE(month) THEN ITERATE this_loop; to ELSEIFUCASE(b_month) = UCASE(month) THEN LEAVE this_loop;. 

E. Add the statement ELSE CONTINUE; before the statement END IF;. 

Answer: A,D 



see more C2090-735 dumps