Digital Developer Conference: Hybrid Cloud 2021. On Sep 21, gain free hybrid cloud skills from experts and partners. Register now

Archived | Harness the power of IBM Watson Language Translator on IBM i

Archived content

Archive date: 2021-03-30

This content is no longer being updated or maintained. The content is provided “as is.” Given the rapid evolution of technology, some content, steps, or illustrations may have changed.

We are now living in a cognitive era, where cognitive capabilities can easily be added to your applications. An easy way to add cognitive capabilities is utilize the capabilities of IBM® Watson® as provided by the IBM Cloud® platform. These capabilities are described by a number of IBM Redbooks® (see IBM Watson Redbooks link in the resources on the right). This article describes how to use one of the services provided by IBM Watson—the IBM Watson Language Translator service.

The article begins by illustrating the creation of an IBM Watson Language Translator service and describes how to obtain the credentials needed to access the service. It then shows how to access the various capabilities of the Language Translator service using SQL statements issued in IBM DB2® for i. Finally, it illustrates the use of a Java™ program to access those same capabilities.

While this article specifically uses the Language Translator service, the concepts presented here are easily adapted to access the other IBM Watson services.

Creating an IBM Watson Language Translator service

Creating a new service on IBM Cloud is relatively simple. This section illustrates how to create a new Language Translator service and obtain the credentials needed to access the service.

  1. The first step in creating an IBM service is to access the services menu from IBM Cloud. After you log into IBM Cloud at, you will see the screen shown in Figure 1. Click Services at the left side of the screen.
    Figure 1. IBM Cloud services menu
  2. Then, click Watson as shown in Figure 2.
    Figure 2. Selecting Watson services
  3. Click Create Watson service as shown in Figure 3.
    Figure 3. Create Watson service button
    alt You can then select from several services as shown in Figure 4. While a variety of different services can be used, this article describes the use of the Language Translator service.
    Figure 4. Watson services
  4. Click the Language Translator service. On the Language Translator service page that is displayed (as shown in Figure 5) you can designate the name by which your service and corresponding credentials are known in your IBM Cloud environment. In this case, the default generated names of Language Translator-x6 and Credentials-1 were used.
    Figure 5. Language Translator service page
  5. Scroll down the page and view the various pricing options for this service, as shown in Figure 6. Select the pricing option you need to use, and click Create to create the service.
    Figure 6. Language Translator pricing options
  6. After you have created the service, it is available for use. On the main IBM Cloud dashboard, find your new service and select it. Information about your service will then be displayed, as shown in Figure 7.
  7. In order to use the service, you need to obtain the credentials for the service. Click Service credentials at the left side of the screen.
    Figure 7. Obtaining service credentials
  8. You can see your credentials, as shown in Figure 8. When using the Language Translator service, you must specify the URL, the user name, and the password. In this example, the URL is, the user name is 4ce92bfb-3d82-4425-96d3-20b43f3689ef, and the password is 6DpcOu83xX0K. These values are used to access the translator service and will be the values used in the remainder of this article.
    Figure 8. Service credentials
  9. To learn about using the application programming interface (API) provided by the service, return to the Language Translator service page. At the right side of the page, click Documentation under Developer resources. Scroll down to the Quick links section (as shown in Figure 9) to find a link to a working demo, a link to the list of software development kits (SDKs), as well as a link to the API Explorer.
    Figure 9. Language Translator service documentation
    alt If you click API Explorer, you will see the Watson API Explorer as shown in Figure 10. This page shows the different APIs that can be accessed. You can use this page to experiment using the different APIs. The models__APIs show the language models that can be used by the service. The translate__APIs are used to perform language translation, and the identify__APIs are used to identify the language used by text. The APIs can be accessed using the GET or POST HTTP methods. This article uses the GET HTTP method and illustrates how several of these APIs can be accessed from IBM i.
    Figure 10. Watson API Explorer

Accessing the Watson Language Translator service from IBM i

DB2 for i includes a number of HTTP functions that are useful for accessing web services. This article accesses the Watson Language Translator service as a web service using the HTTP functions.

Before using the HTTP functions, you must verify whether they are supported by your IBM i system. You must be using version 7.1 or later to use the HTTP functions. When using the HTTP functions, your job CCSID of the database job must be set to something other than 65535. Your default Java must not be set to a version lower than 1.6. Finally, the system must have access to the internet and must be able to connect to Watson at

The HTTP functions are used to access servers using Representational State Transfer (REST). In its simplest form, REST involves a client sending an HTTP request to a web server. The web server uses the information in the request to generate a reply and the reply is returned, as text, to the client.

The first step in accessing the Watson Language Translator service is to create the base URL that will be used to access the service. This base URL contains the credential information necessary to access the service. For easy demonstration purposes, this URL will be stored in an SQL global variable. Listing 1 shows the SQL statements used to create the BASEURL global variable and set it to the correct value. As show in this listing, the base URL consists of “https://“, followed by the user name from the credentials, followed by a colon “:”, followed by the password from the credentials, followed by “@”, and finally followed by the rest of the URL specified in the credentials. In summary, the formula for constructing the URL is “https://<username>:<password>@<url>“.

Listing 1. Creating a global variable with credential information


        SET BASEURL='https://' CONCAT
                   '4ce92bfb-3d82-4425-96d3-20b43f3689ef:' CONCAT
                   '6DpcOu83xX0K@' CONCAT

After the base URL is defined, it can be used in subsequent queries that access the service. One capability provided by the service is to view the languages supported by the Language Translator service. You can access the Language Translator service using the SYSTOOLS.HTTPGETCLOB function within an SQL VALUES statement as shown in Listing 2. The SYSTOOLS.HTTPGETCLOB function uses a URL to retrieve information from a server on the web, which it returns as a character value. In this case, the parameter passed to SYSTOOLS.HTTPGETCLOB is created by combining the base URL with /v2/identifiable_languages. This returns the list of identifiable languages in the JSON format, as also shown in Listing 2. These are the languages that the Language Translator service can identify.

Listing 2. Calling the service to get identifiable languages


        -- This returns
              }, ...

If you are using versions 7.2 or 7.3 of IBM i with a recent database fix pack, you can use the JSON_TABLE function to generate a relational table from the JSON returned from this API. Listing 3 shows the SQL statement used to format the returned JSON data using JSON_TABLE and place the result in a new database table. The result of querying this table is shown in Figure 11.

Listing 3. JSON_TABLE query to create table containing languages

              '$.languages[]' COLUMNS(
                    "language" VARCHAR(80),
                    "name" VARCHAR(80)
              ))x) WITH DATA;

Figure 11. Languages supported by the translator service

Another important API is the one that lists the translation models supported by the Language Translator service. A translation model is used to translate text from one language to another. The Language Translator service can only translate text from one language to another if a model for the translation exists. As shown back in the Watson API Explorer in Figure 10, this API is the models API. We again use the HTTPCLOB function to access the API. Just like we did for the identifiable_languages API, JSON_TABLE is used to create a table containing the supported translations. Listing 4 shows the SQL statement used to create a table containing the supported translations. Refer to Figure 12 to see the contents of this table.

Listing 4. JSON_TABLE query for supported translations

           '$.models[]' COLUMNS(
                  "model_id" VARCHAR(80),
                  "source" VARCHAR(80),
                  "target" VARCHAR(80)
            ))x) with data;

Figure 12. Models

In the examples that have been presented, the translator APIs have been used to determine the capabilities of the Language Translator service. The most exciting capability of the service is actually translating from one language to another. The API to do the translation is translate. With this API, we need to pass two named parameters in the URL. The first parameter is named model_id and specifies the model to use for the translation. The second parameter is named “text and specifies the text to be translated. These parameters are combined with the BASEURL and API name as shown in Listing 5. In this case, we use the model_id, “en-es“, which means to translate from English to Spanish. The text we are translating is the word “Hello”. The result of this translation is the word “Hola”.

Listing 5. Translating a wordfrom English to Spanish

              '/v2/translate?' ||
              'model_id=en-es&' ||
        -- This results in the following answer

Because the API passes parameters using a URL, spaces in phrases cannot be directly passed to the API. Instead, spaces must be replaced with their HTML equivalent, which is “%20”. Using SQL, this replacement is easily accomplished using the REPLACE function. Listing 6 shows an example of translating a phrase from English to Spanish. In this example, we create two global variables, MODEL and SOURCETEXT to hold the model and the text to be translated. The MODEL variable is set to en-es to translate from English to Spanish. The SOURCETEXT variable is set to the text we wish to translate. Because this text includes spaces, the REPLACE function is used to replace all spaces with %20. We then use the VALUES statement and SYSTOOLS.HTTPGETCLOB function to obtain the result from the service. In this case, the result is “Hola, mi nombre es John”.

Listing 6. Translating a phrase from English to Spanish


        SET MODEL='en-es';
        SET SOURCETEXT='Hello, my name is John.';

        VALUES(SYSTOOLS.HTTPGETCLOB(BASEURL||'/v2/translate?' ||
                                    'model_id='|| MODEL||

        -- This results in the following answer
        Hola, mi nombre es John.

Suppose you want to translate a phrase from English using all supported translations. This can easily be done using an SQL query in conjunction with the web service and the information we already retrieved. Listing 7 shows the SQL statements to accomplish this. First, we create a table containing only the English models using the information from the MODEL and LANGUAGES tables we previously created. We then use the model ID from this table as the input to the SYSTOOLS.HTTPGETCLOB function. Figure 13 shows the result of this query.

Listing 7. Translating a phrase from English to multiple languages

           SELECT "model_id", "source", "target", "name"
           where "source"='en' and "target"="language"
        ) WITH DATA;

        SET SOURCETEXT='Hello, I can speak many languages.';

        SELECT "model_id", SYSTOOLS.HTTPGETCLOB(BASEURL||'/v2/translate?' ||
                                    'model_id='|| "model_id"||
                 FROM ENGLISH_MODELS;

Figure 13. Translation from English to other languages

Another service provided by Watson Language Translator is the ability to recognize a language. In this case, the API to use is identify. This API accepts a single parameter, named text, which is the text to be analyzed. As shown in Listing 8, the VALUES statement is used in conjunction with the HTTPGETCLOB function to invoke the service. The URL passed to HTTPGETCLOB contains the name of the API, identify, as well as the text, on which the REPLACE function has been used to replace spaces with their HTML equivalent. The result of this query is es, which means Spanish.

Listing 8. Identifying the language

        SET SOURCETEXT='Hola, mi nombre es John.';


        -- This returns

Using Java to access the Language Translator service

Watson also provides a Java API that can be used to access the service. Unfortunately, utilizing this API is not trivial and requires downloading about eight requisite JAR files. Another approach is to just use the same Java code used by the HTTPGETCLOB function. The Java method used by the HTTPGETCLOB method is the httpGetClob method of the DB2UDFWrapper class. Knowing this, we can construct a simple Java program that is shown in Listing 9. In this program, we use static variables to contain the user ID and password information. This information is used to construct the BASEURL value, similar to how the BASEURL SQL variable was constructed. The Java program provides a translate method which accepts a model and a text parameter. The model parameter is passed to the translator API without modification, while the text parameter is modified to replace any spaces by using the Java String``replaceAll function. The DB2UDFWrapper.httpGetClob method is then called to access the translator service. The result is a Java Clob object from which we retrieve the string value by calling the getSubString method.

Listing 9. Java program

public class Translate {

    public static String USERID="4ce92bfb-3d82-4425-96d3-20b43f3689ef";
    public static String PASSWORD="6DpcOu83xX0K";
    public static String BASEURL= "https://"+USERID+":"+PASSWORD+

    public static String translate(String model, String text) throws Exception {

      String escapedText = text.replaceAll(" ","%20");
      String url = BASEURL+"/v2/translate?model_id="
      java.sql.Clob clob = DB2UDFWrapper.httpGetClob(url,null);

      return clob.getSubString(1,(int)clob.length());

   public static void main(String[] args) {
        try {
     } catch (Exception e) {


To utilize this program, I created the file,, which contains this program. I copied this file to the /QIBM/UserData/OS400/SQLLIB/function directory on IBM i. I then started QSHELL using STRQSH. I changed my current directory using the command, cd /QIBM/UserData/OS400/SQLLIB/function_,_and then compiled the program using the command, javac -cp .:jar/SYSTOOLS/DB2RESTUDF.jar At this point, the program can then be run as shown in Figure 14.

Figure 14. Compiling and running the Java program

This program was structured so that it could also be used as a Java user-defined function (UDF). Listing 10 shows how this can be done. First, a Java UDF is created using the CREATE FUNCTION statement. The function has two parameters, model and test, and produces VARCHAR results. The language and parameter style used for the UDF is JAVA and the EXTERNAL NAME is set to Translate.translate, which is the Java method called when the UDF is invoked. What’s nice about this approach is all the URL information is now hidden from the SQL user. All the SQL user has to do is call the JAVA_TRANSLATE function.

Listing 10. Java UDF

           RETURNS VARCHAR(32000)

        VALUES(JAVA_TRANSLATE('en-es','My life is good.'));

Other programming languages

The capabilities of Watson can be accessed from other languages and environments. Watson also provides client-side libraries for use with Node.js and Python. Paul Tuohy has a nice article, RPG Talks to Watson, which is included in the resources on the right. That article also uses the HTTP functions to access Watson services.


Using IBM Cloud and APIs available on IBM i, you can utilize the IBM Watson Language Translator service to translate text from applications running on IBM i. This article has described how to create an instance of the IBM Watson Language Translator service and then obtain the necessary credentials. The article has shown how to access various aspects of the translator service using SQL statements on DB2 for i. It has also shown how to create a simple Java program to translate text using the IBM Watson Language Translator service. Using this information, an application developer can easily incorporate the cognitive power of IBM Watson to translate text from one language to another in their IBM i applications.