I agree with Damien,

If you go from 2.6 to 2006, properly test the optimizer.
The behavior has changed quite a bit and some queries may not behave as before.
We had many 'canned' SQL statements that relied on sort elimination and the new optimizer took a different approach.
Some SQL 'tweaking' had to be done to make them run like they used to.

Andre


________________________________

From: info-ingres-bounces (AT) kettleriver...ting (DOT) com [mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Owen, Damien
Sent: March 17, 2009 5:55 AM
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] Upgrading from Ingres 2.6 to Ingres 2006- version9.2


We moved to 9.1.1 on Solaris 10 October 2008. Since then we've encountered various different bugs and had to deploy 3 patches. We've recently discovered another bug that will require a service pack and another patch. Many of the issues we've faced have been to do with the optimizer and query performance. In some instances queries that worked fine in 2.6 just don't work in 2006. They either hang in the QEF or fill the work location and roll back. 90% of the query performance issues we' ve had were a direct result of a new bug. Some queries just required a little re-working or a new index and they ran OK. In the most recent issue we've discovered a UNION of two queries is returning way more rows than it should do.

Regards

Damien Owen

Sandyx Systems
www.sandyxsystems.co.uk <http://www.sandyxsystems.co.uk>



----- Original Message -----
From: Robert Allely <mailto:allelyr (AT) poal (DOT) co.nz>
To: Ingres and related product discussion forum <mailto:info-ingres (AT) kettleriverconsulting (DOT) com>
Sent: Tuesday, March 17, 2009 2:01 AM
Subject: [Info-Ingres] Upgrading from Ingres 2.6 to Ingres 2006- version 9.2


We have finally decided to move to the new version of the dbms- does anyone think Ingres 9.2 is too new to move to? How long has it been out there?

Thanks,
Robert



Confidentiality/Privilege Notice:
This communication is confidential and may be legally privileged. If you are not the intended recipient please delete the message and notify the sender at Ports of Auckland Limited. Any use, disclosure, copying, distribution or retention of this communication is strictly prohibited.


________________________________




_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres




________________________________

-------------------------------------------------------------------------------------------
This email and the files transmitted with it are meant solely for the use of the individual addressee named above. They may contain confidential and/or legally privileged information. If you are not the addressee or responsible for delivery of the message to the addressee, please delete it from your system and contact the sender right away. Sandyx Systems Ltd has taken steps to ensure that this email and any attachments are virus-free, but it remains your responsibility to confirm and ensure this.

Does anybody know if the defects mentioned by Damien are resolved in the
latest Ingres Version

Regards


Gregory Wilding

Customer Solutions IT
ING Australia
Level 6, 347 Kent Street, SYDNEY. 2000

T (02) 9234 7435
F (02) 9262 2246
E greg.wilding (AT) ing (DOT) com.au



________________________________

From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Owen, Damien
Sent: Tuesday, 17 March 2009 8:55 PM
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] Upgrading from Ingres 2.6 to Ingres 2006-
version9.2


We moved to 9.1.1 on Solaris 10 October 2008. Since then we've
encountered various different bugs and had to deploy 3 patches. We've
recently discovered another bug that will require a service pack and
another patch. Many of the issues we've faced have been to do with the
optimizer and query performance. In some instances queries that worked
fine in 2.6 just don't work in 2006. They either hang in the QEF or fill
the work location and roll back. 90% of the query performance issues we'
ve had were a direct result of a new bug. Some queries just required a
little re-working or a new index and they ran OK. In the most recent
issue we've discovered a UNION of two queries is returning way more rows
than it should do.

hoping that these issues are nothing more than glitches that are ironed
out as the maturity of the release grows but, I'm yet to see any
evidence to suggest that's the case.

Regards

Damien Owen

Sandyx Systems
www.sandyxsystems.co.uk <http://www.sandyxsystems.co.uk>



----- Original Message -----
From: Robert Allely <mailto:allelyr (AT) poal (DOT) co.nz>
To: Ingres and related product discussion forum
<mailto:info-ingres (AT) kettleriverconsulting (DOT) com>
Sent: Tuesday, March 17, 2009 2:01 AM
Subject: [Info-Ingres] Upgrading from Ingres 2.6 to Ingres 2006-
version 9.2


We have finally decided to move to the new version of the dbms-
does anyone think Ingres 9.2 is too new to move to? How long has it been
out there?

Thanks,
Robert



Confidentiality/Privilege Notice:
This communication is confidential and may be legally
privileged. If you are not the intended recipient please delete the
message and notify the sender at Ports of Auckland Limited. Any use,
disclosure, copying, distribution or retention of this communication is
strictly prohibited.


________________________________




_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com

http://www.kettleriverconsulting.com...fo/info-ingres




________________________________

------------------------------------------------------------------------
-------------------
This email and the files transmitted with it are meant solely for the
use of the individual addressee named above. They may contain
confidential and/or legally privileged information. If you are not the
addressee or responsible for delivery of the message to the addressee,
please delete it from your system and contact the sender right away.
Sandyx Systems Ltd has taken steps to ensure that this email and any
attachments are virus-free, but it remains your responsibility to
confirm and ensure this.


Important notice
This communication, including any file attachments, is intended solely for the use of the individual or entity to whom it is addressed. If you are notthe intended recipient, or the person responsible for delivering this communication to the intended recipient, please immediately notify the sender by email and delete the original transmission and its contents. Any unauthorised use, dissemination, forwarding, printing, or copying of this communication including any file attachments is prohibited.
It is your responsibility to scan this communication including any file attachment for viruses and other defects. To the extent permitted by law, ING Australia Limited and its associates will not be liable for any loss or damage arising in any way from this communication including file attachments.

Greg Wilding wrote:

I guess only Damien would know the answer to that, but he was talking
about going from 2.6 to 9.1.1. There have been two releases of Ingres
since 9.1.1.

My experience of going from 2.6 to 9.1.1 at a large Irish Government
department was that there were a few issues, but the only one that
required a patch was a non-critical replication bug. The patch was
produced very quickly. (I should mention that they had first attempted
to upgrade from 2.6 to 9.1.0 but that proved to have a problem with
consistency points. Since 9.1.1 was already available and had fixed the
problem, the decision was made to go direct to 9.1.1 instead. It has
been in production there without major incidents ever since.)

It is worth pointing out that 9.1.x incorporates a large number of
enhancements over 2.6, including radical changes to the optimizer
including (for example) greedy enumeration to speed up the generation of
plans for queries with large numbers of tables, and to raise the number
of tables allowed in a query. There were also radical changes to add
various kinds of parallelism. All disruptive but worthwhile stuff.
It is not surprising that there would be some problems as a result.
That is why one does application regression testing on major new
releases.

The question is, has Ingres Corp promptly produced patches and fixes for
any problems, and so far my answer is yes, they've been very
responsive. YMMV, but I can think of no good reason not to at least
start regression testing with 9.2 with the intention of going live with
it. Just be realisitic and expect to have to work a few issues.

--
Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.

Greg,

If the question is "Is bug xxxxxx fixed?", I can answer that question
quite easily, if I have the xxxxxx.

Damien has not been specific and I am unable to relate what he has
written to any support issues.



In general, I would encourage clients with support contracts to open
issues with Ingres to have specific questions resolved.



Michael Dyer



________________________________

From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of Greg
Wilding
Sent: 18 March 2009 05:00
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] Upgrading from Ingres 2.6 to Ingres 2006-
version9.2



Does anybody know if the defects mentioned by Damien are resolved in the
latest Ingres Version



Regards



Gregory Wilding

Customer Solutions IT
ING Australia
Level 6, 347 Kent Street, SYDNEY. 2000

T (02) 9234 7435
F (02) 9262 2246
E greg.wilding (AT) ing (DOT) com.au





________________________________

From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Owen, Damien
Sent: Tuesday, 17 March 2009 8:55 PM
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] Upgrading from Ingres 2.6 to Ingres 2006-
version9.2

We moved to 9.1.1 on Solaris 10 October 2008. Since then we've
encountered various different bugs and had to deploy 3 patches. We've
recently discovered another bug that will require a service pack and
another patch. Many of the issues we've faced have been to do with the
optimizer and query performance. In some instances queries that worked
fine in 2.6 just don't work in 2006. They either hang in the QEF or fill
the work location and roll back. 90% of the query performance issues we'
ve had were a direct result of a new bug. Some queries just required a
little re-working or a new index and they ran OK. In the most recent
issue we've discovered a UNION of two queries is returning way more rows
than it should do.



hoping that these issues are nothing more than glitches that are ironed
out as the maturity of the release grows but, I'm yet to see any
evidence to suggest that's the case.



Regards



Damien Owen


Sandyx Systems
www.sandyxsystems.co.uk <http://www.sandyxsystems.co.uk>

----- Original Message -----

From: Robert Allely <mailto:allelyr (AT) poal (DOT) co.nz>

To: Ingres and related product discussion forum
<mailto:info-ingres (AT) kettleriverconsulting (DOT) com>

Sent: Tuesday, March 17, 2009 2:01 AM

Subject: [Info-Ingres] Upgrading from Ingres 2.6 to Ingres 2006-
version 9.2



We have finally decided to move to the new version of the dbms-
does anyone think Ingres 9.2 is too new to move to? How long has it been
out there?

Thanks,
Robert



Confidentiality/Privilege Notice:
This communication is confidential and may be legally
privileged. If you are not the intended recipient please delete the
message and notify the sender at Ports of Auckland Limited. Any use,
disclosure, copying, distribution or retention of this communication is
strictly prohibited.


________________________________


_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com

http://www.kettleriverconsulting.com...fo/info-ingres



________________________________

------------------------------------------------------------------------
-------------------
This email and the files transmitted with it are meant solely for the
use of the individual addressee named above. They may contain
confidential and/or legally privileged information. If you are not the
addressee or responsible for delivery of the message to the addressee,
please delete it from your system and contact the sender right away.
Sandyx Systems Ltd has taken steps to ensure that this email and any
attachments are virus-free, but it remains your responsibility to
confirm and ensure this.

Important notice

This communication, including any file attachments, is intended solely
for the use of the individual or entity to whom it is addressed. If you
are not the intended recipient, or the person responsible for delivering
this communication to the intended recipient, please immediately notify
the sender by email and delete the original transmission and its
contents. Any unauthorised use, dissemination, forwarding, printing, or
copying of this communication including any file attachments is
prohibited.

It is your responsibility to scan this communication including any file
attachment for viruses and other defects. To the extent permitted by
law, ING Australia Limited and its associates will not be liable for any
loss or damage arising in any way from this communication including file
attachments.

We upgraded from Ingres 2.6 to Ingres 2006 Release 2 (9.1?). We had
some problems with the optimizer choosing inefficient plans in
complicated situations, but they were resolved _extremely_ quickly by
patches from Ingres support. I have nothing but praise for the support
we received from Ingres Corp.
Even when we were doing officially unsupported things, they helped us
out (such as rolling forward a database to retrieve one old table from
a checkpoint taken on another machine).
Other than that, the greedy optimization has eliminated our occasional
long waits for QEPs to be figured out on very large tables and has
been the single most important improvement for us. We have many tables
on some joins (8 and more) and many indexes on some tables (12 being
the most).

qiug (AT) internode (DOT) on.net escreveu:
Guys,

I installed a fresh
Ingres 2006 Release 2 Service Pack 1 +
Ingres 2006 Release 2 Service Pack 1 Patches on RHEL5 x86_64, my actual
database is :

II 2.6/0604 (a64.lnx/00)
13171
------------------------------------------------------------
II 2.6/0604 (a64.lnx/00)
12292
------------------------------------------------------------
II 2.6/0604 (a64.lnx/00)
SP5

My biggest database has 40Gb.

Anyone has a step by step how to upgrade from 2.6 to 2006 ?

I read the Ingres2006MigrationGuide.pdf but I got stuck at the processes.

tkz all !

--
Regards,

Thiago Zerbinato
Oracle/Ingres DBA
Ribeirao Preto/SP
Brazil

On Mar 23, 2009, at 3:12 PM, thiagomz wrote:
Chapter 3 of the Migration Guide is a step by step using upgradedb.
Chapter 4 is step by step using unload and reload.

If you didn't get to either of those chapters, try following the
relevant one. (I suggest using upgradedb unless there is some
specific reason not to, like simultaneous move to new hardware.)

If you were stuck reading either chapter 3 or 4, could you
explain your difficulty in a bit more detail?

I still have some parental fondness for the Migration Guide,
even though many hands have been on it since mine. So I
have a certain interest in helping to correct anything in
there that is needlessly confusing.

Karl

So far the upgrade has been good for optimizer , but theres a problem
with dates- since ingres 9.2 adds on the timezone +13 hours when
comparing
A date-only field like '23/3/9' - it assumes this is GMT!

I cant see any logic to this yet, and it makes our data wrong as we
have date-only fields
in the same table column as date-time fields.

Ingres 2.6 treats '23/3/9' as midnight on the day. Local time of course!
Ingres 9.2 treats it as 13:00 on the day. When doing comparisons.
But not always -

* select date_gmt(date('23/3/9'))
* \g
Executing . . .


2009_03_22 11:00:00 GMT

Robert
Confidentiality/Privilege Notice:
This communication is confidential and may be legally privileged. If you are not the intended recipient please delete the message and notify the sender at Ports of Auckland Limited. Any use, disclosure, copying, distribution or retention of this communication is strictly prohibited.

Hi Robert!

Robert Allely wrote:
As far as I know, this is normal behaviour of Ingres, at least from
version 2.6 on to the most recent releases. When you store a date in a
INGRESDATE (simply DATE in 2.6) column, Ingres also stores a timezone
value. The timezone value stored is based on the system's current
II_TIMEZONE_NAME setting.

The reason I became aware of this was alike to yours. Before our dbms
had been (incorrectly) configured to use GMT, so dates were stored in a
form of

2005_08_27 00:00:00 GMT
2005_09_29 00:00:00 GMT
2005_11_28 00:00:00 GMT

Later, when the dbms was upgraded in-place, it was configured to use
EUROPE-EASTERN. Because of this, all new dates started to be stored in a
form of

2005_08_26 21:00:00 GMT
2005_09_28 21:00:00 GMT
2005_11_27 22:00:00 GMT

When retrieved, the dates looked the same, but naturally comparison
failed, since under the hood the dates were different.

The "funny" thing in this is that if you dump the database out in ASCII
format and then reload it, the timezone information is lost (actually:
replaced with the new system II_TIMEZONE_NAME based value). If you use
binary format, then the old timezone information is retained and
problems may arise. *This is one of the cases where ASCII and BINARY
copies differ.*

You can "fix" the problematic data by doing a ASCII dump, recreating the
database and then reloading the data from the ASCII dump. The other way
is to do a SQL UPDATE on the INGRESDATE columns where you replace the
value of the column with value DATE(datecol).

--
Regards,
Pauli Borodulin
University of Tampere, Finland

Hi Pauli

The behaviour of ingres has definitely changed between 2.6 and 9.2
At 2.6 if you insert '25/3/9' into a date field it is stored so that
when retrieved it is midnight local time.
At 9.2 it is stored so that when retrieved it is midnight GMT which is
not midnight local time.

Theres various other confusing things that I am seeing eg. At 9.2
Date_gmt('25/3/9') returns 2009_03_24 11:00:00 GMT
But at 2.6 it returns 2009_03_25 00:00:00 GMT. Which does not agree with
the above midnight local time.
Our timezone is GMT+13 at present .

Confused? I am.
It would be better to avoid using date-only and always specify a
date-time because that's what its being used for.
But we would like to upgrade before making any changes to the apps and
data.
Robert

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Pauli Borodulin
Sent: Tuesday, 24 March 2009 9:28 p.m.
To: info-ingres (AT) kettleriverconsulting (DOT) com
Subject: Re: [Info-Ingres] Upgrading from Ingres 2.6 to
Ingres2006-version9.2

Hi Robert!

Robert Allely wrote:
As far as I know, this is normal behaviour of Ingres, at least from
version 2.6 on to the most recent releases. When you store a date in a
INGRESDATE (simply DATE in 2.6) column, Ingres also stores a timezone
value. The timezone value stored is based on the system's current
II_TIMEZONE_NAME setting.

The reason I became aware of this was alike to yours. Before our dbms
had been (incorrectly) configured to use GMT, so dates were stored in a
form of

2005_08_27 00:00:00 GMT
2005_09_29 00:00:00 GMT
2005_11_28 00:00:00 GMT

Later, when the dbms was upgraded in-place, it was configured to use
EUROPE-EASTERN. Because of this, all new dates started to be stored in a
form of

2005_08_26 21:00:00 GMT
2005_09_28 21:00:00 GMT
2005_11_27 22:00:00 GMT

When retrieved, the dates looked the same, but naturally comparison
failed, since under the hood the dates were different.

The "funny" thing in this is that if you dump the database out in ASCII
format and then reload it, the timezone information is lost (actually:
replaced with the new system II_TIMEZONE_NAME based value). If you use
binary format, then the old timezone information is retained and
problems may arise. *This is one of the cases where ASCII and BINARY
copies differ.*

You can "fix" the problematic data by doing a ASCII dump, recreating the
database and then reloading the data from the ASCII dump. The other way
is to do a SQL UPDATE on the INGRESDATE columns where you replace the
value of the column with value DATE(datecol).

--
Regards,
Pauli Borodulin
University of Tampere, Finland
_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres
Confidentiality/Privilege Notice:
This communication is confidential and may be legally privileged. If you are not the intended recipient please delete the message and notify the sender at Ports of Auckland Limited. Any use, disclosure, copying, distribution or retention of this communication is strictly prohibited.