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:
Q41. Which two procedures demonstrate the correct use of dynamic SQL? (Choose two.)
A. CREATE PROCEDURE update_count1 (IN new_count INTEGER, IN item_code
INTEGER)
BEGIN
DECLARE v_dynSQL VARCHAR(200);
SET v_dynSQL = 'UPDATE stock SET quantity_on_hand=? WHERE item_number=?'
PREPARE v_stmt1 FROM v_dynSQL;
EXECUTE v_stmt1 USING new_count, item_code;
END
B. CREATE PROCEDURE update_count2 (IN tab_name VARCHAR(128), IN
new_count INTEGER, IN
item_code INTEGER)
BEGIN
DECLARE v_dynSQL VARCHAR(200);
SET v_dynSQL = 'UPDATE ? SET quantity_on_hand=? WHERE item_number=?'
PREPARE v_stmt1 FROM v_dynSQL;
EXECUTE v_stmt1 USING tab_name, new_count, item_code;
END
C. CREATE PROCEDURE update_count3 (IN new_count INTEGER, IN item_code
INTEGER)
BEGIN
DECLARE v_dynSQL VARCHAR(200);
SET v_dynSQL = 'UPDATE stock SET quantity_on_hand=' || CHAR(new_count) || '
WHERE
item_number=' || CHAR(item_code);
EXECUTE IMMEDIATE v_dynSQL;
END
D. CREATE PROCEDURE update_count4 (IN tab_name VARCHAR(128), IN
col_name1 VARCHAR(128), IN
col_name2 VARCHAR(128), IN new_count INTEGER, IN item_code INTEGER)
BEGIN
DECLARE v_dynSQL VARCHAR(200);
SET v_dynSQL = 'UPDATE ? SET ?=? WHERE ?=?'
PREPARE v_stmt1 FROM v_dynSQL;
EXECUTE v_stmt1 USING tab_name, col_name1, new_count, col_name2, item_code;
END
E. CREATE PROCEDURE update_count5 (IN new_count INTEGER, IN item_code
INTEGER)
BEGIN
DECLARE v_dynSQL VARCHAR(200);
DECLARE v_col_name VARCHAR(128);
SET v_col_name = 'item_number'
SET v_dynSQL = 'UPDATE stock SET quantity_on_hand=? WHERE ?=?'
PREPARE v_stmt1 FROM v_dynSQL;
EXECUTE v_stmt1 USING new_count, v_col_name, item_code;
END
Answer: A,C
Q42. Click the Exhibit button.
***MISSING EXHIBIT***
-- func1.db2 CREATE FUNCTION dept_salary(deptin CHAR(3)) RETURNS DECIMAL(12,2) RETURN SELECT SUM(salary) FROM employee WHERE workdept = deptin! W Times New Roman ArialZ
In the exhibit, the code for an SQL user-defined function is contained in the file func1.db2.What is the correct command to build this function from the command line?
A. db2 td! vf dept_salary()
B. db2 td! vf func1.db2
C. db2 tvf func1.db2
D. db2 tvf vf get_dept_sal
Answer: B
Q43. You are developing a application that calls a number of SQL procedures and you do not want end users to be able to see your SQL procedure source code.Which statement(s) can be used to protect your code?
A. ALTER PROCEDURE schema.procname HIDE PROCEDURE BODY;
B. ALTER PROCEDURE schema.procname ENCRYPT PROCEDURE BODY;
C. GET ROUTINE INTO sp.sar FROM PROCEDURE schema.procname ENCRYPT BODY DROP PROCEDURE schema.procname PUT ROUTINE FROM sp.sar
D. GET ROUTINE INTO sp.sar FROM PROCEDURE schema.procname HIDE BODY DROP PROCEDURE schema.procname PUT ROUTINE FROM sp.sar
Answer: D
Regenerate C2090-735 exam prep:
Q44. Which two statements are true with regards to nesting SQL procedures? (Choose two.)
A. An SQL procedure cannot invoke procedures built in other languages (e.g., C, Java).
B. An SQL procedure can invoke the same SQL procedure more than once.
C. An SQL procedure defined with the CONTAINS SQL clause can invoke an SQLprocedure defined with the READS SQL DATA clause.
D. An SQL procedure defined with the CONTAINS SQL clause can invoke an SQLprocedure defined with the MODIFIES SQL DATA clause.
E. An SQL procedure defined with the CONTAINS SQL clause can invoke an SQLprocedure defined with the NO SQL clause.
Answer: B,E
Q45. Which three statements are true about SQL procedures? (Choose three.)
A. SQL procedures reside outside of the database for easy portability.
B. SQL procedures can be called from triggers.
C. SQL procedures may return multiple result sets to the caller or to a client application.
D. SQL procedures support recursion.
E. SQL procedures support the PRINT statement.
Answer: B,C,D
Q46. Given the SQL statement shown below:
DECLARE test CURSOR FOR SELECT hiredate FROM employee FOR UPDATE;
Which statement correctly describes the cursor that is created?
A. The cursor will be considered a read-only cursor.
B. The cursor can only be used to perform positioned updates.
C. The cursor can only be used to perform positioned deletes.
D. The cursor can be used to perform positioned updates and deletes.
Answer: D
Pinpoint C2090-735 testing material:
Q47. Which statement will successfully create an SQL procedure that returns the name of the current month?
A. CREATE PROCEDURE proc.current_month(OUT month VARCHAR(20))
BEGIN
DECLARE today DATE;
SET (today = CURRENT_DATE);
SET month = MONTHNAME(today);
END
B. CREATE PROCEDURE proc.current_month(OUT month VARCHAR(20))
BEGIN
DECLARE today DATE;
SELECT (CURRENT_DATE) INTO today;
SET month = MONTHNAME(today);
END
C. CREATE PROCEDURE proc.current_month(OUT month VARCHAR(20))
BEGIN
DECLARE today DATE;
VALUES (CURRENT_DATE) INTO today;
SET month = MONTHNAME(today);
END
D. CREATE PROCEDURE proc.current_month(OUT month VARCHAR(20))
BEGIN
SET month = MONTHNAME(SELECT (CURRENT_DATE))
END
Answer: C
Q48. Click the Exhibit button.
***MISSING EXHIBIT***
CREATE FUNCTION check_id(p_id INTEGER) RETURNS VARCHAR(70) BEGIN ATOMIC DECLARE v_err VARCHAR(70) SET v_err = 'Error: ID ' || CHAR(p_id) || ' is not valid' IF p_id = 0 THEN SET v_err = 'Application Error' ELSEIF p_id < 0 THEN ELSEIF p_id IS NULL THEN SIGNAL SQLSTATE '80000' SET MESSAGE_TEXT=v_err ELSE SET v_err = 'Valid ID' RETURN NULL END IF RETURN v_err END
Given the user-defined function shown in the exhibit, what will the function return if invoked using the statement shown below?
SELECT check_id(1) FROM SYSIBM.SYSDUMMY1
A. Error: ID 1 is not valid
B. Application Error
C. SQLSTATE 80000 will be returned, with the custom error message "Error: ID 1 is notvalid"
D. NULL
Answer: D
Q49. Which SQL procedure returns two cursors to the application, by-passing any intermediary SQL procedures?
A. CREATE PROCEDURE read_employee_and_dept() DYNAMIC RESULT SETS 1 BEGIN DECLARE c_emp CURSOR WITH RETURN TO CALLER FOR SELECT salary, bonus, comm FROM employee WHERE job != 'PRES' DECLARE c_dept CURSOR WITH RETURN TO CALLER FOR SELECT deptno, deptname, mgrno FROM department; END
B. CREATE PROCEDURE read_employee_and_dept() DYNAMIC RESULT SETS 2 BEGIN DECLARE c_emp CURSOR WITH RETURN TO CLIENT FOR SELECT salary, bonus, comm FROM employee WHERE job != 'PRES' DECLARE c_dept CURSOR WITH RETURN TO CLIENT FOR SELECT deptno, deptname, mgrno FROM department; OPEN c_emp; OPEN c_dept; END
C. CREATE PROCEDURE read_employee_and_dept() DYNAMIC RESULT SETS 2 BEGIN DECLARE c_emp CURSOR WITH RETURN TO CLIENT FOR SELECT salary, bonus, comm FROM employee WHERE job != 'PRES' DECLARE c_dept CURSOR WITH RETURN TO CLIENT FOR SELECT deptno, deptname, mgrno FROM department; END
D. CREATE PROCEDURE read_employee_and_dept() DYNAMIC RESULT SETS 2 BEGIN DECLARE c_emp CURSOR WITH RETURN TO CALLER FOR SELECT salary, bonus, comm FROM employee WHERE job != 'PRES' DECLARE c_dept CURSOR WITH RETURN TO CALLER FOR SELECT deptno, deptname, mgrno FROM department; OPEN c_emp; OPEN c_dept; END
Answer: B
Q50. Click the Exhibit button.
***MISSING EXHIBIT***
* Exhibit Y 1? 0?
CREATE TABLE emp_hours (emp_name CHAR(10), starting TIME, ending TIME); CREATE TRIGGER trig1 BEFORE INSERT ON emp_hours REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL WHEN (n.ending IS NULL) SET n.ending = n.starting + 1 HOUR; CREATE TRIGGER trig2 BEFORE INSERT ON emp_hours REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL WHEN (n.ending IS NULL) SET n.ending = n.starting + 1 HOUR;
The table and triggers in the exhibit have been created successfully, when the statements shown below are executed.
INSERT INTO emp_hours VALUES('KAY', '9:00', '17:00');
INSERT INTO emp_hours (emp_name, starting) VALUES('BILL', '9:00');
What is the result?
A. Table EMP_HOURS will contain these rows: KAY 9:00:00 AM 5:00:00 PM BILL 9:00:00 AM 10:00:00 AM
B. Table EMP_HOURS will contain this row: BILL 9:00:00 AM
C. Table EMP_HOURS will contain these rows: KAY 9:00:00 AM 5:00:00 PM BILL 9:00:00 AM
D. Table EMP_HOURS will contain this row: BILL 9:00:00 AM 17:00:00 PM
Answer: A