Implementing IBM DB2 for zSeries Partitioning

This procedure explains how to implement zSeries partitioning.

Before you perform it, prerequisite steps such as analyzing space requirements and creating the database must already be performed.

Verify connectivity between the client and the zSeries server; at the prompt, type:

db2 connect to databasename user username using password
Use the sysload command to build the product line that should be used with the partitioned database. At the prompt, type:

sysload productline productline.dump
Define an IBM system database space for the product line using the dbdef utility.
1. Start dbdef.
2. Press F4 to select.
3. Choose the appropriate product line and press F12.
4. Press F6.
5. Choose the Database Space option and press F12.
6. Enter the appropriate information on the Database Definition form as shown in the example below.
7. Press Escape and then press F6.
8. Choose the Data Areas option and press F12.
9. Highlight the Database Space field on the Database Space Definition form and press F4.
10. Select the IBM system database space that you previously created and press F12.
11. Exit the dbdef utility.
Create the table and partitioned table space assignments using dbdef.

Note: Repeat steps a-f for each partitioned table space. Doing so makes the partitioned table spaces available to the product line when table spaces are assigned to specific tables in step 5.
1. Start dbdef.
2. Press F4 to select.
3. Choose the appropriate product line and press F12.
4. Press F6.
5. Choose the Data Areas option and press F12.
6. Press F6.
7. Choose the File Sizes and Database Spaces option and press F12.
8. Highlight the file name that represents the table you want partitioned.
9. Highlight the Database Space column and press F4 to select a partitioned table space to assign to the table.
10. Highlight the partitioned table space and press F12.
11. Press F12 when you finish all table space assignments.
12. Exit dbdef.
Build the new dictionary for the product line using blddbdict. At the prompt, type:

blddbdict productline
Create and edit the Infor Lawson System FoundationIBM DB2 database driver configuration file for the new product line using the appropriate parameter values.

Modify the build_law_390.sql script to include the database name, tablespace name, and index stogroup name. Use the following as an example:

create table LAWSON.LAW_DBA_TABLE
(
    NAME          varchar(128)   not null,
    DBNAME        varchar(128),
    OPTIONS       varchar(255)
) in DATABASE.TABLESPACE;

create unique index LAWSON.LAW_DBA_TABSET1
  on LAWSON.LAW_DBA_TABLE (NAME) 
  using stogroup STOGROUP;

create table LAWSON.LAW_DBA_TS
(
    NAME          varchar(128)   not null, 
    MID_OPTIONS   varchar(255),
    NUMPARTS      integer        not null,
    POST_OPTIONS  varchar(255)
) in DATABASE.TABLESPACE;

create unique index LAWSON.LAW_DBA_TSSET1
  on LAWSON.LAW_DBA_TS (NAME) 
  using stogroup STOGROUP;

create table LAWSON.LAW_DBA_TSPART
(
    TSNAME        varchar(128)   not null, 
    PART          integer        not null,
    OPTIONS       varchar(255)   not null
) in DATABASE.TABLESPACE;

create unique index LAWSON.LAW_DBA_TSPSET1
  on LAWSON.LAW_DBA_TSPART (TSNAME, PART) 
  using stogroup STOGROUP;

create unique index LAWSON.LAW_DBA_INDSET1
    on LAWSON.LAW_DBA_INDEX (NAME) 
    using stogroup STOGROUP;

create unique where not null index LAWSON.LAW_DBA_INDSET2
    on LAWSON.LAW_DBA_INDEX (TBNAME, CLUST)
    using stogroup STOGROUP;

create table LAWSON.LAW_DBA_IXPART
(   IXNAME        varchar(128)   not null, 
    PART          integer        not null,
    VALUES        varchar(255)   not null,
    OPTIONS       varchar(255)
) in DATABASE.TABLESPACE;

create unique index LAWSON.LAW_DBA_IXPSET1 
  on LAWSON.LAW_DBA_IXPART (IXNAME, PART) 
  using stogroup STOGROUP;

Execute the build_law_390.sql script through the DB2 client. At the prompt, type:


db2 connect to databasename user username
 using password db2 -t -f \your_location
\build_law_390.sql

One series of insert statements must be executed for each partitioned table.

Create a script to populate the law_dba_[partition] table similar to the following example:
```
insert into law_dba_partition values (tablename,
 partition_number, key value, options)
```
Note: The script should reflect the range of values present in the key column of the table to be partitioned. The data matching a given key value is stored in the corresponding partition defined in the law_dba_[partition] table.

Execute the script you created. Use the following as example:

db2 connect to databasename user username using password
db2 -t -f \linte\univ\ibm\scriptfilename

Execute the dbreorg utility on your product line to finish creating the database structure on the partitioned database.
1. Create a list of changes that will occur when you reorganize the product line. At the command prompt, type:
  
  dbreorg -lc productline
  
  Caution:
  Be sure to view the changes first by using the -lc options (-l and -c) to confirm the changes.
2. View the list of potential changes. At the command prompt, type:
  
  lashow -f $GENDIR/productline/reorg.hist
  
  EDTF $GENDIR/productline/reorg.hist
3. If the changes listed are correct, reorganize the product line. At the command prompt, type:
  
  dbreorg productline
Load the finished product line with data using impexp utility. At the prompt, type:

impexp -f productline productline.data
Execute the verifyibm utility to check the finished installation. At the prompt, type:

verifyibmproductline