Simulation of C2090-735 question materials and cram for IBM certification for customers, Real Success Guaranteed with Updated C2090-735 pdf dumps vce Materials. 100% PASS DB2 9.5 SQL Procedure Developer (C2090-735) exam Today!
2021 May C2090-735 Study Guide Questions:
Q21. Click the Exhibit button.
***MISSING EXHIBIT***
CREATE FUNCTION emp_birthmonth (month INTEGER)
RETURNS TABLE (l_name VARCHAR(15),
f_name VARCHAR(12),
b_month VARCHAR(20))
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
RETURN
SELECT lastname, firstnme, MONTHNAME(birthdate) FROM employee WHERE MONTH(employee.birthdate) = emp_birthmonth.month
A user-defined function was created using the statement shown in the exhibit. What is the correct way to invoke this function from the DB2 Command Line Processor?
A. SELECT * FROM emp_birthmonth(8)
B. VALUES emp_birthmonth(8)
C. SELECT * FROM TABLE(emp_birthmonth(8))
D. VALUES emp_birthmonth(INTEGER(8))
Answer: C
Q22. The CREATE PROCEDURE statement shown below was executed against a database called MYDB.
CREATE PROCEDURE myschema.proc1(IN p1 INTEGER, OUT p2 CHAR(4), OUT p3 SMALLINT)
BEGIN
SET p2 = 'abc'
END
Which two CREATE PROCEDURE statements, when executed against the same database, will succeed? (Choose two.)
A. CREATE PROCEDURE myschema.proc1(IN p1 CHAR(4), OUT p2 INTEGER)
BEGIN
SET p2 = 123;
END
B. CREATE PROCEDURE myschema.proc1(IN p1 INTEGER, OUT p2 CHAR(4),
OUT p3 CHAR(4))
BEGIN
SET p2 = 'abc'
END
C. CREATE PROCEDURE myschema.proc1(IN p1 CHAR(4), OUT p2 INTEGER,
OUT p3 SMALLINT)
BEGIN SET p2 = 123; END
D. CREATE PROCEDURE otherschema.proc1(IN p1 CHAR(4), OUT p2 CHAR(4),
OUT p3 CHAR(4))
BEGIN
SET p2 = 'abc'
END
E. CREATE PROCEDURE myschema.proc1(IN p1 NUMBER, OUT p2 NUMBER,
OUT p3 NUMBER)
BEGIN
SET p2 = 'abc'
END
Answer: A,D
Q23. In which type of table space must global temporary tables be created?
A. REGULAR
B. LONG
C. SYSTEM TEMPORARY
D. USER TEMPORARY
Answer: D
Improve C2090-735 rapidshare:
Q24. Click the Exhibit button.
***MISSING EXHIBIT***
A table named DEPT is created using the following DDL:
CREATE TABLE dept (id INT NOT NULL, name CHAR(9), salary DEC(7,2), comm
DEC(7,2), PRIMARY KEY (id))
Immediately after the table is created, the following CREATE PROCEDURE statement
is issued:
CREATE PROCEDURE test5 ()
BEGIN NOT ATOMIC DECLARE v1 VARCHAR(10)
INSERT INTO dept ( id, name) VALUES ( 10, 'Sales')
INSERT INTO dept ( id, name) VALUES ( 20, 'Service')
INSERT INTO dept ( id, name) VALUES ( 30, 'Service')
SIGNAL SQLSTATE '70000'
INSERT INTO dept ( id, name) VALUES ( 40, 'Service')
END
The procedure TEST5 shown in the exhibit was invoked.How many rows will be added to the DEPT table?
A. 1
B. 2
C. 3
D. 4
Answer: C
Q25. Which two statements are true when working with triggers? (Choose two.)
A. Triggers can be used to enforce data integrity rules.
B. Triggers can be used with both tables and views.
C. Triggers can be altered.
D. Triggers can be used on Materialized Query Tables.
E. Triggers can be defined using Java or C
Answer: A,B
Q26. Click the Exhibit button.
***MISSING EXHIBIT***
CREATE TABLE ar.sales (customer_id INTEGER, sales_amt DECIMAL(5,2),
rct TIMESTAMP NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP); INSERT INTO ar.sales (customer_id, sales_amt) VALUES(1, 20.5); SELECT * FROM ar.sales WHERE ROW CHANGE TIMESTAMP FOR ar.sales <= CURRENT TIMESTAMP; A DB2 Command Line Processor (CLP) file contains the set of statements shown in the exhibit.
What does this sequence of statements illustrate?
A. work identity
B. row sequencing
C. row change tokenizing
D. optimistic locking
Answer: D
Tested C2090-735 pack:
Q27. What demonstrates the correct syntax for assigning three rows to the EMPNO, FIRSTNAME, and LASTNAME columns of a table named EMPLOYEE?
A. INSERT INTO employee (empno, firstname, lastname)
VALUES (100, 200, 300, 'John', 'Jane', 'Paul', 'Doe', 'Smith', 'Jones')
B. INSERT INTO employee (empno, firstname, lastname)
VALUES (100, 'John', 'Doe'), (200, 'Jane', 'Smith'), (300, 'Paul', 'Jones')
C. SET (empno, firstname, lastname)
VALUES (100, 200, 300, 'John', 'Jane', 'Paul', 'Doe', 'Smith', 'Jones')
FOR employee
D. SET (empno, firstname, lastname)
VALUES (100, 'John', 'Doe'), (200, 'Jane', 'Smith'), (300, 'Paul', 'Jones')
FOR employee
Answer: B
Q28. Given the statement shown below:
CREATE TRIGGER newprod NO CASCADE BEFORE INSERT ON product REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL BEGIN ATOMIC SET (n.description) = XMLVALIDATE(n.description ACCORDING TO XMLSCHEMA URI 'http://posample.org/product.xsd'); END
What happens to NEWPROD if 'http://posample.org/product.xsd' is dropped?
A. NEWPROD will validate against a new schema.
B. NEWPROD will raise an exception.
C. NEWPROD is marked as inoperative.
D. NEWPROD is dropped.
Answer: C
Q29. Click the Exhibit button.
***MISSING EXHIBIT***
CREATE PROCEDURE procmain ()
DYNAMIC RESULT SETS 2
BEGIN
CALL procA();
END
CREATE PROCEDURE procA ()
DYNAMIC RESULT SETS 0
BEGIN
CALL procB();
END
CREATE PROCEDURE procB ()
BEGIN
DECLARE c_1 CURSOR WITH RETURN TO CALLER FOR
SELECT * FROM sysibm.sysdummy1;
DECLARE c_2 CURSOR WITH RETURN TO CLIENT FOR
SELECT * FROM sysibm.sysdummy1;
OPEN c_1;
OPEN c_2;
END
If the three procedures were built as shown in the exhibit, what will take place if an application invokes SQL procedure PROCMAIN?
A. 0 result sets are returned.
B. 1 result set is returned.
C. 1 result set is returned with a warning.
D. 2 result sets are returned with a warning.
Answer: B
Q30. Which statement will create a scalar function named FCN1?
A. CREATE SCALAR FUNCTION fcn1(v1 CHAR(3)) RETURNS CHAR(3)
B. CREATE FUNCTION fcn1(v1 CHAR(3)) RETURNS ROW CONTAININGCHAR(3)
C. CREATE SCALAR FUNCTION fcn1(v1 CHAR(3)) RETURNS ROW CONTAINING CHAR(3)
D. CREATE FUNCTION fcn1(v1 CHAR(3)) RETURNS CHAR(3)
Answer: D