BPM, Workflow, and Case

BPM, Workflow, and Case

Come for answers. Stay for best practices. All we’re missing is you.

 View Only

Enable liberty feature with SQL connector for Workflow in CP4BA

By PING MEI posted Mon March 21, 2022 03:01 AM

  
If you want to connect to external database, we support to enable liberty feature with SQL connector for workflow starter and production pattern, you can follow the steps below to configure in your environment.

1.Config for starter and production pattern in your custom resource(CR) file

* Starter Pattern - Workflow Authoring and Workstream
According to the current topology,  BAStudio and Workflow Authoring are sharing one container, so you can see the different configuration for BAStudio and Workflow Authoring to enable SQL connector in your CR

1) [Optional]Prepare file store PVC, if your environment does not support the dynamical provision, please do the following steps. Or else, skip it, the default PVC named < CRName >-bastudio-files-pvc will be created after you apply CR

a. Create baw file store PVC as below, you can update the info of path and server in the file according to your requirement
baw-filestore-pv.yaml
 
apiVersion: v1
kind: PersistentVolume
metadata:
  name: baw-filestore-pv
spec:
  storageClassName: baw-filestore-pv
  accessModes:
  - ReadWriteMany
  capacity:
    storage: 1Gi
  nfs:
    path: NFS_storage_directory/baw/filestore
    server: NFS_server_IP
  persistentVolumeReclaimPolicy: Recycle

baw-filestore-pvc.yaml

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: baw-filestore-pvc
spec:
  accessModes:
  - ReadWriteMany
  resources:
    requests:
      storage: 1Gi
  storageClassName: baw-filestore-pv
  volumeName: baw-filestore-pv
status:
  accessModes:
  - ReadWriteMany
  capacity:
    storage: 1Gi

b. run the following command to create baw file store PVC
oc apply -f baw-filestore-pv.yaml
oc apply -f baw-filestore-pvc.yaml

c. config file store PVC under workflow_authoring_configuration in your CR
 
workflow_authoring_configuration:
     ## storage configuration
     storage:
       use_dynamic provisioning: false
       ## Persistent volume claim (PVC) for generic files.
       existing_pvc_for_filestore: "baw-filestore-pvc"
       ## Minimum size of the persistent volume (PV) that is mounted as the generic file store.
       size_for_filestore: "1Gi"

2) You can run the command below to copy your JDBC driver or files of SQL Integration services to BAStudio container location /opt/ibm/bawfile, the files will be stored in PV related PVC created and kept between restarting.
oc cp jdbcdriver.file bastudio-pod:/opt/ibm/bawfile/jdbcdriver.file

3) Customize the Liberty custom XML file is set in the BAStudio configuration of your CR(note: for the file path, like jdbc driver, database certification, it is container path /opt/ibm/bawfile mounted with file store PVC),  example for postgresql/oracle with SSL config

Note: for Postgresql client authentication, you cannot directly use *.key of private key for jdbc connection, you can use the command "openssl pkcs8 -topk8 -inform PEM -outform DER -nocrypt -in client.key -out client.pk8" to change the format of private key from *.key to *.pk8

  bastudio_configuration:
    # Custom liberty XML configurations
    custom_xml: |+
      <server>
        <!-- Postgresql server authentication -->
        <dataSource id="DefaultDataSource" jndiName="jdbc/postgres">
          <jdbcDriver libraryRef="PostgresLib"/>
          <properties.postgresql databaseName="chdb2" serverName="my.fyre.ibm.com" portNumber="5432" user="postgres" password="postgres" ssl="true" sslRootCert="/opt/ibm/bawfile/postgresql_server.crt"/>
        </dataSource>

        <library id="PostgresLib">
          <file name="/opt/ibm/bawfile/postgresql-42.2.18.jar"/>
        </library>

        <!-- Postgresql client authentication -->
        <dataSource id="DefaultDataSource" jndiName="jdbc/postgresclient">
          <jdbcDriver libraryRef="PostgresLib"/>
          <properties.postgresql databaseName="chdb1" serverName="my.fyre.ibm.com" portNumber="5432" user="authadmin" password="authadmin" ssl="true" sslRootCert="/opt/ibm/bawfile/root.crt" sslCert="/opt/ibm/bawfile/client.crt" sslKey="/opt/ibm/bawfile/client.pk8" sslMode="verify-full"/>
        </dataSource>

        <library id="PostgresLib">
          <file name="/opt/ibm/bawfile/postgresql-42.2.18.jar"/>
        </library>
        
        <!-- oracle SSL -->
        <dataSource id="oracle" jndiName="jdbc/oracle">
          <jdbcDriver libraryRef="OracleLib"/>
          <properties.oracle URL="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=my.fyre.ibm.com)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=pdb.fyre.ibm.com)))" user="chuser2" password="Passw0rd" connectionProperties="oracle.net.wallet_location=/opt/ibm/bawfile/cwallet.sso"/>
        </dataSource>

        <library id="OracleLib">
          <file name="/opt/ibm/bawfile/ojdbc8.jar"/>
          <file name="/opt/ibm/bawfile/oraclepki.jar"/>
          <file name="/opt/ibm/bawfile/osdt_core.jar"/>
          <file name="/opt/ibm/bawfile/osdt_cert.jar"/>
        </library>
      </server>
​

For other types of databases, you can refer to Configuring relational database connectivity in Liberty for data source configuration of database connection, more properties of datasource, like ssl setting, refer to WebSphere Application Server Liberty document https://www.ibm.com/docs/en/was-liberty/nd?topic=configuration-openidconnectprovider#oauthProvider/databaseStore/dataSource

* Production Pattern - Workflow Authoring or Workflow Runtime Server
1) [Optional]Prepare file store PVC, if your environment does not support the dynamical provision, please do the following steps. Or else, skip it, the default PVC named < CRName >-bastudio-files-pvc will be created after you apply CR

a. Create baw file store PVC as below, you can update the info of path and server in the file according to your requirement
baw-filestore-pv.yaml
 
apiVersion: v1
kind: PersistentVolume
metadata:
  name: baw-filestore-pv
spec:
  storageClassName: baw-filestore-pv
  accessModes:
  - ReadWriteMany
  capacity:
    storage: 1Gi
  nfs:
    path: NFS_storage_directory/baw/filestore
    server: NFS_server_IP
  persistentVolumeReclaimPolicy: Recycle

baw-filestore-pvc.yaml

apiVersion: v1
kind: PersistentVolumeClaim
metadata:
  name: baw-filestore-pvc
spec:
  accessModes:
  - ReadWriteMany
  resources:
    requests:
      storage: 1Gi
  storageClassName: baw-filestore-pv
  volumeName: baw-filestore-pv
status:
  accessModes:
  - ReadWriteMany
  capacity:
    storage: 1Gi

b. run the following command to create baw file store PVC
oc apply -f baw-filestore-pv.yaml
oc apply -f baw-filestore-pvc.yaml

c. config file store PVC under workflow_authoring_configuration for workflow authoring or baw_configuration for workflow runtime server  in your CR
Workflow Authoring:
 
workflow_authoring_configuration:
     ## storage configuration
     storage:
       use_dynamic provisioning: false
       ## Persistent volume claim (PVC) for generic files.
       existing_pvc_for_filestore: "baw-filestore-pvc"
       ## Minimum size of the persistent volume (PV) that is mounted as the generic file store.
       size_for_filestore: "1Gi"

Workflow Runtime Server
baw_configuration:
- name: bawins1
  ## storage configuration
  storage:
    use_dynamic_provisioning: false
    ## Persistent volume claim (PVC) for generic files.
    existing_pvc_for_filestore: "baw-filestore-pvc"
    ## Minimum size of the persistent volume (PV) that is mounted as the generic file store.
    size_for_filestore: "1Gi"​

2) You can run the command below to copy your JDBC driver or files of SQL Integration services to workflow authoring or runtime container location /opt/ibm/bawfile, the files will be stored in PV related PVC created and kept between restarting.
oc cp jdbcdriver.file workflow-authoring-pod(or workflow-runtime-pod):/opt/ibm/bawfile/jdbcdriver.file

3) Customize the Liberty custom XML file is set in Workflow Authoring or Workflow Runtime Server configuration of your CR(note: for the file path, like jdbc driver, database certification, it is container path /opt/ibm/bawfile mounted with file store PVC),  example for postgresql/oracle with SSL config

Note: for Postgresql client authentication, you cannot directly use *.key of private key for jdbc connection, you can use the command "openssl pkcs8 -topk8 -inform PEM -outform DER -nocrypt -in client.key -out client.pk8" to change the format of private key from *.key to *.pk8

Workflow Authoring

  workflow_authoring_configuration:

    # Custom liberty XML configurations
    liberty_custom_xml: |+
      <server>
        <!-- Postgresql server authentication -->
        <dataSource id="DefaultDataSource" jndiName="jdbc/postgres">
          <jdbcDriver libraryRef="PostgresLib"/>
          <properties.postgresql databaseName="chdb2" serverName="my.fyre.ibm.com" portNumber="5432" user="postgres" password="postgres" ssl="true" sslRootCert="/opt/ibm/bawfile/postgresql_server.crt"/>
        </dataSource>

        <library id="PostgresLib">
          <file name="/opt/ibm/bawfile/postgresql-42.2.18.jar"/>
        </library>

        <!-- Postgresql client authentication -->
        <dataSource id="DefaultDataSource" jndiName="jdbc/postgresclient">
          <jdbcDriver libraryRef="PostgresLib"/>
          <properties.postgresql databaseName="chdb1" serverName="my.fyre.ibm.com" portNumber="5432" user="authadmin" password="authadmin" ssl="true" sslRootCert="/opt/ibm/bawfile/root.crt" sslCert="/opt/ibm/bawfile/client.crt" sslKey="/opt/ibm/bawfile/client.pk8" sslMode="verify-full"/>
        </dataSource>

        <library id="PostgresLib">
          <file name="/opt/ibm/bawfile/postgresql-42.2.18.jar"/>
        </library>
        
        <!-- oracle SSL -->
        <dataSource id="oracle" jndiName="jdbc/oracle">
          <jdbcDriver libraryRef="OracleLib"/>
          <properties.oracle URL="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=my.fyre.ibm.com)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=pdb.fyre.ibm.com)))" user="chuser2" password="Passw0rd" connectionProperties="oracle.net.wallet_location=/opt/ibm/bawfile/cwallet.sso"/>
        </dataSource>

        <library id="OracleLib">
          <file name="/opt/ibm/bawfile/ojdbc8.jar"/>
          <file name="/opt/ibm/bawfile/oraclepki.jar"/>
          <file name="/opt/ibm/bawfile/osdt_core.jar"/>
          <file name="/opt/ibm/bawfile/osdt_cert.jar"/>
        </library>
      </server>
​


Workflow Runtime Server

  baw_configuration:
  - name: bawins1
   # Custom liberty XML configurations
    liberty_custom_xml:: |+
      <server>
        <!-- Postgresql server authentication -->
        <dataSource id="DefaultDataSource" jndiName="jdbc/postgres">
          <jdbcDriver libraryRef="PostgresLib"/>
          <properties.postgresql databaseName="chdb2" serverName="my.fyre.ibm.com" portNumber="5432" user="postgres" password="postgres" ssl="true" sslRootCert="/opt/ibm/bawfile/postgresql_server.crt"/>
        </dataSource>

        <library id="PostgresLib">
          <file name="/opt/ibm/bawfile/postgresql-42.2.18.jar"/>
        </library>

        <!-- Postgresql client authentication -->
        <dataSource id="DefaultDataSource" jndiName="jdbc/postgresclient">
          <jdbcDriver libraryRef="PostgresLib"/>
          <properties.postgresql databaseName="chdb1" serverName="my.fyre.ibm.com" portNumber="5432" user="authadmin" password="authadmin" ssl="true" sslRootCert="/opt/ibm/bawfile/root.crt" sslCert="/opt/ibm/bawfile/client.crt" sslKey="/opt/ibm/bawfile/client.pk8" sslMode="verify-full"/>
        </dataSource>

        <library id="PostgresLib">
          <file name="/opt/ibm/bawfile/postgresql-42.2.18.jar"/>
        </library>
        
        <!-- oracle SSL -->
        <dataSource id="oracle" jndiName="jdbc/oracle">
          <jdbcDriver libraryRef="OracleLib"/>
          <properties.oracle URL="jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=my.fyre.ibm.com)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=pdb.fyre.ibm.com)))" user="chuser2" password="Passw0rd" connectionProperties="oracle.net.wallet_location=/opt/ibm/bawfile/cwallet.sso"/>
        </dataSource>

        <library id="OracleLib">
          <file name="/opt/ibm/bawfile/ojdbc8.jar"/>
          <file name="/opt/ibm/bawfile/oraclepki.jar"/>
          <file name="/opt/ibm/bawfile/osdt_core.jar"/>
          <file name="/opt/ibm/bawfile/osdt_cert.jar"/>
        </library>
      </server>
​
      </server>


For other types of databases, you can refer to Configuring relational database connectivity in Liberty for data source configuration of database connection, more properties of datasource, like ssl setting, refer to WebSphere Application Server Liberty document https://www.ibm.com/docs/en/was-liberty/nd?topic=configuration-openidconnectprovider#oauthProvider/databaseStore/dataSource

2.After you modify the custom resource file, you must update your deployment to have it take effect by applying CR "oc apply -f <your_CR_file>"

3.Wait for operator to reconcile, once the deployment is ready, you can access BAStudio or Workflow Authoring/Runtime server URL to create automation application by process designer and connect your external database

1) Create an automation application to create a process

2) Import SQL connection service flow from Tookit



















3) Provide the value for the variables(note: for dataSourceName, it is same with the configuration jndiName in your CR)

4) Now you connect to your external database, you can see the return result
0 comments
116 views

Permalink

https://community.ibm.com/community/user/blogs/ping-mei1/2022/03/18/enable-liberty-feature-with-sql-connector-for-work