We're so confident of our products that we provide no hassle product exchange.
IBM A2090-545 Exam Overview:
| Certification Vendor: | IBM |
| Exam Name: | Assessment: DB2 9.7 SQL Procedure Developer |
| Exam Number: | A2090-545 |
| Exam Format: | Multiple Choice, Multiple Response, True/False |
| Passing Score: | 60% |
| Certificate Validity Period: | No fixed expiration (product version retired) |
| Exam Price: | Approx. $200 USD |
| Real Exam Qty: | 60 |
| Related Certifications: | IBM Certified Database Associate IBM Certified Database Administrator |
| Exam Duration: | 90 minutes |
| Available Languages: | English |
| Recommended Training: | IBM My Learning IBM DB2 9.7 Documentation |
| Exam Registration: | Pearson VUE IBM Registration |
| Sample Questions: | IBM A2090-545 Sample Questions |
| Exam Way: | Onsite at Pearson VUE test centers or online proctored |
| Pre Condition: | No formal prerequisites; recommended 1–2 years DB2 9.7 experience and SQL knowledge |
| Official Syllabus URL: | https://www.ibm.com/certify |
IBM A2090-545 Exam Syllabus Topics:
| Section | Weight | Objectives |
|---|---|---|
| Topic 1: SQL Procedural Language Fundamentals | 20% | - Error handling and diagnostics - Cursors and result sets - Variables, data types, and assignments - Control flow statements (IF, CASE, LOOP, WHILE) |
| Topic 2: User-Defined Functions | 20% | - CREATE FUNCTION syntax - Scalar and table functions - Function invocation and usage |
| Topic 3: Triggers | 15% | - BEFORE, AFTER, INSTEAD OF triggers - Trigger execution and limitations - CREATE TRIGGER syntax and logic |
| Topic 4: Advanced DB2 9.7 Features | 12% | - Arrays and associated arrays - Declared global temporary tables - Global variables and modules |
| Topic 5: SQL Procedures | 25% | - Testing, debugging, and deployment - CREATE PROCEDURE syntax and parameters - Returning multiple result sets - Dynamic SQL within procedures |
| Topic 6: Development Tools & Performance | 8% | - IBM Data Studio usage - SQL procedure optimization - Best practices and troubleshooting |
IBM Assessment: DB2 9.7 SQL Procedure Developer Sample Questions:
1. Which SQL procedure declaration is coded correctly?
A) CREATE PROCEDURE myproc(IN salary DOUBLE, OUT commission DOUBLE)
BEGIN
DECLARE a DOUBLE;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET commission = 0;
DECLARE my_cur CURSOR FOR SELECT * FROM employee;
SET a = .06 * salary;
SET commission = a;
END
B) CREATE PROCEDURE myproc(IN salary DOUBLE, OUT commission DOUBLE)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET commission = 0;
DECLARE a DOUBLE;
DECLARE my_cur CURSOR FOR SELECT * FROM employee;
SET a = .06 * salary;
SET commission = a;
END
C) CREATE PROCEDURE myproc(IN salary DOUBLE, OUT commission DOUBLE)
BEGIN
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET commission = 0;
DECLARE my_cur CURSOR FOR SELECT * FROM employee;
DECLARE a DOUBLE;
SET a = .06 * salary;
SET commission = a;
END
D) CREATE PROCEDURE myproc(IN salary DOUBLE, OUT commission DOUBLE)
BEGIN
DECLARE a DOUBLE;
DECLARE my_cur CURSOR FOR SELECT * FROM employee;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SET commission = 0;
SET a = .06 * salary;
SET commission = a;
END
2. In the given text below: What will be the value of the output variable BONUS?
A) 500
B) 5500
C) 1000
D) 5200
3. Given the function shown below:
Which statement will execute successfully?
A) SELECT to_upper('Seize the day!') FROM SYSIBM.SYSDUMMY1;
B) SELECT * FROM to_upper_fcn('Seize the day!')
C) SELECT * FROM TABLE(to_upper_fcn('Seize the day!')) AS T
D) SELECT to_upper('Seize the day!')
4. Given the two SQL procedures shown below:
What is the expected output if procedure S1 is invoked with the value 1 provided for parameter V1?
A) 5
B) 2
C) 7
D) NULL
5. Click the Exhibit button.
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) SQLSTATE 80000 will be returned, with the custom error message "Error: ID 1 is not valid"
C) Application Error
D) NULL
Solutions:
| Question # 1 Answer: D | Question # 2 Answer: A | Question # 3 Answer: A | Question # 4 Answer: C | Question # 5 Answer: D |
By Peter
