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:
Q51. Click the Exhibit button.
***MISSING EXHIBIT***
CREATE FUNCTION e_degree(edlevel SMALLINT)
RETURNS CHAR(12)
LANGUAGE SQL
CONTAINS SQL
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
An SQL function was created using the statement shown in the exhibit. Assume this function is invoked by executing the SQL statement shown below:
VALUES e_degree(SMALLINT(17))
What will be returned?
A. SQL0440N No authorized routine named "E_DEGREE" of type "FUNCTION"having compatible arguments was found. SQLSTATE=42884
B. 1
BACHELOR 1 record(s) selected.
C. 1
MASTER 1 record(s) selected.
D. 1
UNKNOWN 1 record(s) selected.
Answer: D
Q52. You want to see the explain plan for all SQL statements used in your SQL procedures. Which two statements will allow you to obtain the explain plan? (Choose two.)
A. Copy SQL statements from the SQL procedure and paste into the Control Center, Command Editor, or Data Studio explain facility.
B. Use the DB2SET command to globally set DB2_SQLROUTINE_PREPOPTS="EXPLAIN YES EXPLSNAP YES" .
C. Call SYSPROC. SET_ROUTINE_OPTS('EXPLAIN YES EXPLSNAP YES') to setexplain parameters for a session and compile the SQL procedure. Run the db2expl ordb2exfmt tool to capture the explain plan.
D. Call SYSPROC. REBIND_ROUTINE_PACKAGE and specify 'EXPLAIN YES EXPLSNAP YES' bind option.Run the db2expl or db2exfmt tool to capture explain plan.
E. Call SYSPROC. SET_ROUTINE_OPTS('EXPLAIN YES EXPLSNAP YES') to setexplain parameters for a session and compile the SQL procedure.
Answer: A,C
Q53. A developer wants to create an SQL procedure that will convert a temperature from degrees Fahrenheit to degrees Centigrade. It is anticipated that the procedure will be invoked from another procedure using a CALL statement that looks like this:
CALL proc.f_to_c(old_temp, new_temp);
Which statement will successfully create an SQL procedure that accepts one temperature value and returns another when called as shown?
A. CREATE PROCEDURE proc.f_to_c(temp_f REAL, temp_c REAL)
BEGIN
DECLARE temp_value REAL;
SET temp_value = (temp_f - 32);
SET temp_c = (5 * temp_value) / 9;
END
B. CREATE PROCEDURE proc.f_to_c(temp_f REAL, INOUT temp_c REAL)
BEGIN
DECLARE temp_value REAL;
SET temp_value = (temp_f - 32);
SET temp_c = (5 * temp_value) / 9;
END
C. CREATE PROCEDURE proc.f_to_c(INOUT temp_f REAL, OUT temp_c REAL)
BEGIN
DECLARE temp_value REAL;
SET temp_value = (temp_f - 32);
SET temp_c = (5 * temp_value) / 9;
END
D. CREATE PROCEDURE proc.f_to_c(IN temp_f, OUT temp_c)
BEGIN
DECLARE temp_value REAL;
SET temp_value = (temp_f - 32);
SET temp_c = (5 * temp_value) / 9;
END
Answer: B
Updated C2090-735 practice test:
Q54. What are two valid DECLARE statements in an SQL procedure? (Choose two.)
A. DECLARE var1 INTEGER;
B. DECLARE var1 DECIMAL [9];
C. DECLARE var1 XML;
D. DECLARE var1 CURRENT DATE;
E. DECLARE var1[10] INTEGER;
Answer: A,C
Q55. Which statement can be used to declare a variable inside an SQL procedure that can be used to represent a monetary value?
A. DECLARE v_money MONEY;
B. DECLARE v_money DOUBLE;
C. DECLARE v_money DECIMAL(9,2);
D. DECLARE v_money CURRENCY;
Answer: C
Q56. Which statement can be used to define an array of 30 names that have a maximum size of 25 characters each?
A. CREATE TYPE names AS VARCHAR(25) ARRAY[30];
B. CREATE ARRAY names[30] VARCHAR(25);
C. CREATE TYPE names[30] VARCHAR(25);
D. CREATE ARRAY names AS VARCHAR(25);
Answer: A
Certified C2090-735 item pool:
Q57. What are two valid special registers? (Choose two.)
A. CURRENT_CLIENT_ACCT
B. CURRENT_SCHEMA
C. CURRENT_PATH
D. CURRENT_DATETIME
E. CURRENT_PARTITION
Answer: B,C
Q58. Which two DB2 privileges are required to issue a CREATE PROCEDURE statement, assuming the SCHEMA name used for the SQL procedure already exists? (Choose two.)
A. EXECUTE privilege on any dependent SQL procedures
B. BINDADD privilege on the database
C. BIND privilege on the database
D. SECADM authority on the database
E. CREATEIN privilege on the schema
Answer: B,E
Q59. Click the Exhibit button.
***MISSING EXHIBIT***
CREATE PROCEDURE p_exit (IN p_in INTEGER, OUT p_out INTEGER) BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN SET p_out = 99;
END;
IF (p_in < 5) THEN SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'Custom
SQL State'
ELSEIF (p_in > 10) THEN RETURN 3;
ELSE SET p_out = 11;
END IF;
RETURN 29;
END
Given the SQL procedure definition shown in the exhibit, what will the value of the P_OUT parameter be if the procedure is invoked with a value of 11 for the P_IN parameter?
A. 3
B. 29
C. 99
D. NULL
Answer: D
Q60. Given the variable declaration shown below:
DECLARE v_mydate DATE;
Which statement will assign a value to the variable V_MYDATE?
A. VALUES CURRENT TIME INTO v_mydate;
B. VALUES CURRENT TIMESTAMP INTO v_mydate;
C. SELECT CURRENT TIMESTAMP INTO v_mydate FROMSYSIBM.SYSDUMMY1;
D. SELECT CURRENT DATE INTO v_mydate FROM SYSIBM.SYSDUMMY1;
Answer: D