FYI: DB2 9.7.4 has been released with new SQL function

[Serge Rielau]

https://www-304.ibm.com/support/docview.wss?uid=swg24029745

Some interesting SQL Enhancements:
* Pass ROWs and ARRAY's of ROWs between
JDBC and SQL Procedures and Functions
* LIKE with non constant patterns (e.g. LIKE as a "join predicate")
* Multi-Action triggers (One trigger for UPDATE/INSERT/DELETE)
* Allow UPDATE/DELETE/INSERT/MERGE in BEFORE triggers

Enjoy
Serge
--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau

[The Boss]

On 19 apr, 22:31, Serge Rielau <srie...@ca.ibm.com> wrote:
> https://www-304.ibm.com/support/docview.wss?uid=swg24029745
>
> Some interesting SQL Enhancements:
> * Pass ROWs and ARRAY's of ROWs between
>    JDBC and SQL Procedures and Functions
> * LIKE with non constant patterns (e.g. LIKE as a "join predicate")
> * Multi-Action triggers (One trigger for UPDATE/INSERT/DELETE)
> * Allow UPDATE/DELETE/INSERT/MERGE in BEFORE triggers
>
> Enjoy
> Serge

Sounds like it's time for a new sequel in the "SQL on Fire!"
series... ;-)

--
Jeroen

[Serge Rielau]

Hehe, hard to keep up.
Prepping a talk about the "SQL PL Profiler" (see blog) for IMTC
Bangalore in two weeks.

Cheers

[Frederik Engelen]

"The LISTAGG function aggregates a set of string values for the group
into one string by appending the string-expression values based on the
order specified in the WITHIN GROUP clause."

So no more feeling special because you know how to do it with
XMLAGG? :-(

Joking, nice release...

--
Frederik Engelen

[Fin]

Will there be a 9.7.4 release for Express-C anytime soon ?

[Serge Rielau]

On 4/20/2011 6:36 PM, Fin wrote:
> Will there be a 9.7.4 release for Express-C anytime soon ?

Totally forgot about LISTAGG!
Yes, my job is to make myself unnecessary ;-)

For DB2 Express C FP4 I propose asking the question in the Express C
forum. I'd be surprised if there were no refresh.

[Lennart Jonsson]

On 2011-04-21 00:36, Fin wrote:
> Will there be a 9.7.4 release for Express-C anytime soon ?

9.7.4 for is now availible for download

/Lennart

[Tonkuma]

Here is an example of LISTAGG(without optional WITHIN GROUP).

http://www.dbforums.com/db2/1666067-how-get-change-ip-middle-session.html

[Fin]

I have updated from Express-C 9.7.2 to 9.7.4 and am trying this new
LISTAGG function.

During installation it created the a new SAMPLE database which worked
just fine and the sample LISTAGG command from the info center works
just great. Problem is however that trying the same command (slightly
different syntax of course) in my existing database (ex 9.7.2) fails
with :

SQL0440N No authorized routine named "LISTAGG" of type "FUNCTION"
having
compatible arguments was found. SQLSTATE=42884

Is there some upgrade command, access to grant, permission to set,
bind to perform or something similar I am missing in order to get this
new function working on my existing database ?

[Helmut Tessarek]

After apply

On 05/03/2011 11:00 AM, Fin wrote:
> Is there some upgrade command, access to grant, permission to set,
> bind to perform or something similar I am missing in order to get this
> new function working on my existing database ?

After applying a fixpak, you usually use the following steps to upgrade
the db.

db2updv9 -d <dbname>

db2 BIND ~/sqllib/bnd/@db2ubind.lst GRANT PUBLIC BLOCKING ALL
db2 BIND ~/sqllib/bnd/@db2cli.lst GRANT PUBLIC BLOCKING ALL
db2 BIND ~/sqllib/bnd/db2schema.bnd BLOCKING ALL GRANT PUBLIC sqlerror
continue

Try those steps and your LISTAGG should be available.

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

[Fin]

Many thanks Helmut,

That did the trick, problem solved.

Cheers, Fin.

[Serge Rielau]

Background:
Whenever a new built-in function is added to DB2 it gets a version-stamp.
This is to avoid overriding a pre-existing user defined function of the
same name which may be an equally good match.
By running db2upd you move the version stamp of the DB forward which
makes the new function visible.

[AD]

Why LISTAGG doesn't support distinct values ex. listagg(distinct
COLUMN,',')? Such statement executes but doesn't work. Maybe in future
release?

[Serge Rielau]

Interesting. Can you open a PMR? I'll take a look. If it's possible to
support (off hand I don't see why not), I'll fix it, otherwise I'll need
to block it properly.
Ignoring the keyword is definitely wrong *eggontheface*.

Refer support to me directly.

[Tonkuma]

A workaround may be LAG and NULLIF, like...

------------------------------ Commands Entered
------------------------------
SELECT workdept
, COUNT(*) AS members
, SUBSTR(
LISTAGG( VARCHAR( NULLIF(edlevel , lag_edlevel) ) , ' ' )
WITHIN GROUP(ORDER BY edlevel)
, 1 , 20 ) AS distinct_edlevel
FROM (SELECT e.*
, LAG(edlevel)
OVER(PARTITION BY workdept
ORDER BY edlevel) AS lag_edlevel
FROM employee e
) e
GROUP BY
workdept
;
------------------------------------------------------------------------------

WORKDEPT MEMBERS DISTINCT_EDLEVEL
-------- ----------- --------------------
A00 3 14 18 19
B01 1 18
C01 3 16 18 20
D11 9 16 17 18
D21 6 14 15 16 17
E01 1 16
E11 5 12 14 16 17
E21 4 14 16

8 record(s) selected.

[AD]

> Interesting. Can you open a PMR? I'll take a look. If it's possible to
> support (off hand I don't see why not), I'll fix it, otherwise I'll need
> to block it properly.
> Ignoring the keyword is definitely wrong *eggontheface*.

I'm quite new to DB2 and using Express-C only so I don't know how to
open PMR (which seems to be available to customers only).

[Serge Rielau]

IC, no problem.

[pae...@gmail.com]

Since users still find this post right on top when searching for Db2 LISTAGG DISTINCT I wanted to let you know that at least with DB2 10.1 it works fine as one would expect it to work.