R3190 Slowdown - Any ideas from the gurus? Peoplesoft is stumped

We are multi-foundation and recently went through the process of updating 3 of our environments from update6/sp20 to update7/sp22 S1. We did this over the course of several weeks. On the first two updates, everything went fine and the R3190 ran at the same speed that it always had in both environments. After the third upgrade, the R3190 began running like a *dog* on the server in all 3 update7/sp22 S1 environments. It runs at a normal speed when run locally (fat client).

The major difference (to us) in what happened with the third update we performed is that we patched our server OS from Win2K sp2 to sp4 and our database level from SQL2K sp2 to sp3a. Right after that, the R3190 went from around a 15 minute run on the server to 3+ hours (we don't know how long it would take to let the report finish - we've had to terminate it so that other reports could run - but based on the rate of record processing we saw in the debug log, we think it could take up to 6 hours).

We have looked at this thing from various angles - spec - but it was only changed for the third environment and should not affect the first two, indexes - regenerated them with no improvement, client-server - did a get from the server with no slowdown on the client, logging - no errors and you can watch the data process in the debug log and see that the report is moving forward.

Since we still have a couple of environments on update6/sp20, we verified that they still run normally on the server. Our CNC admin also reconfigured the report to run on our backup enterprise server, which is update7/sp22 but still on the old OS and SQL service pack levels - it runs normally. In fact, that's our current workaround, but it's not the long-term solution we want.

All of our other reports and applications are running normally, some faster than before as reported by the users. We suspect that this has to be related to the change in OS level or SQL level, but Peoplesoft does not agree. However, they don't have any other ideas, and at this point, I feel like we're going in circles with them.

I'm wondering if anyone else has experienced something like this or has a good idea for us.

Shelley,

I experienced this slow down after instaling SQL SP3. There was a serious MDAC bug that was distributed with SQL2000 SP3, but this was fixed in SP3a. As long as you installed 3a you should not have this issue. If the report runs fine from a fat client, or another enterprise server I think it points to MDAC or ODBC on the SQL server. I suggest downloading the MDAC checker from the Microsoft web site to verify what version your MDAC dll's are at on the server. If you want to compare versions you can contact me directly or post back to the list. You can also compare them with the MDAC versions on your enterprise server that does run properly.

I would suspect that something is going on that the jde.log would highlight.

If you haven't done so already, try turning on the server log:

1) stop the server services
2) open system\bin32\jde.ini

3) Setup the [DEBUG] section like:

LogErrors=1
Output=NONE
Trace=FALSE
DebugFile=E:\JDEdwardsOneWorld\ddp\B7333\Log\jdedebug.log
JobFile=E:\JDEdwardsOneWorld\ddp\B7333\Log\jde.log
ClientLog=1
JDETSFile=E:\jdedwardsoneworld\ddp\b7333\log\JDETS.log
KeepLogs=1
RepTrace=1
TamTraceLevel=1

note that if you see the Output=FILE in your jde.ini, then you found your problem. This would definitely have caused the slowdown you are experiencing.

4) Save and restart services
5) Look in the directory where you specified JobFile above. You should see newly create .log when you restarted your services.
6) Run R3190.
7) Monitor the log files. If there is a catastrophic problem, the log file for the job should be getting filled up with errors. If this is the case, open the log file to see if you recognize what the potential problem could be.

Possible causes could be data dictionary, DB connect problems, ODBC, ODBC tracing turned on...

Best of luck...

Paul,

It turns out that while we *thought* we had installed sp3a, after a closer look, we realized it was sp3. Very embarrassing, but I believe upgrading to sp3a and MDAC 2.71a will fix our problem, given what you've said. We'll be doing that this weekend. Thanks for prompting us to re-check.