Next version of IDS (post 11.50) has WHAT?

Hey ladies, If I can just interrupt your bickering for just a moment? I would like to see an official (or semi-official (hell, even unofficial)) feature list for the upcoming IDS release of 2010. I hear it's codename is Panther?

The feature list is currently under NDA and likely still in flux. You'll have to contact IBM and sign one and/or join the early evaluation program. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Oct 13, 2009 at 2:10 AM, Andrew Clarke <aclarke@civica.com.au>wrote: > Hey ladies, > > If I can just interrupt your bickering for just a moment? I would like to > see > an official (or semi-official (hell, even unofficial)) feature list for the > upcoming IDS release of 2010. I hear it's codename is Panther? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00151747bfe23ca8d00475cea3b3

> The feature list is currently under NDA and likely still in flux. You'll > have to contact IBM and sign one and/or join the early evaluation program. > hmmm ok. we got this through a game of chinese whispers from an IBM rep: "For IDS, there is not support yet for case insensitive searches at the database level. That is a feature for the next release of IDS, code named Panther, due out in the next year. There is the possibility of using the text datablade to do these, which is free and comes shipped with the product. The text datablade interfaces thru the application program function bts_contains .... which would be executed in each sql statement where appropriate. " but injecting thousands of calls to to bts_contains, not to mention the behavioural change from it's fuzzy nature is just not appropriate. Bring on Panther! At the moment, MS Squeel Server is getting all our new sales just over this case insensitivity issue. We would prefer to get them on IFX.

Andrew Clarke wrote: >> The feature list is currently under NDA and likely still in flux. You'll >> have to contact IBM and sign one and/or join the early evaluation program. >> > > hmmm ok. we got this through a game of chinese whispers from an IBM rep: > > "For IDS, there is not support yet for case insensitive searches at the > database level. That is a feature for the next release of IDS, code named > Panther, due out in the next year. > > There is the possibility of using the text datablade to do these, which is > free and comes shipped with the product. The text datablade interfaces thru > the application program function bts_contains .... which would be executed > in each sql statement where appropriate. " > > but injecting thousands of calls to to bts_contains, not to mention the > behavioural change from it's fuzzy nature is just not appropriate. Bring on > Panther! At the moment, MS Squeel Server is getting all our new sales just > over this case insensitivity issue. We would prefer to get them on IFX. You've been able to do this since, I guess, 9.20 (possibly even earlier) with a functional index. No calls to BTS required. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

> > You've been able to do this since, I guess, 9.20 (possibly even earlier) > with a functional index. No calls to BTS required. > really? show me how select * from cust where cust.name = ? can be done without having to rewrite as select * from cust where cust.name = downshift(?) so that it picks up a functional index on downshift() once again, rewrite is a pipe dream. I'd really like to know how to do it without rewrites; whoever comes up with an answer has been promised an excellent outcome in the next pay review.

Andrew Clarke wrote: >> You've been able to do this since, I guess, 9.20 (possibly even earlier) >> with a functional index. No calls to BTS required. >> > > really? show me how OK, I'm stumped. How is a case insensitive search ANSI compliant? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

> Andrew Clarke wrote: > >> You've been able to do this since, I guess, 9.20 (possibly even earlier) > >> with a functional index. No calls to BTS required. > > > > really? show me how > > OK, I'm stumped. How is a case insensitive search ANSI compliant? Who said anything about compliance? We're talking market forces!

Andrew Clarke wrote: >> Andrew Clarke wrote: >>>> You've been able to do this since, I guess, 9.20 (possibly even earlier) >>>> with a functional index. No calls to BTS required. >>> really? show me how >> OK, I'm stumped. How is a case insensitive search ANSI compliant? > > Who said anything about compliance? We're talking market forces! Ah. Every time I hear something like that, a little part of me dies. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean.

> Ah. Every time I hear something like that, a little part of me dies. > > -- > Cheers, You and me both. Cheers indeed.

On Tue, 2009-10-13 at 19:05 -0400, Andrew Clarke wrote: > > > > You've been able to do this since, I guess, 9.20 (possibly even earlier) > > with a functional index. No calls to BTS required. > really? show me how > select * from cust where cust.name = ?> can be done without having to rewrite as > select * from cust where cust.name = downshift(?) > so that it picks up a functional index on downshift()> once again, rewrite is a pipe dream. Eh? Seriously, who develops an application, tests the application, deploys an application... AND THEN realizes the search needs to be case insensitive? How do you get into a situation where you need to rewrite for that? [if that is what is driving M$-SQL sales (a) I'll eat my hat, (b) the Microsoft cool-aid is even more potent then I ever imagined possible, and (c) Microsoft customers are deeply stupid.] I've been doing case insensitive searches, in PostgreSQL and Informix, for years. In 94% of the cases you can make your ORM take care of it for you anyway. > I'd really like to know how to do it without rewrites; whoever comes up with > an answer has been promised an excellent outcome in the next pay review. In PostrgeSQL it is just create index person_name_idx on person (lower(name));explain select company_id from person where lower(name) = 'adam'; ------------------------------------------------------------------------------ Bitmap Heap Scan on person (cost=4.29..19.06 rows=4 width=4) Recheck Cond: (lower((name)::text) = 'adam'::text) -> Bitmap Index Scan on person_name_idx (cost=0.00..4.29 rows=4 width=0) Index Cond: (lower((name)::text) = 'adam'::text) The system merrily used our index. I don't have an Informix system at my finger tips but I recall the syntax being nearly identical.

Just for curiosity, and if you care to spare me the trouble of looking up... How does SQL Server do that? Your query per si, if it matches "test" against "TEST" would be a wrong result in any RDBMS. Unless of course you have some kind of column or index property to specify that it should be case insensitive... And if that is so, how does it handle the search? Does it write the index using UPPER or LOWER, and then converts the argument so that an index match can be found? Thanks in advance. On Wed, Oct 14, 2009 at 12:05 AM, Andrew Clarke <aclarke@civica.com.au>wrote: > > > > You've been able to do this since, I guess, 9.20 (possibly even earlier) > > with a functional index. No calls to BTS required. > > > > really? show me how > > select * from cust where cust.name = ?> > can be done without having to rewrite as > > select * from cust where cust.name = downshift(?)> > so that it picks up a functional index on downshift() > > once again, rewrite is a pipe dream. > > I'd really like to know how to do it without rewrites; whoever comes up > with > an answer has been promised an excellent outcome in the next pay review. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --000e0cd573400ee46b0475da0675

The select statement below you would want to do case insensitive search. Without changing the application this means ALL character column to string would be case insensitive. Is this what you want?? select * from cust where cust.name = ? I have not tried this but had one idea that sounds like it would work. 1. create a distinct data type called ichar. 2. Overload the = (equal) operator for ichar,char with a case insensitive compare John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 10/13/2009 04:05:59 PM: > [image removed] > > Re: Next version of IDS (post 11.50) has WHAT? [17513] > > Andrew Clarke > > to: > > ids > > 10/13/2009 04:06 PM > > Sent by: > > ids-bounces@iiug.org > > Please respond to ids > > > > > You've been able to do this since, I guess, 9.20 (possibly even earlier) > > with a functional index. No calls to BTS required. > > > > really? show me how > > select * from cust where cust.name = ?> > can be done without having to rewrite as > > select * from cust where cust.name = downshift(?)> > so that it picks up a functional index on downshift() > > once again, rewrite is a pipe dream. > > I'd really like to know how to do it without rewrites; whoever comes up with > an answer has been promised an excellent outcome in the next pay review. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

> On Tue, 2009-10-13 at 19:05 -0400, Andrew Clarke wrote: > > Eh? Seriously, who develops an application, tests the application, > deploys an application... AND THEN realizes the search needs to be case > insensitive? How do you get into a situation where you need to rewrite > for that? [if that is what is driving M$-SQL sales (a) I'll eat my hat, > (b) the Microsoft cool-aid is even more potent then I ever imagined > possible, and (c) Microsoft customers are deeply stupid.] > > I've been doing case insensitive searches, in PostgreSQL and Informix, > for years. In 94% of the cases you can make your ORM take care of it > for you anyway. > I feel the urge to channel OTC, but I'll restrain myself. > In PostrgeSQL it is just > And we care ab.... no, restraint. Cheers

From what I've looked in a quick search MS SQL Server does case insensitive search by default (not sure how it creates an index...). If we want to do a case sensitive search we can specifiy a collation... Now... In Informix we can create indexes with specific collations... would it be technically possible to create a custom collation definition that handle this? If so, this would take care of index matches, but wouldn't help on non-index searches... Your solution is very interesting (assuming it works). Regards. On Wed, Oct 14, 2009 at 1:22 AM, John Miller iii <miller3@us.ibm.com> wrote: > The select statement below you would want to > do case insensitive search. Without changing the > application this means ALL character column to string > would be case insensitive. Is this what you want?? > > select * from cust where cust.name = ?> > I have not tried this but had one idea that > > sounds like it would work. > > 1. create a distinct data type called ichar. > > 2. Overload the = (equal) operator for ichar,char > > with a case insensitive compare > > John F. Miller III > STSM, Support Architect > miller3@us.ibm.com > 503-578-5645 > IBM Informix Dynamic Server (IDS) > > ids-bounces@iiug.org wrote on 10/13/2009 04:05:59 PM: > > > [image removed] > > > > Re: Next version of IDS (post 11.50) has WHAT? [17513] > > > > Andrew Clarke > > > > to: > > > > ids > > > > 10/13/2009 04:06 PM > > > > Sent by: > > > > ids-bounces@iiug.org > > > > Please respond to ids > > > > > > > > You've been able to do this since, I guess, 9.20 (possibly even > earlier) > > > with a functional index. No calls to BTS required. > > > > > > > really? show me how > > > > select * from cust where cust.name = ?> > > > can be done without having to rewrite as > > > > select * from cust where cust.name = downshift(?)> > > > so that it picks up a functional index on downshift() > > > > once again, rewrite is a pipe dream. > > > > I'd really like to know how to do it without rewrites; whoever comes up > with > > an answer has been promised an excellent outcome in the next pay review. > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --000e0cd5c0768957a70475da46d3

> Just for curiosity, and if you care to spare me the trouble of looking > up... How does SQL Server do that? Your query per si, if it matches "test" > against "TEST" would be a wrong result in any RDBMS. I think SQL server gets it wrong by going too far. Depending on the character set you use, the caseless applies either to all CHAR or to no CHAR field. You don't get a choice as far as I know. Depends on the logical domain of the column. For a name-like or annotation type of field, it's pretty clear that in the real world where any illiterate can get a job doing data entry, it's a Good Thing to be able to find "John smith" when you search for "John Smith" - in fact, it's probably a nice thing for even a good end user to be able to search for "john smith" without regard for case; it's just easier. Try to imagine how annoying Google would be if it took your case literally. For a code-like column - eg regional states, Y/N, ("ORD", "INV", "PST", "CAN", etc etc etc) it's probably not a good idea to have caseless matches; then again it should be harmless if all the codes are stored properly in the ALLUPPER form. Constraints and/or triggers can easily enforce that. You can easily think of many column types in your own world where you care or don't care about case. Anyway, to answer your question, if you are using a caseless character set in Sequel server, then ALL character tests are caseless. It just happens. We actually demanded case sensitive installs out of fear of the unknown, but one of the customers secretly installed a caseless engine and basically beta'd the whole issue for us. Turned out to be no big deal, so now all our Sequel installs are caseless. > Unless of course you have some kind of column or index property to specify > that it should be case insensitive... > And if that is so, how does it handle the search? Does it write the index > using UPPER or LOWER, and then converts the argument so that an index match > can be found? > All the Sequel internals are probably using locale-based functions such as stricmp or even more advanced functions that account for the worlds quota of languages using locales, collation tables and code equivalence charts. You should try reading about Unicode in depth, it's quite entertaining/torturous. At least Unicode can subsume all the features of single-use encoding schemes such as JIS, the Latin-* extensions to ASCII or the monster sets used in China and Asia.

> The select statement below you would want to > do case insensitive search. Without changing the > application this means ALL character column to string > would be case insensitive. Is this what you want?? > > select * from cust where cust.name = ?> > I have not tried this but had one idea that > > sounds like it would work. > > 1. create a distinct data type called ichar. > > 2. Overload the = (equal) operator for ichar,char > > with a case insensitive compare > That sounds good on paper. You seem to have experience with this? How would you expect the performance to change? Light scans and index-only are probably unavailable...

>From what I've looked in a quick search MS SQL Server does case insensitive > search by default (not sure how it creates an index...). > Yes, depending on character set and associated parameters at install time. I don't know what they store in the indexes but it will be calling generic all- powerful operators. Informix's CHAR at least have the blessing of being comparable using memcmp() which is insanely fast. I want to pick and choose my columns for caseless precisely because I want to retain that performance for code-type fields. It's the names, addresses, annotations etc containing chit-chat that need caseless and can afford to suffer a bit of performance drop. > If we want to do a case sensitive search we can specifiy a collation... > Now... In Informix we can create indexes with specific collations... would > it be technically possible to create a custom collation definition that > handle this? If so, this would take care of index matches, but wouldn't > help on non-index searches... > Ooooo believe me, I've looked into this. I've even tried to reverse-engineer the GLS tables and try to figure out a way to make my own character set. For example, in French you can apparently choose a collation sequence where all the ^ and ' variations on the vowels are considered equivalent. If that's not exactly what I'm looking for then I don't know what is. But I haven't been able to nutt out the code tables to any degree of satisfaction If the GLS can just give me a caseless English variant, we can flip all our caseless columns to NCHAR and everyone would be happy.

it is better to be not that platform-specific 2009/10/13 Andrew Clarke <aclarke@civica.com.au> > Hey ladies, > > If I can just interrupt your bickering for just a moment? I would like to > see > an official (or semi-official (hell, even unofficial)) feature list for the > upcoming IDS release of 2010. I hear it's codename is Panther? > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cd17c2e3497dd0475db1140

> it is better to be not that platform-specific > Supporting Informix, Oracle and Sequel Server isn't platform independent enough? When people see one engine doing something useful - actually useful, they start to make choices. Which came first, the chicken or the egg? Maybe informix needs to keep up. People have been complaining about caseless for several years now.

Just for fun, start here http://msdn.microsoft.com/en-us/library/ms143726.aspx

On Tue, 2009-10-13 at 20:30 -0400, Andrew Clarke wrote: > > On Tue, 2009-10-13 at 19:05 -0400, Andrew Clarke wrote: > > > > Eh? Seriously, who develops an application, tests the application, > > deploys an application... AND THEN realizes the search needs to be case > > insensitive? How do you get into a situation where you need to rewrite > > for that? [if that is what is driving M$-SQL sales (a) I'll eat my hat, > > (b) the Microsoft cool-aid is even more potent then I ever imagined > > possible, and (c) Microsoft customers are deeply stupid.] > > I've been doing case insensitive searches, in PostgreSQL and Informix, > > for years. In 94% of the cases you can make your ORM take care of it > > for you anyway. > I feel the urge to channel OTC, but I'll restrain myself. > > In PostrgeSQL it is just > And we care ab.... Because, as I said, the Informix syntax is pretty much identical; I was demonstrating the concept - maybe that is too abstract for you to grasp. Sorry.

On Wed, 2009-10-14 at 06:24 -0400, Adam Tauno Williams wrote: > On Tue, 2009-10-13 at 20:30 -0400, Andrew Clarke wrote: > > > On Tue, 2009-10-13 at 19:05 -0400, Andrew Clarke wrote: > > > Eh? Seriously, who develops an application, tests the application, > > > deploys an application... AND THEN realizes the search needs to be case > > > insensitive? How do you get into a situation where you need to rewrite > > > for that? [if that is what is driving M$-SQL sales (a) I'll eat my hat, > > > (b) the Microsoft cool-aid is even more potent then I ever imagined > > > possible, and (c) Microsoft customers are deeply stupid.] > > > I've been doing case insensitive searches, in PostgreSQL and Informix, > > > for years. In 94% of the cases you can make your ORM take care of it > > > for you anyway. > > I feel the urge to channel OTC, but I'll restrain myself. > > > In PostrgeSQL it is just > > And we care ab.... > Because, as I said, the Informix syntax is pretty much identical; I was > demonstrating the concept - maybe that is too abstract for you to grasp. > Sorry. I don't know why I bothered, since I don't believe the poster actually cares about the answer, but how about: CREATE FUNCTION toLower( name VARCHAR(255)) RETURNS VARCHAR(255) WITH (NOT VARIANT);RETURN lower( name ); END FUNCTION; CREATE INDEX test ON oemr(toLower(oe_oem_code)); SELECT * FROM oemr WHERE toLower(oe_oem_desc) = 'hyster'; Now go use MS-SQL with the smug confidence that everyone else is an idiot.