I am use these recursive stmt, but it is given warning message and don't shows any output in unix(putty).

CREATE TABLE rec
(
snum INTEGER, -- sentence number
wordnum INTEGER, -- word number in the sentence
word VARCHAR(100) -- word being saved
);

INSERT INTO rec
VALUES (1, 1, 'This'), (1, 2, 'is'), (1, 3, 'a'), (1, 4, 'fine'), (1, 5, 'example');

INSERT INTO rec
VALUES (2, 1, 'This'), (2, 2, 'is'), (2, 3, 'another'), (2, 4, 'example');

INSERT INTO rec
VALUES (3, 1, 'This'), (3, 2, 'example '), (3, 3, 'shows'), (3, 4, 'what'),
(3, 5, 'can'), (3, 6, 'happen'), (3, 7, 'when'), (3, 8, 'the'),
(3, 9, 'data'), (3, 10, 'represents'), (3, 11, 'a'), (3, 12, 'really'),
(3, 13, 'long'), (3, 14, 'line'), (3, 15, 'that'), (3, 16, 'overflows'),
(3, 17, 'the'), (3, 18, 'length'), (3, 19, 'of'), (3, 20, 'an'),
(3, 21, 'object');


WITH rquery (snum, wordnum, sentence)
AS
(
SELECT base.snum, base.wordnum, CAST(base.word AS VARCHAR (2000))
FROM rec base
WHERE wordnum = 1

UNION ALL

SELECT t1.snum, t1.wordnum, sentence || ' ' || t1.word
FROM rquery t0, rec t1
WHERE t0. snum = t1. snum
AND t0.wordnum + 1 = t1.wordnum
)
SELECT *
FROM rquery rq
WHERE rq.wordnum = (SELECT max(wordnum) FROM rquery WHERE snum = rq.snum);

How it should be corrected, or any other way is there to get same output

Thanks in advance

The missing output is unrelated to the warning. I don't know how you execute the query but if the tool doesn't recognize warnings as warnings but treats them as errors, your query tools has a bug. Period.

If you want to get rid of the warning, you can introduce a counter to limit the number of recursive steps. For example, "k" is doing this here: Sqlcode = 347, warning: The recursive common table expression

Thanks stolze

The reason of the warning was described clearly by stolze.

So, you can use wordnum to suppress the warning, like...
SELECT t1.snum, /*t1.wordnum*/t0.wordnum + 1, sentence || ' ' || t1.word
FROM rquery t0, rec t1
WHERE t0. snum = t1. snum
AND t0.wordnum + 1 = t1.wordnum
AND t0.wordnum < 100000 /* add */

You are using DB2 in Unix.
String concatenation can be done easily on DB2 9.7.4 or later on LUW.

For example.
Note: datatype of output sentence would be VARCHAR(4000).

thanks tonkuma, for your information.

I am new to db2/unix, Please can u provide the link where we can learn more about the same.

DB2 SQL cook book,

DB2 SQL Cookbook

and Information Center

IBM DB2 9.7 for Linux, UNIX and Windows Information Center