Topic
2 replies Latest Post - ‏2012-04-23T14:46:00Z by SystemAdmin
SystemAdmin
SystemAdmin
3129 Posts
ACCEPTED ANSWER

Pinned topic 7.1 SQL CREATE INDEX with adjoining UPPER derived keys

‏2012-04-20T12:45:26Z |
Perhaps I have found an IBM bug, but, as always, I fear I may have just made a simple mistake. Can anyone please set me straight?

I have created an SQL table for equivalent names (in proper case). (The SQL for the CREATE TABLE is at the bottom.) A table row looks like this (in DBU):
EWRD1 Amy
EWRD2 Amiee

I then created an SQL index over the table using the 7.1 UPPER qualifier to create a derived key.

CREATE INDEX DTALIB/BGBFL751 ON BGBFP750 (
UPPER (EWRD1) AS EWRD1U ASC,
UPPER (EWRD2) AS EWRD2U ASC)
RCDFMT BGBFX751;

The problem is that the created index presents the data as:
EWRD1 Amy
EWRD2 Amiee
EWRD1U AMY
EWRD2U AMY

So, the first derived key works fine, but, the second duplicates the results from first field. I tested the index in a program and the second UPPER-word key is, in fact, presented as a duplicate of the first. The error remains if I reverse the fields in the CREATE INDEX statement. The error remains if I change the names of the derived keys. The error remains when built from interactive SQL. The error is the same using a different physical file.

Removing the UPPER qualifier does give me my expected results (but, in proper case). If I change the CREATE INDEX, adding an intervening derived key, my index shows the data as I would expect.

CREATE INDEX DTALIB/BGBFL751 ON BGBFP750 (
UPPER (EWRD1) AS EWRD1U ASC,
SUBSTR (EWRD2,1,4) AS EWRD2U ASC,
UPPER (EWRD2) AS EWRD3U ASC)
RCDFMT BGBFX751;

EWRD1 Amy
EWRD2 Amiee
EWRD1U AMY
EWRD2U Amie
EWRD3U AMIEE

So, the problem is that, with two adjoining UPPER statements, the first works fine, but all following adjoining UPPERs duplicate the results from the first. (I tried it with 3 adjoining UPPERs.) Non-adjoining UPPERs give correct results. Is this crazy? Or, is it just me?

The SQL code for my CREATE TABLE (excluding LABELs) is:
CREATE TABLE DTALIB/BGBFP750 (
Equiv_Word_1 FOR EWRD1 CHAR (60) NOT NULL WITH DEFAULT,
Equiv_Word_2 FOR EWRD2 CHAR (60) NOT NULL WITH DEFAULT,
PRIMARY KEY(EWRD1,EWRD2) )
RCDFMT BGBFX750;
Updated on 2012-04-23T14:46:00Z at 2012-04-23T14:46:00Z by SystemAdmin
  • krmilligan
    krmilligan
    446 Posts
    ACCEPTED ANSWER

    Re: 7.1 SQL CREATE INDEX with adjoining UPPER derived keys

    ‏2012-04-20T21:04:21Z  in response to SystemAdmin
    Sounds like you may have found a defect. I'm assuming that your "presenting the index" values by reading the SQL Index as a keyed logical file. Possible it might be a problem on how the program is accessing index/LF.

    I would first load the latest Database Group PTF and contact IBM Support if the problem still exists.

    From a best practices perspective, I would only use the system column names (FOR clause) on non-SQL interfaces and use the longer names on all SQL requests (eg, CREATE INDEX statement).
    • SystemAdmin
      SystemAdmin
      3129 Posts
      ACCEPTED ANSWER

      Re: 7.1 SQL CREATE INDEX with adjoining UPPER derived keys

      ‏2012-04-23T14:46:00Z  in response to krmilligan
      Thank you for the response. I was really hoping it was just me.

      When I used DBU 9.0 against the index and saw the duplicate values, I suspected DBU. So, I put a non-SQL RPGLE program into debug and viewed the values returned from the index. They matched what I saw in DBU.

      Thanks also for the best practices perspective. In this case, I'm using this example to practice building tables and indexes to replace existing physicals/logicals without changing the RPGLE programs. Well, without changing them too much.

      I appreciate the help, and will move on to IBM Support.