On September 24th 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.
