Administration and Programming Guide for OS/400

SQL Language Environment

To learn about DB2 performance considerations, see DB2 for 0S/400 SQL Programming. This publication has a wealth of information, such as effectively using SQL indexes, improving performance of join queries, and improving performance when selecting data from more than two tables.

Use the following SQL language environment techniques to improve performance.

• Reduce the number of user IDs connecting to a database to avoid reconnecting to the database. The SQL language environment associates a user-profile and password to any remote connections to databases that it establishes. If the LOGIN and PASSWORD variables do not match the user-profile and password associated with an opened connection, the connection is closed and re-established, and the LOGIN and PASSWORD values are associated with the re-opened connection.
• Use the START_ROW_NUM and RPT_MAX_ROWS Net.Data variables to reduce the size of returned tables. If, on a SELECT SQL statement, the result set contains hundreds of records, return a subset of the result set back to the browser by using the START_ROW_NUM like a scrollable cursor and RPT_MAX_ROWS to limits the number of records returned. You should be aware that Net.Data reissues the query every time since there is no notion of state. However, you can use Net.Data support for persistent macros to store the result set in a Net.Data table that persists for the life of the transaction. See Transaction Management with Persistent Macros to learn more about persistent Net.Data macros.
• Consider calling a stored procedure that uses static SQL. Dynamic SQL is prepared at run time, while static SQL is prepared at the precompile stage. The SQL language environment uses dynamic SQL, which allows it to run SQL statements at program run time. Because preparing statements requires additional processing time, static SQL may be more efficient.

  Note that starting in OS/400 V4R2, the SQL engine has a prepared statement cache. Using the cache, the SQL engine stores away information about prepared statements, and keeps this information in system-wide storage. Then, when the same statement is executed again, even if its by a different user and a different job, the statement will run much faster. The system-wide prepared statement cache is part of normal SQL processing and requires no user action to configure or enable it. The cache may reduce any performance benefits that the static SQL might have over dynamic SQL.