Store Procedure failure

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • George
    #1

    Store Procedure failure

    Hi ,

    I am getting the below error while i am invoking a Java stored
    procedure from another java stored procedure.

    [IBM][CLI Driver][DB2/NT] SQL0751N Routine "" (specific name "")
    attempted to execute a statement that is not allowed.

    The Main SP say A ,calls SP1 reads the data and in a while loop
    calls the SP2 to insert the data in that is retrieved in the
    resultset.
    Failure happens when the executeUpdate() is called for SP2 in the
    first time itself.

    I am using DB2 v8.1.5.449, and developing using the DB2 SPB.

    Has anyone have any idea or ever faced this problem please do let me
    know the solution ..I am stuck in the middle of development with this
    issue.



    Thanks much in advance


    Regards,
    george
    Tags: None
    • Sean McKeough
      #2
      Re: Store Procedure failure

      Check the sql levels you created the routines with...you can't call a
      modifies sql data routine from a reads sql data routine for example...

      George wrote:[color=blue]
      > Hi ,
      >
      > I am getting the below error while i am invoking a Java stored
      > procedure from another java stored procedure.
      >
      > [IBM][CLI Driver][DB2/NT] SQL0751N Routine "" (specific name "")
      > attempted to execute a statement that is not allowed.
      >
      > The Main SP say A ,calls SP1 reads the data and in a while loop
      > calls the SP2 to insert the data in that is retrieved in the
      > resultset.
      > Failure happens when the executeUpdate() is called for SP2 in the
      > first time itself.
      >
      > I am using DB2 v8.1.5.449, and developing using the DB2 SPB.
      >
      > Has anyone have any idea or ever faced this problem please do let me
      > know the solution ..I am stuck in the middle of development with this
      > issue.
      >
      >
      >
      > Thanks much in advance
      >
      >
      > Regards,
      > george[/color]

        Comment

        • George
          #3
          Re: Store Procedure failure

          For all the procedures in the context it is given as the "stored
          procedure modifies sql data".

            Comment

            • Sean McKeough
              #4
              Re: Store Procedure failure

              I don't understand what this means. Do you mean you catalog this with
              "modifies SQL data" if so, your best bet is probably a statement event
              monitor to see exactly which SQL is being rejected.

              George wrote:[color=blue]
              > For all the procedures in the context it is given as the "stored
              > procedure modifies sql data".[/color]

                Comment

                • George
                  #5
                  Re: Store Procedure failure

                  Hi Sean,

                  Thanks for your reply..The procedure that are developed has "Modifies
                  SQL data" set in as the config parameter.

                  Using SPB of DB2 ,if i right click on Properties -->Options-->Store
                  Procedure-->
                  "Modifies SQL data"..

                  The problem is < I Have 2 procedures >

                  Main procedure -->That invokes SP1 --->>This invokes a set of
                  procedures-->>SP2,SP3

                  It fails at the first level itself ...by throwing the below exception

                  IBM][CLI Driver][DB2/NT] SQL0751N Routine "" (specific name "")
                  attempted to execute a statement that is not allowed.

                  Thanks

                  Rgds,george


                  Sean McKeough <mckeough@nospa m.ca.ibm.com> wrote in message news:<c60jm5$qo m$1@hanover.tor olab.ibm.com>.. .[color=blue]
                  > I don't understand what this means. Do you mean you catalog this with
                  > "modifies SQL data" if so, your best bet is probably a statement event
                  > monitor to see exactly which SQL is being rejected.
                  >
                  > George wrote:[color=green]
                  > > For all the procedures in the context it is given as the "stored
                  > > procedure modifies sql data".[/color][/color]

                    Comment

                    • George
                      #6
                      Re: Store Procedure failure

                      Hi Sean,

                      Thanks for your reply..The procedure that are developed has "Modifies
                      SQL data" set in as the config parameter.

                      Using SPB of DB2 ,if i right click on Properties -->Options-->Store
                      Procedure-->
                      "Modifies SQL data"..

                      The problem is < I Have 2 procedures >

                      Main procedure -->That invokes SP1 --->>This invokes a set of
                      procedures-->>SP2,SP3

                      It fails at the first level itself ...by throwing the below exception

                      IBM][CLI Driver][DB2/NT] SQL0751N Routine "" (specific name "")
                      attempted to execute a statement that is not allowed.

                      Thanks

                      Rgds,george


                      Sean McKeough <mckeough@nospa m.ca.ibm.com> wrote in message news:<c60jm5$qo m$1@hanover.tor olab.ibm.com>.. .[color=blue]
                      > I don't understand what this means. Do you mean you catalog this with
                      > "modifies SQL data" if so, your best bet is probably a statement event
                      > monitor to see exactly which SQL is being rejected.
                      >
                      > George wrote:[color=green]
                      > > For all the procedures in the context it is given as the "stored
                      > > procedure modifies sql data".[/color][/color]

                        Comment

                        • Sean McKeough
                          #7
                          Re: Store Procedure failure

                          To be sure that your routines are cataloged correctly, look at
                          SQL_DATA_ACCESS in SYSROUTINES. If they all show up with "M" then you
                          need to find exactly which sql statement is being rejected (statement
                          evmon or good sp error handling should find this)...another idea would
                          be to try invoking sp1 directly to see if it's really a problem with
                          routine interaction, or something lower down...

                          The only reason I can think of for a CALL statement to fail is if you're
                          calling a dari style (or uncataloged) sp, or if the incorrect SQL access
                          levels are set.

                          George wrote:[color=blue]
                          > Hi Sean,
                          >
                          > Thanks for your reply..The procedure that are developed has "Modifies
                          > SQL data" set in as the config parameter.
                          >
                          > Using SPB of DB2 ,if i right click on Properties -->Options-->Store
                          > Procedure-->
                          > "Modifies SQL data"..
                          >
                          > The problem is < I Have 2 procedures >
                          >
                          > Main procedure -->That invokes SP1 --->>This invokes a set of
                          > procedures-->>SP2,SP3
                          >
                          > It fails at the first level itself ...by throwing the below exception
                          >
                          > IBM][CLI Driver][DB2/NT] SQL0751N Routine "" (specific name "")
                          > attempted to execute a statement that is not allowed.
                          >
                          > Thanks
                          >
                          > Rgds,george
                          >
                          >
                          > Sean McKeough <mckeough@nospa m.ca.ibm.com> wrote in message news:<c60jm5$qo m$1@hanover.tor olab.ibm.com>.. .
                          >[color=green]
                          >>I don't understand what this means. Do you mean you catalog this with
                          >>"modifies SQL data" if so, your best bet is probably a statement event
                          >>monitor to see exactly which SQL is being rejected.
                          >>
                          >>George wrote:
                          >>[color=darkred]
                          >>>For all the procedures in the context it is given as the "stored
                          >>>procedure modifies sql data".[/color][/color][/color]

                            Comment

                            • Sean McKeough
                              #8
                              Re: Store Procedure failure

                              To be sure that your routines are cataloged correctly, look at
                              SQL_DATA_ACCESS in SYSROUTINES. If they all show up with "M" then you
                              need to find exactly which sql statement is being rejected (statement
                              evmon or good sp error handling should find this)...another idea would
                              be to try invoking sp1 directly to see if it's really a problem with
                              routine interaction, or something lower down...

                              The only reason I can think of for a CALL statement to fail is if you're
                              calling a dari style (or uncataloged) sp, or if the incorrect SQL access
                              levels are set.

                              George wrote:[color=blue]
                              > Hi Sean,
                              >
                              > Thanks for your reply..The procedure that are developed has "Modifies
                              > SQL data" set in as the config parameter.
                              >
                              > Using SPB of DB2 ,if i right click on Properties -->Options-->Store
                              > Procedure-->
                              > "Modifies SQL data"..
                              >
                              > The problem is < I Have 2 procedures >
                              >
                              > Main procedure -->That invokes SP1 --->>This invokes a set of
                              > procedures-->>SP2,SP3
                              >
                              > It fails at the first level itself ...by throwing the below exception
                              >
                              > IBM][CLI Driver][DB2/NT] SQL0751N Routine "" (specific name "")
                              > attempted to execute a statement that is not allowed.
                              >
                              > Thanks
                              >
                              > Rgds,george
                              >
                              >
                              > Sean McKeough <mckeough@nospa m.ca.ibm.com> wrote in message news:<c60jm5$qo m$1@hanover.tor olab.ibm.com>.. .
                              >[color=green]
                              >>I don't understand what this means. Do you mean you catalog this with
                              >>"modifies SQL data" if so, your best bet is probably a statement event
                              >>monitor to see exactly which SQL is being rejected.
                              >>
                              >>George wrote:
                              >>[color=darkred]
                              >>>For all the procedures in the context it is given as the "stored
                              >>>procedure modifies sql data".[/color][/color][/color]

                                Comment

                                Previous template Next
                                Working...