Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2009

    Unanswered: Federation Server to Oracle 10g - Sequences

    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,
    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?


  2. #2
    Join Date
    Jun 2003
    Toronto, Canada
    Provided Answers: 1
    You could create a UDF on the Oracle side and a function mapping on the DB2 side.
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2009


    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts