Routine to connect to DB2
Moderators: chulett, rschirm, roy
Routine to connect to DB2
Hi all,
We have a requirement in our job where we need to retrieve the max of a column from a table in DB2
For this we need to write a routine which would connect with DB2 and return the max value
Any ideas how to achieve this?
Thanks a ton,
Prathyusha
We have a requirement in our job where we need to retrieve the max of a column from a table in DB2
For this we need to write a routine which would connect with DB2 and return the max value
Any ideas how to achieve this?
Thanks a ton,
Prathyusha
Why not use a DataStage server job to do this instead of writing a routine? You can use DS in several ways to effect this. A lookup stage with custom SQL is the first and easiest method that springs to mind.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
Hmmm, don't see how a routine will be any faster than doing a singleton lookup. But you can achieve the calls by writing a routine that does a command line "DB2 CONNECT" and then executes the "DB2 {command}" and parses the captured output and passes the returned value back to the calling program.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
In BASIC it would be along the lines of
Code: Select all
ConnectCommand = 'DB2 CONNECT to ':YourServer:' USER ':YourUser:' USING ':YourPassword
QueryCommand = 'DB2 SELECT MAX........'
EXECUTE ConnectCommand CAPTURING ScreenIO RETURNING ErrorCode
** and so on
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Charter Member
- Posts: 560
- Joined: Wed Jul 13, 2005 5:36 am
- Location: Ohio
Why not dump the table into a hash file using datastage and then use that hash file for your lookups.pratyusha wrote:Hi,
Thanks for the response.Right now we are using a look up and achieving the purpose.
But this is time consuming in real time
so we are in a position to write a routine for this
Thanks,
Prathyusha
Using routines looks a bit strange for me but thats me.
add a line like
to your job to see what your error is.
Code: Select all
IF ErrorCode THEN CALL DSLogWarn('Error "':ScreenIO:'" returned from DB2 call','')
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Yes, but that's not what you need to do.
Change the first argument of DSExecute() from "UV" - which causes a DataStage command to be executed - to "DOS" - which cause the command to be executed as an operating system command.
Change the first argument of DSExecute() from "UV" - which causes a DataStage command to be executed - to "DOS" - which cause the command to be executed as an operating system command.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
We got a solution to this at last..
:D
please see the code below
Cmd = "db2 " : "'connect to pdw_dev user pdw using Db2dba123'"
Call DSExecute('UNIX',Cmd,Output,Status)
Cmd = "db2 -x " : "'select max(err_id) from etl_job_err_log'"
Call DSExecute('UNIX',Cmd,Output1,Status)
Ans = trimb(trimf(Output1))
Thanks to all who helped us wid this
Prathyusha
:D
please see the code below
Cmd = "db2 " : "'connect to pdw_dev user pdw using Db2dba123'"
Call DSExecute('UNIX',Cmd,Output,Status)
Cmd = "db2 -x " : "'select max(err_id) from etl_job_err_log'"
Call DSExecute('UNIX',Cmd,Output1,Status)
Ans = trimb(trimf(Output1))
Thanks to all who helped us wid this
Prathyusha
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Your original post on this thread suggested that your DataStage server is on Windows, which is why I suggested "DOS" as the first argument of the DSExecute() call. That "UNIX" works tells me that your DataStage server is, in fact, on a UNIX machine. Had your DataStage server actually been on a Windows machine "UNIX" would have failed.
It would probably be nice to issue a disconnect command so as to free up DB2 resources as soon as they are no longer required.
It would probably be nice to issue a disconnect command so as to free up DB2 resources as soon as they are no longer required.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.