DSXchange

I would like to get opinion from Gurus on mimicing Key management routines.

We have a sequence table which is very similar to the one that Ascential DataStage Key management routines use to manage the key values.

The structure of our sequence table is

SEQ_NAME
INITIAL_VALUE
CURRENT_VALUE
INCREMENT_VALUE
MAX_VALUE

We want to access 4 or 5 sequence names from the above sequence table in one server job. So we would like to mimic the key management routines which accepts a sequence name and it reads the current value and increments it to increment value, updates the sequence table and returns the incremented value.

Is this something doable using Ascential BASIC language? If anyone has done similar stuff, I would appreciate if they can share their code and give pointers.

Thanks for your time,
-Anil

I think the issue is the time it would take to connect to update one of these. It would be faster in a stored procedure. You could do it in a function and return the current value if you are not using Oracle.

Thanks Kim for your time. We are using a DB2 database.

I need to be able to read, increment and update the current value in the sequence table and return incremented value.

I don't have any experience with BASIC. Could you point me to any guides on connecting to db and executing SQL statements in BASIC?

Thanks,
-Anil

If you have no experience with BASIC, the last thing you want to tackle is using it to 'connect to db and executing SQL statements'.

Why not just make a copy of the routine in question and see about modifying it to suit your needs? It already does most of what you want...

The ultimate reference is BCI Reference Guide. This mimics the ODBC API. You will need a licensed ODBC driver to connect from DataStage routine.

I would not do this in BASIC. I would do it in a function if you can update in a function. You could do it like they do a sequence number in Oracle.

Kim,

Do u mean like a Table function in DB2? Or a user-defined function?

And then create a view on top of these functions and turn this in to one of the stages in server job?

Thanks,
-Anil

I do not know DB2 but in Oracle you can create functions. ODBC and plugins treat these like columns. In Oracle I do not think you can do an update or insert but if you could then a function could work like Nextval. This can be used in a user defined insert where the key is replaced by the Oracle sequence Seq.Nexval. There are a lot of posts about Oracle sequences. A function with an update statement could duplicate this functionality.

You can use parameters and make it a shared container.