WSO2 Enterprise Integrator, DSS, and Time Zone Handling

Introduction: Understanding DSS

One of the significant strengths of the WSO2 SOA infrastructure lies in its use of the Enterprise Integrator as a bridge between a Database and the API Manager.

This is achieved through Data Services Server (DSS), which allows you to connect to a database, execute desired queries, and subsequently transform them into Web services and APIs.

Dates and DSS

A problem I encountered while using DSS was the management of dates. Specifically, I noticed that when retrieving a date from a database query, the DSS response converted it to UTC time. For example:

SQL date = 2020-08-31 23:00:00.000 Date in DSS results = “2020-09-01 01:00:00.000”

This is clearly a significant issue. How do we resolve it?

The Solution

Here are the steps:

  1. Navigate to the /bin folder within your EI home directory.
  2. Open the integrator.sh file (on Linux) or integrator.bat file (on Windows) with a text editor (I hope you're using Linux, so use nano, ee, or vi).
  3. Add the following instruction at the end of the file: -Ddss.legacy.timezone.mode=true
  4. Restart the EI.

Simple and painless.

Here's an example of the modification to the integrator.sh file:

CMD_LINE_ARGS=
-Xbootclasspath/a:%CARBON_XBOOTCLASSPATH%
-Xms256m -Xmx1024m
-XX:MaxPermSize=256m
-XX:+HeapDumpOnOutOfMemoryError -XX:HeapDumpPath="%CARBON_HOME%\repository\logs\heap-dump.hprof" -Dcom.sun.management.jmxremote
-classpath %CARBON_CLASSPATH% %JAVA_OPTS%
-Djava.endorsed.dirs=%JAVA_ENDORSED% -Dcarbon.registry.root=/
-Dcarbon.home="%CARBON_HOME%" -Dwso2.server.standalone=true
-Djava.command="%JAVA_HOME%\bin\java"
-Djava.opts="%JAVA_OPTS%" -Djava.io.tmpdir="%CARBON_HOME%\tmp"
-Dcatalina.base="%CARBON_HOME%\lib\tomcat"
-Dwso2.carbon.xml=%CARBON_HOME%\repository\conf\carbon.xml
-Dwso2.registry.xml="%CARBON_HOME%\repository\conf\registry.xml"
-Dwso2.user.mgt.xml="%CARBON_HOME%\repository\conf\user-mgt.xml"
-Dwso2.transports.xml="%CARBON_HOME%\repository\conf\mgt-transports.xml"
-Djava.util.logging.config.file="%CARBON_HOME%\repository\conf\etc\logging-bridge.properties"
-Djava.util.logging.manager=org.apache.juli.ClassLoaderLogManager -Dcarbon.config.dir.path="%CARBON_HOME%\repository\conf"
-Dcomponents.repo="%CARBON_HOME%\repository\components"
-Dconf.location="%CARBON_HOME%\repository\conf"
-Dcom.atomikos.icatch.file="%CARBON_HOME%\lib\transactions.properties" -Dcom.atomikos.icatch.hide_init_file_path="true"
-Dorg.apache.jasper.compiler.Parser.STRICT_QUOTE_ESCAPING=false
-Dorg.apache.jasper.runtime.BodyContentImpl.LIMIT_BUFFER=true
-Dcom.sun.jndi.ldap.connect.pool.authentication=simple
-Dcom.sun.jndi.ldap.connect.pool.timeout=3000
-Dorg.terracotta.quartz.skipUpdateCheck=true
-Dcarbon.classpath=%CARBON_CLASSPATH%
-Dfile.encoding=UTF8 -DworkerNode=false
-Dorg.wso2.ignoreHostnameVerification=true
-Ddss.legacy.timezone.mode=true
WSO2 DSS Timezone