Db/2 – Error – “SQL Error [42725]: Reference to routine was made without a signature, but the routine is not unique in its schema.. SQLCODE=-476”

Background

Trying to manage a routine and ended up with the error listed below.

Error

Error Image

SQLError.42725.SYSPROC.SNAP_GET_DB.20181013.0859PM

Error Text


SQL Error [42725]: Reference to routine "SYSPROC.SNAP_GET_DB" was made without a signature, but the routine is not unique in its schema.. SQLCODE=-476

Recreate Issue

SQL

Code

Syntax


GRANT EXECUTE ON FUNCTION [schema].[function] TO [principal]

Example


GRANT EXECUTE ON FUNCTION SYSPROC.SNAP_GET_DB TO DADENIJI

Trouble Shooting

Objective

Let us determine the signatures for our referenced object, SYSPROC.SNAP_GET_DB.

SQL

Identify Routine Parameters

Code


WITH cteRoutineParm
AS
(

    SELECT 

              tblRP.ROUTINESCHEMA
            , tblRP.ROUTINENAME
            , tblRP.SPECIFICNAME    

            , LISTAGG
                (
                      CASE 

                        WHEN (

                                tblRP.TYPENAME
                                    IN (
                                           'CHAR'
                                         , 'VARCHAR'
                                       )
                            )

                                THEN tblRP.TYPENAME
                                    CONCAT ' ('
                                    CONCAT tblRP.LENGTH
                                    CONCAT ' )'

                        ELSE tblRP.TYPENAME

                      END       

                    , ', '
                )
              WITHIN GROUP
              (
                ORDER BY

                    tblRP.ORDINAL
              )

              AS "type"         

    FROM   SYSCAT.ROUTINEPARMS tblRP

    WHERE  ROWTYPE = 'P'

    GROUP BY

              tblRP.ROUTINESCHEMA
            , tblRP.ROUTINENAME
            , tblRP.SPECIFICNAME        

)   

SELECT
              tblSS.ROUTINESCHEMA
            , tblSS.ROUTINENAME
            , tblSS.SPECIFICNAME
            , tblSS.LANGUAGE
            , tblSS.IMPLEMENTATION
            , tblSS.PARM_COUNT
            , cteRP."type"

FROM SYSCAT.ROUTINES tblSS

INNER JOIN cteRoutineParm cteRP 

        ON tblSS.ROUTINESCHEMA = cteRP.ROUTINESCHEMA
        AND tblSS.ROUTINENAME  = cteRP.ROUTINENAME
        AND tblSS.SPECIFICNAME = cteRP.SPECIFICNAME 

WHERE tblSS.ROUTINESCHEMA  IN
        (
            'SYSPROC'
        )

AND tblSS.ROUTINENAME IN
        (
            'SNAP_GET_DB'
        )

ORDER BY
          1
        , 2
        , 3<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>

Output

SYSPROC.SNAP_GET_DB.20181014.1213PM.PNG

Explanation

  1. ROUTINESCHEMA
    • SYSPROC
  2. ROUTINENAME
    • SNAP_GET_DB
  3. SPECIFICNAME
    • SNAP_GET_DB
    • SNAP_GET_DB_AP
  4. LANGUAGE
    • C
  5. IMPLEMENTATION
    • db2monudf!snap_get_db
    • db2monudf!snap_get_db_ap
  6. PARAMETER COUNT
    • 2
    • 1
  7. PARAMETER TYPE
    • VARCHAR, INTEGER
    • VARCHAR

Remediation

Objective

Let us determine the signatures for our referenced object, SYSPROC.SNAP_GET_DB.

SQL

Use Specific Name

Outline

Use Specific Name.

Code

Syntax

GRANT EXECUTE ON SPECIFIC FUNCTION [schema].[function] TO principal

Actual

GRANT EXECUTE ON SPECIFIC FUNCTION SYSPROC.SNAP_GET_DB TO DADENIJI

Use Function Signature

Outline

Use Specific Name.

Code

Syntax

GRANT EXECUTE ON FUNCTION [schema].[function](parmtype1, parmtype2)<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span> TO principal

Actual

GRANT EXECUTE ON FUNCTION SYSPROC.SNAP_GET_DB (VARCHAR(255), INTEGER) TO DADENIJI

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s