The dump truck of database features driven by IBM is delivering yet again, unloading a full load of great enhancements for the beloved DB2 for i RDBMS. In addition to the new features included in the various IBM i 7.1 technology refresh (TR) releases, 7.2 has even more security, developer, and administrative friendly enhancements that I’ll be discussing in this and future tips.

TRUNCATE TABLE

The TRUNCATE TABLE statement is used for deleting all of the rows in a table in one fell swoop. You can think of this statement as SQL’s version of the Clear Physical File Member (CLRPFM)

In the April 16, 2014, issue of this august publication, I shared how encoded vector indexes can help with the task of summarizing data from large tables. Tom McKinley of IBM graciously emailed me with more information about that technique, and it is my pleasure to pass Tom’s wisdom along to you.

Assume two tables–one of items, one of sales.

create table Items
  ( Item        char (12) not null,
    Description char (25),
    Cost        dec  (7,2),
    Price       dec  (7,2),
  primary key (Item))

create table Sales
  ( ID                integer as identity,
    Invoice           dec  (9,0),
    Line              dec  (3,0),
    InvoiceDate       date,
    Customer          dec  (7,0),
    

Last issue, I began putting together a template for detecting and efficiently reorganizing physical files on IBM i partitions. I covered the most common reasons administrators reorganize files and demonstrated a procedure for detecting which files on your system need reorganization. This week, I’ll complete the process by reviewing different ways to reorganize files and look at some techniques for file reorganization while the file is active.

Steps To Effectively Reorganizing Files

As I discussed last time, here are the steps to follow for an effective disk reorganization strategy.

1. Set up a procedure for identifying files and members that need