Connecting to Db2 with node-ibm_db
To connect to Db2 with node-ibm_db, you will need to set a connection string, which will include information for connection such as your database name (Datasource name or DSN) and authentication credentials.
An example connection string would look like this:
const connStr = ‘DSN=[YOUR_DATASOURCE_NAME];UID=[YOUR_UID];PWD=xxxxxxx’
Or an alternate way would be setting the environment variable:
export IBM_DB_DBNAME=[YOUR_DATASOURCE_NAME]
export IBM_DB_UID=[YOUR_UID]
export IBM_DB_PWD=”xxxxxxx”
You can open the connection with the connection string:
ibmdb.open(connStr, function (err, conn) {
//…
});
Or you can open the connection synchronously:
const conn = ibmdb.openSync(connStr);
Querying
You can query Db2 by calling the query method on the Database object, which is returned after successfully opened a connection:
ibmdb.open(connStr, function (err, conn) {
conn.query(‘create table mytab1 (c1 int, c2 varchar(10));’, function (err, data) {
//…
});
});
Note: Currently Db2 on z/OS does not support BOOLEAN type, so you will get an error if you create a table with a BOOLEAN type column
If you want to run multiple queries synchronously, you can either call the following query inside the callback
conn.query(‘create table mytab1 (c1 int, c2 varchar(10));’, function (err, data) {
conn.query(‘insert into mytab1 value (4, 'a');’, function (err, data) {
//…
})
});
Or use querySync
conn.querySync(‘create table mytab1 (c1 int, c2 varchar(10));’);
// This query will run after the query above
conn.query(‘insert into mytab1 value (4, 'a');’, function (err, data) {
//…
});
Asynchronous query
One of the signature features of node.js is that it is asynchronized. You can also easily run multiple query asynchronously:
conn.query(‘select * from mytab1 where c1 = 2;’, function (err, data) {
//…
});
conn.query(‘select * from mytab1 where c1 = 4;’, function (err, data) {
//…
});
but be aware that you need to ensure that the queries you are about to run are safe to run asynchronously.
Prepare statement
It is a good practice to prepare your SQL statement before executing it. You can prepare a statement by:
conn.prepare('insert into mytab (insert into mytab (id, varchar(10)) VALUES (?, ?)', function (err, stmt) {
//…
});
To execute a prepared statement, you can:
conn.prepare('insert into mytab (insert into mytab (id, varchar(10)) VALUES (?, ?)', function (err, stmt) {
stmt.execute([ 42, ‘abc’], function (err, result) {
//…
});
});
It will bind the variable 42 and “abc” to the two “?” declared in the prepared statement respectively and execute the statement.
If your prepared statement is an UPDATE, an INSERT, a DELETE, or a MERGE, you can run:
conn.prepare('insert into mytab (insert into mytab (id, varchar(10)) VALUES (?, ?)', function (err, stmt) {
stmt.executeNonQuery([ 42, ‘abc’], function (err, affectedRowCount) {
//…
});
});
executeNonQuery() returns the number of rows affected in the table by the statement. If no rows are affected, it returns -1 via the callback function.Metadata
Sometimes you may need to retrieve metadata information in your tables such as column names.
To get the column names:
// result is the ODBCResult object return from execute() and query()
result.getColumnNamesSync();
To get the column metadata:
result.getColumnMetadataSync()
Note: If you are getting SQLCODE = -440 error, please ensure that you have Database MetaData routines installed in your Db2. These routines are provided to perform catalog queries such as SQLColumns and SQLTables. You can run job DSNTIJRV provided in the SDSNSAMP library to validate that the Database MetaData routines are successfully created. To install the routines, run job DSNTIJRT as part of the Db2 installation. If you can confirm that the routines are already installed but still getting -440 failure, try to rebind everything.
Closing connection
You will need to close the connection once you are finished. You can close your connection by:
conn.close(function (err) {
console.log(‘Done’);
});
Synchronously,
conn.closeSync()
You should always remember to close your connection, especially when your program runs into an error:
if(err) {
console.log(err);
return conn.closeSync();
}
Running your application
You can run your application with Node.js by
node myapp.js
provided that your code is saved in myapp.js.
At this point, you should have a basic understanding of how to use the node-ibm_db module to communicate between your node.js application and Db2 on your z/OS system. For the full list of available APIs, see here.
YouTube Links:
Comments
Post a Comment