Setting up DB2

These instructions assume that you are using DB2 Universal Database and guide you through the following tasks:

Create a database called DBINPUT for use by the DatabaseInput Node sample
Bind the DB2 CLI packages

The following tasks are optional:

Create a table, DBINPUT_CUSTOMER, for use by the DatabaseInput Node sample
Create an event table, DBINPUT_EVENTS, to hold the information for which the DatabaseInput node polls
Create triggers to populate the DBINPUT_EVENTS table

Complete the following steps:

Open a new document in a text editor, copy and paste the following script, which creates the DBINPUT database and runs the BIND utility, into the document, name the file dbinput.sql and save the file.
- On Windows:
  You must replace install_dir with the path to your DB2 product installation directory, such as C:\PROGRA~1\IBM\SQLLIB. You can find this location by using the db2level command:
```
CREATE DB DBINPUT
CONNECT TO DBINPUT
BIND 'install_dir\bnd\@db2cli.lst' blocking all grant public
TERMINATE
```
- On Linux:
  You must replace install_dir with the path to your DB2 product installation directory. You can find this location by using the db2level command:
```
CREATE DB DBINPUT
CONNECT TO DBINPUT
BIND install_dir/bnd/@db2cli.lst grant public CLIPKG 5
TERMINATE
```
In a DB2 command window, enter the following command to ensure that DB2 is started:
```
db2start
```
In a DB2 command window, navigate to the folder that contains dbinput.sql and enter the following command:
```
db2 -vf dbinput.sql
```

Next: Choose one of the following options to create the database tables and triggers:

Return to the main sample page and choose one of the import options, see DatabaseInput Node sample. After importing you can choose to set up the database from SQL scripts.
If you prefer to create the tables manually, use the following instructions:

Manually create the database tables and triggers

Note. When you create and access the database tables, be aware of the following issues:

When you run the script that creates the table in the database, the user identifier from which you run the script determines the database schema name. For example, if your user identifier is USERID, the fully qualified name of the DBINPUT_CUSTOMER table is USERID.DBINPUT_CUSTOMER.
When you set a node to access a table, if the node is not using the same user identifier to access the database that you used when you created the database and tables, include the schema name in the integration node. For example, Database.USERID.DBINPUT_CUSTOMER.

Create the database tables and triggers:

Open a new document in a text editor, copy and paste the following script, which creates the DBINPUT_CUSTOMER and DBINPUT_EVENTS table, into the document, name the file dbinput_create_tables_db2.sql and save the file.

 
CONNECT TO DBINPUT@

DROP TABLE "DBINPUT_CUSTOMER"@
CREATE TABLE "DBINPUT_CUSTOMER" (
		"PKEY" VARCHAR(10) NOT NULL,
		"FIRSTNAME" VARCHAR(20),
		"LASTNAME" VARCHAR(20),
		"CCODE" VARCHAR(10)
	)
	DATA CAPTURE NONE@

DROP TABLE "DBINPUT_EVENTS"@
CREATE TABLE "DBINPUT_EVENTS" (
		"EVENT_ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE NO CACHE),
		"OBJECT_KEY" VARCHAR(80) NOT NULL,
		"OBJECT_VERB" VARCHAR(40) NOT NULL
	)
	DATA CAPTURE NONE@


ALTER TABLE "DBINPUT_CUSTOMER" ADD CONSTRAINT "DBINPUTCUSTOMERPK" PRIMARY KEY
	("PKEY")@

ALTER TABLE "DBINPUT_EVENTS" ADD CONSTRAINT "DBINPUTEVENTPK" PRIMARY KEY
	("EVENT_ID")@

CREATE TRIGGER "DBIN_CUST_CREATE" 
	AFTER INSERT ON "DBINPUT_CUSTOMER"
	REFERENCING  NEW AS N
	FOR EACH ROW

INSERT INTO DBINPUT_EVENTS (OBJECT_KEY, OBJECT_VERB)
       VALUES (N.pkey, 'Create')@

CREATE TRIGGER "DBIN_CUST_DELETE" 
	AFTER DELETE ON "DBINPUT_CUSTOMER"
	REFERENCING  OLD AS O
	FOR EACH ROW

INSERT INTO DBINPUT_EVENTS (OBJECT_KEY, OBJECT_VERB)
       VALUES (O.pkey, 'Delete')@

CREATE TRIGGER "DBIN_CUST_UPDATE" 
	AFTER UPDATE ON "DBINPUT_CUSTOMER"
	REFERENCING  NEW AS N
	FOR EACH ROW

INSERT INTO DBINPUT_EVENTS (OBJECT_KEY, OBJECT_VERB)
       VALUES (N.pkey, 'Update')@

TERMINATE@

Ensure the database is running and connected.
Open a command window to access your database, move to the folder that contains dbinput_create_tables_db2.sql and enter the following command:
```
db2 -td@ -vf dbinput_create_tables_db2.sql
```
Wait for the script to finish running. If you are running the script for the first time, the following message is displayed twice:
```
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0204N  "DB2ADMIN.DBINPUT_EVENTS" is an undefined name.  SQLSTATE=42704
```
Ignore both messages. The message is displayed because the script attempts to remove existing tables, DBINPUT_CUSTOMER and DBINPUT_EVENTS, before it creates the new tables, but if you have not run the script before, the script cannot find the existing tables.
If you are not running the script for the first time, you might see the following warning for each of the triggers:
```
SQL0280W  View, trigger or materialized query table
"DB2ADMIN.DBIN_CUST_UPDATE" has replaced an existing inoperative view, trigger
or materialized query table.  SQLSTATE=01595
```
You can also ignore these messages.

Back to Setting up the database

Back to sample home