Discussions

Performance and scalability: Running SQL statement on iseries DB2 is hell ??

  1. Running SQL statement on iseries DB2 is hell ?? (6 messages)

    I have written a servlet which connect to the i820 AS/400 V5R1 DB2 Database using the old fashion JDBC standard programming from the beginning. Right now, my boss is pushing me so so so hard to speed up the web since some of the SQL statement needed to join 4 or more table to get the result, and it make the AS/400 slow like hell. So I am tempting to rewrite the data retrieval part of this application, is there any GOOD solution against AS400 with servlet beside data caching, since our data needed to be very REAL TIME !!

    I am looking at the Hibernate, but I am not sure about the preformance since it will generate the SQL statement to retrieve data too, that's pretty similar to what my problem is.

    Hi all experts, is there any good solution to suggest? Thanks !
  2. If it's anything like DB2 on OS/390, then your best bet is to use Stored Procedures close to the database. They'll call it Static SQL rather than Dynamic SQL (which is what JDBC mostly is). Your other option is to use SQLJ but I never really liked that option since it require precompiling your source and wreaks havoc on my IDE. That will also enable Static SQL.

    Hope that helps.
    Andy O
  3. Thanks a lot, let me see which one is the best for my current situation. But if I assume I will re-write the code all over again, what's the best bet? Thanks !
  4. DB2 on iSeries[ Go to top ]

    Hi, I used to be an AS/400 programmer, have you set up the keys for your access paths on your physical and logical files within the iSeries database?
  5. Use SQLJ for real good performance[ Go to top ]

    Hi!

    DB2 on OS/390 is extremely slow in dynamically parsing SQL statements. Ensure that your SQL statements are optimized. Avoid any type of table scan or index scan. Use "explain" to see if DB2 uses a good path to your data.

    For better performance use PreparedStatements and reuse the once prepared statements (I saw people using PreparedStatements but never reusing them - this makes no sense).

    But to get the best performance - use SQLJ. The output is a so called bind package that will be bound to the database. This is faster than PreparedStatements since DB2 is able to optimize the path while binding.
  6. Use SQLJ for real good performance[ Go to top ]

    Thanks for all the replies. Me and the AS/400 programmer now worked out a solution, to speed things up. He will use RPG to write the stored procedure, and I just need to call that SP from the java application. And the result is pretty fast and the workload of as/400 has been reduced a lot. But beside the work on as/400 programmer side, is SQLJ can do the same thing, if so, I will start to learn SQLJ than, so I can master the SQL's condition instead of asking him to do changes on this and that !!
  7. SQL DDL or DDS[ Go to top ]

    Are these tables defined through SQL DDL, or through the native iSeries DDS spec?