DB2 for Linux on iSeries: Implementation Guide

118 DB2 UDB for Linux on iSeries: Implementation Guide

Compile and run

The following command compiles the program TESTESQL and creates an

executable module. As well as Linux C embedded SQL scenario, the connection

to the target Linux database is also needed to create and store the SQL package

for this program when compiling. The USER and PASSWORD are used to

connect to the Linux SAMPLE database to create the SQL package.

CRTSQLRPG PGM(TESTESQL) RDB(SAMPLE) USER(DB2INST1) PASSWORD('password')

OPTION(*SQL) DATFMT(*USA)

Figure A-9 shows the job log of the compile. You can see that the precompiler

generates a source member in QTEMP/QSQLTEMP, then the RPG compiler

generates the program. The database connection to the SAMPLE database is

also held and an SQL package is created and stored. These are similar

processes to the Linux case as in 3.7, “Embedded SQL scenario” on page 69.

Figure A-9 TESTESQL compile JOBLOG

To run the program, we have to provide the run time user and password

information to the program. It can be done by the following command.

ADDSVRAUTE USRPRF(DB2INST1) SERVER(SAMPLE) USRID(DB2INST1) PASSWORD(‘password’)

To store the password in the system, the SYSVAL QRETSVRSEC must be set to

‘1’.

Display All Messages

System: AS05

Job . . : QPADEV0001 User . . : DB2INST1 Number . . . : 010214

Job 010214/DB2INST1/QPADEV0001 started on 11/05/03 at 10:31:06 in

subsystem QINTER in QSYS. Job entered system on 11/05/03 at 10:31:05.

> /* */

3 > CRTSQLRPG PGM(TESTESQL) RDB(SAMPLE) USER(DB2INST1) PASSWORD() OPTION(*SQL)

DATFMT(*USA)

File QSQLTEMP created in library QTEMP.

Member TESTESQL added to file QSQLTEMP in QTEMP.

Database connection started over TCP/IP or a local socket.

Database TCP/IP or local socket connection ended.

Program TESTESQL is placed in library REDBOOK. 00 highest severity.

Created on 11/05/03 at 10:32:55.

Data area RETURNCODE created in library QTEMP.

Database connection started over TCP/IP or a local socket.

SQL package TESTESQL in REDBOOK at SAMPLE has been created.

3>> dspjoblog

Bottom

Press Enter to continue.

F3=Exit F5=Refresh F12=Cancel F17=Top F18=Bottom

Appendix A. Accessing DB2 UDB for Linux from OS/400 application 119

After the setup, you can run the program simply issuing CALL TESTESQL.

Figure A-10 shows the spool data created by TESTESQL. These are the data

retrieved from Linux DB2.

Figure A-10 The spool data generated by TESTESQL

Display Spooled File

File . . . . . : QPRINT Page/Line 1/1

Control . . . . . Columns 1 - 78

Find . . . . . .

*...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+...

290 Quill 84 Mgr 10 19,818

300 Davis 84 Sales 5 15,454

310 Graham 66 Sales 13 21,000

320 Gonzal 66 Sales 4 16,858

330 Burke 66 Clerk 1 10,988

340 Edward 84 Sales 7 17,844

350 Gafney 84 Clerk 5 13,030

Bottom

F3=Exit F12=Cancel F19=Left F20=Right F24=More keys

120 DB2 UDB for Linux on iSeries: Implementation Guide