How to browse the "Activity" H2 database of WSO2 BPS and typical H2 databases in any WSO2 product

This guide will show you how to browse the activity database in WSO2 BPS and also the typical WSO2CARBON_DB of any WSO2 product too if you read the latter part.

All WSO2 Products are embedded with H2 database, and so there is no need of downloading h2 database separately and running it to browse WSO2 H2 database. (But remember that it can be done too).

And H2 databases are equipped with a Console Web application which lets you access and browse it using a browser.

Browse the "Activity" H2 database of WSO2 BPS

WSO2 BPS uses a separate database for its activity engine (which relate with BPMN process management) and if you are working with BPMN, and want to see the activity database, this is what you should do.

1. Open carbon.xml located in <PRODUCT_HOME>/repository/conf

2. Uncomment the <H2DatabaseConfiguration> element. (only uncomment the first 3 property elements of it as follows)

<H2DatabaseConfiguration>
        <property name="web" />
        <property name="webPort">8082</property>
        <property name="webAllowOthers" />
        <!--property name="webSSL" />
        <property name="tcp" />
        <property name="tcpPort">9092</property>
        <property name="tcpAllowOthers" />
        <property name="tcpSSL" />
        <property name="pg" />
        <property name="pgPort">5435</property>
        <property name="pgAllowOthers" />
        <property name="trace" />
        <property name="baseDir">${carbon.home}</property-->
</H2DatabaseConfiguration>

 3. Open the <WSO2_BPS_HOME>/repository/conf/datasources/activiti-datasources.xml file.

Now copy the <url> element's first part before the first semicolen (;). It would be as follows.

jdbc:h2:repository/database/activiti

4. Start the Product Server.
5. Open bowser and enter "http://localhost:8082/" (if you changed the <webport> in carbon.xml in step 2, you have to change the URL according to that)
6. Now you will see the H2 Database Console in the browser.

7. Here you have to give the JDBC URL , User Name and Password.

•  JDBC URL : jdbc:h2:repository/database/activiti  

        (the one you copied in step 3- note that the "activity" is the name of the database in H2, you want to access)

• User Name : wso2carbon
• Password : wso2carbon

(Default User name and passwords are as above, but if you have editted them in activiti-datasources.xml file they should be changed accordingly )

8. Now press "Connect" and you are done. If you have successfully connected "jdbc:h2:repository/database/activiti" database will be displayed and there will be a list of database tables such as, "ACT_EVT_LOG" , "ACT_GE_BYTEARRAY", "ACT_GE_PROPERTY", etc. which are created by the BPS activity engine. You can run queries there too.

Now in the terminal you started the wso2carbon BPS server, you can see long log traces related to h2 database and if you want to get rid of them you have only to comment out or remove the <property name="trace" /> in carbon.xml in step 2. Restart the server and connect to h2 console and you will see, they have gone.

Browse typical H2 database of any wso2 product

 All the wso2 products have the typical "WSO2CARBON_DB" and if you want to connect to it only difference is that, you only have to change the JDBS_URL given as follows in the step 7.
     jdbc:h2:repository/database/WSO2CARBON_DB 

(You can skip the step 3 here as you know the to use the WSO2CARBON_DB 's JDBC_URL. Anyway if you want to know, this url is also defined in repository/conf/master-datasources.xml file, and if its default configurations are editted, you will have to see this file)

Browse H2 database of any WSO2 product using an external H2 client.

 Anyway if you don't like to edit any wso2 product configuration file, you can do that too using a external H2 client. So you do not have to edit the carbon.xml as described in step 2.

But there is a trade off in this method. It is, we cannot access the h2 database via this external client, while the wso2 product is running. We have to shut-down the server before accessing via this method. And note that the attempt of vise-versa ( trying to start wso2 product server, while we have connected to the wso2 h2 database via this external client) would fail successful starting of wso2 product as the external client locks the h2 database so that the wso2 product cannot access it.

Anyway keeping these facts in mind let's see how to do the task.

1. Download H2 Database from here https://code.google.com/archive/p/h2database/downloads
   (a latest version is preferred)
2. Extract zip file and execute the h2.sh in bin directory with terminal (start terminal from inside bin directory and run sh h2.sh)
3. The H2 web console will be automatically started in your browser.
4. Then give the,

•  JDBC URL : jdbc:h2:/<WSO2_PRODUCT_HOME>/repository/database/WSO2CARBON_DB
  
  eg: jdbc:h2:/home/samithac/Repositories/product-bps-2/product-bps/modules/distribution/target/wso2bps-3.6.0-SNAPSHOT/repository/database/WSO2CARBON_DB
  
  Note that you have include the full path of the database directory here. (not just a relative path as we did before)
  (if you want to connect to wso2 bps's, activity database, just change above url's "WSO2CARBON_DB" part to "activity")

• User Name : wso2carbon
• Password : wso2carbon

5. Then Press connect & you are done..!!!