{"_id":"@alexxxcoding/db2-rest-client-ibm-cloud","_rev":"1-c26a9b49988ceb18de74838007fbdd40","name":"@alexxxcoding/db2-rest-client-ibm-cloud","dist-tags":{"latest":"1.0.0"},"versions":{"1.0.0":{"name":"@alexxxcoding/db2-rest-client-ibm-cloud","version":"1.0.0","description":"Node.js Client for DB2 on IBM Cloud (REST api) - administration, monitoring, exploring, loading data.","main":"index.js","scripts":{"test":"mocha test/unit --recursive","coverage":"nyc npm test","integration":"mocha test/integration --recursive --timeout 50000","lint":"eslint index.js lib","db2-rest-client":"node ./bin/db2-rest-client.js"},"bin":{"db2-rest-client":"bin/db2-rest-client.js"},"pre-commit":["lint","test"],"repository":{"type":"git","url":"git+https://github.com/adriantanasa/db2-rest-client.git"},"author":{"name":"Adrian Tanasa","email":"adrian_tanasa@yahoo.com"},"license":"MIT","bugs":{"url":"https://github.com/adriantanasa/db2-rest-client/issues"},"homepage":"https://github.com/adriantanasa/db2-rest-client#readme","devDependencies":{"chai":"^4.1.2","coveralls":"^3.0.4","eslint":"^5.16.0","eslint-plugin-node":"^7.0.1","mocha":"^5.2.0","nock":"^10.0.0","nyc":"^13.0.1","pre-commit":"^1.2.2","sinon":"^6.1.5"},"dependencies":{"csvtojson":"^2.0.8","debug":"^3.1.0","lodash":"^4.17.11","minimist":"^1.2.0","request":"^2.88.0","request-promise-native":"^1.0.5"},"keywords":["db2","db2 rest client","dashDb client","db2 client"],"engines":{"node":">=8.11.0"},"directories":{"lib":"lib","test":"test"},"gitHead":"0b91ab3c08ba8f9ce73745eacefc80187be55cc0","_id":"@alexxxcoding/db2-rest-client-ibm-cloud@1.0.0","_nodeVersion":"12.18.0","_npmVersion":"6.14.4","dist":{"integrity":"sha512-0WcoT9uoeNeYHO2dL6dVPQSS/DTVcrOEV2TpBiYYix9ZW/P8h9vK7bJaAxPMvm3SY1oZdLThCSXeF4Fymk6FgA==","shasum":"d118708453d195c7b8c00ad23d5354005bfb35eb","tarball":"https://registry.npmjs.org/@alexxxcoding/db2-rest-client-ibm-cloud/-/db2-rest-client-ibm-cloud-1.0.0.tgz","fileCount":38,"unpackedSize":77927,"npm-signature":"-----BEGIN PGP SIGNATURE-----\r\nVersion: OpenPGP.js v3.0.13\r\nComment: https://openpgpjs.org\r\n\r\nwsFcBAEBCAAQBQJfhtUOCRA9TVsSAnZWagAA6TYP/1b9VPk9NNGkWATre+oF\nw8NLb2yzgmq8VTgueEg0sGxcQJgDRh6JxGbFeHVgOOBLhRT4Q6R0YJH7YTD4\nwkf12zaFNFaSJi/wM+4CIvLJKdZJr/YBncI4CcPJXPkOcVyDWaHvyTJqu3T1\nPr7kzvrEeaAMGQZcN611HWXK9peGzQMD2HMvtDqLZtE9o0/Aa093sE43khXX\n8/7JDeCYb2W1+rC2E0Y/oIKffoktACDVYhg5z9bSiGRPFtiUNoUeQDsLqGEr\nawUOijRT7F2/oieZ2PNZB8OE6im25F9P8VM3VhVB6jhzhLvvEDe4i/ukRPfQ\nxQGSL+nRCc7MmGaDL/PLUlk9P9YeoCvSXgjwo9agQJJtbY25uVV00fx3B2Bq\nKcO5/iiJkzLVrl/39bcEH4zZFzXQ0IeUuyYyYGY9pBda2nniOUGOxPiFn4k3\nkL9GuPVX5Gdb3TssEBlMtC23LG/kGUz6mDT/RV/8tmKG7UxypbOiuaHVkBtY\ncZ6+NL8D8vDv2ukFndVx5rvNwDHXx5hCnYX0q0TY9yOQkwpPAU2tjiP4bNfx\nEU/YKJyzPHwKdNx3FyGLe57YGmI6AM50aeS2/ah0MR6sfcKFO6fUlyc1Lmaw\nLkukDiRNxb/vk282BFLw+qmbox4aKtqny0cwNubP7M6E/rdzWRE1i8qONIlz\nVVeO\r\n=ggu6\r\n-----END PGP SIGNATURE-----\r\n","signatures":[{"keyid":"SHA256:jl3bwswu80PjjokCgh0o2w5c2U4LhQAE57gj9cz1kzA","sig":"MEYCIQDqo4rUBDJOoaGZjlkrw93r8YpTEhkn6Du53v9FTRTg3QIhAOuQ0TH6P0Y0y0Mqfnw3gRVVZneq47BhiMn+u28y/iDp"}]},"maintainers":[{"name":"alexxxcoding","email":"alexxxhuno@gmail.com"}],"_npmUser":{"name":"alexxxcoding","email":"alexxxhuno@gmail.com"},"_npmOperationalInternal":{"host":"s3://npm-registry-packages","tmp":"tmp/db2-rest-client-ibm-cloud_1.0.0_1602671886012_0.9001910443397299"},"_hasShrinkwrap":false}},"time":{"created":"2020-10-14T10:38:05.965Z","1.0.0":"2020-10-14T10:38:06.130Z","modified":"2022-04-04T12:42:00.918Z"},"maintainers":[{"name":"alexxxcoding","email":"alexxxhuno@gmail.com"}],"description":"Node.js Client for DB2 on IBM Cloud (REST api) - administration, monitoring, exploring, loading data.","homepage":"https://github.com/adriantanasa/db2-rest-client#readme","keywords":["db2","db2 rest client","dashDb client","db2 client"],"repository":{"type":"git","url":"git+https://github.com/adriantanasa/db2-rest-client.git"},"author":{"name":"Adrian Tanasa","email":"adrian_tanasa@yahoo.com"},"bugs":{"url":"https://github.com/adriantanasa/db2-rest-client/issues"},"license":"MIT","readme":"# db2-rest-client\r\n\r\n[![NPM Version][npm-image]][npm-url]\r\n[![Build Status][travis-image]][travis-url]\r\n[![NPM Downloads][downloads-image]][downloads-url]\r\n[![Coveralls Status][coveralls-image]][coveralls-url]\r\n\r\nMODIFICATION TO WORK WITH DB2 ON IBM CLOUD!\r\n\r\nNode.js client for [IBM Db2 (Warehouse) on Cloud](#references) REST API (previously DashDB).\r\nIt is intended to be used for DevOps (administration, monitoring, data load) for DB2 on Cloud service.\r\nThe client can be used as part of a Node.js application or as a CLI tool.\r\n\r\nThe target APIs are covering the following main areas: authentication, database objects, data load, SQL, file storage, monitoring, settings, users administration.\r\n\r\n- [Installation & Usage](#installation--usage)\r\n    - [Node.js Application](#nodejs-application)\r\n    - [CLI - CI/Shell script](#cli---cishell-script)\r\n- [Client API](#client-api)\r\n- [CLI API / Jobs](#cli-api--jobs)\r\n- [Starting checklist](#starting-checklist)\r\n- [Integration Testing](#integration-testing)\r\n- [Debugging](#debugging)\r\n- [Contribution](#contribution)\r\n- [References](#references)\r\n\r\n## Installation & Usage\r\n\r\n### Node.js Application\r\n\r\nInstalling the client locally and using it in a Node.JS application:\r\n\r\n```bash\r\nnpm i db2-rest-client --save\r\n```\r\n\r\n```javascript\r\nconst Db2RestClient = require('db2-rest-client');\r\n\r\n// calling it directly in a root script\r\n(async () => {\r\n    try {\r\n        const db2Client = new Db2RestClient({\r\n            credentials: {\r\n                userid: 'userId',\r\n                password: 'password'\r\n            },\r\n            uri: 'https://<db2-on-cloud-hostname>/dbapi/v3'\r\n        });\r\n\r\n        // load data from a local file - target table previously created\r\n        const res = await db2Client.load('./path/to/data.csv', 'MY_TABLE', 'MY_SCHEMA');\r\n        // query\r\n        const data = await db2Client.query('SELECT COUNT(*) AS TOTAL FROM MY_SCHEMA.MY_TABLE');\r\n        console.log(data);\r\n    } catch (err) {\r\n        console.err('Client error', err);\r\n    }\r\n})();\r\n```\r\n\r\n### CLI - CI/Shell script\r\n\r\nInstalling and using the module in CLI mode:\r\n\r\n```bash\r\nnpm i -g db2-rest-client\r\n\r\n# shell code start\r\nexport DB_USERID='<USERNAME>'\r\nexport DB_PASSWORD='<PASSWORD>'\r\nexport DB_URI='https://<hostname>/dbapi/v3'\r\n# optional parameters\r\nexport DB_POLLING_WAIT=5000\r\nexport DB_POLLING_MAX_RETRIES=50\r\nexport DEBUG=db2-rest-client:cli\r\n# call a job - load data to a target table\r\ndb2-rest-client load --file=sample1.csv --table='MY_TABLE' --schema='MY_SCHEMA'\r\n# shell code end\r\n```\r\n\r\n## Client API\r\n\r\nCheck the /test/integration and the /lib/strategy folders for usage examples.\r\n\r\n### construct\r\n\r\nAccept an config object as input with the following properties:\r\n\r\n- credentials - {userid: '...', password:'...'} as per Db2 on Cloud credentials page\r\n\r\n- uri - URI of the DB2 REST api (check the example above for V3)\r\n\r\n- pollingWait - Waiting time (ms) between polling requests\r\n\r\n- pollingMaxRetries - Number of polling retries before aborting a job\r\n\r\n### request \r\n\r\nBase method for performing a request to DB2 Rest API - it performs authorization if necesary.\r\n\r\n- type ('DEFAULT') - maps to one of the preefined object in config/apiPrototypes.json\r\n\r\n- extraOptions ({}) - overrides the type based request options - check request-promise module for the format.\r\n\r\n- authRequired (true) - does request requires an auth key or not\r\n\r\n### requestPolling\r\n\r\nBase method for the request polling required for some async rest calls as upload progress, batch queries etc. \r\nIt checks the status of a job until the response body matches success or failure object or the polling limit is reached (pollingMaxRetries)\r\n\r\n- type ('DEFAULT') - as above\r\n\r\n- extraOptions - as above\r\n\r\n- success ({status: 'completed'}) - The body match for success - can be also an array\r\n\r\n- failed ({status: 'failed'})\r\n\r\n- tryCount - used internaly for polling\r\n\r\n- pollingMaxRetries (20) - Number of polling retries before aborting the job\r\n\r\n- pollingWait - Waiting time in miliseconds between polling requests\r\n\r\n### query\r\n\r\nExecutes 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.\r\n\r\n- sqlQuery - SQL query\r\n\r\n### bulkQueries\r\n\r\nExecutes a comma separated list of SQL queries. All the queries that are not SELECT have also to use this method.\r\n\r\n- sqlQueries - SQL command (ex: 'DELETE FROM MY_SCHEMA.MY_TABLE WHERE 1; INSERT INTO MY_SCHEMA.MY_TABLE VALUES (\\'1\\',\\'TEST\\');')\r\n\r\n### upload\r\n\r\nUploads a local (CSV) file to the DB2 on Cloud server in order to be used later in a load job.\r\n\r\n- filePath - path to local .csv file\r\n\r\n### load\r\n\r\nLoads data into a target table from a local .csv file. It calls the upload method above as part of the process.\r\n\r\n- filePath - path to local .csv file\r\n\r\n- table - Target table\r\n\r\n- schema - Table schema\r\n\r\n- isReplace (true) - Data load mode (REPLACE vs INSERT)\r\n\r\n- extraOptions ({}) - Override request payload - for example file type, data separator.\r\n\r\n## CLI API / Jobs\r\n\r\nThe 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).\r\nFor the jobs that have an non-debug output (ex: query) can have it redirected to a separate file.\r\n\r\nEnvironment variables - see the [CLI api](#client-api) for values:\r\n\r\n- DB_USERID\r\n- DB_PASSWORD\r\n- DB_URI || DB_HOSTNAME\r\n- DB_POLLING_WAIT\r\n- DB_POLLING_MAX_RETRIES\r\n\r\n### query\r\n\r\nExecutes an SQL statement in sync mode and returns up to 100.000 rows of data in JSON format. Only SELECT statements are allowed by\r\nthe DB2 endpoint api.\r\n\r\n```bash\r\nexport DB_USERID='<USERID>';export DB_PASSWORD='<PASSWORD>';export DB_URI='https://<hostname>/dbapi/v3';export DEBUG=db2-rest-client:cli;\r\n# example of output to a file\r\ndb2-rest-client query --query=\"SELECT * FROM MANUAL.TST_SAMPLE\" > test.json\r\n```\r\n\r\n### batch\r\n\r\nExecutes a coma separated list of SQL statements.\r\n\r\n```bash\r\ndb2-rest-client batch --query=\"INSERT INTO MANUAL.TST_SAMPLE (ID, DESCRIPTION) VALUES ('100', 'test'); SELECT * FROM MANUAL.TST_SAMPLE;\" > test.json\r\n```\r\n\r\n### load\r\n\r\nLoads data from a local .csv file into a target table.\r\n\r\n```bash\r\ndb2-rest-client load --file=./test/data/sample1.csv --table='TST_SAMPLE' --schema='MANUAL' --type=INSERT\r\n```\r\n\r\nTests performed on a DB2 on Cloud instance (Flex plan - IBM dedicated):\r\n\r\n - file size 70MB / ~ 4 million rows completed in 3 minutes\r\n - file size 200MB / ~ 7 million rows completed in 7 minutes\r\n\r\n### load-in-place\r\n\r\nLoads 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).\r\n\r\n```bash\r\n# default CSV file\r\ndb2-rest-client load-in-place --file=./test/data/sample2.csv --table='TST_SAMPLE' --schema='MANUAL'\r\n\r\n# customize request - TSV file with header\r\ndb2-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\"}}}'\r\n\r\n```\r\n\r\n### request\r\n\r\nExecutes a raw authenticated request using a JSON object as input compatible with request-promise-native.\r\n\r\n```bash\r\n# performing a GET request for the users list\r\ndb2-rest-client request --options='{\"uri\": \"/users\"}'\r\n# returning storage information\r\ndb2-rest-client request --options='{\"uri\": \"/monitor/storage\"}'\r\n# performing a POST request to create a schema\r\ndb2-rest-client request --options='{\"uri\": \"/schemas\", method:\"POST\", \"body\": {\"name\":\"NEWSCHEMA\"}}'\r\n```\r\n\r\n### request-polling\r\n\r\nSome 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.\r\n\r\n```bash\r\n# example for checking a load job created in a previous request\r\ndb2-rest-client request-polling --options='{\"uri\": \"/load_jobs/1536865382644\"}' --success='{\"status\": {\"status\": \"Success\"}}' --failed='{\"status\": {\"status\": \"Failure\"}}'  --pollingMaxRetries=50 --pollingWait=10000\r\n```\r\n\r\n_Note:_ The job is using the DB2 _RENAME_ statement so additional actions are needed to re-create the indexes and other constraints.\r\n\r\n## Starting checklist\r\n\r\n- DB2 on Cloud or DB2 Warehouse on Cloud Service created\r\n- At least one user defined on the service Credentials page - **IBM on Cloud > DB2 Service > Service Credentials** or **Data & Analytics > Db2 Warehouse on Cloud > Service Credentials**\r\n- (Recommended) Repository cloned localy and [integration tests](#integration-testing) executed with success\r\n\r\n## Integration Testing\r\n\r\nAllows 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.\r\n\r\n\r\n```bash\r\n# by default uses the user schema (some plans don't allow additional schemas - Entry  plan for Db2 Warehouse on Cloud)\r\nexport DB_USERID='<userid>';export DB_PASSWORD='<password>';export DB_URI='https://<hostname>/dbapi/v3'; npm run integration\r\n\r\n# testing creation of new schema as well\r\nexport DB_USERID='<userid>';export DB_PASSWORD='<password>';export DB_URI='https://<hostname>/dbapi/v3';export DB_NEW_SCHEMA=true; npm run integration\r\n```\r\n\r\n## Debugging\r\n\r\n```\r\n# all log levels\r\nexport DEBUG=db2-rest-client:*\r\n# debug info\r\nexport DEBUG=db2-rest-client:info\r\n# cli mode\r\nexport DEBUG=db2-rest-client:cli\r\n```\r\n\r\n## Contribution\r\n\r\nWe are welcoming contributors - feel free to report issues, request features and help us improve the tool.\r\nFor code contribution please create first a feature request (issue - tagged *enhancement* or *bug*) then a PR request from your forked branch.\r\nCode needs to pass lint and UT automate checks before being reviewed.\r\n\r\n## References\r\n\r\n* [IBM Db2 Warehouse on Cloud REST API](https://developer.ibm.com/static/site-id/85/api/db2whc-v3/)\r\n* [Db2 on Cloud - IBM Knowledge Center](https://www.ibm.com/support/knowledgecenter/en/SS6NHC/com.ibm.swg.im.dashdb.kc.doc/welcome.html)\r\n* [IBM DB2 Warehouse on Cloud](https://www.ibm.com/cloud/db2-warehouse-on-cloud)\r\n* [IBM DB2 on Cloud](https://www.ibm.com/cloud/db2-on-cloud)\r\n* [ibm_db module](https://www.npmjs.com/package/ibm_db)\r\n\r\n\r\n[npm-image]: https://img.shields.io/npm/v/db2-rest-client.svg\r\n[npm-url]: https://npmjs.org/package/db2-rest-client\r\n[travis-image]: https://img.shields.io/travis/adriantanasa/db2-rest-client/master.svg\r\n[travis-url]: https://travis-ci.org/adriantanasa/db2-rest-client\r\n[downloads-image]: https://img.shields.io/npm/dm/db2-rest-client.svg\r\n[downloads-url]: https://npmjs.org/package/db2-rest-client\r\n[coveralls-image]: https://coveralls.io/repos/github/adriantanasa/db2-rest-client/badge.svg?branch=master\r\n[coveralls-url]: https://coveralls.io/github/adriantanasa/db2-rest-client?branch=master\r\n","readmeFilename":"README.md"}