Administration Guide

Example Data File for db2split

This section provides an example of a data file and the table into which data is to be loaded. The following sections provide an example of the configuration file that you would use, and an explanation of how the file is set up.

Assume that you have a delimited data file called MYDATA, which is as follows:

  25,125,dog,123.45,1984-12-15,12.00.23,1984-12-14-11.11.59.000000
  ,,cat,12.34,,,1982-12-15-11.11.25.001200
  213,424,bird,56.345,,,

Also assume that you want your table to be distributed on 3 nodes (4,7,8), and that you create it with the following definition:

db2 CREATE NODEGROUP MyNodeGroup ON NODES (4,7,8)
 
db2 CREATE TABLESPACE MyTableSpace IN NODEGROUP MyNodeGroup
 
db2 CREATE TABLE MyTable (col1 INTEGER,
                          col2 SMALLINT,
                          col3 VARCHAR(5),
                          col4 DECIMAL,
                          col5 DATE,
                          col6 TIME,
                          col7 TIMESTAMP)
        IN MyTableSpace
        PARTITIONING KEY (col7,col1,col3,col4,
                          col2,col5)

AIX Configuration File

  Infile=MYDATA
  ;
  Nodes=(4,7,8)
  OutputNodes=(4,7,8)
  ; MapFili=MyInputMap
  MapFilo=MyOutputMap
  OutFile=MyOutput
  RunType= partition
  DistFile=DISTRIBUTION
  LogFile=MyLog
  CDelimiter=,
  SDelimiter="
  Partition=col7,7,,,N,timestamp
  Partition=col1,1,,,N,integer
  Partition=col3,3,,5,N,character
  Partition=col4,4,,,N,decimal(6,3)
  Partition=col2,2,,,N,smallint
  Partition=col5,5,,,N,date
  Trace=20
  FileType=DEL

Notes:

1. The Nodes field should be consistent with the table nodegroup.

   If the MyNodeGroup definition was:

     db2 CREATE NODEGROUP MyNodeGroup ON NODES (0,1,2)
   

   Then the Nodes parameter could be specified as Nodes=(0-2) in the configuration file. db2split generates a partitioning map with repetitive sequences of the elements specified in this parameter.

   You would not specify this field if you were specifying MapFili.

2. In this situation, the OutputNodes field is also optional. By default, the output nodes have the same members as the Nodes field.

   If you specify OutputNodes=(4,8), the program writes the results of database partitions 4 and 8 to the output data file as defined in OutFile. Consequently, you will have two output files: MyOutput.004 and MyOutput.008.

   If you want only the output data for database partition 7, then specify OutputNodes=(7) (the parentheses are required). In this situation, OutputNodes has only one member. The result will be written to stdout. If you want to direct the output to the file MyOutput.007 rather than stdout, set OutputType to w:

     OutFile=MyOutput
     OutputType=w
   

3. For partitioning keys, the column name field of character data can be anything you want. Normally, this field is used to identify the corresponding column name from the table.

   The length field of character data type must be consistent with that of the database table. In this situation, the table is created with a column length of 5. As a result, the length field of the partitioning key col3 is set to 5 as well.

   The first decimal record (123.45) suggests that Partition=col4,4,,,N,decimal(5,2) should be specified. The third record, however, is 56.345, which means that the precision has to be increased to 3 and the total length to 6, as follows:

     Partition=col4,4,,,N,decimal(6,3)
   

[ DB2 List of Books | Search the DB2 Books ]