DB2 Version 9.7 for Linux, UNIX, and Windows

Load sessions in a partitioned database environment - CLP examples

The following examples demonstrate loading data in a multi-partition database.

The database has four database partitions numbered 0 through 3. Database WSDB is defined on all of the database partitions, and table TABLE1 resides in the default database partition group which is also defined on all of the database partitions.

Example 1

To load data into TABLE1 from the user data file load.del which resides on database partition 0, connect to database partition 0 and then issue the following command:

   load from load.del of del replace into table1

If the load operation is successful, the output will be as follows:

   Agent Type     Node     SQL Code     Result
   ___________________________________________________
   LOAD           000      +00000000    Success.
   ___________________________________________________
   LOAD           001      +00000000    Success.
   ___________________________________________________
   LOAD           002      +00000000    Success.
   ___________________________________________________
   LOAD           003      +00000000    Success.
   ___________________________________________________
   PARTITION      001      +00000000    Success.
   ___________________________________________________
   PRE_PARTITION  000      +00000000    Success.
   ___________________________________________________
   RESULTS:       4 of 4 LOADs completed successfully.
   ___________________________________________________

   Summary of Partitioning Agents:
   Rows Read                   = 100000
   Rows Rejected               = 0
   Rows Partitioned            = 100000

   Summary of LOAD Agents:
   Number of rows read         = 100000
   Number of rows skipped      = 0
   Number of rows loaded       = 100000
   Number of rows rejected     = 0
   Number of rows deleted      = 0
   Number of rows committed    = 100000

The output indicates that there was one load agent on each database partition and each ran successfully. It also shows that there was one pre-partitioning agent running on the coordinator partition and one partitioning agent running on database partition 1. These processes completed successfully with a normal SQL return code of 0. The statistical summary shows that the pre-partitioning agent read 100,000 rows, the partitioning agent distributed 100,000 rows, and the sum of all rows loaded by the load agents is 100,000.

Example 2

In the following example, data is loaded into TABLE1 in the PARTITION_ONLY mode. The distributed output files is stored on each of the output database partitions in the directory /db/data:

   load from load.del of del replace into table1 partitioned db config mode 
      partition_only part_file_location /db/data

The output from the load command is as follows:

   Agent Type     Node     SQL Code     Result
   ___________________________________________________
   LOAD_TO_FILE   000      +00000000    Success.
   ___________________________________________________
   LOAD_TO_FILE   001      +00000000    Success.
   ___________________________________________________
   LOAD_TO_FILE   002      +00000000    Success.
   ___________________________________________________
   LOAD_TO_FILE   003      +00000000    Success.
   ___________________________________________________
   PARTITION      001      +00000000    Success.
   ___________________________________________________
   PRE_PARTITION  000      +00000000    Success.
   ___________________________________________________

   Summary of Partitioning Agents:
   Rows Read                   = 100000
   Rows Rejected               = 0
   Rows Partitioned            = 100000

The output indicates that there was a load-to-file agent running on each output database partition, and these agents ran successfully. There was a pre-partitioning agent on the coordinator partition, and a partitioning agent running on database partition 1. The statistical summary indicates that 100,000 rows were successfully read by the pre-partitioning agent and 100,000 rows were successfully distributed by the partitioning agent. Since no rows were loaded into the table, no summary of the number of rows loaded appears.

Example 3

To load the files that were generated during the PARTITION_ONLY load operation above, issue the following command:

   load from load.del of del replace into table1 partitioned db config mode 
       load_only part_file_location /db/data

The output from the load command will be as follows:

   Agent Type     Node     SQL Code     Result
   ___________________________________________________
   LOAD           000      +00000000    Success.
   ___________________________________________________
   LOAD           001      +00000000    Success.
   ___________________________________________________
   LOAD           002      +00000000    Success.
   ___________________________________________________
   LOAD           003      +00000000    Success.
   ___________________________________________________
   RESULTS:       4 of 4 LOADs completed successfully.
   ___________________________________________________

   Summary of LOAD Agents:
   Number of rows read         = 100000
   Number of rows skipped      = 0
   Number of rows loaded       = 100000
   Number of rows rejected     = 0
   Number of rows deleted      = 0
   Number of rows committed    = 100000

The output indicates that the load agents on each output database partition ran successfully and that the sum of the number of rows loaded by all load agents is 100,000. No summary of rows distributed is indicated since distribution was not performed.

Example 4 - Failed Load Operation

If the following LOAD command is issued:

   load from load.del of del replace into table1

and one of the loading database partitions runs out of space in the table space during the load operation, the following output might be returned:

   SQL0289N  Unable to allocate new pages in table space "DMS4KT".  
   SQLSTATE=57011

   Agent Type     Node     SQL Code     Result
   ________________________________________________________________
   LOAD           000      +00000000    Success.
   ________________________________________________________________
   LOAD           001      -00000289    Error. May require RESTART.
   ________________________________________________________________
   LOAD           002      +00000000    Success.
   ________________________________________________________________
   LOAD           003      +00000000    Success.
   ________________________________________________________________
   PARTITION      001      +00000000    Success.
   ________________________________________________________________
   PRE_PARTITION  000      +00000000    Success.
   ________________________________________________________________
   RESULTS:       3 of 4 LOADs completed successfully.
   ________________________________________________________________

   Summary of Partitioning Agents:
   Rows Read                   = 0
   Rows Rejected               = 0
   Rows Partitioned            = 0

   Summary of LOAD Agents:
   Number of rows read         = 0
   Number of rows skipped      = 0
   Number of rows loaded       = 0
   Number of rows rejected     = 0
   Number of rows deleted      = 0
   Number of rows committed    = 0

The output indicates that the load operation returned error SQL0289. The database partition summary indicates that database partition 1 ran out of space. If additional space is added to the containers of the table space on database partition 1, the load operation can be restarted as follows:

   load from load.del of del restart into table1