webMethods API Portal tutorial
Since API Portal do not support a highly available configuration using out of the box RDBMS supplied (PostgreSQL), you will have to use an external DBMS (where "external" means "software not provided together with SoftwareAG") like Oracle or MS SQL, which themselves offer mechanisms for clustering and high availability. This involves following 4 steps:
- Registering the external DBMS as an external service
- Creating the database schemas for each tenant
- Assigning the tenant to the database service
- Enhancing the runnables with the JDBC driver for the external database system
Step 1: Registering external service
Since our runnables use Zookeeper to find services, the information needed for API Portal to find and use an external DBMS has to be added to Zookeeper as well. We will be using the ACC commands for registering and updating external services. With the register external service command you perform the initial registration of the service. With the list external services command you can get an overview over all external services that are currently registered. With the show external service command you can review the properties of individual registered services. With the update external service command you can change individual properties of the registered service.
Note: For any of these commands to work, you need to have started your Zookeeper runnable (or Zookeeper runnables, if you have a highly available Zookeeper ensemble).
Registering an external Oracle DBMS
To register an external Oracle DBMS, you need to run the "register external service" command structured like this (note that the command below has been broken up across several lines by adding "\" character in front of every line break that we introduced for legibility).
register external service db \ host=<OracleHostnameOrIP> \
url="jdbc:oracle:thin:@<OracleHostnameOrIP>:<OraclePort>:<OracleServiceName>" \
driverClassName=oracle.jdbc.OracleDriver \
jmxEnabled=true \
username="<AppUserName>" \
password="<AppUserPwd>" \
maxIdle=15 \
maxActive=300 \
maxWait=10000 \
removeAbandoned=false \
removeAbandonedTimeout=600 \
logAbandoned=false \
initialSize=0 \
testOnBorrow=true \
validationQuery="select 1 from dual" \
defaultAutoCommit=false \
rollbackOnReturn=true \
jmxEnabled=true
|
Replace the placeholders (the parts in angled brackets), i.e.,<OracleHostnameOrIP>, <OraclePort>, <OracleServiceName>, <AppUserName> and <AppUserPwd>, with the hostname (or the IP address), the port and the service name of your Oracle DBMS, the name of the application user and the password of this user (as you specified them in the envset.bat file that comes as part of the schema creation scripts for the variables CIP_APP_USER and CIP_APP_PWD, respectively), respectively. Note that the <OracleHostnameOrIP> placeholder is used twice, once for the value of the "host" parameter, and a second time as part of the value of the "url" parameter.
The values for <OracleHostnameOrIP>, <OraclePort>, <OracleServiceName> (or the entire value of the "url" parameter) will have to be provided by your database administrator.
Registering an external MSSQL DBMS
The procedure to register an external Microsoft SQL Server DBMS is pretty much the same as for Oracle, the only difference being the default (and fixed) value of the "driverClassName" parameter and the structure of the value of the "url" parameter. The basic structure of the "register external service" command for MS SQL is the following:
register external service db \ host=<SqlServerHostnameOrIP> \
url="jdbc:sqlserver://<SqlServerHostnameOrIP>:<SqlServerPort>;DatabaseName=<DatabaseName>" \
driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver \
jmxEnabled=true \
username="<AppUserName>" \
password="<AppUserPwd>" \
maxIdle=15 \
maxActive=300 \
maxWait=10000 \
removeAbandoned=false \
removeAbandonedTimeout=600 \
logAbandoned=false \
initialSize=0 \
testOnBorrow=true \
defaultAutoCommit=false \
rollbackOnReturn=true \
jmxEnabled=true
|
Replace the parts in angled brackets, i.e., <SqlServerHostnameOrIP>,<SqlServerPort>, <DatabaseName>, <AppUserName> and <AppUserPwd> with the hostname (or IP address), the port, the database name and the database instance name of your SQLServer DBMS, and the application user name and password as you set it in the envset.bat before running the schema creation scripts. Note that the <SqlServerHostnameOrIP> placeholder is used twice, once for the value of the "host" parameter, and a second time as part of the value of the "url" parameter.
Step 2: Creating the database schemas for each tenant
API Portal is a multi-tenant-enabled application, i.e., a single physical API Portal installation (whether it is a single-node or multi-node, i.e., distributed installation) can host several "logical API Portal installations", i.e., "tenants". The different tenants share the infrastructure of their installation, but have completely separated content (i.e., users and user groups, collaboration content, documents, etc.). Inside the DBMS the data of different tenants is separated by holding it in different database schemas (or their respective equivalent, Oracle DBMS does not have the concept of a schema as a "container for database tables", but uses databases users for that purpose). Even if you do not plan to use more than one tenant for actual API Portal to work, each API Portal installation has at minimum not one but two tenants, named "default" and "master". "default" is the tenant that by default is used for API Portal. "master" is the tenant used for managing other tenants, in particular for creating new and deleting existing tenants.
In general, a dedicated schema needs to be created for each tenant. Since as we mentioned above each API Portal installation has at minimum not one but two tenants ("default" and "master"), you need to create at least two schemas, plus additional schemas for each additional tenant you want use on the installation.
Oracle
- Edit the file envset.bat to specify the connection data of the Oracle instance.
- Run the script cip_create_app_user.bat. This will create the application user.
-
Create an empty schema for each tenant.
- cip_create_schema_for_tenant.bat aris_master
- cip_create_schema_for_tenant.bat aris_default
MSSQL
- Edit the file envset.bat to specify the connection data of the MSSQL instance.
- Execute "inst.bat" .This will create the database including the two mandatory schemas and the application user.
- If you need additional tenants please use the "create_schema_for_tenant.bat" file.
Step 3: Assigning the tenant to the database service
After you registered your Oracle or MS SQL Server database system as described above and created the schemas for each tenant, you now need to tell about two things
1) the database service that should be used for each tenant
2) the name of the schema in the respective database that should be used for each tenant
Point 1) is needed, because when an external DB service is used, API Portal will not automatically assign tenants to it (as it would do when our PostgresSQL runnable is used), and because there can be multiple database services, as explained in the section about horizontal scaling across tenants above. Point 2) is needed because the choice of naming the schemas for the different API Portal tenants is at the discretion of the database administrators and will in general be different from the default schema naming rules used when API Portal itself can create the schemas (i.e., when our PostgresSQL runnable is used).
Fortunately, both aspects can be handled with a single ACC command that needs to be run once for each tenant, the "assign tenant to service" command. The command create a logical connection between a tenant and a concrete service instance (in this case an instance of the service type DB, as we created it in Zookeeper when we registered the external DB above). In addition, parameters can be specified for this logical connection. The only mandatory parameter is "com.aris.cip.db.schema", which is used for specifying the schema name.
The basic grammar of the "assign tenant to service" command is
"assign" "tenant" <tenantId> "to" "service" <serviceId> (<key> "=" <value>)*
- <tenantId> is the ID of the tenant ("default" and "master", respectively, for the two tenants that implicitly exist in every installation, or any string of a maximum length of 30 characters, that starts with a lower-case latin letter (i.e., "a" - "z") and consists of only lower-case latin letters and digits),
- <serviceId> the ID of the service (as it was returned by the "register external service command" above). <key> and <value> are the name and the value of various properties that can be stored with the tenant-2-service assignment. Multiple such key-value pairs can be specified.
For example, assuming your registered external DB service was given the service ID db0000000000 and you named the schema for the master tenant "aris_master" and the schema for the default tenant "aris_default", you would run the following two commands to make ARIS use this DB and these schemas for these tenants:
| assign tenant master to service db0000000000 com.aris.cip.db.schema="aris_master" assign tenant default to service db0000000000 com.aris.cip.db.schema="aris_default" |
Step 4: Enhancing the runnables with the JDBC driver for the external database system
The drivers have to be added to APIBundle runnable that access the database, by using the Provisioning enhancement functionality, using the commonsClasspath enhancement point. The basic structure of the required enhancement command is
on <nodeName> enhance <instanceId> with commonsClasspath path <pathToJdbcDriverFileInRepo>
Let's assume your JDBC driver JAR file was named driver.jar and you copied it into the directory "c:/jdbc". To enhance a runnable named "apiportalbundle_s" on node n1 with this file, you would run the command
on n1 enhance apiportalbundle_s with commonsClasspath path "c:/jdbc/driver.jar"