|
|
|
Partitioned Files
Partitioned files are an advanced FairCom Database feature that allows rapid purging and archiving of data. FairCom RTG tables can be partitioned based on a provided index key rule to enable partitioning of data. Partitioned files are accessed, encrypted, and replicated like any other table. However, they appear physically on disk as multiple data and index files.
Quickly convert an existing table to a partitioned file
FairCom RTG 5 introduced an extended resource format that allowed nearly all FairCom Database features to be applied to RTG tables, including partitioned files. Now you can convert an existing RTG table to a partitioned file. This makes deleting large blocks of data very fast and efficient.
How to use it
There are two methods to create or convert an RTG table to a partitioned table:
The rule can use the field names as defined in the XDD when the file was sqlized (or defined using sqlinfo). The full range of FairCom Database conditional expressions are then used to calculate a final numerical value to partition the data files into unique files.
Once created, use ctpartadmin to purge, archive, or reactivate archived partitions.
The current partition rule can be obtained with ctutil -sqlifno.
Example
To convert a customer master table to a partitioned file, the customer number field must be indexed. The table must also have a schema applied with either -sqlize or -sqlinfo, using an appropriate XDD, XFD or .iss.
The following rule partitions the data off of the first character of the customer number. The ASCII value of 1 is 49, so by subtracting 48, we normalize this to the number 1. Customer numbers such as 1001 - 1999 will be located in partitioned files noted with .001, etc.
ctutil -partition CUSTMAST "(atoi(ascii(cm_custnumb)-48)" 0
The physical directory listing appears like so:
-rw-rw----. 1 rtguser rtguser 44311 Dec 10 13:06 CUSTMAST.001.DAT
-rw-rw----. 1 rtguser rtguser 163840 Dec 10 13:06 CUSTMAST.001.idx
-rw-rw----. 1 rtguser rtguser 44311 Dec 10 13:06 CUSTMAST.002.DAT
-rw-rw----. 1 rtguser rtguser 163840 Dec 10 13:06 CUSTMAST.002.idx
-rw-rw----. 1 rtguser rtguser 44311 Dec 10 13:06 CUSTMAST.003.DAT
-rw-rw----. 1 rtguser rtguser 163840 Dec 10 13:06 CUSTMAST.003.idx
-rw-rw----. 1 rtguser rtguser 44311 Dec 10 13:06 CUSTMAST.004.DAT
-rw-rw----. 1 rtguser rtguser 163840 Dec 10 13:06 CUSTMAST.004.idx
-rw-rw----. 1 rtguser rtguser 131072 Dec 10 13:06 CUSTMAST.DAT
-rw-rw----. 1 rtguser rtguser 131072 Dec 10 13:06 CUSTMAST.idx
To verify which is the current partitioning rule, ctutil -sqlinfo will include this in the XML output:
ctutil -sqlinfo CUSTMAST
<?xml version="1.0" encoding="US-ASCII"?>
<table minRecLen="4" maxRecLen="157">
<key duplicate="false" primary="true" partitionRule="atoi(ascii(CM_CUSTNUMB)-48)">
<segment offset="0" size="4" type="String"/>
</key>
<schema >
<field name="CM_CUSTNUMB" size="4" type="Alphanum" digits="4" scale="0" nullable="false" />
<field name="CM_CUSTZIPC" size="9" type="Alphanum" digits="9" scale="0" nullable="false" />
<field name="CM_CUSTSTAT" size="2" type="Alphanum" digits="2" scale="0" nullable="false" />
<field name="CM_CUSTRTNG" size="1" type="Alphanum" digits="1" scale="0" nullable="false" />
<field name="CM_CUSTNAME" size="47" type="Alphanum" digits="47" scale="0" nullable="false" />
<field name="CM_CUSTADDR" size="47" type="Alphanum" digits="47" scale="0" nullable="false" />
<field name="CM_CUSTCITY" size="47" type="Alphanum" digits="47" scale="0" nullable="false" />
</schema>
</table>
Limitations
Partitioned file support requires an RTG resource version of RTGIv2. You can determine the current version with ctutil -info:
ctutil -info custmast
If the file shows up as RTGIv1 (or other), upgrade your table to the RTGIv2 version.
