Problem(Abstract)
dbexport might find that the database is in use, preventing dbexport from locking the database in exclusive mode. This document provides information on how to disconnect only those sessions that are locking objects within the database.
Symptom
While running a dbexport command to unload a database, dbexport stops and you get the following errors:
-425 - Database is currently opened by another user.
-107 - ISAM error: record is locked.
Cause
The database cannot be locked using dbexport, because there is at least one session still connected and holding a lock on an object.
Resolving the problem
Find all sessions that are still connected to the database and holding a lock, then disconnect them using onmode -z before running dbexport again.
You can get the sessions and the lock holders still connected to the database by using onstat commands.
You can also run this SQL statement to find out the sessions that hold locks.
Note: In the following query, you have to replace the two occurrences of database_name by the name of the database you want to export.
database sysmaster;
SET ISOLATION TO DIRTY READ;
SELECT "database lock" table_name,
l.type lock_type, l.keynum index_num, HEX(l.rowidlk) rowid,
s.sid session_id, s.username, s.pid, s.hostname,
q.sqs_statement statement
FROM syslocks l, sysdatabases d, syssessions s, syssqlstat q
WHERE d.name='database_name' AND l.rowidlk = d.rowid
AND l.owner = s.sid
AND dbsname = 'sysmaster' AND tabname = 'sysdatabases'
AND s.sid = q.sqs_sessionid
UNION ALL
SELECT l.tabname,
l.type, l.keynum, HEX(l.rowidlk),
s.sid, s.username, s.pid, s.hostname,
q.sqs_statement
FROM syslocks l, syssessions s, syssqlstat q
WHERE l.dbsname='database_name' AND l.owner = s.sid
AND s.sid = q.sqs_sessionid AND dbsname != 'sysmaster'
AND tabname != 'sysdatabases'
ORDER BY 5;
- database_name
- Replace it by the name of the database you want to export.
A sample of this query output, run replacing database_name to stores7, will show all the locks on this specific database:
table_name database lock
lock_type X
index_num 0
rowid 0x00000205
session_id 5
username vgomes
pid 1832
hostname workstation1
statement DATABASE stores7 EXCLUSIVE
- table_name
- Is either the name of the table that has an object locked (row, index, table itself) or it will be the phrase 'database lock' if the whole database was locked.
- Is the type of lock, for example: X for Exclusive, S for Shared, U for Update. Seeonstat -k information in IBM® Informix® Dynamic Server™ (IDS) Administrator's Reference or Administrator's Guide for further information on types of lock.
- the index key number if it is an index lock.
rowid
- the rowid (in hexadecimal) for the record locked in case it is a record was locked. If a database was entirely locked, this is the rowid (in hexadecimal) for the record corresponding to this database locked in sysmaster:sysdatabases table.
- the session id for the database session that locked the object.
username
- Is the user id associated to the session that locked the object.
pid
- the process id associated to the session that locked the object.
hostname
- the machine name for the workstation or client host that created the session that locked the object.
- the SQL statement that generated the lock in the object.
You can see the details on the session by using this command at the command line:
- $ onstat -g ses session_id
- session_id
- The id of the session locking the object given as result by the previous query.
Example:
From the previous query output:
- $ onstat -g ses 5
To disconnect a session use this command at the command line:
- $ onmode -z session_id
- session_id
- The id of the session locking the object.
Example:
To disconnect the session locking the database object, you can run:
- $ onmode -z 5