Document Number: 15893238 Document Title: SQLCLI Call to Remote System Over DRDA Link Fails Document Description: Domino for AS/400 can access DB/2 data via SQL calls. When the call is run server side, Domino uses SQLCLI to access DB2. When run client side, Domino uses ODBC. This document only contains information regarding server side agents. When an agent is run on server, it uses SQLCLI to access DB2 data. This is true even if that information resides on another AS/400 system. When accessing Data on another AS/400 system, it uses the Remote relational database directory entry to access the remote system. Domino uses an SQL package to store the procedures for reuse. If Domino is accessing Data on the local AS/400 system, that package is created when the agent runs. If the DB2 data resides on another system, the call may fail with the following error: SQL package QSQCLIPKGN in QGPL not found To avoid the error, you need to create the required SQL package on the remote AS/400 system. To do this, sign on to the remote AS/400 system and perform the following operations: 1 From an AS/400 command line, enter the following command: WRKOBJ OBJ(QGPL/*ALL) OBJTYPE(*SQLPKG) 2 Look for these two objects: QSQCLIPKGC QSQCLIPKGN 3 If the object QSQCLIPKGC exists, delete it, using the following command: DLTSQLPKG SQLPKG(QGPL/QSQCLIPKGC) 4 If the object QSQCLIPKGN exists, delete it, using the following command: DLTSQLPKG SQLPKG(QGPL/QSQCLIPKGN) Next, use a 4.6 or later version of the Notes client to create the following LS:DO script on the local AS/400 system or add the script to the beginning of your existing LS:DO script: Event Options: Option Public Uselsx "*lsxodbc" Sub Initialize Dim con As New ODBCConnection Dim qry As New ODBCQuery Dim res As New ODBCResultSet con.autocommit=False If (con.connectto("remotesystem","user","password")) Then qry.sql ="select * from QCUSTCDT" Set res.Query = qry Res.Execute Call Res.FirstRow Res.Close(Db_Close) Call con.disconnect End If End Sub Note: This agent must be run from the server. To verify that your script ran successfully, repeat steps 1 and 2 above. After you successfully run your script, delete the newly added script; it needs to run only once to create the SQL package on the remote system. Note: You can retrieve messages by using the GetExtendedErrorMessage method. +--- | This is the Domino/400 Mailing List! | To submit a new message, send your mail to DOMINO400@midrange.com. | To subscribe to this list send email to DOMINO400-SUB@midrange.com. | To unsubscribe from this list send email to DOMINO400-UNSUB@midrange.com. | Questions should be directed to the list owner/operator: email@example.com +---
As an Amazon Associate we earn from qualifying purchases.
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.