automated sequence numbers

[dstage443]

i am trying to load a file into a table...whenever i load each row i need to increment the value of the line num field by 1 and insert it into the line number field of the table ...how do i do this.

also how can i set default values ....most of the fields in the tables should be set to default ....

[DSguru2B]

You mean if the row is already present in the table, get that line num and 1 or regardless? If anwer is latter then just add 1 in the transformer stage to that column.
Default values can be set at the database level, talk to your dba about it.

[dstage443]

there is no number in line num field ...i need to generate a sequence which will increment by 1 each time i insert

[DSguru2B]

In other words you need a surragate key. many ways to achieve this. There are out of the box key management routines or manage key generation on your own. Search the forum for posts on surrogate key generation.

[ag_ram]

In other words you need a surragate key. many ways to achieve this. There are out of the box key management routines or manage key generation on your own. Search the forum for posts on surrogate key generation.

I suppose Surrogate Key Generator is in Parallel Job palette not in Server Job palette.

Maybe, the least option is to create two Jobs for extracting the line num from the table and getting the Link Count of that Job(last Line Num) then use it in the next Job with the logic to increment by one.

[shamshad]

(1) Use "KeyManagement" in Data StageFunctions that gives the Sequence
number from a table.
(2) If you want to create your own, have a lookup(hash file) that
has 2 columns
Key_Column= 'X'
Last_RowN0= select max(column) from the table

In your original job, do a lookup in #2 file and match with 'X' from source. That will give you the max number in the table. Then in Transformer or stage variable keep adding +1 to the number till the end of processing.

[ray.wurlod]

Why not just use @OUTROWNUM system variable, perhaps added to a constant (starting value) supplied as a job parameter?

[chulett]

also how can i set default values ....most of the fields in the tables should be set to default ....

True database default values are achieved by not including the columns in your job.

[ag_ram]

Why not just use @OUTROWNUM system variable, perhaps added to a constant (starting value) supplied as a job parameter?

If the Starting value is supplied by the user,

1. Can we go with this solution into production?

2. How user determines the last record line number in the table?

Please clarify these questions.

[ag_ram]

Why not just use @OUTROWNUM system variable, perhaps added to a constant (starting value) supplied as a job parameter?

If the Starting value is supplied by the user,

1. Can we go with this solution into production?

2. How user determines the last record line number in the table?

Please clarify these questions.

[dstage443]

@INROWNUM or @OUTROWNUM was the one that worked for me

[ag_ram]

@INROWNUM or @OUTROWNUM was the one that worked for me

Can you please let me know how did you do that?

Do you have the value of the Job parameter to mean the last line number which is supplied by the user or extracted from the table by any other job?

[vivekgadwal]

@INROWNUM or @OUTROWNUM was the one that worked for me

Can you please let me know how did you do that?

Do you have the value of the Job parameter to mean the last line number which is supplied by the user or extracted from the table by any other job?

I believe that by using @INROWNUM, he is trying to restart the sequence of the numbers from 1 through however number of rows he reads from the source.

I also think that if it is only inserts all the time, then generating a sequence number at the Database level provides a viable solution!