ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

AS400 SQL Stored proc creates extra parms??

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • AS400 SQL Stored proc creates extra parms??

    We have a vendor app that's written in a language that's close to VB4.

    Wrote a stored proc in SQL Server with parameters and the vendor app calls it fine.

    Wrote the same SP in AS400 (SQL stored proc, not an external one) and get this error:
    the TCP/IP connection was terminated unexpectedly

    -- Ran some tests:
    I open iSeries Navigator and call the stored proc. It works fine.
    My coworker used strsql and called it . it worked fine.
    When we call it with NO parameters, it works fine.
    Calling As400 from .Net app works fine.
    Calling SQL Server stored proc from the vendor app works fine.

    -- My coworker said when a SQL stored proc is created on AS400...AS400 creates that with extra parameters. We cant see these parameters How he found this out...i dont know. But is this true?

    We called IBM and they said to create a CL.

    Any ideas?

  • #2
    Re: AS400 SQL Stored proc creates extra parms??

    I don't think that a SQL stored procedure can be created with extra parameters. It could however be the case with an EXTERNAL stored procedure where the parameter styles can be :

    . SQL parameter style
    . DB2SQL parameter style
    . GENERAL WITH NULLS parameter style
    . GENERAL parameter style

    Check out chapters 5 & 6 in the Redbook
    Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeries
    Philippe

    Comment


    • #3
      Re: AS400 SQL Stored proc creates extra parms??

      I'll read that but it's not an External stored proc.

      It has : Language SQL

      We even shortened the name to 10 chars just in case AS400 is looking for 10 chars (someone said old AS400 didnt take names longer than 10)...

      we're lost ...

      Comment


      • #4
        Re: AS400 SQL Stored proc creates extra parms??

        Can you paste the stored procedure here pls ?
        What is the error MSGID (CPFnnnn?) you get ?
        Last edited by Mercury; October 25, 2007, 06:24 PM.
        Philippe

        Comment


        • #5
          Re: AS400 SQL Stored proc creates extra parms??

          The error is what I posted earlier...about TCP IP connection. We see that thru our vendor app... Runs fine when I call it from iseries Navigator. My coworker tried strsql and he said it worked fine from there too... I'm thinking it's the vendor app but the vendor app calls a SQL Server sp just fine...

          Code:
          Set Current Schema ='....';
          
           Create Procedure usp_UpdateCBIRpting
          
          	(IN reportRunDate Date,
          
          	IN whoIn Char(10),
          
          	IN whereIn Char(10))
          
          
          
              Result Sets 1
          
              Language SQL
          
              Not Deterministic
          
          
          
          Begin
          
          
           
             DECLARE SQLCODE INTEGER DEFAULT 0;
             DECLARE retCode INTEGER DEFAULT 0;
          
           --1: log errors.
          
             DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING , NOT FOUND
               Begin
                 SET retCode = SQLCODE;
                 Insert into  VQUECTL.EVENTLOG (EVAGENCY,EVWHEN,EVWHENT,EVWHO,EvPGM,EVMSG)
                    values(Right(current schema,3),CURDATE(),CURTIME(),whoin,'usp_UpdateCBIRpting SP','Code (No Error if Code=0) :' || retcode );
               End;
          
          
          --log the procedure call
          
          Insert Into VQUECTL.EVENTLOG (EVAGENCY, EVWHEN, EVWHENT, EVWHO, EVWHERE, EVPGM, EVMSG) 
          
              Values(Right(Current Schema,3), CurDate(), CurTime(), whoIn, whereIn, 'usp_UpdateCBIRpting', 'Start of SP');
          
          
          Update IPFCB
          
          Set T8COUNT = T8COUNT + 1 
          
              Where T8DATRPT >= reportRunDate and T8ACTP in ('DA', '62');
          
          
          
          Update DETAILDB
          
              Set D2CRED = '1' 
          
              Where Exists(Select * From IPFCB Where T8REF = D2REF and T8COUNT > 3);
          
          
          
          End

          Comment


          • #6
            Re: AS400 SQL Stored proc creates extra parms??

            I said the error MSGID not the message text.
            You don't declare or open any cursor in this procedure. What the heck is this "Result sets 1" for ? Take it out, recreate the stored procedure and give it a try.
            Last edited by Mercury; October 25, 2007, 06:38 PM.
            Philippe

            Comment


            • #7
              Re: AS400 SQL Stored proc creates extra parms??

              The vendor app doesnt show an MSGID.

              I have to remove that Result Sets 1 in the morning and ask my coworker to run the app.

              Will post back in the morning.

              Comment


              • #8
                Re: AS400 SQL Stored proc creates extra parms??

                removed result 1 but didnt work. Got more info about the error.

                -- Ran the stored proc from the Green Screen and got this:

                Code:
                CALL PGM(QUE25/USP_UCBIR) PARM('2007-10-24' 'TOM' 'MyLaptop')
                PHP Code:
                Error:
                 
                Message ID . . . . . . :   CEE9901       Severity . . . . . . . :   30

                 Message type 
                . . . . . :   Information

                 Date sent  
                . . . . . . :   26/10/07      Time sent  . . . . . . :
                08:16:05  
                 

                 Message 
                . . . . :   Application error.  MCH3601 unmonitored by USP_UCBIR at

                   statement 0000000010
                instruction X'0000'.

                 
                Cause . . . . . :   The application ended abnormally because an exception

                   occurred 
                and was not handled.  The name of the program to which the

                   unhandled exception is sent is USP_UCBIR USP_UCBIR main
                The program was

                   stopped at the high
                -level language statement number(s0000000010 at the

                   time the message was sent
                .  If more than one statement number is shownthe 
                   program is an optimized ILE program
                .  Optimization does not allow a single  
                   statement number to be determined
                .  If *N is shown as a valueit means the 
                   real value was not available
                .

                 
                Recovery  . . . :   See the low level messages previously listed to locate
                the
                   cause of the exception
                .  Correct any errors, and then try the request again
                Job Log has this:

                PHP Code:
                CALL PGM(QUE25/USP_UCBIRPARM('2007-10-24' 'TOM' 'MyLaptop')
                 
                Pointer not set for location referenced.                           
                 
                Application error.  MCH3601 unmonitored by USP_UCBIR at statement  
                   0000000010
                instruction X'0000'.                                 
                 
                Application error.  MCH3601 unmonitored by USP_UCBIR at statement  
                   0000000010
                instruction X'0000'

                For now, we used SQL/RPG but if anyone knows what's going on, i'd like to know.

                Comment


                • #9
                  Re: AS400 SQL Stored proc creates extra parms??

                  You must call the SP from STRSQL
                  STRSQL
                  CALL QUE25/USP_UCBIR ('2007-10-24' 'TOM' 'MyLaptop')

                  or in an embedded SQL RPG program.
                  /free
                  Exec sql CALL QUE25/USP_UCBIR ('2007-10-24', 'TOM', 'MyLaptop');

                  you can also pass parameters defined in your program instead
                  Exec sql CALL QUE25/USP_UCBIR (:ReportRunDate, :WhoIn, :WhereIn);

                  You can NOT call the SP from a command line. Unlike a classical program the SP is stored in DB2 by the DB2 Mgmt System when you create the procedure.
                  Philippe

                  Comment


                  • #10
                    Re: AS400 SQL Stored proc creates extra parms??

                    Asked him to run it from strsql.

                    We need to see if the vendor app has something like 'go inside strsql first, then run the stored proc'.

                    We're trying to write it in RPG/SQL. I'm not an AS400 developer but we're getting error 7008 for that one when we execute the RPG/SQL code.

                    It's a dynamic update ..something like :
                    sqlstmt = 'Update ' + %Char(libraryName) + ' .....'

                    Right after that, we have execute immediate sqlstmt. We look at the value sqlstmt and the syntax is correct.

                    Having said all that...your explanation makes sense now. I think vendor app doesnt know how to go inside strsql and tries to run it as an AS400 program.

                    Comment


                    • #11
                      Re: AS400 SQL Stored proc creates extra parms??

                      This call should work within STRSQL
                      CALL QUE25/USP_UCBIR ('2007-10-24' 'TOM' 'MyLaptop')
                      Don't try to put PGM or PARM. Just type literally as I've written.
                      However not sure to understand all what you're trying to tell me.
                      I presume that you get the 7008 on a dynamic update stm ?
                      Where does this dynamic stm take place in the procedure ??
                      Pls Paste here the SQLRPG program.
                      Philippe

                      Comment


                      • #12
                        Re: AS400 SQL Stored proc creates extra parms??

                        Yes, that call from STRSQL works fine.

                        I was saying that my coworker is trying to do the AS400 Stored proc in SQL/RPG but he was getting error 7008. I have to ask him to give me a copy of his SQL/RPG and post it here.

                        Otherwise, your explanation makes sense to me now. Vendor app probably doesnt know how to go inside STRSQL and gives that error.

                        Let me see if he still has that SQL/RPG code.

                        Comment


                        • #13
                          Re: AS400 SQL Stored proc creates extra parms??

                          He didnt save the SQL/RPG code.

                          Comment


                          • #14
                            Re: AS400 SQL Stored proc creates extra parms??

                            I'm confused. I don't clearly understand what you're trying to do.
                            You wrote a stored procedure in SPL (SQL Procedural Language). This procedure works now as intended. Then you write the same procedure in RPG and you dynamically build the SQL stm in the program. What is your goal ? If as I presume, you get issues when trying to recreate the SQL SP in RPG and can't tune the program accordingly, I can paste here a RPG IV program showing how to do within a few minutes. Should you be interested ?

                            PS. I can assure you, without knowing sql server that vendor app is not "going inside strsql" I call sometimes DB2 stored procedures from a client VBA and the call has nothing to do with strsql.
                            Philippe

                            Comment


                            • #15
                              Re: AS400 SQL Stored proc creates extra parms??

                              No, you answered my question.

                              My coworker wanted to rewrite the SQL Stored proc in RPG/SQL and he was getting errors. That's what i was talking about.

                              But you did answer my question. All makes sense.

                              Comment

                              Working...
                              X