Routine to connect to DB2

[pratyusha]

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

[ArndW]

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.

[pratyusha]

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

[ArndW]

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.

[pratyusha]

Hi,
We had this idea but we dont know the syntax for connecting to DB and fetch the value.
If possible,can you please give some example for the same

Thanks
Prathyusha

[ArndW]

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

[DSguru2B]

You can also use DSExecute() function.

[pratyusha]

Thanks for the code
But when i execute i get a -1 or 127 as output
Ideally what shud ErrorCode be returning?
And shud we specify anth like "Db2Cmd" which goes to command prompt and execute the command?

Thanks
Prathyusha

[Krazykoolrohit]

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

Why not dump the table into a hash file using datastage and then use that hash file for your lookups.

Using routines looks a bit strange for me but thats me.

[ArndW]

add a line like

Code: Select all

IF ErrorCode THEN CALL DSLogWarn('Error "':ScreenIO:'" returned from DB2 call','')

to your job to see what your error is.

[ray.wurlod]

Loading the required rows and columns (and only those) from the DB2 table into a hashed file and using that for the lookups will be fastest.

[pratyusha]

Thanks for that
but i am facing another prob now in the routine
the error msg says"Verb db2 not in your VOC"
i understand from old posts that VOC is vocabulary file

Any ideas how to add db2 in the VOC to execute the db2 command?

thanks
Prathyusha

[ray.wurlod]

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.

[pratyusha]

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

[ray.wurlod]

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.