Best Quality of C2090-735 exam cram materials and pack for IBM certification for IT professionals, Real Success Guaranteed with Updated C2090-735 pdf dumps vce Materials. 100% PASS DB2 9.5 SQL Procedure Developer (C2090-735) exam Today!
2021 Apr C2090-735 Study Guide Questions:
Q31. Which CREATE TRIGGER statement is valid?
A. CREATE TRIGGER deletestaff AFTER DELETE ON org REFERENCING OLD_TABLE AS deletedorg FOR EACH STATEMENT MODE DB2SQL DELETE FROM staff WHERE dept IN (SELECT deptnumb FROM deletedorg)
B. CREATE TRIGGER deletestaff NO CASCADE BEFORE DELETE ON org REFERENCING OLD_TABLE AS deletedorg FOR EACH STATEMENT MODE DB2SQL DELETE FROM staff WHERE dept IN (SELECT deptnumb FROM deletedorg)
C. CREATE TRIGGER deletestaff AFTER DELETE ON org REFERENCING OLD AS deletedorg FOR EACH STATEMENT MODE DB2SQL DELETE FROM staff WHERE dept=deletedorg.deptnumb
D. CREATE TRIGGER deletestaff NO CASCADE BEFORE DELETE ON org REFERENCING OLD AS deletedorg FOR EACH STATEMENT MODE DB2SQL DELETE FROM staff WHERE dept=deletedorg.deptnumb
Answer: A
74. Which two rules apply to the REFERENCING clause in a CREATE TRIGGER statement? (Choose two.)
A. The OLD correlation-name and the OLD TABLE identifier can be used if the triggerevent is an INSERT operation.
B. The OLD correlation-name and the OLD TABLE identifier can be used if the triggerevent is an UPDATE operation.
C. The scope of each correlation-name and identifier used is the entire trigger definition.
D. The OLD TABLE or NEW TABLE identifiers can be defined in a BEFORE trigger.
E. A NEW correlation-name can be specified more than once for a trigger.
Answer: B,C
Q32. 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
Q33. Which statement will let you use the result set from the nested procedure CALLEE?
A. ASSOCIATE RESULT SET LOCATOR( loc1) WITH PROCEDURE callee;
B. BIND RESULT SET WITH PARAMETERS FOR PROCEDURE callee;
C. INSERT RESULT SET FROM callee INTO CURSOR c1;
D. SELECT * FROM callee;
Answer: A
Rebirth C2090-735 exam question:
Q34. 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
Q35. Click the Exhibit button.
***MISSING EXHIBIT***
CREATE PROCEDURE s2(IN p1 INT, OUT p2 INT)
BEGIN
SET p2 = p1 + 3;
END@
CREATE PROCEDURE s1(IN v1 INT, OUT v2 INT) BEGIN
DECLARE v3 INT;
DECLARE vrc INT;
SET v3 = v1 + 1;
CALL s2(v3,v2);
GET DIAGNOSTICS vrc = RETURN_STATUS;
SET v2 = v2 + vrc + 2;
END@
Given the two SQL procedures shown in the exhibit, what is the expected output if procedure S1 is invoked with the value 1 provided for parameter V1?
A. NULL
B. 2
C. 5
D. 7
Answer: D
Q36. 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
Real C2090-735 :
Q37. Given the variable declaration shown below:
DECLARE v_mytime TIME;
Which statement will assign a value to the variable named V_MYTIME?
A. SET v_mytime = TIME;
B. VALUES CURRENT TIME INTO v_mytime;
C. VALUES CURRENT TIMESTAMP INTO v_mytime;
D. SET v_mytime = DATE;
Answer: B
Q38. Which statement describes what must be done to create an SQL procedure that returns a result set?
A. Specify the clause DYNAMIC RESULT SETS 1 in the CREATE PROCEDUREstatement; declare a cursor within the procedure body; open the cursor; exit the procedurewithout closing the cursor.
B. Specify the clause DYNAMIC RESULT SETS 1 in the CREATE PROCEDUREstatement; code a SELECT statement in the procedure body.
C. Execute the CREATE PROCEDURE statement using the defaults; declare a cursorwithin the procedure body; open the cursor; exit the procedure without closing the cursor.
D. Execute the CREATE PROCEDURE statement using the defaults; declare a cursorwithin the procedure body; open the cursor; retrieve each row into output variables; closethe cursor before exiting the procedure.
Answer: A
Q39. Click the Exhibit button.
***MISSING EXHIBIT***
CREATE FUNCTION fcn1(deptno CHAR(3)) RETURNS TABLE(empno CHAR(6), firstnme VARCHAR(12)) READS SQL DATA NO EXTERNAL ACTION DETERMINISTIC RETURN SELECT empno, firstnme FROM employee WHERE employee.workdept = fcn1.deptno;
Which statement can be used to invoke the function in the exhibit?
A. SELECT * FROM TABLE(fcn1('B01'))
B. SELECT TABLE(fcn1('B01')) FROM SYSIBM.SYSDUMMY1
C. SELECT * FROM fcn1('B01')
D. SELECT fcn1('B01') FROM SYSIBM.SYSDUMMY1
Answer: A
Q40. Click the Exhibit button.
***MISSING EXHIBIT***
CREATE FUNCTION emp_birthdays (month INTEGER)
RETURNS TABLE (l_name VARCHAR(15),
f_name VARCHAR(12),
month VARCHAR(20),
day INTEGER)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
RETURN
SELECT lastname, firstnme, MONTHNAME(birthdate),
DAY(birthdate)
FROM employee
WHERE MONTH(employee.birthdate) = emp_birthdays.month
A user-defined function was created using the statement shown in the exhibit. Which additional option can be added to the CREATE FUNCTION statement to tell the optimizer that the function does not always return the same results for a given argument value?
A. NO EXTERNAL ACTION
B. NOT FENCED
C. NOT DETERMINISTIC
D. STATIC DISPATCH
Answer: C