cursor
Moderators: chulett, rschirm, roy
cursor
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
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
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
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
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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
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
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 ?
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 ?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
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 ?
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 ?
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.
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.
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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
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
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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