Managing Data Gate tables

This REST API comprises the entire table functionality for Data Gate. It gives you access to the functions that add, remove, load, or synchronize tables in a Data Gate instance.

JSON keys in request or response objects

The following JSON keys can be specified as request parameters, or they are returned in response objects after an API call. The list gives you a brief description of each key:

"archive_state":

Whether a table has been archived or not.

"data_distribution_skew":

A value that shows an imbalance in the workload distribution across worker nodes. The value is the difference between the smallest and the largest portion of the table data in megabytes (MB). A value of 0 indicates an even distribution. The higher the value, the more uneven is the distribution.

"data_integrity":

A status value that indicates whether the target table in the Data Gate instance deviates structurally from its original in the source. Values can be:

unimpaired
violatedOnAccelerator
violatedInDatabaseManagementSystem

"data_organization_percent":

The percentage of the table data that is organized based on the specified organizing keys. For organized tables, this value is typically 100, and 0 for tables that are not organized.

"disk_space_mb":

The available disk space in MB.

"error":

The title of an error message. It usually indicates the type of the error.

"federated_source":

The name of a Db2 subsystem or data sharing group for which federated access has been enabled.

"has_archive_problems":

A numeric counter that indicates how many problems did occur during table archiving.

"informational_unique_key":

A value consisting of one or more table column names to serve as a unique key. You must specify such a key if a primary key or primary index does not exist in the table that you want to use.

"is_acceleration_enabled":

Whether or not a table has been enabled for accelerated queries.

"is_archived":

Whether or not a table has been archived by the High Performance Storage Saver.

"is_replication_enabled":

Whether or not a table has been enabled for synchronization.

"last_loaded_at":

The last time and date when a table was loaded.

"message":

A descriptive message that is returned in addition to an error code.

"message_id":

The numeric identifier of a "message":.

"page":

The number of a result page.

"page_count":

The number of result pages to be returned.

"page_size":

The number of entries on a result page.

"pagination":

A group header that introduces "page_count": and "page_size": specifications.

"physical_layout":

A group header that introduces a set of grouped key/value pairs dealing with the structure of a table. One of these key/value pairs is "informational_unique_key":

"replication_capture_point":

A point in time in the Db2 for z/OS log that marks the start of a synchronization workload.

"replication_state":

One of the various states that the synchronization functions can be in.

"row_count":

The number of rows in a table.

"schema":

The name of a table schema in responses.

"schema_name":

The name of a table schema in a request.

"schemas":

A key that introduces a set of table schemas.

"status":

The status of a table. For example a status of "initial_load_pending" indicates that a table has just been added, but not yet loaded.

"status_code":

The status code returned by an API response, such as 200, 204, 400, 404, or 500.

"statusCode":

The status code that is returned in an error message.

"table":

The name of a table in responses.

"table_name":

The name of a table in requests.

"table_size_kb":

The size of a table in KB.

"tables":

A key that introduces a set of tables.

"total_items":

The total number of result entries (across all pages) in a response.

"total_pages":

The total number of pages in a response.

"type":

The type of the table. The value "regular" indicates a regular instance table. A value of "federatedReference" indicates a reference table that has been created for federated access.

For more information on the meanings of these keys or values, see the description of the second result set of the SYSPROC.ACCEL_GET_TABLES_INFO stored procedure.

Endpoints

This section lists and describes the available endpoints.

Important:

The colored texts like GET, POST, PUT, or DELETE in front of the endpoint strings are not part of the URL. They just indicate the type of the request. To submit a call, just add the part that starts with a slash to the URL stem, which is determined by your REST front-end, but should start with https://service-ip:https-port/ where:

service-ip

Is the IP address of the server that hosts the REST front-end or interface.

https-port

Is the port that the front-end or interface uses to listen to requests.
The JSON parameters (mostly key-value pairs) reoccur frequently in the various response objects. Their meaning is therefore described in an alphabetical list rather than separately for each API call.
Values of -1 in a response indicate the absence of suitable data or statistics. The reasons for that might be:
- The RUNSTATS utility has not been run on the affected tables yet. This is often the case if tables are new.
- The statistics are outdated or were invalidated by DDL changes.
- Some system or catalog tables do not maintain the requested statistics.

The API provides the following endpoints:

GET /v1/instance/tables

Returns the names of all the tables in a Data Gate instance. The response object in JSON format includes table details.

Success code: 200

Response:

See the following extract:

{
  "tables": [
    {
      "schema": "ADB",
      "table_name": "ADBCHG",
      "status": "initial_load_pending",
      "type": "regular",
      "data_integrity": "unimpaired",
      "replication_state": null,
      "archive_state": "not_archived",
      "is_acceleration_enabled": false,
      "is_replication_enabled": false,
      "is_archived": false,
      "has_archive_problems": null,
      "disk_space_mb": null,
      "row_count": null,
      "data_distribution_skew": null,
      "data_organization_percent": 0,
      "last_loaded_at": null,
      "replication_capture_point": null,
      "federated_source": null,
      "physical_layout": {
        "informational_unique_key": null
      }
    },
    {
      "schema": "ADB",
      "table_name": "ADBHOLD",
      "status": "initial_load_pending",
      "type": "regular",
      "data_integrity": "unimpaired",
       .
       .
       .

Failure response:

One of the following JSON objects is returned in case of a failure:

Internal server error:

{
  "message_id": "datagate.00001E",
  "message": "The CSPM stored procedure couldn't be executed. Check if the stored procedure exists and whether your credentials have permission to run the stored procedure.",
  "status_code": 500
}

Service temporarily unavailable:

{
  "message_id": "datagate.00500E",
  "message": "An unexpected internal server error occurred. This is likely a temporary issue. Try your request again.",
  "status_code": 500
}

POST /v1/instance/tables

Adds tables to a Data Gate instance.

Request:

Provide a request object in JSON format similar to the following. Replace the "schema_name": and "table_name": values as needed.

{
  "tables": [
    {
      "schema_name": "ADB",
      "table_name": "ADBCHG"
    },
    {
      "schema_name": "ADB",
      "table_name": "ADBHOLD"
    }
  ]
}

Success code: 204

Response:

No response object is returned if the request was successful.

Failure response:

One of the following JSON objects is returned in case of a failure:

Schema name not specified or empty string:

{
  "error": "Invalid request",
  "message": "schemaName: Schema name cannot be blank, 
   schemaName: Schema name must be between 1 and 128 characters",
  "status_code": 400
}

Table name not specified or empty string:

{
  "error": "Invalid request",
  "message": "tableName: Table name must be between 1 and 128 characters, 
   tableName: Table name cannot be blank",
  "status_code": 400
}

Stored procedure execution failure:

{
  "error": "datagate.00001E",
  "message": "The CSPM stored procedure couldn't be executed because: 
   Failed to execute stored procedure in addTables: 
   Unexpected error executing stored procedure SYSPROC.ACCEL_ADD_TABLES. 
   Check that the stored procedure exists and your credentials have 
   execute permissions. For more information, see the logs or contact 
   support with message ID datagate.00001E.",
  "status_code": 500
}

DELETE /v1/instance/tables

Deletes tables from a Data Gate instance.

Request:

Provide a request object in JSON format similar to the following. Replace the "schema_name": and "table_name": values as needed.

{
  "tables": [
    {
      "schema_name": "ADB",
      "table_name": "ADBCHG"
    },
    {
      "schema_name": "ADB",
      "table_name": "ADBHOLD"
    }
  ]
}

Success code: 204

Response:

No response object is returned if the request was successful.

Failure response:

One of the following JSON objects is returned in case of a failure:

Schema name not specified or empty string:

{
  "error": "Invalid request",
  "message": "schemaName: Schema name cannot be blank, 
   schemaName: Schema name must be between 1 and 128 characters",
  "status_code": 400
}

Table name not specified or empty string:

{
  "error": "Invalid request",
  "message": "tableName: Table name must be between 1 and 128 characters, 
   tableName: Table name cannot be blank",
  "status_code": 400
}

Stored procedure execution failure:

{
  "error": "datagate.00001E",
  "message": "The CSPM stored procedure couldn't be executed because: 
   Failed to execute stored procedure in removeTables: Unexpected error 
   executing stored procedure SYSPROC.ACCEL_REMOVE_TABLES. 
   Check that the stored procedure exists and your credentials 
   have execute permissions. For more information, see the logs or contact 
   support with message ID datagate.00001E.",
  "status_code": 500
}

GET /v1/instance/schemas/{schema}/tables/{table}

Returns a specific table in a Data Gate instance. The response object in JSON format includes table details. To submit this API call, replace {schema} with the name of the table schema and

{table}

with the name of the table you want to retrieve.

Success code: 200

Response:

The response looks like this:

{
  "schema": "ADB",
  "table_name": "ADBCHG",
  "status": "initial_load_pending",
  "type": "regular",
  "data_integrity": "unimpaired",
  "replication_state": null,
  "archive_state": "not_archived",
  "is_acceleration_enabled": false,
  "is_replication_enabled": false,
  "is_archived": false,
  "has_archive_problems": null,
  "disk_space_mb": null,
  "row_count": null,
  "data_distribution_skew": null,
  "data_organization_percent": 0,
  "last_loaded_at": null,
  "replication_capture_point": null,
  "federated_source": null,
  "physical_layout": {
    "informational_unique_key": null
  }
}

Failure response:

One of the following JSON objects is returned in case of a failure:

Schema name not specified

{
   "error":"Invalid request",
   "message":"schema: Schema name cannot be blank",
   "statusCode":400}

Table name not specified

{
   "error":"datagate.00404E",
   "message":"Path not found: /v1/datagate/accelerator/schemas/YXZDEMO/tables/. 
    For more information, see the logs or contact support with message 
    ID datagate.00404E.",
   "statusCode":404}
}

Table not found

{ 
    "error": "datagate.00404E", 
    "message": "The table ADB.Abcs couldn't be found. 
     Verify that the schema name 'ADB' and table name 
     'Abcs' are correct and that the table exists in the instance. 
     For more information, see the logs or contact support with 
     message ID datagate.00404E.", 
    "statusCode": 404
}

GET /v1/source/schemas

Returns a list of all Db2 for z/OS source table schemas that are found in the connected Db2 subsystem or data sharing group.

The complete request URL is http://localhost:8080/v1/source/schemas?page=1&page_size=100. In other words, you can add a query string that allows you specify the number of result pages used for the display and the maximum number of result entries per page. In this case, it is one page with 100 entries.

Success code: 200

Response:

The response looks like the following extract:

{
  "schemas": [
    "ABITBOL",
    "ADB",
    "BANKDEMO",
    "BANKDEMO1",
    "BANKDEMOSYS",
    "BASICSCH",
    "BVT",
    "CCEDBA",
    "CP4DDEMO",
    "DEMOTEST",
    "DGWFD",
    "DSNAQT",
    "DWASYNCHALO5",
    "DWASYNCNIMBUS",
    "DWASYNCU",
    .
    .
    .],
  "pagination": {
    "page": 1,
    "page_size": 100,
    "total_items": 77,
    "total_pages": 1
  }
}

Failure response:

One of the following JSON objects is returned in case of a failure:

Service temporarily unavailable:

{
  "message_id": "datagate.00500E",
  "message": "An unexpected internal server error occurred. This is likely a temporary issue. Try your request again.",
  "status_code": 500
}

GET /v1/source/schemas/{schema}/tables

Returns a list of all Db2 for z/OS source tables that belong to a particular schema in the connected Db2 subsystem or data sharing group. For a valid request, replace {schema} with the actual name of the table schema.

Example:

http://localhost:8080/v1/source/schemas/ADB/tables.?page=1&page_size=100

In this example, the schema name is ADB. A query string has been added that specifies the number of result pages and the maximum number of result entries per page. In this case, it is one page with 100 entries.

Success code: 200

Response:

The response looks like the following extract:

{
  "tables": [
    {
      "schema": "ADB",
      "table": "ADBCATVT",
      "row_count": -1,
      "page_count": -1,
      "table_size_kb": -1
    },
    {
      "schema": "ADB",
      "table": "ADBCHG",
      "row_count": -1,
      "page_count": -1,
      "table_size_kb": -1
    },
    {
      "schema": "ADB",
      "table": "ADBCHGAT",
      "row_count": -1,
      "page_count": -1,
      "table_size_kb": -1
    },
    .
    .
    .
     ],
  "pagination": {
    "page": 1,
    "page_size": 100,
    "total_items": 33,
    "total_pages": 1
  }
}

Failure response:

The following JSON object is returned in case of a failure:

Service temporarily unavailable:

{
  "message_id": "datagate.00500E",
  "message": "An unexpected internal server error occurred. This is likely a temporary issue. Try your request again.",
  "status_code": 500
}

GET /v1/source/tables

Returns a list of all Db2 for z/OS source tables in the connected Db2 subsystem or data sharing group.

Example:

http://localhost:8080/v1/source/tables.?page=1&page_size=100

A query string has been added that specifies the number of result pages and the maximum number of result entries per page. In this case, it is one page with 100 entries.

Success code: 200

Response:

The response looks like the following extract:

{
  "tables": [
    {
      "schema": "ABITBOL",
      "table": "DATA",
      "row_count": -1,
      "page_count": -1,
      "table_size_kb": -1
    },
     {
      "schema": "BVT",
      "table": "TB_CUSTOMER_PARTITIONED",
      "row_count": -1,
      "page_count": -1,
      "table_size_kb": -1
    },
    {
      "schema": "BVT",
      "table": "TB_CUSTOMER_PARTITIONED_WITH_ROWID",
      "row_count": -1,
      "page_count": -1,
      "table_size_kb": -1
    },
    {
      "schema": "CP4DDEMO",
      "table": "PATIENTS",
      "row_count": -1,
      "page_count": -1,
      "table_size_kb": -1
    },
    .
    .
    .
     ],
  "pagination": {
    "page": 1,
    "page_size": 100,
    "total_items": 907,
    "total_pages": 10
  }
}

Failure response:

The following JSON object is returned in case of a failure:

Service temporarily unavailable:

{
  "message_id": "datagate.00500E",
  "message": "An unexpected internal server error occurred. This is likely a temporary issue. Try your request again.",
  "status_code": 500
}