What are Packages and Basic Package Problems

Hey, Howard:

Our company had a problem when all of our ODBC connections failed to retrieve data from the AS/400. To fix the problem, one of our techs deleted the SQL Packages from the production AS/400,then restored the SQL Packages from our Development machine.  What are SQL Packages? Any light shed on this issue would be greatly appreciated.-- R. Moore

Ah, packages the wonderful performance-enhancing technology from IBM. Packages relate to AS/400 query execution plans and are a great way to optimize the performance of your client applications.  The best way to understand packages is with a little background information.

When the AS/400 gets a query from a client application, such as a PC running ODBC, JDBC, or OLEDB, the AS/400 has to decide how to perform the query. The AS/400 query optimizer is the program that decides how to perform the query.  First, the optimizer compiles a list of the physical files that your query is accessing. Then it retrieves a list of the logical files built over the physical files. Next, it evaluates your query's ordering, join, and where criteria against the physical implementation of your database and chooses the best way to retrieve your data. he way that the query optimizer decides to access the data is sometimes called  an access plan, implementation plan or access path.  Once it has a plan, the AS/400 uses the plan to implement your query.

Here is the problem: Many AS/400 shops have numerous logical files built over their physical files, so formulating an access plan can be a very expensive operation. (What? y\You thought it was magic?) Since formulating the plan is both computationally and I/O-intensive, you should avoid that process in production environments as if it was the black plague. A Package file can come in handy for helping to avoid unnecessary and repetitive optimizations.

If you enable package support on your OLEDB, ODBC, or JDBC applications, the AS/400 will optimize the SQL statements and store the execution plans in an SQL Package file on the AS/400.  Thus, any program  attempting to run the same SQL statement does not need to perform query optimization; the AS/400 automagically looks up the SQL statement in the SQL Package file and if it is found, executes the stored access plan. This will reduce both I/O and CPU because the optimization step of implementing the query is avoided. 

How to use packages:

For ODBC applications you can control whether the application uses an SQL Package and how the package is used via the ODBC Data Sources applet in your Windows Control Panel. Note that the following information is version-specific. IBM changes the order and layout of information in the ODBC configuration from time to time, but the names of the fields should be consistent. First, start the applet, and then select a Client Access ODBC data source that you want to modify and go to the Package(s) tab, where you should see a check box called Enable Extended Dynamic Support. In some versions of CA, I believe that this was available via the Performance tab. If this box is checked, your ODBC applications that use this data source will use packages on the AS/400.

Now, to make things a little more confusing, lets talk about some of the other settings that you can alter when an application uses packages, (all of these settings are on or available from the Package(s) Tab). The Default Package Library specifies where the client program should look for its package files. If no package file exists for the application, it will create the package file in this library.

The Application Name drop-down list allows you to select a particular Windows application and associate it with a specific library, file, and settings just for that application. This function can be useful if you are running multiple applications that use the same data source to connect to the AS/400. For example, on my machine, VB6 has its own unique settings  as to how it should use packages that are different from the settings for the application crw32 (Crystal Reports),. Note that the application name isn't really the name of the application as you and I would think of it, it is the name of the executable (EXE) file that you use to start the application. Therefore, if you wanted to set up specific package setting for <B>Microsoft</B> Excel, the Application Name would be EXCEL.

For each application you define, you can set the name of the Package file, choose the library where you want the Package file to  reside, and determine how the package is used. For the Usage option, you can set the package to Disable, Use, or Use/Add. If you specify Disabled, even though a Package file is available, it will not be used by that application. If you specify Use, the Package file will be used but no new execution plans will be added to it.  If you specify Use/Add, the package will be used, and if the AS/400 encounters a query that is not in the Package file, it will store that query and execution plan in the Package file.

Now, here is a setting that is very important to you, Mr. Moore. The Unusable Package setting controls how the client application should respond if there is a problem with the SQL Package file. In your case, the setting is probably set to Error, which causes the client application to receive an error message when it attempts to execute a query and the package file is corrupt or unusable. Setting this option to Warning would cause the client application to receive a warning message when a package is not usable. However, some poorly coded client applications incorrectly interpret SQL warnings as errors, (or worse yet, show the messages to users who then call the help desk), so you might want to set the option to Ignore. If set to Ignore and the application encounters an error in dealing with a package, the AS/400 and client application will merrily continue and not use the SQL Package file.

Another setting you can control is the Clear Package if Package Size Is setting. By checking this box and setting a size limit, you cause the Package file to be cleared when it reaches the size you specify. In addition, the Cache Package Locally option allows the application to maintain a copy of the package file in local memory (on the client) and can improve performance. Thus, the client memory and processor are used to search for the SQL access path first, before giving the query to the AS/400.

Now, IBM changed the interface a little bit in version 8 of the ODBC driver and you now have a pop-up window to configure each application separately. Also, note that in ODBC driver version 7, if you do not specify an application name, you are specifying settings for all applications that use the source by default As application use the data source, the package information will be stored in the Windows registry and associated with that application. So, when you first enable extended dynamic support, the drop-down list of applications will be blank. Run a few applications that use that data source, and then go back into the Data Source administrator program and the applications will appear in the drop-down list.

In JDBC, OLEDB, and ODBC, you can also control the use of packages based on the arguments that you supply in the connection string to the AS/400. Any connection sting arguments will override the package settings in the ODBC data source. I have an article available at www.sqlthing.com under the AS/400 tab that overviews connection string arguments that can affect packages and package use.

Managing Packages

Ok, so now that we have an idea of what packages are and how they are used, lets briefly talk about how you want to manage packages on your AS/400 and with your client applications. First, do not bother to use extended dynamic, (packages), if your client application is all dynamic SQL and the fields in the Where clause, Group by, or Order by change from one run of the query to the next. You will end up with a  really big package file of queries that are not reused, (and shame on you for only using so much dynamic SQL). Packages can greatly improve the performance of well-architected client server applications as they reduce the use of the expensive query optimizer leading to less CPU and less I/O contention.

During the development phase of a project, I typically disable extended dynamic because I am executing so many queries and the queries rapidly change; I do not want the AS/400 to store the execution plans in this fluid environment. In fact, most of the time I am executing the queries not in the application, but doing query development, benchmarking and performance testing in SQLThing. I’m also usually running the queries in Debug mode so I can see all of the implementation messages from the AS/400 query optimizer.

Once I get close to the testing, performance and quality assurance phase of my project, I turn on extended dynamic and allow the AS/400 to store copies of my queries in a package, (using the Use/Add option). I ensure that I go through all of the queries in the application so that all queries are optimized and stored in the package. (In fact, most times I save all queries to a file and then use SQLThing to run the script so I know that each query is stored in the Package file). When I deploy my application, I ensure that the data source is set to use the Package file that I created and the application is set to Use, as I do not want any new access paths saved in the package, (especially if my client application has some dynamic SQL). In addition, I typically set the Unusable Package setting to Ignore so that if the Package file is corrupted or unusable, my client application does not see an error.

Finally, let me relate two performance stories about extended dynamic use in JDBC and then in ODBC. In the first case, the client was using JavaServer Pages (JSP) to display information from a 10,000,000-record table. Performance was intermittent when the application went to production, sometimes taking up to 60 seconds to display the resulting page. All of the processing time was spent on query optimization, (60 logicals had to be evaluated when attempting to formulate an access plan). By writing ten versions of the query--one for each different combination of how the user might request the display--using the prepared statement object in JDBC,  passing parameters to the query and ensuring that the statements were packaged by the settings in the connection string, I took the average data response down to .5 seconds per request. Example number two is an insurance company running ASP pages against their AS/400 database. Again, all pages were executing using dynamic SQL and no extended dynamic support. Some pages took between 20 seconds and two minutes to display, again most of the time wasted on query optimization. By having the ASP pages use the ADO Command object, and then pass parameters to the queries and ensure that all queries are packaged, the average data response is now always less than .25 seconds. -- Howard