AS/400 questions At our company

[iker3000]

AS/400 questions

At our company we are using AS/400 Servers for keeping record of our customer data. In the last period we had many problems, and I couldn't get clear answer from the IT department why. I am not realy experienced with big systems (I am more comfortable with Windows based systems and MSSQL Server, because I develop (big) databases in this environment). My questions are the following:

1. The IT department told us, that our applications are not able to use a multiprocessor system (in fact thay said, that our application can only use one processor at the same time). We have now realy big servers, but the IT department suggested, that we should by a server in which one processor has a bigger capacity. Because our server is not realy old, even the best sollution would only mean a performance improvement of 10%. How can it be that our applications can only use one processor (can it be at all?). If yes, how could we change that.
2. We have 1-2 million of customers and twice as much contracts in our databases. Looking at the payments their is a maximum of 10 millions of records the database has to handle. In spite of this, the closure on every weekend lasts about 48 hours and at the and of every month, we have problems to close before the beginning of the next week. Because I have some experience with MSSql Server, I can't immagine myself any process lasting 48 hors with such amount of data (in fact I even couldn't immagine the process lasting more than a few minutes). So how can this be?
3. Can I use an Oracle or MSSQL database server on AS/400.

Thanks for any answer

Iker

 

[jmd0252]

Well,,, this is an interesting question, do you have any idea what model of AS00 you have? Most AS400s, event the small ones, we have a model 170, which with some changes can be made a multi-processor. Taking 48 hours for a close, is an awful long time. But not knowing just exactly what goes on, it is very difficult to make an judgement call. The AS400 is a database machine, is was designed and build to run databases. There is not any way I know of to install SQL server on the AS400, and use it exclusively. I would guess, and I have been known to be wrong,,, many times, that oracle is not an option. Now there are several products where you can bring the data from the AS400 into a SQL server, or Oracle. Plus it would really depend on the software, it if it supports SQL server,r Oracle. It sounds to me, llike there are other considerations, besides the multi-processor question. Like what actually goes on, when you do a month close, and the size of the AS400.

 

[iker3000]

Hi!

I'm sorry, but I don't know the exact version of AS400 we are running. The thing is, that the IT department plans to buy a new server because we can't use the existing one (which has more then one processors), at least that's what they say). Because we can only use one processor (??????) we have to buy a server with the fastest processor in it what exists on the market (this is only 10% faster than the on we use now, and given the speed in which the number of customer increases, it wouldn't help for a long time). Because we use DB2 what is integrated into the operating system, what ships with the server, I just can't belive, that there are any applications on AS400 which are not able to use the benefits of multiprocessor server. What do you think?

AS to MS-SQL and ORACLE, I've already read about it since I posted the question, and I found, that it has no sense to run them on AS400.

IKER

 

[iSeriesCodePoet]

They might not be telling you the whole story or something. To tell you the speed of an iSeries (or AS/400) I have a program that will create a 60+ page report in a few seconds. This is joining about 3 tables and some difficult selects.

To the best of my knowledge, on an iSeries, if there is more than one processor, if it is enabled, the system will use both. However with the newer systems a single CPU might be faster (you still can get more CPUs). It really depends on your system.

I think your speed problems come from ineffient programs more so than the system itself. But that is only a thought.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[jmd0252]

I agree,,,, the slow system that you are experienceing, is a sympton of something else going on. The AS400, does and will support multi-processors. The idea that you must buy a single processor system, because the mutli-processor system is too slow, esp if you are just going to see a 10% increase in thru put.. If you can find model numbers,,, that way you can look at the MIPS rating via the ibm web site,, and see what is going on. I do know that the new boxes are the market at the current time, are several times faster than what was available even 4 years ago. And in that case it would be a single vs a multi processor machine. What really bothers me is a 48 hour close,,, good grief.

 

[as400pro]

I agree with iSeriesCodePoet that inefficient programs are likely the cause.
I have been asked on more than one occasion to see if I can "speed up" an application.
One time I found where the programmer didn't CHAIN to the Customer Master file, he was reading through the file until he found the record he needed by comparing the field values, that one change alone reduced an 8 to 12 hour run to less than 10 minutes.


T. Bishop

http://www.as400pro.com

 

[flapeyre]

One time I found where the programmer didn't CHAIN to the Customer Master file, he was reading through the file until he found the record he needed by comparing the field values, that one change alone reduced an 8 to 12 hour run to less than 10 minutes.

We have a history file that has a date and time (as numeric fields, down to the second) as part of a unique key, along with a contract number. There was a program that actually waited in a loop for the system time to change before writing the record (!!). This meant that it could only process, at most, one record per second. I fixed that by writing a module that would increase the time value by one second as long as the contract stayed the same. When the contract changes, the correct time is retrieved. Things run a LOT faster now, not surprisingly.

Also, if you only want to see if a particular key is in the file, without retrieving the data, use SETLL and test for %EQUAL. SETLL doesn't take as much time as a READ or READE.




"When once you have tasted flight, you will forever walk the Earth with your eyes turned skyward, for here you have been, and there you will always long to return."

--Leonardo da Vinci

 

[iSeriesCodePoet]

as400pro, don't you hate that. Apparently some of our applications here (purchased, not home-grown) do the same thing. Ouch, that hurts performance big time. But that is exactly what I was thinking when I spoke of programs not optimized.

iSeriesCodePoet
iSeries Programmer/Lawson Software Administrator

http://www.koldark.net

See my progress to converting to linux.

http://linux.koldark.net

 

[VisionSpec]

Hi Iker,

I see alot of conjectures but no facts on which to base your next step. My recommendations would be to turn on PERFORM and start monitoring your EOW or EOM closings. Two things you should be targeting in your analysis: processor usage and I/O usage.

If your closing process is I/O intensive and the I/O is thrashing, a higher processor won't help your cause. Only higher performance I/O and more memory would help the process.

If your closing process is CPU intensive, I would look to the coding that uses the most CPU cycles and try to optimize (revert to C or ASM if necessary for that intensive portion of the processing).

The part about single processor I believe is more indicative of the sync (dependent) or async (independent) requirements of your closing process rather than actual processor. Examine your closing process and see where things can run in parallel (when they aren't dependent upon each other or each other's data sources).

Our experience has been that closing usually involve some last minute data entry (for cash receivables, etc.) that needs to be inserted before the process begins to be included in the closing cycle. We usually run sanity checks daily to make sure transactions are complete and do not require any last minute adjustments that may delay the closing process. If you have progressive reimbursement structures that depends on utilization of the entire month, that may take a while to process.

One way we've been able to minimize the EOM is to run a mid-month (MM) closing which can be initiated any time after the 15th and before the EOM. By closing the 1st half of the month, there's less transactions to take care of at EOM closing. We're currently running about 8-10 hrs for EOM closing.

Last but not least, it helps if the applications are using indices correctly.

Hope that helps...

 

[jools1971]

Hi

I recently spent two months with a client who had some new software installed that used SQL that was supposed to be faster than traditional (RPG) access.

I have two observations/questions

It seems unreasonable that any process takes such a long time. You can trace what is going on with database access on the iseries STRDBMON command, you can monitor ALL activity or just activity on certain jobs - Only use the ALL option if you have to - its quite heavy. The file you get out can tell you lots of things, including whether any new indexes are recommended by DB2 to speed things up.

I have spent ages going through these logs. The last time I built 2 indexes and cut a 80min run time SQL query to 7 seconds.

Secondly - Multi processor.
Of course if the iseries has multiple processors it will use them BUT - and here is what I think the problem you may have... any given job on an iseries can use any SINGLE processor, so four processors four jobs - one each, 3 jobs four processors one each (one unused processor). Sounds like the IT dept has got it kind of right, in that a single processor machine with a faster processor will do a job quicker as it only uses one of the processors.

But there is a way round this. If its RPG programming, you can multithread your job (not for the faint hearted) or if its query/sql based you need to 2 things - having confirmed that your iseries has got more than

1. Install licensed program called SMP (symetric multiprocessor)
1a. Pay IBM somewhere between a pile of cash and a big pile of cash for 1) above
2. Change your query processing options QQRYDEGREE to allow multi CPU parallelism

This WILL make stuff run faster - I have done this loads of times and its quite a WOW.

BUT be warned it will consume the machine if you are not careful. I have had instances where I have had to switch out parallelism during the day as user ad-hoc queries were causing havoc with other jobs. But then switch it back on overnight when the batch jobs are running.

In conclusion then... I would check the efficiency of the job first of all then if its sound but still too slow - install SMP if you indeed already have multiple CPU's which are in effect underutilised by this job.

Regards

Julian