Why Did Passphrase Activation Take Out My ODBC Connection?
November 17, 2010 Hey, Joe:
|
We just activated passphrase support on our i5/OS V5R4M5 system. After activation, one of my Web server ODBC connections stopped working and I have no clue how to reset it. How can I get it working again? –Keith After getting this email, I contacted Keith and ran through a few scenarios. Here’s how we solved this issue as well as another issue that was hiding right behind it. First, we found that Keith was using an embedded password for his ODBC connection, which resided on a Web server that was automatically updating i5/OS data. The password was set to “LETMEIN” (all capitals). I tried signing on with that password and the system wouldn’t let me on. On a hunch, I then tried signing on to the system as the same user but this time I used “letmein” as the password (all lowercase letters). That worked and I was able to get on the system. What I think happened was that when the Password level system value (QPWDLVL) was set to 1, system passwords could only contain a maximum of 10 characters and the system didn’t care about the case-sensitivity of its stored passwords. When Keith’s i5/OS box changed its password level to 2, it activated the following additional operating system support for passwords.
My theory is that the new case-sensitivity requirement bit Keith in this case. The original password “LETMEIN” may actually have been entered and stored in i5/OS as all lowercase letters. This wasn’t a problem with password level 1 when i5/OS was just trying to match the incoming password, regardless of case. But when password level 2 was activated, suddenly Keith’s ODBC connection was sending over an uppercase password of “LETMEIN” when the i5/OS system wanted a lowercase “letmein” password, and the system rejected the login. The Plot Thickens So we changed the ODBC i5/OS user profile passphrase to “LETMEIN” so that it would match the embedded uppercase password in his Web site ODBC connection. Then Keith tried to make his automatic connection and to our surprise, the Web site connected but the application wouldn’t work. This time, we got the following SQL error. SQL0901 - SQL System Error Fortunately, I had encountered this problem before. The SQL0901 error is most commonly caused by SQL dynamic package support issues. Package support tells i5/OS to save the data access plans that the query optimizer creates for your prepared SQL statements. Access plans are saved in an i5/OS object called an SQL package (object type *SQLPKG), and they are saved whenever a client submits any new SQL statement to your i5/OS box through an ODBC, JDBC, or OLE DB driver. The name of the default Database Host Servers SQL package is QZDAPKG and it resides in the QGPL library. What occasionally happens is that QZDAPKG can become unusable because of the following issues:
I had written about this issue a few years ago in an article called One Common Cure for SQL0901 Package Errors. To fix the problem, you need to delete the partition’s database host server package (QZDAPKG) so that the system will recreate a new package that will work again. Deleting the old package is quick and easy, but there’s one catch. QZDAPKG is usually locked by the system’s database server prestart jobs so you have to unlock the object to delete it. You can unlock QZDAPKG by ending the Database Host Server jobs as well as the three database server prestart jobs that access QZDAPKG. But beware. Taking down these servers during the daytime will also disable any remote jobs that are trying to connect to your partition through ODBC, OLE DB, JDBC, and possibly Client Access Data Transfers. So we had to find a time when we could take down these jobs without disturbing Web site processing. We found an open maintenance window time at 4 a.m. the next morning. IBM provides procedures for recreating the QZDAPKG package in support document 86256FF500732959, Deleting the Database Host Server’s SQL Package. We retrieved and ran the procedures during the maintenance window. This process recreated QZDAPKG and fixed the problem. HTH –Joe RELATED STORY One Common Cure for SQL0901 Package Errors
|
