Dynamic Application Platform Products configuration with MySql 5.7 CE

Dynamic application platform products mainly include Agile Apps, Integration Server, Designer, and Business Console. Main component that combines all these products is DBO process (Dynamic Business Orchestrator), which allows user to build end-to-end business process using all the products described above. Each product is compatible with databases like SQL server, Oracle and MySQL.

Below are the steps to configure all involved DAP products with MySQL 5.7 community edition,

  1. Install MySql 5.7 CE.

a. Add “C:\Program Files\MySQL\MySQL Server 5.5\bin” to Environment variable “path”

b. Open cmd, run below commands

mysql –u root –proot

[Where root/root is Username/Password of MySql installation]

If installation is successful MySql version details will be displayed as result

 

  1. To configure MySql 5.7 CE for “Agile Apps”

a. Open C:\ProgramData\MySQL\MySQL Server 5.7\my.ini.

b. Add following entries and restart MySql server [If this parameters already exist then modify them with given values, these configurations      are requirement of Agile Apps product].

sql-mode="PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,                              NO_FIELD_OPTIONS"

 max_allowed_packet = 536870912

c. Open cmd, run below commands. It is to grant permissions for root user at given host[% represent any host]

mysql –u root –proot [It will open MySql console]

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

FLUSH PRIVILEGES;

  1. Download mysql-java-connector jar.

         Download Software AG installer.

a. Install DAP products from recent sandbox by choosing DAP Enterprise Edition from Typical Installation.

Provide MySql DB URL and mysql-java-connector jar location for Agile Apps during the installation along with hostname, port [Usually 3306], MySql root username and password

Sample MySql DB URL: jdbc:mysql://mysqldomain.com:3306/sampledb

Once the installation is complete, run following command to test if Agile Apps DB tables are created

mysql –u root –proot

show databases;

                      Results of above commands will be similar to below screenshot

  

b. If DB tables for Agile Apps are not created, run below command at,

<Software AG_directory>\IntegrationServer\instances

is_instance.bat update -Dinstance.name=<instance_name> - Dpackage.list=WmAgileApps

Example:

is_instance.bat update -Dinstance.name=default -Dpackage.list=WmAgileApps

[Run this command once DB tables for IS are created from Database Configurator]

 

     4. To configure “Database Configurator” tables for MySql CE

a. Place downloaded mysql-java-connector jar in below mentioned directory:

    <Software AG_directory>\common\lib\ext

b. In a text editor, open the setEnv.bat file from

<Software AG_directory>\common\db\bin

Add the following classpath entry in the setEnv.bat file. It adds the location of mysql-java-connector jar file to the classpath

set CLASSPATH=%CLASSPATH%;%DCI_HOME%\..\lib\ext\mysql_driver_jar

Example:

set CLASSPATH=%CLASSPATH%;%DCI_HOME%\..\lib\ext\mysql-connector-java.jar

c. Create DB tables by running “dbConfiguratorUI.bat” at<Software AG_directory>\common\db\bin, by providing same DB details as given  during the DAP product installation and MySql user credentials [root\root].

 

     5. To Configure “Integration server” with MySql CE

a. In a text editor, open the ini.cnf file from the following directory:

<Software AG_directory>\IntegrationServer\instances\IS_instance_name \bin

b. Add the following entry to the application.classpath property:]

%COMMON_LIB_EXT%mysql-connector-java.jar

c. Restart Integration Server [If it’s already up].

d. Use the following URL to configure a JDBC connection pool alias for MySQL Community Edition 5.7 on the

    Settings > JDBC Pools screen in Integration Server Administrator:

jdbc:mysql://<server>:<3306|port>/<databaseName>?relaxAutoCommit=true&useSSL=false&useLegacyDatetimeCode=false&serverTimezone=integration_server_timezone

Example:

jdbc:mysql://localhost:3306/SampleDB?relaxAutoCommit=true&useLegacyDatetimeCode=false&serverTimezone=PST

     6. To configure “MyWebmethods” Server with MySql CE

a. Place downloaded mysql-java-connector jar in the following directory:

    <Software AG_directory>\MWS\lib

b. Create a < mysql-connector-name>.bnd text file in

    <Software AG_directory>\MWS\lib directory,

    Example:

    mysql-connector-java-version.bnd

c. Provide instructions for the OSGi bundle conversion in the .bnd text file

# attach as fragment to the caf.server bundle

Fragment-Host: com.webmethods.caf.server

Bundle-SymbolicName: mysql-connector-java-

Bundle-Version: < mysql-connector-version >

Include-Resource: < mysql-connector-jar >

-exportcontents: *

Bundle-ClassPath: < mysql-connector-jar >

Import-Package: *;resolution:=optional

Example:

# attach as fragment to the caf.server bundle

Fragment-Host: com.webmethods.caf.server

Bundle-SymbolicName: mysql-connector-java-

Bundle-Version: 5.1.41

Include-Resource: mysql-connector-java-5.1.41.jar

-exportcontents: *

Bundle-ClassPath: mysql-connector-java-5.1.41.jar

Import-Package: *;resolution:=optional

d. Run following command at

   <Software AG_directory>\MWS\bin

    mws.bat -s < serverInstanceName > update

    Example:

    mws.bat –s default update

e. Deploying .war files to My webMethods Server might fail after completing the procedure. If such an issue occurs, increase the following parameters in the MySQL configuration file, and then restart the MySQL database server

    innodb_buffer_pool_size = 5G

   innodb_log_file_size = 80M

  max_allowed_packet = 24M

     7. To Configure the MySQL Community Edition 5.7 Driver for “Process Upload”

a. Place downloaded mysql-java-connector jar in the following directory

    <Software AG_directory>\Designer\eclipse\plugins\com.webmethods.process.metadatadeployer_<softwareag_product_version>\lib

    Example:

    C:\SoftwareAG \Designer\eclipse\plugins\com.webmethods.process.metadatadeployer_10.1.0.0000-xxxx\lib

b. In a text editor, open the plugin manifest file from the following directory:

    <Software AG_directory>\Designer\eclipse\plugins\com.webmethods.process.metadatadeployer__<softwareag_product_version>\META-

     INF\manifest.mf

c. Append the mysql connector jar classpath to Bundle-ClassPath as below

    Bundle-ClassPath: metadeployer.jar,lib/wm-wsclient.jar,lib/mysql-connector-java-5.1.43-bin.jar

d. Restart Designer

 

     8. In the Designer, create a DBO Process containing IS start document and User Task [Process can be simple or as per the need can involve Service task, User task and Case task]

     Upload process, sync start document and publish the document, If all the below events takes place then DBO is successfully configured with MySQL 5.7 CE

a. In Business Console Process instance should get triggered

b. Task should be queued

c. On task completion process should get completed