Handling Database Functions from the Transformer Stage

[ririr]

Can I call a database function from the transformer stage.

I have a situation where I am doing a lookup in an hashfile to get the code value(Sequence Number generated by the DB) of the data flowing from source.

The requirement that I have is, if the code value is not found in the hashlookup then populate the value in the database and get the code value (sequence number generated by the DB) for the new row that got created and use it to load the target tables.

Any help is appreciated!

Thanks

[vmcburney]

It gets very messy. One of the problems is that when you find a new code in your input data and add it to your database lookup table it does not get added to your hash file lookup. Therefore if the code appears in a subsequent row it gets added to the database table again with a new sequence number.

I think you are better off pre-validating your lookup codes in a seperate job, you would read in all your code values, validate them against the hash file lookup and any rows that are not found are aggregated and written to the database table. You can then reload your hash file and run your job with full processing.

[ririr]

I know it is a mess, but i have a requirement for doing so.
I have a database function that inserts a record in to the table and returns a value, if the parameter passed is not found in the in the table from wich the function is returning the value.

I need to know if i can call a database function in the transformer stage. Also, I understand that there are DSExecute and EXECSH routines out there.

Is it possible to use any of the functions, Also does the above DS routines return a value.

Any help is appreciated!

Thanks

[kcbland]

Your premise would be soooo poorly performing you shouldn't even think about doing it. It sounds like all you are doing is assigning a surrogate key. You should search this forum for techniques for doing surrogate key assignment.

Basically, get the max value assigned in the table. Maybe use a stage variable and for the initialization call a function to do a shell command to a shell script to run a sql script to get the max and parse the max value returning it. Then, increment that stage variable everytime you need to assign another value.

[ray.wurlod]

I second that! If you really must keep a table updated, take a copy of it (only the actual columns that you need, which are probably the natural key and surrogate key columns) into a hashed file, and maintain this while your job is running (that is, if the natural key is not found in the hashed file then add it to the hashed file with the next value of the surrogate key). Surrogate key generation is best done in stage variables (though you can use a sequence, or a serial data type, in the database table for rows being inserted). If you need to do so, run another job immediately afterwards to flush all the new rows back to a record-keeping table; you won't need to do the main table, because the new keys have been added by the main job.
This technique is taught in the "DataStage Best Practices" class, and used in some examples in the entry-level class "DataStage Essentials".