Sequence generated in DB2

[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.

[amritendra]

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

)

[ray.wurlod]

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.

[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.

[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.

[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?

[tehavele]

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.

[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.

[Abhyankar]

Thanks everyone. The problem is solved now.