Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2008
    Posts
    4

    Unanswered: DB2 iSeries parse string

    Hey, I'm new to the forums! I'm working on a project for my company that requires me to be able to parse a string containing comma (,) separated values using a stored procedure. I'm brand baby-butt-spankin' new to db2 and have found out that we're working on an i-Series machine.

    Problem:

    The input will be a CLOB of 1K (because of specific requirements on the procedure) and a simple table of values that holds item numbers will be returned. For example:

    Parameter: '12345678,87654321,12348765,56784321'

    Returns:
    |itemID|
    |12345678|
    |87654321|
    |12348765|
    |56784321|

    So far I haven't gotten anything remotely working because it seems that DB2 runs differently on the i-Series and uses different tokens and modifiers. So if there are any i-Series experts out there, I would appreciate it if you could lend me a hand.

    I found several examples of creating a parse string procedure on other machines, but I can't get the same methods to work on the i-Series. So far I've found that recursion doesn't work (my original idea was do just recurse through the string, hacking it up each time), and I can't get global temporary tables to work for the life of me (I thought that I could build the temp table and store the actual values of the string in each row).

    If anyone can help out, I would really appreciate it!
    Last edited by mmeng; 10-23-08 at 14:41.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What Version/Release of DB2 are you using?
    It seems that recursive query can be specified on DB2 V5R4 or later from manuals.

  3. #3
    Join Date
    Oct 2008
    Posts
    4
    DB2 V5R4 on an i-Series

    How would you write a recursive statement for the problem described above?

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Changed:
    from: AND LENGTH(item_numbers) > 1
    to: AND LENGTH(item_numbers) > 0

    Tested on DB2 for LUW V9R1.

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH 
     testdata(item_numbers) AS (
    VALUES CAST('12345678,87654321,12348765,56784321' AS CLOB(1k))
    )
    ,recursive_cte(k, itemID, item_numbers) AS (
    SELECT 0
         , CAST('' AS VARCHAR(10))
         , item_numbers||','
      FROM testdata
    UNION ALL
    SELECT k
         , CAST(SUBSTR(item_numbers, 1, n-1) AS VARCHAR(10))
         , SUBSTR(item_numbers, n+1)
      FROM (SELECT k + 1                     AS k
                 , LOCATE(',', item_numbers) AS n
                 , item_numbers
              FROM recursive_cte
             WHERE k < 1000000
               AND LENGTH(item_numbers) > 0
           ) S
    )
    SELECT itemID AS "itemID"
      FROM recursive_cte
     WHERE k > 0
    ;
    ------------------------------------------------------------------------------
    
    itemID    
    ----------
    12345678  
    87654321  
    12348765  
    56784321  
    
      4 record(s) selected.
    Last edited by tonkuma; 10-23-08 at 18:29.

  5. #5
    Join Date
    Oct 2008
    Posts
    4
    Thanks for the replies!

    @tonkuma

    So when I try to run that code I get the following error:

    [SQL0199] Keyword VALUES not expected. Valid tokens: ( SELECT. Cause . . . . . : The keyword VALUES was not expected here. A syntax error was detected at keyword VALUES. The partial list of valid tokens is ( SELECT. This list assumes that the statement is correct up to the unexpected keyword. The error may be earlier in the statement but the syntax of the statement seems to be valid up to this point. Recovery . . . : Examine the SQL statement in the area of the specified keyword. A colon or SQL delimiter may be missing. SQL requires reserved words to be delimited when they are used as a name. Correct the SQL statement and try the request again.

    Processing ended because the highlighted statement did not complete successfully
    This is a problem I've been having and, being new to DB2 and the i-Series constraints, I'm not sure how to get around this. It doesn't expect a VALUES token and freaks out when I use it. Any ideas on how to overcome this?

  6. #6
    Join Date
    Oct 2008
    Posts
    4
    I just noticed that you ran that code again LUW. There are some potential differences between the i-Series and LUW. It seems as though the i-Series is limited in its capabilities. But, of course, these are the constraints of my project.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Change
    VALUES CAST('12345678,87654321,12348765,56784321' AS CLOB(1k))
    to
    SELECT CAST('12345678,87654321,12348765,56784321' AS CLOB(1k)) FROM SYSIBM.SYSDUMMY1

Posting Permissions

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