Modernizing IBM eServer iSeries Application Data Access - A Roadmap Cornerstone

244 Modernizing IBM Eserver iSeries Application Data Acess - A Roadmap Cornerstone

As of January 2004, DB2 Development Center Version 8.1 only supports SQL stored

procedure development when used with DB2 UDB for iSeries. Even though DB2 UDB for

iSeries supports user defined function and Java stored procedures, the DB2 Development

Center does not support the development of these objects on iSeries servers (support is

planned for a future release of DB2 Development Center). Despite the current limitations,

iSeries programmers will find the DB2 Development Center a useful tool for creating,

modifying, and testing SQL stored procedures.

To access the DB2 Development Center, load the DB2 Application Development client, which

is part of the DB2 Personal Developer’s Edition and can be downloaded from IBM’s Web site:

http://www14.software.ibm.com/webapp/download/search.jsp?rs=db2pde

10.5 DB2 Query Management Facility

Query Management Facility (QMF) for Windows is a query and reporting tool set for IBM's

DB2 relational database management system. The Administrator function is used to define

the connection to the DB2 UDB server such as iSeries Server V5.1 or later. The end user can

use a graphical version of the query to build the queries without SQL knowledge by using the

diagram or prompted interfaces. In addition, the QMF companion product has the conversion

utilities available to convert Query/400 or Query Manager/400 into corresponding QMF for

Windows objects. You can modernize the existing query reports instead of recreating them

from scratch.

10.5.1 Migrating existing queries

In the following example, we demonstrate how to convert a query and a query form for iSeries

to QMF for Windows objects. We also use QMF for Windows V8.1 to run a query with a query

form from those objects.

We have *QMQRY object, MYQRY, and *QMFORM object, MYFRM, on the iSeries server.

We use MYQRY and MYFRM to create a report summarizing the order amount and sorted by

customer name, as shown in Figure 10-22.

Figure 10-22 Report created by query objects on iSeries server

Chapter 10. DB2 Development Tools 245

The steps are:

1. We use the commands RTVQMQRY and RTVQMFORM to export our query objects,

MYQRY and MYFRM, respectively. We also specify the source file that is used to receive

the query definitions as a source member.

Here is an example of how to export our query manager object and its source

(Figure 10-23).

RTVQMQRY QMQRY(MYQRY) SRCFILE(QQMQRYSRC)

Figure 10-23 Query Management Query source

Since Query Manager/400 uses the system naming convention, which does not comply

with QMF, we have to change the SQL statements to use the SQL naming convention

instead. You can choose to change either in iSeries before transferring to PC or in QMF

for Windows.

FROM "ITSO4710"."ORDERHDR" A,

"ITSO4710"."CUSTOMER" B

The following shows how export a query form object and its source (Figure 10-24 on

page 246).

RTVQMFORM QMQRY(MYFRM) SRCFILE(QQMFORMSRC)

H QM4 05 Q 01 E V W E R 01 03 04/08/31 17:57

V 1001 050

SELECT

-- Columns

B.CUSTOMER_NAME, sum(a.order_total) AS TOTAL_ORDER_AMT

-- Tables

FROM "ITSO4710"/"ORDERHDR" A,

"ITSO4710"/"CUSTOMER" B

-- Join Conditions

WHERE (A.CUSTOMER_NUMBER = B.CUSTOMER_NUMBER)

-- Summary Columns

GROUP BY B.CUSTOMER_NAME

-- Sort Columns

ORDER BY B.CUSTOMER_NAME

246 Modernizing IBM Eserver iSeries Application Data Acess - A Roadmap Cornerstone

Figure 10-24 Query management form source

2. Transfer the Query/400 definition source members to PC. You can use either File Transfer

Protocol (FTP) or the iSeries Access Data transfer function. In our example we store the

files, MYQRY.QRY and MYFRM.FRM, in the directory c:\temp.

H QM4 05 F 01 E V W E R 01 03 04/09/01 13:43

V 1001 050

T 1110 002 005 1114 007 1115 006 1117 005 1118 003 1113 062

R 2 C 1 Customer name

R SUM 2 K0 2 Order amount

V 1201 001 0

V 1202 001 2

T 1210 004 003 1212 004 1213 006 1214 055

R 1 LEFT &DATE &TIME

R 1 RIGHT Page no. &PAGE

R 2 CENTER My company

R 3 CENTER Summary order amount by customer name

V 1301 001 2

V 1302 001 0

V 1401 002 NO

V 1402 004 1

V 1403 006 0

V 1501 001 1

V 1502 003 YES

V 1505 003 YES

V 1503 003 YES

V 1508 003 YES

V 1507 003 YES

V 1510 003 YES

V 3080 001 1

V 3101 002 NO

V 3102 002 NO

V 3103 001 0

....

....

....

V 3203 006 0

V 3204 001 1