Whatcha' wanta have?????
Madison Pruet
my more favorite exercises. ;-)
We are nearing the end of the coding cycle for IDS 9.5. We've got a whole
bunch of really cool stuff in place and - well - its time to get input from
you guys as to what you want to see in the 9.6 release.
Now, I know that everyone's favorite thing is going to be "marketing", but
I'm in development. So I need to talk features and functionality. So feel
free to send them on in.
Just an FYI - I'll be away for a while and won't be able to get email via my
comcast email address. But, I'll be following the newsgroup rather closely.
Also, next week I'll be in some planning meeting. So getting responses back
fairly quickly would really help.
Thanks
M.Pruet
David Williams
"Madison Pruet" <mpr...@comcast.net> wrote in message
news:yCVUb.239099$xy6.1249571@attbi_s02...
> As a mild diversion from the IDS-DB2 conversion thread, its time for one
of
> my more favorite exercises. ;-)
>
> We are nearing the end of the coding cycle for IDS 9.5. We've got a whole
> bunch of really cool stuff in place and - well - its time to get input
from
> you guys as to what you want to see in the 9.6 release.
>
> Now, I know that everyone's favorite thing is going to be "marketing", but
> I'm in development. So I need to talk features and functionality. So
feel
> free to send them on in.
>
> Just an FYI - I'll be away for a while and won't be able to get email via
my
> comcast email address. But, I'll be following the newsgroup rather
closely.
>
OK...
1. Ability to be able to change the owner of anything.
2. Ability to be able to rename anything.
Surely these are fairly simple?
Harder ones...
1. If have have a down chunk the ability to be able to restore just that
chunk and the rollforward
logs on just that chunk. ala Oracle recover datafile
2. Transportable table..I mean dbspaces...ala Oracle
3. That onmode -B to flush stuff to disk (useful to reduce checkpoint
times) to be
documented and supported
4. Fix that bug listing in PTS where oncheck does not check everything it
could
i.e. a better oncheck.
Alexey Sonkin
Hi, Madison,
It would be nice to see the following two features in IDS9.6:
1. Ability to manage FIRST/NEXT extent size for index.
Extent size is a very big problem for big tables, containing
LVARCHAR. For such tables, index extent size, calculated
by Informix automatically, becomes too small, and the table
might easily run out of extents for the index partition
while it has just few extents in the data partition
2. Ability to PURGE all data from a table in a single
unlogged operation.
Current workaround is to drop the table and create it again,
which is sometimes not possible.
PURGE should be considered DDL, not DML statement,
and should be non-recoverable.
PURGE should also release all unused table extents except
the initial extent.
Best regards,
Alexey Sonkin
-----Original Message-----
From: Madison Pruet [mailto:mpr...@comcast.net]
Sent: Friday, February 06, 2004 6:41 PM
To: inform...@iiug.org
Subject: Whatcha' wanta have?????
As a mild diversion from the IDS-DB2 conversion thread, its time for one of
my more favorite exercises. ;-)
We are nearing the end of the coding cycle for IDS 9.5. We've got a whole
bunch of really cool stuff in place and - well - its time to get input from
you guys as to what you want to see in the 9.6 release.
Now, I know that everyone's favorite thing is going to be "marketing", but
I'm in development. So I need to talk features and functionality. So feel
free to send them on in.
Just an FYI - I'll be away for a while and won't be able to get email via my
comcast email address. But, I'll be following the newsgroup rather closely.
Also, next week I'll be in some planning meeting. So getting responses back
fairly quickly would really help.
Thanks
M.Pruet
sending to informix-list
Alexey Sonkin
Hi, Madison,
Best regards,
Alexey Sonkin
Thanks
M.Pruet
sending to informix-list
sending to informix-list
Jonathan Leffler
> It would be nice to see the following two features in IDS9.6:
>
> 1. Ability to manage FIRST/NEXT extent size for index.
> Extent size is a very big problem for big tables, containing
> LVARCHAR. For such tables, index extent size, calculated
> by Informix automatically, becomes too small, and the table
> might easily run out of extents for the index partition
> while it has just few extents in the data partition
A well known request - not unreasonable.
> 2. Ability to PURGE all data from a table in a single
> unlogged operation.
> Current workaround is to drop the table and create it again,
> which is sometimes not possible.
> PURGE should be considered DDL, not DML statement,
> and should be non-recoverable.
> PURGE should also release all unused table extents except
> the initial extent.
PURGE TABLE - aka TRUNCATE TABLE...
Reasonable request - mostly. I agree it is probably DDL rather than
DML - it certainly isn't a regular DELETE operation, and should use a
different privilege, and ALTER TABLE might be OK, though a new
specific TRUNCATE privilege would be better, I think.
I know Oracle doesn't allow recovery of its TRUNCATE TABLE, but then
it doesn't allow any other DDL operation to be recovered from.
However, Informix does provide recovery of every operation within a
transaction -- why would you want to lose the ability in this one
statemen? You can drop a table in a transaction and rollback the TX
and the table is still there - why should TRUNCATE TABLE, which does
less work, be any different?
[Background: we had an intense discussion about this, and DB2 in
particular does not want to implement a recoverable TRUNCATE TABLE.
The XPS implementation of TRUNCATE TABLE does not allow recovery.
However, it seems to me inherently bad to make one statement different
from all the rest. In particular, the XPS design is such that you
can't do anything else with the truncated table in the same
transaction. I would foresee that there would be occasions where I
wanted to delete, say, 80% of the records, but to reseed the table
with the remaining 20% of the records - in the same TX. I'd expect to
do that by unloading to a temp table (cross-loading?) the stuff to
retain, truncating the main table, and then cross-loading the material
from the temp table back into the main table, all in a single
transaction. Basically, I lost this argument - and I'm still being a
sore loser about it, because I think it is fundamentally b******t that
we can recover from dropping a table and not from truncating it.
However, here's an external contrary view, so I'm seeking to know why
it is not critical to recover from a truncated table, even though you
can recover from a dropped table.]
--
Jonathan Leffler #include <disclaimer.h>
Email: jlef...@earthlink.net, jlef...@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
Andrew Hamm
> Basically, I lost this argument - and I'm still being a sort loser
> about it, because I think it is fundamentally b******t that we
> can recover from dropping a table and not from truncating it.
> However, here's an external contrary view, so I'm seeking to know why
> it is not critical to recover from a truncated table, even though you
> can recover from a dropped table.]
I agree with your point of view that IDS has established practice where
everything is logged. I also agree that some operations probably should not
be logged, or only optionally.
If you introduce a a few baroque options like
truncate table with no log;
drop table with no log;
drop index with no log;
and so on, then everyone's a winner?
Preetinder Dhaliwal
Check in onmonitor and other utilities for adding chunks, dbspaces etc ,
which makes sure that user does not accidentally overwrites existing space.
that is , it should check for overlapping atleast in the same instance.
Rgds
Preetinder
Andrew Hamm
that cannot be performed. For example, if you want to change a table to raw
state, you must drop the constraints, not just disable them. It's a flipping
nuisance.
Since a disabled constraint or index is going to be rebuilt anyway, why not
treat it as "gone" for the purposes mentioned above?
On the subject of raw tables, you cannot change their schema. Catch 22 when
you are trying dirty tricks to quickly administer large tables. We all know
we need to take a real level zero afterwards...
Similar vein: under ER, changes of schema, disabling constraints and a few
other oddities are blocked or cause administration problems. Changing a
table quickly without having to resyncronize is a hassle. Can't a change of
schema be accommodated? If the "select *" in the replicants was treated as
an expanded list of columns, then adding a column at either end shouldn't
have any effect on the replication. I suppose there are housekeeping issues.
Are there any scientific reasons against allowing this?
Let us add a column at one end, change it later at the other end, and we'll
worry about sync if the column needs it. When it doesn't, the process should
be a lot lighter than it currently is.
oooooh yeah - here's a must-have admin facility: add a new state that is not
online, not quiescent, but which will allow SPECIFIC users to connect and do
work. The list could be just informix by default, or a short list of users
defined elsewhere. On some sites, if you bring the engine online to do some
SQL admin, the bloody over-enthusiastic users will connect and make a mess
of your administration.
Related: the ability to bring up or shutdown connectors - eg we could also
control administration by making all users connect via a tcp or ipc
connector. Only administrators get local connectivity with shm connectors.
Therefore, if we can dynamically shutdown the tcp and ipc connectors
(gracefully or immediately) then we have another alternative to
administration. If ER is used on the site, then perhaps ER connections could
be allowed to stay running. This implies that the connectors are still
listening, but they are refusing connections to most attempts.
Preetinder Dhaliwal
Rgds
Preetinder
Madison Pruet wrote:
sending to informix-list
sending to informix-list
Neil Truby
"Preetinder Dhaliwal" <preetinde...@dhl.com> wrote in message
news:c076eq$c86$1...@terabinaries.xmission.com...
>
> Check in onmonitor and other utilities for adding chunks, dbspaces etc ,
> which makes sure that user does not accidentally overwrites existing
space.
>
> that is , it should check for overlapping atleast in the same
instance.
Is this a post from the heart? ;-)
Tim Schaefer
daily basis as a command with SQL-Server, but keep the table without
destroying and then re-creating it with DDL. Don't really see why it
must be DDL, and a simple command to truncate the table. Bummer with
SQL-Server is that it's logged, so it can really be a time-consuming
task on large tables. You might want to add to the request that it
have a LOGGED or NO LOG option as well. ( SQL-Server cannot turn off
logging completely, I don't think DB2 can either without that goofy
alter table command )
Just my .02 USD
Tim
"Jonathan Leffler" <jlef...@earthlink.net> wrote in message news:402717D1...@earthlink.net...
> Alexey Sonkin wrote:
> > It would be nice to see the following two features in IDS9.6:
<...>
Obnoxio The Clown
How about the the ability to rename a database? (That's one for our older
viewers... :o)
Online index builds? (Nasty, I know.)
Better support for extended data types with HDR and remote access.
--
"C'est pas parce qu'on n'a rien ą dire qu'il faut fermer sa gueule"
- Coluche
Tsutomu Ogiwara
Hi Madison and all.
I have been request as bellow.
1. FORCE CHUNK DROP.
2. TRUNCATE TABLE.
If both of them already contain 9.5, I do not have feature request.
But if not, I will request above.
--
Tsutomu Ogiwara from Tokyo Japan.
ICQ#:168106592
>From: "Madison Pruet" <mpr...@comcast.net>
>Reply-To: "Madison Pruet" <mpr...@comcast.net>
>To: inform...@iiug.org
>Subject: Whatcha' wanta have?????
>Date: Fri, 06 Feb 2004 23:41:18 GMT
>
>As a mild diversion from the IDS-DB2 conversion thread, its time for one of
>my more favorite exercises. ;-)
>
>We are nearing the end of the coding cycle for IDS 9.5. We've got a whole
>bunch of really cool stuff in place and - well - its time to get input from
>you guys as to what you want to see in the 9.6 release.
>
>Now, I know that everyone's favorite thing is going to be "marketing", but
>I'm in development. So I need to talk features and functionality. So feel
>free to send them on in.
>
>Just an FYI - I'll be away for a while and won't be able to get email via
>my
>comcast email address. But, I'll be following the newsgroup rather
>closely.
>
>Also, next week I'll be in some planning meeting. So getting responses back
>fairly quickly would really help.
>
>Thanks
>
>M.Pruet
>
>
_________________________________________________________________
The new MSN 8: advanced junk mail protection and 2 months FREE*
http://join.msn.com/?page=features/junkmail
sending to informix-list
Tsutomu Ogiwara
Hi Madison and all.
sending to informix-list
sending to informix-list
June C. Hunt
>[...]
> oooooh yeah - here's a must-have admin facility: add a new state that is
not
> online, not quiescent, but which will allow SPECIFIC users to connect and
do
> work. The list could be just informix by default, or a short list of users
> defined elsewhere. On some sites, if you bring the engine online to do
some
> SQL admin, the bloody over-enthusiastic users will connect and make a mess
> of your administration.
This would be beautiful! I've also had over-enthusiastic users get in the
way... forced me to show my dark side. Of course, I could have disabled
their login ids (having the root password has its advantages)... or gone
through some other motions with the network. But the new state that Andrew
suggests would be a very nice option.
--
June Hunt
Esteban Casuscelli
HDR for specific databases inside one instance.
In HA enviroment sometimes you have database that you dont care but
have one or two very importants, replicating these databases to a
secondary server ( read only ) will give you a faster and easy way,(
no primary keys for each table, no administration tasks added like
create, stop, delete replicates, you dont have to stop the replication
if you have to apply schema changes), to replicate important data.
It isn't a ER enviroment because you dont have all the features and
flexibilite from ER but it 's in middle between ER and HDR.
If you are interested let me know I can send you a document with the
whole idea, and benefits.
esteban.-
"Neil Truby" <neil....@ardenta.com> wrote in message news:<c07fs5$13s251$1...@ID-162943.news.uni-berlin.de>...
Obnoxio The Clown
> We are nearing the end of the coding cycle for IDS 9.5. We've got a whole
> bunch of really cool stuff in place and - well - its time to get input
> from you guys as to what you want to see in the 9.6 release.
Since no one else took the opportunity: "marketing". :o)
--
"C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
- Coluche
Art S. Kagel
Such as (some mentioned by others):
- Online index build - without locking the table - A dirty read is
used to build the index then logical log records are replayed to clean
up keys that were added, rolled back, or deleted during the build.
During the build the index is marked disabled and is reenabled once
the build is completed.
- Built-in table compression/reorg command that in a transaction
rewrites rows filling partial and empty pages, releaseing empty
extents and truncating extents that are partially used (perhaps with a
given slack factor, but not neccessarily explicit say leave up to NEXT
SIZE free in the last extent) - COMPRESS TABLE tablename <IN PLACE>
....; The IN PLACE option controls whether new extents are allocated
to make the table more contiguous or existing extents are simply
compressed.
- Use the logical log rollforward from the online index build to make
an online index/constraint enable option as long as the needed logical
logs are still online. If not a full index rebuild is needed which
an be performed using the full online index build code.
- Better support for using ER for HA replication, perhaps a database
level replication which would permit DDL to be replicated as well.
- Support for multiple HDR replicants.
- Support for concurrent connection to primary and at least one
replicant and passing of transaction information in HDR from primary
to replicant for seamless failover. If sufficient information and
data has been passed to the replicant, then transactions can continue
on the alternative connection unhindered assuming all the needed data
has been passed from the primary (see later on). If a recovery
rollback is triggered on the replicant that affects a particular
transaction that transaction alone will receive a replication error
and its transaction will be marked invalid. Missing data can be
verified by the primary passing transaction management data OOB to the
client. If the transaction progress stamp from the client is later
than that which the replicant finds in its logs after taking over then
the transaction is trashed due to data not transferred from the
primary prior to failure. Basically then an open transaction, after a
primary server failure, is handled more like a 2-Phase Commit problem
with the client acting as transaction coordinator in part. If the
client is lost the transaction has to be rolled back or committed
regardless using standard lost connection rules. It might even be
possible to support this in ER with sychronous replication.
Art S. Kagel
Neil Truby
- How about allowing the administrator the option to skip logical recovery
at database startup time? Obviously, you'd want to restirct it to people
who know what they're doing. Like me! Perhaps you'd have to type in your
Informix Certified Professional ID to enable it ....
- Truncate table, as previously mentioned.
- More information about the progress of long utilities, like ALTER
FRAGMENT...INIT IN
- Is there an inherent reason to prevent querying of a logged database from
an unlogged one?
- Better integration with HP Data Protector (but I'm in Dreamland now).
Serge Rielau
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Madison Pruet
As I mentioned earlier, I'm sure that is a top priority in everyone's mind.
But I need to get the functionality that you guys want to have in the
product.
Thanks -
M.P.
"Obnoxio The Clown" <obn...@hotmail.com> wrote in message
news:c08ah9$13r4o0$1...@ID-64669.news.uni-berlin.de...
Alexey Sonkin
See below
> From: Art S. Kagel [mailto:ka...@erols.com]
>
> Keep moving us towards a no-down-time server as much as possible.
> Such as (some mentioned by others):
>
> - Online index build - without locking the table - A dirty read is
> used to build the index then logical log records are replayed to clean
> up keys that were added, rolled back, or deleted during the build.
> During the build the index is marked disabled and is reenabled once
> the build is completed.
>
Dirty read is not enough to build index 'online'.
One can use mechanism similar to what ONTAPE is doing during archiving:
one can keep old copies of data pages in temporary dbspace,
and, during index build, take 'backuped' copies of those pages,
which timestamp exceeds the 'index build start' timestamp.
Then, Logical log can really be used to roll forward subsequent changes.
'Online' build should be specified as a special option of
'CREATE INDEX' SQL statement, line 'CREATE INDEX ... NONBLOCKING'
That would be a really cool feature which is really needed
for 24x7 systems (like mine). I couldn't even dream about it,
this is why I didn't mention it in my proposal.
The other feature highly desirable for 24x7 is:
GET RID OF ERROR 710!!!! (After alter table, build index, etc)
That is, after executing any DDL statement that changes table
version all prepared SQL statements, open cursors and stored procedures
related to that table became invalid, and user session get -710 error.
If these two features (online index build and online alter table)
get implemented in 9.60, IDS will became THE BEST RDBMS FOR 24x7 SYSTEMS!!!
Best regards,
Alexey Sonkin
sending to informix-list
Alexey Sonkin
See below
sending to informix-list
Alexey Sonkin
David Williams
Also.
1. truncate table x should drop all extents.
2. create table..with no first extent size specified should not allocate an
extent
(why waste space if the table is not used by this particular customer
e.g. SAP/Baan
etc with 40,000 tables!!)
3. Ability to alter the first extent size on a table then
a) a table with no extents would use this as the first extent size
i.e. unused table but the customer decides they want to use a new
area of the overall
application suite which now needs this table to have 100,000 rows
loaded.
Hassle if constraints/views etc also used.
b) alter fragment on table x init in .. could use the new extent size
Wild stuff.
1. We have (my syntax is crap but you get the idea)
create index myind on a (b) fragment with b=null in dbspace_1,
remainder in dbspace_2
why need the remainder?
create index myind as select a from b where a is not null.
e.g. table use to sent an interface file to another system
create table mytab (col1 char(20), col2 int,....,batchid integer)
batchid=null until the data is write to a file to be sent to the
receiving system
(e.g. invoice file)...
Rows gradually get put in the table, once per hour a process does
a) select * from table where batchid is null
b) sends this data out to the other system
c) updates these rows to have a given batchid.
create myind index1 as select * from mytab where batchid is null
would be a much smaller index, probably with less levels!
Esteban Casuscelli
esteban.-
"Madison Pruet" <mpr...@comcast.net> wrote in message news:<yCVUb.239099$xy6.1249571@attbi_s02>...
Alexey Sonkin
See below
Best regards,
Alexey Sonkin
sending to informix-list
sending to informix-list
Serge Rielau
> 2. create table..with no first extent size specified should not allocate an
> extent
> (why waste space if the table is not used by this particular customer
> e.g. SAP/Baan
> etc with 40,000 tables!!)
Uhum.. yep heard about that one.
Murray Wood (IList)
Auto Update Statistics (i.e A set and forget option)
To be done under some logical and meaningful circumstances
- Change in number of rows by x%
- Update of index columns by y%
Optionally, this to be selectable so certain tables can be eliminated from the list of Auto Statistics (Default = All Tables).
Optionally, this can be restricted to certain time of day intervals (Default = All Times).
When corrupt column distributions are found, the table is flagged for update statistics and the distributions are ignored for this sql - rather than returning the Invalid Distributions error code to the application.
MW
sending to informix-list
Murray Wood (IList)
MW
sending to informix-list
sending to informix-list
mos...@wellsfargo.com
> -----Original Message-----
> From: Alexey Sonkin [mailto:ale...@grandvirtual.com]
>
< snipped >
>
> The other feature highly desirable for 24x7 is:
> GET RID OF ERROR 710!!!! (After alter table, build index, etc)
>
> That is, after executing any DDL statement that changes table
> version all
> prepared SQL statements, open cursors and stored procedures
> related to that
> table became invalid, and user session get -710 error.
Related: Implement outstanding feature request to run "set optimization low"
from the *server* side.
Also, I will "second" the earlier request to increase ER usability in an HA
environment.
Paul Mosser
sending to informix-list
David Williams
And a simple one...when message INFORMIXSERVER not found in sqlhosts file
or DBSERVERNAME not found in sqlhosts file could the message include the
name
it is looking for? This would make it much easier to debug!
Also oninit -v option to be documented.
All of onstat options to be documented.
More monitoring stuff added to sysmaster (add everything onstat does and
document the
correspondence)...at least as much as you can.
I'm off to sleep now!
mos...@wellsfargo.com
sending to informix-list
Andrew Hamm
I thought it already did this check; although it doesn't check the
underlying files if you are using symlinks or physical device names.
If the user specifies a device, I think it should issue at least a warning
if it's not a raw device. If it's not even a block device, it probably
should be a fatal error.
Tsutomu Ogiwara
More request.
Marge entent (aka reorg)
--
Tsutomu Ogiwara from Tokyo Japan.
ICQ#:168106592
_________________________________________________________________
Add photos to your e-mail with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=features/featuredemail
sending to informix-list
Alexey Sonkin
Paul,
"Set optimization low" does not help, we've tried it.
"set optimization low" only works well after 'update statistics',
not after real 'alter table'
I think, it's related to minor/major table version change
Alexey Sonkin
>From: mos...@wellsfargo.com [mailto:mos...@wellsfargo.com]
>Sent: Monday, February 09, 2004 6:58 PM
>
>> That is, after executing any DDL statement that changes table
>> version all
>> prepared SQL statements, open cursors and stored procedures
>> related to that
>> table became invalid, and user session get -710 error.
>
>Related: Implement outstanding feature request to run "set optimization
low"
>from the *server* side.
sending to informix-list
Alexey Sonkin
Paul,
Alexey Sonkin
sending to informix-list
sending to informix-list
Tsutomu Ogiwara
More request.
Marge entent (aka reorg)
sending to informix-list
sending to informix-list
Thomas Mieslinger
Please remove the 32GB Tablespce limit.
owner-inf...@iiug.org schrieb am 07.02.2004 00:41:18:
> As a mild diversion from the IDS-DB2 conversion thread, its time for one
of
> my more favorite exercises. ;-)
>
> We are nearing the end of the coding cycle for IDS 9.5. We've got a
whole
> bunch of really cool stuff in place and - well - its time to get input
from
> you guys as to what you want to see in the 9.6 release.
>
> Now, I know that everyone's favorite thing is going to be "marketing",
but
> I'm in development. So I need to talk features and functionality. So
feel
> free to send them on in.
>
> Just an FYI - I'll be away for a while and won't be able to get email via
my
> comcast email address. But, I'll be following the newsgroup rather
closely.
>
> Also, next week I'll be in some planning meeting. So getting responses
back
> fairly quickly would really help.
>
> Thanks
>
> M.Pruet
>
--
General Logistics Systems
Thomas Mieslinger
German-Parcel-Str. 1-7 fon: +49 6677 17 463
36286 Neuenstein fax: +49 6677 17 111
Germany eMail: thomas.m...@gls-germany.com
sending to informix-list
Thomas Mieslinger
sending to informix-list
sending to informix-list
Chris Hall
> my more favorite exercises. ;-)
>
> We are nearing the end of the coding cycle for IDS 9.5. We've got a whole
> bunch of really cool stuff in place and - well - its time to get input from
> you guys as to what you want to see in the 9.6 release.
>
> Now, I know that everyone's favorite thing is going to be "marketing", but
> I'm in development. So I need to talk features and functionality. So feel
> free to send them on in.
>
> Just an FYI - I'll be away for a while and won't be able to get email via my
> comcast email address. But, I'll be following the newsgroup rather closely.
>
> Also, next week I'll be in some planning meeting. So getting responses back
> fairly quickly would really help.
>
> Thanks
>
> M.Pruet
I'd like to see
- 'upsert' (update or insert)
- 'smart' foreign key constraint that uses any feasible index
- configurable buffer page pools to stop rubbish queries on large
tables flushing all buffer pages (sybase had this feature about 8
years ago).
- explicit support for temp spaces in RAM
- anything which can be done to lessen checkpoint times
- a job scheduler in the server (so that I can do things like say
at '2004-03-15 15:00:00' execute procedure my_job()
I'd like a transactional interface so that I can remove and add
jobs as part of the scheduled operation, and a bulletproof implementation
that provides guarantees about when jobs get run if the server goes
down and is restarted
Chris
Thomas Mieslinger
2-4kilobytes. It would be extremly usefull to have pagesizes up to 64k
Dirk Moolman
1. How about a work-around for the "old page" bug on backups ?
2. More options for restores - restoring single tables, etc.
3. Updating statistics not having an effect on programs currently running
________________________________
<< ella for Spam Control >> has removed 1269 Spam messages and set aside 234 Newsletters for me
You can use it too - and it's FREE! www.ellaforspam.com
sending to informix-list
Dirk Moolman
________________________________
sending to informix-list
sending to informix-list
Richard Kofler
>
> As a mild diversion from the IDS-DB2 conversion thread, its time for one of
> my more favorite exercises. ;-)
>
> We are nearing the end of the coding cycle for IDS 9.5. We've got a whole
> bunch of really cool stuff in place and - well - its time to get input from
> you guys as to what you want to see in the 9.6 release.
>
> Now, I know that everyone's favorite thing is going to be "marketing", but
> I'm in development. So I need to talk features and functionality. So feel
> free to send them on in.
>
> Just an FYI - I'll be away for a while and won't be able to get email via my
> comcast email address. But, I'll be following the newsgroup rather closely.
>
> Also, next week I'll be in some planning meeting. So getting responses back
> fairly quickly would really help.
>
> Thanks
>
> M.Pruet
I' d like to see
- physlog inital setup much like rootdbs
- same for loglog
so that one does not have to do the ever same steps at the
very beginning
- when auto build indexes (at reinable constraints time)
automatic adjustment along a ressource spec or
test of available filesystems / memory so that
it is not a crawl (like now)
- automatic usage of memory in update statistics for sorts,
if memory is around in tons (like it tends to be now
everywhere)
- better documentaion of the ER internal tables
used for conflict resolution
like: when does it go to the dbspace the db was
created in (gramented tables) or when does it sit
in the dbspace where the table is....
All that is rather documented not enough to
avoid problems of running out of space
- a new warning if you load zillions of records
into a table with using default FIRST SIZE of 8 pg
- a warning from update statistics if # of datapages
is much smaller then pages used & indexes are not
on that table or only detached ones.
- a way to tell in what file one wants output of
SET EXPLAIN ON
dic_k
--
Richard Kofler
SOLID STATE EDV
Dienstleistungen GmbH
Vienna/Austria/Europe
Andrew Hamm
> Jonathan Leffler wrote:
>> Basically, I lost this argument - and I'm still being a sort loser
>> about it, because I think it is fundamentally b******t that we
>> can recover from dropping a table and not from truncating it.
>> However, here's an external contrary view, so I'm seeking to know why
>> it is not critical to recover from a truncated table, even though you
>> can recover from a dropped table.]
>
> I agree with your point of view that IDS has established practice
> where everything is logged. I also agree that some operations
> probably should not be logged, or only optionally.
I have to revisit this - I've moved completely to Jonathans side on this
one. I cannot support the case for unlogged commands in IDS. Just because a
few enthusiastic DB/2 engineers are now on the committee doesn't mean IDS
has to start doing odd little actions the DB/2 way.
IDS has always logged everything not just so we can do a ROLLBACK WORK, but
in case an engine or machine crash occurs. Without logging, fast recovery
can be compromised.
Now, if you put a TRUNCATE TABLE command into the engine, then some sites
are going to use it and they'll probably like it so much, they'll use it a
lot. They are just begging for a disaster to cause their fast recovery to
fail; at which point they get the opportunity to test how good their backup
procedures are :-/
I do not want an unlogged TRUNCATE TABLE command putting engines at risk. I
do not want to spend a night recovering an engine when I could have been
sleeping. Please do not put something dangerous in the hands of people who
often will not know the risks.
I do see a very effective compromise however. We already have raw tables in
IDS, specifically for the people who like to live dangerously. Clearly, a
TRUNCATE TABLE applied to a raw table will not be logged. If my other idea
about raw tables was implemented (ie allowing raw tables to still have
disabled indexes etc attached to them) then this gives to everyone, and fits
in quite nicely with the characteristics of raw tables.
PS - regarding raw tables; putting a table to raw should CAUSE the indexes,
constraints etc to be automatically disabled imho. It's a nuicance having to
find them all, record their definitions, destroy them and then rebuild them
later.
Andrew Hamm
case insensitive comparisons and collating order!
I don't want that applied to CHAR and VARCHAR types, since they are
fast'n'mean byte-wise datatypes. However, since NCHAR and NVARCHAR fields
can have a GLS locales applied, they are ideal for this little extension.
I cannot find an English Locale that has case-insensitive collation. I don't
know about other european GLS locales, but I'd wager they are case sensitive
too, but could profit from caseless.
I'm not sure that everyone would WANT all strings to be caseless; for us we
would want codes to be sensitive but many description strings to be
insensitive, but we could get away with using CHAR and NCHAR as appropriate.
People using French, German, et. al. would not have that luxury.
Therefore i think that GLS locales need to be provided with an inbuilt
alternative to the case sensitivity of the collation, and then NCHAR and
NVARCHAR fields could have an optional WITH NO CASE (c.f. NOT NULL) to set
the field to one style or the other.
Once again, to repeat, plain ole' CHAR fields should not be affected in any
way.
Tim Schaefer
Just some trivia, not arguing for or against your points.
The ability to control logging is what makes IDS the most totally
controlable database on the planet. None of the other databases
worth mentioning, except maybe possibly Oracle has the flexibility
when it comes to transaction logs. I'm not discounting or dismissing
your points, but maybe only pointing out please appreciate the difference
IDS brought to the table, for me at least, it's now all academic. The
idea, the luxury of arguing the ability to log or not to log, it makes
me cry. :-(
SQL-Server/Sybase - absolutely no control over logging or size of logs.
Everything is logged, no control over size of logs
or how many, but you can specify what the size of
"the" log should start out at. Inside "the log"
file, SQL-Server determines how many logs, when
checkpoints occur, how big it gets, etc. Pretty
pathetic, totally automatic.
DB2 - some ability but not really a planned or thought out ability. Almost
no docs on logging at all. Last I checked it's always on.
MySQL - Either you use MyISAM with limited low-level "non-logged" logging
or InnoDB always-logged. No "dbspaces". MyISAM still has a 4GB
limit on tables on Linux.
Informix - logged, non-logged, number of logs, size of logs. ondblog, onlog, etc.
Totally flexible, checkpoint management, really great monitoring,
onstats. Unmatched for DBA control. Lousy backup/restore options.
Oracle - has the infamous redo logs, which puts it pretty much Informix's
only equal as even coming remotely close to the flexibility of
Informix.
Gawd I miss Informix for log control, but c'est la vie life goes on. Imagine
a world without Informix and you will only begin to understand what it's like
out there. LRUs?? Physical Log? Fagetabotit.
I'm sure IBM is depending on market ignorance of log control at this point,
and also on reverse-ignorance of the Informix community who are so myopic
and in many cases Neanderthal about other products. Just this one ability
will be lost and quietly forgotten when Informix finally disappears.
Enjoy the acedemics while the product lingers, once it's gone, well, it's
really the end of a great product.
Tim
"Andrew Hamm" <ah...@mail.com> wrote in message news:c0cb7h$15c82c$1...@ID-79573.news.uni-berlin.de...
Mark Townsend
control logging at a granular level is a Bad Thing (TM), especially
after seeing some of the messes customers can get themselves into with
nologging. This would make for an interesting industry debate at some stage.
Obnoxio The Clown
> FYI - there are many at Oracle that would also argue that the ability to
> control logging at a granular level is a Bad Thing (TM), especially
> after seeing some of the messes customers can get themselves into with
> nologging. This would make for an interesting industry debate at some
> stage.
Ah, well, it's just customers, isn't it? :o)
On a more serious note, I don't see why competent DBAs should be punished
for my cock-ups... I mean, er, ...
--
"C'est pas parce qu'on n'a rien ą dire qu'il faut fermer sa gueule"
- Coluche
Fernando Nunes
> SET EXPLAIN ON
I like this one, but would suggest a step further (if it makes any sense):
Some kind of API to get the explain from the client side. Currently we have to get file system access to the server where the instance is...
Regards.
Tim Schaefer
I don't know Oracle well enough to talk competently about redo logs,
but it's my limited understanding they are a bit different than the
logical logs in Informix. As I understand it Oracle people live and
die with creative log shipping that most Informix people would never
even think of doing with logical logs. This is a good thing about
Oracle, as I understand it, but the comparison is not quite apples
to apples.
What I think was one of Informix's greatest strengths, the ability
to control tranactional journaling, along with really great tools
like onstat, made it superior in performance over the others. Now
I'm stuck with a lot of choices in the market that literally hide
logical log management, and that makes me nervous about my choices.
But if the vendor has a high-speed loader, and good tunable parameters,
it overcomes some of the fear of living without that logging control.
And I agree, you're right, in many shops it's a good thing the customer
doesn't have the control, they're better off without it. SQL-Server
for all its simplicity is quite appropriate in most of the environments
that use it simply because the talent taking care of it has no idea
what they're missing. :-)
Tim
"Obnoxio The Clown" <obn...@hotmail.com> wrote in message news:c0cm0s$14qef5$1...@ID-64669.news.uni-berlin.de...
> "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
> - Coluche
Sosnowski Robert
As for 9.6 I see 2 important things:
1. Web services
2. BLOB literals
ad. 1
Idea behind stored procedures in SQL database is very similar to that of Web
services. So it seems quite natural that database should expose its stored
procedures as WEB services.
I see that this is:
- useful even in Intranet,
- becoming standard.
Currently in my company we will migrate our database application to .NET.
Even for me as the beginner in .NET concept of exposing application API in
Web Services instead of stored procedures seems straightforward. It would be
even easier if database would support it natively.
It is becoming standard. DB2 and Oracle already have it. Also BEA Tuxedo
middleware. MSSQL and Sybase announced. Don't let Informix be an isolated
data island!
There are some peculiarities with Web services: there are two different
reference platforms:
- .Net
- Apache-Tomcat (java).
As Informix works not only on NT then choice is clear. I'm only afraid about
java: I would prefer implementation not based on java if possible. I just
prefer simpler but faster and more reliable solution.
DB2 also have interesting extension: you can use web service similar as
stored procedure.
In Informix it extends stored procedure implementation method. Currently
Informix have:
- SPL;
- J/SQL (Internet Foundation only);
- C databalades.
I want to add to this list:
- Web Services.
ad 2.
For BYTE, TEXT, CLOB, and BLOB types there are no literals. Why? It just
complicates simple things.
For TEXT and CLOB it would be the same literal as for char(30000) column.
For BYTE and BLOB it have to be some hex literal.
Best Regards,
Robert Sosnowski
IT specialist CK BZ WBK,
e-mail: Robert.S...@bzwbk.pl
ul. Pl. Andersa 5, 61-894 Poznań
phone +48 61 856 54 09
fax +48 61 856 52 36
>>As a mild diversion from the IDS-DB2 conversion thread, its time for one
of
>>my more favorite exercises. ;-)
>>
>>We are nearing the end of the coding cycle for IDS 9.5. We've got a whole
>>bunch of really cool stuff in place and - well - its time to get input
from
>>you guys as to what you want to see in the 9.6 release.
>>
>>Now, I know that everyone's favorite thing is going to be "marketing", but
>>I'm in development. So I need to talk features and functionality. So
feel
>>free to send them on in.
>>
>>Just an FYI - I'll be away for a while and won't be able to get email via
my
>>comcast email address. But, I'll be following the newsgroup rather
closely.
>>
>>Also, next week I'll be in some planning meeting. So getting responses
back
>>fairly quickly would really help.
>>
>>Thanks
>>
>>M.Pruet
===========================
Wiadomość ta oraz wszelkie załączone do niej pliki są poufne i mogą być prawnie chronione.
Jeżeli nie jest Pan/Pani zamierzonym adresatem niniejszej wiadomości, nie może Pan/Pani jej ujawniać,
kopiować, dystrybuować ani też w żaden inny sposób udostępniać lub wykorzystywać.
O błędnym zaadresowaniu wiadomości prosimy niezwłocznie poinformować nadawcę i usunąć wiadomość.
===========================
This email and any attached files are confidential and may be legally privileged.
If you are not the intended recipient, any disclosure, reproduction, copying, distribution,
or other dissemination or use of this communication is strictly prohibited. If you have received this transmission in error please notify the sender immediately and then delete this email.
sending to informix-list
Sosnowski Robert
Best Regards,
Robert Sosnowski
sending to informix-list
sending to informix-list
Obnoxio The Clown
>
> As for 9.6 I see 2 important things:
> 1. Web services
> 2. BLOB literals
>
> ad. 1
> Idea behind stored procedures in SQL database is very similar to that of
> Web services. So it seems quite natural that database should expose its
> stored procedures as WEB services.
> I see that this is:
> - useful even in Intranet,
> - becoming standard.
> Currently in my company we will migrate our database application to .NET.
> Even for me as the beginner in .NET concept of exposing application API in
> Web Services instead of stored procedures seems straightforward. It would
> be even easier if database would support it natively.
>
> It is becoming standard. DB2 and Oracle already have it. Also BEA Tuxedo
> middleware. MSSQL and Sybase announced. Don't let Informix be an isolated
> data island!
It is becoming standard? Which standard?
> There are some peculiarities with Web services: there are two different
> reference platforms:
> - .Net
> - Apache-Tomcat (java).
> As Informix works not only on NT then choice is clear. I'm only afraid
> about java: I would prefer implementation not based on java if possible. I
> just prefer simpler but faster and more reliable solution.
Your grasp of web services seems to be even more hazy than mine, and that is
impressive.
> DB2 also have interesting extension: you can use web service similar as
> stored procedure.
> In Informix it extends stored procedure implementation method. Currently
> Informix have:
> - SPL;
> - J/SQL (Internet Foundation only);
> - C databalades.
> I want to add to this list:
> - Web Services.
Implement them as a DataBlade?
> ad 2.
> For BYTE, TEXT, CLOB, and BLOB types there are no literals. Why? It just
> complicates simple things.
> For TEXT and CLOB it would be the same literal as for char(30000) column.
> For BYTE and BLOB it have to be some hex literal.
This *is* a huge PITA.
--
"C'est pas parce qu'on n'a rien ą dire qu'il faut fermer sa gueule"
- Coluche
Dirk Moolman
-----Original Message-----
From: owner-inf...@iiug.org [mailto:owner-inf...@iiug.org] On Behalf Of Sosnowski Robert
Sent: Wednesday, February 11, 2004 3:15 PM
To: 'inform...@iiug.org'
Subject: Re: Whatcha' wanta have?????
>As for 9.6 I see 2 important things:
>1. Web services
>2. BLOB literals
Mentioning the web, made me think of something else - security.
How about an easier way to control access to tables. It can become messy when you decide to give access to certain tables to groups of people.
And some levels of access should be overlooked by the database, such as creating a new table without having to add group levels of security to it - the security should automatically be set via a higher predefined setting.
(Hope this didn't sound to confusing ...)
________________________________
<< ella for Spam Control >> has removed 1296 Spam messages and set aside 240 Newsletters for me
Dirk Moolman
-----Original Message-----
To: 'inform...@iiug.org'
>1. Web services
>2. BLOB literals
________________________________
sending to informix-list
sending to informix-list
Jonathan Leffler
> Related: Implement outstanding feature request to run "set optimization low"
> from the *server* side.
Can you elucidate this, possibly with the feature request (bug)
number? Are you saying that you cannot run SET OPTIMIZATION LOW in a
stored procedure? The request has to be processed by the server, so
I'm confused...
-=JL=-
Alexey Sonkin
One more request.
Make a 64-bit of IDS on Linux (preferably, AMD64, not I64,
for easier 32->64 upgrade)
I think a lot of people are interested in that.
May be, it is realistic with 9.50 or even with 9.40?
------------------------------------------
Alexey Sonkin
Senior Database Administrator
sending to informix-list
Alexey Sonkin
One more request.
sending to informix-list
sending to informix-list
Bryce Stenberg
Hi,
One thing I would like to see that has bugged me on and off over the years
is to do with the 'group by' clause in SQL (assuming it has not already been
dealt with in 9.4, we are only running 7.31 currently and move to 9.4 this
year).
When doing selects with a 'group by', everything in your select list (maybe
entire records with many columns) has to be listed in your 'group by'
clause, even though you only want to group by one or two of the selected
columns. This is most frustrating.
So, can we have 'group by' where you only need list the columns you actually
want to group by while selecting many more columns than listed in the group
by (or does this break some SQL standard or something?).
Regards,
Bryce Stenberg.
DISCLAIMER: http://www.hrnz.co.nz/eDisclaimer.htm
sending to informix-list
Bryce Stenberg
Hi,
Regards,
Bryce Stenberg.
DISCLAIMER: http://www.hrnz.co.nz/eDisclaimer.htm
sending to informix-list
sending to informix-list
Alexey Sonkin
Jonathan,
We 'set optimization low' in a sessions that
run stored procedures.
Procedures themselves do not contain 'set optimization low',
neither sessions from which procedures were created:
we really need maximized SQL plan, we just do not
want procedures to be reoptimized 'on the fly'
My understanding (from my previous experience and from
Informix documentation), is that SPL reoptomization is
controlled by 'optimizer' setting of a session that executes
the stored procedure. Am I wrong?
It looks like, any 'heavy' alter table (index creation,
constraint creation) of our 'online' tables immediately
causes -710 on all transactional clients. These clients ONLY
access the database through SPL.
Clients are written in C++ using Informix Object Interface libraries
------------------------------------------
Alexey Sonkin
Senior Database Administrator
> -----Original Message-----
> From: jlef...@us.ibm.com [mailto:jlef...@us.ibm.com]
> Sent: Wednesday, February 11, 2004 1:53 PM
> To: inform...@iiug.org
> Subject: Re: Whatcha' wanta have?????
>
sending to informix-list
Alexey Sonkin
Jonathan,
sending to informix-list
mos...@wellsfargo.com
> -----Original Message-----
> From: owner-inf...@iiug.org
> [mailto:owner-inf...@iiug.org] On Behalf Of jlef...@us.ibm.com
> Sent: Wednesday, February 11, 2004 11:53 AM
> To: inform...@iiug.org
> Subject: Re: Whatcha' wanta have?????
>
>
> mos...@wellsfargo.com wrote:
> > Related: Implement outstanding feature request to run "set
> optimization low"
> > from the *server* side.
>
> Can you elucidate this, possibly with the feature request (bug)
> number? Are you saying that you cannot run SET OPTIMIZATION LOW in a
> stored procedure? The request has to be processed by the server, so
> I'm confused...
>
> -=JL=-
>
DYNAMICALLY FROM ENGINE SIDE
We have an extremely critical high-txn-volume HA app that relies very
heavily on SP's. When we run update stats (we use dostats), the SP's have
to be re-optimized A LOT while the stats for the tables are being updated.
This causes slowdowns and lock contention (catalog tables locked while SP is
re-optimized). An "update statistics for procedure" *is* run after the
tables are all done.
Rather than putting "SET OPTIMIZATION LOW" in all of the application code,
it would be much better to be able to flip a switch in the engine (onmode
command??) that effectively turns on SET OPTIMIZATION LOW for *all* SP's,
until the switch is turned off again.
Is that more clear?
Thanks,
Paul Mosser
sending to informix-list
mos...@wellsfargo.com
Is that more clear?
sending to informix-list
mpruet
this turn into an Oracle vrs. IDS thread. We're seeing a lot of
really good ideas and I appreciate the input.
Thanks
M.P.
"Tim Schaefer" <t...@spamethnot.com> wrote in message news:<90qWb.77458$Ux4....@fe10.usenetserver.com>...
Mark D. Stock
Andrew Hamm wrote:
> How could i forget this one?
>
> case insensitive comparisons and collating order!
>
> I don't want that applied to CHAR and VARCHAR types, since they are
> fast'n'mean byte-wise datatypes. However, since NCHAR and NVARCHAR fields
> can have a GLS locales applied, they are ideal for this little extension.
>
> I cannot find an English Locale that has case-insensitive collation. I don't
> know about other european GLS locales, but I'd wager they are case sensitive
> too, but could profit from caseless.
Can't this be achieved using functional indexes?
Cheers,
--
Mark.
+----------------------------------------------------------+-----------+
| Mark D. Stock mailto:mds...@MydasSolutions.com |//////// /|
| Mydas Solutions Ltd http://MydasSolutions.com |///// / //|
| +-----------------------------------+//// / ///|
| |We value your comments, which have |/// / ////|
| |been recorded and automatically |// / /////|
| |emailed back to us for our records.|/ ////////|
+----------------------+-----------------------------------+-----------+
sending to informix-list
Mark D. Stock
Madison Pruet wrote:
> As a mild diversion from the IDS-DB2 conversion thread, its time for one of
> my more favorite exercises. ;-)
>
> We are nearing the end of the coding cycle for IDS 9.5. We've got a whole
> bunch of really cool stuff in place and - well - its time to get input from
> you guys as to what you want to see in the 9.6 release.
>
> Now, I know that everyone's favorite thing is going to be "marketing", but
> I'm in development. So I need to talk features and functionality. So feel
> free to send them on in.
Automatically use SET OPTIMIZATION FIRST_ROWS when using SELECT FIRST n,
where n is a small percentage of the total number of records in the table.
I guess the opposite might be applicable as well -- Automatically use SET
OPTIMIZATION ALL_ROWS when NOT using SELECT FIRST n. Just in case someone
forgot to switch off FIRST_ROWS optimisation. ;-)
Sosnowski Robert
As for 9.6 I see 2 important things:
1. Web services
2. BLOB literals
ad. 1
Idea behind stored procedures in SQL database is very similar to that of Web
services. So it seems quite natural that database should expose its stored
procedures as WEB services.
I see that this is:
- useful even in Intranet,
- becoming standard.
Currently in my company we will migrate our database application to .NET.
Even for me as the beginner in .NET concept of exposing application API in
Web Services instead of stored procedures seems straightforward. It would be
even easier if database would support it natively.
It is becoming standard. DB2 and Oracle already have it. Also BEA Tuxedo
middleware. MSSQL and Sybase announced. Don't let Informix be an isolated
data island!
There are some peculiarities with Web services: there are two different
reference platforms:
- .Net
- Apache-Tomcat (java).
As Informix works not only on NT then choice is clear. I'm only afraid about
java: I would prefer implementation not based on java if possible. I just
prefer simpler but faster and more reliable solution.
DB2 also have interesting extension: you can use web service similar as
stored procedure.
In Informix it extends stored procedure implementation method. Currently
Informix have:
- SPL;
- J/SQL (Internet Foundation only);
- C databalades.
I want to add to this list:
- Web Services.
ad 2.
For BYTE, TEXT, CLOB, and BLOB types there are no literals. Why? It just
complicates simple things.
For TEXT and CLOB it would be the same literal as for char(30000) column.
For BYTE and BLOB it have to be some hex literal.
Best Regards,
Robert Sosnowski
IT specialist CK BZ WBK,
e-mail: Robert.S...@bzwbk.pl
ul. Pl. Andersa 5, 61-894 Poznań
phone +48 61 856 54 09
fax +48 61 856 52 36
>>As a mild diversion from the IDS-DB2 conversion thread, its time for one
of
>>my more favorite exercises. ;-)
>>
>>We are nearing the end of the coding cycle for IDS 9.5. We've got a whole
>>bunch of really cool stuff in place and - well - its time to get input
from
>>you guys as to what you want to see in the 9.6 release.
>>
>>Now, I know that everyone's favorite thing is going to be "marketing", but
>>I'm in development. So I need to talk features and functionality. So
feel
>>free to send them on in.
>>
>>Just an FYI - I'll be away for a while and won't be able to get email via
my
>>comcast email address. But, I'll be following the newsgroup rather
closely.
>>
>>Also, next week I'll be in some planning meeting. So getting responses
back
>>fairly quickly would really help.
>>
>>Thanks
>>
>>M.Pruet
===========================
Wiadomość ta oraz wszelkie załączone do niej pliki są poufne i mogą być prawnie chronione.
Jeżeli nie jest Pan/Pani zamierzonym adresatem niniejszej wiadomości, nie może Pan/Pani jej ujawniać,
kopiować, dystrybuować ani też w żaden inny sposób udostępniać lub wykorzystywać.
O błędnym zaadresowaniu wiadomości prosimy niezwłocznie poinformować nadawcę i usunąć wiadomość.
===========================
This email and any attached files are confidential and may be legally privileged.
If you are not the intended recipient, any disclosure, reproduction, copying, distribution,
or other dissemination or use of this communication is strictly prohibited. If you have received this transmission in error please notify the sender immediately and then delete this email.
sending to informix-list
sending to informix-list
sending to informix-list
Serge Rielau
Bryce,
Can you illustrate this with an example and intended semantics?
The rule is that any local element in the select list which is not
aggregated needs to be grouped. Thus it needs to be in the GROUP BY
clause. If you name, say, a column which is neither grouped upon nor
aggregated, what do you expect as a result?
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Andrew Hamm
> FYI - there are many at Oracle that would also argue that the ability
> to control logging at a granular level is a Bad Thing (TM), especially
> after seeing some of the messes customers can get themselves into with
> nologging. This would make for an interesting industry debate at some
> stage.
Which is exactly why I think letting this one sneak in will be trouble.
People who use RAW tables in IDS should presumably have read the notes about
the risks. If the notes aren't clear enough, then the IDS doco team should
put a stern warning in there. The raw table feature is a very interesting
way to split off dangerous activity from the strong safety of "normal" IDS
functioning.
[reply to Tim will take a bit more thought...]
Mark D. Stock
Andrew Hamm wrote:
> How could i forget this one?
>
> case insensitive comparisons and collating order!
>
> I don't want that applied to CHAR and VARCHAR types, since they are
> fast'n'mean byte-wise datatypes. However, since NCHAR and NVARCHAR fields
> can have a GLS locales applied, they are ideal for this little extension.
>
> I cannot find an English Locale that has case-insensitive collation. I don't
> know about other european GLS locales, but I'd wager they are case sensitive
> too, but could profit from caseless.
Can't this be achieved using functional indexes?
Cheers,
--
Mark.
+----------------------------------------------------------+-----------+
| Mark D. Stock mailto:mds...@MydasSolutions.com |//////// /|
| Mydas Solutions Ltd http://MydasSolutions.com |///// / //|
| +-----------------------------------+//// / ///|
| |We value your comments, which have |/// / ////|
| |been recorded and automatically |// / /////|
| |emailed back to us for our records.|/ ////////|
+----------------------+-----------------------------------+-----------+
Mark D. Stock
Madison Pruet wrote:
> As a mild diversion from the IDS-DB2 conversion thread, its time for one of
> my more favorite exercises. ;-)
>
> We are nearing the end of the coding cycle for IDS 9.5. We've got a whole
> bunch of really cool stuff in place and - well - its time to get input from
> you guys as to what you want to see in the 9.6 release.
>
> Now, I know that everyone's favorite thing is going to be "marketing", but
> I'm in development. So I need to talk features and functionality. So feel
> free to send them on in.
Automatically use SET OPTIMIZATION FIRST_ROWS when using SELECT FIRST n,
where n is a small percentage of the total number of records in the table.
I guess the opposite might be applicable as well -- Automatically use SET
OPTIMIZATION ALL_ROWS when NOT using SELECT FIRST n. Just in case someone
forgot to switch off FIRST_ROWS optimisation. ;-)
Cheers,
Andrew Hamm
>
> Can't this be achieved using functional indexes?
ummmmm, we'd have to deliver 9.X to all customers....
Andrew Hamm
> Mark D. Stock wrote:
> >
> > Can't this be achieved using functional indexes?
>
> ummmmm, we'd have to deliver 9.X to all customers....
and we'd have to change ALLLLLLLL the code to have something like
and upshift(cats_name) = upshift(?)
instead of
and cats_name = ?
wouldn't we? I've not used functional indexes before (note to self...) but
my understanding is that the engine needs to recognise an expression that is
solvable with the index as it was defined.
Also, can a functional index solve this?
and cats_name matches "tidd*l*s"
See, we want case to be ignored. SOMETIMES. When we ask for it ONLY. Without
having to rewrite lots of code.
Andrew Hamm
>
> The ability to control logging is what makes IDS the most totally
> controlable database on the planet. None of the other databases
> worth mentioning, except maybe possibly Oracle has the flexibility
> when it comes to transaction logs. I'm not discounting or dismissing
> your points, but maybe only pointing out please appreciate the
> difference IDS brought to the table, for me at least, it's now all
> academic. The idea, the luxury of arguing the ability to log or not
> to log, it makes me cry. :-(
I'm sorry that your company panicked and changed engines unncessarily. One
of our teams that has recently taken on IBM Lotus Domino in a big way would
be laughing at suggestions that IBM buys products and then kills them off.
Quite the contrary. But this has been gone into a thousand times before...
If anyone wants to reply, PLEASE rename the subject immediately so I can
killfile it at the first wiff of smoke. I will not be partaking in further
discussion on whether IBM is going to kill IDS.
> Enjoy the acedemics while the product lingers, once it's gone, well,
> it's really the end of a great product.
Won't be academic for us while-ever we still recommend IDS as the preferred
engine, and the customers nearly always accept that unless they already have
an extensive in-house Oracle or MSV or .... system. Thankyou FourJs for
allowing us to service these new customers too.
Andrew Hamm
>
> Also, can a functional index solve this?
>
> and cats_name matches "tidd*l*s"
>
That example would be better with
and cats_name matches "*tidd*l*s"
so that it finds these records:
Tiddles
TIddles
tiddles
TIDDLES
Mr Tiddles
Sir Tiddles
Tiddlz
Tsutomu Ogiwara
Support create or replace statemnemt in table, procedure and etc.
--
Tsutomu Ogiwara from Tokyo Japan.
ICQ#:168106592
_________________________________________________________________
STOP MORE SPAM with the new MSN 8 and get 2 months FREE*
http://join.msn.com/?page=features/junkmail
sending to informix-list
Tsutomu Ogiwara
sending to informix-list
sending to informix-list
Tim Schaefer
My company didn't panic and switch. I have been working in a
multi-million dollar company that was using until I arrived,
strictly Microsoft products, SQL-Server being one of them.
When I came in, the first thing I started doing was figuring
out how Linux could be used, ( without jeopardizing my job )
which would open the door for an alternative database product
that would be enterprise quality, and eventually supplant
SQL-Server. Today almost a year and a half later, we are getting
closer to that reality, with Linux proving itself by the numbers.
We had an ETL tool running miserably on Windows that increased
its performance immediately 3x on some jobs and others 10x
simply by moving to Linux.
This got management excited, and has now saved us a lot of
real money by moving to Linux. But it was the only way to
get there, by the numbers.
We haven't made a final decision on what that database will
be, but I think it's safe to say it will be between Oracle
and IBM. Both are on notice to show us the money, by that
I mean they each have to go through a Proof-of-Concept and prove
to us their merits. Being the kind of individual that I am,
I am moving slowly through the murky water of bs to get to
the right solution for our company. Informix will never be
an option for us, until the magical fantasy of Informix being
marketed occurs, and proof that it won't be discontinued. We
couldn't even find people to work on Informix if we had to, and
I'm certainly not going to be the only one with that expertise
supporting it. That would put me and my family at risk, thusly
I couldn't care less about Informix, and certainly won't even
consider them in our evaluation process. Informix doesn't
exist as far as I'm concerned for our evaluation, and it's not
an emotional decision, just business.
You should someday have an opportunity to work in an all Microsoft
shop, with no UNIX or Linux of any kind. You will be amazed at how
problem solving occurs in this environment, and the low expectation
of performance from the database. It's as if the database
marketplace is starting all over, this time around without Informix.
When people here get angry at IBM, it shows me they are not looking
at the business perspective. The more I think about it the more
I think that IBM must be looking at every angle of Informix
technology and aggressively wanting to commoditize it into either
DB2 or something nearby. They understand the meaning of open
systems, I'm sure they see Informix as something they can fold
into that thinking. But will it ever get marketed? Probably
not, it's just not good business sense at this point.
South Florida is supposedly still infected with Informix in some
shops, ( bitter enders! ) but according to our IBM rep ( gads the
cat is out now ) nothing really significant and no new customers.
IBM and Oracle have both been extremely nice to us, and extremely
interested in our business. Very different attitude from when the
arroganti from Informix were around. DB2 may not be perfect, but
in this market who said it has to be? Who says it has to be as
fast as XPS? Is that even relevant? Probably not.
Tim
"Andrew Hamm" <ah...@mail.com> wrote in message news:c0ekfn$16c1b0$1...@ID-79573.news.uni-berlin.de...
Dorn Bhechsonggram
How about a LIMIT clause (LIMIT row_count OFFSET offset) that can be used to
constrain the number of rows returned by the SELECT statement like MySQL,
PostgreSQL.
Dorn B.
----- Original Message -----
From: "Sosnowski Robert" <robert.s...@bzwbk.pl>
To: <inform...@iiug.org>
Sent: Wednesday, February 11, 2004 05:14 AM
Subject: Re: Whatcha' wanta have?????
> >>As a mild diversion from the IDS-DB2 conversion thread, its time for one
> of
> >>my more favorite exercises. ;-)
> >>
> >>We are nearing the end of the coding cycle for IDS 9.5. We've got a
whole
> >>bunch of really cool stuff in place and - well - its time to get input
> from
> >>you guys as to what you want to see in the 9.6 release.
> >>
> >>Now, I know that everyone's favorite thing is going to be "marketing",
but
> >>I'm in development. So I need to talk features and functionality. So
> feel
> >>free to send them on in.
> >>
Dorn Bhechsonggram
Dorn B.
sending to informix-list
sending to informix-list
Jack Parker
----- Original Message -----
From: "Tim Schaefer" <t...@spamethnot.com>
To: <inform...@iiug.org>
Sent: Wednesday, February 11, 2004 9:15 AM
Subject: Re: Whatcha' wanta have?????
> Mark,
>
> I don't know Oracle well enough to talk competently about redo logs,
> but it's my limited understanding they are a bit different than the
> logical logs in Informix. As I understand it Oracle people live and
> die with creative log shipping that most Informix people would never
> even think of doing with logical logs. This is a good thing about
> Oracle, as I understand it, but the comparison is not quite apples
> to apples.
-- slightly OT, but of interest (at least to me)
I was just reading something about this today. They have an undo log -
which is analoguous to our physical log, and a redo log which is analoguous
to our logical log (did I miss a third one here?). Something cute that they
do is allow you to mirror the logs - set up groups and the engine will write
to all members of the group - all three or 15. We would do the same thing
by mirroring the dbspace or the underlying disk.
Also, evidently their desire for 'cooked' files vs raw disk stems from the
ease of backup. Oracle opts for standard utilities to perform cold backups
(database down during backup) - so just copy the file off. They do support
a hot backup - roughly the same way Informix does, but it looks fairly
complex to set up (and I get a queasy feeling reading the description of
events). They don't have anything to compare to the 'simplicity' of onbar
or ontape.
-- sorry, backups are on my mind right now - amanda is, like, totally cool.
(http://www.amanda.org/)
cheers
j.
sending to informix-list
Andrew Hamm
> How about a LIMIT clause (LIMIT row_count OFFSET offset) that can be
> used to constrain the number of rows returned by the SELECT statement
> like MySQL, PostgreSQL.
We have
select first 100 * from ....
however there is no offset; noticable by its absence.
I can see why; it almost makes the engine pull up the first part of the
selection set anyway. I wonder if there is a smart way to do it in Informix.
How do other engines avoid fetching th first 100 records just to get to the
2nd lot of 100 records?
Andrew Hamm
Like i said, i'm not getting involved, but i'll make sure the subject
doesn't drown out Madison's thread. Please please please follow up to Tim on
this subject.
what do the netgeeks say?
oh yeah
*subject plonked*
Jack Parker
cheers
j.
sending to informix-list
sending to informix-list
Sosnowski Robert
Web services:
>> It is becoming standard. DB2 and Oracle already have it. Also BEA Tuxedo
>> middleware. MSSQL and Sybase announced. Don't let Informix be an isolated
>> data island!
>
Definitely not ANSI SQL standard. I see that all big RDBMS have it (or soon
will have).
It does not mean that because others have then Informix have to have it too.
But it means that at Informix should evaluate such a possibility.
And my personal view is that Informix should implement Web services somehow.
I see that it is useful : many database applications are currently using web
services.
So in case of evaluating "which database will be best for our new
application" this feature would be quite important.
>> DB2 also have interesting extension: you can use web service similar as
>> stored procedure.
>> In Informix it extends stored procedure implementation method. Currently
>> Informix have:
>> - SPL;
>> - J/SQL (Internet Foundation only);
>> - C databalades.
>> I want to add to this list:
>> - Web Services.
>Implement them as a DataBlade?
I do not know internals of IDS implementation. It seems that DataBlade would
be good for this.
>> ad 2.
>> For BYTE, TEXT, CLOB, and BLOB types there are no literals. Why? It just
>> complicates simple things.
>> For TEXT and CLOB it would be the same literal as for char(30000) column.
>> For BYTE and BLOB it have to be some hex literal.
>
>This *is* a huge PITA.
Not quite.
I do not want to introduce 2GB literals. Current limitation - 255 characters
is ok.
Suppose you have char_col1 as char(30000). Then I can update it in following
way:
"set char_col1=NULL", "set char_col1='' ", "set char_col1='N/A' ",
"set char_col1='NONE'", "set char_col1[1,2]='TX'"
Above is typical: large column often uses short literals.
With TEXT type I can't do this. So people implements their own workarounds.
Typical approach is: instead of using TEXT we have several varchar columns
and value is splitted between them.
This is useful with not extra large BLOBS only.
I just want to have such possibilities for TEXT and CLOB too.
And Informix already have some kind of BLOB literals. In ODBC TEXT value is
treated similarly as long varchar, which means that in ODBC BLOB literal can
be used. Unfortunately I had problems with my ODBC and I couldn't verify the
documentation.
So this is strange: ODBC connection have BLOB literals, native connection
does not.
Better is to have support on server side so stored procedures would use
literals too.
Literals for BYTE and BLOB: I don't have urgent need for this. Just for
completness it would be usefull to write in update:
"set byte_col1=#FFAA"
Robert Sosnowski
Sosnowski Robert
Robert Sosnowski
sending to informix-list
sending to informix-list
Obnoxio The Clown
>
> Web services:
>>> It is becoming standard. DB2 and Oracle already have it. Also BEA Tuxedo
>>> middleware. MSSQL and Sybase announced. Don't let Informix be an
>>> isolated data island!
>>
>>It is becoming standard? Which standard?
> Definitely not ANSI SQL standard. I see that all big RDBMS have it (or
> soon will have).
> It does not mean that because others have then Informix have to have it
> too. But it means that at Informix should evaluate such a possibility.
> And my personal view is that Informix should implement Web services
> somehow. I see that it is useful : many database applications are
> currently using web services.
> So in case of evaluating "which database will be best for our new
> application" this feature would be quite important.
Yes, I can remember when the promise of run-anywhere Java was going to
redefine the whole application development business. Everyone made a JVM
available to developers so that they could port their code anywhere. Hands
up the number of people who use it, please?
I can see web services being the next Java: loads of hype, it will find a
niche and the rest of the world will move on looking for the Next Big Thing
(tm).
As a totally irrelevant aside, I can't believe that Java performance still
sucks as bad as it does and that there are so many version-related issues.
It seems that every sodding Java application I get needs a different JRE.
And Java is on its way out. We lost our passion for application development
in C because of this crap? :o(
>>> DB2 also have interesting extension: you can use web service similar as
>>> stored procedure.
>>> In Informix it extends stored procedure implementation method. Currently
>>> Informix have:
>>> - SPL;
>>> - J/SQL (Internet Foundation only);
>>> - C databalades.
>>> I want to add to this list:
>>> - Web Services.
>>Implement them as a DataBlade?
> I do not know internals of IDS implementation. It seems that DataBlade
> would be good for this.
Well, write one then?
>>> ad 2.
>>> For BYTE, TEXT, CLOB, and BLOB types there are no literals. Why? It just
>>> complicates simple things.
>>> For TEXT and CLOB it would be the same literal as for char(30000)
>>> column. For BYTE and BLOB it have to be some hex literal.
>>
>>This *is* a huge PITA.
> Not quite.
Oh yes it is. It's probably one of the most FAQs out there. I think it's
aggravating, too. :o)
> I do not want to introduce 2GB literals. Current limitation - 255
> characters is ok.
Why not?
Dirk Moolman
This makes me think of another simple thing:
Increasing the capability of: select first n * from tablename
..... the capability of selecting subsets of data without too much trouble
-----Original Message-----
From: owner-inf...@iiug.org [mailto:owner-inf...@iiug.org] On Behalf Of Dorn Bhechsonggram
Sent: Thursday, February 12, 2004 4:18 AM
Subject: Re: Whatcha' wanta have?????
How about a LIMIT clause (LIMIT row_count OFFSET offset) that can be used to
constrain the number of rows returned by the SELECT statement like MySQL,
PostgreSQL.
Dorn B.
________________________________
<< ella for Spam Control >> has removed 1291 Spam messages and set aside 239 Newsletters for me
You can use it too - and it's FREE! www.ellaforspam.com
sending to informix-list
Tim Schaefer
So Malcom turned his self-righteous-zealot-asshole-moderator-job
over to YOU?
I find it annoying when arrogant-lil-bitches say they're not getting
involved yet get involved and then pontificate dismissals as if they
are in charge, or their opinion is more important. Like you're the
boss here or something? Your dismissive attitude is annoying but the
sucking up and political-asslicking in your posts are actually more
offensive than anything.
In case you weren't aware there are actually people out there that
do not have to look for group consensus on their opinions, and are
willing to stand on their own two feet, right or wrong, without
whimpering to the community for support. I don't expect anyone to
agree with me, in fact, I look forward to people disagreeing. After
all if we were all the same wouldn't that be boring.
You said things to me that were inaccurate, then when I respond you
suddenly, spinelessly duck out, and then add your typical political
sucking up. You must have to carry a towel around wiping your face
off spending so much time up other-peoples-asses.
Anyway, I know you're trying to reach sainthood by the end of year,
please do me a small favor don't go and whine and weeze to the
community that I stood up to you. Go ahead, do the right thing,
pull your head out, and have a hearty, piping hot cup of STFU. I
look forward to a response to ME without your typical request for
community support. That would show me some leadership, but I'm
willing to bet you'll whimp out. You won't be the first, that's
for sure.
Tim
"Andrew Hamm" <ah...@mail.com> wrote in message news:c0f098$o7fjg$1...@ID-79573.news.uni-berlin.de...
Dirk Moolman
-----Original Message-----
PostgreSQL.
Dorn B.
________________________________
sending to informix-list
sending to informix-list
Obnoxio The Clown
Hey Tim, I reckon it's time to move back onto the decaf. :o)
> "Andrew Hamm" <ah...@mail.com> wrote in message
> news:c0f098$o7fjg$1...@ID-79573.news.uni-berlin.de...
>>
>> Like i said, i'm not getting involved, but i'll make sure the subject
>> doesn't drown out Madison's thread. Please please please follow up to Tim
>> on this subject.
>>
>> what do the netgeeks say?
>> oh yeah
>>
>> *subject plonked*
--
Tim Schaefer
"Obnoxio The Clown" <obn...@hotmail.com> wrote in message news:c0fon4$10eudk$1...@ID-64669.news.uni-berlin.de...
>
> Hey Tim, I reckon it's time to move back onto the decaf. :o)
>
Obnoxio The Clown
> Oh c'mon, I spent 20 minutes on that one. The re-writes, it was painful I
> tells ya.
In that case, you need to invest in a spell checker. :o)
Jonathan Leffler
> Bryce Stenberg wrote:
> > One thing I would like to see that has bugged me on and off over the years
> > is to do with the 'group by' clause in SQL (assuming it has not already been
> > dealt with in 9.4, we are only running 7.31 currently and move to 9.4 this
> > year).
> >
> > When doing selects with a 'group by', everything in your select list (maybe
> > entire records with many columns) has to be listed in your 'group by'
> > clause, even though you only want to group by one or two of the selected
> > columns. This is most frustrating.
> >
> > So, can we have 'group by' where you only need list the columns you actually
> > want to group by while selecting many more columns than listed in the group
> > by (or does this break some SQL standard or something?).
Succinctly, it breaks the SQL standard, but if there's a bomb-proof
way of providing the functionality, then it could be considered.
> Can you illustrate this with an example and intended semantics?
> The rule is that any local element in the select list which is not
> aggregated needs to be grouped. Thus it needs to be in the GROUP BY
> clause. If you name, say, a column which is neither grouped upon nor
> aggregated, what do you expect as a result?
This is the key point. Suppose we have our classic Order and
OrderItem tables. The columns in the Order table are dependent on the
primary key - OrderNo - of course.
Therefore, the following pseudo-SQL should produce a defined answer:
SELECT O.OrderNo, O.Customer, O.OrderDate, O.DeliveryDate,
SUM(I.ItemCharge) AS TotalCharge
FROM Order O, OrderItem I
WHERE O.OrderNo = I.OrderNo
GROUP BY O.OrderNo;
For each value in O.OrderNo, there will only be a single value of
O.Customer, etc. So, there is no need to specify them in the grouping
clause. But the DBMS has to be able to determine that the O.OrderNo
column is a candidate key of the Order table.
Extending that to a three-table join is left as an exercise for the
reader.
-=JL=-
Jonathan Leffler
> This makes me think of another simple thing:
>
> Increasing the capability of: select first n * from tablename
>
> ..... the capability of selecting subsets of data without too much trouble
Can you explain what you're thinking of? What do you want that Dorn
Bhechsonggram's suggestion does not provide? Or where do you want to
use the notation that you can't in PostgreSQL and MySQL?
Thanks for the extra information.
And thanks for the valuable ideas you're all submitting. No promises,
of course, but we're definitely taking notes.
-=JL=-
Serge Rielau
> Serge Rielau <sri...@ca.eye-be-em.com> wrote:
>
>>Bryce Stenberg wrote:
>>
>>>One thing I would like to see that has bugged me on and off over the years
>>>is to do with the 'group by' clause in SQL (assuming it has not already been
>>>dealt with in 9.4, we are only running 7.31 currently and move to 9.4 this
>>>year).
>>>
>>>When doing selects with a 'group by', everything in your select list (maybe
>>>entire records with many columns) has to be listed in your 'group by'
>>>clause, even though you only want to group by one or two of the selected
>>>columns. This is most frustrating.
>>>
>>>So, can we have 'group by' where you only need list the columns you actually
>>>want to group by while selecting many more columns than listed in the group
>>>by (or does this break some SQL standard or something?).
>
>
> Succinctly, it breaks the SQL standard, but if there's a bomb-proof
> way of providing the functionality, then it could be considered.
Indeed.
>>Can you illustrate this with an example and intended semantics?
>>The rule is that any local element in the select list which is not
>>aggregated needs to be grouped. Thus it needs to be in the GROUP BY
>>clause. If you name, say, a column which is neither grouped upon nor
>>aggregated, what do you expect as a result?
>
>
> This is the key point. Suppose we have our classic Order and
> OrderItem tables. The columns in the Order table are dependent on the
> primary key - OrderNo - of course.
>
> Therefore, the following pseudo-SQL should produce a defined answer:
>
> SELECT O.OrderNo, O.Customer, O.OrderDate, O.DeliveryDate,
> SUM(I.ItemCharge) AS TotalCharge
> FROM Order O, OrderItem I
> WHERE O.OrderNo = I.OrderNo
> GROUP BY O.OrderNo;
>
> For each value in O.OrderNo, there will only be a single value of
> O.Customer, etc. So, there is no need to specify them in the grouping
> clause. But the DBMS has to be able to determine that the O.OrderNo
> column is a candidate key of the Order table.
>
> Extending that to a three-table join is left as an exercise for the
> reader.
That would be well defined indeed. IMHO however it's a slippery slope.
The correctness of the statement would depend on the existence of a
unique constraint.
There are other cases where the SQL Compiler can prove transitive
uniqueness, e.g. through functional dependencies, theorem proving and
check constraints.
If I had to write the query I woudl write it as such:
SELECT O.OrderNo, O.Customer, O.OrderDate, O.DeliveryDate,
I.TotalCharge
FROM Order O, (SELECT OrderNo, SUM(ItemCharge) AS TotalCharge
FROM OrderItem
GROUP BY OrderNo) AS I
WHERE O.OrderNo = I.OrderNo;
So I see two issus here:
1. As a developer I cannot see the syntactic correctness of the query
anymore.
2. The query could be correct on one version of the product and not on
another, dependending on how the compiler capabilities improve, or God
forbid, regress.
The second point would break the proposal's neck in the standard.
Lastly, and this is my personal opinion entirely, semantic error
checking belongs into the compiler front end, not the back-end.
The back-end should only complain about not having enough resources to
satisfy a perfectly legal query.
But I maybe biased towards what I know.
Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Obnoxio The Clown
> Lastly, and this is my personal opinion entirely, semantic error
> checking belongs into the compiler front end, not the back-end.
> The back-end should only complain about not having enough resources to
> satisfy a perfectly legal query.
> But I maybe biased towards what I know.
Does dbaccess compile my SQL before it passes it through?