JSQSH SETUP WIZARD   Welcome to the jsqsh setup wizard! This wizard provides a (crude) menu driven interface for managing several jsqsh configuration files. These files are all located in $HOME/.jsqsh, and the name of the file being edited by a given screen will be indicated on the title of the screen   Note that many wizard screens require a relative large console screen size, so you may want to resize your screen now.   (C)onnection management wizard The connection management wizard allows you to define named connections using any JDBC driver that jsqsh recognizes. Once defined, jsqsh only needs the connection name in order to establish a JDBC connection   (D)river management wizard The driver management wizard allows you to introduce new JDBC drivers to jsqsh, or to edit the definition of an existing driver. The most common activity here is to provide the classpath for a given JDBC driver   Choose (Q)uit, (C)onnection wizard, or (D)river wizard:

JSQSH CONNECTION WIZARD - (edits $HOME/.jsqsh/connections.xml The following connections are currently defined:   Name Driver Host Port --- -------------------- ---------- ------------------------------ ------ 1 bigsql db2 mn01 32051   Enter a connection number above to edit the connection, or: (B)ack, (Q)uit, or (A)dd connection:

JSQSH CONNECTION WIZARD - (edits $HOME/.jsqsh/connections.xml)   Choose a driver for use by your new connection   Name Target Class --- ---------------- -------------------- -------------------------------------------------- 1 *db2 IBM Data Server (DB2 com.ibm.db2.jcc.DB2Driver 2 *db2zos IBM DB2 z/OS com.ibm.db2.jcc.DB2Driver 3 *hive2 Apache Hive org.apache.hive.jdbc.HiveDriver 4 *mysql MySQL com.mysql.jdbc.Driver 5 derby Apache Derby Server org.apache.derby.jdbc.ClientDriver 6 derbyembed Apache Derby Embedde org.apache.derby.jdbc.EmbeddedDriver 7 firebird Firebird JayBird org.firebirdsql.jdbc.FBDriver 8 hive Apache Hive org.apache.hadoop.hive.jdbc.HiveDriver 9 informix IBM Informix com.informix.jdbc.IfxDriver 10 jdbcodbc JDBC ODBC Bridge sun.jdbc.odbc.JdbcOdbcDriver 11 mssql MS SQL Server com.microsoft.jdbc.sqlserver.SQLServerDriver 12 mssql-jtds MS SQL Server jTDS net.sourceforge.jtds.jdbc.Driver 13 mssql2k5 MS SQL Server 2005+ com.microsoft.sqlserver.jdbc.SQLServerDriver 14 netezza IBM Netezza org.netezza.Driver 15 oracle Oracle oracle.jdbc.OracleDriver 16 oracleoci Oracle OCI oracle.jdbc.driver.OracleDriver 17 pgsql PostgreSQL org.postgresql.Driver 18 sybase Sybase ASE com.sybase.jdbc3.jdbc.SybDriver 19 sybase-asa Sybase ASA com.sybase.jdbc2.jdbc.SybDriver 20 sybase-jtds Sybase ASE jTDS net.sourceforge.jtds.jdbc.Driver   * = Driver is available. If a driver is unavailable, you may choose (D) below to jump to the driver wizard to provide a classpath   Enter the driver number, (D)river wizard, (B)ack or (Q)uit:

JSQSH CONNECTION WIZARD - (edits $HOME/.jsqsh/connections.xml)   The following configuration properties are supported by this driver.   Connection name : _temp_ Driver : IBM Data Server (DB2, Informix, Big SQL) JDBC URL : jdbc:db2://${server}:${port}/${db}   Connection URL Variables ------------------------ 1 db : BIGSQL 2 port : 32051 3 server : localhost 4 user : bigsql 5 password : 6 Autoconnect : false   JDBC Driver Properties ------------------------ None   Enter a number to change a given configuration property, or (T)est, (B)ack, (Q)uit, Add (P)roperty, or (S)ave:

Welcome to JSqsh 4.4 Type "help" for help topics. Using JLine. 1> connect bigsql Password: ******* [cec01n01.cluster.com][bigsql] 1> select * from sysibm.sysdummy1; +---------+ | IBMREQD | +---------+ | Y | +---------+ 1 row in results(first row: 0.3s; total: 0.3s)

db2 catalog tcpip node <bigsql_node> remote <head-node> server <server-port>   db2 catalog db <bigsql_database> at node <bigsql_node>   db2 connect to <bigsql_database> user <user_id> using <user_password>   db2 "select * from sysibm.sysdummy1"

Note: You must catalog the head node. Otherwise, you can receive the following error when you query data:   SQL0969N There is no message text corresponding to SQL error "-5115" in the message file on this workstation. The error was returned from module "SQLRR0AA" with original tokens "SQL statements only allowed on head node" If this error occurs, run the uncatalog node and catalog node commands by using the head node settings. Also, issue the db2 terminate command to recycle your session connections. Try to connect and perform the simple SQL query again: [db2inst1@dn06 ~]$ db2 "select * from sysibm.sysdummy1" SQL0969N There is no message text corresponding to SQL error "-5115" in the message file on this workstation. The error was returned from module "SQLRR0AA" with original tokens "SQL statements only allowed on head node". db2inst1@dn06 ~]$ db2 uncatalog node bigsql DB20000I The UNCATALOG NODE command completed successfully. DB21056W Directory changes may not be effective until the directory cache is refreshed. [db2inst1@dn06 ~]$ db2 catalog tcpip node bigsql remote mn01 server 32051 DB20000I The CATALOG TCPIP NODE command completed successfully. DB21056W Directory changes may not be effective until the directory cache is refreshed. [db2inst1@dn06 ~]$ db2 terminate DB20000I The TERMINATE command completed successfully. [db2inst1@dn06 ~]$ db2 connect to bigsql user bigsql Enter current password for bigsql:   Database Connection Information   Database server = DB2/LINUXPPC64LE 10.6.3 SQL authorization ID = BIGSQL Local database alias = BIGSQL   [db2inst1@dn06 ~]$ db2 "select * from sysibm.sysdummy1"   IBMREQD ------- Y   1 record(s) selected.

Data source	Supported versions	Notes
DB2	DB2 for Linux, UNIX, and Windows 9.7, 9.8, 10.1, and 10.5
Oracle	11g, 11g R1, and 11g R2
Teradata	12, 13, and 14	Not supported on Power Systems.
Netezza	4.6, 5.0, 6.0, and 7.2	Not supported on Power Systems.
Informix	11.5
Microsoft SQL Server	2012

db2 get dbm cfg | grep "Federated Database System Support"

Note: If the FEDERATED setting is not YES, you must enable federation by using the following command: db2 update dbm cfg using FEDERATED YES Then, stop and start the bigsql instance with the following commands: $BIGSQL_HOME/bin/bigsql stop $BIGSQL_HOME/bin/bigsql start

db2 catalog tcpip node <db2_blu_node> remote <db2_blu_host> server <db2_blu_port>   db2 catalog db <db2_blu_database> at node <db2_blu_node>

db2 connect to bigsql user bigsql   db2 CREATE WRAPPER DRDA LIBRARY 'libdb2drda.so'   db2 "CREATE SERVER DB2REMOTE TYPE DB2/UDB VERSION 10.5 WRAPPER DRDA AUTHORIZATION "<db2_blu_instance_owner>" PASSWORD "<db2_blu_instance_owner_password>" OPTIONS (DBNAME '<db_blu_database>')"   db2 “CREATE USER MAPPING FOR <big_sql_instance_owner> SERVER DB2REMOTE OPTIONS ( REMOTE_AUTHID '<db2_instance_owner>', REMOTE_PASSWORD '<db2_instance_owner_password>' )”

./setup.sh -u <userid> -s <host_name> -n <port number> -d <databaseName>

jsqsh <db> -U <user> -P <pass> < $BIGSQL_HOME/samples/queries/GOSALESDW_ddl.sql

CREATE HADOOP TABLE GOSALESDW.TWITTERSENTIMENT ( TWITT_COUNT INTEGER, SEARCHOBJECT VARCHAR(60), CATEGORY VARCHAR(48), BRAND VARCHAR(24), FORMAT VARCHAR(12), FAMILY VARCHAR(12), SUBFAMILY VARCHAR(18), PRODUCT VARCHAR(36), OBJECTPROVENANCE VARCHAR(32), SOURCE VARCHAR(14), ISBUZZ VARCHAR(12), ISSENTIMENT VARCHAR(22), ISINTENT VARCHAR(16), ISOWNERSHIP VARCHAR(22), ISCUSTOMEROF VARCHAR(24), POLARITY VARCHAR(16), CREATEDTIME TIMESTAMP, FULLNAME VARCHAR(58), GENDER VARCHAR(12), CITY VARCHAR(26), STATE VARCHAR(10), SCREENNAME VARCHAR(38), USERID BIGINT, USERVERIFIED VARCHAR(24), FOLLOWERSCOUNT INTEGER, ISPARENT VARCHAR(16), ISMARRIED VARCHAR(18), TEXT VARCHAR(226) )   ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

LOAD HADOOP USING FILE URL 'sftp://<user>:<pass>@<head-node>/tmp/gs_db/TwitterSentiment.csv' WITH SOURCE PROPERTIES ( 'field.delimiter'=',' , 'skip.lines.count'='1', 'date.time.format' = 'MM/dd/yyyy','date.time.format' = 'MM/dd/yyyy hh:mm' ) INTO TABLE gosalesdw.TwitterSentiment OVERWRITE ;

Note: For testing, you can also disable IBM Cognos Application Firewall temporarily with Local Configuration → Security → IBM Cognos Application Firewall. IBM Cognos Application Firewall is an essential component of the product security against penetration vulnerabilities, and it must not be disabled under normal circumstances.

Event	Description
1 - User Authentication	The user authenticates and the historical behavior data is retrieved with the customer ID.
2 - Scoring Service request	The online web store sends customer identification and behavior data to the Scoring Service.
3 - Scoring Service response	The Scoring Service determines the best offer for the particular customer identification and sends the offer back to the online web store.
4 - Presenting offer	The online web store presents the best offer to the user.
5 - Accept/decline offer	The user accepts or declines the offer.
6 - Response Service log	The online web store sends the response to the Response Service and it is logged for future reference and analysis.