Chana, a C# developer, sent me the following message:

"We are using iSeries ODBC driver to connect to AS400 files and write/delete some data to it. The issue is that when I use DELETE FROM MYFILE command, rows deleted, but size of the file stays the same as previous to delete. That's why this file continues to grow constantly and finally (as we suspect) causing problems inserting data to it. Can you help me with this?"

I did not have a chance to get back to Chana before writing this article, so I'm going to go out a limb here and assume that Chana's experience may be more on the .NET side than on the IBM i side (and yes, I know the danger of "assuming"). So what I'll describe here will be elementary to IBM i developers, but may be new to .NET developers who work with the IBM i.

Based on Chana's message, it seems to me that the issues are related to the member size and reuse deleted records properties for the file. You can check these properties using the System i Navigator (the easy way) or IBM i commands entered on a 5250 command line (the more difficult way).

The Easy Way

Start the System i Navigator and drill-down into the Databases section for the IBM i server. Expand the schema (library) and click the Tables item to select it. The tables in the schema are displayed; right-click the table that you need to check and click the Description item from the pop-up menu, as shown in Figure 1.

The Description dialog shown in Figure 2 is displayed. On the General tab, locate the Reuse deleted rows checkbox. If it is not checked, then the storage for a deleted row will not be used for any subsequent inserts into the table.

On the Allocation tab (Figure 3), you can view the row capacity of the table, expressed as the initial number of rows (when the table is created), the increment number of rows and the maximum number of increments. Note also the No maximum rows checkbox; if checked, there is no upper limit (other than O/S or disk limits) on how many rows can be in each member in the table. On my IBM i at V5R3, when I check the no maximum rows checkbox, the row capacity is reported as 4,294,967,288.

You can change the reuse deleted rows option (General tab), the no maximum rows or sizing properties on the Allocation tab. Changes take effect immediately.

The More Difficult Way

The more difficult way to check these properties is to use IBM i commands. On a 5250 command entry line, enter the Display File Description (DSPFD) command for the file you want to check. For example:

DSPFD FILE(ASPNET/QCUSTCDT)

On the first page of the display (Figure 4), verify that the Type of file is Physical. If the type of file is Logical, follow the steps described below to determine which physical file is associated with the logical file. The properties are associated with the physical file, not with any logical files built over the physical file.

If the file is a physical file, scroll to the next page of the display, as shown in Figure 5. You can view the size properties on that display.

Finally, scroll to the third page of the display, shown in Figure 6. The reuse deleted records property is displayed on that page.

To change the reuse or size options, you can use the Change Physical File (CHGPF) command. An example is:

CHGPF FILE(ASPNET/QCUSTCDT) SIZE(*NOMAX) REUSEDLT(*YES)

Working with a Logical File

If you are using the DSPFD command and the file is a logical file, scroll down until you locate the Files accessed by logical file information, shown in Figure 7. That section shows the physical file that you need to check.

Cautionary Notes

Before changing the reuse deleted records or sizing properties, you need to carefully consider how those changes might affect existing applications. In the vast majority of applications, you can change the properties and there will be no problems. However, some older applications might have been created with the idea that rows in a table would always be in a certain sequence (for example, all new rows are added to the physical end of the table). If you enable the reuse deleted rows property, there is no guarantee that newly added rows will be added at any specific location in the file.

To the best of my recollection, the sizing properties were originally included (starting with the System/38) to prevent "run-away" files from consuming all of the then-limited disk space.

Files that are created with the Create Physical File (CRTPF) command are by default set to not reuse deleted records. The CRTPF sizing defaults are 10,000 initial size, 1000 increment size and 3 increments. When you create a table using the CREATE TABLE SQL command, the reuse deleted records option is set to yes and there is no maximum size for the table.