IJ01521: DB2 SQL0206N SQLSTATE=42703 ERROR PUBLISHING COGNOS PACKAGES

APAR status

• Closed as program error.

Error description

• This issue has surfaced when customer upgraded to TPAE 7608
  plus from TPAE 7603.
  This issue applies to Object Structures that include non
  system-level Objects
  [IBM][CLI Driver][DB2/AIX64] SQL0206N  'WOACTIVITY.SITEID' is
  not valid in the context where it is used.  SQLSTATE=42703
  Steps to Reproduce:
  To reproduce the issue we can use the following steps:
  1 - Go to Integration -> Object Structures
  2 - Select any Object Structure (i.e. REP_ASSET)
  3 - Select "Publish as a Cognos Package" from the "Select
  Action" menu.
  4 - A wait window should appear.
  5.  The error should appear:
  After a few seconds or minutes the BMXAA7409E error should
  appear :
  Error "BMXAA7409E - The transformed object structures cannot be
  published to the Cognos server.
  To determine the cause, check the product log files. Metadata
  Provider Error"
  Additionally, please take into consideration the information
  found by our Maximo development team in that Cognos PMR
  regarding the query syntax, it was the key finding that helped
  us solve this issue.
  One of the scenarios performed that reproduced the DB2
  SQL0206N / SQLSTATE=42703 errors is quoted below:
  I created a simple Structure, with just one Object, which
  throws out an error you can replicate:
  
  Object Structure (tab)
  Object structure: ZREP_COMPANIES - Companies
  Consumed by: REPORTING - Reporting
  Application: ZCOMPANIES
  User Defined (tick enabled)
  Configurable?: (tick enabled)
  Support Flat Structure?: (tick enabled)
  Source Objects for ZREP_COMPANIES
  Object: COMPANIES
  Parent Object: (blank)
  Object Location: COMPANIES
  Relationships: (blank)
  Object Order: 1
  User Defined (tick enabled)
  
  When we  tried exporting this structure we got the same error
  message as before. Attached to this email you will find the
  most relevant section of the log.
  
  We also took the chance to perform some additional testing.
  This time we extracted the query that was throwing the error
  and went directly to Cognos Framework Manager and:
  1. Opened an existing project
  2. Created a new Query Subject
  3. Selected "Data Source"
  4. Removed the check on "Run database wizard" and click
  "Finish".
  5. On the SQL field, pasted the query.
  6. Clicked on "Validate"
  7. Got the same SQL0206N SQLSTATE=42703 errors as the ICD logs.
  Original Query in Maximo Logs
  
  
  select * from [MXDB].COMPANIES where(((
           COMPANIES.ORGID in (select distinct ORGFILTER.ORGID
              from [MXDB].ORGFILTER
              where ORGFILTER.USERID =
  UPPER(#sq($account.personalInfo.userName)#)
              and ORGFILTER.APP = 'ZCOMPANIES'))
              or not exists((select ORGFILTER.ORGID
              from [MXDB].ORGFILTER
              where ORGFILTER.USERID =
  UPPER(#sq($account.personalInfo.userName)#)
              and ORGFILTER.APP = 'ZCOMPANIES')))
  
  
     and exists(select 1 from [MXDB].MAXUSER
        where MAXUSER.USERID =
  UPPER(#sq($account.personalInfo.userName)#)
        and MAXUSER.STATUS =
        (select SYNONYMDOMAIN.VALUE
           from [MXDB].SYNONYMDOMAIN
           where SYNONYMDOMAIN.DOMAINID = 'MAXUSERSTATUS'
           and SYNONYMDOMAIN.MAXVALUE = 'ACTIVE')))
  
  
  Query ran in Cognos Framework Manager
  
  select * from [MXDB].COMPANIES where(((COMPANIES.ORGID in
  (select distinct ORGFILTER.ORGID
              from [MXDB].ORGFILTER
             where ORGFILTER.USERID = UPPER('E000422')
              and ORGFILTER.APP = 'ZCOMPANIES'))
  
        or not exists ((select ORGFILTER.ORGID
              from [MXDB].ORGFILTER
              where ORGFILTER.USERID = UPPER('E000422')
              and ORGFILTER.APP = 'ZCOMPANIES')))
  
     and exists
  
     (select 1 from [MXDB].MAXUSER
        where MAXUSER.USERID = UPPER('E000422')
        and MAXUSER.STATUS =select SYNONYMDOMAIN.VALUE
           from [MXDB].SYNONYMDOMAIN
           where SYNONYMDOMAIN.DOMAINID = 'MAXUSERSTATUS'
           and SYNONYMDOMAIN.MAXVALUE = 'ACTIVE')))
  
  We then proceeded to fixing the query by aliasing the tables
  and were able to overcome the error:
  
  select * from [MXDB].COMPANIES as COMPANIES
  where(((COMPANIES.ORGID in (select distinct ORGFILTER.ORGID
              from [MXDB].ORGFILTER as ORGFILTER
              where ORGFILTER.USERID = UPPER('E000422')
              and ORGFILTER.APP = 'ZCOMPANIES'))
  
        or not exists
  
        ((select ORGFILTER.ORGID
              from [MXDB].ORGFILTER as ORGFILTER
              where ORGFILTER.USERID = UPPER('E000422')
              and ORGFILTER.APP = 'ZCOMPANIES')))
  
     and exists (select 1 from [MXDB].MAXUSER as MAXUSER
        where MAXUSER.USERID = UPPER('E000422')
        and MAXUSER.STATUS =(select SYNONYMDOMAIN.VALUE
           from [MXDB].SYNONYMDOMAIN as SYNONYMDOMAIN
           where SYNONYMDOMAIN.DOMAINID = 'MAXUSERSTATUS'
           and SYNONYMDOMAIN.MAXVALUE = 'ACTIVE')))
  
  Window dialog box error message appears:
  
  "RE_INCIDENT - IBM Cognos Framework Manager
  RQP-DEF-0177 An error occurred while performing operation
  'sqlPrepareWithOptions' status='-69'.
  UDA-SQL-0043 The underlying database detected an error during
  processing the SQL request.
  [IBM][CLI Driver][DB2/AIX64]
  SQL0206N "COMPANIES.ORGID" is not valid in the context where it
  is used. SQLSTATE=42703"
  
  We then proceeded to comparing the result with our QA
  environment. We configured the exact same
  Object Structure in our QA environment, ICD 7.6.0.2, exported
  the results to an XML file and compared the same query from the
  log:
  
  select * from COMPANIES where (((ORGID in (select distinct ORGID
              from ORGFILTER
              where USERID = UPPER('E000422')
              and APP = 'ZCOMPANIES'))
                 or not exists ((select ORGID from ORGFILTER
              where USERID = UPPER('E000422')
              and APP = 'ZCOMPANIES')))
              and exists (select 1 from MAXUSER
        where USERID = UPPER('E000422')
        and STATUS =(select VALUE from SYNONYMDOMAIN
           where DOMAINID = 'MAXUSERSTATUS'
           and MAXVALUE = 'ACTIVE')))
  
  I imported this query directly to Cognos FM and it did not
  throw any errors. It is easy to see what changed here: on
  7.6.0.2 there was no explicit context given to the query,
  whilst in 7.6.0.3  implicit context is given and therefore we
  get the error above.
  CURRENT ERRONEOUS RESULT:
  If a user tries to publish an Object Structure to IBM Cognos
  from IBM Control Desk the following error appears:
  'BMXAA7410E - The object Structures cannot be transformed to
  the Cognos Action Logs format.'
  EXPECTED RESULT:
  Successful Publishing of all  source objects levels of
  hierarchy path for object structures.
  ENVIRONMENT:
  App Server IBM WebSphere Application Server 8.5.5.10
  Server OS AIX 7.1
  Server DB DB2/AIX64 10.5 (SQL10058)
  PRODUCT VERSION:
  Cognos 10.2.1.1.
  ICD 7.6.0.3257 Build BUILD DB Build V7511-00
  Tivoli's process automation engine 7.6.0.8-IFIX20170914-1342
  Build 20170512-0100 DB Build V7608-63 HFDB Build HF7608-05
  IBM Maximo for Service Providers 7.6.3.0-20170911-1033 Build
  20170221-2101 DB Build V7630-21 HFDB Build HF7630-02
  IBM TPAE Integration Framework 7.6.0.8 Build 20170510-2330 DB
  Build V7608-43
  IBM Endpoint Manager Integration Enablement 7.6.0.3257 Build
  201709140546 DB Build V7602-02
  IBM Maximo Asset Management Work Centers 7.6.0.2 Build
  20170509-0906 DB Build V7602-57
  IBM Endpoint Manager Integration Configuration 7.6.0.3257 Build
  201709140546 DB Build V7602-01
  AFFECTED APPLICATION (for Maximo Only): Object Structures
  INDUSTRY SOLUTION/ADD ON (if applicable): N/A
  

Local fix

• Dev to provide fix
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Cognos users.                                                *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Receive error such as "'WOACTIVITY.SITEID' is not valid in   *
  * the context where it is used." when publishing in some DB2   *
  * environments.                                                *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  The problem only occurs with DB2, but not all DB2 environments
  are affected.  In some cases, the aliasing of the security
  restriction queries included in the published Cognos models is
  not accepted by DB2.
  

Problem conclusion

• The fix for this APAR is contained in the following maintenance
  package:
  	 | release\fix pack | Fix Pack Release 7.6.1.0 TPAE
  

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:

Modules/Macros

• MAIXMO
  

Fix information

• Fixed component name

  ADMINISTRATION

• Fixed component ID

  5724R46A1

Applicable component levels

• R760 PSN

     UP