DSXchange

hi all.
i've searched in precedent forums how to create a unique number to insert in a field. i've a flat in input , after tranformer stage i insert rows in a table, and rejected rows in another table. in this second table, i 've added in db2 a column for a new id. Now, i cannot use the server function to create a unique index and i'm trying to created a counter in a stage variable as follow :

COUNTID decimal (10) default value 1.

in the derivation of the stage variable :

COUNTID = COUNTID + 1;

then i pass this id to the rowid of the reject table.
but it seems not work and give me problems because of in the rej table the rowid is primary key.
i dont understand why the counter not increase himself...
i try another way in the derivation to force it as follow :

if countid = countid theh countid + 1 else countid ....

You do not need to say countid = ... just give the actual value you want to use. Also ensure that your default value is set to whatever you want to start the value to.

Also you need to ensure that the Transform stage is running sequentially in order to not step on the individual nodes' toes while running in parallel.

Most stable solution - Create a custom stage that calls DB2 to obtain the next value. We do it for Oracle.

-T.J.

Why do you not use the stage columns generator where you do the count and after in a transform. With this stage you can keep your Parallelism and you are sure to have a unique value.

Pey

i'm trying to use the column generator but i don't know well how to set the properties to obtain a count with a unique number...
i'm trying as follow :

FLAT --> COL GENERATOR(in which i add the column) --> transform --> destination flat.

MARKO970 wrote:i'm trying to use the column generator but i don't know well how to set the properties to obtain a count with a unique number...
i'm trying as follow :

FLAT --> COL GENERATOR(in which i add the column) --> transform --> destination flat.

Here's an example:

osh "

generator
-schema record
(
i:int32 {cycle={init=part, incr=partcount}};
s:string[10];
)
-records 10
|
peek
-nrecs 10
-name
;
"

Vitali.

excuse me but i don't understand that schema ...
i'm trying to set the property in the column generator stage ...

Can you pass the reject link to another transformer? If so, try this -->

rejects --> sequential_collection --> transform -->db2

In the transform, set the id = OUTROWNUM (which I believe is available in the PX transform, if not - use the server transform).

Please see a post in the PX forum regarding pitfalls in generating sequential indexes in PX.

-BP

MARKO970 wrote:excuse me but i don't understand that schema ...
i'm trying to set the property in the column generator stage ...

On the DataStage pallet:

1.Open properties for the generator stage
2.Click on the Columns tab
3.Double-click on the positional number of the serial column you are generating.
4.Click on Generator in the Properties field.
5.Choose Type in the Available properties to add.
6.Choose Initial Value=part.
7.Choose Increment=partcount.

Good luck!
Vitali.

Peytot wrote:Why do you not use the stage columns generator where you do the count and after in a transform. With this stage you can keep your Parallelism and you are sure to have a unique value.

Because it will not work if you run multiple programs or versions of the same program writing to the same table.

-T.J.

thank's you all, i've tryed with column generator and it seems run well.
thank's very mutch to all!!!