Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2012
    Posts
    177

    Unanswered: Issue in db2stored procedure

    Hi all,

    In our setup db2 v9.7 with fixpack 4 with the partitioned database.

    db2level
    DB21085I Instance "db2inst1" uses "64" bits and DB2 code release "SQL09074"
    with level identifier "08050107".
    Informational tokens are "DB2 v9.7.0.4", "s110330", "IP23243", and Fix Pack
    "4".

    Facing issues with executed the stored procedure
    =====================================

    [db2inst1@vrdtisdbhcl01 ~]$ db2 -td@ -f Network_Performance_TVWOY_SP
    DB20000I The SQL command completed successfully.

    SQL0171N The data type, length or value of the argument for the parameter in
    position "1" of routine "DBMS_UTILITY.COMMA_TO_TABLE_LNAME" is incorrect.
    Parameter name: "LIST". SQLSTATE=42815


    How to avoid this issue? When i had googled for this issue need to apply patch, And how to apply this patch?

    Thanks,
    laxman..

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Not enough information ..

    what are the arguments you are passing to the routine ?

    Where did you read about patch ? any urls ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Sep 2012
    Posts
    177
    Sathyaram thanks for your valuable reply.

    This is the stored procedure executed in the database.

    CREATE OR REPLACE PROCEDURE "db2DWH".Network_Performance_TVWOY_SP(IN p_years VARCHAR(500))
    RESULT SETS 1
    LANGUAGE SQL
    P1: Begin
    DECLARE v_years_arr DBMS_UTILITY.LNAME_ARRAY;
    DECLARE v_length INTEGER;
    Gt1: Begin
    -- Staging table for query processing
    DECLARE GLOBAL TEMPORARY TABLE NetWork_Performance_stg(
    Years INTEGER) With Replace;
    CALL DBMS_UTILITY.COMMA_TO_TABLE_LNAME(p_years, v_length, v_years_arr);
    stg: BEGIN
    DECLARE i INTEGER DEFAULT 1;
    DECLARE loop_limit INTEGER;

    SET loop_limit = v_length;
    WHILE i <= loop_limit DO
    INSERT INTO SESSION.NetWork_Performance_stg(years)
    VALUES (TO_NUMBER(v_years_arr[i]));
    SET i = i + 1;
    END WHILE;
    END stg;

    -- Send results back to the caller
    Cr_NPI : Begin
    Declare Cr_NetWork_Perf Cursor With Return For
    WITH tis_asst_result AS (
    SELECT Week_No,
    Month_No,
    CASE WHEN (LEAD(cnt) OVER(ORDER BY Week_No,Month_No,Cal_Year) > cnt) AND
    (LEAD(month_no) OVER(ORDER BY Week_No,Month_No,Cal_Year) > Month_No)
    THEN LEAD(month_name) OVER(ORDER BY Week_No,Month_No,Cal_Year)
    WHEN (LAG(cnt) OVER(ORDER BY Week_No,Month_No,Cal_Year) > Cnt) AND
    (LAG(month_no) OVER(ORDER BY Week_No,Month_No,Cal_Year) < Month_No)
    THEN LAG(month_name) OVER(ORDER BY Week_No,Month_No,Cal_Year)
    ELSE Month_Name
    END as Month_Name,
    Cal_Year,
    Original_Value
    FROM(
    SELECT Week_No, Month_No,
    Month_Name, Cal_Year,
    Cnt, AVG(original_value) original_value
    FROM(
    SELECT a.dt, WEEK(a.dt) WEEK_NO,
    MONTH(a.dt) Month_no, MONTHNAME(a.dt) Month_Name,
    YEAR(a.dt) Cal_Year, COUNT(*) OVER(PARTITION BY week(a.dt),MONTH(a.dt),YEAR(a.dt)) cnt,
    AVG(a.original_value) original_value
    FROM "db2DWH".TIS_ASSMT_RESULT_DATA a,
    "db2DWH".TIS_ASSMT_RESULT b,
    "db2DWH".TIS_LOC_LRS_PHYSCL_LGA_SHORT_REF c,
    "db2DWH".TIS_LOC_LRS_PHYSCL_LGA_SHORT_RLTSHP d,
    "db2DWH".TIS_LOC_SITE e
    WHERE a.site_id = b.site_id
    AND a.tfm_id = b.tfm_id
    AND a.dt = b.srvy_dt
    AND a.metric_grp = b.metric_grp
    AND a.metric_sub_grp = b.metric_sub_grp
    AND c.lga_short_nm = d.physcl_lga_short
    AND d.lrs_id = e.lrs_id
    AND e.site_id = a.site_id
    AND e.data_src_cd = b.data_src_cd
    AND c.is_metro_rural_flg = 'M'
    AND d.is_default_flg = 'Y'
    AND a.data_intrvl_in_secs = 3600
    AND Coalesce(b.Sys_Data_Fix_Assmt_Flg,b.Oscltng_Assmt_ Flg,b.Matching_Assmt_Flg) = 'ACC'
    AND b.data_src_cd ='SCTVL'
    AND b.metric_grp = 'VOLUME'
    AND b.metric_sub_grp = 'ALL VEHICLES'
    AND b.is_latest_flg = 'Y'
    AND YEAR(b.srvy_dt) IN (SELECT years
    FROM Session.NetWork_Performance_stg)
    GROUP BY a.dt,WEEK(a.dt), MONTH(a.dt),MONTHNAME(a.dt),YEAR(A.dt))
    GROUP BY Week_No, Month_No, Month_Name,Cal_Year,Cnt))
    SELECT Cal_Year AS "CALENDAR YEAR",
    Month_Name AS "MONTH",
    Week_No AS "WEEK",
    Original_Value AS "WEEKLY AVERAGE",
    CAST(Original_Value AS DOUBLE)/ CAST(AVG(Original_Value) OVER(PARTITION BY Cal_Year) AS DOUBLE) AS "AS % OF ALL WEEKS"
    FROM tis_asst_result;
    OPEN Cr_NetWork_Perf;
    END Cr_NPI;
    END gt1;
    END P1@
    CALL "db2DWH".Network_Performance_TVWOY_SP('2010,2011,2 012')@


    Thanks,
    laxman..

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by laxman babu View Post
    ...

    CREATE OR REPLACE PROCEDURE "db2DWH".Network_Performance_TVWOY_SP(IN p_years VARCHAR(500))
    ...
    ...
    CALL DBMS_UTILITY.COMMA_TO_TABLE_LNAME(p_years, v_length, v_years_arr);
    ...
    ...
    ...
    ...
    END P1@
    CALL "db2DWH".Network_Performance_TVWOY_SP('2010,2011,2 012')@
    Looking in manuals, COMMA_TO_TABLE_LNAME procedure doesn't accept list of numbers.
    (Note: The names must be formatted as valid identifiers.)

    COMMA_TO_TABLE procedures - Convert a comma-delimited list of names into a table of names - IBM DB2 9.7 for Linux, UNIX, and Windows
    COMMA_TO_TABLE procedures - Convert a comma-delimited list of names into a table of names

    The COMMA_TO_TABLE procedure converts a comma-delimited list of names into an array of names. Each entry in the list becomes an element in the array.

    Note: The names must be formatted as valid identifiers.

    Syntax

    >>-COMMA_TO_TABLE_LNAME--(--list--,--tablen--,--tab--)---------><

    ...

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Briefly looking your code,
    I thought that you might used NetWork_Performance_stg and COMMA_TO_TABLE_LNAME to separate p_years into rows of years.

    If so,
    an alternate way might replace
    AND YEAR(b.srvy_dt) IN (SELECT years
    FROM Session.NetWork_Performance_stg)
    by
    Code:
    AND YEAR(b.srvy_dt) IN (
    SELECT INTEGER(
              SUBSTR(
                 p_years
               , LAG(pos_comma , 1 , 0) OVER(ORDER BY k) + 1
               , pos_comma - LAG(pos_comma , 1 , 0) OVER(ORDER BY k) - 1
              )
           ) AS years
     FROM  (SELECT k
                 , INSTR(p_years || ',' , ',' , 1 , k) AS pos_comma
             FROM  (VALUES 1,2,3,4,5,6,7,8,9,10) AS f(k)
           )
     WHERE pos_comma > 0
    )
    By the way,
    you might want to search on this forum to find other examples to separate a comma delimited string into elements.

Posting Permissions

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