Introduction SAP HANA database is among the several options available for ThingWorx as persistence provider, in this blog I'll share the experience and challenges I had while setting up the SAP HANA and then configuring it to run as Persistence Provider for ThingWorx Pre-requisite Eclipse Mars or higher SAP Hana Express Edition 2.0 ThingWorx Hana Persistence Provider Package Setting up SAP Hana SAP Hana Express Edition 2.0 is freely available from SAP via the download with a pre-built machine with SUSE Enterprise OS. For this blog I used the SAP Hana Express Edition 2.0 Server only version while downloading the pre-built VM from SAP Download the Server only or Server + Application VM from the SAP's website 2. Unzip and open the hxe.ova (file name will differ if you are downloading the server + application VM) file in one of the hyper-visor, in my case I'll be using VMWare Workstation, but should work just fine with others e.g. Oracle VirtualBox 3. Once opened in a hypervisor SUSE will boot up and you will be prompted for the new password, as VM is configured for force first time password change 4. If you are unsure of the next steps to follow refer to the Getting Started with SAP Hana express edition guide which is selected by default and gets downloaded when the VM is downloaded 5. Another thing to note about the PDF that it contains all the details on the username and password required for initial setup 6. Provide the password for the OS user which is hxeadm, note you can always refer to the PDF file which we downloaded while downloading the VM from SAP's download manager 7. Once the password is changed for the OS user, you will also be prompted to change the master DB user's password as well 8. Make note of these passwords SAP HANA DB Instance After completing the initial steps for setting up the VM and changing the password we can now check the pre-built database installed on the VM which is called as SystemDB. You can check that and same basic settings running on the instance by using the following command in the shell $ HDB info This command will print out the features and components installed on the VM, if you are using Server only VM as I did you shall see something of this printed on the shell. In case it doesn't show up you can start the database and check again of the running services $ HDB start The output will be something like this, note the highlighted part for below output already include couple of additional services that is because i have already created the ThingWorx Database : FYI: If you wish to shut down the database you can use following command: $ HDB Stop With this let's move to the next step to create the new database which will be used by the ThingWorx application. One thing to note that you can run the SAP Hana in multi-tenant or in single tenant mode. With the free edition i.e. the SAP Hana Express Edition multi-tenant is the only available option. This is why we will create the ThingWorx DB and the schema and use that to populate it with the required tables and other DB entities needed to support the persistence provider from ThingWorx webapp Creating and configuring the ThingWorx Database with ThingWorx Schema For this you'll be required to download from the PTC Software download page the package for the ThingWorx with SAP Hana Persistence provider package which contains the DB creation scripts. You can now choose to create the schema in two ways i.e. a. Creating and Configuring the Database using PTC provided Bash Scripts b. Manually Creating and configuring the SAP Hana Database Both of these details are covered in the Getting Started with SAP Hana and ThingWorx Guide For this blog I created everything manually that is by connecting via the client which i have installed as plugin in my existing Eclipse Mars. If you are running different version of Eclipse check this link If you are also using the Eclipse Mars you can use this link in Eclipse > Help > Install New Software > https://tools.hana.ondemand.com/mars/ Once installed make sure to restart the eclipse and open the SAP Hana Administration Console perspective, which should come up something like this: Since I have already installed and created some connections to the DB they are visible on the left side of the Eclipse. Creating Connection to the SAP Hana Database 1. Navigate to the Systems tab on the left in the SAP Hana Administration Console perspective in Eclipse 2. Click on the Add Systems icon located right on the Systems tab, like so 3. Provide some basic connection details like a. Host Name: IP or machine name of the VM running the SAP Hana Express edition b. Instance Number: 90 c. Mode: If you are using free express edition as I have used for this blog you will be required to select Multiple containers. As soon you will see that we will create ThingWorx as tenant DB alongside the System DB d. Select System Database since for now we have not yet created a database called ThingWorx. Click next. 4. On the next page provide the username/password, i will be logging in as SYSTEM user since I have not yet created any other user and I'll use the master DB password which I'd set when configuring the VM for the first time. 5. Click Finish to complete and test the connection Creating ThingWorx User and ThingWorx Database Successful connection creation will show the connection with green icon under the Systems tab which will allow you to then explore the connection for different objects existing under the SYSTEM database. To begin using the SQL statements select the connection and click on the SQL tab as highlighted in the screenshot below: With this you have now successfully created the connection and also have a SQL client to execute SQL tasks As mentioned previously you have two options to setup the schema and the data model for the ThingWorx Database, these are 1. Using the shell script to create and configure the ThingWorx Database and it's schema together with the required User and the data model. For this you will need following: a. thingworxHanaDatabaseSetup.sh b. thingworxHanaSchemaSetup.sh Note: These two shell scripts will call the SQL procedures provided in the install folder ThingWorx-Platform-Hana-X-X-X.zip you have downloaded from the PTC Software Download ThingWorx-Platform-Hana-X-X-X.zip package incorrectly provides sample platform-settings.json with PostgresPersistenceProviderPackage configuration for ThingWorx Installation. So make sure that user executing those scripts should have all the required files in place, preferably in same folder as the script files and should have sufficient access rights to execute the scripts 2. The other option is to do everything manually i.e. create Database and it's schema, it's user and the data model via the SQL scripts provided in the install folder Both of these options are covered quite in detail in the installation guide so I'd recommend referring to that for step by step process. For creating a SQL connection using the newly created TWADMIN user follow the steps described in the section Creating Connection to the SAP Hana Database above. Here's how the configuration for connecting to the ThingWorx DB will look like With this we are now connected to an empty THINGWORX database under which TWADMIN schema will be created using the SQL scripts provided in the install directory of ThingWorx-Platform-Hana-X-X-X.zip package. Note that it's important that below listed scripts are executed in following order 1. thingworx-model-schema.sql 2. thingworx-data-schema.sql 3. thingworx-property-schema.sql 4. thingworx-data-proc.sql Checking the port for ThingWorx Database created in SAP Hana This can be checked by executing the following command directly from the SQL Client in the Eclipse. You may need to execute this command using SYSTEM user account, if any permission objections are raised in the client select * from "SYS_DATABASES".m_services This will return the result of all the databases and services with their port, check the SQL Port for the ThingWorx database : Configuring the SAP Hana Persistence Provider Now that we have met all the pre-requisites to connect to the our ThingWorx Database in SAP HANA let's configure the platform-settings.json file under placed under the \\<ThingworxInstallationDirectory>\ThingworxStorage\ Note: The sample platform-settings.json included with the downloaded ThingWorx-Platform-Hana-X-X-X.zip package incorrectly includes the PostgresPersistenceProviderPackage. A Jira is already filed and can be tracked via this knowledge base article ThingWorx-Platform-Hana-X-X-X.zip package incorrectly provides sample platform-settings.json with PostgresPersistenceProviderPackage configuration for ThingWorx Installation As a workaround for now, make sure to replace the PostgresPersistenceProviderPackage with HanaPersistenceProviderPackage, like so { "PersistenceProviderPackageConfigs": { "HanaPersistenceProviderPackage": { "ConnectionInformation": { "driverClass": "com.sap.db.jdbc.Driver", "jdbcUrl": "jdbc:sap://<IPAddressForSAPHana>:39041/?autocommit=false¤tschema=TWADMIN", "dbSchema": "TWADMIN", "username": "TWADMIN", "password": "Thingworx123", "acquireIncrement": 5, "acquireRetryAttempts": 50, "acquireRetryDelay": 10000, "checkoutTimeout": 1000000, "fetchSize": 5000, "idleConnectionTestPeriod": 60, "initialPoolSize": 5, "maxConnectionAge": 0, "maxIdleTime": 0, "maxIdleTimeExcessConnections": 300, "maxPoolSize": 100, "maxStatements": 100, "minPoolSize": 5, "numHelperThreads": 8, "testConnectionOnCheckout": false, "unreturnedConnectionTimeout": 0 } } } } Configuring Tomcat to connect to SAP Hana ThingWorx Database Thingworx SAP Hana persistence provider connects to the SAP Hana database instance using the ngdbc.jar. As mentioned in the Getting Started with SAP Hana and ThingWorx guide this jar is required to placed under the <TomcatInstallationDirectory>\lib location (for more detail see ThingWorx SAP Hana Guide). You can obtain this jar by installing this SAP Hana Client or SAP Hana plugin installed in Eclipse. Note: Currently there's a known issue around ThingWorx failing to connect to SAP Hana Express Edition v2.0 due to the use of newer version of ngdbc.jar i.e. v2.0 or above. A Jira, already filed for this, can be tracked in this PTC Knowledgebase Article : Tomcat fails to start with error "Failed to create com.thingworx.persistence.hana.HanaPersistenceProviderPackage. Cause was due to 'null'" when installing ThingWorx with SAP Hana Persistence Provider You can download the jar directly from SAP Hana webpage with a valid service account. Since the older version is not available for direct download without service account from the SAP's download page, this jar can be obtained by installing the old Eclipse e.g. Kepler and then install the SAP Hana plugin on top of it which includes the jar version 1.XX in the folder created by the SAP Hana Tools For more detail on installing and configuring Tomcat or setting up the license file refer to the ThingWorx Core Installation guide Verifying the installation Finally, start the Tomcat service and try to access the ThingWorx Composer by typing in the URL e.g. http://localhost:8080/Thingworx You can also verify the connectivity between the ThingWorx and the SAP Hana by navigating to the application.log located under ThingworxStorage\logs and look for INFO [T: localhost-startStop-1] Database initialization complete. This will confirm the database was initialized and connected to successfully Common connectivity issues Issue 1: After deploying and starting up the Tomcat you may see that the ThingWorx webapp is stalling despite logging the message that the database initialization started Cause: This could happen for variety of reasons e.g. a. Incorrect port was used in the JDBC connection URL in the platform-settings.json b. ThingWorx database is incorrectly spelled c. Connection is timing out e.g. due to network issues Solution: a. For port ensure that correct port is used by executing this command to verify on which port the database service listening b. Connection retries could be increased in the platform-settings.json file c. Reconfirm the correct username Issue 2: Application log in ThingWorx Tomcat would log the error that persistence provider is null Cause: Incorrect jar version is used. Solution: a. Recheck the jar version which has been used under tomcat\lib folder
View full tip
