How to Setup A Sharded Cluster:

------------------------------------------------------------------------------------

This document details the instructions to setup  Informix NoSQL  in a sharded environment. The document also describes the steps to connect Informix server from a Mongo Shell to execute mongo shell commands.

If you would like to setup a shard cluster of 3 nodes on host1, host2 and host3

 For example,  let us refer them as serv1 on host1 serv2 on host2 and serv3 on host3

1) Bring up Informix servers on all participating  nodes

    NoSQL functionality was introduced on Informix server starting from 12.10 version. Install the latest Informix server on all the participating nodes and bring up the server.

2) On all the participating nodes , create a dbspace directory as  informix with 755 permissions

 Example :     mkdir    /datadir

                    chown informix:informix /datadir

                    chmod 755 /datadir

 3) Run the following script as user informix on all the pariticipating servers. Change the  “host”, "domain" and “ /datadir” to your node specific values . Add more host entries as required to the script.

 dbaccess sysadmin  - <<EOF

execute function task('STORAGEPOOL ADD','/datadir',0,0,'64MB',1);

execute function task("modify config persistent", "CDR_AUTO_DISCOVER", "1");

execute function task("modify config persistent", "SBSPACENAME", "sbspace1");

execute function task('CREATE SBSPACE FROM STORAGEPOOL', 'sbspace1', ‘100MB’, 1);

execute function task("cdr add trustedhost",'host1', 'host1.domain.com');

execute function task("cdr add trustedhost",'host2', 'host2.domain.com');

execute function task("cdr add trustedhost",'host3', 'host3.domain.com');

EOF

4) Now on each of the participating nodes (other than host1 which is the master node) run the following cdr command  as user informix in sequence (Wait to finish on one node before starting the cdr command on the next).

    cdr autoconfig server -H <primary server host> -P < primary server port>

    ex: on host 2  :  cdr autoconfig server -H host1  -P 10300

          on host3 :    cdr autoconfig server -H host1 –P 10300

INFORMIXSQLHOSTS entries on each node will be automatically updated with the server information from all the nodes.

Example:

$ cat $INFORMIXSQLHOSTS

g_serv1        group        -        -        i=1

serv1        onsoctcp        host1        10300        g=g_serv1

g_serv2        group        -        -        i=2

serv2        onsoctcp        host2        10700        g=g_serv2

5) Validate the shard cluster setup

 you can validate the cluster setup by running the "cdr list server" command which lists all the server names participating in the cluster.

$ cdr list server

SERVER                 ID STATE    STATUS     QUEUE  CONNECTION CHANGED

-----------------------------------------------------------------------

g_serv1              1 Active   Local                0               

g_serv2              2 Active   Connected       0 Feb 28 18:09:45

6) Sharding a table :

Finally to create a sharded collection ...

Create a database and a table on all participating nodes

Run the following scirpt on all the participating servers to create the database and a sample table :

dbaccess  - <<EOF

create database mydb with log;

create table tab1(id varchar(128) PRIMARY KEY NOT NULL, data bson, modcount bigint, flags int) LOCK MODE ROW;

EOF

Creating a  hash based shard:

Example of  sharding tab1  on the  key  "_id"  using hash strategy  on three servers serv1, serv2 and serv3

userid informix cdr define shardCollection myshard1 mydb:informix.tab1 -c serv1 --type=delete --key="bson_value_lvarchar(data,'_id')" --strategy=hash --versionCol=vcol  g_serv1 g_serv2 g_serv3

Creating an expression based shard:

userid informix cdr define shardCollection myshard1  testdb:informix.tab1 --type delete --key "bson_value_lvarchar(data,'_id')"  --strategy expression \

       g_serv1 " < 'user1000000000000000000' " \

       g_serv2 " between 'user2000000000000000000' and 'user3000000000000000000' " \

       g_serv3  "remainder"

 Listing the shards  on server

$ userid  informix onstat -g shard

 Deleting a shard :

userid informix cdr delete shardCollection myshard1

Connecting to Informix NoSQL Instance using Mongo Shell:

In order to connect to Informix server using Mongo Shell, you will need to first start the listener process. Listener process is an interface between Informix server and the Mongo API.

You can connect from a Mongo shell to either a stand alone Informix server or to an Informix server which is part of a shard cluster.

Sample json listener properties file is given below. Change the parameters to your server specifics and copy into $INFORMIXDIR/etc directory as user informix.

$ cat jsonListener.properties

listener.port=27017

url=jdbc:informix-sqli://<host1>:<portno>:INFORMIXSERVER=<serv1>;USER=<user>;PASSWORD=<passwd>

sharding.enable=true

update.client.strategy=deleteInsert

Starting the listener : Execute the startListener script 

$ cat startListner.sh

$INFORMIXDIR/extend/krakatoa/jre/bin/java -Xmx512M -Dcom.ibm.tools.attach.enable=no -jar $INFORMIXDIR/bin/jsonListener.jar -config $INFORMIXDIR/etc/jsonListener.properties -logfile /tmp/jsonListener.log -start &

Start the Mongo Shell :

Run the Mongo shell and connect to the informix instance

$./mongo host1:27017

MongoDB shell version: 2.4.9

connecting to: host1:27017/test

mongos> db

test

mongos>

You are all set to execute Mongo Shell commands against the Informix server now

Good Luck!!

When you have BI tool which generate reports based on nested views, many columns may get selected every level. Since each view is created with large number of columns, top level query may not use all columns from nested views. Informix server can create a temporary table for processing complex view. View with large number of columns has impact on processing materialized temporary table. Performance degradation will be higher with large number of materialized temporary tables at each nested level.

Informix server 12.10.xC1 release provide temporary table column optimization on views and derived tables. In this approach, Informix server decide to eliminate non-referenced columns dynamically at execution time. With this optimization, size of each materialized temporary table is reduced at each nested level. You will get performance improvement when views have large columns along with higher number of rows in base tables.

Original column map before Informix 12.10.xC1 release :

Query : select v1.vc1, v1.vc2 ,v1.vc3 from v1;

In this example, view v1 has 5 nested views ( v2 to v6), which refer to 6 base tables ( t1 to t6). Each view is materialized into a temp table. At top level v1 view need only 3 projected columns from its base tables. Columns shown in red color below in each temp table are non-referenced columns at every level.

Optimized column map in Informix 12.10.xC1 release :

As shown below, Informix server has eliminated non-referenced columns and retain those required at each query level. These required projected columns are shown in green color. Query performance depends on projected column size for materialized temporary tables. Informix server does not eliminate non-referenced view columns from system catalogs. These non-referenced columns are only removed from the view temp table during execution phase.

What happen when you use a derived table instead of a view ?

-- Informix server apply same column optimization rules for derived tables. In above case , If you use derived table , Informix server will build optimize column map.

Does Informix server eliminate non-reference columns when view ( or derived table) is folded into top level query ?

-- When temp table is not required, non-referenced columns are already eliminated by Informix server. This functionality available in all releases.

Create view view1 (vcol1, vcol2, vcol3) as select col1 , col2, col3 from tab1;

select view1.vcol1 , tab2.col1 from view1 , tab2 where tab2.col1 = view1.vcol1;

Query after view folding :

select tab1.col1 , tab2.col1 from tab1 , tab2 where tab2.col1 = tab1.col1;

In above case, all references of view1 is replaced by tab1. Since vcol2 & vcol3 columns are not selected in projection list, there is no need to perform column optimization.

Informix 12.10.xC2 release has Lateral derived table support. “LATERAL” is a new keyword and it is required at beginning of a derived table . Lateral derived table allow user to access correlated columns inside derived table. In this case, table reference for correlated column should appear before derived table in “FROM” clause table list.

Schema :

create table t1 (c1 int);

create table t2 (c2 int);

create table t3 (c3 int);

insert into t1 values (1);

insert into t1 values (2);

insert into t1 values (3);

insert into t2 values (1);

insert into t2 values (2);

insert into t3 values (1);

insert into t3 values (2);

-- Example-1

select t1.c1, dc1 from t1, LATERAL (select t2.c2 from t2 where t1.c1 = t2.c2) as dtab(dc1) ;

c1 dc1

1 1

2 2

2 row(s) retrieved.

In example-1, t1.c1 column is referenced inside derived table dtab. Since table t1 is not listed as part of the table list inside derived table, LATERAL keyword is required before derived table dtab. In this case, Informix server will provide t1.c1 value from t1 scan to the derived table dtab. Then t1 and dtab join operation is performed using output rows from dtab.

Since there is no value 3 in t2 table, above query returns 2 rows.

Note - Without LATERAL keyword, Informix server will return error for t1.c1 reference inside derived table.

-- Example-2

select t1.c1, dc1 from t1 left join LATERAL (select t2.c2 from t2 where t1.c1 = t2.c2) as dtab(dc1) on 1=1 ;

c1 dc1

1 1

2 2

3

3 row(s) retrieved.

Above Example-2 has left join instead of Informix join in Example-1. In this case, value 3 from t1 table is not matched with derived table column, which gives null value for dc1 column.

-- Example-3

select t1.c1, dc1 from t1 left join LATERAL (select t2.c2 from t2 left join t3 on t2.c2 = t3.c3 where t1.c1 = 1) as dtab(dc1) on 1=1 ;

c1 dc1

1 1

1 2

2

3

4 row(s) retrieved.

Example-3 has left join between t2 and t3 inside derived table and it has post-join filter with lateral column reference t1.c1. Table t1 scan will provide value 1 to the derived table dtab.Now derived table dtab returns 2 rows ( dc1 = 1 & dc1 = 2 ) from “left join” operation between t2 and t3. Remaining scan values from t1 does not produce any rows from derived table dtab.

You can still debug by including the TRACE statement in your SPL routine to generate tracing output while the SPL routine runs. For information about how to produce and examine TRACE statement output, check IDS manual for details.

Starting an SPL debugging session with Optim Development Studio

You can examine the runtime behavior of an SPL routine in a client debugging environment and control the flow of a debugging session, using the standard debugger interface of Optim Development Studio (ODS). The steps that follow are also valid for Optim Data Studio.

Note: SPL Routine Debugger support for IDS 11.70 is available in ODS 2.2.1.0 and later versions. Earlier ODS versions (like 2.2.0) supported IDS, but do not provide SPL support nor SPL Routine Debugger support.

To begin an SPL Routine debugging session in ODS, follow these steps:

IDS 11.70 Server instance: Start-up and configuration

To configure the Informix database server instance so that SPL routine debugging is enabled, follow these steps:

1. Install the IDS 11.70 product.
2. Configure and bring up the IDS 11.70 server with entries in the onconfig and sqlhosts files to support the DRDA communications protocol:
• onconfig:

DBSERVERNAME   ids_spldb

• sqlhosts:

ids_spldb   drsoctcp   ids_server_machine_name   port_number

3. Note: SPL Routine Debugger support for IDS 11.70 connects the server to the client via the IBM Data Server Driver for JDBC and SQLJ, and requires an Informix DRDA protocol connection.
4. You must provide an sbspace where the database server can store XML messages that ODS sends to the server. The setting of the SBSPACENAME configuration parameter specifies the name of the system default sbspace. You can create a default sbspace on the database server by using the onspaces utility with the -Df "LOGGING=ON" option. This sbspace is a requirement for debugging SPL on ODS.
5. If this is a fresh server instance, create a new database using the CREATE DATABASE statement. You will later use this database to create a Database connection in ODS to deploy and debug your SPL routines.

Routine Debugger Session Manager start-up (Optional)

In most cases, you can use the built-in session manager that ODS provides to debug your SPL routines. In some situations, however, (for example, if your server machine is behind a firewall), you might need to start a session manager on a TCP/IP port that has outbound access either on the server machine or on a different machine.

To start the session manager manually, follow these steps:

1. To start the session manager manually, use the following command to export the CLASSPATH environment variable setting:

export CLASSPATH=${INFORMIXDIR}/bin/db2dbgm.jar:$CLASSPATH

2. Using Java 1.5.0 or above and ensuring it is in the PATH environment variable run the following command, specifying your port number that the session manager will use and a pathname for the session manager log file:

java com.ibm.db2.psmd.mgr.Daemon -port port_num -log sess_mgr_log_path

Steps in ODS to deploy and debug SPL Routines

To configure ODS to deploy and debug Informix SPL routines, , follow these steps:

1. If you are not using the built-in session manager and have started a stand-alone session manager, use menu commands to configure that option in ODS (Window > Preferences > Run/Debug > Routine Debugger > DB2 screen) by choosing the "Use an already running Session manager" radio button, and providing the port number and the machine name.
2. To configure ODS for debugging IDS SPL routines, you need to create a Database connection in the Data Source Explorer perspective in ODS. Refer to the Optim Development Studio documentation for details on how to create a Database connection.
3. Right Click on the Database Connection > New Connection dialog box, choose Informix, and create a new database connection by choosing 'Informix 11.7 - IBM Data Server Driver for JDBC and SQLJ Default' from the Driver list. if that driver available. If 11.7 is not available, choose 'Informix 11.5 - IBM Data Server Driver for JDBC and SQLJ Default' from the Driver files: list..
4. In the Edit Driver Definitions dialog box, open the Jar List tab and verify that it includes db2jcc4.jar. If it does not, replace db2jcc.jar with db2jcc4.jar in the Driver files: list.
5. Refer to the Optim Development Studio documentation for details on how to Create, Deploy, and Debug SPL routines.

Now you are ready to create, deploy and debug IDS SPL routines in ODS.