client connection to a multi connection manager sy
Fernando ran two Connection Managers with group (SLA) entries in sqlhosts, but his JDBC URL hard-coded host1:2121, so clients always went through CM1 and lost access when it failed. Jim Xiao showed that the Informix JDBC driver can resolve the group itself by omitting host/port and using e.g. jdbc:informix-sqli:informixserver=oltp_group;...;SQLH_TYPE=FILE;SQLH_FILE=/path/sqlhosts. That connected round-robin but gave "Database not selected yet"; the fix was to add database=mydb as a connection property (or issue a DATABASE statement after connecting). Art Kagel also pointed to an IBM technote on client-side group failover.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello I have one java application that connects to informix server through connection managers, but even when I have more that one connection manager, when one of the fails I cannot connect anymore with my java application. I have two connection managers , and two informix databases servers, This is the sqlhosts file that I have in my two computers that run the connection managers oltp_group group - - i=13, c=1 oltp1 onsoctcp host1 2121 g=oltp_group oltp2 onsoctcp host2 2121 g=oltp_group reports_group group - - i=14, c=1 reports1 onsoctcp host1 2626 g=reports_group reports2 onsoctcp host2 2626 g=reports_group As you can see, I created the SLA oltp_group and the SLA reports_group, each of those SLA are linked to other SLA. This is the configuration file for my connection manager 1 that runs in the host 1: NAME cm_example1 LOG 1 LOGFILE cmlog DEBUG 1 CLUSTER mycluster_1 { INFORMIXSERVER server1, server2 SLA oltp1 DBSERVERS=primary,HDR SLA reports1 DBSERVERS=primary FOC ORDER=DISABLE TIMEOUT=0 RETRY=1 } This is the configuration file for my connection manager 2 that runs in the host 2: NAME cm_example2 LOG 1 LOGFILE cmlog DEBUG 1 CLUSTER mycluster_2 { INFORMIXSERVER server1, server1 SLA oltp2 DBSERVERS=primary, HDR SLA reports2 DBSERVERS=primary FOC ORDER=DISABLE TIMEOUT=0 RETRY=1 } And here comes the problem. For example, When my java application connects to the URL= host1, oltp_group, 2121 the connection manager always send me to oltp1. The connection manager is always sending me to oltp1 because in my java application I have host1 in my url, and if my connection manager 1 fails, I will not be able to connect to oltp_group even when my connection manager 2 is working and my two informix server are working. So I am not achieving any redundancy!!!!, How can I use my connection manager or how can I connect from my java application to oltp_gruop even if one connection manager fails??? Thanks in advance
with Informix JDBC Driver, you can set the INFORMIXSERVER to the group name in the connection URL as "jdbc:informix-sqli:informixserver=oltp_group; user=foo;password=XXXX;SQLH_TYPE=FILE; SQLH_FILE=/informix/sqlhosts"
just to be more specific, this is how I specify my url in my java application: JDBCurl = "jdbc:informix-sqli://host1:2121/database1:INFORMIXSERVER=oltp_group;user=unknow ;password=12345" ; so Can I connect without the "host1" information?? How will the java application know where to go???
Thank, Just a couple of questions. What does this usl section indicates??? "SQLH_TYPE=FILE;SQLH_FILE=/informix/sqlhosts" Does this mean that the application has to go to the sqlhosts file to get the hosts addresses ????
right. I tested this and it works String url ="jdbc:informix-sqli:informixserver=oltp;user=informix; password=xxxxxxxx;SQLH_TYPE=FILE;SQLH_FILE=/work/test/sqlhosts.cm"; /work/test/sqlhosts.cm oltp group - - i=10,e=oltp2 oltp1 onsoctcp argo 9910 g=oltp oltp2 onsoctcp apollo11 9912 g=oltp
And how do you specify the database name???? jdbc:informix-sqli:INFORMIXSERVER=oltp_group;user=unknow;password=12345;SQLH_TYP E=FILE;SQLH_FILE=/informix/sqlhosts" Because in the url we are specifying the user, the password and the file where the host and services level agreement can be found, but where do we specify the database??? When I run the java application using the new url, I get a error: java.sql.SQLException: Database not selected yet.
Yes but you are not using the connection manager ant longer, just failing over to the secondary directly. In transit, when I get home I will send you a link to a developer works article that shows how to make this work with connection manager. Art On Apr 27, 2012 3:59 PM, "JIM XIAO" <jxiao@us.ibm.com> wrote: > right. > > I tested this and it works > > String url ="jdbc:informix-sqli:informixserver=oltp;user=informix; > password=xxxxxxxx;SQLH_TYPE=FILE;SQLH_FILE=/work/test/sqlhosts.cm"; > > /work/test/sqlhosts.cm > oltp group - - i=10,e=oltp2 > oltp1 onsoctcp argo 9910 g=oltp > oltp2 onsoctcp apollo11 9912 g=oltp > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340661ff283f04beaf540d
Fernando: This is a link to the IBM Technote article that explains how to make a windows client PC aware of a group entry for failover between two connection managers or servers: http://www-01.ibm.com/support/docview.wss?uid=swg21447622 Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Apr 27, 2012 at 2:19 PM, FERNANDO GUERRERO <fernando_gr60@yahoo.com>wrote: > Hello > > I have one java application that connects to informix server through > connection managers, but even when I have more that one connection manager, > when one of the fails I cannot connect anymore with my java application. > > I have two connection managers , and two informix databases servers, > > This is the sqlhosts file that I have in my two computers that run the > connection managers > > oltp_group group - - i=13, c=1 > > oltp1 onsoctcp host1 2121 g=oltp_group > > oltp2 onsoctcp host2 2121 g=oltp_group > > reports_group group - - i=14, c=1 > > reports1 onsoctcp host1 2626 g=reports_group > > reports2 onsoctcp host2 2626 g=reports_group > > As you can see, I created the SLA oltp_group and the SLA reports_group, > each > of those SLA are linked to other SLA. > > This is the configuration file for my connection manager 1 that runs in the > host 1: > > NAME cm_example1 > > LOG 1 > > LOGFILE cmlog > > DEBUG 1 > > CLUSTER mycluster_1 > > { > > INFORMIXSERVER server1, server2 > > SLA oltp1 DBSERVERS=primary,HDR > > SLA reports1 DBSERVERS=primary > > FOC ORDER=DISABLE TIMEOUT=0 RETRY=1 > > } > > This is the configuration file for my connection manager 2 that runs in the > host 2: > > NAME cm_example2 > > LOG 1 > > LOGFILE cmlog > > DEBUG 1 > > CLUSTER mycluster_2 > > { > > INFORMIXSERVER server1, server1 > > SLA oltp2 DBSERVERS=primary, HDR > > SLA reports2 DBSERVERS=primary > > FOC ORDER=DISABLE TIMEOUT=0 RETRY=1 > > } > > And here comes the problem. For example, When my java application connects > to > the URL= host1, oltp_group, 2121 the connection manager always send me to > oltp1. The connection manager is always sending me to oltp1 because in my > java > application I have host1 in my url, and if my connection manager 1 fails, I > will not be able to connect to oltp_group even when my connection manager > 2 is > working and my two informix server are working. So I am not achieving any > redundancy!!!!, How can I use my connection manager or how can I connect > from > my java application to oltp_gruop even if one connection manager fails??? > > Thanks in advance > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340d59e7c37504bee4090e
Thanks Kagel, I already read the article that you send me, but my problem is how to connect to the informix connection manager from a java program as a client. According to this link http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp?topic=%2Fcom.ibm .admin.doc%2Fids_admin_1173.htm We have the connections managers, the informix database servers and the clients, The connections managers are already working. But when I write a java program to access a database I have to specify an URL like this one: "jdbc:informix-sqli://host1:2121/databse1:INFORMIXSERVER=oltp_group;user=informi x;password=12345" ; In this URL I specify the hostname (host1), the port (2121), the database name (database1), the informix server name (oltp_group) , the user and the password. If I write an url like the previous one, where my informix server is the SLA group name that I specified in my sqlhosts, I would be falling always to the informix server that is in host1, so I am not taking advantage of the failover feature. JIM XIAO, gave a different way to write the url in my java code, I already tested it and I could see the my java client connected randomly to all the informix servers that belong to the same group, unfortunately in the url format the JIM XIAO sent my, we are not specifying the database, so once I have connected to the informix server and I try to retrieve some data, I get the java error: java.sql.SQLException: Database not selected yet. Even If I shutdown one connection manager the other connection manager is sending my to all my informix servers (so far so good), but I am missing the database.
If you are connecting successfully to the server, you should be able to execute the extended SQL statement "DATABASE databse1;" to open the databse1 database within the connection you already have. Informix permits changing databases without dropping the connection. It should work in Java using which ever method you would use for any other SQL that does not return any data. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Apr 30, 2012 at 11:01 AM, FERNANDO GUERRERO <fernando_gr60@yahoo.com > wrote: > Thanks Kagel, > > I already read the article that you send me, but my problem is how to > connect > to the informix connection manager from a java program as a client. > > According to this link > > http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp?topic=%2Fcom.ibm .admin.doc%2Fids_admin_1173.htm > > We have the connections managers, the informix database servers and the > clients, > > The connections managers are already working. But when I write a java > program > to access a database I have to specify an URL like this one: > > > > "jdbc:informix-sqli://host1:2121/databse1:INFORMIXSERVER=oltp_group;user=informi x;password=12345" > ; > > In this URL I specify the hostname (host1), the port (2121), the database > name > (database1), the informix server name (oltp_group) , the user and the > password. > If I write an url like the previous one, where my informix server is the > SLA > group name that I specified in my sqlhosts, I would be falling always to > the > informix server that is in host1, so I am not taking advantage of the > failover > feature. > > JIM XIAO, gave a different way to write the url in my java code, I already > tested it and I could see the my java client connected randomly to all the > informix servers that belong to the same group, unfortunately in the url > format the JIM XIAO sent my, we are not specifying the database, so once I > have connected to the informix server and I try to retrieve some data, I > get > the java error: > > java.sql.SQLException: Database not selected yet. > > Even If I shutdown one connection manager the other connection manager is > sending my to all my informix servers (so far so good), but I am missing > the > database. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340d59e9e91404bee704d5
or try Connection Property 'database' String url ="jdbc:informix-sqli:informixserver=oltp;database=mydb;user=informix;password=xx xxxxxx;SQLH_TYPE=FILE;SQLH_FILE=/work/test/sqlhosts.cm";