Problem Connecting DB2 Database

HCL Domino Domino Forum
1

Hello,I have been facing a severe problem while trying to connect a remote DB2 Database from Domino database.

I intend to use LCConnection, LCField, LCFieldList etc. to send/retrieve/modify the data from a Domino Database to DB2 database.

I have installed DB2 cLient on the machine from which I intend to connect remote DB2 database with the required DB2 database cataloged from the DB2 Client.

I have been trying to create a connection document on a server with DECS service running to the remote DB2 server and I succeded. Then, using Lotus Script I tried to connect the DB2 Server again from the machine on which both Domino server & DB2 client are installed through an agent. I again succeded to connect.

The problem arises when I try to select the rows from the database table with select query. It returns Nothing. Following is the code that I have used to connect:

Sub Click(Source As Button)

Dim doc As NotesDocument

Dim conn As New LCConnection("db2")

Dim Lcfieldlst As New LCFieldList

Dim Lcfield As  LCField

Dim LCFempid,LCFempdesig,LCFempdept	As LCField



conn.database="<database_Name>"

conn.Password="<password>"

conn.UserID="<user_Name>"



On Error Goto Trap

conn.Connect

Msgbox "DB2 Connected Successfully"

If(conn.Execute("Select * from emp",Lcfieldlst)=0) Then

	Msgbox "No Records Found"

Else

	Set  Lcfield = Lcfieldlst.Lookup("ENAME")

	Set LCFempid = Lcfieldlst.Lookup("EMPNO")

	Set LCFempdesig =  Lcfieldlst.Lookup("JOB")

	Set LCFempdept = Lcfieldlst.Lookup("DEPTNO")

	

End If



conn.Disconnect

Msgbox "Disconnected From DB2"

Exit Sub

Trap:

Msgbox "Connection To DB2 Database Failed"

End Sub

But when I Debugged the script I found an amazing thing.

I found that conn.Connect gave me the handle to the database with the property Connected = true.

But when the line conn.Execute()… fired the return type was LCCOUNT_UNKNOWN where it should have returned the number of records in the table.

Its really puzzling me as to how it could happen that even after connecting the database it is unable to fetch data while there is data in the table.

Similar Lotus script Code worked with Oracle when Oracle was installed in my local machine but failing with remote DB2.

Please Note: For connection I have been using Administrator Login ID for the DB2 database.

Could Someone provide some suggestions/solutions to this problem. That would be of great help.

Thanks a lot.

2

Subject: Problem Connecting DB2 Database

Everything you are doing is correct, so it really all boils down to that SQL SELECT statement. Are you absolutely sure that same query as written will return results with the same user ID / password? Maybe your ID doesn’t have a role allowing access to the emp table or maybe you need to fully qualify the table name with the owner.

Ken

3

Subject: RE: Problem Connecting DB2 Database

Thanks Ken,I have fired the query from within DB2 client and it returned rows as expected. I have been using the same UserID & Password as I been doing in the DB2 Client. I also tried using fully qualified table with owner name but it still failed.

Any suggestions/solutions?

Thanks Again

Sudip

4

Subject: Problem Connecting DB2 Database

DB2 using LC LSX never returns a count of records(not sure why). So your code is likely working as it should if no errors are occurring. The reason you never retrieve any data is that your not using the lcConnection.Fetch method which returns the row(s) of actual data on any LC LSX connection(eg Oracle, DB2, whatever).