EnterpriseOne 81.0 Performance

We are experiencing some performance issues post go-live 8.10. Outside JDE, I perform queries and have some middleware using straight ODBC connections against JDE tables. These queries are now causing significant performance degradation. When I run a simple query over a larger table, such as the F0911, without any joins, the CPU churns at 95+ percent usage and the system comes to a grinding halt. None of this happened during testing. An example SQL statement passing through an ODBC connection would be: SELECT * FROM F0911.

There are two differences between our test environment. First, our production box has more horsepower (CPW and memory) than our test box. Secondly, the tools release for production is 8.93 R1 while the tools release for test is 8.93 D1.

Further points to note. When I issue the SQL statement against production, it not only kills the iSeries (and I'm not exaggerating, it kills it) it also kills the client machine. Bear in mind the client is NOT using EnterpriseOne for anything, this is pure SQL going through an ODBC pipe. The same query going against test goes slow, but doesn't kill the box nor does it kill the client.

William

I don't have an answer for you but I'm curious...did you convert your data to unicode? I'm in the middle of an upgrade from XE to E810. The conversions done and I'm retrofitting now. I haven't done a lot of test yet. I fear that we will have the same problem you are experiencing.

Patty

It might be related to indexing. Did you compare the indexes on the F0911 in production against those in your test environment?

Hi William,

First of all : have you purged your *SQLPKG?
I had similar situations in E810 and it was due to
*SQLPKG in Business Data and Control Tables libraries.
I recommend you to stop JDE and JAS services (on
the AS400), delete all of these files and restart JDE.
You should also consider a reindex (from OMW) of your
largest tables.

Regards, Sebastian Sajaroff

It is a good idea not to run wide open queries on tables like F0911 or
F42199, qualifying it with an index (logical files are selected based on the
Data selection) always gives better result. AS/400 always will run these
queries as interactive jobs and and will allocate maximum resources to the
job.

You need a good AS/400 who can do some tuning and apply some restrictions.
Even after all the tuning you may not get satisfactory query performance on
AS/400.

Hope this helps

Devi Misra
Senior JDE Consultant

Patty,

Yes we converted to Unicode, but both environments are unicode converted and production is the only one of the two that is giving us a problem. Unicode is something we first considered when looking into this problem, but we ruled it out since the problem only manifests itself in production even though both environments are unicode converted. Side Note: NWS is predicting another nasty 'cane season, batten down the hatches there in Ft Myers! Orlando had more than it's share last year, I think we'll be spared this year.

Nicolas,

The indexes should be the same, how can we verify? I doubt this is the problem since I am performing a simple ODBC pass through, the iSeries should simply act on the request (SELECT * FROM F0911) and return results. Furthermore, both development and production tables were created by EnterpriseOne and their table layouts, along with the indexes, should be identical.

Sebastian,

We IPL the box every week (on Sunday). When the box is IPL'd, we automatically delete all non-IBM SQL packages. Again, I see performance degradation while performing SQL queries outside EnterpriseOne (using Visual FoxPro to be specific). We too have had problems withe the SQL packages which is why we delete the packages every week.

You can use either Ops Nav or native 400 commands to manually compare the logical file specifications. How many records do you have in the F0911? I also agree with Sebastian on his thought on reindexing the large tables through OMW.

What version of iSeries Access are you running? Are all your PTF's up to date? Group Database? Hypers? CUME? There are also several individual PTF's required for the iSeries.

For iSeries access what is your LOB threshold size? If using iSeries Access V5R3 you MUST be at SI17XXX. This fixes the database connection drops and the LOB issues which scrambled PO text on earlier SP's of iSeries Access V5R3.

Are the ODBC settings the same between the different datasources?

Also you may want to try deleting the IBM SQLPLG that's in QGPL. It atomatically gets recreated when you restart the database subsystem (or just IPL). See the attached doc for more info. NOTE: This ofen fixes alot on OS/400.

The other thing I'd check is the setup of the two boxes. Are all the system values the same? Also are you prestarting ODBC jobs on both boxes (QZDASOINIT)? Pay attention to CCSID, Performance Adjust, etc.

Colin

William,

We have seen the same kind of issues on our system. We are a Windows 2000/SQL 2000 house, but the concept is the same.

Our solution is to not allow direct queries to production databases. Direct queries do not play nice and whack performance.

We use Cognos and have all of the data replicated over to Cognos in real time. If Cognos is not a good fit for the query, we have the user do the query against the database for Cognos. Yes, it means we have redundant data (Cognos and JDE databases both reside on our server, it's a bullmoose), but that's a better trade off than killing the performance of our production environment.

Our other trick is that we are using an Active/Active Database configuration. We have two rather large Database Servers. We have three seperate instances of SQL 2000 in three different Windows 2000 resource groups. We run Production JDE databases on Node A. We run Production Cognos databases on Node B. The servers act as high availiblity backups for each other, so if need be, we can run both of those databases on the same server. For Disaster Recovery, those two boxes reside in seperate data centers one mile apart.

The table that you are trying to hit is huge (and will get bigger daily) and is CRITICAL to the application. Our mantra, thou shalt not mess with Production data and performance.

Gregg Larkin
JDE System Administrator (CNC) / North America
Praxair, Inc.

Thanks for the reply, but you are missing the point. This did work under Xe and now it doesn't. We also don't allow users to query the data, that isn't what this is about. We have a few custom dll's that broker an exchange of data with some disparate systems (cashiering for example). These middleware brokers need access to real time data, and all we are doing is SELECT'ing small data sets periodically, no updates.

I'd have to check on the iSeries Access version, but before I go into that... This runs fine on our development box EVEN THOUGH our production box is the exact same model but with 5 times the processing power and memory. Production has the horsepower, development does not. Production runs slow, development does not. Time of day has NO effect.

Yes, we delete SQL packages once a week. Thanks for the heads up but we are aware of performance degradation with SQL PKG's. Your document attached is a great primer for anyone not familiar with SQL packages, thanks.

PTF's are up to date. ODBC settings are the same between the datasources.

One thing we have noticed is that the disk drives on Production are getting hammered when the remote query is taking place. We are seeing 80 - 100% disk usage on 10 out of 14 of the disk drives while the query is taking place. Our iSeries guy says he isn't concerned, but I am and so is my CNC guy. Any iSeries Gurus out there, is it normal to have such heavy disk usage on an iSeries during SQL Select's?

I'd be concerned. Perhaps your iSeries resource can enable, if it's not already, perftools on the iSeries and therefore get really useful information from the SQL query analyzer. It will tell you what logicals were considered, which was used, and actually, and I'm not making this up, suggest what new logicals would help things. I would never think that a simple SQL select would cause that much DASD thrashing on the iSeries...unless other things are causing it.

Regards,