Sequence Id

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
praburaj
Premium Member
Premium Member
Posts: 133
Joined: Thu Jun 30, 2005 1:26 am
Location: philippines

Sequence Id

Post by praburaj »

Hi,

My source table having 14,00,000 records. When i have tried to insert the records in to database, out of 14,00,000 records only 10,00,000 records only inserted. Even i have checked in source table any records are duplicate. There no records are duplicate. I hope there is some problem in creating Sequenc_Id. I want to create sequence id for all rows. For that one i have written some query which i have mentioned below.

"if STGLDSEQI = -1 then DSLink30.SEQ_I + @PARTITIONNUM +1
else STGLDSEQI + @NUMPARTITIONS".

while i run the job, i got errors are like this

" Error Idx = 12;
DB2 Driver Embedded SQL message: SQL0803N One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "ADW.MDSE_STYLE_CHAR_E" from having duplicate values for the index key.
SQLSTATE=23505
;
sqlcode = -803;
sqlstate = 23505
Execute failed
the runLocally() of operator [DB2_UDB_Enterprise_76], partition 0 of 1"

Note: Target table LD_SEQ_Id. This column is a key column.


Can anybody help me to resolve this issue?... :roll:
prabakaran.v
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

What are you using the "if" statement for? Is it the derivation of the unique primary key?

The error message is quite clear that you are loading duplicate keys. If you put in a reject link you will see which keys are incorrect and can proceed accordingly.
praburaj
Premium Member
Premium Member
Posts: 133
Joined: Thu Jun 30, 2005 1:26 am
Location: philippines

Post by praburaj »

Yes! that is the derivation for unique primary key. How can i take reject link from transformer?..... Can you explain briefly how to resolve this?...
prabakaran.v
ArndW
Participant
Posts: 16318
Joined: Tue Nov 16, 2004 9:08 am
Location: Germany
Contact:

Post by ArndW »

In PX you cannot take a reject from a transform stage, you need to do it from the Database passive stage. Draw a reject link and activate rejects in the stage details and then either use PEEK or write the errors to a target.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Your expression is unnecessarily complex. You seem to be using a job parameter as the initial value (this is good).

Generate a sequence beginning from 0 (initial value=partition number, increment=number of partitions) and add that to the initial value.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Post Reply