RE: ODBC FROM iSeries - huh?

From: Joe Pluta

> Ah, now this is interesting.  I am definitely a neophyte here.  While I
> understand the basic concept of the CLI, I'm not very familiar with the
> implementation.  Does the SQLRPG precompiler generate calls to the CLI, or
> is there another layer underneath that?  In any event, it shouldn't be too
> hard to find a reference for SQL CLI, find a freeware PC DBMS
> that supports
> CLI, and see if the iSeries can access it.

Yes, the precompiler generates native CLI calls. Here is your reference to
the CLI:

http://publib.boulder.ibm.com/pubs/html/as400/v5r1/ic2924/index.htm?info/cli
/rzadpmstclilist.htm


As far as the database is concerned, you need more than just a SQL engine on
the other side. You need some type of client/server program pair to handle
communications. The CLI is not a communications protocol; it's just a
database API. Examples of this include DRDA, DB2RA (proprietary IBM),
Oracle's SQL*Net.

The client side software is known on the AS/400 as an Application Request
Driver. To the best of my knowledge (which is far from complete), the only
ARD OS/400 provides is for DRDA. This is configured through the
WRK/ADDRDBDIRE commands. There does appear to be a way for additional ARD's
to be added  --via an SQL Client Integration Exit program-- but I've never
persued any research in this area.

> Okay, this made light bulbs go off.  I can understand this completely.  A
> DBMS includes an SQL engine, which should by default respond to CLI
> requests.  This in turn provides ODBC support, because ODBC is just a
> wrapper around CLI.  But in the case of desktop databases, the entire SQL
> implementation is housed inside the ODBC driver.  If there were an actual
> SQL engine around the smaller databases, they too would in theory
> respond to
> SQL CLI calls.

Yes, the driver used by desktop databases is described a "Single Tier"
architecture.


> And I'll bet that the true commercial databases such as Oracle, Informix,
> Sybase, et al, all have actual SQL engines and hence should
> communicate via
> ANSI CLI.

These drivers are described as "Multiple Tier" architecture.


> That raises a different question - is there a "generic" ODBC driver that
> simply converts ODBC calls to ANSI SQL CLI?  If so, and if this
> driver were
> ported to the iSeries, wouldn't it magically provide ODBC access
> to any DBMS
> with a true SQL engine?
>
> Just wondering.

You don't really need a generic driver. You see, ODBC and CLI are virtually
equivalent in terms of syntax, and semantics. The SQL CLI is a standard
created by the X/Open Standards Group. ODBC is MS's implementation of that
standard. Of course, they've "embraced and extended" it, but they remain
very similar.

The big trick is how to get the two machines communicating.

Personally, I don't see why anyone would bother. The conventional argument
is that they wish to read/update a remote database directly through RPG, but
I wonder if they aren't thinking in terms of doing a simple READ/CHAIN et al
against Oracle. Because that's not the way it works. They have to write to
the CLI API, and that doesn't resemble what most think of as database access
in RPG. Have a look for yourself:

http://www.as400.ibm.com/tstudio/db2_400/cli_rpg/rpg_intro.htm

Call me crazy, but I'd much rather just use JDBC.


Regards,

John Taylor