Solved

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

Posted on 2010-09-20
6
396 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
Sometimes clients can lose connectivity with the Lotus Notes Domino Server, but there's not always an obvious answer as to why it happens.   Read this article to follow one of the first experiences I had with Lotus Notes on a client's machine, my…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

623 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