Hi all,
Can u please tell me how can i access a database from the Job control Routine and query it using the SQL?
Thanks and Regards,
Vinay
Using SQL queries in Job Control Routine
Moderators: chulett, rschirm, roy
-
vinaypothnis
- Participant
- Posts: 18
- Joined: Mon Nov 18, 2002 4:02 am
- Location: India
-
vinaypothnis
- Participant
- Posts: 18
- Joined: Mon Nov 18, 2002 4:02 am
- Location: India
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Three points.
(1) The BCI functions only work properly if you have a properly licensed ODBC driver for connecting to the database (this is not a problem on Windows) and all relevant enviroment variables are correctly set.
(2) There is much that can go wrong. Gupta's code assumes that nothing will go wrong. I would VERY STRONGLY advocate checking for errors, particularly after SQLConnect, SQLExecDirect (or SQLExecute if you're using prepared SQL) and SQLFetch.
(3) A better exit from the loop is WHILE Status = SQL.SUCCESS OR Status = SQL.SUCCESS.WITH.INFO. Sometimes a row will have information associated with it, such as truncation or conversion warnings from the database server.
Whenever there is information associated with any of the handles, it is imperative that that information be removed from the handle by repeated calls to SQLError. Without this, you have what is called a "dirty handle" which is not guaranteed to work properly.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
(1) The BCI functions only work properly if you have a properly licensed ODBC driver for connecting to the database (this is not a problem on Windows) and all relevant enviroment variables are correctly set.
(2) There is much that can go wrong. Gupta's code assumes that nothing will go wrong. I would VERY STRONGLY advocate checking for errors, particularly after SQLConnect, SQLExecDirect (or SQLExecute if you're using prepared SQL) and SQLFetch.
(3) A better exit from the loop is WHILE Status = SQL.SUCCESS OR Status = SQL.SUCCESS.WITH.INFO. Sometimes a row will have information associated with it, such as truncation or conversion warnings from the database server.
Whenever there is information associated with any of the handles, it is imperative that that information be removed from the handle by repeated calls to SQLError. Without this, you have what is called a "dirty handle" which is not guaranteed to work properly.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
An even better approach, easier to maintain, would be to construct a small DataStage job that fetched the required information from the database (after all, this is one of the things DataStage does). The result could then be put into the job's user status area, or into a local text or hashed file, for further processing.
Why code when you don't have to? It's one of the great advantages of DataStage that you can use the graphical interface to design you data flows!
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
Why code when you don't have to? It's one of the great advantages of DataStage that you can use the graphical interface to design you data flows!
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518