DB2 Stored Procedure V4R4 without ILE C/400

pts.
Tags:
AS/400
CLP
Database
DB2
DB2 Universal Database
Oracle
SQL
SQL Server
We have an older iSeries running V4R4. We user Rumba/400 for AS400 access, and we've run into a problem using the Rumba ODBC driver for linked server access from our SQL server to our AS400 (The ODBC driver periodically fails when the linked server is used heavily). Because of this, we've been doing everything we can to avoid using the linked server, and instead use OPENROWSET() wherever possible. This works well as long as we don't need to do an update, insert, delete, or execute a program on the as400. When we need to do any of those, we seem to be stuck using the linked server on our SQL server. I have set up several stored procedures to run RPG or CL programs, and they all work well for what we need (they're in DTS packages). We're currently trying to replace an update using the linked server with something more stable. The first thing we tried was to use an update openrowset(), but, it seems we are limited to 1 row at a time which can be rather ugly. After that, I tried putting the SQL into a CL program, and creating a stored procedure to call the external program (SQL based stored procedures require ILE C/400 to compile). This works fine except that no result set is returned, so again, we can't use OPENROWSET since it expects a resultset. My first thought was that since it would be good to know the number of rows updated anyway, simply adding a result set of the returned rows would be the easiest solution. but, I can't seem to find any documentation online as to how to return a result set in RPG or CL. Granted, my experience in RPG, and CL is very limited, so, I could easily be overlooking something very obvious. Any ideas? (purchasing ILE C/400 isn't an option at this time unfortunately) thanks in advance, Kevin

Answer Wiki

Thanks. We'll let you know when a new response is added.

You have to install ILE C/400. You are trying to implement functionality that does not exist in your old system. The only solution we have found that works reliably is for the remote server to call an SQL stored procedure.
RPG and CL do not return result sets. You can go back to FTPing batch files or upgrade.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Hoffman
    Upgrade you OS on the iSeries to v5r4, use the OLEDB provider that comes with Client Access for your SQL Link Server.... Also if you have SQL on the iSeries (try strsql from a command line) you can return a record set from an RPG-SQL program.
    0 pointsBadges:
    report
  • JPLamontre
    this sample in free rpg (sorry for not-free-V4R4) returns a result set. I have found it at http://www.iseriesnetwork.com/isnetforums/showthread.php?p=168208#poststop h dftactgrp(*no) fCstMst1 if a e k disk prefix('CS.') d cs e ds qualified extname(CSTMSTPF) d CustRS pr d pRows 10i 0 d CustRS pi d pRows 10i 0 d CustList ds occurs(100) d CSTMST 481a d i s 10i 0 /free i = 0; setll *Loval CstMst1; read CstMst1; dow not %eof(CstMst1); if i >= pRows; leave; endif; i = i + 1; %occur(CustList) = i; CSTMST = cs; read CstMst1; enddo; exsr setResult; *inlr = *On; begsr setResult; /end-free C/EXEC SQL C+ SET RESULT SETS ARRAY:CustList FOR:I ROWS C/END-EXEC /free endsr; /end-free
    0 pointsBadges:
    report
  • TomLiotta
    I haven't run across a requirement to have ILE C installed for stored procedures unless they are SQL procedures. An ILE RPG stored procedure shouldn't need C. However, I've always had C available, so I haven't actually tested it. I've only seen numerous statements from IBM that always refer to "SQL procedures" and those statements are always separated from statements about "HLL stored procedures" or even "REXX stored procedures". (Be aware that REXX procedures cannot return result sets.) Since no one has posted an example in ILE RPG for a SQL result set, I'll post one in case someone can run an explicit test. I suspect there are still numerous systems that are pre-V5R1 out there. The previous example is intriguing in its use of native I/O to create a result set. At the very worst, even later sites might use examples. A short description follows the code:
         H  dftactgrp( *NO )
         H   actgrp( 'RESULTSET' )
    
         D SQLErrorMsg     pr
    
         C/EXEC SQL
         C+ SET OPTION Commit=*NONE ,
         C+            Naming=*SYS ,
         C+            DynUsrPrf=*Owner
         C/END-EXEC
    
         C/EXEC SQL
         C+ DECLARE C1 CURSOR FOR
         C+     SELECT *
         C+         FROM QIWS/QCUSTCDT
         C/END-EXEC
    
         C/EXEC SQL
         C+ OPEN C1
         C/END-EXEC
    
         C                   if        SQLCOD < *zero
         C                   callp (E) SQLErrorMsg
         C                   endif
    
         C/EXEC SQL
         C+ SET RESULT SETS CURSOR C1
         C/END-EXEC
    
         C                   if        SQLCOD < *zero
         C                   callp (E) SQLErrorMsg
         C                   endif
    
         c                   return
    
          *================================================================
    
         P SQLErrorMsg     b
    
         D QMHSNDPM        pr                  extpgm( 'QMHSNDPM' )
         D  pMsgId                             const like( MsgID )
         D  pMsgFile                     20a   const
         D  pMsgDta                     256    const
         D  pMsgDtaLen                   10i 0 const
         D  pMsgType                     10a   const
         D  pMsgStack                    10a   const
         D  pMsgStack#                   10i 0 const
         D  pMsgKey                            like( MsgKey )
         D  pMsgError                          like( dsErrCode )
          *
         D MsgId           s              7a
         D MsgKey          s              4a
    
         DdsErrCode        ds
         D  BytesProvided                10i 0 inz( %size(MsgData) )
         D  BytesAvail                   10i 0
         D  ExceptionID                   7
         D  Reserved                      1
         D  MsgData                     128
    
         C                   eval      MsgID = 'SQL'
         c                                   + %Subst(
         c                                             %EditW(
         c                                                     %Abs(SQLCOD)
         c                                                   : '0         '
         c                                                   )
         c                                           : 7
         c                                           )
          *  3 levels - subprocedure / program / calling program
         C                   callp (E) QMHSNDPM(
         c                                       MsgId
         C                                     : 'QSQLMSG   *LIBL'
         C                                     : SQLERM
         C                                     : SQLERL
         C                                     : '*ESCAPE'
         C                                     : '*'
         C                                     : 2
         C                                     : MsgKey
         C                                     : dsErrCode
         c                                     )
         C                   return
    
         P SQLErrorMsg     e
    Any site that uses OpsNav (and that can compile a SQLRPGLE program) should be able to test this. It returns a result set from IBM's example QCUSTCDT file in the iSeries Access QIWS library. That's all it does. No parms, no special selection criteria -- just return the result set. It can be cloned to create variations for testing. For example, add a parm to pass a value for a WHERE clause. Create the stored proc by running these statements in OpsNav 'Run SQL Scripts' or from a RUNSQLSTM source member:
    DROP SPECIFIC PROCEDURE mylib/RESULTSET ; 
      
    CREATE PROCEDURE mylib/RESULTSET ( ) 
    	DYNAMIC RESULT SETS 1 
    	LANGUAGE RPGLE 
    	SPECIFIC mylib/RESULTSET 
    	NOT DETERMINISTIC 
    	READS SQL DATA 
    	CALLED ON NULL INPUT 
    	EXTERNAL NAME 'mylib/RESULTSET' 
    	PARAMETER STYLE GENERAL ; 
      
    COMMENT ON SPECIFIC PROCEDURE mylib/RESULTSET 
    	IS 'Result set QIWS/QCUSTCDT' ;
    The DROP PROCEDURE statement will naturally fail if it's run before the first creation. You can specify ERRLVL(20) to ignore it with RUNSQLSTM or use comment characters to nullify the lines. After creation, just run CALL RESULTSET in 'Run SQL Scripts' to test it. I tested this with V5R3, and that's as far back as I can go. It compiled back to V5R1. If anyone can go earlier, any info will be useful to someone. Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following