Slow record count on AS400

gerd_renz3

VIP Member
Hi List,

I recently learned my first CNC-steps on the AS400 and came across a very strange behavior during full package buildS. When I examined the BuildError.txt file I found that counting the number of records in spec tables is very fast, except for fdatext, rdatext and potext. What I mean is this:

9/1/2003 20:21:08 0 Spec file FDATEXT begun.
9/1/2003 20:39:34 0 RDB record count in FDATEXT : 208479
(18:26 minutes)
9/2/2003 00:12:32 0 Spec file POTEXT begun.
9/2/2003 00:13:04 0 RDB record count in POTEXT : 47383
(0:32 minutes)
9/2/2003 00:34:53 0 Spec file RDATEXT begun.
9/2/2003 00:56:04 0 RDB record count in RDATEXT : 234611
(21:07 minutes)

whereas

9/1/2003 21:14:29 0 Spec file GBRSPEC begun.
9/1/2003 21:14:30 0 RDB record count in GBRSPEC : 2960514
(00:01 minutes)

All other counts are as fast as lightning. I have compared with 2 other AS400 installations, different AS versions, different OW versions. The behavior is always the same. I then compared with a Windows/Oracle installation and found that the record counting is not quite as fast, still taking only seconds each, and the time spent is somehow proportional to the number of records, which makes sense. When I do select count() via SQL on the AS or even via ODBC it´s always fast.

My question to all AS400 CNC gurus out there: what is so different with these tables, fdatext/F98750, rdatext/F98760, potext/F98306 that the record counting would take this much time? If you add it up, it´s over half an hour delay just for that. Can others confirm this or am I seeing ghosts ?

Thanks for reading the above, Gerd

ERP8, SP22, Update1, AS400 V5R2
 
Gerd,

We are no longer on the AS/400 but I think you are not taking into account that this process is building the .ddb and corresponding .xdb files - it is not simply counting records.

It is telling you how many records it read and put into each of these files.

Dave
 
The first thing it does is just counting. Next, it builds DDB and XDB files.

The count() speed may correlate with whether you have any indexes on the
table. SQL Server, for example, has a "fast count" feature, when it only
counts index records without doing a full table scan and 1 sec count() in
GBRSPEC suggests that AS400 can do it to.

What I'm getting at is that you may not have any indexes on those 3
tables...

Regards,
Alexander Pastuhov
Pastuhov Consulting Pty. Ltd.
E-Mail: [email protected]
Mobile: 0414 453 433
WWW: http://www.pastuhov.com.au/index.htm


RESTRICTIONS ON USE REPRODUCTION OR DISCLOSURE
The information contained in this email and attachments (if any) is intended
to be communicated only to the person or persons named as addressees.
Copyright subsists in this email. If you are not named as an addressee you
are prohibited from using, reproducing, or disclosing the information in
this email and the existence of this email itself. The information in this
email may also be confidential and subject to professional privilege.
If so, all rights are reserved by the owner of those rights. If you are not
a named addressee please advise us immediately and destroy all copies of
this email in your possession or control. Pastuhov Consulting Pty. Ltd.


----- Original Message -----
From: "dschlieder" <[email protected]>
To: <[email protected]>
Sent: Wednesday, September 03, 2003 8:45 AM
Subject: Re: Slow record count on AS400


that this process is building the .ddb and corresponding .xdb files - it is
not simply counting records.
files.
http://www.jdelist.com/ubb/showthreaded.php?Cat=&Board=OW&Number=60841
messages, login to http://www.jdelist.com/forums, click Control Panel, then
click Edit by "Subscribe / Unsubscribe from receiving board posts by email,
change message notifications, etc." and adjust your subscription
preferences. JDEList is not affiliated with JDEdwards®
 
Alex,
when I first saw this, missing indexes was the first thing that came into my mind. They seem to be all there. As I wrote, I compared with other two installations (which were not done by me) on big machines and it is the same.

Dave, the process first does a count and then starts the ddb build. Take a close look at your logs. The times I stated are for the counting, not the actual specs retrieval and build of ddb and pak files. The xdb index is created only after the ddb file is all completed. This is a process local to the building machine, nothing to do with a database connection.


Actually, all is still a little worse. Yesterday I watched another full AS400 package build closely. It came to the fdatext record count and took, again, 22 minutes. It then was supposed to start retrieving the spec records from the database to build the fdatext.ddb file. It took another 20 minutes before I saw the fdatext.ddb start growing. Other ddb files do grow immediately after the count is done. Yes, I did hit the F5 function key frequently in my windows explorer!



Thansk for the answers, Gerd
 
hello,

the record count should not be that long here's our's from end july this year
14:35:30 0 Spec file FDATEXT begun.
14:35:34 0 RDB record count in FDATEXT : 121009

17:32:49 0 Spec file POTEXT begun.
17:32:52 0 RDB record count in POTEXT : 22714

17:52:03 0 Spec file RDATEXT begun.
17:52:04 0 RDB record count in RDATEXT : 211903

do you build it on the deployment server? have you checked the box for enable lazy close support for the central objects ODBC?
what also helped at our site for building the whole package was setting the record block size to 512 for the central objects

haven't tried building a full package yet with v5r2 yet will happen this weekend.
 
RStam,

I am curious about the record block section of the AS400 DSN. How and what exactly does this setting effect?

Thanks

Ryan Hunt
 
Richard, thanks for the data you are sending.
I certainly agree that the count shouldn´t take that long. Why are only 3 tables affected, not others, much bigger ones?
Yes, I do the builds on the DS. The lazy close suport was disabled. I will try again with this option.

I have compared 3 AS400 installations so far and always found the same. I can now only suspect that problems lies with the language records that all these three, and only these three, spec tables contain.

Can anyone with AS400 and any language installed confirm these numbers?

Thanks, Gerd
 
Gerd,

Sorry - a WAG on my part - we are no longer on the AS/400 and no one had an answer for you so I took a WAG. What you are seeing is what I believe we used to see on the AS/400 package builds.

Another WAG - Some of these files have a BLOB associated with them and I wonder if that may have something to do with the way BLOB fields are handled on the AS/400.

Our full package builds on W2K are under 2 hours and the counts are all under a few seconds.

Dave
 
Ok, I found it myself.
I build the fdatext file without language selection and it ran fast. I found the difference in a debug.log:

With language, slow:
SELECT * FROM COPY7334/F98750 WHERE ( FTLNGP = ' ' OR FTLNGP = 'P' )

without language, fast:
SELECT * FROM COPY7334/F98750 WHERE ( FTLNGP = ' ' ) .


Now that I found the cause, how could I fix this, or is there no fix? I guess I will have to live with this until all my users learn to use OW in English only.


Thanks, Gerd
 
Gerd,

The best advice I have would be to create an index over FTLNGP. Since this is how the optimizer will try to access the DB. This would allow the count to be determined from the index and not by a table scan. Also, the SQL you are talking about doesn't use the COUNT(*) format does it actually retrieve the records from the 400 to the DS and count them there? If so changing you block size might help.

Tom Davidson
 
Tom, sounds like great advice. I will discuss it with the client and see what we will do. For the time being, understanding a little better what´s going on, I am less puzzeled. I guess we could easily gain an hour´s time for each full package build.

Thanks for the tip, Gerd
 
Gerd,

we use Domestic and French languages and the counts for spec files FDATEXT, POTEXT and RDATEXT are very close to those you provided.
 
Jim,
thanks for confirming our numbers. It has become clear by now that it is the extra language that slows down the package build, and it´s not a volume issue.
It has been suggested to add an index over xxLNGP. I thought a little about it and I am not so sure now. For one, the 2 existing indexes do already include this column, and second, half of the records would have a blank in this column, the other half the respective language code (in case only one extra language is installed).
But then, I do not know enough about how the DB2 SQL machine handles selects with or without an extra index in this case. I will have to try it out.

Maybe we language-AS400-CNCs should team up to force Denver to come up with something. For us it´s over one full extra hour of package build time.

Thanks, Gerd
 
Ryan,

Since the SELECT statment does not use the COUNT(*) function to get its count, I'm assuming that JDE does the count on the build machine. By increasing the block size, you can fit more data into each packet, thus reducing the number of packets, and their associated overhead. Obviously actual block size is negociated by the two systems, so both need to be capable, and configured for the block sizes.

Hope this helps.

Tom Davidson
 
Gerd,

On the AS/400 some statistics are kept in the index. These stats always relate to the index requested. By having language as primary key, the numbers would be available from the index itself, and not need to be counted by looking at the data. Of course if JDE pulls all the records then counts, it really doesn't matter what the ES knows. Also since the where clause essentially selects the entire file, on the AS/400 at least, the perferred method of accessing the data would probably be via a full table scan, as you could then use a large blocking factor on the ES (disk to memory), most of the ideas floated so far make an assumption that a SUBSET of the records would be used. If JDE actually used the SQL COUNT(*) function, my guess is that all platforms would be able to fullfill this request in sub-second fashion.

Tom Davidson
 
Ryan,

on advice of someone from jd that came over to our site we changed this block size to 512 on the deployment server and started building a new full pack again as that did take too long a time to build.
After having it set on 512 it did build it faster.

As far as we have tested this, we only gain this speed on the central object data source with package builds.

what happens from a technical view i'm not sure but here it works.
 
Hello Gerd, I know this is not exactly what you were asking, but have you considered suggesting they move their central objects over to an NT/SQL deployment server solution? I have heard that package builds are alot quicker with SQL . We are an AS400 shop and found it was more cost effective to build a separate DEP server rather than using 100GB of expensive DASD (not to mention it saved us from having to purchase very expensive AS400 RAM down the road).

Just an option... Depending on how the AS400 has been sized and how quickly their workloads increase, it seems consistently cheaper to move CO to NT rather than upgrade a 400 later.

IMHO

Ryan
 
When you change the block size, what are you talking about, is this the Allocation Unit Size on the hard disk? How do you change the block size on the DS? I thought it had to be set when the hard drive is formatted. We currently have a Windows 2000 DS.

Thanks, Grant.
 
I believe this refers to the block size in the ODBC setttings. In ODBC Data Source Administrator, select (for example) Central Objects - PD7333, then click Configure, then click the Performance tab, then the Advanced button. There are two settings there, one for Large Objects and one for Fetch of One Row.
 
Back
Top