Solved

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

Posted on 2010-09-20
6
391 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

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

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

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

For users on the Lotus Notes 8 Standard client, this article provides information on checking the Java Heap size and adjusting it to half of your system RAM in attempt to get the Lotus Notes 8.x Standard client to run faster.  I've had to exercise t…
This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

810 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