Surrogate Key generator using DB SEQ option

[bensonian]

I am using Surrogate key generator stage using 'Database Sequence' option.

The database is in Oracle (both source and target).

In surrogate key generator stage

Source Type = Database sequence
Source Name = #db_server#.#db_name#.#schema#.<table_name>

1. Does both Datastage server and Db server reside on the same machine or local??
2. How can we set up communication if they are not on the same server/machine?

Thanks in advance

[bensonian]

Using Surrogate kye generator stages, i was trying to generate a surrogate key with 'Database sequence' option.

1. Do you know where i need to specify 'Database sequence'

<table_name>.nextval from dual
sqlcode is: -2289
esql complaint: ORA-02289: sequence does not exist

"Table is created under the same schema,even select * from dual & commited too".

2. Does SKY Gen stage require a Database sequence to be created externally.. some where ????


Thanks

I am using Surrogate key generator stage using 'Database Sequence' option.

The database is in Oracle (both source and target).

In surrogate key generator stage

Source Type = Database sequence
Source Name = #db_server#.#db_name#.#schema#.<table_name>

Thanks in advance

[ArndW]

A "sequence" needs to be created in the database in order to use it, these are not associated with tables. You will need to issue a command such as:

Code: Select all

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

and then you can use the construct "select MySequence.NextVal from dual;"

[chulett]

Well... they are "associated" with tables in the sense that they are generally (as in a best practice) named after the table that the sequence will be used with, but you're right that they are standalone objects and can be named anything you like.

[bensonian]

A "sequence" needs to be created in the database in order to use it, these are not associated with tables. You will need to issue a command such as:

Code: Select all

CREATE SEQUENCE MySequence INCREMENT BY 1 ST ...[/quote]

Could you please send the whole message you posted, as i am not a premium member, Thanks.

 one more quick question. After i create the sequence, where do i need to specify it in the Surrogate key generator stage or not? 

Thank you

[jreddy]

[quote="bensonian"]
Source Name = #db_server#.#db_name#.#schema#.<table_name>

[/quote]

The source name here should not be the <table_name> but the oracle sequence name. The oracle sequence should be already created in the DB

[girija]

I hope you are using surrogate key generator 'using oracle sequence' for
a specific reason. But if you are simply insert into your target table, use <sequence name>.nextval in your insert statement.