DSXchange

Hi,
How do i implement the sequence generation in Parallel Extender 6.x. Any ideas ?

Regards,

There's a sequence (key) generator in 7x but there's nothing in 6x in fact.

The only way i see in 6x for a unique sequence is either use "SEQ.NEXT_VAL"...
either use in transformer something like :
(@INROWNUM * @NUMPARTITIONS) + @PARTITIONNUM
as a sequence
but don't remember if in 6x u can use these variables.
I didn't try, but it should generate a unique key.

The only way that we've seen to do this in 6 was to create a custom plug-in. Not a difficult task, and you can create your own sequencing using environment variables or inputs/operands given to the stage.

Andet

Hi Edric,
Where will I use the SEQ.NEXTVAL ?

I tried with user-generated query and changed the insert statement as

Insert into tablename (a,b,c)
values ( seq.nextval, orchestrate.b, orchestrate.c );

but it doesn;t help. ?

Regards

That's Oracle syntax and so would only help if you are loading into Oracle. And you can't simply put "SEQ" in your insert statement, you need to replace it with the name of a legitimate Sequencer object created for your target table. Then, doing what you are doing will autogenerate a surrogate key during the insert.

Talk to your DBA to help set something like that up... if you want to try it and you are actually loading into an Oracle database, that is. I'm sure there are comparable techniques for other databases as well.

The "SEQ" what I mentioned in my statement was referenced by an ORACLE SEQUENCE object but still it gives me an error ?

What error?

OraVehicle.LnkInsert: Error when checking operator:

Orchestrate update field 'a' is not an Orchestrate insert field. In order to use the default host array processing for insert, the Orchestrate fields for update must also be Orchestrate insert fields. This is not a requirement for insert non-host array processing. In order to select insert non-host array processing, set argument 'insertArraySize to 1. ?


As I have told u earlier I've modified the insert statement which was generated by DS.

Original Insert
Insert into tablename (a,b,c) values ( orchestrate.a, orchestrate.b, orchestrate.c);

Modified Insert
Insert into tablename (a,b,c) values ( seq.nextval, orchestrate.b, orchestrate.c);

Pls help.

Regards,
Balaji

Use the column generator stage.

Create a single integer column.

Open Edit Column Meta Data view (within "Columns").

Choose "Type" under "Available properties to add"
Choose "Initial Value". Set value as "part".
Choose "Increment". Set value as "partcount".

You will have an unique value provided to each record. All you need to do is somehow determine the proper offset value to provide to this (via database/lookup/transformer).

You can also use value.NEXTVAL within the SQL statement on your Oracle Insert statement if your DBA set that field to be a surrogate key field. Also, you must NOT use "SEQ" for the field name. You must use the actual column name defined by your DBA for the NEXTVAL to work.

Can you pls explain me on how to use the value.nextval ?

"You must use the actual column name defined by your DBA for the NEXTVAL to work." what does this mean ?

Regards

It means either of:

You must use the actual column name (not "a") in the columns list.

You must use the actual sequence name (not "seq") as the name of the object whose NEXTVAL method you are invoking.

Hi All,

Thanks a lot !.

I'm able to generate seq numbers using oracle sequence. I could use this only when I set the InsertArraySize to 1 or if I give a invalid where clause in the user defined update statement something like where 1 = 2 else I get the following error :

" Orchestrate update field 'VIN_ID' is not an Orchestrate insert field. In order to use the default host array processing for insert, the Orchestrate fields for update must also be Orchestrate insert fields. This is not a requirement for insert non-host array processing. In order to select insert non-host array processing, set argument 'insertArraySize to 1. "

Anyway as for as data loads I dont mind to set the InsertArraySize to 1

Regards,
Balaji.