Can an HDR Secondary know if RSS is setup on Prima
A user with one stored procedure deployed on an HDR primary, HDR secondary and RSS server needed the copy running on the HDR secondary to detect whether RSS was configured, since sysmaster:syssrcrss/systrgrss are empty there. Suggestions: query the primary's tables remotely via dynamic SQL (Art Kagel); use sysmaster:sysha_nodes on the primary and sysmaster:sysha_type (ha_primary) on the secondary; or, on the secondary, "select state from sysha:rss_tab" (C = connected, D = disconnected). It was also clarified that the sysha database is only created when an RSS or SDS environment is first set up, which explains its absence on non-RSS servers. The user reported this solved his problem.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello, I have a stored prcedure which exists on three replicating databases (HDR Primary, HDR Secondary and the RSS server). This stored procedure gets called on each server and performs different tasks depending on what mode the server is in and whether RSS is configured. It works this out dynamically (thanks to those to helped in my last question) meaning that any changes to the server roles do not break the procedure. I have met a problem though. when the stored procedure is on the HDR Secondary it needs to know if RSS is running or even just configured between the Primary and the RSS server, as this changes what it should do. How can my stored procedure on the Secondary know if RSS is configured? sysmaster:syssrcrss and sysmaster:systrgrss are obviously empty on the Secondary whilst there is data in those tables on the Primary and RSS server. Thank you in advance.
Access the tables remotely from the HDR secondary to the primary's copies. You can use Dynamic SQL to build the correct query depending on which server you are on. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf 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 Thu, Apr 8, 2010 at 9:06 AM, ANDREW LEMIN <a_lemin@hotmail.com> wrote: > Hello, > I have a stored prcedure which exists on three replicating databases (HDR > Primary, HDR Secondary and the RSS server). > > This stored procedure gets called on each server and performs different > tasks > depending on what mode the server is in and whether RSS is configured. > > It works this out dynamically (thanks to those to helped in my last > question) > meaning that any changes to the server roles do not break the procedure. > > I have met a problem though. when the stored procedure is on the HDR > Secondary > it needs to know if RSS is running or even just configured between the > Primary > and the RSS server, as this changes what it should do. > > How can my stored procedure on the Secondary know if RSS is configured? > > sysmaster:syssrcrss and sysmaster:systrgrss are obviously empty on the > Secondary whilst there is data in those tables on the Primary and RSS > server. > > Thank you in advance. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c927c1e823d70483b9a6ba
Primary servers has info about all the nodes in the cluster, so you can get that info by doing "select * from sysmaster:sysha_nodes". On HDR secondary, you can find out the current primary by doing "select ha_primary from sysmaster:sysha_type" - Nilesh - ids-bounces@iiug.org wrote on 04/08/2010 08:28:22 AM: > From: > > "Art Kagel" <art.kagel@gmail.com> > > To: > > ids@iiug.org > > Date: > > 04/08/2010 08:29 AM > > Subject: > > Re: Can an HDR Secondary know if RSS is setup .... [19570] > > Sent by: > > ids-bounces@iiug.org > > Access the tables remotely from the HDR secondary to the primary's copies. > You can use Dynamic SQL to build the correct query depending on which server > you are on. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > > See you at the 2010 IIUG Informix Conference > April 25-28, 2010 > Overland Park (Kansas City), KS > www.iiug.org/conf > > 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 Thu, Apr 8, 2010 at 9:06 AM, ANDREW LEMIN <a_lemin@hotmail.com> wrote: > > > Hello, > > I have a stored prcedure which exists on three replicating databases (HDR > > Primary, HDR Secondary and the RSS server). > > > > This stored procedure gets called on each server and performs different > > tasks > > depending on what mode the server is in and whether RSS is configured. > > > > It works this out dynamically (thanks to those to helped in my last > > question) > > meaning that any changes to the server roles do not break the procedure. > > > > I have met a problem though. when the stored procedure is on the HDR > > Secondary > > it needs to know if RSS is running or even just configured between the > > Primary > > and the RSS server, as this changes what it should do. > > > > How can my stored procedure on the Secondary know if RSS is configured? > > > > sysmaster:syssrcrss and sysmaster:systrgrss are obviously empty on the > > Secondary whilst there is data in those tables on the Primary and RSS > > server. > > > > Thank you in advance. > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001636c927c1e823d70483b9a6ba > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Hello, thanks for your reply. I thought that a stored procedure executed on a Secondary database was unable to connect to a remote database and start retrieving data from a different engine? If this is in fact possible could you please provide an example on the CALL statement syntax for the stored procedure as when I tried this in the past I could never get it working? Thank you again for all your time and help. Andy.
Alternately, on HDR secondary you can do "select state from sysha:rss_tab" to get the status of an RSS in the cluster. where state: C - connected D - disconnected - Nilesh - ids-bounces@iiug.org wrote on 04/08/2010 10:39:23 AM: > From: > > "ANDREW LEMIN" <a_lemin@hotmail.com> > > To: > > ids@iiug.org > > Date: > > 04/08/2010 10:40 AM > > Subject: > > Re: Can an HDR Secondary know if RSS is setup on P [19573] > > Sent by: > > ids-bounces@iiug.org > > Hello, thanks for your reply. > > I thought that a stored procedure executed on a Secondary database was unable > to connect to a remote database and start retrieving data from a different > engine? > > If this is in fact possible could you please provide an example on the CALL > statement syntax for the stored procedure as when I tried this in the past I > could never get it working? > > Thank you again for all your time and help. > Andy. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >
Thats great. Thank you very much. I am almost there now :o) I have noticed that the 'sysha' database does not seem to exist on all of our database systems. It exists on all the systems with RSS, but appears to not exist on those systems without RSS? Is this correct? When does the sysha database exist/get created? Thank you very much for your time and help.
sysha is created only when it is needed - and that would be when you fi= rst create a RSS or SDS environment. = From: "ANDREW LEMIN" <a_lemin@hotmail.com> = = To: ids@iiug.org = = Date: 04/09/2010 06:10 AM = = Subject: Re: Can an HDR Secondary know if RSS is setup on P [1958= 3] = Sent by: ids-bounces@iiug.org = = Thats great. Thank you very much. I am almost there now :o) I have noticed that the 'sysha' database does not seem to exist on all = of our database systems. It exists on all the systems with RSS, but appears to= not exist on those systems without RSS? Is this correct? When does the sysha database exist/get created? Thank you very much for your time and help. ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =