How do I customize auxiliary database tables?

Custom auxiliary database table attributes

Some NSP Analytics reports require data that is not available by default. Data such as location names, geographic co-ordinates, and maintenance windows must be imported to an auxiliary database in order to be included in reports. The NFM-P has a mechanism for the creation and management of auxiliary database tables and content.

An XML file that you create defines the table columns and data types to add to an auxiliary database. After you import a table definition to the NFM-P, an operator can add data records to the table using a CSV file whose record format matches the format defined in the XML file. An operator can also delete one or more tables, or the content of a table.

Note: A data-import operation appends the new records to the table, and does not affect the existing table contents or structure.

Custom auxiliary database table attributes are retained through system upgrades, and are included in auxiliary database backup and restore operations.

Table definition file format

Figure 20-1, Custom table definition file format, shows a table definition XML file that contains the column definitions for two custom tables.

Figure 20-1: Custom table definition file format

<customTablesConfig organization="OurCompany" name="CustomTableDefs"

   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

   xsi:noNamespaceSchemaLocation="./schema/customtables.xsd">

   <customTables>

      <customTable

         name="table1"

         description="This is table 1"

         orderBy="column1">

         <columns>

            <column name="column1" type="STRING"  length="8" encoding="RLE" />

            <column name="column2" type="INTEGER" />

            <column name="column3" type="FLOAT"   />

            <column name="column4" type="BOOLEAN" />

            <column name="column5" type="NUMERIC" precision="12" scale="4" />

         </columns>

      </customTable>

      <customTable

         name="table2"

         description="This is table 2"

         segmentedBy="column1,column2"

         <columns>

            <column name="column1" type="STRING" length="64" />

            <column name="column2" type="STRING" length="64" />

         </columns>

      </customTable>

   </customTables>

</customTablesConfig>

Table 20-1, Custom attribute definition elements lists and describes the configurable elements in a custom table definition.

Table 20-1: Custom attribute definition elements

Element	Description
description	Text that describes the table; optional
orderBy	Comma-separated list of column names that define how the table data is to be ordered; optional
segmentedBy	Comma-separated list of column names that define how the table is to be segmented; optional
column name	Table column name
type	One of the following: STRING—1 to 4096 characters INTEGER FLOAT BOOLEAN NUMERIC
precision	Maximum number of significant digits, represented by a positive integer less than or equal to 1024; required for NUMERIC data type
scale	Maximum number of digits to the right of the decimal point in a NUMERIC data type, represented by a positive integer less than or equal to the precision value; if omitted, defaults to 0 If the number of decimal digits in a data value exceeds the scale value, the data value is rounded to the number of digits specified by the scale value.
encoding	Text encoding type for STRING data type; optional, default is AUTO
length	Field length; required for STRING data type

Functional description

The management tool for custom tables is the customData.bash CLI script on a main server station. You can use the tool to do the following:

create and delete tables
import, export, and delete data
list the currently defined custom tables
export a table schema

Note: In order for the customData.bash script to function, the main server configuration must include an auxiliary database.

Note: If a string data value to be imported includes a comma, you must precede the comma with a backslash to prevent the comma from being interpreted as a CSV file delimiter.

The customData.bash script has the following operating characteristics:

The script automatically assigns a prefix and suffix to the name of a custom table. References to the table in script operations after table creation must include the samdb prefix and _ct suffix; for example, customTable1 must be specified as samdb.customTable1_ct.
Script operations that modify data, such as createTable, deleteTable, and importData, require the auxiliary database user password.
During a createTable operation, a formatting error in an table definition causes all table creation during the operation to fail, and the script saves the table definition file as tmp/customtables.xml.template.
The script logs each operation in the /opt/nsp/nfmp/server/nms/log/customdata.log; the maximum log size is five Mbytes.