Pre-allocating Storage in DB2 UDB Tables:

One of the attributes that has made DB2® UDB for iSeries® easy to use and manage over the years is that DB2 automatically handles all of the low-level storage allocation for the DB2 objects. All you have to do is create the table, and let DB2 automatically handle allocating disk storage and spreading the table data evenly over the disk drives. This automatic storage allocation and management works fine for the majority of iSeries and AS/400e® customers.

However, it improves performance when you have a table in which new rows are being inserted at high rates by multiples connections and jobs -- especially in batch processing environments.
Consider preallocating storage for the rows in that table when you have a rough idea on what the maximum row count for the table will be. The reason for preallocating the table storage is so that DB2 can insert new rows into the table during heavy, concurrent loads without having to periodically allocate storage for new rows, which interruprts the insertion process. If many connections and jobs are allocating new space for a DB2 table at the same time, then the queue for storage allocation can quickly become a bottleneck.


The Add_New_Rows stored procedure requires three input parameter names:
• Table_Name, which is the name of the table for which storage is to be allocated.
• Lib_Name, which is the schema or collection name.
• Row_Cnt, which is the number of rows for which storage is to be allocated.
The stored procedure uses these parameters to dynamically construct a CHGPF request for the specified tables. Here's an example of the command that is constructed:

CHGPF FILE(MYSCHEMA/MYTABLE) SIZE(50000 1000 3) ALLOCATE(*YES)


• This command allocates storage for 50000 rows in the specified table, MYTABLE. The SIZE parameter contains 3 different numeric values:
• The first numeric value (50000) is the number of rows initially allocated for the table.
• The second parameter contains the number of rows that will automatically be added to the table if the initial row allocation is exceeded.
• The third value is the number of times that additional rows will be allocated.
• In the example shown above, storage for 50000 rows is allocated initially, and the table can support a maximum of 53000 rows (50000 + (3 x 1000)), with 1000 rows being allocated each time the row allocation limit is reached, up to 3 times.

• The Add_New_Rows stored procedure uses a system-provided stored procedure, QCMDEXC, to execute the CHGPF system command from an SQL procedure.
After the CHGPF command has been executed via the QCMDEXC stored procedure, then further action is required to actually activate the specified change. The stored procedure uses either the RGZPFM or CLRPFM system command on the specified table, depending on whether the table already contains rows. The CLRPFM command deletes all of the rows in the specified table, which is why the stored procedure first runs a query to check if there are any rows in the input table. If the table already contains data, then the RGZPFM command is used to reorganize the table to activate the change in allocation. The RGZPFM command can take a very long time to run for an existing table with a large number of rows, so use this stored procedure carefully with tables that already contain large amounts of data.

CREATE PROCEDURE
Add_New_Rows (Table_Name varchar(256), Lib_Name char(10), rowcnt integer )

LANGUAGE SQL

BEGIN

DECLARE qualified_name VARCHAR(20);
DECLARE current_count INTEGER;
DECLARE count_stmt VARCHAR(64);
DECLARE change_cmd VARCHAR(128);
DECLARE activate_tblchg_cmd VARCHAR(64) ;
DECLARE cmd_length NUMERIC(15,5);
DECLARE c1 CURSOR FOR s1;

SELECT strip(system_table_schema) || '/' || strip(system_table_name)
INTO qualified_name
FROM qsys2.systables
WHERE table_name=Table_Name and table_schema=Lib_Name;

/* Execute the CHGPF command to change the table allocation */

SET change_cmd = 'CHGPF FILE('|| qualified_name || ') ' ||
'SIZE(' || CHAR(rowcnt) || '1000 3) ALLOCATE(*YES)';
SET cmd_length=LENGTH(change_cmd);
Call QSYS.QCMDEXC (change_cmd,cmd_length);

/* Need to determine the number of rows in the table to activate
this new table allocation */

SET count_stmt = 'SELECT COUNT(*) FROM ' || Lib_Name || '.' || Table_Name ;
PREPARE S1 FROM count_stmt;
OPEN c1;
FETCH c1 INTO current_count;
CLOSE c1;

/* Use the current number of rows to determine the "activation"command */

IF current_count=0 THEN

/* No rows in the table means a clear operation can be used to get the
new ALLOCATE settings to take effect */

SET activate_tblchg_cmd='CLRPFM ' || qualified_name;
ELSE
/* If there are rows, they cannot be deleted so reorganize instead */
SET activate_tblchg_cmd='RGZPFM ' || qualified_name;
END IF;
SET cmd_length = LENGTH (activate_tblchg_cmd);

/* Activate the new table allocation values by issuing a clear or reorganize */

Call QSYS.QCMDEXC (activate_tblchg_cmd,cmd_length);

END;