Data mining — Creating the sample table

In this exercise, you create the sample table BANKCUSTOMERS and import the banking data. This table is used for the Clustering training run.

To create a table and import the sample data from file bankingScoring.data, run the sample script bankingImport.db2. Use the following command to run the sample script bankingImport.db2:

db2 -stf bankingImport.db2

In the first part of the script bankingImport.db2, the DB2® table BANKCUSTOMERS is created, its columns are specified, and data types are defined for each column.

UPDATE COMMAND OPTIONS USING C off;

CREATE TABLE "BANKCUSTOMERS" (
   "CLIENT_ID"		CHAR(9) NOT NULL PRIMARY KEY,
   "AGE"       		REAL,
   "GENDER"		CHAR(2),
   "MARITAL_STATUS"	CHAR(12),
   "PROFESSION"		CHAR(24),
   "NBR_YEARS_CLI"	REAL, 
   "SAVINGS_ACCOUNT"	CHAR(3),
   "HAS_LIFE_INSURANCE" CHAR(3),
   "INT_CREDITCARD"	CHAR(3),
   "ONLINE_ACCESS"	CHAR(3),
   "JOINED_ACCOUNTS"	CHAR(3),
   "BANKCARD"		CHAR(3),
   "AVERAGE_BALANCE"	DECIMAL(10,2),
   "NO_CRED_TRANS"	REAL,
   "NO_DEBIT_TRANS"	REAL
) NOT LOGGED INITIALLY;

In the second part of the script the flat file bankingScoring.data is imported and inserted into the new table.

IMPORT FROM bankcustomers.data OF DEL INSERT INTO "BANKCUSTOMERS"
(  "CLIENT_ID",
   "AGE",
   "GENDER",
   "MARITAL_STATUS",
   "PROFESSION",
   "NBR_YEARS_CLI", 
   "SAVINGS_ACCOUNT",
   "HAS_LIFE_INSURANCE",
   "INT_CREDITCARD",
   "ONLINE_ACCESS",
   "JOINED_ACCOUNTS",
   "BANKCARD",
   "AVERAGE_BALANCE",
   "NO_CRED_TRANS",
   "NO_DEBIT_TRANS");