Category: Migrating to DB2 11

On September 24^th we migrated 5 standalone DB2 subsystems and 2 subsystems which were members of the same datasharing group.  The migrations themselves had no issues and we were up and running on DB2 11 within a few hours on all of these subsystems.

Within a few hours and over the next couple days we started having several issues.

• SQL failures with research determining that one of the subsystems UNION_COLNAME_7 zparm value was set to the default value of NO instead of YES.  Corrected zparm which corrected problems.
• Dynamic SQL query running long.  Query had IN subquery and we created an index to get query to run in a timely matter. (LAC4 problem)
• Another dynamic SQL query running long.  Query also had IN subquery and created another index to get query to run in a couple minutes instead of an hour and a half. (FLG3 problem)
• Static SQL query running long after package rebound during DB2 11 upgrade.  Query also had IN subquery that was now running 20 CPU minutes instead of 90 CPU seconds. (TTMAGP1 problem)

Since we had three SQL performance issues all with IN subqueries we questioned if some common defect was introduced with DB2 11 so we opened a Service Request to IBM for research.  We provided documentation on each issue and IBM eventually determined that two of these issues were separate defects with APARs opened but the FLG3 issue was just a data skew issue and access path selection that did change with DB2 11 which is something we deal with every time we upgrade DB2 versions.

The LAC4 issue above had APAR PI71415 created. The FLG3 issue was the data skew issue and a work-around was provided by IBM to modify the subquery to prefer a better access path.  The TTMAGP1 issue above had APAR PI70237 created.

IBM did supply APAR fixes for each of the above items and we successfully tested them but at this time we are waiting for APAR closure for the two items with an actual PTF to apply and test before continuing our DB2 11 migrations.

 

Our first test subsystem migration occurred on August 11^th followed the next day by another migration and then we let those “burn-in” for over a week before planning any additional migrations.  We did not experience any issues with the first two migrations so on August 24^th we migrated 5 more test DB2 subsystems.

Problems identified after 5 additional test subsystem migrations.

• -805 SQL Error on common packages DSNTIAD but further research found that the PLAN PKLIST was specified incorrectly and rebinding the PLAN corrected the issues.
• -805 SQL Error on package DSNUTIL.DSNUGSQL due to remote SQL access to a DB2 10 subsystem.  We remote bound this new DB2 11 package version to each DB2 10 subsystem to ensure this error did not occur for any other applications.
• Failures S04E level ID error.  Noticed error running DB2 utilities but the failure occurred if DB2 attempted to access these particular tablespaces at all.  Opened Service Request to IBM and they identified the issue was caused due to old SYSLGRNX entries on some tablespaces which was basically fallout from the Y2K bug.  Applied a fix to allow us to run MODIFY to cleanup old rows from SYSLGRNX and then we worked to identify and cleanup the rest of our DB2 subsystems to ensure this issue did not occur on future migrations.  I strongly suggest researching this potential issue on all your DB2 subsystems before doing migrations.  Working with IBM we had to run some special MODIFY utilities with DIAGNOSE to get entries cleaned up.

We continued our migrations with 5 more on September 7^th and the final 5 on September 21^st completing our test subsystem migrations (17 total).

Additional problem identified after test subsystem migrations  were complete.

• Application reported SQL failures and upon research found they were UNION SQL and eventually determined that during migration some of our zparm UNION_COLNAME_7 values had been set to the default NO instead of YES causing the problem.

At this time our migrations were stable after addressing the above issues and we were ready to start our first production subsystem migrations on September 24^th and my next blog post will describe this effort and related issues that caused us to postpone future migration.

We started migrations of the DB2 subsystems, that our organization supports, prior to the creation of this BLOG site but I will post a chronology of our migrations and the issues we have experienced up to now and continue with updates as we continue our migration efforts.  We are migrating from DB2 10 to DB2 11 and we currently support applications across 40 DB2 subsystems with 17 being test and 23 being production.  We have completed migration of all 17 test subsystems and 4 of the 23 production but currently awaiting PTF fixes for two APARs that were opened related to issues we identified.  Our next migrations are not planned now until February while we wait for the APARs to be finalized and PTFs to be created for our testing.  We did test APAR fixes for both issues and they did correct the SQL performance problems we experienced.