DB2 iSeries parse string

mmeng · #1 (**permalink**) 10-23-08, 11:51

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!

tonkuma · #2 (**permalink**) 10-23-08, 15:57

What　Version/Release of DB2 are you using?
It seems that recursive query can be specified on DB2 V5R4 or later from manuals.

mmeng · #3 (**permalink**) 10-23-08, 16:00

DB2 V5R4 on an i-Series

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

tonkuma · #4 (**permalink**) 10-23-08, 17:24

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.

mmeng · #5 (**permalink**) 10-23-08, 18:11

Thanks for the replies!

@tonkuma

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

Quote:

[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?

mmeng · #6 (**permalink**) 10-23-08, 18:22

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.

tonkuma · #7 (**permalink**) 10-23-08, 20:24

Change
VALUES CAST('12345678,87654321,12348765,56784321' AS CLOB(1k))
to
SELECT CAST('12345678,87654321,12348765,56784321' AS CLOB(1k)) FROM SYSIBM.SYSDUMMY1

Internet Services

Web Development

Digital Marketing

Consumer Tech