Seq Generator

pcmbalaji · Post by **pcmbalaji** » Fri Dec 24, 2004 8:46 am

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

Regards,

Edric · Post by **Edric** » Fri Dec 24, 2004 9:04 am

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.

Andet · Post by **Andet** » Sun Dec 26, 2004 2:35 pm

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

pcmbalaji · Post by **pcmbalaji** » Mon Dec 27, 2004 7:20 am

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

chulett · Post by **chulett** » Mon Dec 27, 2004 7:56 am

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.

pcmbalaji · Post by **pcmbalaji** » Mon Dec 27, 2004 8:02 am

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

chulett · Post by **chulett** » Mon Dec 27, 2004 8:13 am

What error?

pcmbalaji · Post by **pcmbalaji** » Mon Dec 27, 2004 9:39 am

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

T42 · Post by **T42** » Mon Dec 27, 2004 7:53 pm

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.

pcmbalaji · Post by **pcmbalaji** » Tue Dec 28, 2004 9:34 am

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

ray.wurlod · Post by **ray.wurlod** » Tue Dec 28, 2004 2:59 pm

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.

pcmbalaji · Post by **pcmbalaji** » Wed Dec 29, 2004 8:55 am

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.