![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
In the Informix database server environment, a grid is a set of replication servers that simplify administration. Grids are built on Enterprise Replication (ER), an asynchronous log-based tool for replicating data between IBM Informix database servers. When you run SQL data definition statements from within a grid context on one server in the grid, those statements are propagated to all other servers in the grid. You can run SQL data manipulation statements and routines through grid routines. You can choose to set up replication automatically when you create a new table through a grid.
Figure 1 illustrates a grid where row images are replicated among members of the grid.
Figure 1. Grid diagram
Prior to V11.7, there was no easy way to change the schemas of replicated tables or performing any administration tasks on replicated servers. It was necessary to perform tasks on all replicated servers. For example, if you had a three-ER server setup and you wanted to add a column to a replicate table (tab1), you had to first set the table alter mode on all three servers, then go into each server and perform the alter table commands. If you had more than 10 ER servers, the time you need to perform this simple task would drastically increase. With V11.7, you no longer must manage these servers individually.
A grid can be useful when you have multiple replication servers and you often need to perform the same tasks on every replication server. The following tasks can be easily run through the grid:
- Routine server administration tasks, such as adding chunks, removing logical logs, or changing configuration parameter settings
- Updating the database schema, such as altering tables or adding tables
- Running or creating stored procedures or user-defined routines
- Updating data, such as purging old data or updating values based on conditions
- Enabling replication when creating a table
- Altering a replicate definition when altering a replicated table
Several commands and procedures are unique to the grid environment. In the sections below on migrating to the grid and in some of the examples, you'll see these commands in use.
cdr define grid— Creates a grid with given name and adds all replication servers in the domain as members of the gridcdr enable grid— Authorizes users to run commands on the grid and designate server from which grid commands can be runcdr list grid— Shows the servers and authorized users in the gridcdr change grid— To add and remove replication servers from the gridcdr disable grid— To remove users or servers that are authorized to run grid routines
ifx_grid_connect()— Connect to grid to run one or more SQL DDL statementsifx_grid_disconnect()— Disconnect from gridifx_grid_function()— Run SQL administration API commands in grid routines while connected to sysadmin database
The first step in moving to Informix flexible grid is to migrate your
servers to Informix 11.7. As an example, say you have three ER servers set
up and one replicate in your existing 11.50 server. The replicate is
created on the testdb:testtab table.
Figure 2. Replication domain example
Listing 1 shows the environment.
Listing 1. List existing ER servers
List existing ER servers >>> $ cdr list server SERVER ID STATE STATUS QUEUE CONNECTION CHANGED ----------------------------------------------------------------------- g_cdr1 10 Active Local 0 g_cdr2 20 Active Connected 0 Nov 24 14:06:52 g_cdr3 30 Active Connected 0 Nov 24 14:07:03 List existing replicate in your ER setup >>> $ cdr list repl Repl1 DEFINED REPLICATES ATTRIBUTES ------------------------------ REPLICATE: Repl1 STATE: Active ON:g_cdr1 CONFLICT: Ignore FREQUENCY: immediate QUEUE SIZE: 0 PARTICIPANT: testdb:usr1.testtab OPTIONS: transaction,ris,ats,floatcanon,fullrow REPLID: 655364 / 0xa0004 REPLMODE: PRIMARY ON:g_cdr1 APPLY-AS: INFORMIX ON:g_cdr1 REPLTYPE: Master $ dbaccess testdb - Database selected. > info columns for testtab; Column name Type Nulls col1 integer no col2 char(10) yes col3 varchar(30,0) yes |
Server migration and ER migration
Make sure you have completed all backup steps, including backup of the syscdr database. This is to ensure that you can restore the server if migration fails. Perform the following steps on all three servers consecutively.
NOTE: When you are migrating the first server (Cdr1), users can continue to access and manipulate data on Cdr2 and Cdr3. Any data that has changed while Cdr1 is unavailable for the migration will be stored in the queue until Cdr1 is back online.
- After you have completed the database backup, run
cdr stopon the server to stop the replication on the server. This will stop any data from replicating from g_cdr2 and g_cdr3 to g_cdr1. All data will be queued in CDR_QDATASPACE.
Figure 3. Replication
- Bring down the Cdr1 server:
onmode -kuy. - Copy your server environment files (ONCONFIG and INFORMIXSQLHOSTS) to your newly installed 11.70 location.
- Set your new server environment: INFORMIXSERVER, ONCONFIG, INFORMIXSQLHOSTS, INFORMIXDIR, PATH.
- Bring up Cdr1 with new server environment.
- Check the server's log to make sure all databases are converted successfully.
- Run
concdr.shto migrate syscdr databases:sh -x $INFORMIXDIR/etc/conv/concdr.sh 11.50 11.70. - Run
cdr startto start ER. This will resume the replication between Cdr2 and Cdr3. All data stored in queue will be replicated to Cdr1 as well.
Repeat all the steps on Cdr2 and on Cdr3.
Converting your existing ER domain to grid domain
Now that you have converted the server to 11.70, you will convert the existing ER domain to grid:
- Create a grid named Grid_World to include all the ER nodes:
cdr define grid Grid_World -a. Now that you have defined a grid with Grid_World, cdr list grid returns ER nodes in the grid.
Grid Node User ------------------------ ------------------------ ------------------------ Grid_World g_cdr1 g_cdr2 g_cdr3
Figure 4. Grid_World
- Enable the grid with user usr1 and Informix on ER node g_cdr1.
cdr enable grid -g Grid_World -u usr1 -u informix -n g_cdr1 $ cdr list grid Grid Node User ------------------------ ------------------------ ------------------------ Grid_World g_cdr1* informix usr1 g_cdr2 g_cdr3 - Add replicates to grid replicate set by running the
cdr change replsetcommand.$ cdr change replset Grid_World -a Repl1 $ cdr list replset Ex T REPLSET PARTICIPANTS ----------------------------------------------- N N Grid_World Repl1
You now have a grid domain, Grid_World, which includes one replicate and three ER node servers.
Here are some of the examples to show the advantages of using the grid feature.
Example 1: Add a column (col4) to a replicate table
In the first example, you add a column (col4) into replicate table testtab
in the testdb database. Using the grid, you just need to execute the
alter table SQL statement on the server where
you have enabled the grid. The SQL statement will be propagated to the other
servers in the grid. Run the commands on Cdr1, where the grid is
enabled.
Listing 2. Adding a column to a replicate table
$ dbaccess testdb -
Database selected.
> info columns for testtab;
Column name Type Nulls
col1 integer no
col2 char(10) yes
col3 varchar(30,0) yes
Run procedure ifx_grid_connect to connect to the grid and run alter table SQL statement
> execute procedure ifx_grid_connect('Grid_World','Add column',1);
Routine executed.
> alter table testtab add col4 char(50) default null;
Table altered.
Run procedure ifx_grid_disconnect to disconnect from grid
> execute procedure ifx_grid_disconnect();
Routine executed.
> info columns for testtab;
Column name Type Nulls
col1 integer no
col2 char(10) yes
col3 varchar(30,0) yes
col4 char(50) yes
Verify column, col4 is added on table in ER node Cdr2 and Cdr3
> $ dbaccess bank@g_cdr2 -
Database selected.
> info columns for testtab;
Column name Type Nulls
col1 integer no
col2 char(10) yes
col3 varchar(30,0) yes
col4 char(50) yes
> $ dbaccess bank@g_cdr3 -
Database selected.
> info columns for testtab;
Column name Type Nulls
col1 integer no
col2 char(10) yes
col3 varchar(30,0) yes
col4 char(50) yes
|
This example adds a dbspace (dbsp1) with a size of 500M and offset 0 to the
servers participating in the grid. The
create dbspace command that runs on Cdr1 will
be propagated to other servers in the grid. Run the commands on the Cdr1
server, where grid is enabled.
Listing 3. Adding a dbspace that will be propagated to the grid
$ dbaccess sysadmin -
Database selected.
Run procedure ifx_grid_function to connect to grid and run "create dbspace" admin command
>execute function ifx_grid_function('Grid_World', 'task("create dbspace",
"dbsp1", "/tmp/dbsp1","500M","0")');
(expression) 'Space 'dbsp1' added.'
1 row(s) retrieved.
|
This example shows how to create a table (tab_new) in the testdb
database. Specify option 1 to ifx_grid_connect with the
create table command to create the replicate on
the table automatically. Run the commands on Cdr1 server, where you have
enabled the grid.
Listing 4. Creating a table and creating the replicate automatically
$ dbaccess testdb -
Database selected.
Run procedure ifx_grid_connect to connect to the grid and run create table SQL statement
> execute procedure ifx_grid_connect('Grid_World','Create new table tab_new',1);
Routine executed.
> create table tab_new ( col1 int, col2 char(30), col3 date );
Table created.
Run procedure ifx_grid_disconnect to disconnect from grid
> execute procedure ifx_grid_disconnect();
Routine executed.
$ dbaccess testdb@g_er_qa_2 -
Database selected.
> info columns for tab_new;
Column name Type Nulls
col1 integer yes
col2 char(30) yes
col3 date yes
ifx_erkey_1 integer yes
ifx_erkey_2 integer yes
ifx_erkey_3 smallint yes
>> Verify replicate is created on tab_new table.
The replicate created by grid starts with "grid_xxxxxx"
$ cdr list repl grid_655367_10_5
DEFINED REPLICATES ATTRIBUTES
------------------------------
REPLICATE:grid_655367_10_5
STATE: Active ON:g_er_qa_1
CONFLICT:Always Apply
FREQUENCY:immediate
QUEUE SIZE: 0
PARTICIPANT: testdb:informix.tab_new
OPTIONS: row,ris,fullrow
REPLID:655369 / 0xa0009
REPLMODE: PRIMARY ON:g_er_qa_1
APPLY-AS:INFORMIX ON:g_er_qa_1
REPLTYPE:Master,Grid
>> cdr list replset shows that replicate grid_655367_10_5 is
added to Grid_World
$ cdr list replset
Ex T REPLSET PARTICIPANTS
-----------------------------------------------
N N Grid_World Repl1, grid_655367_10_5
|
Grid monitoring with OpenAdmin Tool (OAT)
You can easily monitor the grid with the IBM OpenAdmin Tool (OAT) for Informix, a free web-based management tool that allows you to monitor and administer Informix servers through a browser.
- After you log in to OAT, click the Replication tab,
then choose Grid.
Figure 5. Replication
- Click Actions, then Create
Grid to create the grid.
Figure 6. Grid menu
- Enter the grid name, and it lists the available Informix servers.
Choose a server that can be a part of grid and add to the grid
servers list by clicking Add tab.
Figure 7. Adding servers to the grid
NOTE: Informix servers must be at V11.70. There can be old version servers within the ER domain, but they cannot be part of the grid.
- Choose the nodes within the grid that can be used to perform
grid-level operation, then click Next.
Figure 8. Adding notes to the grid
- Specify the users allowed to perform grid operations and
click Add, then click Next.
Figure 9. Adding users
- After you click Next, the screen displays the
corresponding SQL and CDR commands to define and enable grid for users
authorized to perform grid operations.
Figure 10. SQL and CDR commands
- Click Finish.
Figure 11. Completing grid creation
Figure 12 shows source server and participant servers that are part of the grid.
Figure 12. Grid servers
Using the grid connection manager
The connection manager redirects the client applications to the appropriate server based on following factors specified in the Service-Level Agreement (SLA) policy:
- Lowest latency
- Fewest apply failures
- Lowest workload
- Node availability
The default is weighted equally for each factor. Using the Actions button, you can add an SLA to the list or modify the existing SLA.
Figure 13. Adding or modifying the SLA
The exercise below highlights the steps to modify an existing SLA.
- Choose the SLA that needs to be modified and click Modify
SLA.
Figure 14. Modify SLA
- To add a policy to an SLA, check the SLA
policy box. You can then update relative weights to each policy by
simply moving the sliders. Click on Next to save the
changes.
Figure 15. Adding a policy
- Figure 16 shows the modified SLA command. Click
Finish to log this entry into server system
catalog.
Figure 16. Modified SLA
The SLA was modified successfully.
Figure 17. Modification completed
This completes the connection manager SLA modification.
Figure 18. SLA changes
To view commands that ran on the grid source node:
- Click the Status tab. This will display all the
grid commands, the user who ran the command, and the start and end times of the
command execution.
Figure 19. Grid command status
- If you need more information on any of these commands and their
results on each server, click +.
Figure 20. Grid command details
- To find out the command that ran on source and target
nodes, double-click on the server.
Figure 21. Server details
Enabling one more node as a source node of the grid
- Choose the source node.
Figure 22. Enabling source node
- Choose the server where you want to enable grid operations and click
the Actions drop-down menu. Then choose Enable
Source Server. In the example below, we are choosing g_er3
node.
Figure 23. Choosing g_er3 node
- Now a window pops up, displaying the corresponding SQL and CDR
commands to complete the operation. Click the Enable
tab.
Figure 24. Confirmation message
- You should see a message confirming that the server is now a source
server in the grid.
Figure 25. Success message
This completes enabling a new node as a grid server.
Monitoring failed grid commands and re-running them
In this example, you see that the "Create table" statement failed to propagate on target servers.
Figure 26. Failed grid command
Click on the node where the command failed and it pops up the actual SQL command with its result.
Figure 27. Failed command details
You can rerun the failed command on a node by choosing the server and clicking on the Action drop-down menu, then Rerun Command.
Figure 28. Failed commands
Figure 29. Re-running failed commands
The flexible grid in Informix 11.70 simplifies administration and provides enhanced availability and workload management. It provides one of the industry's most comprehensive set of high-availabilty options in a platform-independent environment. This article illustrated the usefulness of the flexible grid feature in today's world where multiple database servers dispersed geographically can be managed as easily as a single server from a single location. It also gives us the flexibility to add or remove servers as necessary. Monitoring the grid is easily accomplished with IBM OpenAdmin Tool, a free web-based management tool that allows you to monitor and administer grid servers through a browser. This article has given you the information to understand how quick and easy it is to set up an Informix flexible grid.
Learn
- Refer to the Informix Information Center for details on preparing to migrate
an Enterprise Replication environment.
- Read the "Manage
your Informix database with the IDS OpenAdmin Tool" series to learn how the OpenAdmin
Tool makes Informix server administration more user-friendly.
- Stay current with developerWorks Informix page. Find articles and tutorials, and
connect to other resources to expand your Informix skills.
- Learn more about Information Management at the developerWorks Information Management
zone. Find technical documentation,
how-to articles, education, downloads, product information, and
more.
- Stay current with
developerWorks technical events and webcasts.
- Follow developerWorks on
Twitter.
Get products and technologies
- Download a Trial version of Informix.
- Build your next
development project with
IBM trial software,
available for download directly from developerWorks.
Discuss
- Participate in the discussion forum.
- Check out the
developerWorks
blogs and get involved in the
developerWorks community.
Wee Sen Tung is a software engineer who has been working on IBM's Informix Server for the past 10 years. He was a QA member of the team that supports Informix availability technologies, which include Enterprise Replication and high-availability clusters.
Harshavardhan Changappa is a certified systems administrator for Informix databases. He has been working with the Informix development team for over four years now. He has worked with quality assurance and development teams and has been instrumental in developing functional and Integration test cases for various features of IDS. He's widely recognized for his contribution in the areas of IDS replication and high-availability, like flexible grid, transactional survival, ER log lag action, etc.
Bharath Sriram worked with the integration team and on OAT while at IBM. He is certified in systems administration for IBM Informix Dynamic Server V11. He holds a master's degree in computer science from Ohio State University. His research interests include information retrieval and text mining in social networks.
