Having problems Generating Sequence Number

[raju4u]

Hi,

Datastage V 8.1

I am trying to generate the sequence number by using database sequence in surrogate key generator. We have given all the details like database name, instance name, id and password.

The sequence is not generating it is giving zero or 0.

The database value is incrementing but it is not passing to datastage.

what might be the problem?

help me ..

[ray.wurlod]

Please show the SQL you are using to insert the row, and indicate how many columns there are in your job design being sent to the database.

[chulett]

Isn't one of the requirements to doing that that the stage must actually be used to create the sequence? If true, was that in fact done?

[raju4u]

Please show the SQL you are using to insert the row, and indicate how many columns there are in your job design being sent to the database. ...

HI

We are able to insert through insert statement in the db2. But we are generating the sequence through surrogate key genarator where the increment value is not comming. It is coming in as '0'.

from Surrogate Key Generator we are mapping that sequence generation number to db2 column.

there are 3 columns.
thanks..

[raju4u]

Isn't one of the requirements to doing that that the stage must actually be used to create the sequence? If true, was that in fact done?

it should genarate the seq but itsn't....

thanks..

[ray.wurlod]

OK, please show us the properties that you have set within the Surrogate Key Generator stage.

[raju4u]

OK, please show us the properties that you have set within the Surrogate Key Generator stage. ...

db instance
db name
db userid
dbpwd
dbserver
source name: schema.sequencenumber
genarateoutputcoloumn
sequence :database seq


thanks

[ray.wurlod]

Should the source be schema.sequencename.NEXTVAL ?

[raju4u]

Should the source be schema.sequencename.NEXTVAL ? ...

i tried with that value is also and i am getting thr error undefined value like schema.sequencename.NEXTVAL

please advice us..

[ArndW]

Let us simplify the problem and take the output out of the equation. Create a simple job with a row generator going to your surrogate key generator and then outputting the generated key to a PEEK stage.

Does this test job work? Does your sequence (as defined in "source name") exist in the database? If you have any messages or warnings or errors please cut-and-paste them to the thread.

[chulett]

Isn't one of the requirements to doing that that the stage must actually be used to create the sequence? If true, was that in fact done?

it should genarate the seq but itsn't....

Regardless of what you think it should or should not do, this is what I was referring to and asking if you had done - from the docs:

Creating the key source

You can create the surrogate key source by adding a Surrogate Key Generator stage to a job by itself, with no input or output links. You must create a key source before you can use it in a job. The key source can be a state file or a database sequence.

If you are using a database sequence, the sequence must be created by the Surrogate Key stage. You cannot use a sequence previously created outside of DataStage.

Emphasis mine.

[raju4u]

Let us simplify the problem and take the output out of the equation. Create a simple job with a row generator going to your surrogate key generator and then outputting the generated key to a PEEK stage.

Does this test job work? Does your sequence (as defined in "source name") exist in the database? If you have any messages or warnings or errors please cut-and-paste them to the thread.

HI

we have done the debugging with peek stage,but we are getting the warning "When checking operator: When binding output interface field "sa" to field "sa": Implicit conversion from source type "uint64" to result type "int16": Possible range limitation."

we tried with all possible datatypes ...


help us regarding this...

thanks

[chulett]

"sa" is your surrogate? Regardless, it is warning you that your target won't be able to hold all possible generated values. So, what is your target for this?

[raju4u]

"sa" is your surrogate? Regardless, it is warning you that your target won't be able to hold all possible generated values. So, what is your target for this? ...

sa is output coloumn bame and after this i am mapping this to db2 table same coloumn.

thanks,

[chulett]

Data types? Precision?