Sequence generated in DB2
Moderators: chulett, rschirm, roy
Sequence generated in DB2
Hi,
I have generated a Sequence in DB2. How can i import or use it in Datastage? I want to use it as a key generator in my Job.
I have generated a Sequence in DB2. How can i import or use it in Datastage? I want to use it as a key generator in my Job.
-
- Participant
- Posts: 2
- Joined: Mon Aug 04, 2008 11:54 pm
- Location: kolkata
Re: Sequence generated in DB2
Hi In target DB2 stage You can use option user defiened update and insert . Then you can assign value like below.Abhyankar wrote:Hi,
I have generated a Sequence in DB2. How can i import or use it in Datastage? I want to use it as a key generator in my Job.
INSERT INTO
schema name .Table name
(
Key field
)
VALUES
(
NEXTVAL FOR schema name .Sequence name
)
amritendra kumar
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: Sequence generated in DB2
You can use its NEXTVAL property in any SELECT, including on a reference link. Just don't expected it to be fast.Abhyankar wrote:Hi,
I have generated a Sequence in DB2. How can i import or use it in Datastage? I want to use it as a key generator in my Job.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Thanks i did that. I changed the property to 'User defined Sql'. However i had other columns also, so i changed my target syntax as:
INSERT INTO
schema name .Table name
(
Key field,col1,col2
)
VALUES
(
NEXTVAL FOR schema name .Sequence name,?,?
)
My Job is aborting saying below:
" Fatal Error: Fatal: This SQL statement did not contain the same number of parameters as columns on this link. This usually happens with user-defined SQL. "
The number of columns are correct.
INSERT INTO
schema name .Table name
(
Key field,col1,col2
)
VALUES
(
NEXTVAL FOR schema name .Sequence name,?,?
)
My Job is aborting saying below:
" Fatal Error: Fatal: This SQL statement did not contain the same number of parameters as columns on this link. This usually happens with user-defined SQL. "
The number of columns are correct.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
How many columns in your design? You only have two parameter markers, so you require precisely two columns in the design. Specifically you do not include the name of the key column in your design. Just col1 and col2.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Abhyankar wrote:Hi,
i didnt understand= 'Specifically you do not include the name of the key column in your design.'
Am i not supposed to add my key column (The one i am doing NEXTVAL with ) in the columns property of my DB2 stage?
It means
Code: Select all
INSERT INTO
schema name .Table name
(
col1,col2
)
VALUES
(
NEXTVAL FOR schema name .Sequence name,?,?
)
Tejas
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The key column does need to be in the columns list of your user-defined SQL, but NOT on the Columns tab of your input link, because DataStage is not providing the value for the key column.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.