Cube Building with AS400 (Long Post)

[bergin]

Hi all,

please read all three posts. we are experiencing big differences in ODBC Fetches by Cognos Power Play Transformer, we think the problem is in the ODBC DLL used by Client Access as a db connect to the AS400. Any ideas would be needed. Production is a dual 933, 2.5 GB of ram and 60 Gb of storage and Dev is Dual 450, 1 GB of RAM and 20 GB of storage.

Thanks.

Rob


post #1

In Production we pull from a data source and it takes 3.5 hours, the same data from a Development server takes 2.0 hours.

We looked at ODBC and saw that the Production box is running CWODBC.DLL at version 5.00.00 and the Development is running 5.00.08.

The current version of client access or client access express has CWBODBC with 7.00.02.00 with a PTF.

Also can Tranformer build cubes with a OLE DB connection to the AS400 instead of ODBC?

Post #2

Our Data Source is an AS400 running V4R5 and DB2. My question is would I want to upgrade the Production machine to what the DEV environment is running or do I upgrade both of them to the current version of that ODBC DLL which was 7.00.02

Do you know if there is any issues with running CWBODBC 7.00.02 with Transformer?

Also do you know if OLE DB can also work as a substitute for ODBC?

Post #3

Just an update. We updated client access on our Dev box so we are now using the newer 7.00.20.00 CBWODBC.DLL driver to build the cubes.

We are building the largest cube and comparing the production logs between the 5.00.08.00 driver and the 7.00.20.00. It pulls 250,000 records and 1 million records in similar times.

We did this to ensure that the new ODBC driver would not negatively alter performance.

 

[Guest_imported]

Hi

Sorry, there you have to used ODBC, version 7 is best at present.

Check when building cube you are not generating errors on the AS400 Job log. Do this by

WRKACTJOB
Scroll down to Job, named QAZSOINT (Check spelling)
Select optiion 5 (work with)
Select option 10 (Job log)

Sometimes you see a data mapping or devide by zero error, I've produced logs of over a 1000000 lines by this.

I'f problem, change the data source to stop divide by zero, I use Impromptu to chec by writing if (cost = 0) then (0) else value/cost

Also changing the numeric class to 64 bit on the measure can make a change

One day Cognos may support AS400 directly, they said they would do this years ago but then dropped it

Regards

Tony Goldsmith