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
Surrogate Key generator using DB SEQ option
Moderators: chulett, rschirm, roy
Re: Surrogate Key generator using DB SEQ option
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
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
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:
and then you can use the construct "select MySequence.NextVal from dual;"
Code: Select all
CREATE SEQUENCE MySequence INCREMENT BY 1 START WITH 1 NOMAXVALUE CACHE 10;
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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
[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
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