Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    85

    Question Unanswered: Stored Procedure Creation Failure

    User is getting a -551 when attempting to create the below stored procedure:

    x110488@njros1up3145[/home/x110488/GIDW_reports/SP]>db2 -td@ -f 203_PREM_SP00.db2
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0551N "X110488" does not have the privilege to perform operation "SELECT"
    on object "DXTDB.DXTBAR_ERND_PREM". LINE NUMBER=704. SQLSTATE=42501

    x110488@njros1up3145[/home/x110488/GIDW_reports/SP]>db2 "select * from DXTDB.DXTBAR_ERND_PREM fetch first 10 rows only"

    CTLNO_NUM GL_PROD_CD PROD_CD ACCT_CS_CD EP_YEAR EP_PERIOD EP_AS_OF_DT CRNCY_CD PREM_SRC_SYS_CD ERND_PREM_AMT ER_PRM_AMT_DC_IND SRC_SYS_CD SRC_SYS_ENTR_DT DATA_DT REC_LD_DT_TS
    ---------- ------------ ------------ ---------- ------- ---------- ----------- -------- --------------- ------------------- ----------------- ---------- --------------- ---------- --------------------------
    0000090316 LGREGL BASLIF 0000001 2001 JAN 12/31/2001 US GBS 0.01 C BAR 11/20/2003 10/31/2003 2003-12-16-15.56.45.330578
    0000090316 LGREGL DPL 0000001 2001 JAN 12/31/2001 US GBS 0.01 C BAR 11/20/2003 10/31/2003 2003-12-16-15.56.45.330578

    Yet, the instance owner can create the below stored proc.
    What possible privilege authorization could the user who's in a group be lacking?

    Ruby

    P.S. Note that it's the SELECT at the end of the sp which is failing. I have to cut out some of the SP code since the thread can't handle all of the text.


    CREATE PROCEDURE DXTDB.SP_RPT203 (
    INOUT CONSTANT_CTLNO_NUM VARCHAR(1000),
    IN CONSTANT_AS_OF_DT DATE,
    IN CONSTANT_FROM_DATE DATE,
    IN CONSTANT_TO_DATE DATE,
    IN CONSTANT_CLIENTING CHAR(1),
    IN CONSTANT_SRC_SYS_CD CHAR(3),
    IN CONSTANT_GI_PROD_CD VARCHAR(1000),
    IN CONSTANT_GL_PROD_CD VARCHAR(1000),
    IN CONSTANT_PROD_FAM_CD VARCHAR(1000),
    IN CONSTANT_PROD_FUND_ARGMT_CD VARCHAR(1000),
    IN CONSTANT_BL_GRP_CD VARCHAR(2500),
    IN CONSTANT_BL_BR_CD VARCHAR(2500),
    IN CONSTANT_MBR_GRP_CD VARCHAR(2500),
    IN CONSTANT_BLLN_CD VARCHAR(2500),
    IN CONSTANT_ACCT_CS_CD VARCHAR(2500),
    INOUT L_SQLCODE INTEGER,
    INOUT IO_SQLMSG CHAR(254),
    INOUT IO_STMT VARCHAR(10000)
    )
    DYNAMIC RESULT SETS 4
    LANGUAGE SQL
    BEGIN
    DECLARE CONSTANT_RPL_CTLNO_NUM VARCHAR(1500);
    DECLARE CONSTANT_RPL_SRC_SYS_CD VARCHAR(1500);
    DECLARE CONSTANT_RPL_GI_PROD_CD VARCHAR(1500);
    DECLARE CONSTANT_RPL_GL_PROD_CD VARCHAR(1500);
    DECLARE CONSTANT_RPL_PROD_FAM_CD VARCHAR(1500);
    DECLARE CONSTANT_RPL_PROD_FUND_ARGMT_CD VARCHAR(1500);
    DECLARE CONSTANT_RPL_BL_GRP_CD VARCHAR(3000);
    DECLARE CONSTANT_RPL_BL_BR_CD VARCHAR(3000);
    DECLARE CONSTANT_RPL_MBR_GRP_CD VARCHAR(3000);
    DECLARE CONSTANT_RPL_BLLN_CD VARCHAR(3000);
    DECLARE CONSTANT_RPL_ACCT_CS_CD VARCHAR(3000);
    DECLARE SQL_STMT VARCHAR(25000);
    DECLARE SQL_STMT1 VARCHAR(25000);
    DECLARE SQL_BLLN_CD_COND VARCHAR(2000);
    DECLARE SQL_SRC_SYS_CD_COND VARCHAR(2000);
    DECLARE SQL_BL_GRP_CD_COND VARCHAR(2000);
    DECLARE SQL_BL_BR_CD_COND VARCHAR(2000);
    DECLARE SQL_MBR_GRP_CD_COND VARCHAR(2000);
    DECLARE SQL_GL_PROD_CD_COND VARCHAR(2000);
    DECLARE SQL_GI_PROD_CD_COND VARCHAR(2000);
    DECLARE SQL_PROD_FAM_CD_COND VARCHAR(2000);
    DECLARE SQL_PROD_FUND_ARGMT_CD_COND VARCHAR(2000);
    DECLARE SQL_ACCT_CS_CD_COND VARCHAR(2000);
    DECLARE SQL_STMT2 VARCHAR(25000);
    DECLARE IN_SQLCODE INTEGER;
    DECLARE IN_SQLMSG CHAR(254);
    DECLARE SQLCODE INTEGER;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET L_SQLCODE = 100;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    GET DIAGNOSTICS EXCEPTION 1 io_sqlmsg = MESSAGE_TEXT;
    SET L_SQLCODE = SQLCODE;
    SET IO_STMT=SQL_STMT;
    END;

    SET CONSTANT_RPL_CTLNO_NUM = ''''||REPLACE(CONSTANT_CTLNO_NUM,',',''',''')||''' ';
    SET CONSTANT_RPL_SRC_SYS_CD = ''''||REPLACE(CONSTANT_SRC_SYS_CD,',',''',''')||'' '';
    SET CONSTANT_RPL_GI_PROD_CD = ''''||REPLACE(CONSTANT_GI_PROD_CD,',',''',''')||'' '';
    SET CONSTANT_RPL_PROD_FAM_CD = ''''||REPLACE(CONSTANT_PROD_FAM_CD,',',''',''')||' ''';
    SET CONSTANT_RPL_PROD_FUND_ARGMT_CD = ''''||REPLACE(CONSTANT_PROD_FUND_ARGMT_CD,',',''', ''')||'''';
    SET CONSTANT_RPL_BL_GRP_CD = ''''||REPLACE(CONSTANT_BL_GRP_CD,',',''',''')||''' ';
    SET CONSTANT_RPL_BL_BR_CD = ''''||REPLACE(CONSTANT_BL_BR_CD,',',''',''')||'''' ;
    SET CONSTANT_RPL_BLLN_CD = ''''||REPLACE(CONSTANT_BLLN_CD,',',''',''')||'''';
    SET CONSTANT_RPL_MBR_GRP_CD = ''''||REPLACE(CONSTANT_MBR_GRP_CD,',',''',''')||'' '';
    ----------------------------------------------------------------------------
    -- GET THE ACCELERATED CLOSE DATE FOR EACH SOURCE SYSTEM --
    ----------------------------------------------------------------------------
    DECLARE GLOBAL TEMPORARY TABLE SESSION.TMP_ACCT_CL_DT
    (
    SRC_SYS_CD CHAR(3)
    ,ACCT_MTH_CAL_DT DATE
    ,ACCT_YR_NUM SMALLINT
    ,ACCT_MTH_NUM SMALLINT
    )
    WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
    IF CONSTANT_SRC_SYS_CD = 'ALL'
    THEN
    SET SQL_STMT=
    'INSERT INTO SESSION.TMP_ACCT_CL_DT ' ||
    ' ( ' ||
    ' SELECT SRC_SYS_CD ' ||
    ' ,ACCT_MTH_CAL_DT ' ||
    ' ,ACCT_YR_NUM ' ||
    ' ,ACCT_MTH_NUM ' ||
    ' FROM DXTDB.DXT_ACCT_MTH_XREF ' ||
    ' WHERE ACCT_YR_NUM = YEAR('''||CHAR(CONSTANT_AS_OF_DT,ISO)||''') ' ||
    ' AND ACCT_MTH_NUM = MONTH('''||CHAR(CONSTANT_AS_OF_DT,ISO)||''') ) ';
    ELSE
    SET SQL_STMT=
    'INSERT INTO SESSION.TMP_ACCT_CL_DT ' ||
    ' ( ' ||
    ' SELECT SRC_SYS_CD ' ||
    ' ,ACCT_MTH_CAL_DT ' ||
    ' ,ACCT_YR_NUM ' ||
    ' ,ACCT_MTH_NUM ' ||
    ' FROM DXTDB.DXT_ACCT_MTH_XREF ' ||
    ' WHERE ACCT_YR_NUM = YEAR('''||CHAR(CONSTANT_AS_OF_DT,ISO)||''') ' ||
    ' AND ACCT_MTH_NUM = MONTH('''||CHAR(CONSTANT_AS_OF_DT,ISO)||''') '||
    ' AND SRC_SYS_CD IN ( '||CONSTANT_RPL_SRC_SYS_CD||') ) ';
    END IF;
    PREPARE S1 FROM SQL_STMT;
    EXECUTE S1;
    SET SQL_STMT='';
    SET SQL_STMT1='';
    SET SQL_STMT2='';

    DECLARE PREM_RPT203_4 CURSOR WITH RETURN TO CALLER FOR
    -- values 'a';
    SELECT MAX(EP_AS_OF_DT) AS "BARRS Valuation Date" FROM DXTDB.DXTBAR_ERND_PREM WHERE EP_AS_OF_DT <= DATE(CONSTANT_AS_OF_DT);

    OPEN PREM_RPT203_1; OPEN PREM_RPT203_2;OPEN PREM_RPT203_3;OPEN PREM_RPT203_4;
    END;
    END @

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    what db2 version is this?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Sep 2003
    Posts
    85
    UDB 8.1.3 (ESE Fixpak 3).
    Based on most current doc from the Information Center, the user (actually the GROUP which the user is in) creating the procedure has BINDADD and CREATEIN authority for the rqeuired objects. Somehow, the GROUP id I believe isn't being incorporated.

    HTH,
    Ruby

    P.S. I found no hits on IBM's APAR except for 1 hit based on -551 and procedure as the keywords (and that APAR which isn't fixed even after FP5 doesn't relate to this problem).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •