db2-rest-client
Advanced tools
Node.js Client for DB2 on Cloud (REST api) - administration, monitoring, exploring, loading data.
Node.js client for IBM Db2 (Warehouse) on Cloud REST API (previously DashDB). It is intended to be used for DevOps (administration, monitoring, data load) for DB2 on Cloud service. The client can be used as part of a Node.js application or as a CLI tool.
The target APIs are covering the following main areas: authentication, database objects, data load, SQL, file storage, monitoring, settings, users administration.
Installing the client locally and using it in a Node.JS application:
npm i db2-rest-client --save
const Db2RestClient = require('db2-rest-client');
// calling it directly in a root script
(async () => {
try {
const db2Client = new Db2RestClient({
credentials: {
userid: 'userId',
password: 'password'
},
uri: 'https://<db2-on-cloud-hostname>/dbapi/v3'
});
// load data from a local file - target table previously created
const res = await db2Client.load('./path/to/data.csv', 'MY_TABLE', 'MY_SCHEMA');
// query
const data = await db2Client.query('SELECT COUNT(*) AS TOTAL FROM MY_SCHEMA.MY_TABLE');
console.log(data);
} catch (err) {
console.err('Client error', err);
}
})();
Installing and using the module in CLI mode:
npm i -g db2-rest-client
# shell code start
export DB_USERID='<USERNAME>'
export DB_PASSWORD='<PASSWORD>'
export DB_URI='https://<hostname>/dbapi/v3'
# optional parameters
export DB_POLLING_WAIT=5000
export DB_POLLING_MAX_RETRIES=50
export DEBUG=db2-rest-client:cli
# call a job - load data to a target table
db2-rest-client load --file=sample1.csv --table='MY_TABLE' --schema='MY_SCHEMA'
# shell code end
Check the /test/integration and the /lib/strategy folders for usage examples.
Accept an config object as input with the following properties:
credentials - {userid: '...', password:'...'} as per Db2 on Cloud credentials page
uri - URI of the DB2 REST api (check the example above for V3)
pollingWait - Waiting time (ms) between polling requests
pollingMaxRetries - Number of polling retries before aborting a job
Base method for performing a request to DB2 Rest API - it performs authorization if necesary.
type ('DEFAULT') - maps to one of the preefined object in config/apiPrototypes.json
extraOptions ({}) - overrides the type based request options - check request-promise module for the format.
authRequired (true) - does request requires an auth key or not
Base method for the request polling required for some async rest calls as upload progress, batch queries etc. It checks the status of a job until the response body matches success or failure object or the polling limit is reached (pollingMaxRetries)
type ('DEFAULT') - as above
extraOptions - as above
success ({status: 'completed'}) - The body match for success - can be also an array
failed ({status: 'failed'})
tryCount - used internaly for polling
pollingMaxRetries (20) - Number of polling retries before aborting the job
pollingWait - Waiting time in miliseconds between polling requests
Executes an SELECT SQL query and returns up to 100.000 rows as JSON. For all other queries or non-existing table an error is reported.
Executes a comma separated list of SQL queries. All the queries that are not SELECT have also to use this method.
Uploads a local (CSV) file to the DB2 on Cloud server in order to be used later in a load job.
Loads data into a target table from a local .csv file. It calls the upload method above as part of the process.
filePath - path to local .csv file
table - Target table
schema - Table schema
isReplace (true) - Data load mode (REPLACE vs INSERT)
extraOptions ({}) - Override request payload - for example file type, data separator.
The module provides a set of predefined jobs that can be executed in an CLI/automation script and can be integrated with a CI runner (Jenkins/Travis). For the jobs that have an non-debug output (ex: query) can have it redirected to a separate file.
Environment variables - see the CLI api for values:
Executes an SQL statement in sync mode and returns up to 100.000 rows of data in JSON format. Only SELECT statements are allowed by the DB2 endpoint api.
export DB_USERID='<USERID>';export DB_PASSWORD='<PASSWORD>';export DB_URI='https://<hostname>/dbapi/v3';export DEBUG=db2-rest-client:cli;
# example of output to a file
db2-rest-client query --query="SELECT * FROM MANUAL.TST_SAMPLE" > test.json
Executes a coma separated list of SQL statements.
db2-rest-client batch --query="INSERT INTO MANUAL.TST_SAMPLE (ID, DESCRIPTION) VALUES ('100', 'test'); SELECT * FROM MANUAL.TST_SAMPLE;" > test.json
Loads data from a local .csv file into a target table.
db2-rest-client load --file=./test/data/sample1.csv --table='TST_SAMPLE' --schema='MANUAL' --type=INSERT
Tests performed on a DB2 on Cloud instance (Flex plan - IBM dedicated):
Loads data from a local .csv file in a newly created table (from the target table schema) and then replaces the target with the new table (renaming).
# default CSV file
db2-rest-client load-in-place --file=./test/data/sample2.csv --table='TST_SAMPLE' --schema='MANUAL'
# customize request - TSV file with header
db2-rest-client load-in-place --file=./test/data/sample3.tsv --table='TST_SAMPLE' --schema='MANUAL' --extra='{"body": { "file_options": {"has_header_row":"yes","column_delimiter":"0x09"}}}'
Executes a raw authenticated request using a JSON object as input compatible with request-promise-native.
# performing a GET request for the users list
db2-rest-client request --options='{"uri": "/users"}'
# returning storage information
db2-rest-client request --options='{"uri": "/monitor/storage"}'
# performing a POST request to create a schema
db2-rest-client request --options='{"uri": "/schemas", method:"POST", "body": {"name":"NEWSCHEMA"}}'
Some of the requests (as loading data) require first to do a POST request with the information and then check the progress using the returned ID until a success or failure status is reached.
# example for checking a load job created in a previous request
db2-rest-client request-polling --options='{"uri": "/load_jobs/1536865382644"}' --success='{"status": {"status": "Success"}}' --failed='{"status": {"status": "Failure"}}' --pollingMaxRetries=50 --pollingWait=10000
Note: The job is using the DB2 RENAME statement so additional actions are needed to re-create the indexes and other constraints.
Allows the user to tests the core client methods (executing statements, export data, load data from a .csv source file, upload) against a real DB2 (Warehouse) on Cloud instance.
# by default uses the user schema (some plans don't allow additional schemas - Entry plan for Db2 Warehouse on Cloud)
export DB_USERID='<userid>';export DB_PASSWORD='<password>';export DB_URI='https://<hostname>/dbapi/v3'; npm run integration
# testing creation of new schema as well
export DB_USERID='<userid>';export DB_PASSWORD='<password>';export DB_URI='https://<hostname>/dbapi/v3';export DB_NEW_SCHEMA=true; npm run integration
# all log levels
export DEBUG=db2-rest-client:*
# debug info
export DEBUG=db2-rest-client:info
# cli mode
export DEBUG=db2-rest-client:cli
We are welcoming contributors - feel free to report issues, request features and help us improve the tool. For code contribution please create first a feature request (issue - tagged enhancement or bug) then a PR request from your forked branch. Code needs to pass lint and UT automate checks before being reviewed.
FAQs
Node.js Client for DB2 on Cloud (REST api) - administration, monitoring, exploring, loading data.
We found that db2-rest-client demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 1 open source maintainer collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
In this segment of the Risky Business podcast, Feross Aboukhadijeh and Patrick Gray discuss the challenges of tracking malware discovered in open source softare.
Research
Security News
A threat actor's playbook for exploiting the npm ecosystem was exposed on the dark web, detailing how to build a blockchain-powered botnet.
Security News
NVD’s backlog surpasses 20,000 CVEs as analysis slows and NIST announces new system updates to address ongoing delays.