Moving tables between an iSeries and DB2 for Linux

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

c0d3lib@gmail.com - 23 Apr 2008 12:11 GMT

I am attempting to implement, what I believe to be, an unusual
scenario, and am hoping to find someone that has experience with
something similar. I currently have a series of physical and logical
files on an iSeries (AS/400, System i5, or whatever IBM calls it
today), version v5r4, and would like to permanently migrate them to a
DB2 UDB for Linux v8 system. All of these files are used by native
iSeries programs (written mostly in CL and RPG), and some are used by
SQL programs, as well. The goal is to make the migration as
transparent as possible, so the programs operate as if the files were
still local.

My first thought was to configure the two systems to communicate via
Distributed Relational Data Architecture (DRDA) facilities, since both
support it. Then I could create a DDM file on the iSeries that linked
to the remote table on DB2 for Linux database.

I still have several questions/concerns about this approach, and if
anyone can provide advice or hints, it would be greatly appreciated.

1.) Can the native RPG/CL programs perform all of their normal
functions on a remote DDM file, including locking a row, read or
position by a key value, update, delete, etc.

2.) How would I configure tables/views/indexes on the DB2 for Linux so
that they are equivalent to the physical and logical files on the
iSeries. For example, a physical file seems equivalent to a SQL
table, but has an optional index as part of the same object. Does this
mean I create a SQL table with an index, and point the DDM file to the
index, and not the table?

3.) Finally, is there another approach that I am not considering that
would meet my requirements? I've looked at the possibility of using
Websphere Federation Server and DB2 Connect, but they seem to only
work via an SQL interface, so the native programs would all have to be
modified to use embedded SQL. This is not really an option because of
the large number of programs in question.

Again, I'd appreciate any advice from someone that has tried something
like this, or even remotely similar.

Reply to this Message

Larry - 23 Apr 2008 22:03 GMT

> I am attempting to implement, what I believe to be, an unusual
> scenario, and am hoping to find someone that has experience with
[quoted text clipped - 18 lines]
> functions on a remote DDM file, including locking a row, read or
> position by a key value, update, delete, etc.

Not sure what this means. The native RPG/CL pgms would run on the
iseries but the data would reside on DB2 for Linux? DB2 for Linux
doesn't support files. You would have to import or load into a DB2
table. If this is what you are trying to do, I'm not sure there is any
other option besides DRDA for the ongoing access. Don't know how the
performanc would be. Are you not migrating the code to avoid the
migration costs?

> 2.) How would I configure tables/views/indexes on the DB2 for Linux so
> that they are equivalent to the physical and logical files on the
> iSeries. For example, a physical file seems equivalent to a SQL
> table, but has an optional index as part of the same object. Does this
> mean I create a SQL table with an index, and point the DDM file to the
> index, and not the table?

Not sure. Someone from DB2/i might need to answer this. I can tell you
that one way to come close is to use DRDA because it will do the
codepage and ebcidic/ascii translation for you.

> 3.) Finally, is there another approach that I am not considering that
> would meet my requirements? I've looked at the possibility of using
> Websphere Federation Server and DB2 Connect, but they seem to only
> work via an SQL interface, so the native programs would all have to be
> modified to use embedded SQL. This is not really an option because of
> the large number of programs in question.

What brings about the need for Federation Server here? And DB2 Connect
would only be needed in order for a SQL call issued on the Linux machine
to be executed against data on the i-series.

> Again, I'd appreciate any advice from someone that has tried something
> like this, or even remotely similar.

Larry E.

Reply to this Message

c0d3lib@gmail.com - 24 Apr 2008 12:53 GMT

> c0d3...@gmail.com wrote:
> > I am attempting to implement, what I believe to be, an unusual
[quoted text clipped - 54 lines]
>
> Larry E.

Thanks for the reply, Larry. Yes, The scenario you described is what
I'm trying to achieve. The tables will reside on the DB/2 for Linux
host, and need to be accessible to the iSeries RPG/CL programs. As
for your question about migrating the code, this would play into a
larger strategy that allows us to migrate some of the more interesting
processes of a legacy iSeries application. The application is way too
large to try migrating the entire thing.

Have you ever tried or seen a similar implementation using the iSeries
as a client to any non-iSeries system? Do you know of any issues I
should be expecting, such as the differences in security
implementations, etc? Performance is already a concern, but all I can
do there is measure the differences once I have at least a basic
configuration established.

Reply to this Message

Karl Hanson - 24 Apr 2008 16:42 GMT

<snip>

> Thanks for the reply, Larry. Yes, The scenario you described is what
> I'm trying to achieve. The tables will reside on the DB/2 for Linux
[quoted text clipped - 10 lines]
> do there is measure the differences once I have at least a basic
> configuration established.

DDM provides a different level of function than DRDA. DDM allows fairly
transparent transport of "file I/O" requests from the System i running
the RPG application, to a remote System i (for example) that can perform
these file I/O requests. The RPG program will typically do I/O requests
such as read by key (SETLL?), read next key equal, read by relative
record no., etc. As Larry mentioned, these are not SQL operations and
DB2 for LUW does not support "files", nor this type of file I/O (because
it is not, and does not map to, SQL).
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/ddm/rbae5
intrdf.htm

DRDA on the other hand allows transport of SQL requests between systems.
But since your RPG programs do file I/O and not SQL, DRDA would not
support your proposal. In other words, there is no function to
automatically convert file I/O requests to SQL requests, as part of
either DDM or DRDA. As you correctly surmised, in order to use DRDA
(SQL), the RPG file I/O would need to be appropriately replaced with
embedded SQL, or the equivalent.
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/index.jsp?topic=/ddp/rbal1
intro.htm

If the business logic to maintain the data currently resides on System
i, what is the motivation to move the data to DB2 on Linux? If Linux
access to data on System i is the goal, could facilities like ODBC or
JDBC (ie on a Linux client) provide that, w/o migrating the data?

--
Karl Hanson

Reply to this Message

c0d3lib@gmail.com - 24 Apr 2008 17:59 GMT

> c0d3...@gmail.com wrote:
>
[quoted text clipped - 39 lines]
> --
> Karl Hanson

The business logic is being migrated in pieces, so not all of it will
reside on the iSeries, but inevitably there will be non-migrated
processes that will still require access to the same files.

My misunderstanding of DRDA and DDM is that I thought DDM was a subset
of DRDA. I was under the impression, any operation a program could
perform using DDM would translate to some function in DRDA and hence
to SQL concepts. Thanks for clearing that up for me.

Reply to this Message

The Boss - 24 Apr 2008 18:39 GMT

>> c0d3...@gmail.com wrote:
>>
[quoted text clipped - 57 lines]
> perform using DDM would translate to some function in DRDA and hence
> to SQL concepts. Thanks for clearing that up for me.

Disclaimer:
I have no experience with iSeries, AS/400, RPG etc.

That being said, a quick Google-search for "db2+migration+rpg+linux" gave a
number of promising hits, for instance these 2 links for a company called
PKS:
http://www.pks.de/products/iSeries/migration_tools_400.htm
http://www.pks.com/english/aktuelles/pdf_dateien/2008/PKS%20MM%20JAN08%20E.pdf

HTH

Signature

Jeroen

Reply to this Message

Larry - 25 Apr 2008 01:38 GMT

I have seen clients set up i-series and z-series DRDA clients against
DB2 LUW DRDA servers. Far as I know, it works well. It's been a long
while since I've been near an i-series so unfortunately, I can't speak
as to the potential issues. Going the other way is more common.

Larry E.

>>c0d3...@gmail.com wrote:
>>
[quoted text clipped - 70 lines]
> do there is measure the differences once I have at least a basic
> configuration established.

Reply to this Message