IBM Support

How to know when iBase databases were accessed the last time (SQL Server format) ?

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

  1. Open SQL Server Management Studio (SSMS) with an account that has at least Read permission on all the iBase SQL databases
  2. Select the master SQL database, and create a new SQL query
  3. 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 database
USE 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:
image 11005
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"}]

Document Information

Modified date:
22 July 2021

UID

ibm16474517