Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2010
    Posts
    40

    Unanswered: how to Tokenize the input?

    Hello,
    I have a stored procedure which has a input parameter that accepts multiple values as input and returns the respective out after filtering.

    example: call proc1('290,291,897,768,678');

    The column againest which I have to compare the above input is a integer . So I wrote the below query to tokenize the above input but unfortunately this query is taking more time than the actual query part.

    WITH T (ORDINAL, INDEX)
    AS (SELECT 0, 0 FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT ORDINAL + 1,
    COALESCE (NULLIF (LOCATE (',', '290,291', INDEX + 1), 0),
    LENGTH ('290,291') + 1)
    FROM T
    WHERE ORDINAL < length('290,291') AND LOCATE (',', '290,291', INDEX + 1) <> 0),
    T2 (COL1, COL2)
    AS (SELECT ORDINAL, INDEX FROM T
    UNION ALL
    SELECT MAX (ORDINAL) + 1, LENGTH ('290,291') + 1 FROM T),
    T3 (COL1)
    AS (SELECT INT (SUBSTR ('290,291', T1.COL2 + 1, T2.COL2 - T1.COL2 - 1))
    FROM T2 AS T1 JOIN T2 AS T2 ON (T2.COL1 = T1.COL1 + 1))

    when I generate a explain plan it clearly shows that it is looping more than what is needed(T query above).

    Environment is DB2 for ZOS.
    Version 8.X.

    Could somebody help me tokenize the above input?

    Thanks
    R.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought extra loop might be because of "ORDINAL < length('290,291')".
    It should be "INDEX < length('290,291')".

    Example: Tested on DB2 9.7.5 on Windows.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH T (ORDINAL , INDEX) AS (
    SELECT 0 , 0 FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT ORDINAL + 1
         , COALESCE(
              NULLIF(
                 LOCATE (',' , '290,291' , INDEX + 1)
               , 0
              )
            , LENGTH('290,291') + 1
           )
     FROM  T
     WHERE INDEX   < length('290,291')
       AND ORDINAL < 10000
    )
    SELECT INT( SUBSTR('290,291' , T1.INDEX + 1 , T2.INDEX - T1.INDEX - 1) )
     FROM  T AS T1
     INNER JOIN
           T AS T2
      ON   T2.ORDINAL = T1.ORDINAL + 1
    ;
    ------------------------------------------------------------------------------
    
    1          
    -----------
            290
            291
    
      2 record(s) selected.
    Last edited by tonkuma; 08-07-12 at 18:24. Reason: Reformat(add some blanks) sample code.

  3. #3
    Join Date
    Aug 2010
    Posts
    40
    thanks for the reply Tonkuma but that didnt solve my problem.Still the explain plan shows cross join. My explain plans first steps is a T tablescan and the cardinality for same is 10K.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Estimation of cardinality of temporary table may be not easy.
    So, the cardinality may be not a real number of rows.

    Please try...
    Code:
    WITH T (ORDINAL , INDEX) AS (
    SELECT 0 , 0 FROM SYSIBM.SYSDUMMY1
    UNION ALL
    SELECT ORDINAL + 1
         , COALESCE(
              NULLIF(
                 LOCATE (',' , '290,291' , INDEX + 1)
               , 0
              )
            , LENGTH('290,291') + 1
           )
     FROM  T
     WHERE INDEX   < length('290,291')
       AND ORDINAL < 10000
    )
    SELECT COUNT(*)     AS count_rows
         , MAX(ORDINAL) AS loops
     FROM  T
    ;

Posting Permissions

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