re: SQL package QSQCLIPKGN in QGPL not found

[Rob Berendt <rob@xxxxxxxxx>]

       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: meechamw@ptd.net
+---