CertBus provides the most up to date and accurate preparing materials of the C2090-545 certification exam questions and answers , testing software, exam PDF and VCE files to help you prepare your C2090-545 DB2 9.7 SQL Procedure Developer exam. What training you are looking for? Come to visit our site and choose CertBus online certification materials, you will get a quick and cost-efficient way to become a IBM certified professional in IT industry.
We CertBus has our own expert team. They selected and published the latest C2090-545 preparation materials from IBM Official Exam-Center: http://www.certgod.com/C2090-545.html
QUESTION NO:5
Which three optional clauses can be used whencreating an external function? (Choose
three.)
A. SCRATCHPAD
B. NOTEPAD
C. LANGUAGE
D. EXTERNAL NAME
E. DATABASEINFO
Answer: A,C,D
QUESTION NO:12
A developer wants to code the following statements in an SQL procedure:
What order must thesestatements be coded in?
A. 1, 2, 3, 4
B. 2, 4, 3, 1
C. 3, 4, 2, 1
D. 4, 3, 2, 1
Answer: B
10
QUESTION NO:6
Which statement is permitted within a scalar user-defined function body?
A. COMMIT
B. INSERT
C. SIGNAL
D. LOOP
Answer: C
QUESTION NO:11
Which procedure demonstrates the correct use of dynamic SQL?
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;
9
END
C. 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
D. 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 SETquantity_on_hand=? WHERE ?=?’;
PREPARE v_stmt1 FROM v_dynSQL;
EXECUTE v_stmt1 USING new_count, v_col_name, item_code;
END
Answer: A
QUESTION NO:10
8
In the stored procedure below:
What will the value of the P_ID parameter be if the procedure is invoked and a value of 2 is
specified forthe START_VALUE parameter?
A. 1
B. 2
C. 3
D. 4
Answer: C
QUESTION NO:16
If this statement was used tocreate an SQL procedure named P_EXIT:
What will the return status be?
A. 10
B. 20
C. 99
D. NULL
Answer: C
QUESTION NO:1
A developer needs to create a user-defined functionthat will return a list of employees who
work in a particular department. Which statement will successfully create a function that
meets this objective?
A. CREATE FUNCTION dept_employees (deptno CHAR(3))
RETURNS TABLE
LANGUAGE SQL
READS SQL DATA
RETURN
SELECT empno, lastname AS l_name, firstnme AS f_name
FROM employee
WHERE employee.workdept = dept_employees.deptno
B. CREATE FUNCTION dept_employees (deptno CHAR(3))
RETURNS TABLE
DYNAMIC RESULT SETS 1
LANGUAGE SQL
READS SQL DATA
DECLARE emp_info CURSOR WITHRETURN FOR
SELECT empno, lastname AS l_name, firstnme AS f_name
FROM employee
WHERE employee.workdept = dept_employees.deptno
OPEN emp_info;
RETURN
C. 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
D. CREATE FUNCTION dept_employees (deptno CHAR(3))
RETURNS TABLE (empno CHAR(6),
l_nameVARCHAR(15),
f_name VARCHAR(12))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
READS SQL DATA
DECLARE emp_info CURSOR WITH RETURN FOR
SELECT empno, lastname AS l_name, firstnme AS f_name
FROM employee
2
WHERE employee.workdept = dept_employees.deptno
OPEN emp_info;
RETURN
Answer: C
QUESTION NO:2
In the function shown below:
Which statement can be used to invoke the function above?
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
QUESTION NO:7
6
Click the Exhibit button.
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 doesnot 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
QUESTION NO:9
Given the following SQL:
Which of the following statements is true?
A. The procedure declaration requires theDYNAMIC RESULT SETS 1 clause in order to
return a result set.
B. The cursor declaration requires WITH RETURN TO CLIENT clause in order to return a
result set.
C. The cursor declaration requires WITH RETURN TO CALLER clause in order to return a
result set.
D. The cursor declaration requires WITH RETURN clause in order to return a result set.
Answer: B
CertBus exam braindumps are pass guaranteed. We guarantee your pass for the C2090-545 exam successfully with our IBM materials. CertBus DB2 9.7 SQL Procedure Developer exam PDF and VCE are the latest and most accurate. We have the best IBM in our team to make sure CertBus DB2 9.7 SQL Procedure Developer exam questions and answers are the most valid. CertBus exam DB2 9.7 SQL Procedure Developer exam dumps will help you to be the IBM specialist, clear your C2090-545 exam and get the final success.
C2090-545 Latest questions and answers on Google Drive(100% Free Download): https://drive.google.com/file/d/0B_3QX8HGRR1md0dvQWxxRWZtWGc/view?usp=sharing
C2090-545 IBM exam dumps (100% Pass Guaranteed) from CertBus: http://www.certgod.com/C2090-545.html [100% Exam Pass Guaranteed]
Why select/choose CertBus?
Millions of interested professionals can touch the destination of success in exams by certgod.com. products which would be available, affordable, updated and of really best quality to overcome the difficulties of any course outlines. Questions and Answers material is updated in highly outclass manner on regular basis and material is released periodically and is available in testing centers with whom we are maintaining our relationship to get latest material.