connections to specific database

Hi, I'm trying to find out, how I can monitor the connections to a specific database. onstat -u only gives information fo entire IDS. It sounds like something trivial but I just can't find it. thanks for any help Dagmar

>Hi, > >I'm trying to find out, how I can monitor the connections to a specific database. onstat -u only gives information fo entire IDS. It sounds like something trivial but I just can't find it. > >thanks for any help >Dagmar There isn't a built in way to do that. All the onstat commands would be instance wide. You could probably make some scripts up yourself to try and pull info for just certain sessions that are using select databases. If you were going to want to try and do that, I'd start with onstat -g sql. It has a column for the current database. So you could grep/awk out session numbers for just those sessions where the current database was equal to a certain database. Then take those session numbers and use those as values to be grepped out of other onstat commands that reported session numbers, etc... I'm not a big sysmaster person, but I would think you could write sysmaster queries that would do the same sort of thing joining the various tables together to pull the info you were after, but only pulling the info where the current database (as reported by onstat -g sql) was equal to a certain value. Jacques Renaut IBM IDS APD team

Hi, I know that there are other ways of doing this such as using sysdbopen, however When a user connects to the database it creates a shared lock So onstat -k will give you something like with the tblsnum as 100002 Locks address wtlist owner lklist type tblsnum rowid key#/bsiz 70000001cee1828 0 700000459196020 0 S 100002 20f 0 The row id is the rowid of the database in sysmaster:sysdatabases which you can find using select hex(rowid), name from sysmaster:sysdatabases Apply the relevant filtering for the database that you are looking for Regards Mark -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of DAGMAR ULLRICH Sent: 13 May 2009 05:54 PM To: ids@iiug.org Subject: connections to specific database [15739] Hi, I'm trying to find out, how I can monitor the connections to a specific database. onstat -u only gives information fo entire IDS. It sounds like something trivial but I just can't find it. thanks for any help Dagmar **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. ================== Please read our Email Disclaimer : http://www.thefuelgroup.com/disclaimer.html

There is no onstat report to show this. I don't know if OAT can do it either. But take a look at the sysopendb table and syssessions view in the sysmaster database. This might be a useful extension to OAT. A query like the following might be what you want. Adjust the query to your needs. select a.odb_sessionid as session , a.odb_iscurrent as is_current , substr(a.odb_dbname,1,20) as database , substr(b.username,1,20) as user from sysopendb a , syssessions b where odb_dbname = "sysmaster" and odb_iscurrent = "Y" and a.odb_sessionid = b.sid order by odb_sessionid ; Cheers, Dick Snoke IBM Data Management - ChannelWorks dsnoke@us.ibm.com (404) 487-1595 From: "DAGMAR ULLRICH" <ullrichd@hrz.uni-kassel.de> To: ids@iiug.org Date: 05/13/09 11:54 AM Subject: connections to specific database [15739] Sent by: ids-bounces@iiug.org Hi, I'm trying to find out, how I can monitor the connections to a specific database. onstat -u only gives information fo entire IDS. It sounds like something trivial but I just can't find it. thanks for any help Dagmar ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.