C2090-543 DB2 9.7 Application Development

Test information:
Number of questions: 60
Time allowed in minutes: 90
Required passing score: 60%
Languages: English

This certification exam certifies that the successful candidate has important knowledge, skills, and abilities necessary to design, build, execute, and deploy DB2 9.7 database applications.

Significant experience with intermediate or advanced level skills in developing applications that interact with DB2 databases. This individual has strong skills in DB2 SQL and in database application development concepts.

Section 1 – Database Objects (11%)
Demonstrate knowledge of naming conventions of DB2objects
Aliases
Views
Tables
Describe when to use SQL routines, functions and modules
How to use DB2’s built-in routines
Functions
Stored procedures
Demonstrate knowledge of data types
Integer
Floating point
Strings
Date/time
LOBs
XML data type

Section 2 – Traditional Data Manipulation (32%)
Demonstrate knowledge of authorities needed to access datain an application
Describe the differences between dynamic and static SQL
Demonstrate knowledge of how to query databases acrossmultiple tables and views
Given a scenario, demonstrate knowledge of changing data
Insert
Update
Delete
Demonstrate knowledge of cursors
Types of cursors
Read only
Updatable
Scrollable
Scope of cursors
Create and manipulate cursors
Given a scenario, demonstrate the ability to manipulate largeobjects
Locators
Demonstrate the ability to manage a unit of work
Isolation levels
COMMIT
ROLLBACK
SAVEPOINT

Section 3 – XML Data Manipulation (15%)
Given a scenario, demonstrate knowledge of XML schemavalidation
XML schema evolution
Demonstrate the ability to use XML functions
XMLPARSE
Whitespace handling
XMLSERIALIZE
XMLTRANSFORM
Functions for preparing XML documents fromrelational data
Given a scenario, demonstrate knowledge of how to useXQuery expressions and evaluate the results
Given a scenario, describe how to construct queries thatretrieve both traditional and XML data

Section 4 – Core Concepts (27%)
Describe how to bind and rebind a package
Given a scenario, demonstrate knowledge of using parametermarkers
Demonstrate knowledge of how to connect to a database (mayuse scenarios)
JDBC
ADO.NET
CLI/ODBC
php_ibm_db2
Embedded SQL
Demonstrate the ability to submit an SQL statement
JDBC
ADO.NET
CLI/ODBC
php_ibm_db2
Embedded SQL
Describe how to manipulate result sets
JDBC
ADO.NET
CLI/ODBC
php_ibm_db2
Embedded SQL
Demonstrate knowledge of problem determination
JDBC
ADO.NET
CLI/ODBC
php_ibm_db2
Embedded SQL

Section 5 – Advanced Programming (15%)
Given a scenario, describe how to create and register externalstored procedures
Given a scenario, describe how to create external functions
OLE DB table
External table
Given a scenario, demonstrate knowledge of how changingdata will work when referential constraints are involved
Demonstrate knowledge of distributed unit of work (two phasecommit)
Demonstrate knowledge of trusted contexts
Demonstrate knowledge of using advanced database objects
Global declared temporary tables
Sequences
MQTs

IBM Certified Application Developer – DB2 9.7 for Linux, Unix and Windows

Job Role Description / Target Audience
This certification exam certifies that the successful candidate has important knowledge, skills, and abilities necessary to design, build, execute, and deploy DB2 9.7 database applications.

Recommended Prerequisite Skills
Significant experience with intermediate or advanced level skills in developing applications that interact with DB2 databases. This individual has strong skills in DB2 SQL and in database application development concepts.

Requirements

This certification requires 2 test(s).
Test(s) required:

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-543 – DB2 9.7 Application Development

---

QUESTION 1
Which condition will prevent a developer from using the DB2 Call Level Interface in an application?

A. The developer must control the cursor names to comply with company naming standards.
B. An SQL precompiler is not available to the developer.
C. The application must create an external scalar function with the CREATE FUNCTION
statement.
D. The DECLARE CURSOR statement needs to be used.

Answer: D

Explanation:

---

QUESTION 2
The statement below was used to create a cursor named CSR1:
DECLARE csr1 DYNAMIC SCROLL CURSOR FOR
SELECT * FROM employee;
After fetching all of the rows in the result set associated with cursor CSR1, which two statements
can be used to fetch the data starting at the end of the result set? (Choose two.)

A. FETCH LAST
B. FETCH PRIOR
C. FETCH RELATIVE
D. FETCH OFFSET

Answer: B,C

Explanation:

---

QUESTION 3
Click the Exhibit button.
CREATE TABLE store(sid INTEGER, info XML);
INSERT INTO store VALUES (1,
‘<storeinfo sid=”1″>
<name>Grocery A</name>
<items>
<fruit><name>Mango</name><price>1.20</price></fruit>
<fruit><name>Apple</name><price>0.50</price></fruit>
<dessert><name>Ice Cream</name><price>6.00</price></dessert>
</items>
</storeinfo>’);
Given the statements shown in the exhibit, a user executes the query shown below:
XQUERY for $store in db2-fn:xmlcolumn(‘STORE.INFO’)/storeinfolet $items := $store/items/fruit,
$count := fn:count($items) return <itemcount>$count</itemcount>
What is the output?

A. 2
B. <itemcount>2</itemcount>
C. <itemcount>1</itemcount>
D. <itemcount>$count</itemcount>

Answer: D

Explanation:

---

QUESTION 4
You have created a stored procedure (MULTIRESULTS()) on the server which will return multiple
result sets. Whiledeveloping a PHP application using the IBM_DB2 extension, you want to call the
stored procedure. The code shown below will execute the stored procedure:
$stmt = db2_exec($conn, ‘CALL multiResults()’);
How can you fetch all of the result sets from $stmt?

A. Call db2_fetch_object($stmt) for the first result set; call db2_next_result($stmt) for more result
sets.
B. Call db2_next_result($stmt) for each result set.
C. Call db2_fetch_object($stmt) for each result set.
D. Call db2_next_result($stmt) for each result set; call db2_fetch_object($stmt) for each row in the
result set.

Answer: A

Explanation:

Click here to view complete Q&A of C2090-543  exam
Certkingdom Review

Best IBM C2090-543 Certification, IBM C2090-543 Training at certkingdom.com