DataStage - Surrogate Key Generator Stage with Database Sequence

When using the Surrogate Key Generator Stage with Database Sequence, please note that before using the sequence values:

1. The "Sequence" needs to be created in the database in order to use it. Sequence creation script:  

CREATE SEQUENCE Sequence_Name INCREMENT BY 1 START WITH 1  NOMAXVALUE CACHE 10;

2. For a Database sequence, note here to use appropriate action with respect to the following:

CYCLE: Specify CYCLE to indicate that the sequence continues to generate values after reaching either its maximum or minimum value. After an ascending sequence reaches its maximum value, it generates its minimum value. After a descending sequence reaches its minimum, it generates its maximum value.

NOCYCLE: Specify NOCYCLE to indicate that the sequence cannot generate more values after reaching its maximum or minimum value. This is the default.

3. Test the Sequence on the database server side with script: 

    Select Sequence_Name.NextVal from dual;

4. Create sequence environmental variable on DataStage server side, to make the stage/job reusable

5. Use the following in the Surrogate Key Generator Stage:   

Source Type = Database sequence 

Source Name = #db_server#.#db_name#.#schema#.<sequence_name>

The source name here should not be the <table_name> but the oracle sequence name, which we had created in the above steps. 

6. Alternatively, In the Target DB Stage you can when simply inserting into your target table, use <sequence name>.nextval in your insert statement.

Thanks!