Kbase P888: AS/400: Define case-insensitive index on index component
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
15/10/2008 |
|
Status: Verified
FACT(s) (Environment):
Progress 8.0Cxx
Progress 9.0xx
Progress 9.1xx
Progress/400 DataServer
SYMPTOM(s):
No records are retrieved when using a FOR EACH statement with Progress/400 DataServer and case-sensitive indexes.
Can not force client to resolve the query
CAUSE:
DB2/400 handles case sensitivity. By default, Progress is case insensitive, but DB2/400 is case sensitive. In this example provided below, the DB2/400 case sensitivity is applied at the index level, but on a standard Progress database it is applied at the field level.
If a multi-component index is defined in a Progress/400 DataServer and just one of the components is case sensitive, the entire index becomes case sensitive. For example, the following is a case-sensitive
index:
Index : pmword-1 on table pmword
- cpy FORMAT X(6)
- word FORMAT X(20) CASE-SENSITIVE
If the data physically stored in the cpy field is "daz" (lower case) and there is no index defined in the "cpy" field alone, it would be correct to expect the following query against the DataServer to retrieve some records:
FOR EACH pmword WHERE cpy = "daz":
However, in this situation, no records would be returned because the query is searching purely on a case insensitive component of a case sensitive index.
Before submitting the query to the DataServer, the compiler converts it to upper case. Subsequently no records are found with a "cpy" field matching "DAZ".
If the data physically stored in the "cpy" field is converted to "DAZ" and the same lower case query run, the expected records will be displayed.
FIX:
Define a case insensitive index on field or
force the client to resolve the query by using CAPS function on both side of the equation: WHERE CAPS(field) = CAPS(data).