Can you recommend a good book or methodology to make sure that we get the best performance out of our databases?
This is a very vast subject. First of all, database can be very different. The database may be very old style, with few indexes and no views. It could be more modern, relational but still DDS based. It may be SQL based. Also, it may be read with old style tools (which are sometimes very quick) like FMTDTA or the RPG cycle. RPG may access them with or without blocking (blocking enables up to 32 kb of data to be read into the memory in one go). It may be accessed via OPNQRYF or SQL and this with or without very long examination of available access path or creation of access path. A book may give you some ideas, but if you want to improve certain processes, you need to observe. You may want to check option 14 of WRKJOB and see how many times a file is read. If some records are read repeatedly even though the records are not changed by the process, would an array built in a program be more efficient? If you need SQL or OPNQRYF and it creates a huge and slow index, would it not be possible to do a first SQL or OPNQRYF to put your records into a temporary file and then order the file? Test is the answer. And test in live conditions. If your database contains millions of records, testing performance on a few dozens simply won't do. When exploiting a database, some processes may increase proportionally to the number of records, but some others may do so geometrically.
MORE INFORMATION ON THIS TOPIC
Search400's targeted search engine: Get relevant information on DB2/400.
The Best Web Links: tips, tutorials and more.
Check out this online event, Getting the Most out of SQL & DB2 UDB for the iSeries.