We've been using DB2 as a relational repository for data that is shared by our Mainframe applications (Cobol, DL-1, z/VSE) and our distributed applications (Java, Oracle 10g, Linux).

We are currently evaluating Federation Server to access Oracle directly from the Mainframe. There is no z/VSE Oracle client.

I have Federation Server running on SuSe Linux and I'm pointing to an Oracle 10g database on another Linux server that contains a duplicate of part of my DB2 production database (Tables, Views, etc.).

In DB2, we used Identity columns but this doesn't exist in Oracle so I'm using a Sequence with an ON INSERT trigger to duplicate the process.

In DB2, right after the insert, I can say,
SELECT IDENTITY_VAL_LOCAL()
INTO :ACCT-ACCOUNT-ID
FROM SYSIBM.SYSDUMMY1
to get the value of the identity column for use as a key when inserting child rows.

This doesn't exist in Oracle (that I know of).

From the DB2 side, how can I get the last value of the sequence?

SELECT sequence_name.CURRVAL from DUAL; fails because DUAL doesn't exist and the sequence name is not defined.

Is it possible to define a Nickname for an Oracle Sequence?

Ideas?

You could create a UDF on the Oracle side and a function mapping on the DB2 side.

That's what I did. Since there are several primary key columns that were identity columns in DB2 and are not being loaded by sequences in Oracle, I created a stored procedure and defined it to DB2. An insert to the table executes the ON INSERT trigger that populates the column from the sequence. The program calls the stored procedure and passes the column name, the stored procedure branches to the correct "SELECT sequenceName.CURRVAL from DUAL" statement and returns the value.

We did find that with DB2 VM/VSE there is a limit of 18 characters for a stored procedure name (including the schema qualifier) so had to change the nickname to abreviate the name.