RE: SQL multi member

["Larry Loen" <lwloen@xxxxxxxxxx>]

Richard D. Dettinger wrote:
>Shouldn't be any need for a copy.  Use the SQL "Create alias" command.

>Syntax is something like:
>create alias cujosql.nameit for cujolib.mytable (member)

I have to deal with multiple members all the time and (for technical
reasons) need to use Java and SQL.

Rich is right, here.  In addition, it is not very hard to encapsulate the
alias operation into an object.

Basic steps:

1.  The constructor either obtains or is passed a data base connection.

2.  It is given the OS/400 file and member name and (if necessary, the
library AKA schema).

3.  An alias file name is derived from System.currentTimeMillis() and some
leading alphabetic string.  I usually use a single character so that I can
derive a nine digit number.  Note carefully that you must account for the
possibility that currentTimeMillis returns a negative number.  I usually
either take the absolute value or do a >>> of 1.

4.  A delay of several milliseconds is inserted (this is a bit of a hack to
ensure uniqueness -- if you have need for multiple, concurrent alias
creation, you'll have to put in some sort of system-wide serialization
trick here.  I don't have that problem and, truth be told, you may not
either).

5.  The CREATE ALIAS operation is constructed as a Java string using the
information from step 3 and the file/member name from step 2.  If it fails,
pass on the SQL Exception.

6.  A "fetch" method in the object will return the name from step 3, which
can then be used in ordinary SQL statements pretty much wherever a TABLE
could go.

The CREATE ALIAS operation will (IIRC) create a DDM or some simlar object
that is modest in size.  As far as operations go, I have never been able to
tell the difference between an alias name and a regular SQL table name.

You can do a DROP ALIAS operation when you're all done and be rid of it, or
let it hang around if you like.

The alias function isn't as well known because it is of relatively recent
vintage (came in around V4R1 or V4R2 IIRC).  However, since this IS the
Java list, it is a technique worth knowing.  This is because there is no
other support for members as such in SQL and if you are using
client/server, OVRDBF also isn't available as an "out."


Larry W. Loen  -   Senior Linux, Java, and iSeries Performance Analyst
                          Dept HP4, Rochester MN