LCLSX and DB2 stored proc on iSeries (AS/400)

HCL Domino Domino Forum
1

I need to accomplish the following:

  1. Create a stored procedure on DB2/400 that returns a resultset. No input or output parameters are needed.

  2. Call this stored procedure from LotusScript

  3. Manipulate the resultset

I managed to piece together enough from the “DB2 Stored Procedures and Triggers” RedBook to use the Run SQL utility from Operations Navigator and create the stored procedure. However, when I try to execute it via LCLSX calls I get the error “Metadata object does not exist”. I gave (Public) full permissions and even tried using my own account but I get the same error regardless. I think the problem is something on the iSeries server that I’m missing, but I have no idea what.

In case it does matter, below is my code. The client is Windows 2000 SP4 with ND 6.5.1 and the server is OS/400 V5R2 with ND 6.5. If anyone can offer any assistance I would appreciate it tremendously.

Thanks,

Charles

P.S. For those who may tell me to post this in the Enterprise Integration forum, I did that several days ago and have not received any response.

Dim lcSession As LCSession

Dim lcConn As LCConnection

Dim lcFields As LCFieldList



Set lcSession = New LCSession

lcSession.ConnectionPooling = True



Set lcConn = New LCConnection("odbc2")

With lcConn

	.Server = "LOCAL"

	.UserID = "user"

	.Password = "pass"

End With	

Call lcConn.Connect



If Not lcConn.IsConnected Then

	Messagebox "Error:  Unable to connect."

	Exit Sub

End If

'WMBIRD is the library, SALESSUM is the stored proc name

Set lcFields = New LCFieldList

If lcConn.Execute("CALL WMBIRD.SALESSUM()", lcFields) Then

	If lcConn.Fetch(lcFields, 1, 1) > 0 Then

		Print lcFields.Lookup("CLCODE").Text(0)

	End If

End If



Call lcConn.Disconnect

Set lcConn = Nothing

Set lcFields = Nothing

Set lcSession = Nothing
2

Subject: RE: LCLSX and DB2 stored proc on iSeries (AS/400)

Though I can’t speak for anyone else, you didn’t get any response from me in the EI Forum because you posted no details of your problem. Now that you’re supplying partial source code and the partial text of the error message, maybe we can make some progress. We’d do better if you gave the full text of the error, which should include the name of the metadata it’s trying to use (probably “” since I don’t see you assigning the Metadata property). Can you also please add error trapping code (On Error …) to show the line number on which the error occurred, so that we can narrow things down to the statement causing the error?

I don’t understand how you could get “Metadata object does not exist” from the script you are showing. This message comes from the LC LSX, not from DB2, and it only appears when you’re doing something that uses the Metadata property of the connection, and Metadata refers to a nonexistent table. The Execute method does not use Metadata, so it shouldn’t ever generate this message. Fetch works off the last result set, so generally speaking it shouldn’t require Metadata either.

I’m curious why you’re using ODBC to access your database instead of the native DB2 connector. What you’re doing should work either way, but stored procedure support is better in the DB2 connector in your version.

Also I would note that the preferred method for calling a stored procedure is to assign the Procedure property of the connection and use Call.

What happens when you execute this same SQL statement using the SQL command line interface of DB2?

3

Subject: RE: LCLSX and DB2 stored proc on iSeries (AS/400)

Andre, thanks for the response. I didn’t post details before because I’m not really sure how to do this and was looking for some help in getting started. I’ve never created a stored procedure for DB2 and I’ve not been very successful in using LCLSX in general and really needed some cookbook type instructions.

When that wasn’t forthcoming I dug into the Lotus Connectors help (which is atrocious), the RedBook “DB2 Stored Procedures and Triggers” (which is somewhat better), and finally out of desperation I begged for sample code from another Notes.Net user. Because the other documentation is so poor I have been relying most heavily on the sample source code I was given from a working environment.

I am using “odbc2” because I don’t have any DB2 tools other than what is on the iSeries. It is my understanding that I can’t use the “db2” connector unless I have a DB2 Connect Client, which costs an insane amount considering I just need it for testing. I was told that once the agent gets scheduled on the iSeries I can use “db2” with no problem.

The Procedure property does not show up in the list of available properties nor is it listed in the Lotus Connectors documentation. Based on your information I changed my code to use the Procedure property and the Call method, as shown here:

Set lcParms = New LCFieldList

Set lcFields = New LCFieldList

lcConn.Procedure = “WMBIRD.SALESSUM”

If lcConn.Call(lcParms, 1, lcFields) > 0 Then

The error is generated by the line:

If lcConn.Call(lcParms, 1, lcFields) > 0 Then

The exact error message is “Error: Metadata object does not exist, Connector ‘odbc2’, Method -Call-”.

Using the STRSQL interface on the iSeries I entered “CALL WMBIRD.SALESSUM()”. It returns the error “Qualified object name SALESSUM not valid”, which corroborates the LC LSX error. If I go through STRPDM and view the WMBIRD library I see the following:

SALESSUM *PGM CLE

All I did was use the STRSQL interface and issue my CREATE PROCEDURE command, which generated the file I list above. It didn’t return any errors, but I’m guessing there is something else I need to do to make this accessible via a SQL CALL command. Unfortunately I haven’t the foggiest idea what that might be.

Any ideas from here?

Thanks,

Charles

4

Subject: SOLVED: LCLSX and DB2 stored proc on iSeries (AS/400)

I called IBM and they had me delete and recreate the stored proc and it worked fine. I still don’t know what I did wrong the first time, but it was apparently something on the iSeries.

Thanks for the help.

– Charles