Home

Reverse engineering usage of Siebel base tables

2010, February 3 - 13:17 — müzso
All Siebel base tables have a couple of mandatory columns, including a LAST_UPD date column that contains the date of last modification for a given record. Using this you can easily write a procedure to look for tables/records that have changed since a specific date (or in the last "n" minutes). Thus you can find out for every operation available in Siebel Tools the tables it involves. Eg. you can edit the web layout of an applet, save the changes and then query the list of tables that contain records updated in the last 1-2 minutes to see where are the layout settings stored. You can apply the same method for any operations via the user interface (like adding/modifying a new contact, an employee, etc.).

I'm using Siebel on Microsoft SQL Server at the moment, so my example will be in Transact SQL ...
USE siebeldb
GO

DECLARE Cur CURSOR FOR
        SELECT t.TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES AS t
                INNER JOIN INFORMATION_SCHEMA.COLUMNS AS c ON t.TABLE_CATALOG = c.TABLE_CATALOG AND t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
        WHERE c.COLUMN_NAME = 'LAST_UPD'
        AND c.DATA_TYPE = 'datetime'
        ORDER BY t.TABLE_NAME

OPEN Cur
DECLARE @TabName VARCHAR(100)
DECLARE @Sql NVARCHAR(200)
DECLARE @Cnt INT
DECLARE @LastUpd DATETIME
SET @LastUpd = '2010-02-03 11:00:00.000'
FETCH NEXT FROM Cur INTO @TabName
WHILE (@@FETCH_STATUS = 0)
BEGIN
        SET @Sql = 'SELECT @Cnt = COUNT(*) FROM ' + @TabName + ' WHERE LAST_UPD >= @LastUpd'
        EXEC sp_executesql @Sql, N'@Cnt int output, @LastUpd datetime', @Cnt = @Cnt OUTPUT, @LastUpd = @LastUpd
        IF @Cnt > 0 PRINT @TabName + ': ' + CONVERT(VARCHAR(10), @Cnt)
        FETCH NEXT FROM Cur INTO @TabName
END
CLOSE Cur
DEALLOCATE Cur
GO

Note that values in the LAST_UPD column are in GMT (Greenwich Mean Time), thus the value of @LastUpd should be specified in GMT as well.

If you want to look for records changed in the eg. last 10 minutes, then the value of @LastUpd should be calculated accordingly:
SET @LastUpd = DATEADD(MI, -10, GETUTCDATE())

P.S.: of course you can do the same via enabling tracing in your database, but imho that's a bit of an overkill for this purpose (and has a number of disadvantages too).

Syndicate content