Page 1 of 1
Sequence generated in DB2
Posted: Mon Mar 23, 2009 11:22 pm
by Abhyankar
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.
Re: Sequence generated in DB2
Posted: Mon Mar 23, 2009 11:57 pm
by amritendra
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.
Hi In target DB2 stage You can use option user defiened update and insert . Then you can assign value like below.
INSERT INTO
schema name .Table name
(
Key field
)
VALUES
(
NEXTVAL FOR schema name .Sequence name
)
Re: Sequence generated in DB2
Posted: Tue Mar 24, 2009 12:50 am
by ray.wurlod
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.
You can use its NEXTVAL property in any SELECT, including on a reference link. Just don't expected it to be fast.
Posted: Tue Mar 24, 2009 12:54 am
by Abhyankar
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.
Posted: Tue Mar 24, 2009 12:56 am
by ray.wurlod
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.
Posted: Tue Mar 24, 2009 1:26 am
by Abhyankar
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?
Posted: Tue Mar 24, 2009 2:04 am
by tehavele
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,?,?
)
Remove key_field name from stage defination's as well.
Posted: Tue Mar 24, 2009 4:16 am
by ray.wurlod
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.
Posted: Tue Mar 24, 2009 5:55 am
by Abhyankar
Thanks everyone. The problem is solved now.