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 number
IJ01521
Reported component name
ADMINISTRATION
Reported component ID
5724R46A1
Reported release
760
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2017-11-09
Closed date
2018-01-15
Last modified date
2018-01-15
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
[{"Business Unit":{"code":"BU048","label":"IBM Software"},"Product":{"code":"SSCHPP5","label":"System Related"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"760","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
15 January 2018