calling a sequencer from teradata-multi load stage

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
ssunda6
Participant
Posts: 91
Joined: Tue Sep 19, 2006 9:32 pm

calling a sequencer from teradata-multi load stage

Post by ssunda6 »

Hi,

In oracle, we can create a sequence(eg., myseq) and use it in any sql-query as
insert into ttest values(myseq.nextval,'abc');

Can we create a sequence similarly in teradata also? if so please let me know how.

I want to use that sequencer in user-defined sql statement in teradata-multiload stage.

regards,
ssunda.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Have you tried using user-defined SQL? Have you tried using a database sequence in the manner you describe? The important thing, if you do, is NOT to provide a value from DataStage for that particular column; the number of columns in the DataStage job must exactly match the number of parameter markers in the SQL statement.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
ssunda6
Participant
Posts: 91
Joined: Tue Sep 19, 2006 9:32 pm

Post by ssunda6 »

Hi Ray,

I tried creating a sequencer in teradata. It is not identifying the syntax "create sequence". So, may be it has some other syntax or I am not sure if we can create a sequencer in teradata.

Iam not able to read the whole content posted by you, Ray.(Iam not a charter memeber). So , I dont know wat you have discussed in the previous reply. Please post the whole reply again. or shall I give my e-mail ID.

thanks in advance,
regards,
ssunda.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Don't you have access to any Teradata resources where you work? You need to find out if they even support the concept, something analogous to an Oracle 'sequence' object or other database's SERIAL field - and that's got nothing to do with DataStage.
-craig

"You can never have too many knives" -- Logan Nine Fingers
ssunda6
Participant
Posts: 91
Joined: Tue Sep 19, 2006 9:32 pm

Post by ssunda6 »

Hi Craig,

I checked teradata related information.

By creating a sequencer in teradata,I wanted to access it in teradata-multiload stage , by writing a custom query ( insert into temp values(sequencer.nextval) ) .

But now using different appraoch , I got a solution.

Thanks,
ssunda.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

That's good... care to share the solution? That way others with the same questions searching the forum can benefit from your efforts.
-craig

"You can never have too many knives" -- Logan Nine Fingers
ssunda6
Participant
Posts: 91
Joined: Tue Sep 19, 2006 9:32 pm

Post by ssunda6 »

Hi
Last edited by ssunda6 on Wed Oct 11, 2006 11:46 am, edited 1 time in total.
ssunda6
Participant
Posts: 91
Joined: Tue Sep 19, 2006 9:32 pm

Post by ssunda6 »

Hi All,

Creating sequences in teradata(using IDENTITY) was not possible, as Database structure changes was not allowed in our case. Hence we went with the following approach. Input file contains empnames. Into target table, unique numbers for empno should be inserted.

we used a column generator to generate the sequence numbers with the following logic:

The stages used are-

column generator (adding a column 'dummy' with value '1') ->
Join stage ( with Teradata enterprise stage as another input to Join stage(which gets the max(empno),1 as dummy) ) ->
column generator ( to generate the column 'count'-column generator generates values from 1, with incremental value of '1' ) ->
transformer (which performs addition of count and max(empno))->
target( teradata stage)

Explanation:

Add a dummy column (e.g., dummy ) with a value '1' to the incoming data. The main purpose of adding a dummy column is to have a common column so that we can perform Join operation. We will get max(empno),1 as dummy from teradata stage. Output of join stage is empname,max(empno). Column generator generates unique values for 'count'(starting from 1 ). In the transformer stage, add max(empno) and 'count' -this is value for empno. Finally empno, empname are inserted into target table.

Here we need not maintain a temp file and update the max(empno) everytime to the file.
Any inputs regarding the above approach are welcome.

regards,
ssunda.
Post Reply