query sysmaster db

Posted in 2007

Summary

Frank (AIX 5.3, IDS 10.UC5) reported that his sysmaster monitoring query joining syssqlstat and syssessions, which filters out sqs_dbname = 'sysmaster', began returning its own SELECT text as the statement for every session after the upgrade. Jack Parker suggested adding "and sqs_sessionid != dbinfo('sessionid')", which worked on his own 10.UC5 system, but Frank's output was unchanged. Jack then noted the output showed two different session IDs reporting the same query and odd non-sysmaster sqs_dbname values, suspecting the self-row was still being picked up. The thread ends there with no resolution recorded.

Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.

Topics: General Discussion

FRANK — Mon, 29 Jan 2007 16:02:56 — source: IIUG Forums & Mailing Lists

AIX5.3 IDS 10 UC5. The following sysmaster database query used to work well, but it does not after we move to IDS 10 UC5. The purpose of the query is to report all user's SQL statements. But now( on IDS UC5), the result includes itself ( the following statement) for every user session. Misleading! select a.sqs_sessionid, a.sqs_dbname, b.uid, b.username,b.pid,b.hostname,a.sqs_statement from syssqlstat a, syssessions b where a.sqs_sessionid=b.sid and sqs_dbname <>'sysmaster' Thanks Frank

↪ replying to FRANK

Jack Parker — Mon, 29 Jan 2007 16:12:35 — source: IIUG Forums & Mailing Lists

and sqs_sessionid != dbinfo('sessionid') j. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of FRANK Sent: Monday, January 29, 2007 11:03 AM To: ids@iiug.org Subject: query sysmaster db [8313] AIX5.3 IDS 10 UC5. The following sysmaster database query used to work well, but it does not after we move to IDS 10 UC5. The purpose of the query is to report all user's SQL statements. But now( on IDS UC5), the result includes itself ( the following statement) for every user session. Misleading! select a.sqs_sessionid, a.sqs_dbname, b.uid, b.username,b.pid,b.hostname,a.sqs_statement from syssqlstat a, syssessions b where a.sqs_sessionid=b.sid and sqs_dbname <>'sysmaster' Thanks Frank **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Jack Parker

FRANK — Mon, 29 Jan 2007 16:58:09 — source: IIUG Forums & Mailing Lists

Jack, It still reports the similar thing, see below. sqs_sessionid 350067 sqs_dbname noaa uid 3009 username saapmgr pid 1839334 hostname kate-dat sqs_statement select a.sqs_sessionid, a.sqs_dbname, b.uid, b.username,b.pid ,b. hostname, a.sqs_statement from syssqlstat a, syssessions b where a.sqs_sessionid=b.sid and sqs_dbname <>'sysmaster' and sqs_sessionid != sqs_sessionid 349959 sqs_dbname noaa uid 3009 username saapmgr pid 1658976 hostname kate-dat sqs_statement select a.sqs_sessionid, a.sqs_dbname, b.uid, b.username,b.pid ,b. hostname, a.sqs_statement from syssqlstat a, syssessions b where a.sqs_sessionid=b.sid and sqs_dbname <>'sysmaster' and sqs_sessionid != .............................. Thanks Frank On 1/29/07, Jack Parker <jack.parker4@verizon.net> wrote: > > > and sqs_sessionid != dbinfo('sessionid') > > j. > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of > FRANK > Sent: Monday, January 29, 2007 11:03 AM > To: ids@iiug.org > Subject: query sysmaster db [8313] > > AIX5.3 IDS 10 UC5. > > The following sysmaster database query used to work well, but it does not > after we move to IDS 10 UC5. > > The purpose of the query is to report all user's SQL statements. But now( > on > IDS UC5), the result includes itself ( the following statement) for every > user session. Misleading! > > select a.sqs_sessionid, a.sqs_dbname, b.uid, b.username,b.pid,b.hostname,> a.sqs_statement > from syssqlstat a, syssessions b > where a.sqs_sessionid=b.sid and > sqs_dbname <>'sysmaster' > > Thanks > Frank > > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

↪ replying to FRANK

Jack Parker — Mon, 29 Jan 2007 17:19:13 — source: IIUG Forums & Mailing Lists

Odd, select a.sqs_sessionid, a.sqs_dbname, b.uid, b.username, b.pid,b.hostname, a.sqs_statement from syssqlstat a, syssessions b where a.sqs_sessionid=b.sid and sqs_sessionid != dbinfo('sessionid') Works fine for me on 10.UC5. Is it possible that you have two sessions both running the same query? j. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of FRANK Sent: Monday, January 29, 2007 11:58 AM To: ids@iiug.org Subject: Re: query sysmaster db [8315] Jack, It still reports the similar thing, see below. sqs_sessionid 350067 sqs_dbname noaa uid 3009 username saapmgr pid 1839334 hostname kate-dat sqs_statement select a.sqs_sessionid, a.sqs_dbname, b.uid, b.username,b.pid ,b. hostname, a.sqs_statement from syssqlstat a, syssessions b where a.sqs_sessionid=b.sid and sqs_dbname <>'sysmaster' and sqs_sessionid != sqs_sessionid 349959 sqs_dbname noaa uid 3009 username saapmgr pid 1658976 hostname kate-dat sqs_statement select a.sqs_sessionid, a.sqs_dbname, b.uid, b.username,b.pid ,b. hostname, a.sqs_statement from syssqlstat a, syssessions b where a.sqs_sessionid=b.sid and sqs_dbname <>'sysmaster' and sqs_sessionid != ............................... Thanks Frank On 1/29/07, Jack Parker <jack.parker4@verizon.net> wrote: > > > and sqs_sessionid != dbinfo('sessionid') > > j. > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of > FRANK > Sent: Monday, January 29, 2007 11:03 AM > To: ids@iiug.org > Subject: query sysmaster db [8313] > > AIX5.3 IDS 10 UC5. > > The following sysmaster database query used to work well, but it does not > after we move to IDS 10 UC5. > > The purpose of the query is to report all user's SQL statements. But now( > on > IDS UC5), the result includes itself ( the following statement) for every > user session. Misleading! > > select a.sqs_sessionid, a.sqs_dbname, b.uid, b.username,b.pid,b.hostname,> a.sqs_statement > from syssqlstat a, syssessions b > where a.sqs_sessionid=b.sid and > sqs_dbname <>'sysmaster' > > Thanks > Frank > > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

↪ replying to Jack Parker

FRANK — Mon, 29 Jan 2007 17:44:05 — source: IIUG Forums & Mailing Lists

Jack, just one session was running this query. what I attached is part of the result, it repeats for every session in db Frank On 1/29/07, Jack Parker <jack.parker4@verizon.net> wrote: > > > Odd, > > select a.sqs_sessionid, a.sqs_dbname, b.uid, b.username,> > b.pid,b.hostname, a.sqs_statement > from syssqlstat a, syssessions b > where a.sqs_sessionid=b.sid > > and sqs_sessionid != dbinfo('sessionid') > > Works fine for me on 10.UC5. Is it possible that you have two sessions > both > running the same query? > > j. > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of > FRANK > Sent: Monday, January 29, 2007 11:58 AM > To: ids@iiug.org > Subject: Re: query sysmaster db [8315] > > Jack, > > It still reports the similar thing, see below. > > sqs_sessionid 350067 > sqs_dbname noaa > uid 3009 > username saapmgr > pid 1839334 > hostname kate-dat > sqs_statement select a.sqs_sessionid, a.sqs_dbname, b.uid, b.username, > b.pid > ,b. > > hostname, > > a.sqs_statement > > from syssqlstat a, syssessions b > > where a.sqs_sessionid=b.sid and > > sqs_dbname <>'sysmaster' and sqs_sessionid != > > sqs_sessionid 349959 > sqs_dbname noaa > uid 3009 > username saapmgr > pid 1658976 > hostname kate-dat > sqs_statement select a.sqs_sessionid, a.sqs_dbname, b.uid, b.username, > b.pid > ,b. > > hostname, > > a.sqs_statement > > from syssqlstat a, syssessions b > > where a.sqs_sessionid=b.sid and > > sqs_dbname <>'sysmaster' and sqs_sessionid != > > ................................ > > Thanks > Frank > > On 1/29/07, Jack Parker <jack.parker4@verizon.net> wrote: > > > > > > and sqs_sessionid != dbinfo('sessionid') > > > > j. > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of > > FRANK > > Sent: Monday, January 29, 2007 11:03 AM > > To: ids@iiug.org > > Subject: query sysmaster db [8313] > > > > AIX5.3 IDS 10 UC5. > > > > The following sysmaster database query used to work well, but it does > not > > after we move to IDS 10 UC5. > > > > The purpose of the query is to report all user's SQL statements. But > now( > > on > > IDS UC5), the result includes itself ( the following statement) for > every > > user session. Misleading! > > > > select a.sqs_sessionid, a.sqs_dbname, b.uid, b.username,b.pid,b.hostname > ,> > a.sqs_statement > > from syssqlstat a, syssessions b > > where a.sqs_sessionid=b.sid and > > sqs_dbname <>'sysmaster' > > > > Thanks > > Frank > > > > > > > > **************************************************************************** > > *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > **************************************************************************** > *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

↪ replying to FRANK

Jack Parker — Mon, 29 Jan 2007 17:55:39 — source: IIUG Forums & Mailing Lists

You have two sessions 350067 and 349959 both reporting the same query. Oddly enough, you have sqs_dbnames that are not 'sysmaster' for the query - which doesn't make sense either - forgive me for sounding foolish, but it looks like you are getting the sqs_statement where sqs_sessionid=dbinfo('sessionid') j. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of FRANK Sent: Monday, January 29, 2007 12:44 PM To: ids@iiug.org Subject: Re: query sysmaster db [8317] Jack, just one session was running this query. what I attached is part of the result, it repeats for every session in db Frank On 1/29/07, Jack Parker <jack.parker4@verizon.net> wrote: > > > Odd, > > select a.sqs_sessionid, a.sqs_dbname, b.uid, b.username,> > b.pid,b.hostname, a.sqs_statement > from syssqlstat a, syssessions b > where a.sqs_sessionid=b.sid > > and sqs_sessionid != dbinfo('sessionid') > > Works fine for me on 10.UC5. Is it possible that you have two sessions > both > running the same query? > > j. > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of > FRANK > Sent: Monday, January 29, 2007 11:58 AM > To: ids@iiug.org > Subject: Re: query sysmaster db [8315] > > Jack, > > It still reports the similar thing, see below. > > sqs_sessionid 350067 > sqs_dbname noaa > uid 3009 > username saapmgr > pid 1839334 > hostname kate-dat > sqs_statement select a.sqs_sessionid, a.sqs_dbname, b.uid, b.username, > b.pid > ,b. > > hostname, > > a.sqs_statement > > from syssqlstat a, syssessions b > > where a.sqs_sessionid=b.sid and > > sqs_dbname <>'sysmaster' and sqs_sessionid != > > sqs_sessionid 349959 > sqs_dbname noaa > uid 3009 > username saapmgr > pid 1658976 > hostname kate-dat > sqs_statement select a.sqs_sessionid, a.sqs_dbname, b.uid, b.username, > b.pid > ,b. > > hostname, > > a.sqs_statement > > from syssqlstat a, syssessions b > > where a.sqs_sessionid=b.sid and > > sqs_dbname <>'sysmaster' and sqs_sessionid != > > ................................ > > Thanks > Frank > > On 1/29/07, Jack Parker <jack.parker4@verizon.net> wrote: > > > > > > and sqs_sessionid != dbinfo('sessionid') > > > > j. > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of > > FRANK > > Sent: Monday, January 29, 2007 11:03 AM > > To: ids@iiug.org > > Subject: query sysmaster db [8313] > > > > AIX5.3 IDS 10 UC5. > > > > The following sysmaster database query used to work well, but it does > not > > after we move to IDS 10 UC5. > > > > The purpose of the query is to report all user's SQL statements. But > now( > > on > > IDS UC5), the result includes itself ( the following statement) for > every > > user session. Misleading! > > > > select a.sqs_sessionid, a.sqs_dbname, b.uid, b.username,b.pid,b.hostname > ,> > a.sqs_statement > > from syssqlstat a, syssessions b > > where a.sqs_sessionid=b.sid and > > sqs_dbname <>'sysmaster' > > > > Thanks > > Frank > > > > > > > > **************************************************************************** > > *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > **************************************************************************** > *** > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > **************************************************************************** > *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Glossary

IDS (Informix Dynamic Server)