Same Cognos SQL succeed on one machine but failed on another
I use a cognos sql query in the etl fact build, to query data from ClearQuest. It works well on my laptop, but when I use remote desktop to logon to a server, open the same etl catalog and test the query, it fails.
Both of 2 machine os is windows xp, insight version is 1.0.1.1, the db2 server installed is v9.7.
The sql I used is:
the log messages of the successful test is:
and the log messages of the failed test is:
So we can see the 2 "Preparing" messages are different:
I copied all the files under \etl\jdbc, \etl\odbc and \elt\plugin, and overwritten the corresponding folders on the server, but it didn't work.
Anyone can help on this?
Both of 2 machine os is windows xp, insight version is 1.0.1.1, the db2 server installed is v9.7.
The sql I used is:
SELECT DISTINCT Department, DATASOURCE_ID
FROM "{$RESOURCEGROUP}".UserInfo
WHERE Department = 'MMQS'
the log messages of the successful test is:
05/15/2012 02:31:02,125 INFO com.ibm.rational.drivers.jdbc.xml.RDSConnection : CRRRE1234I: Start initializing the connection to => jdbc:rds://D:\program files\IBM\Rational Insight\configtool\${ICM project folder}\Configurations\ClearQuest Test.xdc
05/15/2012 02:31:02,265 INFO com.ibm.rational.drivers.jdbc.xml.RDSConnection : CRRRE1233I: Finished initializing the connection to => jdbc:rds://D:\program files\IBM\Rational Insight\configtool\${ICM project folder}\Configurations\ClearQuest Test.xdc
05/15/2012 02:31:02,328 INFO com.ibm.rational.drivers.jdbc.xml.RDSStatement : CRRRE1237I: Creating a new instance of RDSStatement.
05/15/2012 02:31:02,328 INFO com.ibm.rational.drivers.jdbc.xml.RDSStatement : CRRRE1223I: Preparing the query => select "Department", "DATASOURCE_ID" from "PREP_STGC_V3TK1"."UserInfo"
05/15/2012 02:31:02,343 INFO com.ibm.rational.etl.database.services.util.SQLRowFactoryHandler : Initialize XML handler
05/15/2012 02:31:02,343 INFO com.ibm.rational.drivers.jdbc.xml.RDSStatement : CRRRE1224I: Finished preparing the query => select "Department", "DATASOURCE_ID" from "PREP_STGC_V3TK1"."UserInfo"
05/15/2012 02:31:02,359 INFO com.ibm.rational.drivers.jdbc.xml.RDSStatement : CRRRE1225I: Executing the query => select "Department", "DATASOURCE_ID" from "PREP_STGC_V3TK1"."UserInfo"
05/15/2012 02:31:02,359 INFO com.ibm.rational.drivers.jdbc.xml.RDSStatement : CRRRE1226I: Finished executing the query => select "Department", "DATASOURCE_ID" from "PREP_STGC_V3TK1"."UserInfo"
05/15/2012 02:31:02,375 INFO com.ibm.rational.drivers.jdbc.xml.internal.PageFetcherThread : CRRRE1219I: Initial URL Request => http://insiteb2.rchland.ibm.com:9060/DataServices/ClearQuest/PREP_STGC/V3TK1/Record+Queries/UserInfo?fields=Query%2Fresults%2FUserInfo%2FDepartment
05/15/2012 02:31:08,250 INFO com.ibm.rational.drivers.jdbc.xml.RDSStatement : CRRRE1228I: Close this statement
and the log messages of the failed test is:
05/15/2012 01:34:36,078 INFO com.ibm.rational.drivers.jdbc.xml.RDSConnection : CRRRE1234I: Start initializing the connection to => jdbc:rds://C:\Program Files\IBM\Rational Insight\etl\configs\Configurations\ClearQuest Test.xdc
05/15/2012 01:34:37,360 INFO com.ibm.rational.drivers.jdbc.xml.RDSConnection : CRRRE1233I: Finished initializing the connection to => jdbc:rds://C:\Program Files\IBM\Rational Insight\etl\configs\Configurations\ClearQuest Test.xdc
05/15/2012 01:34:37,851 INFO com.ibm.rational.drivers.jdbc.xml.RDSStatement : CRRRE1237I: Creating a new instance of RDSStatement.
05/15/2012 01:34:37,851 INFO com.ibm.rational.drivers.jdbc.xml.RDSStatement : CRRRE1223I: Preparing the query => select "Department", "DATASOURCE_ID" from "PREP_STGC_V3TK1"."UserInfo" where "Department" = 'MMQS'
So we can see the 2 "Preparing" messages are different:
05/15/2012 02:31:02,328 INFO com.ibm.rational.drivers.jdbc.xml.RDSStatement : CRRRE1223I: Preparing the query => select "Department", "DATASOURCE_ID" from "PREP_STGC_V3TK1"."UserInfo"
05/15/2012 02:31:02,343 INFO com.ibm.rational.etl.database.services.util.SQLRowFactoryHandler : Initialize XML handler
05/15/2012 01:34:37,851 INFO com.ibm.rational.drivers.jdbc.xml.RDSStatement : CRRRE1223I: Preparing the query => select "Department", "DATASOURCE_ID" from "PREP_STGC_V3TK1"."UserInfo" where "Department" = 'MMQS'
I copied all the files under \etl\jdbc, \etl\odbc and \elt\plugin, and overwritten the corresponding folders on the server, but it didn't work.
Anyone can help on this?
2 answers
First of fall, Windows XP is not the server official support operating system for Rational Insight.
Then, your successful and failed are different, which one has where clause and other has not. Please enable "debug" level of JDBC log, and upload failed JDBC log to help deep investigation.
Then, your successful and failed are different, which one has where clause and other has not. Please enable "debug" level of JDBC log, and upload failed JDBC log to help deep investigation.
The problem is caused by using Cognos sql when query data from a data source with type of Data Service. Insight developed the jdbc driver to query data from Data Service, and it only support limited sql. The keywords like DISTINCT, ALL and etc. are not supported, and it don't support JOIN/UNION either.
Please don't use cognos sql in ETL query to query data from data services, the complex table query and operations can be done after the data is loaded into data warehouse.
Please don't use cognos sql in ETL query to query data from data services, the complex table query and operations can be done after the data is loaded into data warehouse.