Controller API

The Controller API enables secure data transmission to IBM Controller from outside the firewall using standard Extract, Transform, and Load (ETL) tools.

Before you begin

Before you can use the Controller API feature, you must install the Controller Batch Web and Controller API components. After you install the components, you must configure the Controller Batch Web component.

Controller Batch Web component configuration:

Create the environment variable KEYSTORE_PASSWORD and set the value of this variable as key store password.
Create JWT signing key for Controller API:
1. Open Command Prompt.
2. Change the directory path to ..\ccr_64\frontend.
3. Execute the following command in the terminal
```
.\Node\node .\addEtlKey.js
```
4. A key file named key.jks is created at ..\ccr_64\fcmweb\jre\bin\.
  Note: This is a one-time setup task that must be performed by the System Administrator after the kit installation.
5. To verify if the key is generated, in the ..\ccr_64\fcmweb\jre\bin\ directory, execute the following script
```
keytool -list -v -keystore key.jks
```
6. Check for a key with an alias name ending in _keyetl.
7. If the key is not found, re-run Step C.
Configure file size limit for import:
1. Open PowerShell as Administrator.
2. In the PowerShell window, change the directory to ..\ccr_64\frontend.
3. To configure the file size limit, execute the following command
```
.\update_file_size.ps1
```
  Note: This is a mandatory configuration step that must be completed by the Administrator after installation. No default file size limit is set by the system.
Configure token expiry time for API access:
1. Open PowerShell as Administrator.
2. In the PowerShell window, change the directory to ..\ccr_64\frontend.
3. To set the token expiry time, execute the following command
```
.\update_token_expiry_time.ps1
```
  Note: This is a mandatory configuration step that must be completed by the Administrator after installation. There is no default expiry time set by the system.
Create a new database for the IBM Controller Batch Web service on the SQL server and name the database CCRBATCH.
Note: The SQL server user used for creating the CCRBATCH database should have access to all other CCR databases.
Open the Controller configuration tool and do the following:
1. Create a new database connection and name the connection as CCRBATCH.
2. Click Save.
Open Database Conversion utility for CCRBATCH and do the following steps:
1. In the Database Conversion Utility window, select Batch DB.
2. In the UDL File section, click Connect.
3. Click Create DB.
Go to the C:\Program Files\ibm\cognos\ccr_64\Data folder and create a new folder and name it BATCH. Move the CCRBATCH.udl file to the newly created BATCH folder.

Open a command prompt window and run the SyncDBConf.bat utility in the following path:

For example:

C:\Program Files\ibm\cognos\ccr_64\fcmweb\SyncDBConf.bat ..\Data wlp\usr\shared\config\datasources

Navigate to \..\ccr_64\fcmweb\wlp\usr\servers\fcm.web
1. Open the com.ibm.cognos.fcm.web.properties file.
2. Uncomment and update the following property value selfservice.backuprestore.services.url=http://localhost:9084/fcm.batch/.
Navigate to \..\ccr_64\fcmweb\wlp\usr\servers\fcm.batch
1. Open the config.properties file.
2. Update jdbc.dbname=<database connection name defined in Controller configuration>. For example, jdbc.dbname=CCRBATCH
3. Update fcm.web.url=<your server name>. For example, fcm.web.url=http://localhost:3000
4. Update etl.batch.enabled, change it from false to true. For example, etl.batch.enabled=true
5. Restart the following services:
  - IBM Controller Batch Web
  - IBM Controller Web
  - IBM Controller API

Procedure

Controller API access permission check
1. The right access can be defined in Controller Classic under Maintain Menu > Rights > Security group.
2. A new Controller API menu item is added under Controller Web.
3. The access right can either be Normal or Not available. If you select Not available, the user with that specific security group will not be able to access Controller API.
Note: Refer documentation for creating new security Group with restriction Create Security Groups.
If you want to disable Controller API, you must enable security flag as mentioned below from Controller Classic.
1. Navigate to Maintain Menu > Rights > User.
2. Check the Security System Enabled check-box.
3. Select User for which Backup and Restore access need to be restricted.
4. Move to Limitations tab.
5. Select the relevant menu group for which the Controller API access is restricted. For more information, see Create Security Groups.
6. Save the changes and close Controller Classic application.
Log in to the Controller Web.
On the Controller Web dashboard, select Administration.
Select Controller API tile. The Controller API page opens.
On the Controller API page, enter the Service ID and click Submit.

Note: Creating Service ID is a one-time activity. Service ID is an unique ID for a database. If two authorized users update it, the latest value will overwrite the previous value.
Create the required import specification for import data from Controller Web.

Note: Do not use Get from Context option for creating the specification for Controller API.
Following is the list of APIs that should be called in for doing the data import:
1. getToken API: This API generates JWT token. This token is required for accessing uploadFile and getStatus APIs.
  - HTTP Method: POST
  - URI: /controller-api/token
  - Content-Type: application/json
  - RequestBody:
    - datasource - Controller database connection name (as defined in Controller Configuration and/or as displayed at logon (database selection))
    - scope - Scope will be import_data for all the APIs related for import feature.
    - serviced - Service ID
  Sample request
```
POST {http://fcmweb-server-name.com:9085/controller-api/token}
Content-Type: application/json
{
  "datasource": "ccr_int",
  "scope": "import_data",
  "serviceId": "test"
}
```
  The response of this API contains the JWT token. This token must be provided in the request header for the uploadFile and getStatus APIs.
  Sample response
```
{
  "data": {
    "token": "eyJhbGciOiJIUzI1NiJ9.eyJzZXJ2aWNlSWQiOiJJTVBfZ2h5dCIsInJvbGUiOiJpbXBvcnRfZGF0YSIsImV4cCI6MTcxNjkwOTI2OH0.Z6v3J25_bsSLblPMbq06RN8Ehi3N1cbP-y8kC1QJDUM"
  },
  "status": "200 OK",
  "code": null,
  "message": null
}
```
2. uploadFile API: This API uploads the data file (xlsx/csv). Once the file is uploaded successfully, the batch process is triggered, this copies the file content to Controller database.
  - HTTP Method: POST
  - URI: /controller-api/file
  - Content-Type: multipart/form-data
  - Request Header: The token created by the getToken API
  - Request parameters:
    - file - The path and filename of the file to be uploaded
    - datasource - Controller database connection name (as defined in Controller Configuration and/or as displayed at logon (database selection))
    - importSpec - Import specification name
  Sample request
```
POST {http://fcmweb-server-name.com:9085/controller-api/file}
Content-Type: multipart/form-data,
"token": "eyJhbGciOiJIUzI1NiJ9.eyJzZXJ2aWNlSWQiOiJJTVBfZ2h5dCIsInJvbGUiOiJpbXBvcnRfZGF0YSIsImV4cCI6MTcxNjkwOTI2OH0.Z6v3J25_bsSLblPMbq06RN8Ehi3N1cbP-y8kC1QJDUM"
{
  "file": "ImportFile1.xlsx",
  "datasource": "ccr_int",
  "importSpec": "TEST1_AC",
}
```
  The response of this API contains the acknowledgment ID. This acknowledgment ID is used for tracking the status of the import.
  Sample response
```
{ "data": { "acknowledgementId": "1:ccr_int" }, "status": "201 CREATED", "code": null, "message": null }
```
3. getStatus API: This API tracks the status.
  - HTTP Method: GET
  - URI: /controller-api/status
  - Request header: The token created by the API getToken
  - Request parameter: The acknowledgment ID created by the uploadFile API.
  The response contains the details of the import status, including the rejected records and the reason for the rejection. This response is required for the user to review the status of the import. This is the only record of the import status. There are no other logs, inside of IBM Controller UI, to review the status.
  Sample request
```
GET {http://fcmweb-server-name.com:9085/controller-api/status?ackId=1:ccr_int}
token:eyJhbGciOiJIUzI1NiJ9.eyJzZXJ2aWNlSWQiOiJJTVBfdGVzdCIsInJvbGUiOiJpbXBvcnRfZGF0YSIsImRhdGFzb3VyXXXXXXX0ZXN0IiwiZXhwIjoxNzI0ODU3Njk1fQ.mnpdFConkjcHJE7xZ_5_eSCAdaIxU1nWjpSGVO6FzHo
```
  Sample response
```
{
  "data": {
    "status": "completed",
    "summary": {
      "overview": "2,475 of 2,520 rows imported",
      "mappingErrors": null,
      "otherErrors": {
        "1247": [
          "Company is locked"
        ],
        "1248": [
          "Company is locked"
        ],
        "1249": [
          "Company is locked"
        ] }
}
},
  "status": "200 OK",
  "code": null,
  "message": null
}
```