16.11. User Custom Object Migration Utility

User Custom Objects #

In order to help the customers who create their own objects in our main universes it is decided to create a class called ‘User Custom Objects’, this class can be found under the ‘Support’ class.

These objects can be used if customisation of the universe is necessary. These objects will work ’through’ the versions delivered by DXC. Clients are advised to use the next available Custom Object. When receiving a new universe it is up to customers to reapply the SQL to the Custom Objects again. It is suggested that when these objects are used the clients rename the objects to e.g. ‘Entry Amount Settlement -NN’, where NN is the object number. The reason for including the NN in the object name is because when a customer receives a new version of the Universe they will have to find each previous custom object and copy the SQL from each to the corresponding object in the new Universe. If a customer decides to drag a custom object to another folder they can still find it later using the Edit/Find feature in Designer and looking for part of the object name (e.g. the NN). Therefore it is important to name the Objects in a way that makes them easy to find later. Alternatively leave them in the User Custom Objects Folder.

Instructions for use of the User Custom Object Migration Utility #

This utility automates the process of updating a new release of a universe to include any objects that have been created using “User Custom Objects” Class.

The methodology will be illustrated using this example..

UDT23.3.PNG

Shown above is the current universe (source). The three objects that have been created using user custom objects are shown selected.

UDT23.3_2.PNG

Shown above is the new version of the universe (Destination). The User Custom Objects directory is shown selected. This is where all objects will be recreated in the new universe. (See Limitations section)

Database Replication #

For replication of the SICS operational database to a specific reporting database, it is advised to use the built-in functionality within the database service tools. This has proven to be easier than to use replication utility. The scripts for creating tables, views, indexes and procedures/functions are provided.

This will enable the creation of the foundation of the reporting database. The method of actual data transfer from the online database to the reporting database is then something that can be decided upon at each site. Using the provided indexes specific for reporting shows in our benchmarking tests a 30-50% increase in performance on a set of reports.

Creation of Tables, Views, Stored Procedures and Stored Functions of Databases

Oracle #

This covers the operations needed to be carried out in order to create a new reporting database on Oracle.

It covers the creation of:

Tables
Views
Stored Procedures
Stored Functions
Indexing

Pre-requisites #

A valid Oracle account in which to create the database.

Skills required #

Basic SQL (including the running of scripts)

Software required #

Oracle Client Middleware successfully installed and connected to the database.
SQL Plus.

ORACLE Installation #

Using SQL Plus (or similar application), login to the database account.
Create the physical tables by running the script Create_Oracle_Reporting_Database.txt.

To run the script, type the following at the SQL> prompt:

@ <FILE_LOCATION> Create_Oracle_Reporting_Database.txt

where FILE_Location is the directory location of the txt file. For example:

SQL>@ c:\Create_Oracle_Reporting_Database.txt

* To create SICS Reporting tables #

Creating SICS Reporting Tables using SICS SYSTEM ADMIN

The reporting tables/columns/indexes can be created and dropped by opening Manage Tables in SICS System Administration Utility.

Select the BO Reporting tab followed by the Tables tab.

Please note that the methods cannot be mixed. If tables using one method are created, it should also execute the procedures using that method.

To create the reporting tables/columns/indexes, select the create operation followed by the preferred method and click Start.

To drop the reporting tables/columns/indexes, select the drop operation followed by the preferred method and click Start.

To output the operation as a SQL Script instead of executing it directly from SICS, click Generate Script.

Table23.2.PNG

Creating SICS Reporting Tables Headlessly:

SICS BO Reporting tables can be created from command line using the following:-

a. Set the java classpath in the command prompt by running the following command.

SET CLASSPATH=conf;lib\sics.launcher.jar;lib\sics.classloader.jar;lib\sics.handwritten.jar;lib\cnu.dataloader.jar;lib\sics.dev.launcher.jar;

b. Run the below commands as per the SICS Installation:

For P&C:

Method A:

java -Xss2000k -Xmx512m -Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain pc-createBOReportingTables - reportingTablesMethodA -duUserName -dpPassword -dnDataSourceName

Method B:

java -Xss2000k -Xmx512m -Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain pc -createBOReportingTables -reportingTablesMethodB -duUserName -dpPassword -dnDataSourceName

For LIFE:

Method A:

java-Xss2000k-Xmx512m-Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain lf-createBOReportingTables-reportingTablesMethodA -duUserName -dpPassword -dnDataSourceName

Method B:

* To create SICS Reporting Views #

Creating SICS Reporting View using SICS SYSTEM ADMIN:

Database views on reporting tables for can be created from the SICS P&C/Life System Administration.

Select Database Setup -> Manage Tables after logging into SICS P&C/Life System Administration.

Under the BO Reporting tab, select the View tab.

To Create or Drop BO Reporting Views, Select Create or Drop options and press the Execute Script button.

To output the operation as a SQL Script instead of executing it directly from SICS, click Generate Script.

Creating_Views_23.2.png

Create Reporting Views Headlessly:

SICS BO Reporting Views can be created fom command line using the following steps:-

a. Set the java classpath in the command prompt by running the following command.

SET CLASSPATH=conf;lib\sics.launcher.jar;lib\sics.classloader.jar;lib\sics.handwritten.jar;lib\cnu.dataloader.jar;lib\sics.dev.launcher.jar;

b. In order to create BO Reporting Views, below command lines to be used:

For P&C:

java -Xss2000k -Xmx512m -Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain pc -createBOReportingViews -duUserName -dpPassword -dnDataSourceName -deSchema

For Life:

java -Xss2000k -Xmx512m -Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain lf -createBOReportingViews -duUserName -dpPassword -dnDataSourceName -deSchema

* To create stored procedures #

Create / Drop Procedures through SICS SYSTEM ADMIN:

The Stored Procedures for BO Reporting can be created and dropped by opening Manage Tables in SICS System Administration Utility.

Select BO Reporting tab followed by the Stored Procedure tab.

To create a Stored Procedure, select the create operation followed by the procedure and click Start.

To drop a Stored Procedure, select the drop operation followed by the procedure and click Start.

To output the operation as a SQL Script instead of executing it directly from SICS, click Generate Script.

Stored_proc_23.2.PNG

Create SICS Stored Procedures Headlessly:

SICS reporting Stored Procedures can be created headlessley using command prompt using below steps:-

a. Set the java classpath in the command prompt by running the following command:

SET CLASSPATH=conf;lib\sics.launcher.jar;lib\sics.classloader.jar;lib\sics.handwritten.jar;lib\cnu.dataloader.jar;lib\sics.dev.launcher.jar;

b. In order to create BO Stored Procedures, below command lines to be used:

For P&C:

Single script:

java-Xss2000k-Xmx512m-Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain pc-createBOReportingStoredProcedures procedure -duUserName -dpPassword -dnDataSourceName

Multiple scripts:

All Scripts Together:

java-Xss2000k-Xmx512m-Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain pc-createBOReportingStoredProcedures -allBOReportingStoredProcedures -duUserName -dpPassword -dnDataSourceName

For Life:

Single Script:

java-Xss2000k-Xmx512m-Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain lf-createBOReportingStoredProcedures procedure -duUserName -dpPassword -dnDataSourceName

Multiple Scripts:

All Script Together:

java-Xss2000k-Xmx512m-Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain lf-createBOReportingStoredProcedures -allBOReportingStoredProcedures -duUserName -dpPassword -dnDataSourceName

* To create stored functions #

Creating SICS Stored Functions through SICS SYSTEM ADMIN:

The Stored Functions for BO Reporting can be created and dropped by opening Manage Tables in SICS System Administration Utility.

Select BO Reporting tab followed by the Stored Functions tab.

To create a Stored Functions, select the create operation followed by the functions and click Start.

To drop a Stored Functions, select the drop operation followed by the functions and click Start.

To output the operation as a SQL Script instead of executing it directly from SICS, click Generate Script.

Cr_Stored_Functions23.2.png

Create SICS Stored Functions Headlessly:

SICS Reporting Stored Functions can be created headlessley using command prompt using below steps:-

a. Set the java classpath in the command prompt by running the following command:

SET CLASSPATH=conf;lib\sics.launcher.jar;lib\sics.classloader.jar;lib\sics.handwritten.jar;lib\cnu.dataloader.jar;lib\sics.dev.launcher.jar;

b. In order to create BO Stored Functions, below command lines to be used:

For P&C:

Single script:

java-Xss2000k-Xmx512m-Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain pc-createBOReportingStoredFunctions function -duUserName -dpPassword -dnDataSourceName

Multiple scripts:

All Scripts Together:

java-Xss2000k-Xmx512m-Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain pc-createBOReportingStoredFunctions -allBOReportingStoredFunctions -duUserName -dpPassword -dnDataSourceName

For Life:

Single Script:

java-Xss2000k-Xmx512m-Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain lf-createBOReportingStoredFunctions function -duUserName -dpPassword -dnDataSourceName

Multiple Scripts:

All Script Together:

java-Xss2000k-Xmx512m-Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain lf-createBOReportingStoredFunctions-allBOReportingStoredFunctions -duUserName -dpPassword -dnDataSourceName