DB2 for Linux on iSeries: Implementation Guide

56 DB2 UDB for Linux on iSeries: Implementation Guide

Figure 3-22 Connection methods summary

Figure 3-22 shows the conceptual view of the access methods provided by DB2.

Note that the only programming interfaces which are facilitated via DB2 Connect

are discussed in this book. They are represented as Program A in the chart.

There are some other native programming interfaces to access DB2 UDB for

iSeries from the Linux platform, but are not part of the DB2 UDB for Linux and not

discussed in this book. These are:

򐂰 iSeries ODBC Driver for Linux (5733-L01)

򐂰 IBM Toolbox for Java Type 4 JDBC driver

The program which uses either of these two connection methods is shown as

Program B in the chart. This program can access DB2 UDB for iSeries directly

but cannot access the Linux local database. If the database access requirement

can be restricted only to DB2 UDB for iSeries, these two are good selections.

3.4.1 Create the sample database in OS/400

In this section, we create the DB2 sample database in the OS/400 partition using

DB2 Command Line Processor (CLP). CLP is the basic command line interface

to operate DB2 databases interactively. It is like the OS/400 STRSQL interface.

DB2 Engine

AS05

Database

OS/400

DRDA AS

Schema : SAMPLEDB01

Alias : OS400

DB2 Engine

SAMPLE

Database

Linux

DB2 Connect

(DRDA AR)

CLI

ODBC

Embedded SQL

JDBC

SQLJ

Program

A

uses any of

DB2 interfaces

Program B

uses

DB2 UDB for iSeries

native interface

ODBC

JDBC

Schema : DB2INST1

Alias : SAMPLE

Chapter 3. Connectivity with DB2 UDB for iSeries 57

The sample database we create here is used by the sample programs of the

following sections. Figure 3-23 shows the CLP dialog.

Figure 3-23 Create DB2 sample database in OS/400 partition

db2inst1@linux02:~> db2

(c) Copyright IBM Corporation 1993,2002

Command Line Processor for DB2 SDK 8.1.2

You can issue database manager commands and SQL statements from the command

prompt. For example:

db2 => connect to sample

db2 => bind sample.bnd

For general help, type: ?.

For command help, type: ? command, where command can be

the first few keywords of a database manager command. For example:

? CATALOG DATABASE for help on the CATALOG DATABASE command

? CATALOG for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside

interactive mode, all commands must be prefixed with 'db2'.

To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => connect to OS400 user DBDEMO using PASSWORD

Database Connection Information

Database server = OS/400 5.2.0

SQL authorization ID = DBDEMO

Local database alias = OS400

db2 => call qsys.create_sql_sample('SAMPLEDB01')

Return Status = 0

db2 => select * from sampledb01.staff where ID > 200

ID NAME DEPT JOB YEARS SALARY COMM

------ --------- ------ ----- ------ --------- ---------

210 Lu 10 Mgr 10 20010.00 -

220 Smith 51 Sales 7 17654.50 992.80

230 Lundquist 51 Clerk 3 13369.80 189.65

240 Daniels 10 Mgr 5 19260.25 -

250 Wheeler 51 Clerk 6 14460.00 513.30

260 Jones 10 Mgr 12 21234.00 -

270 Lea 66 Mgr 9 18555.50 -

280 Wilson 66 Sales 9 18674.50 811.50

290 Quill 84 Mgr 10 19818.00 -

300 Davis 84 Sales 5 15454.50 806.10

310 Graham 66 Sales 13 21000.00 200.30

320 Gonzales 66 Sales 4 16858.20 844.00

330 Burke 66 Clerk 1 10988.00 55.50

340 Edwards 84 Sales 7 17844.00 1285.00

350 Gafney 84 Clerk 5 13030.50 188.00

15 record(s) selected.

db2 => terminate

DB20000I The TERMINATE command completed successfully.

db2inst1@linux02:~>