Troubleshooting
Problem
When trying to log into IBM i2 iBase or iBase Designer, users may experience an error which reports that there is an 'Exclusive Lock' on their database.
The Warning message is : "You cannot open this database. It has been opened exclusively by another user (userName)"
Cause
Can occur when IBM i2 iBase or iBase Designer is improperly shutdown.
Diagnosing The Problem
You must check that the folder that contains the IDB file does not contain a file with an ldb (LDB) extension, and the same name as the IDB file.
For example, if the IDB file is called MyBase.idb, you must check to see if there is a file called MyBase.ldb.
If yes, then this may be the cause of the problem.
In this case, please refer to the following technote :
https://www.ibm.com/support/pages/ibm-i2-ibase-designer-exclusive-lock-you-attempted-open-database-already-opened-exclusively-user-machine
This current document handles the case when there is no ldb file.
Resolving The Problem
Ensure that you have a backup copy of your SQL database(s) before attempting the steps below.
To resolve the issue, follow these steps:
This should remove the 'Exclusive Lock' from the database.
Additionally:
The "select * from ALTER DATABASE <dbname> SET SINGLE_USER WITH NO_WAIT" command is not something iBase issues explicitly. This is a consequence of iBase not being able to put the database into single user mode. The "ALTER DATABASE <dbname> SET SINGLE_USER WITH NO_WAIT" command, which iBase is issuing explicitly, is failing.
This command fails when there is an active connection to the iBase database.
Re-start SQL Server and then open SQL Server Management Studio and open a new query window (while connected to the master database, not the iBase database) then run this command:
EXEC sp_who2
This will display details of all the active database connections. In theory there will be a connection to their iBase database... the one that is causing the 'SET SINGLE USER' command to fail. Kill this connection by executing the following command:
KILL <SPID>
where <SPID> is the SPID in the results table for the row which shows a connection to the iBase database.
Note: the ProgramName column will indicate what program/process is connected to the iBase database. This is the process that is causing the problem.
If there are no connections to the iBase database then try opening iBase in Designer. If it fails then try the above again.
If you are still unable to open the database in iBase Designer then run the "ALTER DATABASE <dbname> SET SINGLE_USER WITH NO_WAIT" command in a query window (while connected to the master database, not the iBase database.) Replace <dbname> with the name of the iBase database.) Run this command "ALTER DATABASE <dbname> SET MULTI_USER WITH NO_WAIT" and then try opening the database in iBase Designer.
Furthermore, execute the following command to view what triggers are set to in SQL Server that may be connecting to the iBase database:
SELECT * FROM sys.server_triggers;
To resolve the issue, follow these steps:
- Login to Microsoft SQL Server Management Studio
- Expand the Database folder
- Expand Tables folder
- Look for the entries 'dbo._Configuration_Def', 'dbo._Configuration_Text' and 'dbo._Configuration_Binary'
- Open the table 'dbo._Configuration_Def' and look for the entry SQLServer:Exclusive" and check the value against it. If the value is 1 then it means the same value also exist in the 'dbo._Configuration_Binary' table. However, if the value is 0 then it means the same value also exist in the 'dbo._Configuration_Text' table.
- Delete the entry (row) from table 'dbo._Configuration_Text' or 'dbo._Configuration_Binary'
- Then delete the value from 'dbo._Configuration_Def' table
You can also run these SQL scripts :
-- check for Application exclusive lock
select * from _Configuration_Def where item='SQLServer:Exclusive'
select * from _Configuration_Binary where item='SQLServer:Exclusive'
select * from _Configuration_Text where item='SQLServer:Exclusive'
-- clear application exclusive lock
delete from _Configuration_Binary where item='SQLServer:Exclusive'
delete from _Configuration_Text where item='SQLServer:Exclusive'
delete from _Configuration_Def where item='SQLServer:Exclusive'
select * from _Configuration_Def where item='SQLServer:Exclusive'
select * from _Configuration_Binary where item='SQLServer:Exclusive'
select * from _Configuration_Text where item='SQLServer:Exclusive'
-- clear application exclusive lock
delete from _Configuration_Binary where item='SQLServer:Exclusive'
delete from _Configuration_Text where item='SQLServer:Exclusive'
delete from _Configuration_Def where item='SQLServer:Exclusive'
This should remove the 'Exclusive Lock' from the database.
Additionally:
The "select * from ALTER DATABASE <dbname> SET SINGLE_USER WITH NO_WAIT" command is not something iBase issues explicitly. This is a consequence of iBase not being able to put the database into single user mode. The "ALTER DATABASE <dbname> SET SINGLE_USER WITH NO_WAIT" command, which iBase is issuing explicitly, is failing.
This command fails when there is an active connection to the iBase database.
Re-start SQL Server and then open SQL Server Management Studio and open a new query window (while connected to the master database, not the iBase database) then run this command:
EXEC sp_who2
This will display details of all the active database connections. In theory there will be a connection to their iBase database... the one that is causing the 'SET SINGLE USER' command to fail. Kill this connection by executing the following command:
KILL <SPID>
where <SPID> is the SPID in the results table for the row which shows a connection to the iBase database.
Note: the ProgramName column will indicate what program/process is connected to the iBase database. This is the process that is causing the problem.
If there are no connections to the iBase database then try opening iBase in Designer. If it fails then try the above again.
If you are still unable to open the database in iBase Designer then run the "ALTER DATABASE <dbname> SET SINGLE_USER WITH NO_WAIT" command in a query window (while connected to the master database, not the iBase database.) Replace <dbname> with the name of the iBase database.) Run this command "ALTER DATABASE <dbname> SET MULTI_USER WITH NO_WAIT" and then try opening the database in iBase Designer.
Furthermore, execute the following command to view what triggers are set to in SQL Server that may be connecting to the iBase database:
SELECT * FROM sys.server_triggers;
Related Information
[{"Product":{"code":"SSXW43","label":"i2 iBase"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"--","Platform":[{"code":"PF033","label":"Windows"}],"Version":"8.9","Edition":"","Line of Business":{"code":"LOB24","label":"Security Software"}}]
Was this topic helpful?
Document Information
Modified date:
11 June 2020
UID
swg21613718