Hey, Ted:

I thought the tip showing how to use the grep function to find a string was cool, so I showed it to one of the guys I work with. We are starting to use XML for an insurance system and store those files as OS/400 IFS files. The grep command will come in handy when we need to look for something and don't want to map a drive. There is one problem; with a mapped drive you can search sub-directories. It looks like you can search for all kinds of patterns with grep, but I don't see how you search through sub-directories. Am I missing something?

-- Dan

No, you are not missing anything. The grep utility on iSeries and AS/400 systems does not directly support traversal of sub-directories, although grep on some other systems does. There is a way though. Many of the Unix utilities, including grep, allow the output of one command to be the input to another command.

Hey, Howard:

-- R. Moore

Ah, packages the wonderful performance-enhancing technology from IBM. Packages relate to DB2/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 DB2/400 gets a query from a client application, such as a PC running ODBC, JDBC, or OLE DB, the query optimizer 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. The way that the query optimizer decides to access the data is sometimes called an access plan or implementation plan. Once it has a plan, the DB2/400 uses the plan to implement your query each time the query runs.

Here is the problem: Many iSeries and AS/400 shops have numerous logical files built over their physical files, so formulating an access plan can be a very expensive operation. (What? You thought it was magic?) The system must look at the access paths and determine if any of them can speed up data retrieval, and that means lots of I/O. Since formulating the plan is an I/O-intensive operation, 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 OLE DB, ODBC, or JDBC applications, the DB2/400 will optimize the SQL statements and store the execution plans in SQL package files on the AS/400. Thus, any program attempting to run the same SQL statement does not need to perform query optimization; the database engine 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:

Use the Windows Control Panel to control whether or not ODBC applications use SQL packages, and to control how packages are used. 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, from the Control Panel, select ODBC Data Sources, then select a Client Access ODBC data source that you want to modify. Go to the Packages 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, let's 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 Packages 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 setting 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, package settings for VB6 are different from package settings for 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, but 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 Microsoft 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 Package usage option, you can set the package to Disable, Use, or Use/Add. If you specify Disable, that application will ignore an available package file. 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 addresses your question. 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 DB2/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. Therefore, 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.