IBM DB2 User Manual

Without the capability for multiple, concurrently open iterators for a single SQL
statement, if you want to select employee and salary values for a specific employee
number, you need to define a different SQL statement for each employee number,
as shown in Figure 41.
Figure 42 demonstrates how you can perform the same operations when you have
the capability for multiple, concurrently open iterators for a single SQL statement.
Related concepts
“Data retrieval in SQLJ applications” on page 123
Multiple open instances of an iterator in an SQLJ application
Multiple instances of an iterator can be open concurrently in a single SQLJ
application. One application for this ability is to open several instances of an
iterator that uses host expressions. Each instance can use a different set of host
expression values.
MultiIter iter1 = null; // Iterator instance for retrieving
// data for first employee
String EmpNo1 = "000100"; // Employee number for first employee
#sql [ctx] iter1 =
{SELECT EMPNO, SALARY FROM EMPLOYEE WHERE EMPNO = :EmpNo1};
// Assign result table to first iterator
MultiIter iter2 = null; // Iterator instance for retrieving
// data for second employee
String EmpNo2 = "000200"; // Employee number for second employee
#sql [ctx] iter2 =
{SELECT EMPNO, SALARY FROM EMPLOYEE WHERE EMPNO = :EmpNo2};
// Assign result table to second iterator
// Process with iter1
// Process with iter2
iter1.close(); // Close the iterators
iter2.close();
Figure 41. Example of concurrent table operations using iterators with different SQL
statements
...
MultiIter iter1 = openIter("000100"); // Invoke openIter to assign the result table
// (for employee 100) to the first iterator
MultiIter iter2 = openIter("000200"); // Invoke openIter to assign the result
// table to the second iterator
// iter1 stays open when iter2 is opened
// Process with iter1
// Process with iter2
...
iter1.close(); // Close the iterators
iter2.close();
...
public MultiIter openIter(String EmpNo)
// Method to assign a result table
// to an iterator instance
{
MultiIter iter;
#sql [ctxt] iter =
{SELECT EMPNO, SALARY FROM EMPLOYEE WHERE EMPNO = :EmpNo};
return iter; // Method returns an iterator instance
}
Figure 42. Example of concurrent table operations using iterators with the same SQL
statement
Chapter 4. SQLJ application programming 129

IBM DB2 Specifications

General

Developer	IBM
Initial Release	1983
License	Proprietary
Written in	C, C++
Category	Database Management System (RDBMS)
Operating System	Linux, Windows
Programming Languages	C, C++
Data Models	Relational
Editions	Community, Standard, Advanced
Key Features	high availability, scalability