Defining a REST SQL service

Define a REST SQL service by specifying its name, version, and the SQL statement that it is to issue. A service can be associated with a SELECT, INSERT, UPDATE, or DELETE statement, and can include parameters.

Note: This topic illustrates how to define a REST SQL service using Python. You can follow a similar procedure to define services using other programming languages such as Curl, Perl, or JavaScript.

For example, you might want to define a new service that returns the contents of the employee table.

Before you define a service, you must first set up the REST server metadata, either by activating and initialing REST service capability, or by issuing the following REST call. If the schema property is omitted, the REST metadata is created in the schema DB2REST.

def setup_metadata(schema):
  url = "https://%s:50050/v1/metadata/setup" % (restHostname)

  header = {
    "content-type": "application/json",
    "authorization": token
  }
  body = {
    "schema": schema
  }
  response = requests.post(url, headers = headers, json = body)
    pprint(response.json())

The following call defines a REST service with the name listEmployees that returns the contents of the table with the name DEMO.EMPLOYEE.

def create_listemployees_service():
    url = "https://%s:50050/v1/services" % (restHostname)
    headers = {
        "content-type": "application/json",
        "authorization": token
    }
    json = {
        "serviceName": "listemployees",
        "serviceDescription": "Lists the employees",
        "sqlStatement": "SELECT EMPID, FIRSTNAME, LASTNAME FROM DEMO.EMPLOYEE ORDER BY LASTNAME, FIRSTNAME",
        "version": "1.0",
        "isQuery": True,
    }

    response = requests.post(url, verify = False, headers = headers, proxies = None, json = json)
    if response.status_code == 201:
        print("Success.")
    else:
        pprint(response.json())

After the service is defined, a client can invoke it as described in Creating applications that use REST services.