DB2 for Linux on iSeries: Implementation Guide

116 DB2 UDB for Linux on iSeries: Implementation Guide

Figure A-8 Check the OS/400 TCP/IP host table

To run TESTCLI for the local database, issue the CALL command from the

DB2INST1’s 5250 session as follows :

CALL REDBOOK/TESTCLI PARM('AS05' 'SAMPLEDB01' 'DB2INST1' 'password')

Embedded SQL scenario

Example A-2 (TESTESQL) is the OPM RPG simple embedded SQL program

which does the same work as tectESQL.c in 3.7, “Embedded SQL scenario” on

page 69. Instead of showing the retrieved data on the screen, this program

makes a spool file. The database name and schema name is hardcoded and this

program takes no parameter.

Work with TCP/IP Host Table Entries

System: AS05

Type options, press Enter.

1=Add 2=Change 4=Remove 5=Display 7=Rename

Internet Host

Opt Address Name

127.0.0.1 LOOPBACK

LOCALHOST

192.168.1.1 AS05

Bottom

F3=Exit F5=Refresh F6=Print list F12=Cancel F17=Position to

Internet address entry 192.168.1.1 added to host table.

Tip: If the program is terminated in error, the OS/400 job state often goes to

some error state of database access. Usually they are remaining opened

database connections or transactions which have not committed yet. To clean

up the state, you should once sign off and sign on again. COMMIT or

ROLLBACK CL commands are also useful. You can issue them from the

command line.

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

Example: A-2 OPM RPG program to access Linux database (QRPGSRC/TESTESQL)

*************** Beginning of data ************************************

0001.00 FQPRINT O F 132 PRINTER

0002.00 C Z-ADD0 ID 50

0003.00 C MOVE *BLANK NAME 9

0004.00 C Z-ADD0 DEPT 50

0005.00 C MOVE *BLANK JOB 5

0006.00 C Z-ADD0 YEARS 50

0007.00 C Z-ADD0 SALARY 90

0008.00 C*

0009.00 C/EXEC SQL WHENEVER SQLERROR GO TO ERROR

0010.00 C/END-EXEC

0011.00 C*

0012.00 C/EXEC SQL DECLARE C1 CURSOR FOR

0013.00 C+ SELECT ID, NAME, DEPT, JOB, YEARS, SALARY FROM DB2INST1.STAFF

0014.00 C+ WHERE ID > 280

0015.00 C/END-EXEC

0016.00 C*

0017.00 C/EXEC SQL OPEN C1

0018.00 C/END-EXEC

0019.00 C*

0020.00 C/EXEC SQL WHENEVER NOT FOUND GO TO DONE

0021.00 C/END-EXEC

0022.00 C*

0023.00 C SQLCOD DOUNE0

0024.00 C/EXEC SQL FETCH C1 INTO :ID, :NAME, :DEPT, :JOB, :YEARS, :SALARY

0025.00 C/END-EXEC

0026.00 C SQLCOD IFEQ 0

0027.00 C EXCPTOUT

0028.00 C ENDIF

0029.00 C ENDDO

0030.00 C*

0031.00 C DONE TAG

0032.00 C/EXEC SQL CLOSE C1

0033.00 C/END-EXEC

0034.00 C*

0035.00 C ERROR TAG

0036.00 C SETON LR

0037.00 C RETRN

0038.00 C*

0039.00 OQPRINT E OUT

0040.00 O ID 4 5

0041.00 O NAME 17

0042.00 O DEPT 4 19

0043.00 O JOB 30

0044.00 O YEARS 4 38

0045.00 O SALARY2 50

****************** End of data **************************************