My splitpersonalities: July 2006

How to put a record into a table ? SQL INSERT !
How to put 10 records into a table ? SQL INSERT 10 Times !! But, what if the record/table has tediously long list of fields/columns ?
How to put 100, 1000, million rows into a table ? IBM came up with LOAD utility (but, ofcourse they would have thought of LOAD even before attempting to insert 100 or 1000 rows *gag* ). It is their wise decision to isolate privilege for LOAD from other authorities and helped programmers and DBAs to delegate testing related LOAD tasks to development team.

LOAD can add/append data to non-empty table or replace the data as well. RESUME is the keyword against the much anticipated one APPEND and consiously RESUME YES stands to be the default. REPLACE is the keyword to flip data. It is allowed to use REPLACE keyword on an empty table. But, the interesting thing is: RESUME YES can work on an empty table as well But,the RESUME NO will not work on a non-empty table. Logical understanding of these constraints made me think, there is some sensible brains behind IBM Software Labs.

LOADing Partitioned tablespace would throw more interesting things that the REPLACE or RESUME keywords can be used in tablespace level or at partition level. A Partition is nothing but a part of tablespace (tablespace is divided into 'n' parts) and mainly aimed at increasing parallelism. Because, most of the operations on any Partition can be done independant of other partitions. For example, each partition could be defined to have similar data for different departments and any data engineering could be done concurrently for each departments.

A REPLACE on the tablespace level, deletes all the data from all the partitiones of the tablespace, before loading. The LOAD statement could be loading particularly for just one or few of the Partitions alone. Unfortunately and unexepectedly, remaining data would be lost. A REPLACE keyword on the partition level just deletes the data from the partition alone.

LOADing the partitions concurrently is absolutely doable but, on conditions. The LOAD involves the building of Non-partioning indexes(NPI) too and parallel loading of partitions will not work if the paritions are loaded from different MVS jobs. Some used to drop such NPIs to do parallel LOAD and then recreate the dropped ones. However DB2 in its V7 brought in feature to LOAD partitions of a tablespace in parallel, if they are done in a single MVS Job step. The Keys for PI & NPIs are collected in parallel LOADs and later build.

Improve performance of the LOAD by building index in parallel during the RELOAD itself. SORTKEYS would tell DB2 to do it. An optional keyword in V7, IBM made it default in V8 that, anywhere the process of LOAD fails during the RELOAD phase, the process starts from the top. Its pity that its good when its good and very bad when its in bad state. In a way, parallel build and the default SORTKEYS give a sigh of relief when the data is loaded in RESUME YES mode. Since the reloaded records are rolled back, the database is not corrupt or inconsistent. If it is in V7 LOAD RESUME YES, the table goes corrupt and it becomes a complicated scenario to set it right.

Is LOAD = crude LOAD + REBUILD + CHECK DATA + IMAGECOPY + RUNSTATS ?? Yes, it is. There are times, when I had done LOAD in bits N pieces.

PS: The author of this post is a
IBM Certified Specialist - DB2 v6.1/7.1 User
IBM Certified Solutions Expert - DB2 UDB V7.1 Database Administration for Os/390
IBM Certified Solutions Expert - DB2 UDB V7.1 Applications Development
IBM Certified Database Administrator - DB2 UDB V8.1 on Linux, Unix & Windows
&
A Fucked up confused bachelor with lots of distasteful thoughts and lives on profanity.