New Released Braindump2go IBM A2090-735 Dumps PDF – Questions and Answers Updated with IBM Official Exam Center! Visit Braindump2go and download our A2090-735 Exam Questions Now, Pass A2090-735 100% at your first time!
Exam Code: A2090-735
Exam Name: Assessment: DB2 9.5 SQL Procedure Developer-Assessment
![1[10]](http://www.itexamquiz.com/braindump2go/bdimages/3db1a5ccc352_C86A/110.jpg "1[10]")
QUESTION 1
Given the statement shown below:
SELECT ROW CHANGE TOKEN FOR dept, RID_BIT (dept)
FROM dept WHERE deptno =’A00′ WITH UR
Which two statements are true? (Choose two.)
A. The statement is selecting two columns from DEPT table.
B. The statement will allow the latest ROW CHANGE TOKEN value to be returned.
C. The statement will allow the earliest ROW CHANGE TOKENvalue to be returned.
D. The statement will return a TIMESTAMP value.
E. The statement uses optimistic locking.
Answer: BE
QUESTION 2
Which CREATE PROCEDURE statement option should be used if you plan on issuing a DECLARE GLOBAL TEMPORARY TABLEstatement from within the SQL procedure body?
A. CONTAINS SQL
B. READS SQL DATA
C. MODIFIES SQL DATA
D. LANGUAGE SQL
Answer: C
QUESTION 3
Click the Exhibit button.
***MISSING EXHIBIT***
CREATE PROCEDURE testproc( IN i1 INT, INOUT i3 INT)
SPECIFICtestproc
BEGIN
SET i3 = i1;
END
CREATE PROCEDURE testproc( IN i1 INT, INOUT i2 INT, INOUT i3 INT)
SPECIFIC testp
BEGIN
SET i3 = i1 * i2;
END
Given that the statements in the exhibits have executed successfully, which solution contains the complete set ofcommands that could be used to drop both procedures in the order presented?
A. DROP PROCEDURE testp;
DROP PROCEDURE testp;
B. DROP PROCEDURE testp;
DROP PROCEDURE testproc;
C. DROP SPECIFIC PROCEDURE testproc;
DROP PROCEDURE testproc;
D. DROP PROCEDUREtestproc(INT);
Answer: C
QUESTION 4
Click the Exhibit button.
***MISSING EXHIBIT***
CREATE FUNCTION sum(a INT, b INT)
RETURNS INTEGER
SPECIFIC sum_of_2
RETURN a + b;
CREATE FUNCTION sum(a INT, b INT, c INT)
RETURNS INTEGER
SPECIFIC sum_of_3
RETURNa + b + c;
Given the two functions in the exhibit, what is the correct command to invoke the function which calculates the sum of two numbers from an SQL procedure?
A. SELECT sum_of_2 FROM table1;
B. SELECT sum(2,4,?);
C. SET res_sum = sum(2,6);
D. CALLsum(?,?,?);
Answer: C
QUESTION 5
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: BD
QUESTION 6
Given the SQL statementshown 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 positionedupdates.
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
QUESTION 7
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
QUESTION 8
What will be the initial value of V_MAX in thedeclaration statement shown below?
DECLARE v_max DECIMAL(9,2);
A. 0.0
B. 2
C. 9
D. NULL
Answer: D
QUESTION 9
Which statement should be used to declare an array with at most 10 elements of type INTEGER?
A. DECLARE sub_total INTEGER[10];
B. DECLARE sub_total[10] INTEGER;
C. CREATE TYPE sub_total AS INTEGER[10];
D. CREATE TYPE sub_total[10] AS INTEGER;
Answer: C
QUESTION 10
What are two valid DECLARE statements in an SQL procedure? (Choose two.)
A. DECLARE var1 INTEGER;
B. DECLARE var1DECIMAL [9];
C. DECLARE var1 XML;
D. DECLARE var1 CURRENT DATE;
E. DECLARE var1[10] INTEGER;
Answer: AC
QUESTION 11
Which steps must be followed to return a result set from an SQL procedure?
A. 1. Create the procedure using the DYNAMIC RESULTSETS clause.
2. Declare the cursor.
3. Open the cursor in the SQL procedure.
4. Close the cursor.
5. Return to the application.
B. 1. Create the procedure using the DYNAMIC RESULTSETS clause.
2. Declare the cursor using the WITH RETURN clause.
3. Open thecursor in the SQL procedure.
4. Return to the application.
C. 1. Create the procedure using the WITH RETURN clause.
2. Declare the cursor using the DYNAMIC RESULT SETS clause.
3. Open the cursor in the SQL procedure.
4. Return to the application.
D. 1. Create the procedure using the WITH RETURN clause.
2. Declare the cursor using the DYNAMIC RESULT SETS clause.
3. Open the cursor in the SQL procedure.
4. Close the cursor.
Answer: B
QUESTION 12
Which statement can be used to declare a variable insidean 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
QUESTION 13
What are two valid special registers? (Choosetwo.)
A. CURRENT_CLIENT_ACCT
B. CURRENT_SCHEMA
C. CURRENT_PATH
D. CURRENT_DATETIME
E. CURRENT_PARTITION
Answer: BC
QUESTION 14
Which statement will successfully create an SQL procedure that returns the name of the current month?
A. CREATEPROCEDURE 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) INTOtoday;
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
Braindump2go Offers the Latest IBM A2010-503 Practice Tests Questions and Answers in PDF & VCE, We Promise 100% A2010-503 Exam Pass Or Full Money Back (Have a try- If success, you will get a high pay job! Failed, nothing, money back!)! We provide Instant Download of our A2010-503 Dumps after Payment so you can study earlier than others!
![1[5]](http://www.itexamquiz.com/braindump2go/bdimages/3db1a5ccc352_C86A/15.jpg "1[5]")