PQ75356: VARIOUS FIXES

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• 1. Use of the JOIN verb standalone, as  in: 
   'SELECT ... FROM T1 JOIN T2' . This SQL form represents a
      default  "Inner Join". While SQL PA was evaluating the
  query correctly  costwise, it did not put out messages
  regarding a "join in progress", since the  Parser did not
  pick up on the lone JOIN keyword, which it does now.
  2. Parser cannot handle the colon (':') as a normal  string, and
  converted all colon variables into parameter marker,except
  Date/Time variables. If user said SELECT  ':ABC' FROM T1,
  SQL PA  would convert that into SELECT ? FROM T1.Parser is
  now corrected so that  ANY string enclosed in quotes or
    double quotes will be left AS IS, in both DBRM  and flat file
  processing. So, users can have constants that contain a colon,
  and  SQL PA will no longer convert them to parm markers.
  3. SQL error -417 contains an invalid use of  parameter markers
  (both sides of an operator). User may understand that we
  cannot Prepare/Explain it, due to DB2 limitations,but there
  was no  notification. Now, we trap SQL error code -417
  with an explanatory  message. 
  4. Sync Error ANL2003E appears when we cannot  internally
  resolve our parser intelligence with the actual plan records
  produced  by DB2 in the Plan Table. This might occur due to
    view materialization, something we cannot derive from parsing.
  SQL PA has  demoted the 2003E internal message to appear only
  with DBTRACE ALL  diagnostics, and now takes corrective action
    in the program to follow the  Plan_Table access plan and
  continue processing SQL statements (program had  previously
  terminated with this error). 
  5. TSO users may want to be able to edit DSNA, then  change 
  their minds and re-edit DSNB by typing over the "EDIT *" on
    the  panel and changing the Input dataset name/member. 
  Modified Clist  ANLLOOP so that is now possible, even though
  SPUFI requires users  to F3 (back out) and come back in, which
    we used as our original process 'model'. Enhancement added to
  PTF.
  6. TSO users may also want to change  parameters AFTER they
    edited the SQL... we made this possible, too, in the same
  ANLLOOP Clist. 
  7. TSO users complained that DBRMKEY would reset to  '+OFF+'
    default each time they returned to main panel. This was
  corrected on  panel ANLMAIN, which will now keep persistent
    DBRMKEY in profile data and  refresh upon reentry.
  8. Users complained that Correlation Names could be  used to
    tell which tables were being accessed, and some had special
    schemes to  map back to internal departments. As such, they
    wanted Correlation Names  displayed in the reports. This is a 
  reasonable request, so now if  Correlation Names are nonblank
  they are included along with the Table  access information in
  both the explain and trace reports.
  9. NLSCODE KOR users complained that lower case support was only
    working  sporadically on mixed data systems with MCCSID 933
  (Korean mixed data char set).  SQL PA was recompiled to
   include the appropriate mixed data capabilities that  resolve
  this situation. Note that single byte character set users
     (EBCDIC  37, e.g.) are unaffected by this enhancement.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: Users of DB2 SQL Performance Analyzer.       *
  ****************************************************************
  * PROBLEM DESCRIPTION: 1. Use of the JOIN verb standalone, as  *
  *                         in: 'SELECT ... FROM T1 JOIN T2' .   *
  *                         This SQL form represents a default   *
  *                         "Inner Join". While SQL PA was       *
  *                         evaluating the query correctly       *
  *                         costwise, it did not put out         *
  *                         messages regarding a                 *
  *                         "join in progress", since the Parser *
  *                         did not pick up on the lone JOIN     *
  *                         keyword, which it does now.          *
  *                      2. Parser cannot handle the colon (':') *
  *                         as a normal  string, and converted   *
  *                         all colon variables into parameter   *
  *                         marker,except Date/Time variables.   *
  *                         If user said SELECT ':ABC' FROM T1,  *
  *                         SQL PA  would convert that into      *
  *                         SELECT ? FROM T1.Parser is now       *
  *                         corrected so that  ANY string        *
  *                         enclosed in quotes or double quotes  *
  *                         will be left AS IS, in both DBRM and *
  *                         flat file processing. So, users can  *
  *                         have constants that contain a colon, *
  *                         and  SQL PA will no longer convert   *
  *                         them to parm markers.                *
  *                      3. SQL error -417 contains an invalid   *
  *                         use of  parameter markers            *
  *                         (both sides of an operator). User    *
  *                         may understand that we cannot        *
  *                         Prepare/Explain it, due to DB2       *
  *                         limitations,but there was no         *
  *                         notification. Now, we trap SQL error *
  *                         code -417 with an explanatory        *
  *                         message.                             *
  *                      4. Sync Error ANL2003E appears when we  *
  *                         cannot  internally resolve our       *
  *                         parser intelligence with the actual  *
  *                         plan records produced  by DB2 in the *
  *                         Plan Table. This might occur due to  *
  *                         view materialization, something we   *
  *                         cannot derive from parsing. SQL PA   *
  *                         has demoted the 2003E internal       *
  *                         message to appear only with DBTRACE  *
  *                         ALL diagnostics, and now takes       *
  *                         corrective action in the program to  *
  *                         follow the  Plan_Table access plan   *
  *                         and continue processing SQL          *
  *                         statements (program had previously   *
  *                         terminated with this error).         *
  *                      5. TSO users may want to be able to     *
  *                         edit DSNA, then change their minds   *
  *                         and re-edit DSNB by typing over the  *
  *                         "EDIT *" on the  panel and changing  *
  *                         the Input dataset name/member.       *
  *                         Modified Clist ANLLOOP so that is    *
  *                         now possible, even though SPUFI      *
  *                         requires users to F3 (back out) and  *
  *                         come back in, which we used as our   *
  *                         original process 'model'.            *
  *                         Enhancement added to PTF.            *
  *                      6. TSO users may also want to change    *
  *                         parameters AFTER they edited the     *
  *                         SQL... we made this possible, too,   *
  *                         in the same ANLLOOP Clist.           *
  *                      7. TSO users complained that DBRMKEY    *
  *                         would reset to '+OFF+' default each  *
  *                         time they returned to main panel.    *
  *                         This was corrected on  panel         *
  *                         ANLMAIN, which will now keep         *
  *                         persistent DBRMKEY in profile data   *
  *                         and refresh upon reentry.            *
  *                      8. Users complained that Correlation    *
  *                         Names could be used to tell which    *
  *                         tables were being accessed, and some *
  *                         had special schemes to map back to   *
  *                         internal departments. As such, they  *
  *                         wanted Correlation Names displayed   *
  *                         in the reports. This is a reasonable *
  *                         request, so now if Correlation Names *
  *                         are nonblank they are included along *
  *                         with the Table access information in *
  *                         both the explain and trace reports.  *
  *                      9. NLSCODE KOR users complained that    *
  *                         lower case support was only working  *
  *                         sporadically on mixed data systems   *
  *                         with MCCSID 933                      *
  *                         (Korean mixed data char set).        *
  *                         SQL PA was recompiled to include the *
  *                         appropriate mixed data capabilities  *
  *                         that  resolve this situation. Note   *
  *                         that single byte character set users *
  *                         (EBCDIC  37, e.g.) are unaffected by *
  *                         this enhancement.                    *
  ****************************************************************
  * RECOMMENDATION: Install this APAR PQ75356.                   *
  ****************************************************************
  Code changes have been made to correct these conditions.
  

Problem conclusion

• Apply PTF.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  UQ78740

Modules/Macros

•    ANL@DSN  ANLCOST  ANLLOGO  ANLLOOP  ANLMAIN
  ANLPROC  ANLSCAN  ANL4QMF
  

Fix information

• Fixed component name

  DB2 SQL PER AN

• Fixed component ID

  5697F5701

Applicable component levels

• R210 PSY UQ78740

     UP03/07/24 P F307

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.