SICS Database Maintenance Launcher

SICS Database Maintenance Launcher

Introduction #

SICS SysAdmin Utility is shipped with a command-line utility for maintenance work on SICS databases. Being a command-line utility means it is a headless application, well suited for automation.

The command-line utility is implemented as a Java program which is launched from a class named com.csc.sics.launcher.DatabaseMaintenanceLauncherMain.

The SICS Database Maintenance Launcher has the following capabilites:

  • Create a new SICS database
  • Update an existing SICS database to a higher version
  • Create SICS reporting tables and views
  • Create SICS reporting stored procedures and functions

Usage #

Bootstrapping #

In order to execute the SICS Database Maintenance Launcher, a few JAR’s from the lib folder of the SICS SysAdmin Utility must be added to the Java class-path.

Linux # 

export CLASSPATH=$CLASSPATH:conf:lib/sics.launcher.jar:lib/sics.dev.launcher.jar:lib/sics.classloader.jar:lib/sics.handwritten.jar:lib/cnu.dataloader.jar

Windows # 

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

Command-line arguments #

Java arguments #

In order to not run out of memory during execution, and to get best possible performance out of the SICS Database Launcher, we suggest to run Java with the following arguments:

-Xss2000k -Xmx512m -Djava.lang.Integer.IntegerCache.high=1200

Note:
The settings above is a recommendation, your environment may require different values.

Program arguments #

The SICS Database Maintenance Launcher utility accepts various arguments which control:

  1. The SICS System (P&C or Life)
  2. The operation to execute (create, update, patch database and create base data)
  3. Database connection settings (username, password, environment and data-source name)
  4. Optional additional reporting-related operation to execute (drop and create BO tables, views, stored procedures and functions)
Argument Description Example
pc Argument for setting SICS Environment to P&C.

Note:
Do not combine with argument lf		 pc
lf Argument for setting SICS Environment to Life.

Note:
Do not combine with argument pc		 lf
-du Argument for setting SICS database username. -duSICS
-dp Argument for setting SICS database password. -dpSICS
-de Argument for setting SICS database environment.

Note:
This is normally not used when executing create, update, patch database and create base data		 -deSICS
-dn Argument for setting SICS data-source name.

This argument refers to a data source name defined in file conf/sics-database-sources.xml		 -dnSICS
-manageDbTables Argument for instructing the SICS Database Maintenance Launcher to manage database tables operations like Build Tables, Build Views, Add Defined Indexes, Add FK Indexes, Add Constraints, Comment Tables, Drop all Users Tables, Drop all User Views, Drop all Indexes and Drop Constraints.

Note:
Do not combine with argument -createdb, -updatedb, -dbPatch		 -manageDbTables
-buildTables Argument for instructing the SICS Database Maintenance Launcher to create Tables and Join Tables. This should be used with -manageDbTables -buildTables
-buildViews Argument for instructing the SICS Database Maintenance Launcher to create Common Views. This should be used with -manageDbTables -buildViews
-addDefinedIndexes Argument for instructing the SICS Database Maintenance Launcher to create defined Indexes and Join Table Indexes. This should be used with -manageDbTables. -addDefinedIndexes
-addForeignKeyIndexes Argument for instructing the SICS Database Maintenance Launcher to create Foreign Key Indexes. This should be used with -manageDbTables -addForeignKeyIndexes
-addConstraints Argument for instructing the SICS Database Maintenance Launcher to create Table Constraints and Join Table Constraints. This should be used with -manageDbTables -addConstraints
-addComments Argument for instructing the SICS Database Maintenance Launcher to comment tables. This should be used with -manageDbTables -addComments
-dropTables Argument for instructing the SICS Database Maintenance Launcher to drop tables. This should be used with -manageDbTables -dropTables
-dropViews Argument for instructing the SICS Database Maintenance Launcher to drop all views. This should be used with -manageDbTables -dropViews
-dropIndexes Argument for instructing the SICS Database Maintenance Launcher to drop Indexes. This should be used with -manageDbTables -dropIndexes
-dropConstraints Argument for instructing the SICS Database Maintenance Launcher to drop table Constraints. This should be used with -manageDbTables -dropConstraints
-createdb Argument for instructing the SICS Database Maintenance Launcher to create Tables, Views, Indexes, Constraints and optionally base data (combined with -basedata option).

Note:
Do not combine with argument -manageDbTables, -updatedb, -dbPatch		 -createdb
-createBaseData Argument for instructing the SICS Database Maintenance Launcher to create and load data.

Note:
Do not combine with argument -manageDbTables, -createdb, -updatedb, -dbPatch		 -createBaseData
-updatedb Argument for instructing the SICS Database Maintenance Launcher to update Base Reference Data, Messages, Security, EMessaging Rules (if EMessaging Module has been entered in the System Parameters). Also update Edi / ADHBaseData (specific to P&C). It will not update the Reference Data Notes.

Note:
Do not combine with argument -manageDbTables, -createdb, -dbPatch		 -updatedb
-dbPatch Argument for instructing the SICS Database Maintenance Launcher to apply a database patch. This should be used with -enable or -disable option.

Note:
Do not combine with argument -manageDbTables, -createdb, updatedb.		 -dbPatch
-enable <patch name> Argument for instructing the SICS Database Maintenance Launcher to enable a database patch. This should be used with -dbPatch. -enable re_1234
-disable <patch name> Argument for instructing the SICS Database Maintenance Launcher to disable a database patch. This should be used with -dbPatch. -disable re_1234
-createBOReportingTables Argument for instructing the SICS Database Maintenance Launcher to generates or run scripts for creating Business Objects Reporting Tables.

Note:
This argument creates reporting tables with Method A or Method B		 -createBOReportingTables
-createBOReportingTables reportingTablesMethodA New data will be maintained in a new set of reporting tables. Read-only access is sufficient for all other tables.

Note: This argument creates and drop reporting tables with Method A		 -createBOReportingTables reportingTablesMethodA
-createBOReportingTables reportingTablesMethodB Two new columns will be introduced in each of the existing tables AC_WORKSHEET and AC_STL_BALANCE. The columns will contain a date and batch number indicating if and when related data was transferred to the reporting tables.

Note: This argument creates and drop reporting tables with Method B		 -createBOReportingTables reportingTablesMethodB
-createBOReportingViews Argument for instructing the SICS Database Maintenance Launcher to generates or run scripts for creating and dropping Business Objects Reporting Views. -createBOReportingViews
-createBOReportingStoredProcedures procedure Argument for instructing the SICS Database Maintenance Launcher to generates or run scripts for creating and dropping Business Objects Stored Procedures. Stored Procedures can be created in Single, Multiple and All together in one go.

 -createBOReportingStoredProcedures procedure

-createBOReportingStoredProcedures procedure1 procedure2 procedure3 procedure4

-createBOReportingStoredProcedures allBOReportingStoredProcedures
-createBOReportingStoredFunctions function Argument for instructing the SICS Database Maintenance Launcher to generates or run scripts for creating and dropping Business Objects Stored Functions. Stored Functions can be created in Single, Multiple and All together in one go. -createBOReportingStoredFunctions function

-createBOReportingStoredFunctions function1 function2 function3 function4

-createBOReportingStoredFunctions allBOReportingStoredFunctions

Create a new SICS database #

This operation will create a new SICS database from scratch with the same version of the SICS Database Maintenance Launcher.

Example:

  • The SICS Database Maintenance Launcher is version 4.21.2.

The SICS Database Maintenance Launcher will try to create a new database with version 4.21.2.

Command for P&C for Linux(example) # 

export CLASSPATH=$CLASSPATH:conf:lib/sics.launcher.jar:lib/sics.dev.launcher.jar:lib/sics.classloader.jar:lib/sics.handwritten.jar:lib/cnu.dataloader.jar
java -Xss2000k -Xmx512m -Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain pc -createdb -duSICS -dpSICS -dnSICS

Command for P&C for Windows(example) # 

SET CLASSPATH=%CLASSPATH%;conf;lib/sics.launcher.jar;lib/sics.dev.launcher.jar;lib/sics.classloader.jar;lib/sics.handwritten.jar;lib/cnu.dataloader.jar
java -Xss2000k -Xmx512m -Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain pc -updatedb -duSICS -dpSICS -dnSICS

Command for Life for Linux (example) # 

export CLASSPATH=$CLASSPATH:conf:lib/sics.launcher.jar:lib/sics.dev.launcher.jar:lib/sics.classloader.jar:lib/sics.handwritten.jar:lib/cnu.dataloader.jar
java -Xss2000k -Xmx512m -Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain lf -createdb -duSICS -dpSICS -dnSICS

Command for Life for Windows (example) # 

SET CLASSPATH=%CLASSPATH%;conf;lib/sics.launcher.jar;lib/sics.dev.launcher.jar;lib/sics.classloader.jar;lib/sics.handwritten.jar;lib/cnu.dataloader.jar
java -Xss2000k -Xmx512m -Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain lf -createdb -duSICS -dpSICS -dnSICS

Update an existing SICS database to a higher version #

This operation will upgrade an existing SICS database to the same version of the SICS Database Maintenance Launcher.

This is done by applying the necessary SICS database patches, one by one.

Example:

  • The existing database is version 4.21.1.
  • The SICS Database Maintenance Launcher is version 4.21.2.

The SICS Database Maintenance Launcher will try to upgrade the database from version 4.21.1 to version 4.21.2.

Command for P&C for Linux (example) # 

export CLASSPATH=$CLASSPATH:conf:lib/sics.launcher.jar:lib/sics.dev.launcher.jar:lib/sics.classloader.jar:lib/sics.handwritten.jar:lib/cnu.dataloader.jar
java -Xss2000k -Xmx512m -Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain pc -updatedb -duSICS -dpSICS -dnSICS

Command for P&C for Windows (example) # 

SET CLASSPATH=%CLASSPATH%;conf;lib/sics.launcher.jar;lib/sics.dev.launcher.jar;lib/sics.classloader.jar;lib/sics.handwritten.jar;lib/cnu.dataloader.jar
java -Xss2000k -Xmx512m -Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain pc -updatedb -duSICS -dpSICS -dnSICS

Command for Life for Linux (example) # 

export CLASSPATH=$CLASSPATH:conf:lib/sics.launcher.jar:lib/sics.dev.launcher.jar:lib/sics.classloader.jar:lib/sics.handwritten.jar:lib/cnu.dataloader.jar
java -Xss2000k -Xmx512m -Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain lf -updatedb -duSICS -dpSICS -dnSICS

Command for Life for Windows (example) # 

SET CLASSPATH=%CLASSPATH%;conf;lib/sics.launcher.jar;lib/sics.dev.launcher.jar;lib/sics.classloader.jar;lib/sics.handwritten.jar;lib/cnu.dataloader.jar
java -Xss2000k -Xmx512m -Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain lf -updatedb -duSICS -dpSICS -dnSICS

Create SICS Reporting Objects Headlessly #

Creating SICS Reporting Tables Headlessly #

This operation:

  • Creates reporting tables from scratch.
  • Drops all the earlier reporting tables and creates new set of reporting tables everytime command executes.
  • In case of any updated reporting table, this operation drops all the reporting tables and creates updated reporting tables.

Following steps are needed to execute command lines for dropping/creating SICS reporting tables:

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;

In order to create reporting tables below command lines to be used:

Command 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`

Command 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:

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

Creating SICS Reporting Views Headlessly #

This operation:

  • Creates reporting views from scratch.
  • Drops all the earlier reporting views and creates new set of reporting views everytime command executes.
  • In case of any updated reporting view, this operation drops all the reporting views and creates updated reporting views.

Following steps are needed to execute command lines for dropping/creating SICS reporting views:

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;

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

Command for P&C: # 

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

Command for Life: # 

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

Creating Reporting Stored Procedures Headlessly #

This operation:

  • Creates reporting stored procedures from scratch.
  • Drops all the earlier reporting stored procedures and creates new set of reporting stored procedures everytime command executes.
  • In case of any updated reporting stored procedure, this operation drops all the reporting stored procedures and creates updated reporting stored procedures.

Following steps are needed to execute command lines for dropping/creating SICS reporting stored procedures :

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;

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

Commands 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:
java-Xss2000k-Xmx512m-Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain pc-createBOReportingStoredProcedures procedure1 procedure2 procedure3 procedure4 -duUserName -dpPassword -dnDataSourceName
  • All Scripts Together:
java-Xss2000k-Xmx512m-Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain pc-createBOReportingStoredProcedures -allBOReportingStoredProcedures -duUserName -dpPassword -dnDataSourceName

Commands 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:
java-Xss2000k-Xmx512m-Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain lf-createBOReportingStoredProcedures procedure1 procedure2 procedure3 procedure4 -duUserName -dpPassword -dnDataSourceName
  • All Script Together:
java-Xss2000k-Xmx512m-Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain lf-createBOReportingStoredProcedures -allBOReportingStoredProcedures -duUserName -dpPassword -dnDataSourceName

Creating Reporting Stored Functions Headlessly #

This operation:

  • Creates reporting stored functions from scratch.
  • Drops all the earlier reporting stored functions and creates new set of reporting stored functions everytime command executes.
  • In case of any updated reporting stored function, this operation drops all the reporting stored functions and creates updated reporting stored functions.

Following steps are needed to execute command lines for dropping/creating SICS reporting stored functions:

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;

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

Commands 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:
java-Xss2000k-Xmx512m-Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain pc-createBOReportingStoredFunctions function1 function2 function3 function4 -duUserName -dpPassword -dnDataSourceName
  • All Scripts Together:
java-Xss2000k-Xmx512m-Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain pc-createBOReportingStoredFunctions -allBOReportingStoredFunctions -duUserName -dpPassword -dnDataSourceName

Commands 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:
java-Xss2000k-Xmx512m-Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain  lf-createBOReportingStoredFunctions function1 function2 function3 function4 -duUserName -dpPassword -dnDataSourceName
  • All Script Together:
java-Xss2000k-Xmx512m-Djava.lang.Integer.IntegerCache.high=1200 com.csc.sics.launcher.DatabaseMaintenanceLauncherMain  lf-createBOReportingStoredFunctions-allBOReportingStoredFunctions -duUserName -dpPassword -dnDataSourceName