How to find out 'mode' of IDS through sysmaster
The poster wanted a stored procedure to determine the server's role/mode (Primary, HDR Secondary, SD Secondary, RSS) without using 'onstat -'. Suggestions included sysmaster:sysshmvals.sh_mode (with the 0-6 online/quiescent/etc. value list), the syslicenseinfo table/sysfeatures view, checking SQLCA.SQLWARN[7] for 'W' on pre-v11 to detect a read-only secondary, and sysmaster:sysha_type. He settled on SELECT ha_type FROM sysmaster:sysha_type (1=Primary, 2=HDR Secondary, 3=SD Secondary, 4=RSS), which covered all his cases.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello,
Is it possible to find out the mode of the Informix database I have connected
to through sysmaster?
I have some stored procedures and they need to know what mode the server is in
(Primary/Secondary/RSS).
Obviously I can do this with 'onstat -' but I need to find this out from
within a stored procedure?
> select sh_mode from sysmaster:sysshmvals;
sh_mode
5
1 row(s) retrieved.
Regards,
-- Mirav
=
From: "ANDREW LEMIN" <a_lemin@hotmail.com> =
=
To: ids@iiug.org =
=
Date: 04/07/2010 09:17 AM =
=
Subject: How to find out 'mode' of IDS through sysmaster [19532] =
=
Sent by: ids-bounces@iiug.org =
=
Hello,
Is it possible to find out the mode of the Informix database I have
connected
to through sysmaster?
I have some stored procedures and they need to know what mode the serve=
r is
in
(Primary/Secondary/RSS).
Obviously I can do this with 'onstat -' but I need to find this out fro=
m
within a stored procedure?
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=
The values returned by 'onstat -' are:
-1/255 Offline
0 Initialisation
1 Quiescent
2 Recovery
3 Backup
4 Shutdown
5 Online
6 Abort
On Wed, Apr 7, 2010 at 7:54 PM, Mirav Kapadia <mirav@us.ibm.com> wrote:
> > select sh_mode from sysmaster:sysshmvals;>
> sh_mode
>
> 5
>
> 1 row(s) retrieved.
>
> Regards,
>
> -- Mirav
>
> =
>
> From: "ANDREW LEMIN" <a_lemin@hotmail.com> =
>
> =
>
> To: ids@iiug.org =
>
> =
>
> Date: 04/07/2010 09:17 AM =
>
> =
>
> Subject: How to find out 'mode' of IDS through sysmaster [19532] =
>
> =
>
> Sent by: ids-bounces@iiug.org =
>
> =
>
> Hello,
>
> Is it possible to find out the mode of the Informix database I have
> connected
> to through sysmaster?
>
> I have some stored procedures and they need to know what mode the serve=
> r is
> in
> (Primary/Secondary/RSS).
>
> Obviously I can do this with 'onstat -' but I need to find this out fro=
> m
> within a stored procedure?
>
> ***********************************************************************=
> ********
>
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
> =
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001636e0b6213d48780483a68adb
Hi,
What you want is in the syslicenseinfo table. Take a look at the
definition of the sysfeatures view in INFORMIXDIR/etc/sysmaster.sql to see
how the flags in syslicenseinfo should be interpretted. The sysfeatures
view is restricted to user informix, but the underlying syslicenseinfo
table appears to be open to the public. Note that you have to restrict
the select since there's a row in the table for each week the instance has
existed.
Cheers,
Dick Snoke
Executive IT Specialist
IBM Software Group - ChannelWorks
Tel: (404) 487-1595
Email: dsnoke@us.ibm.com
From:
"ANDREW LEMIN" <a_lemin@hotmail.com>
To:
ids@iiug.org
Date:
04/07/10 10:17 AM
Subject:
How to find out 'mode' of IDS through sysmaster [19532]
Sent by:
ids-bounces@iiug.org
Hello,
Is it possible to find out the mode of the Informix database I have
connected
to through sysmaster?
I have some stored procedures and they need to know what mode the server
is in
(Primary/Secondary/RSS).
Obviously I can do this with 'onstat -' but I need to find this out from
within a stored procedure?
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
IB that the mode is stored in the sysmaster:sysshmvals pseudo-table. Don't
have access to a server right now.
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 Wed, Apr 7, 2010 at 10:16 AM, ANDREW LEMIN <a_lemin@hotmail.com> wrote:
> Hello,
>
> Is it possible to find out the mode of the Informix database I have
> connected
> to through sysmaster?
>
> I have some stored procedures and they need to know what mode the server is
> in
> (Primary/Secondary/RSS).
>
> Obviously I can do this with 'onstat -' but I need to find this out from
> within a stored procedure?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001636b2b9608ad1220483a76b3f
> From: "ANDREW LEMIN" <a_lemin@hotmail.com>
> To: ids@iiug.org
> Date: 04/07/2010 09:17 AM
>
> Subject:
>
> How to find out 'mode' of IDS through sysmaster [19532]
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Hello,
>
> Is it possible to find out the mode of the Informix database I have
connected
> to through sysmaster?
>
> I have some stored procedures and they need to know what mode the
> server is in
> (Primary/Secondary/RSS).
select ha_type from sysmaster:sysha_type
refer --
http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.adref.doc/id
s_adr_0231.htm
- Nilesh -
>
> Obviously I can do this with 'onstat -' but I need to find this out from
> within a stored procedure?
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
In addition if you are on version prior to version 11 and
want to know if you have a secondary server check SQLCA.SQLWARN 7
setting immediately after a database statement. If this field is a 'W'=
then
you have a read only secondary server.
http://publib.boulder.ibm.com/infocenter/idshelp/v115/index.jsp?topic=3D=
/com.ibm.sqls.doc/ids_sqs_0652.htm
John F. Miller III
STSM, Support Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
=
From: Nilesh Ozarkar/Lenexa/IBM@IBMUS =
=
To: ids@iiug.org =
=
Date: 04/07/2010 08:49 AM =
=
Subject: Re: How to find out 'mode' of IDS through sysm.... [1953=
8]
=
Sent by: ids-bounces@iiug.org =
=
> From: "ANDREW LEMIN" <a_lemin@hotmail.com>
> To: ids@iiug.org
> Date: 04/07/2010 09:17 AM
>
> Subject:
>
> How to find out 'mode' of IDS through sysmaster [19532]
>
> Sent by:
>
> ids-bounces@iiug.org
>
> Hello,
>
> Is it possible to find out the mode of the Informix database I have
connected
> to through sysmaster?
>
> I have some stored procedures and they need to know what mode the
> server is in
> (Primary/Secondary/RSS).
select ha_type from sysmaster:sysha_type
refer --
http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.adr=
ef.doc/ids_adr_0231.htm
- Nilesh -
>
> Obviously I can do this with 'onstat -' but I need to find this out f=
rom
> within a stored procedure?
>
>
>
***********************************************************************=
********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=
Wow, Thank you very much everyone for all your suggestions. I didn't realise there was so many ways round the problem. I the end I have gone with; SELECT ha_type INTO iMode FROM sysmaster:sysha_type; -- 1 = Primary -- 2 = HDR Secondary -- 3 = SD Secondary -- 4 = RSS I went with this because my stored procedure could be on an HDR Primary, HDR Secondary or an RSS Server. Thank you all for your help.