If you need to configure Zato for Oracle DB connections and you want to ensure the highest performance possible, this is the post which goes through the process step-by-step. Read on for details.

Overview

Note that Zato 3.1+ is required. The tasks involved are:

  • Installing Zato
  • Installing an Oracle client
  • Installing cx_Oracle
  • Greenifying the Oracle client
  • Starting servers
  • Creating database connection definitions
  • Confirming the installation

Installing Zato

  • Choose your preferred operating system and follow the general installation instructions - Oracle DB connections will work the same no matter the system Zato runs on

  • Create a Zato environment with as many servers as required. Again, there are no Oracle-specific steps at this point yet.

Installing an Oracle client

  • Download an Oracle client and install it on all the systems with Zato servers

  • Add the client's installation path to LD_LIBRARY_PATH. For instance, if the client is installed to /opt/zato/instantclient_19_3, add the following to ~/.bashrc:

  export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/zato/instantclient_19_3

Installing cx_Oracle

  • cx_Oracle is a Python driver for Oracle DB, it can be installed using pip, as user zato, on each Linux instance that Zato servers use:
  $ cd /opt/zato/current
  $ ./code/bin/pip install cx_Oracle

Greenifying the Oracle client

  • This step is crucial for achieving the highest performance of SQL queries

  • For each Zato server installed, open its server.conf file and find stanza [greenify]

  • If there is no such stanza in server.conf, create it yourself

  • Modify the stanza to greenify the libclntsh.so library - this is a library that the Oracle client ships with. For instance, if the client's installation path is /opt/zato/instantclient_19_3, the full path to the library is /opt/zato/instantclient_19_3/libclntsh.so

  • The stanza should read as below, assuming the installation path as above:

  [greenify]
  /opt/zato/instantclient_19_3/libclntsh.so=True
  • Note that entries in this stanza are followed by =True, which is a signal to Zato that a particular library should be processed

Starting servers

  • Start Zato servers
  • For each, make sure that an entry such as below is saved in server logs
  INFO - Greenified library `/opt/zato/instantclient_19_3/libclntsh.so.19.1`

Creating database connection definitions

  • Go to web-admin and create a new Oracle DB connection via Connections -> Outgoing -> SQL, as below:


Create a new SQL connection

  • Update the newly created connection's password by clicking Change password

  • Click Ping to confirm the connectivity to the remote server

  • This concludes the process, the connection is ready for use in Zato services now

This article introduces features built into Zato that let one take advantage of publish/subscribe topics and message queues in communication between Zato services, API clients and backend systems.

Overview

Let's start by recalling the basic means through which services can invoke each other.

  • Using self.invoke will invoke another service directly, in a blocking manner, with the calling service waiting for a response from the target one.
  self.invoke('my.service', 'my.request')
  • With self.invoke_async, the calling service will invoke another one in background, without waiting for a response from the one being called.
  self.invoke_async('my.service', 'my.request')

There are other variants too, like async patterns, and all of them work great but what they all have in common is that the entire communication between services takes place in RAM. In other words, as long as Zato servers are up and running, services will be able to communicate.

What happens, though, when a service invokes another one and the server the other one is running on is abruptly stopped? The answer is that, without publish/subscribe, a given request will be lost irrevocably - after all, it was in RAM only so there is no way for it to be available across server restarts.

Sometimes this is desirable and sometimes it is not - publish/subscribe topics and message queues focus on scenarios of the latter kind. Let's discuss publish/subscribe, then.

Introducing publish/subscribe


In its essence, publish/subscribe is about building message-based workflows and processes revolving around asynchronous communication.

Publishers send messages to topics, subscribers have queues for data from topics and Zato ensures that messages will be delivered to subscribers even if servers are not running or if subscribers are not currently available.

Publish/subscribe and Zato services

Publish/subscribe, as it pertains to Zato services, is an extension of the idea of message topics and queues. In this case, it is either internal or user-defined services that topics and queues are used by. Whereas previously, publishers and subscribers were external applications, here both of these roles are fulfilled by services running in Zato servers.

In the diagram above, an external API client invokes a channel service, for instance a REST one. Now, instead of using self.invoke or self.invoke_async the channel service will use self.pubsub.publish to publish the message received to a backend service which will in turn deliver it to external, backend systems.

The nice part of it is that, given how Zato publish/subscribe works, even if all servers are stopped and even if some of the recipients are not available, the message will be delivered eventually. That is the cornerstone of the design - if everything works smoothly, the message will be delivered immediately, but if anything goes wrong along the way, the message is retained and attempts are made periodically to deliver it to its destination.

Python code

As usual in Zato, the Python code needed is straightforward. Below, one service publishes a message to another - the programmer does not need to think about the inner details of publish/subscribe, about locations of servers, re-deliveries or guaranteed delivery. Merely using self.publish.publish suffices for everything to work out of the box.

# -*- coding: utf-8 -*-

from __future__ import absolute_import, division, print_function, unicode_literals

# Zato
from zato.server.service import Service

# ################################################################################

class MyService(Service):
    name = 'pub.sub.source.1'

    def handle(self):

        # What service to publish the message to
        target = 'pub.sub.target.1'

        # Data to invoke the service with, here, we are just taking as-is
        # what we were given on input.
        data = self.request.raw_request

        # An optional correlation ID to assign to the published message,
        # if givenm it can be anything as long as it is unique.
        cid = self.cid

        self.pubsub.publish(target, data=data, cid=cid)

        # Return the correlation ID to our caller
        self.response.payload = cid

# ################################################################################

class PubSubTarget(Service):
    name = 'pub.sub.target.1'

    def handle(self):

        # This is how the incoming message can be accessed
        msg = self.request.raw_request

        # Now, the message can be processed accordingly
        # The actual code is skipped here - it will depend
        # on one's particular needs.

# ################################################################################

Asynchronous message flows

The kind of message flows that publish/subscribe topics promote are called asynchronous because, seeing as in a general case it is not possible to guarantee that communication will be instantaneous, the initial callers (e.g. API clients) and possibly other participants too should only submit their requests without expectations that responses, if any, will appear immediately.

Consider a simple case of topping up a pay-as-you go mobile phone. Such a process will invariably require participation from at least several backend systems, all of which can be coordinated by Zato.

Let's say that the initial caller, the API client initiating the process, is a cell phone itself, sending a text message with a top-up code from a gift card.

Clearly, there is no need for the phone itself to actively wait for the response. With several backend systems involved, it may take anything between seconds to minutes before the card is actually recharged and there is no need to keep all of the systems involved, including the cell phone, occupied.

At the same time, in case some of the backend systems are down and the initial request is lost, we cannot expect that the end user will keep purchasing more cards - we need some kind of a guaranteed delivery mechanism, which is precisely where Zato topics are useful with their ability to retain messages in case immediate delivery is not possible.

With topics, if a response is needed, instead of waiting in a synchronous manner, API callers can be given a correlation ID (CID) on output when they submit a request. A CID is just a random piece of string, uniquely identifying the request.

In the Python code example, self.cid is used for the CID. It is convenient to use it because it already available for each service and Zato knows how to use it in other parts of the platform - for instance, if the request is via HTTP (REST or SOAP), the correlation ID will be saved in Apache-style HTTP access logs. This facilitates answering of typical support questions, such as 'What happened to this or that message, when was it processed or when was the response produced?'

We have a CID but why is it useful? It is because it may be used an ID to key messages of two kinds:

  • API callers may save it and then be notified later on by Zato-based services that such and such request, one with a particular CID, has been already processed

  • API callers may save it and then periodically query Zato-based services if a given request is already processed

Which style to use ultimately depends on the overall business and technical processes that Zato publish/subscribe and services support - sometimes it is more desirable to receive notifications yet sometimes it is not possible at all, e.g. if the recipients are rarely accessible, for instance, if they join networks irregularly.

Web-admin GUI

In parting words, it needs to be mentioned that a very convenient aspect of Zato services' being part of the bigger publish/subscribe mechanism is that web-admin GUI treats them just like any other endpoint and we can browse their topics, inspect last messages published, consult web-admin to check how many messages were published, or carry out other tasks that it is capable of, like in the screenshots below:

This is a quick tip on how to quickly and easily enable Bash completion for Zato commands - each time you press Tab when typing a Zato command, its arguments and parameters will be auto-completed.

Prerequisites

First off, note that when you install Zato from a .deb or .rpm package, it already ships with the Bash completion functionality and what is needed next is its activation as such.

Thus, there is only one prerequisite, core package needed. For example, this command installs core Bash completion in Ubuntu and Debian:

$ sudo apt-get install bash-completion

Enable Bash completion

Again, each operating system will have its own procedure to enable Bash completion.

For Ubuntu and Debian, edit file ~/.bashrc and add the commands below if they do not exist yet.

# Enable bash completion in interactive shells
if ! shopt -oq posix; then
  if [ -f /usr/share/bash-completion/bash_completion ]; then
    . /usr/share/bash-completion/bash_completion
  elif [ -f /etc/bash_completion ]; then
    . /etc/bash_completion
  fi
fi

Afterwards, Bash completion will be enabled in every future session and you will be able to use it in the zato command, e.g.:

$ zato st[tab] # This will suggest either 'zato start' or 'zato stop'
$ zato start /path/to/[tab] # This will suggest a file-system path to use

This post goes through the steps of exposing Windows commans and PowerShell scripts as remote Zato API services that can be invoked by REST clients.

This lets one access a fleet of Windows systems from a single place and makes it possible for Zato services to participate in Windows management processes.

Note that Zato servers always run on Linux and no installation of any kind of software under Windows is necessary for Zato to connect to remote systems.

Prerequisites

Start by installing a library that implements the remote Windows connectivity:

$ cd /opt/zato/current
$ ./bin/pip install pywinrm

Next, stop and start again any servers running, e.g.:

$ zato stop /path/to/server
$ zato start /path/to/server

Python code

Deploy the following service to your Zato cluster - note its name, windows.remote.management.

# -*- coding: utf-8 -*-

from __future__ import absolute_import, division, print_function, unicode_literals

# stdlib
from traceback import format_exc

# pywinrm
import winrm

# Zato
from zato.server.service import Service

class MyService(Service):
    name = 'windows.remote.management'

    class SimpleIO:
        input_required = 'type', 'data'
        output_required = 'exec_code'
        output_optional = 'status_code', 'stdout', 'stderr', 'details'
        response_elem = None
        skip_empty_keys = True

    def handle(self):

        # Local aliases
        input_type = self.request.input.type
        input_data = self.request.input.data

        # Validate input - we support either regular commands
        # or PowerShell scripts on input.
        if input_type not in ('cmd', 'ps'):
            self.response.payload.exec_code = 'error'
            self.response.payload.details = 'Invalid type'

        # Input was valid, we can try to execute the command now
        else:

            try:

                # Remote server details
                host = '10.151.139.17'

                # Credentials
                username = 'myuser'
                password = 'NZaIhMezvK00Y'

                # Establish a connection to the remote host
                session = winrm.Session(host, (username, password))

                # Dynamically select a function to run,
                # either for commands or PowerShell
                func = session.run_cmd if input_type == 'cmd' else session.run_ps

                # Run the function with input data given
                result = func(input_data)

                # Status code is always available
                self.response.payload.status_code = result.status_code
                self.response.payload.stdout = result.std_out
                self.response.payload.stderr = result.std_err

            except Exception:
                self.response.payload.exec_code = 'error'
                self.response.payload.details = format_exc()

            # Everything went fine
            else:
                self.response.payload.exec_code = 'ok'

REST channel

Create a new REST channel in web-admin and mount service windows.remote.management on it. Make sure to set data format to JSON.

Usage

Let us invoke the service from command line, using curl. For clarity, the output of commands below is limited to a few lines.

First, we will run a regular command to get a directory listing of drive C:

$ curl http://api:password@localhost:11223/windows -d '{"type":"cmd", "data":"dir c:"}'
{"status_code": 0,
 "exec_code": "ok",
 "stdout": " Volume in drive C has no label.\r\n
 Volume Serial Number is 1F76-3AB6\r\n\r\n
 Directory of C:\\Users\\Administrator\r\n\r\n07/22/2019  11:53 PM    <DIR>"}

What if we provide an invalid drive name?

$ curl http://api:password@localhost:11223/windows -d '{"type":"cmd", "data":"dir z:"}'
{"status_code": 1,
 "exec_code": "ok",
 "stderr": "The system cannot find the path specified.\r\n"}

Now, invoke a PowerShell script, which in this case is a single-line one to check connection from the remote Windows system to example.com, but it could be much more complex, there are no limitations:

$ curl http://api:password@localhost:11223/windows -d \
  '{"type":"ps", "data":"Test-Connection example.com"}'

This time, both stdout and stderr are returned but because the overall status_code is 0, we know that the invocation was successful.

{"status_code": 0,
 "exec_code": "ok",
  "stdout": "WIN-A3I92B... example.com     93.184.216.34",
  "stderr": "#< CLIXML\r\n<Objs Version=\"1.1.0.1\" ...",

In conclusion

The service is just a starting point and there are a couple ways to extend it:

  • Details of remote servers, including credentials, should be kept separately
  • Permissions, including ACLs, can be added to allow or disallow access to particular commands to selected REST users only

Yet, even in this simple form, it already shows how easy it is to connect to Windows servers and turn remote commands into REST APIs microservices.

On top of it, REST is but one of many formats that Zato supports - one could just as well design workflows around AMQP, ZeroMQ, IBM MQ, FTP or other protocols in addition to REST with no changes to Python code required.

This article will show you how to invoke MS SQL stored procedures from Zato services - a feature new in the just released version 3.1 of the Python-based integration platform.

In web-admin

Start off by installing the latest updates.

Next, the first thing needed is creation of a new outgoing SQL connection - make sure to choose the MS SQL (Direct) type, as below.

It is considered a direct one because, even though it is based on SQLAlchemy, it does not make use of the most of SQLAlchemy's functionality and lets one invoke stored procedures alone, i.e. it is not possible to use this type of connections with ORM or anything else - only stored procedures are supported.

Make sure to change the password after creating a connection - the default one is a randomly generated string.

Python code

In most cases, to invoke a stored procedure, use the code below:

# -*- coding: utf-8 -*-


# Zato
from zato.server.service import Service

class MyService(Service):
    def handle(self):

        # Connection to use
        name = 'My MS SQL Connection'

        conn = self.outgoing.sql.get(name)
        session = conn.session()

        # Procedure to invoke
        proc_name = 'get_current_user'

        # Arguments it has on input
        args = ['my.user.id']

        data = session.callproc(proc_name, args)

        # Data is a list of dictionaries, each of which
        # represents a single row of data returned by the procedure.
        for row in data:
            ...

Lazy evaluation

The usage example above will work in many cases but, supposing a procedure returns many thousands of rows, it may not be efficient to read them in all in a single call.

This would potentially create a big list of row elements - if all them are indeed required in a single place then this is not a concern. But if they should be processed one by one then it may be better to explicitly fetch and process a single row at a time.

To achieve it, use_yield=True can be applied, as in the code below. Now, each iteration of the for loop will return a new row, without ever accumulating all of them in RAM.

# -*- coding: utf-8 -*-


# Zato
from zato.server.service import Service

class MyService(Service):
    def handle(self):

        # Connection to use
        name = 'My MS SQL Connection'

        conn = self.outgoing.sql.get(name)
        session = conn.session()

        # Procedure to invoke
        proc_name = 'get_current_user'

        # Arguments it has on input
        args = ['my.user.id']

        data = session.callproc(proc_name, args, use_yield=True)

        # Data is a Python generator now and each iteration
        # of the loop returns a new row from the stored procedure.
        for row in data:
            ...

Wrapping up

Ability to use MS SQL is a feature new in Zato 3.1 - it works in a way similar to other SQL connection types with the notable exception that only stored procedures can be invoked from Python code.

There are two ways to invoke stored procedures - either by reading the whole output into a service or processing rows one by one. The latter is recommended if a large number of rows is to be processed by the service.