The functions mentioned (SQLTables SQLColumns) can be VERY expensive as they each can return millions of rows.
In addition, as Mr Giltner suggested, the Mainframe is a shared resource supporting many users and many applications. A common mistake made by users of poor Linux machines is they use issolation level RR (Repeatable Read). Most of the time issolation level CS (Cursor Stability) or UR is the prefered choice. Your application may not only be causing delays for your self but for 1000's of other users as well. As you are reading through the catalog you areobtaining and holding locks (due to RR) There may be a delay in obtaining the lock and other people may be delayed to you holding it. Five important coding tips 1) Avoid RR 2) Always commit or terminate at the end of each UOW even if you know you are inquiry only. 3) Check SQL return codes, consider any negative value is a problem even if you don't know what it means. 4) Reduce traffic across the communications link by using stored procedures. 5) Existance checks are expensive if "not found" is unexpected deal with it from the return code rather than via an extra request. DB2 z/OS V7.1 goes out of service the end of June 2008. Tell your mainframe people they need to upgrade. Try the DB2-L listserv for more DB2 / SQL support. In the mean time put in an order for an extra hard drive for your poor Linux machine so, if needed, you can run the bank on it. Best Wishes Avram Friedman -----Original Message----- From: John S. Giltner, Jr. [mailto:[EMAIL PROTECTED] Sent: Monday, November 19, 2007 9:29 PM Subject: Re: performance of db2 7 on mainframe [EMAIL PROTECTED] wrote: > hello, > I'm having a performance issue with a Windows CLI/ODBC application > that connects to a DB2 rel 7.1 on a mainframe of a bank. > I have to say I'm pretty new with this kind of environment. And I > don't know the details of the HW of the host. I just know it's an host > of a bank (and I expect it is expensive). > The application is working, but it is much slower than when I run > against a DB2 7.1 on a poor Linux machine(using the same amount of > data in the tables used by the application). > I made some trace and I found the most noticeable difference is in the > time of the SQLExecute. Against the Linux is not relevant (the same > millisecond), against the mainframe it tooks an average of 2 cent of > sec. Do you mean 2 percent of a second, or 0.02 seconds? > This mainframe also has a long time in execution of SQLTables and > SQLColumns (almost 15 sec both), so that I've already fixed my > application to check for table existency and for information on the > columns, using a dummy select and SQLDescribeCol. > I'm now in dubt if: > -I have to reengineer the logic of the application or > -the fact it run faster on a poor Linux machine meas that there are > some problems with something outside of my app > > thanks in advance to anyone who can answer > LD What is the connection between your Windows box and the z/OS box? When running against Linux, what is the network connection? How much data is returned? What has the z/OS DB2 system programmer said? There are a lot of other factors that would need to be looked at. A "poor" linux box running nothing but DB2 could out perform a z/OS box that is running 5, 10, 20, 100 other applications that are doing 100, 1,000, or 10,000 of transactions a second. ---------------------------------------------------------------------- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html ---------------------------------------------------------------------- For IBM-MAIN subscribe / signoff / archive access instructions, send email to [EMAIL PROTECTED] with the message: GET IBM-MAIN INFO Search the archives at http://bama.ua.edu/archives/ibm-main.html
