v8.1.4 new features

[PM \(pm3iinc-nospam\)]

For those interested in new features,
Suggested reading...

http://publib.boulder.ibm.com/infoce...help/index.jsp
Release info.
v8.1.4
app.dev.enhence ments
sql data change operations

Result set retrieval from SQL data-change operations

ex:
SELECT * FROM FINAL TABLE (INSERT ...)

You should also check the include columns page.
In UPDATE statements, you can use include columns to return both the old
and new column values for a row.

SELECT salary, oldSalary FROM FINAL TABLE
(UPDATE employee INCLUDE (oldSalary decimal(9,2))
SET oldSalary = salary, salary = salary * 1.05
WHERE job = 'CLERK')
PM

[Serge Rielau]

There is a small change in CREATE FUNCTION (SQL Scalara, Row, Table):
MODIFIES SQL DATA is allowed for SQL Table functions.
In a nutshell you can execute inline SQL PL in a join, rather than
coding a cursor if the loop is simple enough to be handled by inline SQL
PL. Another usage of it are "select triggers" which have been requested
a few times in this group.

Cheers
Serge

PS: I would very much like to hear feedback on the FP4 SQL Features,
both from a usability and performance point of view.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Paul Reddin]

Serge,

Is it possible to create a table (or GLOBAL TEMPORARY) within a
'MODIFIES SQL DATA' table funcion?

i.e we would like to build a result table in the UDF and then
return the contents.

It seems to work fine if we pre-create the table before the UDF
is called, but we would prefer to handle the temp. table within the
UDF?

Thanks.

Paul.

Serge Rielau <sr*****@ca.e ye-bee-m.com> wrote in message news:<br******* ***@hanover.tor olab.ibm.com>.. .

There is a small change in CREATE FUNCTION (SQL Scalara, Row, Table):
MODIFIES SQL DATA is allowed for SQL Table functions.

[Serge Rielau]

No you can't. There are two angles of attack on this in some future:
Introduce LOCAL temporary tables.
IMHO creating and modifying a LOCAL temporary table wouldn't even count
as MODIFIES SQL DATA.
Or introduce DB2 z/Series style CREATED GLOBAL temporary tables.
The latter means the temp is defined in the catalogs (and hence the UDF
can record a dependency on it) but the content is private to teh session.

Until any of these happen you will need to use a persistent table and
privatize it using a where clause and e.g. generate_unique ().

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Bernard Dhooghe]

Serge Rielau <sr*****@ca.e ye-bee-m.com> wrote in message news:<br******* ***@hanover.tor olab.ibm.com>.. .

PS: I would very much like to hear feedback on the FP4 SQL

Features, both from a usability and performance point of view.

We appreciate DB2 enhancements in general so there is no reason to
criticize improvements.

I must still deplore that:

- a rollforward pending database can not be accessed in read-only
mode

- there is no support for scrollable cursors in embedded languages

- row value constructors (the capabilities of SQL92) are only
partially implemented

(see for 2 and 3:

http://parallel.rz.uni-mannheim.de/m...c/td000005.htm
)

Bernard Dhooghe

[Mark Yudkin]

"Serge Rielau" <sr*****@ca.e ye-bee-m.com> wrote in message
news:br******** **@hanover.toro lab.ibm.com...

PS: I would very much like to hear feedback on the FP4 SQL Features,
both from a usability and performance point of view.

I know this isn't quite what you want to hear, but I have the feeling that
FP4 received far too little testing. For example we had to open a PMR
against bad query results (UNION / DISTINCT), it became a HIPER APAR and
we've now received a hotfix. The defect was introduced with FP4. We have
other problems too (increase of unexplained sqlcode=-911 rc=2 that appear to
result from a failure to release locks), but this was also in FP3.

So, to increase usability I'd like to see new DB2 features get more
exhaustive testing to assure they don't break things.

[Bill Chen]

Serge Rielau <sr*****@ca.e ye-bee-m.com> wrote in message news:<bs******* ***@hanover.tor olab.ibm.com>.. .

Not much I can say about that. It's a well known problem that testing
can never we enough.

Hi Serge,

We are using DB2 V7.2 and going to move to DB2 V8.1.4 soon.
Could please you tell me what wrong on (UNION/DISTINCT) on V8.1.4?
Where we can get the HOTFIX? (from IBM site, URL please)

Thanks,

Bill

[Serge Rielau]

I don't know Mark's problem with UA.
Maybe he can post the APAR#.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Mark Yudkin]

You get wrong answers from queries (extra rows that don't meet the
conditions) - it was a no-go for us.

PMR 10781,211,848. Hotfix 9272. Don't know the APAR number as I am the
programmer, not the DB2 sysadm who relayed with IBM support.

"Bill Chen" <ch********@yah oo.com> wrote in message
news:32******** *************** ***@posting.goo gle.com...

Serge Rielau <sr*****@ca.e ye-bee-m.com> wrote in message

news:<bs******* ***@hanover.tor olab.ibm.com>.. .

Not much I can say about that. It's a well known problem that testing
can never we enough.

Hi Serge,

We are using DB2 V7.2 and going to move to DB2 V8.1.4 soon.
Could please you tell me what wrong on (UNION/DISTINCT) on V8.1.4?
Where we can get the HOTFIX? (from IBM site, URL please)

Thanks,

Bill

[Mark Yudkin]

I think you've missed my point.

Fixpaks are supposed to be a vehicle for delivering fixes for problems. By
altering it to a vehicle for adding new functionality, you increase the risk
that - by adding some new features - you introduce new problems. In
particular, 8.1.4 (as per your post requesting feedback thereon) did just
that - it broke basic query processing that worked previously (PMR
10781,211,848. Hotfix 9272).

Accordingly my feedback is that you would be best off leaving fixpaks as a
means of delivering fixes and not as a means of enhancing functionality.
This is especially so given the frequency of fixpaks and the consequent
limited testing new features receive and consequent large risk of
introducing new defects.

As a user, I am used to performing exhaustive testing with new product
versions. I do not expect to have to do this for fixpaks, in particular as
the standard initial PMR response is "install the latest fixpak and see if
that fixes your problem".

We stumbled over this bug "by accident" - it was only by luck that we
weren't in production. The DB2 team wanted to go live with 8.1.4 (based
mostly on our significant 8.1.3 testing), and I was the one who insisted on
more testing time. Although outnumbered, it was finally accepted that if
there was a problem, I was going to be the one yelled at by the users, so
that if I wanted more testing time, I could have it. And what I was worried
about was actually a spurious SQLCode -911. I wasn't expecting to find that
simple queries gave bad results.

So not only do you need more testing (we agree on that), but you should stop
using FIXpaks as a means of adding new features. Leave them to do what they
say the do - bundles FIXes.

"Serge Rielau" <sr*****@ca.e ye-bee-m.com> wrote in message
news:bs******** **@hanover.toro lab.ibm.com...

Not much I can say about that. It's a well known problem that testing
can never we enough.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab