9 Transferring Data Between Pipeline Manager Databases

This chapter describes how to transfer data from one Pipeline Manager database to another, such as from a test database to a production database, by using the Oracle Communications Billing and Revenue Management (BRM) LoadIfwConfig utility.

About Transferring Data

You transfer data by extracting data from a source Pipeline Manager database and then loading the data into a destination Pipeline Manager database. You specify which data to extract at the command line or by using an XML file. The LoadIfwConfig utility extracts the specified data from the source database to an output XML file. The utility can then load the output XML file directly into the destination database.

About Specifying the Data to Extract

You can specify to extract:

• All data in the Pipeline Manager database. You specify to extract all data by using only a utility command. For instructions, see "Extracting All Database Objects with LoadIfwConfig".

• All Pipeline Manager data that has been modified after a specified date and time. You specify to extract data based on the modification date and time by using utility commands. For instructions, see "Extracting All Database Objects Modified after a Specific Time".

• A subset of the data, based on the objects' attributes and modification time. You specify to extract a subset of the data by using an input XML file with the LoadIfwConfig utility. See "About Creating an Input XML File to Extract Data".

About Creating an Input XML File to Extract Data

If you are extracting a subset of data, you must create an input XML file that specifies the table from which to extract the data and, optionally, the criteria that the data must meet. The criteria consists of fields and their required values. For example, you can specify to extract from a specific table only those objects that have the SAMPLE field set to 100. The utility would then extract all objects with a SAMPLE field set to 100 as well as any child objects and any dependent objects. See "About Specifying to Extract Child and Dependent Objects".

When the input XML file specifies a table only, the utility extracts objects from the entire table as well as from any child and dependent objects. When the input XML file specifies a table and required field values, the utility extracts from the table only those objects that contain the matching field values plus any child and dependent objects.

The syntax for the input XML file is shown below:

<RecordSet>
  <TableName [FieldName1 ="Value1"] [FieldName2 ="Value2"] .../>
</RecordSet>

where:

• TableName is the name of the table from which to extract objects.

• FieldNameX is the name of the table field that must match the specified value. You can list multiple field-value pairs.

• ValueX specifies the required field value. To be able to list multiple values for each field, see "About Using Regular Expressions when Specifying the Data to Extract".

For example, the following input XML file specifies to retrieve all objects from the IFW_RUM table that match all of the criteria below:

• Have their NAME field set to Duration.

• Have their RUM field set to DUR.

• Have their TYPE field set to D.

<RecordSet>
   <IFW_RUM NAME ="Duration" RUM ="DUR" TYPE ="D" />
</RecordSet>

About Specifying to Extract Child and Dependent Objects

When extracting a subset of data, the LoadIfwConfig utility automatically extracts all objects that meet your specified criteria as well as any related child objects. For example, when the utility extracts a charge object, the utility automatically extracts all child objects of the charge, such as the charge version and charge configuration objects.

If one of the child objects is also related to other objects, you can configure the utility to extract the other objects as well.

Figure 9-1 Object Relationships and Dependencies

Description of "Figure 9-1 Object Relationships and Dependencies"

For example, in Figure 9-1 above, object A meets your specified criteria. Because object A is a parent object, the utility automatically extracts object A and its two child objects, B and C. Because object C is also related to object D, extracting only the objects in object group 1 would break the relationships in object group 2.

To prevent this from happening, you can configure the utility to extract object D whenever object C is extracted. You do this by making object D a dependent of object C. In this case, whenever object C is extracted, the utility would also extract:

• Object D, because it is a dependent of object C.

• Object E, because it is a child of object D.

The utility determines the object dependencies by using the pipeline_home/tools/XmlLoader/LoadIfwConfig.xsd file, where pipeline_home is the directory where you installed Pipeline Manager. You customize the dependencies by using the pipeline_home/tools/XmlLoader/CustomConfig.xml file.

Note:

The settings in the CustomConfig.xml file override the settings in the LoadIfwConfig.xsd file.

The syntax for the CustomConfig.xml file is shown below:

<TableName AddDependingTable="AddTable" AddDependingTableMapping="Field1:Field2"/>
<TableName DependingTableNames="DependingTable"/>

where:

• TableName specifies the table that has dependent objects in another table.

• AddTable specifies the dependent table. The utility extracts data using the provided mapping whenever data from TableName is extracted.

• Field1 is the field from TableName that is related to a field in AddTable.

• Field2 is the relating field in AddTable. The utility extracts any objects from AddTable that have matching values whenever data from TableName is extracted.

• DependingTable specifies the list of dependent tables. Data from these tables must be extracted whenever data is extracted from TableName. You can list multiple table names by using the pipe symbol (|) as a delimiter.

Sample CustomConfig.xml entries are shown below:

<IFW_RATEPLAN_CNF AddDependingTable="IFW_TIMEMODEL" AddDependingTableMapping="TIMEMODEL:TIMEMODEL"/>
<IFW_RATEPLAN_CNF DependingTableNames="IFW_TIMEMODEL|IFW_PRICEMODEL"/>

• The first line specifies that the utility must extract dependent data from IFW_TIMEMODEL, relating the TIMEMODEL field of IFW_RATEPLAN_CNF to the TIMEMODEL field of IFW_TIMEMODEL.

• The second line specifies to extract dependent data from the IFW_TIMEMODEL and IFW_PRICEMODEL tables whenever data is extracted from IFW_RATEPLAN_CNF.

About Using Regular Expressions when Specifying the Data to Extract

By default, you cannot use regular expressions when specifying the data to extract. This means that the input XML file must include a separate line for each required field value, which impacts performance because multiple entries generate multiple SQL queries to the database. For example, to retrieve objects from the IFW_RATEPLAN_CNF table that have an IMPACT_CATEGORY value of FRANCE or SPAIN, the input XML file would contain these lines:

<IFW_RATEPLAN_CNF IMPACT_CATEGORY="FRANCE"/>
<IFW_RATEPLAN_CNF IMPACT_CATEGORY="SPAIN"/>

You can configure the utility to accept the following regular expressions when searching defined fields:

• The asterisk (*) symbol for wildcard searches.

• The pipe (|) symbol for the logical OR operation.

You define which fields support regular expressions by configuring the RegExFields entry in the pipeline_home/tools/XmlLoader/CustomConfig.xml file.

The syntax for the RegExFields entry is shown below:

<TableName RegExFields="FieldName"/>

where:

• TableName is the name of the table that contains the specified field.

• FieldName is the name of the field that can be searched with regular expressions. You can list multiple fields by using the pipe symbol (|) as a delimiter.

For example, the following entry specifies that you can use regular expressions when searching for values in the IMPACT_CATEGORY field of the IFW_RATEPLAN_CNF table:

<IFW_RATEPLAN_CNF RegExFields="IMPACT_CATEGORY"/>

For the above example, you could retrieve records that have their IMPACT_CATEGORY field set to FRANCE or SPAIN by using this input XML entry:

<IFW_RATEPLAN_CNF IMPACT_CATEGORY="FRANCE|SPAIN"/>

About the LoadIfwConfig Error Messages

The LoadIfwConfig utility logs information about any errors it encounters to the log file you specified in the ProcessLog section of the LoadIfwConfig.reg registry file. Table 9-1 describes the utility's error messages.

Note:

The utility will pass through any error messages thrown by the Xerces SAX parser and the Oracle database. For information about these error messages, see the appropriate vendor's documentation.

Table 9-1 LoadIfwConfig Error Messages utility

Error message	Description
ERROR: Connection is not Valid	The utility failed to connect to the database.
ERROR: DataBaseStatus is not Valid	The database credentials or database connect string is not correct.
ERROR: Couldn't get next sequence: SQLString	The utility could not generate a sequence number to insert into the database.
ERROR: during insert: SQLString	The utility encountered an error during the insert operation.
ERROR: during update: SQLString	The utility encountered an error during the update operation.
ERROR: during delete: SQLString	The utility encountered an error during the delete operation.
Exception occurred while executing SQLString	The utility encountered an error while running other SQL statements.
Exception from DB: ErrorMessage	The error message that was thrown by the Oracle database.
File Not parsed properly or Braces not matched properly	The utility's registry file (LoadIfwConfig.reg) contains incorrect entries or the entries are not framed correctly.
DependentFields structure provided in XML for depending table not proper	The AddDependingTableMapping entry in the CustomConfig.xml file is set incorrectly.
No rows for deletion because rows present in Child table	The utility could not delete the requested row because the row's associated child records still contain data.
Could not find valid Translation for: Table: TableName Referred Table: TableName Field: FieldName CODE Field Value: FieldValue	The required table dependencies are not provided in the input XML file.
FATAL ERROR at file: FileName line: LineNumber char: Position Message: Message	The input XML file contains mistakes, such as unparseable characters.

Using LoadIfwConfig to Transfer Data between Databases

To transfer data from one Pipeline Manager database to another, perform these steps:

1. Connect LoadIfwConfig to the source Pipeline Manager database. See "Connecting LoadIfwConfig to the Pipeline Manager Database".

2. (Optional) Specify the regular expressions and table dependencies that are supported on the source Pipeline Manager system. See "Customizing the Regular Expression and Dependent Table Settings".

3. Extract data from the source Pipeline Manager database. See "Extracting Data from a Pipeline Manager Database".

4. Connect LoadIfwConfig to the destination Pipeline Manager database. See "Connecting LoadIfwConfig to the Pipeline Manager Database".

5. Load data into the destination Pipeline Manager database. See "Loading Data into Pipeline Manager Databases".

Connecting LoadIfwConfig to the Pipeline Manager Database

You connect the LoadIfwConfig utility to the Pipeline Manager database by using the LoadIfwConfig.reg registry file. You can edit this registry file manually, but it is also updated by the pin_setup utility during the LoadIfwConfig installation process.

Note:

If you upgraded to Pipeline Manager 7.4, you must create the pipeline_home/tools/XmlLoader/log directory before you start the LoadIfwConfig utility.

To connect LoadIfwConfig to the Pipeline Manager database:

1. Open the pipeline_home/tools/XmlLoader/LoadIfwConfig.reg registry file in a text editor.

2. Edit the registry entries to match your system environment. In particular, pay attention to these entries:

   • In the XMLCustomizationFile entry, specify the location of the optional customization XML file. LoadIfwConfig contains a sample customization file (CustomConfig.xml) and a schema file (CustomConfig.xsd) to which the XML should conform.

   • In the LoadDataFromDB entry, specify whether to increase performance by loading the sequence-to-code translation information into memory. If this entry is missing or blank, it defaults to False.

   • (Optional) In the RowFetchSize entry, specify the number of database rows to retrieve on each trip to the database. Increasing the number of rows can reduce the required number of database fetches and increase the utility's performance. The default is 100.

   The other entries are standard logging and connection registry entries.

3. Save and close the file.

A sample LoadIfwConfig.reg file is shown below:

LoadIfwConfig
{
  LogMessageTable
  {
    MessageFilePath   = ./
    MessageFileSuffix = .msg
  }

  ProcessLog
  {
    FilePath          = ./log
    FileName          = LoadIfwConfig
    FileSuffix        = .log
    WriteMessageKey   = True
  }

  DataPool
  {
    Database
    {
      ModuleName = DbInterface
      Module
      {
        # Common
        DatabaseName = DatabaseName
        UserName     = UserName
        PassWord     = EncryptedPassword
        AccessLib    = oci231
      }
    }
  }
  XMLCustomizationFile = CustomConfig.xml
  LoadDataFromDB = True
  RowFetchSize = 200
}

Customizing the Regular Expression and Dependent Table Settings

If you want the LoadIfwConfig utility to support regular expressions for any fields, or if you want to add table dependencies for any objects, you must modify the CustomConfig.xml file.

To customize the regular expression and dependent table settings, perform these steps on the source Pipeline Manager:

1. Open the pipeline_home/tools/XmlLoader/CustomConfig.xml file in a text editor.

2. Specify the table fields that support regular expressions by using the RegExFields entry. For example, the following entry specifies that the CODE and NAME fields in the IFW_RATEPLAN table support regular expressions:

   <IFW_RATEPLAN RegExFields="CODE|NAME"/>
   

   For more information, see "About Using Regular Expressions when Specifying the Data to Extract".

3. Specify any object dependencies by using the AddDependingTable and AddDependingTableMapping entries. For example, the following entry specifies to retrieve dependent data from IFW_TIMEMODEL, relating the TIMEMODEL field of IFW_RATEPLAN_CNF to the TIMEMODEL field of IFW_TIMEMODEL:

   <IFW_RATEPLAN_CNF AddDependingTable="IFW_TIMEMODEL" AddDependingTableMapping="TIMEMODEL:TIMEMODEL"/>
   

   For more information, see "About Specifying to Extract Child and Dependent Objects".

4. Specify any table dependencies by using the DependingTableNames entry. For example, the following entry specifies to extract dependent data from the IFW_TIMEMODEL table whenever data from IFW_RATEPLAN_CNF is extracted:

   <IFW_RATEPLAN_CNF DependingTableNames="IFW_TIMEMODEL"/>
   

   For more information, see "About Specifying to Extract Child and Dependent Objects".

5. Save and close the file.

Extracting Data from a Pipeline Manager Database

You can use the LoadIfwConfig utility to extract:

• All Pipeline Manager database objects. See "Extracting All Database Objects with LoadIfwConfig".

• All Pipeline Manager database objects modified after a certain date and time. See "Extracting All Database Objects Modified after a Specific Time".

• A subset of Pipeline Manager database objects. See "Extracting a Subset of Database Objects with LoadIfwConfig".

Extracting All Database Objects with LoadIfwConfig

The utility extracts all database objects by iterating through each table in the schema in order, selecting all of the rows, and dumping them directly into an XML file.

To extract all Pipeline Manager database objects:

1. Go to the pipeline_home/tools/XmlLoader directory.

2. Enter this command:

   Interactive mode

   LoadIfwConfig
   write [OutputFile]
   retrieve_all
   

   where OutputFile specifies the name and location of the file to which to extract the pipeline data. By default, the utility writes the output to a file named default.out in the current directory.

   Non-interactive mode

   LoadIfwConfig  -rall [-o OutputFile]
    

   where OutputFile specifies the name and location of the file to which to extract the pipeline data. By default, the utility writes the output to a file named default.out in the current directory.

   Note:

   For more information about the utility's syntax, see "LoadIfwConfig".

The utility writes the extracted objects to the output file in XML format. You can now load the output XML file directly into the destination Pipeline Manager database.

Extracting All Database Objects Modified after a Specific Time

To extract all Pipeline Manager database objects that have been modified after a specified date and time:

1. Go to the pipeline_home/tools/XmlLoader directory.

2. Enter this command:

   Interactive mode

   LoadIfwConfig
   write [OutputFile]
   retrieve_all [-t Modifidate]
   

   where:

   • OutputFile specifies the name and location of the file to which to extract the pipeline data. By default, the utility writes the output to a file named default.out in the current directory.

   • Modifidate specifies the timestamp after which to retrieve pricing objects. Enter the timestamp in the ISO-8601 format: YYYY-MM-DDThh:mm:ss or YYYY-MM-DD, with the server time zone as the default. For example:

     1997-07-16T19:20:30
     

   Non-interactive mode

   LoadIfwConfig  -rall [-t Modifidate] [-o OutputFile]
    

   where:

   • Modifidate specifies the timestamp after which to retrieve pricing objects. Enter the timestamp in the ISO-8601 format: YYYY-MM-DDThh:mm:ss or YYYY-MM-DD, with the server time zone as the default. For example:

     1997-07-16T19:20:30
     

   • OutputFile specifies the name and location of the file to which to extract the pipeline data. By default, the utility writes the output to a file named default.out in the current directory.

     Note:

     For more information about the utility's syntax, see "LoadIfwConfig".

The utility writes the extracted objects to the output file in XML format. You can now load the output XML file directly into the destination Pipeline Manager database.

Extracting a Subset of Database Objects with LoadIfwConfig

To extract a subset of Pipeline Manager database objects:

1. Create an XML file that lists the objects to extract. The file specifies the table from which to extract the objects and, optionally, the criteria that the objects must meet. You can use the sample input XML file (pipeline_home/tools/XmlLoader/samples.xml) as a starting point.

   See "About Specifying the Data to Extract" for more information.

2. Go to the pipeline_home/tools/XmlLoader directory.

3. Enter the following command:

   Interactive mode

   LoadIfwConfig 
   [-nodep]
   read InputFile
   fetch [-t Modifidate]
   write [OutputFile]
   

   where:

   • -nodep suppresses the object dependency relationships you configured in the pipeline_home/tools/XmlLoader/CustomConfig.xml file. The utility extracts only the objects specified in InputFile and ignores all dependent objects.

     Note:

     To suppress object dependency relationships in interactive mode, the utility session must start with the -nodep parameter.

   • InputFile specifies the name and location of the file that lists which objects to retrieve. This is the file that you created in step 1.

   • Modifidate specifies to retrieve objects that were modified after the specified timestamp. Enter the timestamp in the ISO-8601 format: YYYY-MM-DDThh:mm:ss or YYYY-MM-DD, with the server time zone as the default. For example:

     1997-07-16T19:20:30
     

   • OutputFile specifies the output file to which the Pipeline Manager data is extracted. By default, the utility writes the output to a file named default.out in the current directory.

   Non-interactive mode

   LoadIfwConfig  -i InputFile  -r [-nodep] [-t Modifidate] [-o OutputFile]
   

   where:

   • InputFile specifies the name and location of the file that lists which objects to retrieve. This is the file that you created in step 1.

   • -nodep suppresses the object dependency relationships you configured in the pipeline_home/tools/XmlLoader/CustomConfig.xml file. The utility extracts only the objects specified in InputFile and ignores all dependent objects.

   • Modifidate specifies to retrieve objects that were modified after the specified timestamp. Enter the timestamp in the ISO-8601 format: YYYY-MM-DDThh:mm:ss or YYYY-MM-DD, with the server time zone as the default. For example,

     1997-07-16T19:20:30
     

   • OutputFile specifies the output file to which the Pipeline Manager data is extracted. By default, the utility writes the output to a file named default.out in the current directory.

The utility writes the extracted objects to the output file in XML format. You can now load the output XML file directly into the destination Pipeline Manager database.

Loading Data into Pipeline Manager Databases

You load data into the destination Pipeline Manager database by using the LoadIfwConfig utility's update option or insert option.

• Update option: The utility verifies whether the data provided in the input XML file already exists in the database. If the data already exists, the utility updates the database record with the new information. If the data does not exist, the utility inserts the data into the database. To update data see, "Updating the Pipeline Manager Database".

• Insert option: The utility inserts data into the database without verifying whether the data already exists. To insert data, see "Inserting Data into the Pipeline Manager Database".

Updating the Pipeline Manager Database

To update the data in the Pipeline Manager database:

1. Go to the pipeline_home/tools/XmlLoader directory.

2. Enter this command:

   Interactive mode

   LoadIfwConfig
   read InputFile
   update
   commit
   

   where InputFile specifies the name and location of the XML file that contains the extracted pipeline data from the source database. This is the output file you generated in "Extracting Data from a Pipeline Manager Database".

   Non-interactive mode

   LoadIfwConfig  -u  -c  -i InputFile  
   

   where InputFile specifies the name and location of the XML file that contains the extracted pipeline data from the source database. This is the output file you generated in "Extracting Data from a Pipeline Manager Database".

   Note:

   For more information about the utility's syntax, see "LoadIfwConfig".

The utility loads the data from the XML file into the Pipeline Manager database and commits the data. If there is a failure, the utility rolls back the data and displays an error message.

Inserting Data into the Pipeline Manager Database

To insert data into a Pipeline Manager database:

1. Go to the pipeline_home/tools/XmlLoader directory.

2. Enter this command:

   Interactive mode

   LoadIfwConfig
   read InputFile
   insert
   commit
   

   where InputFile specifies the name and location of the XML file that contains the extracted data from the source database. This is the output file you generated in "Extracting Data from a Pipeline Manager Database".

   Non-interactive mode

   LoadIfwConfig   -I  -c  -i InputFile
   

   where InputFile specifies the name and location of the XML file that contains the extracted data from the source database. This is the output file you generated in "Extracting Data from a Pipeline Manager Database".

   Note:

   For more information about the utility's syntax, see "LoadIfwConfig".

The utility loads the data from the XML file into the Pipeline Manager database and commits the data. If there is a failure, the utility rolls back the data and displays an error message.

Deleting Data from a Pipeline Manager Database

Note:

Make sure the utility is connected to the Pipeline Manager database. See "Connecting LoadIfwConfig to the Pipeline Manager Database".

To delete data from a Pipeline Manager database:

1. Create an XML file that specifies the data to delete. The file includes the table from which to delete the objects and, optionally, the criteria that the objects must meet. For information, see "About Specifying the Data to Extract".

2. Test the XML file by running the LoadIfwConfig utility with the -r or fetch parameter. Verify that the output file lists the correct objects to delete. See "Extracting a Subset of Database Objects with LoadIfwConfig" for more information.

3. Go to the pipeline_home/tools/XmlLoader directory.

4. Enter the following command:

   Interactive mode

   LoadIfwConfig
   read InputFile 
   delete
   

   where:

   • InputFile specifies the name and location of the file that lists the objects to delete. This is the file that you created in step 1.

   Non-interactive mode

   LoadIfwConfig  -p[f] -i InputFile 
   

   where:

   • f turns off the delete confirmation message.

   • InputFile specifies the name and location of the file that lists the objects to delete. This is the file that you created in step 1.

     Note:

     For more information about the utility's syntax, see "LoadIfwConfig".

The utility deletes the specified database objects.