Sequence number generation

[vbeeram]

Hi,

I have Audit table which maintains Run History.Using same job im loading data into ODS table and Audit table.Audit table has two extra columns than my ODS table,i.e Loaded Date and Sequence_No.

First time when i load the data Sequence_No is 1, when i load the data second time it has to check the last loaded date,if both the dates are same,then Sequence_No:2(Means same day data loading twice).,
If both dates are different this means next day load then again sequence_No starts from 1.

I am trying to use lookup on audit table but there is no common key from the main Source.

Is there any way to open the last run date and max sequence_No directly in Transformer?

OR

Is it possible to open Database using Basic program to capture last run and Max Sequence_No?

Or any other advice?


Thanks in advance,
VBeeram

[ray.wurlod]

You can capture the current maximum using a job, say into a hashed file keyed by table name.
This needs you to be 100% confident that no other process will be updating the audit table.
Otherwise use a database mechanism for generating the sequence_no, such as an Oracle sequence, or a serial (auto-incrementing) data type.

[pani]

You can also join on a dummy key . i.e. get the max seq # from database and then have a column generator for dummy column , join with link coming from main source , also 1 up number using database features like sequence or identity will kill the performance.

[vbeeram]

Hi Ray,

As you said i already did ,im able to take max value(Latest date and Max sequence_No) from Hash file, but im not able to pass these columns into Transformer,since these are individual columns.

Since im using Sever job i cannot use Column Generator Stage.



ODBC(SOurce)--------->TRNSFORMER------->ODBC1(ODS Table)
ODCB2(Audit Table)

To use lookup for max value and latest date i dont have common key between Source and Audit table.

I also tried with Hashfile directly attaching to Transformer but it's treating as LOokup and expecting common key.

Thanks
Thiru[/img]

[vbeeram]

Hi Ray,

As you said i already did ,im able to take max value(Latest date and Max sequence_No) from Hash file, but im not able to pass these columns into Transformer,since these are individual columns.

Since im using Sever job i cannot use Column Generator Stage.



ODBC(SOurce)--------->TRNSFORMER------->ODBC1(ODS Table)
ODCB2(Audit Table)

To use lookup for max value and latest date i dont have common key between Source and Audit table.

I also tried with Hashfile directly attaching to Transformer but it's treating as LOokup and expecting common key.

Thanks
Thiru

[ray.wurlod]

Here's one of several ways.

Run a job that captured SELECT MAX(SKcolumn) FROM table into a hashed file whose key is the table name and where the captured key value (presumably an Integer) is the only non-key field. Upcase the table name if you like.

In the Transformer stage you want to initialize a stage variable with the current maximum value. To initialize the stage variable use the Trans() function:

Code: Select all

Trans(hashedfilename, Upcase("tablename"), 1, "X")

The expression editor will complain about this, because Trans() is not in the DSParams file, but accept the expression. It's valid. Edit the DSParams file if it concerns you, or annotate the job to warn future developers about this situation.

Derive the stage variable (imagine it's called svSKvalue) as something like

Code: Select all

If refinput.keycol.NOTFOUND Then svSKvalue + 1 Else svSKvalue

That is, only increment the SK value if you need to; specifically it does not already exist in the target system with the same candidate column values as in the source.

This is one of many considerations you would learn about by enrolling on a DataStage Best Practices class (DS306).