Database import tables and columns

Staging tables are database tables that replicate the loadable columns of specific operational tables in the PolicyCenter database. The operational zone table pc_zone has a corresponding staging table pcst_zone. You prepare zone data for bulk import into the PolicyCenter operational table pc_zone by first loading the data into the pcst_zone staging table.

A logical unit of work (LUW) groups related records across multiple staging tables into a single unit. All rows in an LUW must load from staging tables into operational tables. An error in any row in an LUW invalidates the whole LUW. For example, a Zone record for a postal code must load into the pcst_zone staging table at the same time as a Zone record for its city. The zone import tool creates an identifier (LUWID) for each LUW that it defines.

After staging table data loads successfully into operational tables, PolicyCenter deletes all the rows from the staging tables.

The load error table holds data from failed data integrity checks. Do not directly read or write load error table data. Examine these tables using the Server Tools > Info Pages > Load Errors interface. Most errors relate to a particular staging table row, so the Load Errors page shows the following information.

• Table
• Row number
• Logical unit of work ID – The zone import tool creates the LUWIDs for zone data.
• Error message
• Data integrity check, also called the query, that failed

To resolve an error, you must correct the data in the CSV file and reload the staging table data.

See also

• The Load Errors screen

The load history table stores results for import processes, including rows for each integrity check, each step of the integrity check, and row counts for the expected results. Use the information in these tables to verify that the import tools loaded the correct amount of data. You can view this information in PolicyCenter at Server Tools > Info Pages > Load History.

See also

• The Load History Information screen

Running a staging table import copies all loadable entities from the staging tables to the operational tables. The staging table import run generates a load command id value. PolicyCenter sets the load command ID property (LoadCommandID) to this value on every entity that the load command imports. The command prompt tools that perform a database table import return the LoadCommandID. You can see the load command ID and track load import history using the user interface at Server Tools > Info Pages > Load History.

An entity’s LoadCommandID property is always null for rows that did not enter PolicyCenter through staging table import. For example, a row that a user created by using the PolicyCenter user interface has a null value for the LoadCommandID property.

The LoadCommandID property does not change if the values of other properties on the entity change. If the user, application logic, or integration APIs change the data, the LoadCommandID property stays the same as when the row was first created.

Use the value of the LoadCommandID property to determine whether an entity was loaded using database staging tables or was created in some other way. Test an entity’s LoadCommandID from your business rules or from a Java plugin. From Gosu, check entity.LoadCommandID. From Java, use the entity.getLoadCommandID method. The method returns a TableImportResult entity instance, which contains a LoadCommandID property, which is the load command ID. Call result.getLoadCommandID() to get the load command ID for that load request.

See also

• The Load History Information screen

Running a staging table import populates user ID properties on imported entities. PolicyCenter sets the value of the CreateUserID and UpdateUserID properties to the user ID of the user that authenticated the web service or command prompt tool. To facilitate the selection of imported records by a database query, use a specific PolicyCenter user to import the staging table data into operational tables. For example, run the import commands as a user called Conversion. This user must have the SOAP Administration permission to execute the web service or command prompt tool. Do not give this user additional privileges or access to the user interface or other portions of PolicyCenter.

Use the value of the CreateUserID property to determine whether an entity was loaded using database staging tables or was created in some other way.

Running a staging table import populates time stamp properties on imported entities. PolicyCenter sets the CreateTime and UpdateTime properties to the start time of the server transaction. All rows have the same time stamp for a single import run.