Prev: How to "LOAD COPY NO" without backup ?
Next: System Time and Utilities Start/Stop Time
From: Tonkuma on 21 Nov 2008 21:21
> Yeah, u are true, It was necessary to use a xml2clob in order to get
> the result on Db2 9.1 for LUW.
>
> But using SELECT XMLAGG(XMLELEMENT(NAME a, NAME)) from
> TECPG01.TEST1111 on Db2 9.1 for z/OS  I was able to get the same
> result as in using XML2CLOB with above for Db2 LUW.
I got same result by using Command Editor on DB2 LUW 9.1 and 9.5
without using XML2CLOB function.
But, the data type must be XML and I guessed that Command Editor
converted the result into displayable format.
Because, the statement "SELECT SUBSTR(XMLAGG(XMLELEMENT(NAME a, NAME)),
1,100) from Colors;", returned error message SQL0440N.
It is neccesary XML data to cast to string(I did it by using XML2CLOB)
to use the XML data as a string argument of DB2 functions like SUBSTR,
REPLACE, so on.

>
> I went over the syntax of Replace. replace function accepts only
> expressions as arguments in Db2 for LUW
> but Replace functions accepts only string aruguments in db2 for z/OS.
>
REPLACE on DB2 for LUW is almost same as REPLACE on DB2 for z/OS.

If you read the description of the function on SQL Reference of DB2
for LUW,
you can see:
The first argument can be of any built-in character string or binary
string type.
<snipped>
The type of the second and third arguments is identical to that of the
first argument.

> Does this anyway is effecting what I wanted to achieve.
>
> If yes, How can I convert an expression to a string argument.
As far as I know, there's no way to convert XML data to string on DB2
for z/OS by using bult-in functions.

From: mark83anthony on 24 Nov 2008 10:54
On Nov 21, 9:21 pm, Tonkuma <tonk...(a)fiberbit.net> wrote:
> > Yeah, u are true, It was necessary to use a xml2clob in order to get
> > the result on Db2 9.1 for LUW.
>
> > But using SELECT XMLAGG(XMLELEMENT(NAME a, NAME)) from
> > TECPG01.TEST1111 on Db2 9.1 for z/OS  I was able to get the same
> > result as in using XML2CLOB with above for Db2 LUW.
>
> I got same result by using Command Editor on DB2 LUW 9.1 and 9.5
> without using XML2CLOB function.
> But, the data type must be XML and I guessed that Command Editor
> converted the result into displayable format.
> Because, the statement "SELECT SUBSTR(XMLAGG(XMLELEMENT(NAME a, NAME)),
> 1,100) from Colors;", returned error message SQL0440N.
> It is neccesary XML data to cast to string(I did it by using XML2CLOB)
> to use the XML data as a string argument of DB2 functions like SUBSTR,
> REPLACE, so on.
>
>
>
> > I went over the syntax of Replace. replace function accepts only
> > expressions as arguments in Db2 for LUW
> > but Replace functions accepts only string aruguments in db2 for z/OS.
>
> REPLACE on DB2 for LUW is almost same as REPLACE on DB2 for z/OS.
>
> If you read the description of the function on SQL Reference of DB2
> for LUW,
> you can see:
> The first argument can be of any built-in character string or binary
> string type.
> <snipped>
> The type of the second and third arguments is identical to that of the
> first argument.
>
> > Does this anyway is effecting what I wanted to achieve.
>
> > If yes, How can I convert an expression to a string argument.
>
> As far as I know, there's no way to convert XML data to string on DB2
> for z/OS by using bult-in functions.

Hi Tonkuma,

Thanks so much for replying.

I am a DB2 AIX guy, so fairly new to z/os.

I used recursive SQL to produce what i wanted to.

Below is the SQL.



WITH temp1(NAME, all_words, cnt) as
(SELECT min(NAME), VARCHAR(min(NAME),2000),SMALLINT(1) FROM
TECPG01.TEST1111 a
UNION ALL
SELECT a.NAME, b.all_words||','||a.NAME,SMALLINT(b.cnt+1)
FROM TECPG01.TEST1111 a, temp1 b
WHERE a.NAME > b.NAME
AND a.NAME = (select min(c.NAME) from TECPG01.TEST1111 c where c.NAME
> b.NAME)
)
SELECT all_words FROM temp1 d
where cnt = (SELECT max(cnt) FROM temp1)

Now, I decided to write a function and ask users to call this function
to concatenate multiple rows to a string.

How do I write a function using Recursive SQL for this,

Does DB2 for z OS support Recursive SQL.

I would be very thankful to you if you get me this function for me to
get going.

Thanks in Advance again.
From: mark83anthony on 24 Nov 2008 15:47
On Nov 24, 10:54 am, mark83anth...(a)gmail.com wrote:
> On Nov 21, 9:21 pm, Tonkuma <tonk...(a)fiberbit.net> wrote:
>
>
>
>
>
> > > Yeah, u are true, It was necessary to use a xml2clob in order to get
> > > the result on Db2 9.1 for LUW.
>
> > > But using SELECT XMLAGG(XMLELEMENT(NAME a, NAME)) from
> > > TECPG01.TEST1111 on Db2 9.1 for z/OS  I was able to get the same
> > > result as in using XML2CLOB with above for Db2 LUW.
>
> > I got same result by using Command Editor on DB2 LUW 9.1 and 9.5
> > without using XML2CLOB function.
> > But, the data type must be XML and I guessed that Command Editor
> > converted the result into displayable format.
> > Because, the statement "SELECT SUBSTR(XMLAGG(XMLELEMENT(NAME a, NAME)),
> > 1,100) from Colors;", returned error message SQL0440N.
> > It is neccesary XML data to cast to string(I did it by using XML2CLOB)
> > to use the XML data as a string argument of DB2 functions like SUBSTR,
> > REPLACE, so on.
>
> > > I went over the syntax of Replace. replace function accepts only
> > > expressions as arguments in Db2 for LUW
> > > but Replace functions accepts only string aruguments in db2 for z/OS.
>
> > REPLACE on DB2 for LUW is almost same as REPLACE on DB2 for z/OS.
>
> > If you read the description of the function on SQL Reference of DB2
> > for LUW,
> > you can see:
> > The first argument can be of any built-in character string or binary
> > string type.
> > <snipped>
> > The type of the second and third arguments is identical to that of the
> > first argument.
>
> > > Does this anyway is effecting what I wanted to achieve.
>
> > > If yes, How can I convert an expression to a string argument.
>
> > As far as I know, there's no way to convert XML data to string on DB2
> > for z/OS by using bult-in functions.
>
> Hi Tonkuma,
>
> Thanks so much for replying.
>
> I am a DB2 AIX guy, so fairly new to z/os.
>
> I used recursive SQL to produce what i wanted to.
>
> Below is the SQL.
>
> WITH temp1(NAME, all_words, cnt) as
> (SELECT min(NAME), VARCHAR(min(NAME),2000),SMALLINT(1) FROM
> TECPG01.TEST1111 a
> UNION ALL
> SELECT a.NAME, b.all_words||','||a.NAME,SMALLINT(b.cnt+1)
> FROM TECPG01.TEST1111 a, temp1 b
> WHERE a.NAME > b.NAME
> AND a.NAME = (select min(c.NAME) from TECPG01.TEST1111 c where c.NAME> b.NAME)
>
> )
> SELECT all_words FROM temp1 d
> where cnt = (SELECT max(cnt) FROM temp1)
>
> Now, I decided to write a function and ask users to call this function
> to concatenate multiple rows to a string.
>
> How do I write a function using Recursive SQL for this,
>
> Does DB2 for z OS support Recursive SQL.
>
> I would be very thankful to you if you get me this function for me to
> get going.
>
> Thanks in Advance again.- Hide quoted text -
>
> - Show quoted text -

Or else,

Can i get an equivalent function/ stored procedure of the below
function in DB2 for z os.


CREATE FUNCTION get_str()
LANGUAGE SQL
RETURNS VARCHAR(1024)
BEGIN ATOMIC
DECLARE str VARCHAR(1024);
SET str = '';
loop1: FOR row AS (SELECT Name FROM TECPG01.TEST1111)
DO
IF row.Name IS NOT NULL THEN
SET str = str || row.Name || '';
END IF;
END FOR loop1;
RETURN str;
END@
First  |  Prev  | 
Pages: 1 2 3
Prev: How to "LOAD COPY NO" without backup ?
Next: System Time and Utilities Start/Stop Time