Page 1 of 1
Surrogate Key generator using DB SEQ option
Posted: Fri Jul 18, 2008 9:59 am
by 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
Re: Surrogate Key generator using DB SEQ option
Posted: Fri Jul 18, 2008 1:22 pm
by 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
bensonian wrote: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
Posted: Sat Jul 19, 2008 1:13 am
by 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;"
Posted: Sat Jul 19, 2008 7:05 am
by 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.
Posted: Mon Jul 21, 2008 8:57 am
by bensonian
ArndW wrote: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
Re: Surrogate Key generator using DB SEQ option
Posted: Mon Jul 21, 2008 12:30 pm
by 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
Posted: Mon Jul 21, 2008 12:46 pm
by 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.