Stored Procedures error SQL0440N on db2 8.1 fp5

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • shalini@austin.rr.com
    #1

    Stored Procedures error SQL0440N on db2 8.1 fp5

    All, we are trying to create and execute our stored procs on db2 ver
    8.1 fp5. This is a new database that we setup and are having some
    trouble. When I try and run the stored proc from the db2 command line,
    I get the following error:

    SQL0440N No authorized routine named "XXX" of type
    "PROCEDURE" having compatible arguments was found. SQLSTATE=42884

    When the application (java) tries to execute the sp. we see the
    following error:

    com.ibm.db2.jcc .c.....SqlExcep tion: DB2 SQL error: SQLCODE: -444,
    SQLSTATE: 42724, SQLERRMC:

    We have checked to make sure that:

    - the schema exists
    - the SP exists
    - user executing the sp has execute permissions on the proc
    - the user also has insert/update/delete rights on the table being
    accessed in the sp

    Please note that this is happening for all our stored procs.

    Thanks!

    Tags: None
    • Serge Rielau
      #2
      Re: Stored Procedures error SQL0440N on db2 8.1 fp5

      shalini@austin. rr.com wrote:[color=blue]
      > All, we are trying to create and execute our stored procs on db2 ver
      > 8.1 fp5. This is a new database that we setup and are having some
      > trouble. When I try and run the stored proc from the db2 command line,
      > I get the following error:
      >
      > SQL0440N No authorized routine named "XXX" of type
      > "PROCEDURE" having compatible arguments was found. SQLSTATE=42884
      >
      > When the application (java) tries to execute the sp. we see the
      > following error:
      >
      > com.ibm.db2.jcc .c.....SqlExcep tion: DB2 SQL error: SQLCODE: -444,
      > SQLSTATE: 42724, SQLERRMC:
      >
      > We have checked to make sure that:
      >
      > - the schema exists
      > - the SP exists
      > - user executing the sp has execute permissions on the proc
      > - the user also has insert/update/delete rights on the table being
      > accessed in the sp
      >
      > Please note that this is happening for all our stored procs.
      >
      > Thanks!
      >[/color]
      The number one reason for unexpected -440 is that the proc has a
      CHAR() or SMALLINT argument and literals are provided (which are
      VARCHAR() and INTEGER respectively).
      Use CAST('hello' AS CHAR(..)) and CAST(1 AS SMALLINT) as required.
      Once we get the -440 out of teh way let's look at the -444.

      Cheers
      Serge

      --
      Serge Rielau
      DB2 SQL Compiler Development
      IBM Toronto Lab

        Comment

        • shalini@austin.rr.com
          #3
          Re: Stored Procedures error SQL0440N on db2 8.1 fp5

          FYI...The same stored procs work on another database, same version. The
          only thing that we changed when we created this new database, was the
          schema. Any thoughts on that?

          Serge Rielau wrote:[color=blue]
          > shalini@austin. rr.com wrote:[color=green]
          > > All, we are trying to create and execute our stored procs on db2[/color][/color]
          ver[color=blue][color=green]
          > > 8.1 fp5. This is a new database that we setup and are having some
          > > trouble. When I try and run the stored proc from the db2 command[/color][/color]
          line,[color=blue][color=green]
          > > I get the following error:
          > >
          > > SQL0440N No authorized routine named "XXX" of type
          > > "PROCEDURE" having compatible arguments was found. SQLSTATE=42884
          > >
          > > When the application (java) tries to execute the sp. we see the
          > > following error:
          > >
          > > com.ibm.db2.jcc .c.....SqlExcep tion: DB2 SQL error: SQLCODE: -444,
          > > SQLSTATE: 42724, SQLERRMC:
          > >
          > > We have checked to make sure that:
          > >
          > > - the schema exists
          > > - the SP exists
          > > - user executing the sp has execute permissions on the proc
          > > - the user also has insert/update/delete rights on the table being
          > > accessed in the sp
          > >
          > > Please note that this is happening for all our stored procs.
          > >
          > > Thanks!
          > >[/color]
          > The number one reason for unexpected -440 is that the proc has a
          > CHAR() or SMALLINT argument and literals are provided (which are
          > VARCHAR() and INTEGER respectively).
          > Use CAST('hello' AS CHAR(..)) and CAST(1 AS SMALLINT) as required.
          > Once we get the -440 out of teh way let's look at the -444.
          >
          > Cheers
          > Serge
          >
          > --
          > Serge Rielau
          > DB2 SQL Compiler Development
          > IBM Toronto Lab[/color]

            Comment

            • Serge Rielau
              #4
              Re: Stored Procedures error SQL0440N on db2 8.1 fp5

              shalini@austin. rr.com wrote:[color=blue]
              > FYI...The same stored procs work on another database, same version. The
              > only thing that we changed when we created this new database, was the
              > schema. Any thoughts on that?
              >
              > Serge Rielau wrote:
              >[color=green]
              >>shalini@austi n.rr.com wrote:
              >>[color=darkred]
              >>>All, we are trying to create and execute our stored procs on db2[/color][/color]
              >
              > ver
              >[color=green][color=darkred]
              >>>8.1 fp5. This is a new database that we setup and are having some
              >>>trouble. When I try and run the stored proc from the db2 command[/color][/color]
              >
              > line,
              >[color=green][color=darkred]
              >>>I get the following error:
              >>>
              >>>SQL0440N No authorized routine named "XXX" of type
              >>>"PROCEDURE " having compatible arguments was found. SQLSTATE=42884
              >>>
              >>>When the application (java) tries to execute the sp. we see the
              >>>following error:
              >>>
              >>>com.ibm.db2. jcc.c.....SqlEx ception: DB2 SQL error: SQLCODE: -444,
              >>>SQLSTATE: 42724, SQLERRMC:
              >>>
              >>>We have checked to make sure that:
              >>>
              >>>- the schema exists
              >>>- the SP exists
              >>>- user executing the sp has execute permissions on the proc
              >>>- the user also has insert/update/delete rights on the table being
              >>>accessed in the sp
              >>>
              >>>Please note that this is happening for all our stored procs.
              >>>
              >>>Thanks!
              >>>[/color]
              >>
              >>The number one reason for unexpected -440 is that the proc has a
              >>CHAR() or SMALLINT argument and literals are provided (which are
              >>VARCHAR() and INTEGER respectively).
              >>Use CAST('hello' AS CHAR(..)) and CAST(1 AS SMALLINT) as required.
              >>Once we get the -440 out of teh way let's look at the -444.
              >>
              >>Cheers
              >>Serge
              >>
              >>--
              >>Serge Rielau
              >>DB2 SQL Compiler Development
              >>IBM Toronto Lab[/color]
              >
              >[/color]
              SET PATH = CURRENT PATH, <thatnewschem a>

              Cheers
              Serge

              --
              Serge Rielau
              DB2 SQL Compiler Development
              IBM Toronto Lab

                Comment

                • shalini@austin.rr.com
                  #5
                  Re: Stored Procedures error SQL0440N on db2 8.1 fp5

                  Thank you we figured out our issue. We were using a type 4 driver
                  instead of a type 2 driver to connect!


                  Serge Rielau wrote:[color=blue]
                  > shalini@austin. rr.com wrote:[color=green]
                  > > FYI...The same stored procs work on another database, same version.[/color][/color]
                  The[color=blue][color=green]
                  > > only thing that we changed when we created this new database, was[/color][/color]
                  the[color=blue][color=green]
                  > > schema. Any thoughts on that?
                  > >
                  > > Serge Rielau wrote:
                  > >[color=darkred]
                  > >>shalini@austi n.rr.com wrote:
                  > >>
                  > >>>All, we are trying to create and execute our stored procs on db2[/color]
                  > >
                  > > ver
                  > >[color=darkred]
                  > >>>8.1 fp5. This is a new database that we setup and are having some
                  > >>>trouble. When I try and run the stored proc from the db2 command[/color]
                  > >
                  > > line,
                  > >[color=darkred]
                  > >>>I get the following error:
                  > >>>
                  > >>>SQL0440N No authorized routine named "XXX" of type
                  > >>>"PROCEDURE " having compatible arguments was found. SQLSTATE=42884
                  > >>>
                  > >>>When the application (java) tries to execute the sp. we see the
                  > >>>following error:
                  > >>>
                  > >>>com.ibm.db2. jcc.c.....SqlEx ception: DB2 SQL error: SQLCODE: -444,
                  > >>>SQLSTATE: 42724, SQLERRMC:
                  > >>>
                  > >>>We have checked to make sure that:
                  > >>>
                  > >>>- the schema exists
                  > >>>- the SP exists
                  > >>>- user executing the sp has execute permissions on the proc
                  > >>>- the user also has insert/update/delete rights on the table being
                  > >>>accessed in the sp
                  > >>>
                  > >>>Please note that this is happening for all our stored procs.
                  > >>>
                  > >>>Thanks!
                  > >>>
                  > >>
                  > >>The number one reason for unexpected -440 is that the proc has a
                  > >>CHAR() or SMALLINT argument and literals are provided (which are
                  > >>VARCHAR() and INTEGER respectively).
                  > >>Use CAST('hello' AS CHAR(..)) and CAST(1 AS SMALLINT) as required.
                  > >>Once we get the -440 out of teh way let's look at the -444.
                  > >>
                  > >>Cheers
                  > >>Serge
                  > >>
                  > >>--
                  > >>Serge Rielau
                  > >>DB2 SQL Compiler Development
                  > >>IBM Toronto Lab[/color]
                  > >
                  > >[/color]
                  > SET PATH = CURRENT PATH, <thatnewschem a>
                  >
                  > Cheers
                  > Serge
                  >
                  > --
                  > Serge Rielau
                  > DB2 SQL Compiler Development
                  > IBM Toronto Lab[/color]

                    Comment

                    Previous template Next
                    Working...