2

Where I work, we have an IBM Power System that we use for data storage and access. It is currently running i OS 6.1. We have a website on an IIS server that pulls data from this server and has several different web apps that do different functions. All of these have some sort of search function. There is an issue with case sensitivity on two of these sites.

Here's where it gets a bit hairy. This problem only started happening when we upgraded from v5r4 to v6r1 on the IBM system. Again though, it does not happen on all of the sites (there are 8 total, 2 have the issue). This is very strange, because all of them use the same IO procedures. The website was developed by another party, who is able to remotely test our database. They are running v7r1, and didn't have any issues when using the site with our database. The website accesses the data via ODBC, and I've tried reconfiguring the DSN. One of the support people even had me register their exact ODBC config with a .reg file, and still nothing. Both they and I have run out of ideas, so for now, we've resorted to a help link. I happened to be working on a different website that uses the same server for queries, and the ODBC connection in this site had the same behavior...names and any other search fields seem to all be case sensitive. I know, since I got a sudden surprise of my site not working, that my site was NOT case sensitive before the v6r1 upgrade. We also use a 3rd party app to run SQL queries, and on the same DSN the queries in this application are NOT case sensitive. So as you can see, I can't find any correlation. Does anyone know of issues with IBM DSNs or case sensitivity with IBM servers/filesystems that could cause this for certain queries?

SausageBuscuit
  • 165
  • 1
  • 11

1 Answers1

2

If the suspect is the ODBC driver, check the DSN. Select the Language tab, and then choose the sort type 'Sort based on language ID'. Then choose your language ID. The default setting is 'Sort based on *HEX values'.

If the suspect is the IBM i server, have the admin check the SRTSEQ property of the server jobs - QZDASOINIT, probably - to set it to *LANGIDSHR. The default is *HEX. Clearly, this will affect ALL ODBC access to the IBM server, not just your access. But if that's how it was before the upgrade, perhaps it would be best to put it back.

It might be more transparent if the queries handled the monocasing explicitly. So rather than ...WHERE CUSTNAME LIKE 'JONES%'... it would be ...WHERE UPPER(CUSTNAME) LIKE 'JONES%'...

Buck Calabro
  • 653
  • 6
  • 9
  • Per the support reps, the DSN should have (and did have) "Sort based on specified table" with QSYS\QSYSTRNTBL as the table. Through the iSeries client, we can see that this is some sort of case conversion table. I tried setting it sort based on HEX per IBM, but it still does the same. My thoughts were the queries at first as well, but I know without a shadow of a doubt that I didn't have to specify case in my queries on the site that I'm working on, and they did work before. Sort sequence in the jobs is set to *HEX. If this DSN is supposed to use QYSTRNTBL, what should SRTSEQ be set to? – SausageBuscuit Jan 09 '13 at 22:07
  • Slight Amendment to that...now that I look through some of the jobs, some of them have SRTSEQ set to *HEX, and some are QYSTRNTBL. Could it be possible the working sites could be coming up with one and the non-working ones with the other? – SausageBuscuit Jan 09 '13 at 22:16
  • *HEX means to sort according to the absolute position in the EBCDIC code table. *LANGIDSHR and 'Sort based on language ID' both mean to sort according to the language rules. So in English, E and e are identical. In French, E, e and é are identical, etc. QSYSTRNTBL is a system-supplied lower-to-upper case translation table. If some jobs are SRTSEQ(*HEX) and some are SRTSEQ(QSYS/QSYSTRNTBL) then this explains your scenario. – Buck Calabro Jan 09 '13 at 22:27
  • I'm using my test site as a reference since the userid for the ODBC connections is pretty unique and dispensable. When I go into this user's attributes, I've tried setting the sort sequence on this userid to the other values (*LANGIDSHR and the qsys library), but even when it is changed it still shows *HEX in the running jobs. Is there somewhere else this attribute should be set? – SausageBuscuit Jan 10 '13 at 14:45
  • The user profile is the right place. When the subsystem restarts, the settings in the user profile will propagate into the jobs running on the server. Until then, consider manually changing the job to the SRTSEQ() you need. – Buck Calabro Jan 10 '13 at 15:49
  • Had to tweak my connection string just a little, also. Using SYSVAL in the user profile still worked, but I did have to change it Sort by Language ID on the DSN level. Thanks for your help! – SausageBuscuit Jan 10 '13 at 21:03