Tuesday, December 31, 2013

BizTalk Services Mapper - WCF SQL Select Query - Cumulative Concatenate

Say you want to create a map in order to produce the SELECT statement for a WCF-SQL query and you start of with a list of values to include in the where clause.

How to achieve this with the BizTalk Services mapper?

The Source XML:
<ns0:GetProducts xmlns:ns0="http://GetProductsBizTalkService.Schemas">
  <Column>
    <Name>Name_0</Name>
    <Value>Value_0</Value>
  </Column>
  <Column>
    <Name>Name_1</Name>
    <Value>Value_1</Value>
  </Column>
  <Column>
    <Name>Name_2</Name>
    <Value>Value_2</Value>
  </Column>
</ns0:GetProducts>

The Destination XML:
<ns3:Select xmlns:ns3="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products">
  <ns3:Columns>*</ns3:Columns>
  <ns3:Query>where Name_0='Value_0' and Name_1='Value_1' and Name_2='Value_2'</ns3:Query>
</ns3:Select>

First, how could we do this within XSLT?

This can be done with the following XSLT:
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:msxsl="urn:schemas-microsoft-com:xslt"
                xmlns:s0="http://GetProductsBizTalkService.Schemas"
                xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products"
                exclude-result-prefixes="msxsl s0">
  <xsl:output method="xml"
              indent="yes"/>

  <xsl:template match="/">
    <xsl:apply-templates select="/s0:GetProducts" />
  </xsl:template>
  <xsl:template match="/s0:GetProducts">
    <ns0:Select>
      <ns0:Columns>*</ns0:Columns>
      <xsl:if test="Column">
        <ns0:Query>
          <xsl:for-each select="Column">
            <xsl:if test='position() = 1'>where </xsl:if>
            <xsl:value-of select="Name"/>='<xsl:value-of select="Value"/>'
            <xsl:if test='position() != last()'> and </xsl:if>
          </xsl:for-each>
        </ns0:Query>
      </xsl:if>
    </ns0:Select>
  </xsl:template>
</xsl:stylesheet>

The result of the above will be:
<?xml version="1.0" encoding="utf-8"?>
<ns0:Select xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/TableOp/dbo/Products">
  <ns0:Columns>*</ns0:Columns>
  <ns0:Query>
    where Name_0='Value_0'
    and Name_1='Value_1'
    and Name_2='Value_2'
  </ns0:Query>
</ns0:Select>

Finally, how to achieve the same thing with the mapper:



What did we do?

First of all we need a list and the list will contain the list of expressions based on the column name and values:
Name_0='Value_0'
Name_1='Value_1'
Name_2='Value_2'

For this we use the new "Create List" functoid:

include the "ForEach Loop" functoid - in order to loop the "Column" records -:

use a "String Concatenate" in combination with the "Add Item to List" functoid to produce the above list:


Now that we have the list, how to produce the query statement? "Cumulative Concatenate" comes to the rescue:


Since the "Query" element in the destination schema is optional, I also included a "Conditional Assignment" functoid to only map the query in case we have columns on the input.
I also used the "Cumulative Count" functoid to count the number of items in the list and used the "Logical Expression" functoid to check if the value is greater than 0:


Based on that result we will create the "Query" element or not, but first we need to prefix the query with the "where" word using the "String Concatenate" functoid:


Simple!

The thing to remember here is to include a "ForEach" in the "Create List"...

Koen

Sunday, December 1, 2013

Installing Terracotta as a windows service



Since the release of webMethods 9.0, Software AG uses TerraCotta as distributed cache for their cluster solution on Integration Server. The software can be easily installed using the SoftwareAG installer, but has a big drawback on Microsoft Windows systems: there is no procedure to install Terracotta as a Windows Service. Therefore, the server needs to be started on command line with all its consequences.
Fortunately, there are several solutions available:
Tanuki java wrapper
Can be configured to work with Terracotta, but the community edition only supports 32-bit JVM. Due to its memory limitation, this is not a very good solution. You can find the software at http://wrapper.tanukisoftware.com/
In case you want to buy the software for 64-bit support, configuration can be done as follows:
  • ·        Download the wrapper and unzip in folder D:\SoftwareAG\Terracotta\wrapper 
  •       Make a wrapper file TerraCotta.conf with the following configuration and place it under D:\SoftwareAG\Terracotta. Suppose the Terracotta config file tc-config.xml can be found under D:\SoftwareAG\Terracotta\bin
# for logging
wrapper.java.command.loglevel=INFO
wrapper.java.command=D:\SoftwareAG\jvm\jvm170_32\bin\java
# Method 1 main class
wrapper.java.mainclass=org.tanukisoftware.wrapper.WrapperStartStopApp
# classpath
wrapper.java.classpath.1=D:\Temp\wrapper\lib/wrapper.jar
wrapper.java.classpath.2=D:/SoftwareAG/Terracotta/lib/tc.jar
wrapper.java.library.path.1=D:\Temp\wrapper/lib
# Java Additional Parameters
wrapper.java.additional.1=-server
wrapper.java.additional.2=-XX:+HeapDumpOnOutOfMemoryError
wrapper.java.additional.3=-Dcom.sun.management.jmxremote
wrapper.java.additional.4=-Dsun.rmi.dgc.server.gcInterval=31536000000
wrapper.java.additional.5=-Dnet.sf.ehcache.enableShutdownHook=false
wrapper.java.additional.6=-Dcom.tc.l2.db.factory.name=com.tc.objectserver.storage.derby.DerbyDBFactory
wrapper.java.additional.7=-Dtc.install-root=D:/SoftwareAG/Terracotta
# Initial Java Heap Size (in MB)
wrapper.java.initmemory=512
# Maximum Java Heap Size (in MB)
wrapper.java.maxmemory=512
# Application parameters. This is where you specify TC server main class
wrapper.app.parameter.1=com.tc.server.TCServerMain
wrapper.app.parameter.2=2
wrapper.app.parameter.3=-f
wrapper.app.parameter.4=D:\SoftwareAG\Terracotta\bin\tc-config.xml
wrapper.app.parameter.5=com.tc.admin.TCStop
wrapper.app.parameter.6=FALSE
wrapper.app.parameter.7=5
wrapper.app.parameter.8=-f
wrapper.app.parameter.9=D:\SoftwareAG\Terracotta\bin\tc-config.xml
wrapper.app.parameter.10=-n
wrapper.app.parameter.11=Server2
wrapper.app.parameter.12=9520
wrapper.logfile=D:\SoftwareAG\Terracotta/wrapper.log
# Format of output for the log file.  (See docs for formats)
wrapper.logfile.format=LPTM
# Log Level for log file output.  (See docs for log levels)
wrapper.logfile.loglevel=INFO
# On exit behaviour
wrapper.on_exit.0=SHUTDOWN
wrapper.on_exit.default=RESTART

  • ·        You can install the service by running the following command: sc create Terracotta binPath= D:\SoftwareAG\Terracotta\wrapper\wrapper.exe -s D:\SoftwareAG\Terracotta\TerraCotta.conf start= auto

Yet Another Java Service Wrapper (YAJSW)
YAJSW is a pure java implementation of the Tanuki Java Wrapper. Its big advantage is that it’s free and supports both 32 and 64-bit JDK 5, 6 and 7. You can find the software at http://yajsw.sourceforge.net/
Configuration can be done as follows:
  • ·        Download and unzip the wrapper under D:\SoftwareAG\Terracotta\wrapper 
  •       Make a wrapper.conf file with the following configuration and place it under D:\SoftwareAG\Terracotta\wrapper. Important to mention is to use / instead of \ !!
wrapper.java.command=D:/SoftwareAG/jvm/jvm170_64/bin/java
wrapper.stop.conf = D:/SoftwareAG/Terracotta/wrapper/wrapper.stop.conf
wrapper.java.classpath.1= D:/SoftwareAG/Terracotta/wrapper/wrapper.jar
wrapper.java.classpath.2=D:/SoftwareAG/Terracotta/lib/tc.jar
wrapper.java.app.mainclass = com.tc.server.TCServerMain
wrapper.console.title = TerraCotta
wrapper.ntservice.name = TerraCotta
wrapper.ntservice.displayname = TerraCotta
wrapper.ntservice.description = TerraCotta
wrapper.app.parameter.1= -f
wrapper.app.parameter.2= D:/SoftwareAG/Terracotta/bin/tc-config.xml
wrapper.logfile=D:/logs/wrapper.log
wrapper.logfile.format=LPTM
wrapper.logfile.rollmode=DATE
wrapper.logfile.maxdays=10
wrapper.java.additional.1=-server
wrapper.java.additional.2=-Xms1024M
wrapper.java.additional.3=-Xmx1024M
wrapper.java.additional.4=-XX:+HeapDumpOnOutOfMemoryError
wrapper.java.additional.5=-Dcom.sun.management.jmxremote
wrapper.java.additional.6=-Dsun.rmi.dgc.server.gcInterval=31536000000
wrapper.java.additional.7=-Dnet.sf.ehcache.enableShutdownHook=true
wrapper.java.additional.8=-Dcom.tc.l2.db.factory.name=com.tc.objectserver.storage.derby.DerbyDBFactory
wrapper.java.additional.9=-Dtc.install-root=D:/SoftwareAG/Terracotta
wrapper.java.additional.10=-Djava.net.preferIPv4Stack=true
wrapper.on_exit.0=SHUTDOWN
wrapper.on_exit.default=RESTART
  • ·        Also make a wrapper.stop.conf file in the same folder with the following configuration:
wrapper.stopper = true
wrapper.java.command=D:/SoftwareAG/jvm/jvm170_64/bin/java
wrapper.java.classpath.1= D:/SoftwareAG/Terracotta/wrapper/wrapper.jar
wrapper.java.classpath.2=D:/SoftwareAG/Terracotta/lib/tc.jar
wrapper.java.app.mainclass = com.tc.admin.TCStop
wrapper.app.parameter.1=-f
wrapper.app.parameter.2=D:/SoftwareAG/Terracotta/bin/tc-config.xml
wrapper.app.parameter.3=-n
wrapper.app.parameter.4=Server1
wrapper.app.parameter.5=9520
wrapper.logfile=D:/logs/wrapper_stop.log
wrapper.logfile.format=LPTM
wrapper.logfile.rollmode=DATE
wrapper.logfile.maxdays=10
wrapper.java.additional.1=-server
wrapper.java.additional.2=-Xms512M
wrapper.java.additional.3=-Xmx512M
wrapper.java.additional.4=-XX:+HeapDumpOnOutOfMemoryError
wrapper.java.additional.5=-Dcom.sun.management.jmxremote
wrapper.java.additional.6=-Dsun.rmi.dgc.server.gcInterval=31536000000
wrapper.java.additional.7=-Dnet.sf.ehcache.enableShutdownHook=true
wrapper.java.additional.8=Dcom.tc.l2.db.factory.name=com.tc.objectserver.storage.derby.DerbyDBactory
wrapper.java.additional.9=-Dtc.install-root=D:/SoftwareAG/Terracotta
wrapper.java.additional.10=-Djava.net.preferIPv4Stack=true

·        You can test the configuration by running D:\SoftwareAG\Terracotta\wrapper\bat\runConsole.bat
In that case the wrapper will load the configuration file and start the process. This can be used to debug the wrapper configuration file. Afterwards the service can be installed using the D:\SoftwareAG\Terracotta\wrapper\bat\installService.bat command

For both wrappers you can define the wrapper on exit behavior. By setting wrapper.on_exit.0=SHUTDOWN the wrapper will do nothing when it gets a normal return code 0 from terracotta. For all other return codes the wrapper will automatically restart the terracotta service. We do this by setting wrapper.on_exit.default=RESTART.
We need this option in an active-mirror terracotta setup. If we shut down the passive node and restart it, we will get the following error in the terracotta server log:
ERROR com.tc.l2.ha.ClusterState -
********************************** ERROR ***********************************
* This server is running with persistence turned on and was stopped in
* PASSIVE-STANDBY state. Only the ACTIVE-COORDINATOR server is allowed  to
* be restarted without cleaning up the data directory with persistence
* turned on.
*
* Please clean up the data directory and make sure that the
* ACTIVE-COORDINATOR is up and running before starting this server. It is
* important that the ACTIVE-COORDINATOR is up and running before starting
* this server else you might end up losing data
****************************************************************************
[WrapperStartStopAppMain] ERROR tc.operator.event - NODE : Server2  Subsystem: CLUSTER_TOPOLOGY Message: Started with dirty database. Exiting!! Restart enabled
[WrapperStartStopAppMain] ERROR com.terracottatech.dso - Marking the object db as dirty ...
[WrapperStartStopAppMain] ERROR com.terracottatech.console - This standby Terracotta server instance had to restart to automatically wipe its database and rejoin the cluster.

Terracotta will stop with a return code different than 0. By using the on exit options, the wrapper automatically restarts the service.