Solved

How do you test for a valid connection to a DB2 application

Posted on 2010-09-20
6
387 Views
Last Modified: 2013-11-16
Greetings experts,

I have a Domino agent that access a DB2 application using *lsxodbc.On good days I can pull over 29K documents. There are days however when even though I have a connection to the DB2 application I pull only 1 record and it is bogus record.

I inherited this code from someone else and I am not too familiar with *lsxodbc and wonder if there is someway for me to check for this strange situation? I would like to gain a better understanding of *lsxodbc. Suggestions for reading material? Or have you run across anything like this before? My code is listed below.

Could it be something as simple as checking for whether the 1st GetValue returns a valid entry? Any insight would be greatly appreciated.

Thank you - Notes Rookie.
Dim sess As NotesSession
Dim thisdb As NotesDatabase
Dim anydate As NotesDateTime
Dim doc As NotesDocument
Dim con As ODBCConnection
Dim qry As ODBCQuery
Dim result As ODBCResultSet
Dim qrytxt As String
Dim searchstr As String
Dim serial As String
Dim profiledoc As NotesDocument
Dim view As NotesView
Dim vcoll As NotesViewEntryCollection
Dim agentlog As NotesLog, agent As NotesAgent, agentname As String

Set sess = New NotesSession
Set thisdb = sess.CurrentDatabase
Set anydate = New NotesDateTime("01/01/1901")     
Set con = New ODBCConnection
Set qry = New ODBCQuery
Set result = New ODBCResultSet
	
Set agent = sess.CurrentAgent
agentname = agent.Name
Set agentLog = New NotesLog(thisdb.Title & " - " & agentname)
Call agentLog.OpenNotesLog( thisdb.Server, "AgentLog.nsf" )
Call agentLog.LogAction("Begin")
	
'delete current entries
Call agentLog.LogAction("Deleting current entries ...")
Set view = thisdb.GetView("All Assets\By Machine Type")
Set vcoll = view.AllEntries
	
If vcoll.Count>0 Then
	Call vcoll.RemoveAll(True)
	Call agentLog.LogAction("Entries deleted")
End If

Set profiledoc = thisdb.GetProfileDocument("Database Profile")
pseudo$ = profiledoc.PseudoDiv(0)
	
qrytxt$ =  "SELECT DEP_AREA, COUNTRY_CODE, MACHINE_TYPE, ASSET_SERIAL, EMPNO, RCOSTCTR, COSTCTR, "     & _
"VEN_NUM, CUST_NUM, ASSET_CLASS, CUR_GAV, CUR_NBV, DESCR1, DESCR2, SUBNUM, "      & _
"CAPDATE, PIT_DATE, PIT_RES, PIT_NUM, MANUFACTURER, MODEL, USAGE_CODE, "    & _
"COMMENTS, COMPANY_CODE, PRFCTR, FLR_RMNUM, WORK_LOC, BLDG_NUM, "   & _
"INVENTORY_NUMBER, STATUS, LIFEREM_MON, FUNC_CODE, AAS_STAT, LOC_TYPE, NON_IBM_MODSER, ZZINVIND "   &_
"FROM SAWDB.BOOK_W_US, SAWDB.TZAMTWWCTRY "        & _
"WHERE MACHINE_TYPE = ZZMT AND ZZINVIND = 'Y' AND LAND1 = 'US' "  &_
"AND DEP_AREA = '40' AND STATUS = 'ACTIVE' AND PRFCTR IN " & pseudo$
	
If con.ConnectTo("CERIS", profiledoc.logon(0), profiledoc.pwd(0)) Then
	Call agentLog.LogAction("Connected to CERIS")
	Set qry.Connection = con
	qry.SQL = qrytxt
	Set result.Query = qry
	Print "Accessing CERIS on DB2"
	If Not result.Execute() Then
		Messagebox( " =Row: " & result.GetErrorMessage(DB_LASTERROR))
		Exit Sub
	End If
	result.CacheLimit = 100
	Do
		Call result.NextRow()
		If result.CurrentRow = 0 Then   'no ceris records found
			'Goto nextquery
			Goto bailout
		End If
		'Print "processing row " & Str(result.CurrentRow) " " & serial$ 
			
		Set doc = thisdb.CreateDocument
		doc.Form = "Asset"
		doc.Type = "Asset"
		doc.DEP_AREA = result.GetValue("DEP_AREA")
		doc.Country = result.GetValue("COUNTRY_CODE")        
		doc.INVTYP = result.GetValue("MACHINE_TYPE")
		doc.INVNR = result.GetValue("ASSET_SERIAL")
		doc.INVNUM = result.GetValue("INVENTORY_NUMBER")
		doc.OWNER = result.GetValue("EMPNO")			
		doc.BLDG = Cstr(result.GetValue("BLDG_NUM"))
		doc.DEPUS = result.GetValue("RCOSTCTR")
		doc.COSTCTR = result.GetValue("COSTCTR")
		doc.VEND = result.GetValue("VEN_NUM")
		doc.CNRIAC = result.GetValue("CUST_NUM")           
		doc.ATYP = result.GetValue("ASSET_CLASS")
		doc.CVL = result.GetValue("CUR_GAV")
		doc.NBL = result.GetValue("CUR_NBV")
		doc.DESCR = result.GetValue("DESCR1")
		doc.DESCR2 = result.GetValue("DESCR2")
		doc.SUBNUM = result.GetValue("SUBNUM")
		If result.IsValueNull("CAPDATE") Then
			doc.CAPDTE = ""
		Else
			doc.CAPDTE = result.GetValue("CAPDATE")
		End If
		doc.PHINV = result.GetValue("PIT_RES")
		doc.PITNUM = result.GetValue("PIT_NUM")
		If result.IsValueNull("PIT_DATE") Then
			doc.PHINVDTE = ""
		Else
			doc.PHINVDTE = result.GetValue("PIT_DATE")
		End If
		doc.MFG = result.GetValue("MANUFACTURER")
		doc.MODEL = result.GetValue("MODEL")
		doc.USAGE = Right(result.GetValue("USAGE_CODE"), 2)
		doc.COMM1 = result.GetValue("COMMENTS")
		doc.ROOM = Cstr(result.GetValue("FLR_RMNUM"))
		doc.PseudoDiv = result.GetValue("PRFCTR") 
		doc.SappCompC = result.GetValue("COMPANY_CODE")
		doc.WorkLocation = result.GetValue("WORK_LOC")
		doc.STATUS = result.GetValue("STATUS")
		doc.LIFEREM = result.GetValue("LIFEREM_MON")
		doc.FUNC = result.GetValue("FUNC_CODE")
		doc.AAS = result.GetValue("AAS_STAT")
		doc.LocType = result.GetValue("LOC_TYPE")
		doc.nonibmserialnum = result.GetValue("NON_IBM_MODSER")
		doc.INVIND = result.GetValue("ZZINVIND")
		doc.Today = Today
		Call doc.Save(True, False) 
	Loop Until result.IsEndofData    
		
Else
	Messagebox("unable to connect to ceris database")
	Call agentLog.LogAction("Unable to connect to CERIS")
	Call agentLog.LogAction("Done.")
	Exit Sub
End If
	
Set vcoll = Nothing
Set view = thisdb.GetView("All Assets\By Machine Type")
Call view.Refresh
Set vcoll = view.AllEntries
	
bailout:
Print "processing complete"
Call agentLog.LogAction("Done.")

Open in new window

0
Comment
Question by:notesrookie
  • 3
  • 3
6 Comments
 
LVL 22

Expert Comment

by:mbonaci
ID: 33723068
The code lacks error handling, which would probably explain what goes wrong.
I see the code uses NotesLog. The info is logged in agent log (right-click on your agent in designer and choose Agent log).

Add this to your code:

On the top (just bellow Sub Initialize):
    On Error Goto ErrHandler

On the very bottom (just above End Sub):
ErrHandler:
    Call notesLog.LogError( Err, "Error " & Error$ & " in line " & Cstr( Erl ) )


See here for more info on ODBC connectivity (type ODBC in the search box and click Go):
http://publib.boulder.ibm.com/infocenter/domhelp/v8r0/index.jsp
0
 
LVL 22

Accepted Solution

by:
mbonaci earned 500 total points
ID: 33723095
And yes, to check whether the SQL query yielded any data use:

    flag = odbcResultSet.IsResultSetAvailable

Return value:
True indicates that the result set has been retrieved with at least one row of data.
False indicates that no result set is available.

See here for more info:
http://publib.boulder.ibm.com/infocenter/domhelp/v8r0/topic/com.ibm.designer.domino.main.doc/H_ISRESULTSETAVAILABLE_PROPERTY.html
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 33723106
This one will also interest you:

    num% = odbcResultSet.NumRows

You can determine the number of rows only after the entire result set is fetched, not immediately after a query or while fetching records. You can immediately determine whether the result contains any rows at all; 0 is a valid value for this property.
This property is DB_NORESULT if there is no result set; DB_ROWSUNKNOWN if the number of rows is not known; DB_ROWSLIMITED if MaxRows limits the number of rows.
If a memory shortage causes truncation of the result set, IsEndOfData is True on the last row but NumRows is still DB_ROWSUNKNOWN.

See here:
http://publib.boulder.ibm.com/infocenter/domhelp/v8r0/topic/com.ibm.designer.domino.main.doc/H_NUMROWS_PROPERTY.html
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:notesrookie
ID: 33725481
Thank you, mbonaci. Let me get to reading and add more error checking in place. I will get back to you.
0
 

Author Comment

by:notesrookie
ID: 33746625
mbonaci - I implemented your 2nd suggestion for checking whether a result set is available. Only time will tell if this works. In the meantime I will close this question. Thank you for responding.
0
 

Author Closing Comment

by:notesrookie
ID: 33750716
The resolution answered my question.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now