Configuration - Separating Runtime DDL and DML Operations : Portal

When the Runtime starts up, it checks the Log Database structure and creates it or updates it when necessary.

This behavior can be disabled, which is useful when the security policy requires separating User Database permissions (DML versus DDL).

The overall idea is:

Preparing two database users with the appropriate permissions
Configuring the Runtime to disable the "autoUpdate" of the Log Database
Preparing two Runtime engineParameters.xml configuration files: one for each database user
Launching the Runtime for the first time using the startengine script with the DDL user and the -updatedb option, so that the Log database structure is created
The next times, the Runtime can be launched with the DML user
When upgrading the Runtime to a new version, launch the startengine script with the DDL user and the -updatedb option, so that the Log database structure is updated

Let's see all these steps into details.

Preparing database users

In this example, we use an Oracle Database with the following Users :

Oracle user USUPPORT_DML which can only manipulate data: SELECT, INSERT, DELETE, UPDATE
Oracle user USUPPORT_DDL which can manipulate data and also CREATE and ALTER tables

Here is how we created these users:

Create role ZROLE_DML_ONLY ;
Grant create session to ZROLE_DML_ONLY;
Grant insert any table TO ZROLE_DML_ONLY;
Grant update any table TO ZROLE_DML_ONLY; 
Grant delete any table TO ZROLE_DML_ONLY;
Grant select any table  TO ZROLE_DML_ONLY;
CREATE USER USUPPORT_DML IDENTIFIED BY password01 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE "TEMP";
Grant ZROLE_DML_ONLY to USUPPORT_DML ;
CREATE USER USUPPORT_DDL IDENTIFIED BY password02 DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE "TEMP";
Grant dba to USUPPORT_DDL ;

Configuring the Runtime

Edit the "engineParameters.xml" file.

startInternalDb: false - because we don't use the internal H2 Log database
Oracle Log Database connection details

<logs>
<log userLogName="logDatabase" autoUpdate="false" userLogClass="com.indy.engine.userLog.RdbmsUserLog">
<parameter name="userLogRdbmsDriver" value="oracle.jdbc.driver.OracleDriver"/>
<parameter name="userLogRdbmsUrl" value="jdbc:oracle:thin:@qaoora11g2-01.stambia.fr69:1521:ORA112"/>
<parameter name="userLogRdbmsUser" value="USUPPORT_DML"/>
<parameter name="userLogRdbmsPassword" value="password01"/>
<parameter name="userLogRdbmsSchemaName" value="RUNTIMELOGS_01"/>
<parameter name="userLogRdbmsVarcharType" value="varchar2"/>
<parameter name="userLogRdbmsVarcharMaxSize" value="4000"/>
<parameter name="userLogRdbmsClobType" value="clob"/>
<parameter name="userLogRdbmsBlobType" value="blob"/>
<parameter name="userLogRdbmsNumericType" value="number"/>
<parameter name="userLogRdbmsDeleteSyntaxe" value="Delete from"/>
<parameter name="userLogRdbmsDeliveryFormat" value="text"/>
<parameter name="userLogRdbmsPropertyMaxVarcharSize" value="1000"/>
<parameter name="userLogRdbmsPropertyMaxClobSize" value="10000"/>
<parameter name="userLogRdbmsPropertyBinaryFormat" value="compressed"/>
</log>
</logs>

Note the autoUpdate="false" attribute which disables the DDL operations at Runtime startup.

Preparing the second engineParameters.xml file

Copy the "engineParameters.xml" file to "engineParametersDDL.xml".

Edit the new "engineParametersDDL.xml" file so that it uses the USUPPORT_DDL user.

<parameter name="userLogRdbmsUser" value="USUPPORT_DDL"/>
<parameter name="userLogRdbmsPassword" value="password02"/>

Trying to launch the Runtime without DDL for the first time

For a test purpose, let's first try to launch the Runtime with the default engineParameters.xml file.

The job executions will fail because the Log Database does not exist.

D:\apps\stambiaRuntime\stambiaRuntimeSupport>startengine.bat 
08/09/2021 11:34:22,785 - Runtime version: s17.5.4_20180302
08/09/2021 11:34:22,785 - Java version: 1.8.0_251 vendor: Oracle Corporation home: D:\apps\java\jre1.8.0_251
08/09/2021 11:34:23,775 - RMI server is started: rmi://STAMBIA-TBL:42000
08/09/2021 11:34:24,155 - Scheduler is started
08/09/2021 11:34:24,535 - SOAP Endpoint: http://STAMBIA-TBL:42200/wsi/DeliverableService?WSDL
08/09/2021 11:34:24,535 - SOAP Legacy "Non WSI-Compliant" Endpoint: http://STAMBIA-TBL:42200/StambiaDeliveryService?WSDL
08/09/2021 11:34:24,535 - HTTP Rest Endpoint v2: http://STAMBIA-TBL:42200/rest/StambiaDeliveryService/2/<deliveryName>
08/09/2021 11:34:24,545 - HTTP Rest Endpoint v1: http://STAMBIA-TBL:42200/rest/StambiaDeliveryService/1/<deliveryName>
java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1017)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:655)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:249)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:566)

Installing the Log Database with startengine -updatedb

Before starting the Runtime, the Log Database must be initialized using the DDL user and the -updatedb option.

When using this -updatedb option, the Runtime is only doing the DDL operations for installing/updating the Log Database, and then it will stop.

D:\apps\stambiaRuntime\stambiaRuntimeSupport>startengine.bat -conf "properties\engineParametersDDL.xml" -updatedb

D:\apps\stambiaRuntime\stambiaRuntimeSupport>echo off
08/09/2021 11:38:36,555 - Updating Database...
08/09/2021 11:38:36,572 - Connection...
08/09/2021 11:38:37,395 - Connected, version:
08/09/2021 11:38:37,395 - Updating...
08/09/2021 11:38:45,086 - Updated to: 1.7.4.0

D:\apps\stambiaRuntime\stambiaRuntimeSupport>

Now this is done, the Oracle database contains the 15 Log Database tables:

The STB_LOG_VERSION_V table shows the Log Database model version:

Launching the Runtime with DML for standard usage

Now we can launch the Runtime with the "engineParameters.xml" file, and execute Deliveries.

D:\apps\stambiaRuntime\stambiaRuntimeSupport>startengine.bat
08/09/2021 11:53:27,224 - Runtime version: s17.5.4_20180302
08/09/2021 11:53:27,224 - Java version: 1.8.0_251 vendor: Oracle Corporation home: D:\apps\java\jre1.8.0_251
08/09/2021 11:53:28,244 - RMI server is started: rmi://STAMBIA-TBL:42000
08/09/2021 11:53:28,605 - Scheduler is started
08/09/2021 11:53:28,909 - SOAP Endpoint: http://STAMBIA-TBL:42200/wsi/DeliverableService?WSDL
08/09/2021 11:53:28,909 - SOAP Legacy "Non WSI-Compliant" Endpoint: http://STAMBIA-TBL:42200/StambiaDeliveryService?WSDL
08/09/2021 11:53:28,909 - HTTP Rest Endpoint v2: http://STAMBIA-TBL:42200/rest/StambiaDeliveryService/2/<deliveryName>
08/09/2021 11:53:28,910 - HTTP Rest Endpoint v1: http://STAMBIA-TBL:42200/rest/StambiaDeliveryService/1/<deliveryName>
08/09/2021 11:54:41,983 - MYPROCESS1 (c0a8000a017bc4d4b9c403c15466e66e) is started
08/09/2021 11:54:43,954 - MYPROCESS1 (c0a8000a017bc4d4b9c403c15466e66e) is ended

Upgrading the Runtime

Upgrading the Runtime consists in the following steps:

Migrating the files: see the procedure detailed on stambia.org
Migrating the database:

D:\apps\stambiaRuntime\stambiaRuntimeSupportNew>startengine.bat -conf "properties\engineParametersDDL.xml" -updatedb

D:\apps\stambiaRuntime\stambiaRuntimeSupportNew>echo off
08/09/2021 12:15:08,882 - Updating Database...
08/09/2021 12:15:09,213 - Connection...
08/09/2021 12:15:10,042 - Connected, version: 1.7.4.0
08/09/2021 12:15:10,042 - Updating...
08/09/2021 12:15:10,733 - Updated to: 1.7.5.8

D:\apps\stambiaRuntime\stambiaRuntimeSupportNew>

The STB_LOG_VERSION_V table shows the new Log Database model version:

The Runtime is now updated and can now be started with the DML user.

Configuration - Separating Runtime DDL and DML Operations Print