How To
Summary
It is easy to create iBase databases.
You might have created iBase databases for testing purposes, for training, for small projects...
The result is that you might have dozen or hundreds of iBase databases.
And you might want to archive or delete the databases that haven't been opened for several years.
This document explains how to know for each iBase database (in SQL Server format) when was the last time a user connected from iBase or Analyst's Notebook.
Objective
For each iBase database in an SQL Server, get the name of the database and the date of the last successful connection.
You can then decide on a case per case basis what you want to do with the databases that haven't been accessed for more than, say, 5 years.
Steps
- Open SQL Server Management Studio (SSMS) with an account that has at least Read permission on all the iBase SQL databases
- Select the master SQL database, and create a new SQL query
- Copy-paste the following query:
-- We get all the **_LOG SQL Databases and put them in a Cursor-- For each one, we look in the table _AuditLog most recent record with Action_Type = 101
-- Action_Type = 101 corresponds to a user connecting to this iBase database from iBase (or ANB)
-- We then print this date, and we repeat for every *_LOG databaseUSE master;DECLARE @DatabaseName NVarchar(300);
DECLARE @theSQL nvarchar(max);
DECLARE @LastAccessed nvarchar(50);
DECLARE @Result nvarchar(50);
DECLARE @OutputResults nvarchar(300);-- We get all the ***_LOG SQL Databases and put them in a Cursor
DECLARE dbs CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT name
FROM sys.databases
WHERE name like '%_LOG'
AND state_desc = 'ONLINE';OPEN dbs;
FETCH NEXT FROM dbs INTO @DatabaseName;WHILE @@FETCH_STATUS = 0
BEGIN
-- We check to see if the SQL Database is an iBase DB by searching for the _LinkEnd table
IF OBJECT_ID (QUOTENAME(@DatabaseName) +'.dbo._AuditLog', N'U') IS NOT NULL
BEGIN
SET @OutputResults = LEFT(@DatabaseName, LEN(@DatabaseName) - 4)
SET @theSQL = ' SELECT TOP 1 @LastAccessed = Date_Time
FROM ' + QUOTENAME(@DatabaseName) + '.[dbo].[_AuditLog]
WHERE Action_Type = 101
ORDER BY Date_Time DESC'EXEC sp_executesql @theSQL, N'@LastAccessed varchar(100) out', @Result out
SET @OutputResults = @OutputResults + ' == Last Accessed : ' + LTRIM(@Result)-- And we print the result.
RAISERROR(@OutputResults,0,1) WITH NOWAIT
END;FETCH NEXT FROM dbs INTO @DatabaseName;
END
CLOSE dbs;
DEALLOCATE dbs;
The result is displayed on the screen.
For example:

Once this is done, you can save the SQL query, if you plan to use it again in the future.
And you can then close SSMS.
Document Location
Worldwide
[{"Type":"MASTER","Line of Business":{"code":"LOB24","label":"Security Software"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSXW43","label":"i2 iBase"},"ARM Category":[{"code":"a8m50000000CiA3AAK","label":"i2 iBase and i2 Analyst\u0027s WorkStation-\u003EAuditing"}],"ARM Case Number":"TS006105684","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]
Was this topic helpful?
Document Information
Modified date:
22 July 2021
UID
ibm16474517