Re: How to find which user is holding a database table?

Hi, I have a script in my articule on Exploring the Sysmaster database on my web site that does this. This is the same reason I created the script. The following is the SQL, see the artcule of details at www.access.digex.net/~lester database sysmaster;select sysdatabases.name database,-- Database Name syssessions.username,-- User Name syssessions.hostname,-- Workstation syslocks.owner sid -- Informix Session ID from syslocks, sysdatabases , outer syssessions where syslocks.tabname = "sysdatabases" and syslocks.rowidlk = sysdatabases.rowid name and syslocks.owner = syssessions.sid order by 1; Regards - Lester > Hi All > > I have to do schema changes to a database which has lots of people (developers). Most of the time there is no problem but sometimes I get the message: > > 215: Cannot open file for table (tablename) > 106: ISAM error: non-exclusive access> > from which I understand that somebody is using the table and hence I cannot alter its structure or drop an index from it. > > I have written a script which essentially does an onstat -g ses and using the session id's generated, does an onstat -g sql <sessid>. I first grep the output for the database that I want. If it is the database that I want then I grep the output for the table name, hoping I will get the tablename in the "Current SQL statement" and the "Last Parsed SQL statement". > > However my script does not find the table which is giving me these errors as being contained in any of the onstat -g sql <sessid> outputs. > > I have verified that my script works by feeding it table names that I can see manually by doing an onstat -g sql <sessid>. What am I doing wrong? Is there any other way of doing this? > > TIA > > Sujit Pal ############################################################################# # Lester Knutsen lester@access.digex.net # # Advanced DataTools Corporation Voice: 703-256-0267 # # Grant group privileges for Informix databases with DB Privileges # # Visit our Web page: http://www.access.digex.net/~lester # # Washington Area Informix User Group: http://www.access.digex.net/~waiug # #############################################################################