Poor Query Performance on my iSeries 270 @ V5R2???

[AS400Admin]

I am relatively new to AS400 but I have been enlisted as my company's AS400 admin and DBA (I am already a SQL DBA). We have an iSeries 270 with max'd out hardware, and running V5R2. I have been reading redbook after redbook discussing the vast hardware performance improvements, and of course, the new SQE. Everything I have read sounds pretty impressive. Nonetheless, my query performance is far from impressive and far behind our SQL boxes. I just don't get it.

I have been investing time in learning about V5R2's new SQE, access plan cache, Statistics Manager, and redesigned MI layer, yet I cannot find anything wrong with my databases or my setup.

Does anyone have experience with both DB2 and MS SQL? I have transfered large tables from DB2 to SQL, run identical queries, and SQL is killing DB2. Since our largest investment and most important systems are tied up in our AS400, I am pretty frustrated.

Any input would be appreciated.

Thanks

Ryan Hunt

 

[uman3000]

you may check:
disk defrag and separate this job on ist's owen drive
memory allocation for the job (assuming maximum memory in the box)
job priority
other background programs that may need to be stopped

U

 

[flapeyre]

If you have ORDER BY clauses in your SQL statement, are they the same order as the access paths of the file? If not, build an index, and use that in the query instead. Queries slow down considerably if the query optimizer has to build the access path.

You can get some clues as to what is happening by running the query under debug (STRDBG) and looking at the job log when the query finishes (if running in batch, make sure the job description used will create a job log - use the CHGJOBD JOBD(your_jobd) LOG(4 00 *SECLVL) LOGCLPGM(*YES) command to do this).


"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

 

[AS400Admin]

I found the discrepancy. OS400/DB2 does not return drive space back to the system when records are deleted. Rather, deleted records are marked as inactive and retained in the file until a RGZPFM is performed. The table in question had ~380,000 live records and 32 MILLION deleted. Therefore, my select * was doing a table scan against a total of 32,380,000 records, not 380,000.

Thanks

 

[flapeyre]

In that case, if this file has a lot of deletes, you can change it to reuse deleted records:

CHGPF FILE(Filename) REUSEDLT(*YES)

A better way would be to periodically reorganize all PF's on the system (or at least in that library). There are plenty of freeware utilities to do that; I searched and found one:

http://hkrebs.dk/rgzpfmbrs.html

"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