Create Physical File (CRTPF) command and SQL CREATE TABLE statement

This topic provides information about creating a distributed physical file by using the Create Physical File (CRTPF) command or the SQL CREATE TABLE statement.

When you want to create a partitioned file, you need to specify the following parameters on the CRTPF command:

A node group name for the NODGRP parameter
The field or fields that are to be used as the partitioning key (use the PTNKEY parameter)

The partitioning key determines where (on what node) each record of data physically resides. You specify the partitioning key when the CRTPF command is run or when the SQL CREATE TABLE statement is run. The values of the fields that make up the partitioning key for each record are processed by the HASH algorithm to determine where the record is located.

If you have a single-field partitioning key, all records with the same value in that field reside on the same system.

If you want to create a distributed physical file, your user profile must exist on every node within the node group, and your user profile must have the authority needed to create a distributed file on every node. If you need to create a distributed file in a specific library, that library must exist on every node in the node group, and your user profile must have the necessary authority to create files in those libraries. If any of these factors are not true, the file is not created.

The way that the systems are configured can influence the user profile that is used on the remote system. To ensure that your user profile is used on the remote system, that system should be configured as a secure location. To determine if a system is configured as a secure location, use the Work with Configuration Lists (WRKCFGL) command.

The following example shows how to create a physical file named PAYROLL that is partitioned (specified by using the NODGRP parameter) and has a single partitioning key on the employee number (EMPNUM) field:

CRTPF FILE(PRODLIB/PAYROLL) SCRFILE(PRODLIB/DDS) SRCMBR(PAYROLL)
      NODGRP(PRODLIB/PRODGROUP) PTNKEY(EMPNUM)

When the CRTPF command is run, the system creates a distributed physical file to hold the local data associated with the distributed file. The CRTPF command also creates physical files on all of the remote systems specified in the node group.

The ownership of the physical file and the public authority on all the systems is consistent. This consistency also includes any authority specified on the AUT parameter of the CRTPF command.

The SQL CREATE TABLE statement also can be used to specify the node group and the partitioning key. In the following example, an SQL table called PAYROLL is created. The example uses the IN nodgroup-name clause and the PARTITIONING KEY clause.

  CREATE TABLE PRODLIB/PAYROLL
               (EMPNUM INT, EMPLNAME CHAR(12), EMPFNAME CHAR (12))
               IN PRODLIB/PRODGROUP
               PARTITIONING KEY (EMPNUM)

When the PARTITIONING KEY clause is not specified, the first column of the primary key, if one is defined, is used as the first partitioning key. If no primary key is defined, the first column defined for the table that does not have a data type of date, time, timestamp, or floating-point numeric is used as the partitioning key.

To see if a file is partitioned, use the Display File Description (DSPFD) command. If the file is partitioned, the DSPFD command shows the name of the node group, shows the details of the node group stored in the file object (including the entire partition map), and lists the fields of the partitioning key.

For a list of restrictions you need to know when using distributed files with DB2^® Multisystem, see Restrictions when creating or working with distributed files with DB2 Multisystem.