Number of questions: 60
Time allowed in minutes: 90
Required passing score: 60%
Languages: English
An IBM Certified Solution Developer - DB2 9.7 SQL Procedure Developer has significant experience with intermediate or advanced level skills in developing DB2 stored procedures, user defined functions and triggers.
An IBM Certified Solution Developer - DB2 9.7 SQL Procedure Developer has significant experience with intermediate or advanced level skills in developing DB2 stored procedures, user defined functions and triggers.
Section 1 - SQL Procedural Language (30%)
Describe how to define variables and cursors
Demonstrate the ability to code assignment statements
Demonstrate knowledge of SQL Control statements
Demonstrate knowledge of error handling
Section 2 - DB2 SQL Procedures (23%)
Demonstrate knowledge how to identify proper usage of SQL procedures
Demonstrate the ability to use the CREATE PROCEDURE statement
Demonstrate knowledge of the proper structure of an SQL procedure body
Demonstrate knowledge of how to return values and result sets from SQL procedures
Demonstrate knowledge of nested procedures
Demonstrate how to test and deploy SQL procedure
Section 3 - Functions (12%)
Demonstrate knowledge how to identify proper usage of functions
Demonstrate the ability to use the CREATE FUNCTION statement
Demonstrate knowledge of the proper structure of a function body
Demonstrate knowledge of how to return values and a table using functions
Describe how to invoke functions
Demonstrate how to test and deploy functions
Section 4 - Triggers (8%)
Demonstrate knowledge how to identify proper usage of triggers
Demonstrate the ability to use the CREATE TRIGGER statement
Demonstrate the ability to identify the actions of a trigger
Demonstrate knowledge of advanced usage of triggers
Demonstrate how to test and deploy triggers
Section 5 - Advanced Features (16%)
Demonstrate knowledge of Declared Global Temporary Tables
Describe how to use system features (ADMIN_CMD)
Describe how to use Arrays and Associated Arrays
Describe how to use Global Variables
Describe how to use Modules
Describe how to enable a database to support PL/SQL procedures
Section 6 - Related Tools (10%)
Demonstrate knowledge of application development tools
Demonstrate knowledge of Debugging with tools
Demonstrate knowledge of using Explain with tools
IBM Certified Solution Developer - DB2 9.7 SQL Procedure
Job Role Description / Target Audience
An IBM Certified Solution Developer - DB2 9.7 SQL Procedure has significant experience with intermediate or advanced level skills in developing DB2 stored procedures, user defined functions and triggers.
Recommended Prerequisite Skills
This individual has strong skills in DB2 SQL, DB2 SQL Procedural Language and in related tools, such as IBM Data Studio.
Requirements
This certification requires 2 test(s).
Test(s) required:
Click on the link(s) below to see test details, test objectives, suggested training and sample tests.
Any 1 of the following tests:
Test C2090-700 - DB2 V8.1 Family Fundamentals
Test C2090-730 - DB2 9 Family Fundamentals
Test C2090-610 - DB2 10.1 Fundamentals
Test C2090-320 - DB2 11 Fundamentals for z/OS
Test C2090-545 - DB2 9.7 SQL Procedure Developer
QUESTION 1
A developer needs to create a user-defined function that 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 WITH RETURN 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_name VARCHAR(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
WHERE employee.workdept = dept_employees.deptno
OPEN emp_info;
RETURN
Answer: C
Explanation:
A developer needs to create a user-defined function that 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 WITH RETURN 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_name VARCHAR(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
WHERE employee.workdept = dept_employees.deptno
OPEN emp_info;
RETURN
Answer: C
Explanation:
QUESTION 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
Explanation:
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
Explanation:
QUESTION 3
Which statement correctly describes characteristics of external functions?
A. External functions cannot return tables.
B. All cursors opened within an external function should stay open until the database is quiesced.
C. Scratchpads can be used to allocate dynamic memory required for multiple function
invocations.
D. Transactions can be terminated within external functions.
Answer: C
Explanation:
Which statement correctly describes characteristics of external functions?
A. External functions cannot return tables.
B. All cursors opened within an external function should stay open until the database is quiesced.
C. Scratchpads can be used to allocate dynamic memory required for multiple function
invocations.
D. Transactions can be terminated within external functions.
Answer: C
Explanation:
QUESTION 4
Which three optional clauses can be used when creating an external function? (Choose three.)
A. SCRATCHPAD
B. NOTEPAD
C. LANGUAGE
D. EXTERNAL NAME
E. DATABASEINFO
Answer: A,C,D
Explanation:
QUESTION 4
Which statement is permitted within a scalar user-defined function body?
A. COMMIT
B. INSERT
C. SIGNAL
D. LOOP
Answer: C
Explanation:
