cursor

luca · Post by **luca** » Fri Feb 21, 2003 3:51 am

Hi !
I've been searching through the DS documentation to find how to define and use a SQL cursor in a job control or a routine.
The only cursor I found was for "screen and cursor control ..." stuff.
Does this mean that with Datastage you cannot define a SQL cursor ?
What I want to do in a job control or routine :
I have a query to a DataBase which returns many rows.
I want to loop on these rows to do things for each of them.
I DO NOT WANT TO USE A FILE to store the result of my query and then proceeding this file because one of the field contain sensitive data (password) which is encrypted in our DB and must not be writen in a file.
Can you define such a cursor in DS ?
Thanks

WoMaWil · Post by **WoMaWil** » Fri Feb 21, 2003 7:30 am

Hi Luca,

easiest way is to write a job like this.

[OCI] => TRANSFORMER1] => [TRANSFORMER2] => [SEQUENTIAL]

You can do on this way anything you want within the transformers, what you like.

- you can filter
- you can change it

and so on.

In transformer2 you may write then nonsens to your sequential file without taking any information you get from Transformer1.

Simply write "Nonsense" in derivation.

Wolfgang

vmcburney · Post by **vmcburney** » Fri Feb 21, 2003 3:27 pm

From what you describe you do not want to move the data, you just want to transform it. One option is to read all the fields that require processing, perform a transformation, then write the results back with a database stage using an update action. Since you don't need to change the password there is no need to include it in the list of extract columns. You shouldn't be extracting any columns that you don't need.

Your password is encrypted by your database, if you write it out to a file you will get an encrypted string which is meaningless to prying eyes. You can write this encrypted string back to the database and it should keep the same password value.

You can also call a stored procedure from DataStage which can perform the pocessing for you or return a recordset to be extracted. Could you give a bit more information about your process.

Vincent McBurney
Data Integration Services
www.intramatix.com

luca · Post by **luca** » Sun Feb 23, 2003 11:17 pm

Thanks for your suggestions.
One important precision :
I wanted to do this cursor in a job control code or in a routine code.
For the moment, I have 3 jobs :
one sequencer which sequences the 2 followings :
- 1st job (job server with design flow) : select the rows from the table and write them to a sequential file,
- 2nd job (job control code) : read this sequential file and process the rows read (this process must be done by a job control because I have to launch one job for each row read).
If it is possible, I would like to replace this 3 jobs by one only. I want to keep the last job (JOB CONTROL) and replace the loop onto the sequential file by a SQL query and a cursor to process the rows returned by the SQL query.
Do you know wether this is possible or not ?

luca · Post by **luca** » Mon Feb 24, 2003 1:19 am

No, I mean that for each row of my table, I launch a job to run. This table is a "scheduling table" , it contains the jobs that have to be run (for each row of the table, I do a DSRunJob).

luca · Post by **luca** » Mon Feb 24, 2003 2:29 am

ManjulaMadhu, what you suggest is exactly what I want to do.
but to do this ("get the required rows from the table in the second job itslef and then do the process") I need to define a cursor on the rows returned by the SQL query, don't I ?

ray.wurlod · Post by **ray.wurlod** » Mon Feb 24, 2003 3:22 am

An alternative is to read the rows from your table then derive an output column in a Transformer stage. That output column could have as its derivation expression an invocation of the UtilityRunJob function provided in the SDK. This function is written as a transform function. Its output can be decoded in a second Transformer stage via the UtilityGetRunJobInfo function, also supplied in the SDK.
It may be more appropriate in your case to adapt the UtilityRunJob function, depending on whether you need to wait for each job to finish before the next starts, or to run multiple instances in parallel (I am assuming you are running at least release 5.1).

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

luca · Post by **luca** » Mon Feb 24, 2003 3:24 am

I still don't understand how I could do a DSRunJob for each row in a job which has a design ...
I have a strong feeling that I need to write a JOB CONTROL CODE to do all what I need to do (retreive job name and parameters values from Oracle tables and then execute a DSRunJob for each row)
No suggestions to do this in a JOB CONTROL ?

luca · Post by **luca** » Mon Feb 24, 2003 3:37 am

I sent my previous email before reading Ray.
Ray suggested to do this in a job (with design) too.

Does it mean that it is not possible to do this in a JOB CONTROL CODE ?

I was looking in this direction because it seems simpler.
I thought datastage could allow to change your data source (file or database table) easily. This can be done easily when the data we need are stored in a file, why couldn't we do it the same way when they are stored in a table of a DB ?

Anyway, I'll try to understand all this suggestions and see if I keep what I have already or change it.

Thanks.

vmcburney · Post by **vmcburney** » Mon Feb 24, 2003 3:58 am

The batch job code and Routine code are both Universe basic, the coding language of the underlying Datastage engine. In order to connect to databases from this code you need to run the Universe Basic client interface commands.

Go to this link for a full list of Universe reference manuals:
http://www-3.ibm.com/software/data/u2/p ... index.html

The one you are interested in is BASIC SQL Client Interface (BCI):
http://www-3.ibm.com/software/data/u2/p ... v/8745.pdf

There are other good things here such as SQL references. You may find that your current design - writing out to a sequential file - is easier than using the BCI commands.

Vincent McBurney
Data Integration Services
www.intramatix.com

ray.wurlod · Post by **ray.wurlod** » Mon Feb 24, 2003 4:10 am

One of the beautiful things about DataStage is that there are so often many ways to accomplish a task.
I usually try to advocate the one that results in least work; which uses the GUI as much as possible.
But, yes, it can be done in job control code. And, if you MUST obtain a row at a time directly from a table, this is precisely what the SQLFetch() function (part of the BCI) does, provided all the necessary prerequisite SQL functions have been invoked successfully. And provided you have a licensed ODBC driver with which to connect to the database (this can be an issue on UNIX platforms, where licensed ODBC drivers cost money!).
Running a simple job to SELECT them and write them into a local text file, as Vincent suggested, is a convenient workaround if you lack a licensed ODBC driver.

(Vincent, it may be closer to the truth to provide URLs for the release 9.6 UniVerse manuals rather than the release 10.0 ones.)

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518