Creating and managing connections, AI objects, and models with SQL DI REST API

After successful installation and configuration, connect your SQL Data Insights (SQL DI) application to your Db2 system or data sharing group, create AI objects, enable the objects for AI query, and if needed, retrain and deploy object models. You can create and manage the connections, objects, and object models by using the SQL DI REST API.

Procedure

In your REST API client, start a new HTTPS request in JSON format.

See https://<SQLDI-IPAddress>:<SQLDI-PortNumber>/swagger or Db2 SQL Data Insights REST APIs for a full list of SQL DI REST API endpoints, options, descriptions, and examples.
If you have not done so, generate a bearer authentication token.

Specify the tokenTTL option so that you can use the token for the current request before it expires. See Generating an authentication token with SQL DI REST API for instructions.
Optionally, encrypt your Db2 password.
See Encrypting your Db2 password with SQL DI REST API for instructions.
Create, modify, or delete a connection or list all the connections to the same Db2 server.
- To create a connection, create a new HTTPS POST request in JSON format:
  1. In the request header, specify the Authorization and Encrypted parameters.
  2. In the request body, include the following options for the new connections:
```
{
  "description": "",
  "hostname": "hostname or IP address",
  "location": "string",
  "name": "string",
  "port": "port",
  "userName": "string",
  "password": "string"
}
```
    Set the password option to the encrypted password string as described in Step 3.
  3. Send the new POST request to the following address:
```
POST https://<SQLDI-IPAddress>:<SQLDI-PortNumber>/sqldi/v2/connections
```
    If your request is successful, a new connection is created. The response will include the unique conID for the new connection like the following example:
```
4be52dfd-e561-48d9-a470-3abc824ea67b
```
- To modify a connection, create a new HTTPS PATCH request in JSON format:
  1. In the request header, specify the Authorization and Encrypted parameters.
  2. In the request body, include the following options with the required information for the connection that you want to modify:
```
{
  "description": "",
  "hostname": "hostname or IP address",
  "location": "string",
  "name": "string",
  "port": "port",
  "userName": "string",
  "password": "string"
}
```
    Set the password option to the encrypted password string as described in Step 3.
  3. Send the new PATCH request to the following address that includes the unique conID for the connection:
```
PATCH https://<SQLDI-IPAddress>:<SQLDI-PortNumber>/sqldi/v2/connections/{connId}
```
    If your request is successful, you will receive a response like the following example:
```
Connection successfully edited.
```
- To remove a connection, create a new HTTPS DELETE request in JSON format:
  1. In the request header, specify the Authorization and Encrypted parameters.
  2. Send the new DELETE request to the following address that includes the unique conID for the connection that you want to delete:
```
DELETE https://<SQLDI-IPAddress>:<SQLDI-PortNumber>/sqldi/v2/connections/{connId}
```
    If your request is successful, you will receive a response like the following example:
```
Connection successfully removed.
```
- To retrieve a list of all the connections to the same Db2 server, create a new HTTPS GET request in JSON format:
  1. In the request header, specify the Authorization parameter.
  2. Send the GET request to the following address:
```
GET https://<SQLDI-IPAddress>:<SQLDI-PortNumber>/sqldi/v2/connections
```
    If your request is successful, you will receive a response with the details about each connection that are similar to the following example:
```
[
  {
    "createdBy": "USRT001",
    "createdDate": 1714594842225,
    "description": "",
    "hostname": "hostname",
    "id": "4be52dfd-e561-48d9-a470-3abc824ea67b",
    "isConnected": false,
    "lastUpdatedBy": "USRT001",
    "lastUpdatedDate": 1714594842225,
    "location": "DB2A",
    "jdbcProperties": "string",
    "db2SpecialRegisters": "string",
    "name": "DB2A_connection",
    "port": "portNumber",
    "sslConnection": false,
    "sslCertificate": "string",
    "type": "Db2 v13"
  }
]
```
Create an AI object and enable it for AI query, delete an object, or view the column configuration of an object.
- To create an AI object and enable it for AI query, create a new HTTPS POST request in JSON format:
  1. In the request header, specify the Authorization, Db2-Username, Db2-Password, and Encrypted parameters.
  2. In the request body, include the following options with the required information for the new AI object that you want to create:
```
{
  "configuration": {
    "name": "string",
    "keepIdentifierKey": "N",
    "neglectValues": "",
    "columnsConfig": [
      {
        "neglectValues": "",
        "aiSqlType": "C",
        "length": "integer",
        "name": "string",
        "sqlType": "VARCHAR"
      }
    ]
  },
  "schema": "string",
  "table": "string"
}
```
  3. Send the POST request to the following address that includes the unique conID for the connection on which the new AI object will be created:
```
POST https://<SQLDI-IPAddress>:<SQLDI-PortNumber>/sqldi/v2/connections/{connId}/objects
```
    If your request is successful, you will receive a response like the following example:
```
AI object created successfully.
```
    Upon successful creation, the REST API call automatically starts the process of enabling the object for AI query.
- To remove an AI object, create a new HTTPS DELETE request in JSON format:
  1. In the request header, specify the Authorization, Db2-Username, Db2-Password, and Encrypted parameters.
  2. Send the DELETE request to the following address that includes the object ID and the conID for the connection on which the object exists:
```
DELETE https://<SQLDI-IPAddress>:<SQLDI-PortNumber>/sqldi/v2/connections/{connId}/objects/{object}
```
    The request cancels any active model training job for the object and then deletes the object itself. If your request is successful, you will receive a response like the following example:
```
AI object removed successfully.
```
- To retrieve a list of all AI objects for a specific connection or the details of a specific object, create a new HTTPS GET request in JSON format:
  1. In the request header, specify the Authorization, Db2-Username, Db2-Password, and Encrypted parameters.
  2. Send the GET request to the following address that includes the conID for the connection on which all the objects exist:
```
GET https://<SQLDI-IPAddress>:<SQLDI-PortNumber>/sqldi/v2/connections/{connId}/objects
```
    If your request is successful, you will receive a response with the details for each object like the following example:
```
[
  {
    "description": "",
    "id": 295,
    "modelId": 315,
    "modelLastUpdatedDate": 1718741583568,
    "modelStatus": "Available",
    "name": "CHURN_PRIMARY_KEY",
    "objectCreatedDate": 1717715860106,
    "objectLastUpdatedDate": 1718741583572,
    "objectStatus": "ENABLED",
    "objectType": "T",
    "retrainEligible": true,
    "retrainSection": {
      "id": 69,
      "lastUpdatedDate": 1714687008749,
      "messages": "{\"messages\":\"The retrained model is removed.\",\"sparkSubmitId\":\"driver-20240502145447-0009\"}",
      "retrainStatus": "STOPPED"
    },
    "schema": "ADMF001",
    "trainingJobId": 315,
    "trainingLastUpdatedDate": 1718741583572,
    "trainingMessage": "{\"messages\":\"Model training is complete\",\"resumeInfo\":{\"isPrefetchSupported\":true,\"isRetrainSupported\":true,\"phase\":\"completed\",\"trainingFileDirectory\":\"/sqldi/sqldi-home/temp/training/DSNAIDB_AIDB_ADMF001_CHURN_PRIMARY_KEY_1718741489705\"},\"sparkSubmitId\":\"driver-20240618131125-0000\"}",
    "trainingProgress": 100,
    "trainingStatus": "Completed"
  }
]
```
    If you include the object parameter in the request, the response will return the details of the specified object only.
- To retrieve the details of the column configuration for an AI object, create a new HTTPS GET request in JSON format:
  1. In the request header, specify the Authorization, Db2-Username, Db2-Password, and Encrypted parameters.
  2. Send the GET request to the following address that includes the connection conID and the object for which you want to fetch the column configuration:
```
GET https://<SQLDI-IPAddress>:<SQLDI-PortNumber>/sqldi/v2/connections/{connId}/objects/{object}/config
```
    If your request is successful, you will receive a response with the column configuration details for the specified object like the following example:
```
[
  {
  "columnsConfig": [
    {
      "aiSqlType": "C",
      "id": 1,
      "isSelected": true,
      "length": 20,
      "name": "CONTRACT",
      "neglectValues": "",
      "priority": "H",
      "scale": 0,
      "sqlType": "VARCHAR"
    },
    {
      "aiSqlType": "C",
      "id": 2,
      "isSelected": true,
      "length": 10,
      "name": "DEPENDENTS",
      "neglectValues": "",
      "priority": "H",
      "scale": 0,
      "sqlType": "VARCHAR"
    }
]
```
Optionally, retrain an object model, deploy a retrained model, or remove a retrained model:
- To retrain the model of an AI object, create a new HTTPS POST request in JSON format.
  1. In the request header, specify the Authorization, Db2-Username, Db2-Password, and Encrypted parameters.
  2. Send the POST request to the following address that includes the object ID and the conID for the connection on which the object exists:
```
POST https://<SQLDI-IPAddress>:<SQLDI-PortNumber>/sqldi/v2/connections/{connId}/objects/{object}/retraining
```
    If your request is successful, you will receive a response like the following example:
```
Model retraining is successfully started.
```
- To deploy a retrained model, create a new HTTPS POST request in JSON format:
  1. In the request header, specify the Authorization, Db2-Username, Db2-Password, and Encrypted parameters.
  2. Send the POST request to the following address that includes the object ID and the conID for the connection on which the object exists:
```
POST https://<SQLDI-IPAddress>:<SQLDI-PortNumber>/sqldi/v2/connections/{connId}/objects/{object}/retraining/deployment
```
    If your request is successful, you will receive a response like the following example:
```
The retrained model for object <name> is successfully deployed.
```
- To delete a retrained model, create a new HTTPS DELETE request in JSON format:
  1. In the request header, specify the Authorization, Db2-Username, Db2-Password, and Encrypted parameters.
  2. Send the DELETE request to the following address that includes the object ID and the conID for the connection on which the object exists:
```
DELETE https://<SQLDI-IPAddress>:<SQLDI-PortNumber>/sqldi/v2/connections/{connId}/objects/{object}/retraining
```
    If your request is successful, you will receive a response like the following example:
```
The retrained model is successfully deleted.
```