Oracle10g на Линухе - быстрее всех!

Dmitry67 · Post by **Dmitry67** » 04 Mar 2004 15:25

А как кастати в DB2 с UNICODE ?

zVlad · Post by **zVlad** » 04 Mar 2004 16:08

We have problem with cases in replication product, which does uniform works for all of hundreds of tables: it generates UPDATE or DELETE statements like, for example:

DELETE FROM tab1 where c1='Ftype' (becasuse in DB2 rows with c1='Ftype' were deleted). The same time other rows are existing with c1='FTYPE' on both DB2 and MS SQL sites. MS SQL deletes all rows with either 'Ftype' or 'FTYPE'. Then someone updates in DB2 rows with c1="FTYPE" - replication stuff on MS SQL issues: "Where clause does not match any rows." We are in trouble.

What would you recommend here?

How would you comment this:

"...assuming there is a record with PK = 'W123P',

INSERT INTO <the table> (PK, ...) VALUES ('W123p', ...) will fail because SQL Server considered they are the same, the side-effect of "case-insentive" code page.

UPDATE <the table> SET PK = 'W123p' WHERE PK = 'W123p' will change 'W123P' to W123p' because the where clause can find the record."

Above was a part of our investigation of problem happened with replication from DB2 to MS SQL. Above was provided to us from MS SQL guy, who knows MS SQL for sure. It was in 2002. Maybe things are changed since there. I don't know.
I am totally confused hearing about case sensetive, and case insensetive in relation to information, to DATA. There are no rules other than direct comparison of codes, and 'a' should never been considerated to be equival to 'A' in either circumstances. NEVER. Those pieces of DATA are ALWAYS different.

zVlad · Post by **zVlad** » 04 Mar 2004 16:12

Dmitry67 wrote:А как кастати в DB2 с UNICODE ?

As I know, UNICODE is supported in DB2 as it could be expected and desired. Ver. 8 DB2 catalog itself is in UNICODE.

Dmitry67 · Post by **Dmitry67** » 04 Mar 2004 16:26

zVlad wrote:We have problem with cases in replication product, which does uniform works for all of hundreds of tables: it generates UPDATE or DELETE statements like, for example:

DELETE FROM tab1 where c1='Ftype' (becasuse in DB2 rows with c1='Ftype' were deleted). The same time other rows are existing with c1='FTYPE' on both DB2 and MS SQL sites. MS SQL deletes all rows with either 'Ftype' or 'FTYPE'. Then someone updates in DB2 rows with c1="FTYPE" - replication stuff on MS SQL issues: "Where clause does not match any rows." We are in trouble.

What would you recommend here?

How would you comment this:

"...assuming there is a record with PK = 'W123P',

INSERT INTO <the table> (PK, ...) VALUES ('W123p', ...) will fail because SQL Server considered they are the same, the side-effect of "case-insentive" code page.

UPDATE <the table> SET PK = 'W123p' WHERE PK = 'W123p' will change 'W123P' to W123p' because the where clause can find the record."

It was part of our investigation of problem happened with replication from DB2 to MS SQL.
I am totally confused hearing about case sensetive, and case insensetive in relation to information, to DATA. There are no rules other than direct comparison of codes, and 'a' should never been considerated to be equival to 'A' in either circumstances. NEVER. Those pieces of DATA are ALWAYS different.

Если Вы можете менять саму базу то я бы ее переделал на case-sensitive
Если же это не ваш продукт и Вы не имеете права менять такие вещи то тогда возникает противоречие - продукт третьей фирмы тредует чтобы база была case-insensitive, а репликатор чтобы была caase-sensitive... Тут не знаю что посоветовать
Какой из случаев в данном случае ?

zVlad · Post by **zVlad** » 04 Mar 2004 16:32

Dmitry67, you mean that our MS SQL guys could re-create database and make it works like DB2 does, and 'a' would be never the same as 'A'. If your answer definatelly 'Yes' then I don't understand why our MS SQL guys still don't tell us about it. Just few weeks ago I was involved in data change we did to fix that case problem, and I saw myself SQL Server worked as case-insensetive product. We desire to have SQL Server be case-sensetive for long long time.

Merle · Post by **Merle** » 04 Mar 2004 16:33

zVlad wrote: What would you recommend here?

Сходу можно предложить два варианта.
1. "радикальный": Изменить столбец "c1" с явным указанием COLLATION, в названии которого присутствует .._CS_.. - Case Sencitive. Например, Latin1_General_CS_AI.
Не подходит, в том случае, если есть другие запросы, которые рассчитывают на то, что значение не чуствительно к регистру.

2. Указать нужный COLLATION прямо в запросе.
DELETE FROM tab1 where c1 COLLATE Latin1_General_CS_AI = 'Ftype'

Вот, рабочий пример:

Code: Select all

create table #er (c1 nvarchar(200))
GO

insert into #er (c1) values('aaaAA')

--- удаляем с учетом регистра
---
DELETE FROM #er where c1 COLLATE Latin1_General_CS_AI = 'aaaaa' 
--- 0 rows affected

--- удаляем без учета регистра
---
DELETE FROM #er where c1 COLLATE Latin1_General_CI_AI = 'aaaaa'
--- 1 rows affected

То же самое и со всеми остальными Вашими задачками.

zVlad · Post by **zVlad** » 04 Mar 2004 16:51

Thank you, guys. I have submitted question to our MS SQL people. Waiting for respond.

zVlad · Post by **zVlad** » 04 Mar 2004 17:14

To Merle. While I am waithing for respond from our SQL people, I think none of what you recommended could be helpful in our case. Imagine, there are hundreds of tables which we have to replicate from DB2 to SQL Server. Software we are using is multi-platform, and inter-platform. Vendor name was BMC, then Striva, and finally now it is "Informatica". So it is impossible to change names of columns (thousands of columns). It is also difficult to figure out when COLLATE must be applied in SQL statement, you know, SQL statements are generated on fly. In uniform fashion.
I went to SQL Server Enterprise Manager, I tried to create table with case sensitive column data type. There is nothing there to specify it :pain1:

Do you mean it works somehow if one creates column with special name conventions?

Also I looked at database properties, and I did't see anything related to case sensitivity there.

Dmitry67 · Post by **Dmitry67** » 04 Mar 2004 18:44

2 zVlad
Я тоже люблю болешь case sensitive
Но по умолчанию если поставить то он будет case-insensitive
sensitivety задается

1 для всего сервера при инсталляции
2 при создании базы (но если collation базы не совпадает с collation сервера то могут быть сюрпризы с временными таблицами)
3 Наконец при создании каждой таблицы и колонки

collаtion базы и сервера есть точно в enterprise, просто вы наверное ищете слова case sensitivity а надо искать collation
таблицы из Enterprise я не создаю и не помню есть ли тамcollation
В create table разумеется есть

Учтите что если базу перелить и изменить collation то нкторе ператоры SQL могут перестать быть синтактически коррекными по оняным причинам то есть продукт может перестать работать

vc · Post by vc » 04 Mar 2004 18:52

zVlad,

zVlad wrote:To Merle. While I am waithing for respond from our SQL people, I think none of what you recommended could be helpful in our case. Imagine, there are hundreds of tables which we have to replicate from DB2 to SQL Server. Software we are using is multi-platform, and inter-platform. Vendor name was BMC, then Striva, and finally now it is "Informatica". So it is impossible to change names of columns (thousands of columns). It is also difficult to figure out when COLLATE must be applied in SQL statement, you know, SQL statements are generated on fly. In uniform fashion.
I went to SQL Server Enterprise Manager, I tried to create table with case sensitive column data type. There is nothing there to specify it Do you mean it works somehow if one creates column with special name conventions?

Also I looked at database properties, and I did't see anything related to case sensitivity there.

If you have a SQL SERVER 2000, you can make a in the table case-sensitive even if the whole installation is case-insensitive.

E.g.:

Code: Select all

create table t1(x varchar(10) collate SQL_Latin1_General_Cp437_CS_AS primary key)

---collate SQL_Latin1_General_Cp437_CS_AS defines it as case sensitive

VC

vc · Post by vc » 04 Mar 2004 19:01

zVlad wrote:To Merle. While I am waithing for respond from our SQL people, I think none of what you recommended could be helpful in our case. Imagine, there are hundreds of tables which we have to replicate from DB2 to SQL Server. Software we are using is multi-platform, and inter-platform. Vendor name was BMC, then Striva, and finally now it is "Informatica". So it is impossible to change names of columns (thousands of columns). It is also difficult to figure out when COLLATE must be applied in SQL statement, you know, SQL statements are generated on fly. In uniform fashion.
I went to SQL Server Enterprise Manager, I tried to create table with case sensitive column data type. There is nothing there to specify it Do you mean it works somehow if one creates column with special name conventions?

Also I looked at database properties, and I did't see anything related to case sensitivity there.

Forgot to mention ...

This statement will create a case-sensitive _database_:

Code: Select all


CREATE DATABASE myDB
COLLATE SQL_Latin1_General_Cp437_CS_AS
ON PRIMARY
( NAME = myDB_dat,
   FILENAME = 'd:\program files\microsoft sql server\mssql\data\myDb_dat.mdf',
   SIZE = 10,
   MAXSIZE = UNLIMITED,   
   FILEGROWTH = 5 )
LOG ON
( NAME = myDb_log,
   FILENAME = 'd:\program files\microsoft sql server\mssql\data\myDb_log.ldf'
   SIZE = 5,
   MAXSIZE = UNLIMITED,
   FILEGROWTH = 2 )

Of course, you would use appropriate paths for the files.
VC

Dmitry67 · Post by **Dmitry67** » 04 Mar 2004 19:12

При создании базы в Enterpise есть такая штучка куда давить чобы изменить case-sensitivity

vc · Post by vc » 04 Mar 2004 19:12

zVlad wrote:To Merle. While I am waithing for respond from our SQL people, .. Also I looked at database properties, and I did't see anything related to case sensitivity there.

Also:

Code: Select all



-- to find out the database collation (the database name is test):
select convert(sysname,DatabasePropertyEx('test','Collation'));

-- to change the db collation:
alter database test collate <whatever>;

-- to find out all the column collations in a table:
select name, collation from syscolumns where id=object_id('t1')

-- to change the collation for a column:
alter table t1 alter column c1 char(10) COLLATE <whatever>

VC

Dmitry67 · Post by **Dmitry67** » 04 Mar 2004 19:19

2 vc
Я сейчас проверить не могу
Но мне кажется что изменить collation у колонки по которой создан индекс так посто не удасться
Или можно ? Я не пробовал
Аналогичо интересно провеить что будет пи изменеии collation базы CS -> CI, если в базе есть таблицы A и a

zVlad · Post by **zVlad** » 04 Mar 2004 19:42

On my question:

"...why don't change it to case sensitive? Like we have in DB2."

our SQL Server guy answered:

"...will be performance hit to the database. using UCase(..) will force SQL Server to scan the table rather than utilizing indexes. and more problem with table joins."

I don't really undestand what he meant saying "...and more problem with table joins". But what I know now that dispite the fact that our replication problem could be fixed by using case sensitive it will never be possible to have because of other issues would happen if we do it.
Forget about it.