Read config/sequence from database

calvinlo · Post by **calvinlo** » Tue Jul 15, 2003 10:04 pm

Dear all,

what method/routine can i use to read config from database tables in DataStage??? also...I hope to use my own sequence in database to keep track w my job logs instead of the DS one. How can i read the sequence number and parse to the Job control?
Thank you very much!

Cal

ray.wurlod · Post by **ray.wurlod** » Wed Jul 16, 2003 12:54 am

Depends on the database and whether you have a licensed ODBC driver to connect to it. If yes, you can create code that uses BCI (BASIC SQL Client Interface functions) - search the archives of this site for examples. If no, your code can run a small job that retrieves the value in question into a text file, that your routine can read.

But if there is a sequence in the database, why bother loading its current value into DataStage? Simply use user-defined SQL to invoke the NEXTVAL method of the sequence (I am assuming Oracle here), or whatever syntax is appropriate to invoke the next value of a SERIAL data type (for example providing a zero value, or omitting that column from the INSERT statement).

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

calvinlo · Post by **calvinlo** » Wed Jul 16, 2003 2:08 am

BCI only supports ODBC?? does it contain the driver for db2? oracle?

But if i use user-defined SQL for the sequence number, how can i get the data back to my DS script? populate to text file then read?
Thank you.

Cal

roy · Post by **roy** » Wed Jul 16, 2003 2:35 am

Hi,
regarding the serial/sequence number,
you can manage it with DS by a simple method:
1. select max(old_val) to a hash file.
2. when processing new lines get this number via lookup to the file you got in step 1.
3. use @OUTROWNUM + lkp.oldmaxval generating a unique and new sequence number for your column.

Roy R.

calvinlo · Post by **calvinlo** » Wed Jul 16, 2003 2:43 am

Since i have hundreds of jobs running at the same time, retreiving the sequence number, if i put it in hash file...will it cause sync problem?? or the file is locked?? or always need to wait?
and so i just hope a direct db connection will be better. correct me if i am wrong.
Thanks.

Cal

tonystark622 · Post by **tonystark622** » Wed Jul 16, 2003 7:15 am

Cal,

I'm not sure from reading your responses that I completely understand your problem.

Assuming that I do, you can return the database sequence to DataStage by using user-defined SQL as Ray suggested. Something like:

Create an Oracle sequence:

Create sequence ds_seq;

Get the value into DataStage (user-defined SQL):

select ds_seq.nextval, field1, field2 from yourtable;

Good Luck,
Tony

kduke · Post by **kduke** » Wed Jul 16, 2003 9:17 am

Cal

You have hundreds of jobs running at the same time updating the same table and all need a sequence number? What are you doing? Sounds like a real time problem. I would not use a hash file. You will read a record, add one to it and write it back out. You would have to do this in a routine. A lookup does not lock the record when it reads it so it may create duplicates. An Oracle sequence number is the fastest. You can do it on the insert statement. There was an example earlier this week. If you need a routine then it would not be hard to write. You will have hundreds of jobs trying to lock the same record. I would modify the Utility HashFileLookup routine in the SDK and give it a new name like NextSequence(TargetTable).

Kim.

cwong · Post by **cwong** » Wed Jul 16, 2003 2:58 pm

Calvin,

How about cloning this routine: KeyMgtGetNextValueConcurrent

You may find this routine under sdk/KeyMgt.
This is to facilitate "surrogate" key or sequence number in concurrent environment processing.

It is using a hash file to track sequence number by "key" name and allow concurrent processing => update to take place like the sequence object in database.

Hope this help ... [:I]

calvinlo · Post by **calvinlo** » Wed Jul 16, 2003 8:12 pm

Thanks many all your help.
to make it clear, i explain in more detail on what i need...I need a JOBID for logging each job runs. And the jobid, as suggested by my supervisor, should be retreived from a DB2 Sequence. And so, every jobs start i need to retreive the sequence number. I am wondering how i can do that. I thought I could make a routine to read a sequence number but still trying...

And as recommended by you all,
i don't understand how i can do the following..

"Create an Oracle sequence:
Create sequence ds_seq;
Get the value into DataStage (user-defined SQL):
select ds_seq.nextval, field1, field2 from yourtable;"

after i select the value in a stage, how can i populate the value to my datastage script??

Hopes you all can help me. Many thanks.

Cal

ray.wurlod · Post by **ray.wurlod** » Wed Jul 16, 2003 9:17 pm

This is the first time you've mentioned that you're using DB2; everyone has posted examples of using Oracle sequences.

Normally the whole point of using a sequence (which is an object in the database that delivers its next number) is so that you don't have to do so within DataStage.

In your example you need to access the next value of the sequence from a job control routine presumably to provide the run ID (as a parameter value?) to other jobs in the run.

Typically the next value of a sequence is not obtained directly using a query or a routine, but indirectly. For example you construct a query of the form
SELECT sequencename_nextval FROM table
and add selection criteria (or use a table that is guaranteed to have only one row, such as "dual") so that only one row is returned.

Run this as a preliminary DataStage job for each "run", and put the result into a text file.

Then, in your job control routine, retrieve the result from the text file. For example:
Call DSExecute("UNIX", "head -1 filename", Result, ExitStatus)

If you must have a routine (for example you want to use a Routine Activity in a job sequence), that routine simply needs to read the first line from the file created by the preliminary job.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

calvinlo · Post by **calvinlo** » Wed Jul 16, 2003 9:34 pm

Thank you.
But this indirect way need a text file as a bridge, and if many jobs run at the same time, it will have syncronization problem. (many jobs update the text file at the same time). Some jobs might get the same id or the sequence will get "jump". That is why i hope to have a method to retreive the sequence number directly.

Cal

ray.wurlod · Post by **ray.wurlod** » Thu Jul 17, 2003 6:17 pm

You can use a different text file for each job, or a different record in a hashed file.
Or you can pay for a licensed ODBC driver and learn how to write code using BCI functions (consult the archives).
And, to answer one of your earlier posts, BCI supports only ODBC.
If you want to create C functions to access your database, and call these through the GCI (general call interface) you can, but you will need to create a new image of the DataStage shell executable that includes your function definitions, and which practice is discouraged by Ascential.

calvinlo · Post by **calvinlo** » Thu Jul 17, 2003 7:48 pm

Thank you!
I did find out the way now!
Data Direct Connect ODBC is bundle with DataStage. And so there is free odbc drivers for different database connections. But the license states that we can only use Datastage for this driver. And I could create routine to fetch sequence number using BCI functions.

Cal

ray.wurlod · Post by **ray.wurlod** » Thu Jul 17, 2003 10:51 pm

I think you'll find that the DataDirect drivers work only for a one month trial period (from first use), then you have to pay for them. As delivered, they are licensed only for use by DataStage stages, not for use by BCI code.
Check out their web site: http://www.datadirect-technologies.com
Note also that Ascential will NOT support "my ODBC driver has stopped working" issues in this case; the driver emits errors containing the message that the driver is not licensed.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

calvinlo · Post by **calvinlo** » Thu Jul 24, 2003 8:23 pm

Hi all,

if i hope to use BCI to connect to my database, all i need is to buy the DataDirect Driver through Ascential..or DataDirect??
Also could i connect through BCI if i use other licensed ODBC driver on AIX?
Thanks

Cal