Home

Reverse engineering usage of Siebel base tables

2010, February 3 - 12: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).

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
2011, September 12 - 06:05 — John Mac (not verified)

LAST_UPD

Is it usual to use LAST_UPD when extracting delta data from Siebel tables ? We're considering using it in our Delta data extraction process, but our DBAs are claiming that adding an Index to Siebel tables on column LAST_UPD is highly irregular and undesirable (because of the claimed impact on OLTP).

Our prod tables will eventually hold around 700M rows. I can't see how our extracts can possibly work without us havinig an Index on LAST_UPD.

Any thoughts ?

2011, September 12 - 11:17 — müzso

Re: LAST_UPD

1.) Siebel itself (eg. Tools) does use LAST_UPD for computing differences between two points of time (usually a past date and the present). So I guess you can do it too. But beware that LAST_UPD might not be the right choice for you. It's updated always when a record is "touched" (by a Siebel internal process or a user through the UI). Even if nothing else changed in the given record! Eg. a user types something in a field in an applet, moves on (does other things), then goes back and reverts the changes. In this case the record contents are not different between a past and present, but there was a change which is reflected by LAST_UPD. In another scenario an external system might call a Siebel webservice and regardless of the call parameters (even if the parameters are empty, thus no change'd be necessary), the webservice might update the given BC record and update LAST_UPD.

2.) Probably your DBA is right. I'm not sure how "uncommon" it is to put a new index on every Siebel table's LAST_UPD column, but I guess it might seriously screw up Siebel's performance. Most Siebel queries do not specify to the last dot which tables are to be accessed through which indexes (and methods). Siebel support services might not recommend this approach either (but you should ask them). If it was for me to decide (and Siebel support services would not be relevant), I'd try and test. Create a real-life scenario (ie. Siebel database with as many and type of records as it'll be in production), run relevant tests on performance, create the new indexes and run the tests again. It's quite important to test all sorts of functionality that you use in Siebel. Eg. it'd be quite unpleasant if an EIM job was not tested and it'd turn out to be slow (due to the new indexes) only during use on the production system.

3.) What do you mean by "extracting delta data from Siebel tables"? Are you going to extract changes through SQL and import them to another server (through SQL as well)? Shocked Siebel support services does seriously discourage from direct manipulation of Siebel tables and AFAIK a Siebel support contract is invalidated by any similiar database manipulation (apart from the cases, when Siebel support services tells you to run an SQL statement). Siebel tables are meant be modified only through Siebel provided means (Siebel Tools, UI, EAI/EIM, etc.), not direct SQL statements.

I'm not sure about the best approach here. Without knowing what this is all about ("delta data extraction"?) I cannot tell if you're heading in a totally wrong direction with this extraction + new indexes on LAST_UPD columns idea.

2011, November 8 - 01:03 — John Mac (not verified)

Hi - sorry for the tardy

Hi - sorry for the tardy response Sad

Delta Data extraction ? : I need to extract all data that has been changed/inserted since the last time I extracted the data.

Why ? : The Siebel data is to be used to populate an analytics datawarehouse on a different server using a different DBMS - so no changes will be applied to any Siebel tables. So, during the day the siebel tables are updated by the various online transactions, and then at some point (or points) in time, I'll invoke an extract process to go and grab all relevant data that has been inserted/changed.
This delta data will then be ported to the analytics server and incorporated into the Warehouse to give an accurate 'as at' picture of our customers.

I've come to accept now that LAST_UPD may not be the answer to my situation - the resistance from the DBAs is very strong. However, at the moment, we're struggling to come up with alternatives for the extraction process. I can't help feeling that there must be 'normal' ways used around the world for extracting recently changed (delta) data from large Siebel systems. Perhaps I should be looking instead at using some kind of EIM feature to get at the data?

Any thoughts gratefully accepted. Thanks.

Syndicate content