1 / 32

Support of Case Insensitivity in IDS

Ajaykumar Gupte/Neeraj Kapoor Session D02 IBM Mon 4/23 10:50AM. Support of Case Insensitivity in IDS. Agenda. Problem and Background Proposed Solution Effect of this on IDS server functionality Data Migration. Problem.

denton
Download Presentation

Support of Case Insensitivity in IDS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Ajaykumar Gupte/Neeraj Kapoor Session D02 IBM Mon 4/23 10:50AM Support of Case Insensitivity in IDS Session D 02

  2. Agenda • Problem and Background • Proposed Solution • Effect of this on IDS server functionality • Data Migration Session A14

  3. Problem • Application developers should be cognizant of multi-case data in the database and write queries that search accordingly. • Difficult to enforce in n-tier application environment. • The queries with lower(lname) = 'mcdonald' cannot use index • Creation and use of Functional Index requires application modification • Functional index reduces performance • BTS (Basic Text Search) index supports case insensitive search, but needs application modification and has performance overhead Session A14

  4. Informix Solution • Newly introduced Database property called insensitive/sensitive • Only NCHAR/NVARCHAR type columns will notice the effect • Create Database syntax: create database foo with log nlscase insensitive; create database foo with log nlscase sensitive; • Sysmaster:sysdatabases will have the case sensitive information about each database Session A14

  5. Examples - 1 CREATE TABLE test (colv varchar(10); coln nvarchar(10)); INSERT INTO test VALUES('gamma', 'gamma'); INSERT INTO test VALUES('alpha', 'alpha‘); INSERT INTO test VALUES('beta', 'beta'); INSERT INTO test VALUES('epsilon', 'epsilon'); -- query varchar SELECT * FROM test WHERE colv = "GAMMA"; <0 rows returned> -- query nvarchar SELECT * FROM test WHERE coln = "GAMMA"; <1 rows returned> Session A14

  6. Examples - 2 create table foo (cc char(5), nc nchar(5)); insert into foo values ('IBM', 'IBM'); insert into foo values ('ibm', 'ibm'); insert into foo values ('Ibm', 'Ibm'); select distinct cc from foo; cc IBM Ibm ibm 3 row(s) retrieved select distinct nc from foo; nc IBM 1 row(s) retrieved Session A14

  7. Server functionality Effects • All the searches on NCHAR/NVARCHAR types will become case insensitive • Indexes on NCHAR/NVARCHAR columns • Fragmentation • Constraints (Primary/Referential and Check) • Aggregates , Group by, Order by and distinct Session A14

  8. Effects (cont…) • Statistics • Casting • Cross database/Cross server Query • Dbatools/Dbaccess Session A14

  9. Basic Case Insensitive Search create database testdb with log nlscase insensitive; create table tab1 (col1 nchar(20)); insert into tab1 values ('IBM IDS database'); insert into tab1 values ('IBM ids database'); insert into tab1 values ('ibm ids database'); insert into tab1 values ('ibm ids'); insert into tab1 values ('ibm IDS'); insert into tab1 values ('database'); Session A14

  10. Basic Case Insensitive Search select col1 from tab1 where col1 = 'IBM IDS' ; col1 ibm ids ibm IDS select col1 from tab1 where col1 not like 'IBM IDS%' ; col1 database Session A14

  11. Basic Case Insensitive Search • select col1 from tab1 where col1 like 'IBM IDS%'; col1 IBM IDS database IBM ids database ibm ids database ibm ids ibm IDS Session A14

  12. Basic Case Insensitive Search select col1 from tab1 where col1 matches 'IBM*' ; col1 IBM IDS database IBM ids database ibm ids database ibm ids ibm IDS Session A14

  13. Effect On Indexes • Unique Index create unique index uidx1 on tab1 (col1) ; 371: Cannot create unique index on column with duplicate data. 100: ISAM error: duplicate value for a record with unique key. Error in line 22 Near character position 38 • Duplicate index create index uidx1 on tab1 (col1) ; Index created. Session A14

  14. Effect on Joins • create table t1(c1 int, c2 nchar(10)); insert into t1 values (1,"aaaaa"); insert into t1 values (2,"bbbbb"); insert into t1 values (3,"ccccc"); insert into t1 values (4,"ddddd"); • create table t2(c1 int, c2 nchar(10)); insert into t2 values (1,"AAAAA"); insert into t2 values (2,"BBBBB"); insert into t2 values (3,"CCCCC"); Session A14

  15. Effect on Joins • select * from t1, t2 where t1.c2 = t2.c2; c1 c2 c1 c2 1 aaaaa 1 AAAAA 2 bbbbb 2 BBBBB 3 ccccc 3 CCCCC • select * from t1 left outer join t2 on t1.c2=t2.c2 where t1.c2 like 'a%'; c1 c2 c1 c2 1 aaaaa 1 AAAAA Session A14

  16. Effect on Joins • select * from t1 left outer join t2 on t1.c2=t2.c2; c1 c2 c1 c2 1 aaaaa 1 AAAAA 2 bbbbb 2 BBBBB 3 ccccc 3 CCCCC 4 ddddd • select * from t1 left outer join t2 on (t1.c2=t2.c2 and t1.c2 like 'A%'); c1 c2 c1 c2 1 aaaaa 1 AAAAA 2 bbbbb 3 ccccc 4 ddddd Session A14

  17. Effect on Grouping create table foo (icol int, cc char(5), nc nchar(5)); insert into foo values (1, 'IBM', 'iBM'); insert into foo values (2, 'ibm', 'ibM'); insert into foo values (3, 'ibm', 'ibM'); insert into foo values (4, 'Ibm', 'Ibm'); insert into foo values (5, 'abc', 'ABC'); insert into foo values (6, 'abc', 'ABc'); insert into foo values (7, 'abc', 'Abc'); Session A14

  18. Effect on Grouping • select distinct nc from foo; nc Abc Ibm • select count(distinct nc) from foo; (count) 2 • select count(unique nc) from foo; (count) 2 Session A14

  19. Effect on Grouping • select nc, count(nc) from foo group by nc order by nc; nc (count) ABC 3 iBM 4 • select nc, count(nc) from foo group by nc having max(icol) > 3 order by nc; nc (count) ABC 3 iBM 4 • select nc, count(nc) from foo group by nc having max(icol) > 5 order by nc; nc (count) ABC 3 Session A14

  20. Effect on Constraints • create database casedb with log nlscase insensitive; • create table parent (cc char(5), nc nchar(5) primary key); • insert into parent values ('IBM', 'IBM'); • insert into parent values ('ibm', 'ibm'); 268: Unique constraint (informix.u100_1) violated. 100: ISAM error: duplicate value for a record with unique key. • insert into parent values ('Ibm', 'Ibm'); 268: Unique constraint (informix.u100_1) violated. 100: ISAM error: duplicate value for a record with unique key. Error Case Error Case Session A14

  21. Effect on Constraints • create table child(cc1 char(5), nc1 nchar(5), foreign key(nc1) references parent(nc) ); • insert into child values ('Ibm', 'Ibm'); • insert into child values ('ibm', 'ibm'); • insert into child values ('abc', 'abc'); 691: Missing key in referenced table for referential constraint (informix.r101_2). 111: ISAM error: no record found. Should work Error Case Session A14

  22. Effect on Constraints • update child set nc1 = 'IBM' where nc1 = 'ibm'; 2 row(s) updated. • select * from child; cc1 nc1 Ibm IBM ibm IBM Session A14

  23. Effect on Constraints • update child set nc1 = 'ibm' where nc1 = 'IBM'; 2 row(s) updated. • select * from child; cc1 nc1 Ibm ibm ibm ibm Session A14

  24. Effect On Fragmentation create table t1 (c1 int ,c2 nchar(20)) fragment by expression partition part_1 (c2 = 'ibm') in rootdbs, partition part_2 (c2 = 'informix') in rootdbs, partition part_3 remainder in rootdbs; insert into t1 values (1, 'IBM'); insert into t1 values (2, 'Ibm'); insert into t1 values (3, 'ibm'); insert into t1 values (1, 'INFORMIX'); insert into t1 values (2, 'Informix'); insert into t1 values (3, 'informix'); insert into t1 values (1, 'IDS'); Session A14

  25. Effect On Fragmentation set explain on; select c2 from t1 where c2 = 'iBm'; c2 IBM Ibm ibm Explain output will show only partition part_1 was accessed Session A14

  26. Effect On Casting create table t1 (c1 nchar(20), c2 nvarchar(20), c3 char(20), c4 varchar(20)) ; insert into t1 values ('ibm', 'ibm', 'IBM', 'IBM'); insert into t1 values ('Ibm', 'Ibm', 'IBM', 'IBM'); insert into t1 values ('IBM', 'IBM', 'IBM', 'IBM'); select c1 from t1 where c1 = 'ibm'; c1 ibm Ibm IBM NCHAR cases Session A14

  27. Effect On Casting • select c1 from t1 where c1 = ibm'::varchar(10); c1 ibm Ibm IBM • select c1 from t1 where c1 = c3; c1 ibm Ibm IBM Compare NCHAR to CHAR Session A14

  28. Effect On Casting select c1 from t1 where c2 = c4; c1 ibm Ibm IBM Compare NVARCHAR to VARCHAR Session A14

  29. Effect On Casting select c1 from t1 where c3 = 'ibm'; c1 No rows found. select c1 from t1 where c3 = 'ibm'::nchar(10); c1 ibm Ibm IBM CHAR case Session A14

  30. Data Migration • New database property, can be associated with new databases only . No conversion/reversion impact. No inplace upgrade support. • Dbexport and Dbimport can be used for migrating the case sensitive database to case insensitive databases after changing the desired columns to NCHAR/NVARCHAR types. • Dbimport will have a new command line option “-ci” for importing into Case Insensitive Database. Session A14

  31. References • Informix 11.70 Info Center - http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp • DeveloperWorks CI article - http://www.ibm.com/developerworks/data/library/techarticle/dm-1108caseinsensitive/index.html?ca=drs- Session A14

  32. Questions?!? 2/15/12 Template Presentation - Session Z99 Session A14 32

More Related