• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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.

    • Longer passwords could be entered.
    • Almost every keyboard character could be used in a password.
    • System passwords became case-sensitive.

    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:

    • A system upgrade has occurred and the old package is incompatible with the new operating system
    • Object corruption
    • It hits a size limitation

    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



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    ARCAD Software

    [Webinar Series] Demystifying DevOps on the IBM i

    Join us for this 3-Part Roundtable Webinar Series, where ARCAD experts will demystify the move to Git and an automated process with options that work for everyone!

    This Series will be an engaging discussion on key DevOps topics as Git, Builds, Automation and much more.

    Part 1: IBM i & Git – Developer Tools (Thursday, January 26th, at 12:00PM ET / 9:00AM PT)

    During this 1st session, we will discuss Developer Tools and all the ways to use Git including iProject, Merlin, VS Code, ARCAD’s Centralized option and more.

    Part 2: Branching & Building (Thursday, February 9th, at 12:00PM ET / 9:00AM PT)

    During this 2nd session, we will discuss Feature/Release and Branch Management and building the branches with tools like Bob and ARCAD.

    Part 3: DevOps – Automated Workflow (Thursday, February 23rd, at 12:00PM ET / 9:00AM PT)

    During this 3rd session, we will discuss what can be automated in the IBM i DevOps process starting from the build to other steps in your workflow.  We’ll discuss pipeline tools like Jenkins and the new automation features of Git packages.

    Register NOW

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    SEQUEL Software:  FREE Webinar. See what SEQUEL software can do for you. Nov 30
    LANSA:  FREE Webinar. "Think Beyond Modernization." Nov 18, Dec 1, Dec 16
    COMMON:  Join us at the 2011 IT Executive Conference, May 1-3, in Minneapolis, MN

    IT Jungle Store Top Book Picks

    BACK IN STOCK: Easy Steps to Internet Programming for System i: List Price, $49.95

    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The iSeries Pocket Database Guide: List Price, $59
    The iSeries Pocket SQL Guide: List Price, $59
    The iSeries Pocket WebFacing Primer: List Price, $39
    Migrating to WebSphere Express for iSeries: List Price, $49
    Getting Started with WebSphere Express for iSeries: List Price, $49
    The All-Everything Operating System: List Price, $35
    The Best Joomla! Tutorial Ever!: List Price, $19.95

    iTech to Resell CODA Financials for IBM i Whaaa? IBM Gets Stingier with Power Systems Deals

    Leave a Reply Cancel reply

Volume 10, Number 36 -- November 17, 2010
THIS ISSUE SPONSORED BY:

Vision Solutions
SEQUEL Software
WorksRight Software

Table of Contents

  • The Top 10 IBM i Security Exposures, Part 1
  • Case-Sensitive SQL Identifiers
  • Admin Alert: Prototype Instructions for Running TCP/IP in i5/OS Restricted State
  • Large Subprocedure Return Values: V7 Brings Relief
  • Find Hidden IFS Files
  • Why Did Passphrase Activation Take Out My ODBC Connection?

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • N2i Gains Traction Among IBM i Newbies
  • Realizing The Promise Of Cross Platform Development With VS Code
  • 2023 IBM i Predictions, Part 3
  • Four Hundred Monitor, January 25
  • Join The 2023 IBM i Marketplace Survey Webinar Tomorrow
  • It Is Time To Have A Group Chat About AI
  • 2023 IBM i Predictions, Part 2
  • Multiple Vulnerabilities Pop Up In Navigator For i
  • Participate In The 2023 IBM i Marketplace Survey Discussion
  • IBM i PTF Guide, Volume 25, Number 4

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.