Create partition policies using the ABL API

Generate a policy program to serve as a template

Before you generate a policy program that you can use as a template, you must create at least one partition policy with one partition policy detail for a table. The easiest way to do this is to use the Database Administration Console.

By default, the policy program contains a partition policy structure based on the policy you used to generate the program. The program code is ordered the same way as the Create Table Partition Policy wizard in the Database Administration Console:

1. The partition policy is defined.
2. The partition fields and indexes are specified.
3. The individual partition policy details and their property values are defined.

Follow these steps to generate a policy program that you can use as a template based on an existing partition policy:

1. From the Database Administration Console, click Database Administration > Database Connections.

   A new Database Administration tab appears, with the left pane showing the list of available database connections.

2. Click the database connection of the database that is enabled for table partitioning.

   The right pane displays five sections of information about the database, including the Storage Management section.

3. From the Storage Management section, click Partition policies.
4. Click the partition policy from which you want to generate a policy program.
5. Click Generate policy program.

   The Save As dialog box opens. By default, the file is named create_policy.p.

6. Rename the file if desired, browse to a directory of your choice, and then click Save to save the file.

Edit the policy program

After you generate a policy program to use as a template, you modify the required values to create the new partition policy and its associated policy details. If you want to add more policies and policy details to your program, you can simply copy and paste relevant code and modify the values.

DataAdminService

The policy program begins by instantiating a DataAdminService for a database.

If the partition policy that you used to generate the program is based on the same database that you will use to create new partition policies and partition policy details, then the database is already properly specified.

If you are going to create new partition policies and partition policy details for a different database, then you have to find the DataAdminService line in the program and change the name of database.

Here is an example of a DataAdminService line for the newsportsco database:

...
service = new DataAdminService("newsportsco").
...

Property values

Recall that when you create a partition policy using the Database Administration Console, you specify values for the policy properties as shown in the graphic. In the policy program, you just need to specify values for the following properties for each partition policy:

Partition fields and indexes

Recall that when you create a partition policy using the Database Administration Console, you add partition fields and partition-aligned indexes to the policy as shown in the graphic. In the policy program, all you need to do is specify values for two properties: Fields and Indexes.

In this example, because the HasRange property is set to yes, the code implements range partitioning with OrderDate as the range field and OrderDateLocalIdx as the local index.

...
    partitionPolicy:HasRange = yes
...
partitionPolicy:Fields:Add(partitionPolicy:Table:Fields:Find("OrderDate")). 
partitionPolicy:Indexes:Add(partitionPolicy:Table:Indexes:Find("OrderDateLocalIdx")).
...

In this example, because the HasRange property is set to no, the code implements list partitioning with Country as the list field and CountryLocalIdx as the local index.

...
    partitionPolicy:HasRange = no
...
partitionPolicy:Fields:Add(partitionPolicy:Table:Fields:Find("Country")). 
partitionPolicy:Indexes:Add(partitionPolicy:Table:Indexes:Find("CountryLocalIdx")).
...

Partition fields and indexes for subpartitioning

To configure subpartitioning, you must specify a separate Fields property line for each partition field. You can also specify multiple Indexes property lines, if required.

In this example, because the HasRange property is set to yes, the code implements list-range subpartitioning with Carrier as the list field, OrderDate as the range field, and CarrierDateLocalIdx as the local index. Note that the lines of code for the fields are arranged in the order that matches the subpartitioning.

...
    partitionPolicy:HasRange = yes
...
partitionPolicy:Fields:Add(partitionPolicy:Table:Fields:Find("Carrier")). 
partitionPolicy:Fields:Add(partitionPolicy:Table:Fields:Find("OrderDate")).
partitionPolicy:Indexes:Add(partitionPolicy:Table:Indexes:Find("CarrierDateLocalIdx")).
...

In this example, because the HasRange property is set to no, the code implements list-list subpartitioning where Carrier and Country are both list fields.

...
    partitionPolicy:HasRange = no
...
partitionPolicy:Fields:Add(partitionPolicy:Table:Fields:Find("Carrier")). 
partitionPolicy:Fields:Add(partitionPolicy:Table:Fields:Find("Country")).
partitionPolicy:Indexes:Add(partitionPolicy:Table:Indexes:Find("CarrierCountryLocalIdx")).
...

Run the partition policy program

After you specify property values for the partition policies and policy details that you want to create in the partition policy program, you run it against the database in which the partitions will be created.

When you complete running all the policy programs, you can access the Database Administration Console to verify that the partition policies and policy details are created.

When you run a policy program, set the Directory Size (-D) startup parameter to at least 1000. The default value is 100 but it might not be adequate. If you use the default value, you might encounter this message:

"-D limit has been exceeded; automatically increasing to 150. (5410) "

The -D parameter is used to change the number of compiled procedure directory entries. The maximum -D value is 2,147,483,647.

Here is an example of how to run a partition policy program, create_policy_order.p, to create partitions for the Order table in the sportsco database.

prowin32 sportsco -p create_policy_order.p -D 1000