Req help - identifying applns using given database
A newcomer on IDS 9.40C2 wanted to discover every application connecting to a particular database, with user, host, PID, program name and SQL text. He tried syssqlcurses, syssqexplain, syssqlstat and onstat -g ses, hitting slow queries and sessions showing his own monitoring SQL. Art Kagel noted 9.40 is long out of support and lacks SQL Trace (added in 11.50), so sysmaster polling (syssessions, etc.) is the only route, pointed to Lester Knutsen's sysmaster presentations/webcast, and suggested the odd results may be an SMI bug in that old version; SET EXPLAIN / onmode -Y only writes to an OS file. Khaled added ph_task scheduling (v11+) and casting to limit column width. No definitive solution is recorded; the thread ends with the poster still puzzling over differing row counts between two sysmaster queries.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I am new to informix and the group.
Request your help with a requirement where we need to identify all the
applications which use a particular database schema on an Informix database
server. The current version of the engine is 9.40C2. It is obtained using the
query - select owner from sysmaster:systables where tabid = 99.
We are considering querying sysmaster tables for getting details. Any other
approach like using onstat or other unix utilities is also welcome.
We are considering building a script to monitor the DB and running the script
at predefined intervals. Since we do not have the list of applications, we are
looking at this work around. Request you to kindly confirm if enabling SQL
tracing for a predefined period of time, if feasible, would be a better way
instead of running a script frequently on the server. Any other ways to get
all details of the queries and applications issuing them is also welcome.
We require the following details:
· How long and frequently the DB server should be monitored to identify all
applications using the database schema.
· How to identify the application from the program identified using process id.
· SQL's issued with application details.
· Identify kind of data accessed by applications.
· Identify peak time connections, load and performance parameters.
· Identify in which format data is extracted file or other types.
Please get back to me if any other information is required.
Request you to re-direct me to the correct forum if this not the right place.
Thanks a lot for your time.
Please find the options tried for fetching the details of the queries and
applications below. Thanks a lot for your time.
Option 1 : Query with statement using syssqlcurses.
Issue : The query takes a really long time to execute and yet doesnt give the
result set and I had to cancel the select due to performance impact. Also it
is prompting for commit/rollback of transaction even though I have issued only
a select statement.
Option 2 : Query with statement using syssqexplain.
Issue : The query takes a really long time to execute and yet doesnt give the
result set and I had to cancel the select due to performance impact. Once even
the first query seemed to take a really long time for fetching just 1 row.
Option 3 : Query with statement using syssqlstat.
Issue : the same query repeats itself across sessions mixed with application
query even after using sqs_sessionid != dbinfo('sessionid'). [Solution
suggested in the thread "query sysmaster db"].
Option 4 : Using onstat g ses command.
Issue : Cannot make use of the onstat g ses command since we have only
database name and not the hostname, terminal name.
First, the version of Informix that you are using is from around 2001 and
is out-of-support. Also it is an early 9.40 release which is likely rather
buggy. You REALLY should upgrade and get back on support! The currently
active releases are 11.50, 11.70, and 12.10.
Second, you mention SQL Tracing, v9.40 did not support SQL Trace which was
introduced in v11.50 IB, so that's not an option for you.
You will have to query sysmaster. Look at the syssessions table for basic
session information. Download one of Lester Knutsen's presentations on
sysmaster for more details or watch the recording of the Webcast Lester did
earlier this year, you'll find the link on our web site:
www.advancedatatools.com/Informix/Webcasts.html
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 Sat, Jun 22, 2013 at 11:49 AM, VIJAY KANNAN <annanyan@gmail.com> wrote:
> I am new to informix and the group.
>
> Request your help with a requirement where we need to identify all the
> applications which use a particular database schema on an Informix database
> server. The current version of the engine is 9.40C2. It is obtained using
> the
> query - select owner from sysmaster:systables where tabid = 99.
>
> We are considering querying sysmaster tables for getting details. Any other
> approach like using onstat or other unix utilities is also welcome.
>
> We are considering building a script to monitor the DB and running the
> script
> at predefined intervals. Since we do not have the list of applications, we
> are
> looking at this work around. Request you to kindly confirm if enabling SQL
> tracing for a predefined period of time, if feasible, would be a better way
> instead of running a script frequently on the server. Any other ways to get
> all details of the queries and applications issuing them is also welcome.
>
> We require the following details:
>
> · How long and frequently the DB server should be monitored to identify all
> applications using the database schema.
> · How to identify the application from the program identified using process
> id.
> · SQL's issued with application details.
> · Identify kind of data accessed by applications.
> · Identify peak time connections, load and performance parameters.
> · Identify in which format data is extracted file or other types.
>
> Please get back to me if any other information is required.
>
> Request you to re-direct me to the correct forum if this not the right
> place.
> Thanks a lot for your time.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c3b4ae092b8504dfd108a8
Thanks a lot for your time and guidance Art.
The details i require from the session are :
username,
hostname,
session id,
process id,
application name,
SQL statement executed.
Query used :
CONNECT TO 'sysmaster';
SELECT t1.username, t1.hostname, t0.sqs_sessionid, t1.pid, t0.sqs_statement
FROM syssqlstat AS t0, syssessions AS t1
WHERE t0.sqs_dbname = 'pegasusdb'
AND NOT t0.sqs_statement IS NULL
AND t0.sqs_sessionid = t1.sid AND t1.tty = 'BENONI3'
ORDER BY t0.sqs_sessionid DESC
Issue : the same query repeats itself across sessions instead of just
displaying the application queries even after using : sqs_sessionid !=
dbinfo('sessionid').[Solution suggested in "query sysmaster db" thread].
I found this query in one of Lester's sites
(http://www.informix.com.ua/articles/sysmast/sysmast.htm)
. But the query modified to select the SQL statement takes a really long time
to execute. Request you to suggest the correct query and also how to stop
truncation of result set columns over 200. Request you to suggest which query
would best suit the requirement. Thanks a lot for your time.
select
syssessions.username username,
syssessions.hostname hostname,
syslocks.owner sid,
syssessions.pid pid,
syssessions.feprogram appln,
l2date(syssessions.connected) startdate
from syslocks, sysdatabases , outer syssessions
where syslocks.rowidlk = sysdatabases.rowid
and syslocks.tabname = "sysdatabases"
and syslocks.owner = syssessions.sid
and sysdatabases.name = 'pegasusdb'
order by 1;
Thanks & Regards,
Vijay
I am considering using query given below to fetch the required details.
Request you to confirm if the query given below when run every 10 minutes over
a span of time is sufficient to identify all the applications running on top
of the database. Request you to validate if this approach would work. Request
you to share links for scheduling the SQL query/shell script to be run
periodically. Request you to kindly share ways to set the output column
statement size larger than 200. Thanks a lot for your time.
Query:
select
syssessions.username username,
syssessions.hostname hostname,
syssqexplain.sqx_sqlstatement statement,
syssessions.sid sid,
syssessions.pid pid,
syssessions.feprogram appln,
l2date(syssessions.connected) startdate,
syssqexplain.sqx_conbno syssqexpconbno,(select
dbinfo('utc_to_datetime',sh_curtime) as currenttime from sysmaster:sysshmvals)as currenttime
from syssessions, syssqexplain
where syssqexplain.sqx_sessionid = syssessions.sid
and syssqexplain.sqx_sqlstatement like '%' || 'pegasusdb' || '%';
Thanks & Regards,
Vijay
Hi Vijay,
This query does not give you all of the queries that are run against the
informix instance ? Unless I am missing something from your request.
Plus, connections come and go, so if you want "all" this will not do it .
You can of course schedule tasks in the ph_task table of the sysadmin
database. You can look into the informix documentation in order to do
this. This will only for version 11 or above. Otherwise, you have to do
it on your own.
If you want to show only the first 200 characters, you can either teh
substr function or use a cast such as
syssqexplain.sqx_sqlstatement::char(200)
Cordialement, Regards,
Khaled Bentebal
Directeur Général - ConsultiX
Président UGIF - User Group Informix France
IIUG - Board of Directors
Tél: 33 (0) 1 39 12 18 00
Fax: 33 (0) 1 39 12 18 18
Mobile: 33 (0) 6 07 78 41 97
Email: khaled.bentebal@consult-ix.fr
Site Web: www.consult-ix.fr
Le 24/06/13 12:03, VIJAY KANNAN a écrit :
> I am considering using query given below to fetch the required details.
> Request you to confirm if the query given below when run every 10 minutes
over
> a span of time is sufficient to identify all the applications running on top
> of the database. Request you to validate if this approach would work. Request
> you to share links for scheduling the SQL query/shell script to be run
> periodically. Request you to kindly share ways to set the output column
> statement size larger than 200. Thanks a lot for your time.
>
> Query:
>
> select
>
> syssessions.username username,
>
> syssessions.hostname hostname,
>
> syssqexplain.sqx_sqlstatement statement,
>
> syssessions.sid sid,
>
> syssessions.pid pid,
>
> syssessions.feprogram appln,
>
> l2date(syssessions.connected) startdate,
>
> syssqexplain.sqx_conbno syssqexpconbno,(select
> dbinfo('utc_to_datetime',sh_curtime) as currenttime from
sysmaster:sysshmvals)> as currenttime
> from syssessions, syssqexplain
> where syssqexplain.sqx_sessionid = syssessions.sid
> and syssqexplain.sqx_sqlstatement like '%' || 'pegasusdb' || '%';
>
> Thanks& Regards,
> Vijay
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
I'm not sure I understand your "issue". Are you saying that the query just
returns itself for every session instead of the correct SQL for each
session?
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, Jun 24, 2013 at 3:20 AM, VIJAY KANNAN <annanyan@gmail.com> wrote:
> Thanks a lot for your time and guidance Art.
>
> The details i require from the session are :
>
> username,
> hostname,
> session id,
> process id,
> application name,
> SQL statement executed.
>
> Query used :
>
> CONNECT TO 'sysmaster';
>
> SELECT t1.username, t1.hostname, t0.sqs_sessionid, t1.pid, t0.sqs_statement
> FROM syssqlstat AS t0, syssessions AS t1
> WHERE t0.sqs_dbname = 'pegasusdb'
> AND NOT t0.sqs_statement IS NULL
> AND t0.sqs_sessionid = t1.sid AND t1.tty = 'BENONI3'
> ORDER BY t0.sqs_sessionid DESC>
> Issue : the same query repeats itself across sessions instead of just
> displaying the application queries even after using : sqs_sessionid !=
> dbinfo('sessionid').[Solution suggested in "query sysmaster db" thread].>
> I found this query in one of Lester's sites
> (http://www.informix.com.ua/articles/sysmast/sysmast.htm)
> .. But the query modified to select the SQL statement takes a really long
> time
> to execute. Request you to suggest the correct query and also how to stop
> truncation of result set columns over 200. Request you to suggest which
> query
> would best suit the requirement. Thanks a lot for your time.
>
> select
>
> syssessions.username username,
>
> syssessions.hostname hostname,
>
> syslocks.owner sid,
>
> syssessions.pid pid,
>
> syssessions.feprogram appln,
>
> l2date(syssessions.connected) startdate
> from syslocks, sysdatabases , outer syssessions
> where syslocks.rowidlk = sysdatabases.rowid
> and syslocks.tabname = "sysdatabases"
> and syslocks.owner = syssessions.sid
> and sysdatabases.name = 'pegasusdb'
> order by 1;
>
> Thanks & Regards,
> Vijay
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c372dc54e64104dfe4ad29
Hi Khaled,
The requirement is to identify all the applications which would be issuing
queries against a particular database (pegasusdb) on an Informix DB. The
current version of the engine is 9.40C2.
It is suggested that we monitor the database for identifying the applications.
We are considering using the following query periodically (every 10 minutes)
for a predefined span of time to identify all the applications. We are trying
to use the pid to identify the unix process and the application. We also need
the query that was issued by the application.
Query to SQLs involving pegasusdb database:
-- To identify the SQL statements containing database name
select
syssessions.username username,
syssessions.hostname hostname,
syssqexplain.sqx_sqlstatement statement,
syssessions.sid sid,
syssessions.pid pid,
syssessions.feprogram appln,
l2date(syssessions.connected) startdate,
syssqexplain.sqx_conbno syssqexpconbno,(select
dbinfo('utc_to_datetime',sh_curtime) as currenttime from sysmaster:sysshmvals)as currenttime
from syssessions, syssqexplain
where syssqexplain.sqx_sessionid = syssessions.sid
and syssqexplain.sqx_sqlstatement like '%' || 'pegasusdb' || '%';
union
-- To identify SQL statements not containing database name
select
syssessions.username username,
syssessions.hostname hostname,
syssqexplain.sqx_sqlstatement statement,
syslocks.owner sid,
syssessions.pid pid,
syssessions.feprogram appln,
l2date(syssessions.connected) startdate,
syssqexplain.sqx_conbno syssqexpconbno
from syslocks, sysdatabases , outer (syssessions, syssqexplain)
where syslocks.rowidlk = sysdatabases.rowid
and syslocks.tabname = "sysdatabases"
and syslocks.owner = syssessions.sid
and syssqexplain.sqx_sessionid = syssessions.sid
and sysdatabases.name = 'pegasusdb';
Any other ways to get all details of the queries and applications issuing them
is welcome. Request you to kindly validate the approach and share your
thoughts.
Thanks a lot for your time.
Thanks & Regards,
Vijay
Hi Art, Yes. The same query is available in the result set with different session ids. At times, it also displays application queries which is the intended output. Thanks for your time. Thanks & Regards, Vijay
That's odd. But if I am remembering correctly you are using a very old Informix version so it may be a bug in the SMI interface (ie sysmaster) to shared memory. 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, Jun 24, 2013 at 8:52 AM, VIJAY KANNAN <annanyan@gmail.com> wrote: > Hi Art, > > Yes. The same query is available in the result set with different session > ids. > At times, it also displays application queries which is the intended > output. > Thanks for your time. > > Thanks & Regards, > Vijay > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1133b7da0fa61204dfe6ee74
Hi Khaled,
I added the second part of the query (following union) to capture all the SQLs
which does not contain the database name pegasusdb in the query (since it
would not be captured by the first part of the query).
To check the query, i established a db session for ho_bus_unit by using
dbaccess utility in unix and got the session id as 91449254. But the following
query returned no rows selected. I was not sure if this is due to query
execution completion. A select for update statement also does not display
results for the query given below even though the transaction is not complete.
Query:
select syslocks.owner sid,sysdatabases.name from syslocks, sysdatabases where
syslocks.rowidlk = sysdatabases.rowid and syslocks.tabname = "sysdatabases"
and syslocks.owner = 91449254;
Request you to clarify until what point of time a query can be expected to be
present in SMI tables since this would be a critical input in deciding how
frequently the queries need to be monitored to be able to catch all of them
over a span of time. Request you to share your thoughts on the approach.
Thanks a lot for your time.
Thanks & Regards,
Vijay
Request you to let me know if it would be possible to use explain (like oracle which stores explain results in plan table for querying later) or any other such utility to indirectly log the details of the query being executed on the database. Request you to direct me to available documentation/discussions. Thanks a lot for your time. Regards, Vijay
Informix has an explain facility but it only logs to an OS file. You can
enable it from inside the session with "set explain on;" or from external
to the sessikn using "onmode -Y sessionid".
Art
On Jul 3, 2013 11:23 AM, "VIJAY KANNAN" <annanyan@gmail.com> wrote:
> Request you to let me know if it would be possible to use explain (like
> oracle
> which stores explain results in plan table for querying later) or any other
> such utility to indirectly log the details of the query being executed on
> the
> database. Request you to direct me to available documentation/discussions.
> Thanks a lot for your time.
>
> Regards,
> Vijay
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e014933807d462604e09ff504
We are making use of the following query to get the details of the application
queries from sessions older than 10 minutes:
Original query:
---------------
select count(*)
from sysconblock a,syssessions b,sysopendb c
,sysscblst d
where odb_dbname="pegasusdb"
and a.cbl_sessionid=b.sid
and c.odb_sessionid =b.sid
and c.odb_sessionid = d.sid
and dbinfo( "utc_to_datetime", d.connected ) > current - 10 units minute;
We observed that the column connected is present in syssessions view defined
on sysscblst table and removed the table from query. We were expecting to find
the same count as for the query given above. Surprisingly, different count
values are obtained for these two queries. Please let me know if i am missing
something or if it has something to do with the life time of the query in
sysmaster tables. Thanks for your time.
Modified query:
---------------
select count(*)
from sysconblock a,syssessions b,sysopendb c
-- ,sysscblst d
where odb_dbname="pegasusdb"
and a.cbl_sessionid=b.sid
and c.odb_sessionid =b.sid
-- and c.odb_sessionid = d.sid
and dbinfo( "utc_to_datetime", b.connected ) > current - 10 units minute;