Solved

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

Posted on 2010-09-20
6
393 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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

Suggested Solutions

Title # Comments Views Activity
Lotus notes email code 2 126
IBM Notes 9 crashed when open any attachment file 16 510
Lotus Notes Database "Invalid NSF Version" 5 2,044
Lotus notes - Follow up notes mail missing 15 77
Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
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.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

808 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