Exam Code: C2090-735 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: DB2 9.5 SQL Procedure Developer (C2090-735)
Certification Provider: IBM
Free Today! Guaranteed Training- Pass C2090-735 Exam.
2021 May C2090-735 Study Guide Questions:
Q1. Click the Exhibit button.
***MISSING EXHIBIT***
MERGE INTO archive ar
USING (SELECT activity, description FROM activities) ac
ON (ar.activity = ac.activity)
WHEN MATCHED THEN
UPDATE SET description = ac.description
WHEN NOT MATCHED THEN
INSERT (activity, description)
VALUES (ac.activity, ac.description)
Given the code segment shown in the exhibit, which statement accurately describes the result?
A. For activities whose description has not changed, update the description in the ARCHIVE table. For new activities, insert into the ARCHIVE table. The ARCHIVE and ACTIVITIES table both have ACTIVITY as a primary key.
B. For activities whose description has changed, update the description in the ARCHIVEtable. For new activities, insert into the ARCHIVE table. The ARCHIVE andACTIVITIES table both have ACTIVITY as a primary key.
C. For activities whose description has changed, update the description in the ACTIVITYtable. For new activities, insert into the ACTIVITIES table. The ARCHIVE andACTIVITIES table both have ACTIVITY as a primary key.
D. The statement will fail since MERGE is not a supported operator.
Answer: B
Q2. 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
Q3. Given the statements shown below:
DECLARE c_dept CURSOR WITH HOLD FOR
SELECT * FROM dept;
OPEN c_dept;
Which two conditions are true? (Choose two.)
A. C_DEPT will remain open after a ROLLBACK.
B. C_DEPT will remain open after a COMMIT.
C. C_DEPT will be returned to the caller of the routine.
D. C_DEPT will be positioned before the next logical row.
E. All locks held by C_DEPT will be released after a COMMIT.
Answer: B,D
Updated C2090-735 exams:
Q4. How is the FOR statement distinct from other conditional statements?
A. FOR statements are evaluated at the completion of each iteration of the loop.
B. FOR statements are evaluated before each iteration of the loop.
C. FOR statements have a terminating condition clause.
D. FOR statements are used to iterate over rows in a defined read-only result set.
Answer: D
Q5. 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
Q6. Click the Exhibit button.
***MISSING EXHIBIT***
CREATE PROCEDURE procB (INOUT p_parm1 INT, INOUT p_parm2 CHAR(5) )
BEGIN DECLARE v_1 INT DEFAULT 0;
DECLARE v_2 CHAR(5) DEFAULT '12345'
SET p_parm1 = v_1;
SET p_parm2 = v_2;
END@
CREATE PROCEDURE procA (IN p_parm1 INT, INOUT p_parm2 INT)
BEGIN DECLARE v_1 INT DEFAULT 0;
DECLARE v_2 INT DEFAULT 0;
SET v_1 = p_parm1;
CALL procB(v_1, v_2);
SET p_parm2 = v_2;
END@ CALL procA(1, 1)@
The file myscript.sql (shown in the exhibit) is executed from the CLP using the command:
db2 -td@ -vf myscript.sql
What is the expected outcome?
A. SQL procedures PROCA and PROCB will be created, but the CALL command willfail.
B. SQL procedure PROCA will not be created.
C. SQL procedures PROCA and PROCB will be created and the CALL command willsucceed.
D. SQL procedure PROCB will not be created.
Answer: B
Free C2090-735 pack:
Q7. Using Optimistic Locking, both Transaction 1 and Transaction 2 have read the same row including the RID_BIT and ROW CHANGE TOKEN values. Transaction 1 updates the row after ensuring that the row has not changed after the previous SELECT by adding a RID_BIT and ROW CHANGE TOKEN predicate to the UPDATE statement.
Transaction 2 now tries to update that same row using the same predicate as transaction 1.What will be the result?
A. The row will be found with the same ROW CHANGE TOKEN. Transaction 2 willnow insert its values replacing those of transaction 1.
B. The row will be found with a different ROW CHANGE TOKEN and Transaction 2will overwrite the values set in transaction 1.
C. The row will not be found because the value of the ROW CHANGE TOKEN haschanged in regard to the UPDATE of transaction 1. Transaction 2 has to retry in order toretrieve the current data.
D. The row will not be found because the value of the ROW CHANGE TOKEN haschanged in regard to the UPDATE of transaction 1. Transaction 2 will complete with aninsert of a new row.
Answer: C
Q8. Click the Exhibit button.
***MISSING EXHIBIT***
EMPNO FIRSTNME LASTNAME JOB WORKDEPT
000010 CHRISTINE HAAS PRES A00
000020 MICHAEL THOMPSON MANAGER B01
000050 JOHN GEYER MANAGER E01
000060 IRVING STERN MANAGER D11
000070 EVA PULASKI MANAGER D21 000090 EILEEN HENDERSON MANAGER E11 000100 THEODORE SPENSER MANAGER E21 000200 DAVID BROWN DESIGNER D11 000210 WILLIAM JONES DESIGNER D11 000220 JENNIFER LUTZ DESIGNER D11 000230 JAMES JEFFERSON CLERK D21 000240 SALVATORE MARINO CLERK D21 000250 DANIEL SMITH CLERK D21 000260 SYBIL JOHNSON CLERK E21 000270 MARIA PEREZ CLERK D21 000280 ETHEL SCHNEIDER OPERATOR E11 000290 JOHN PARKER OPERATOR E11 000320 RAMLAL MEHTA FIELDREP E21 000330 WING LEE FIELDREP E21 000340 JASON GOUNOT FIELDREP E21 200140 KIM NATZ ANALYST C01 200170 KIYOSHI YAMAMOTO DESIGNER D11 200220 REBA JOHN DESIGNER D11 200240 ROBERT MONTEVERDE CLERK D21 200330 HELENA WONG FIELDREP E21 200340 ROY ALONZO FIELDREP E21 UDF definition: CREATE FUNCTION getnumbemployee(p_dept VARCHAR(3)) RETURNS TABLE (enum INT, empno VARCHAR(6), lastname VARCHAR(15), firstnme VARCHAR(12)) RETURN SELECT ROW_NUMBER() OVER (), e.empno, e.lastname, e.firstnme
FROM employee e WHERE e.workdept = p_dept;
Referring to the exhibit, how many rows will be returned by the SQL query shown below?
SELECT * FROM TABLE(getnumemployee(E21)) AS d
A. 0
B. 1
C. 7
D. 10
Answer: C
Q9. Which CREATE PROCEDURE statement option should be used if you plan on issuing a DECLARE GLOBAL TEMPORARY TABLE statement from within the SQL procedure body?
A. CONTAINS SQL
B. READS SQL DATA
C. MODIFIES SQL DATA
D. LANGUAGE SQL
Answer: C
Q10. Which statement will change the value of a special register?
A. UPDATE SPECIAL REGISTER TIME = 12:30:00
B. UPDATE SPECIAL REGISTER SCHEMA = 'DB2ADMIN'
C. SET CURRENT TIME = 12:30:00
D. SET CURRENT SCHEMA = 'DB2ADMIN'
Answer: D