Share

When is it acceptable to run SQL against Siebel database tables?

A. never
B. to insert into Siebel EIM (interface) table such as EIM_ACCOUNT
C. to drop tables from the database
D. to insert data into Siebel customer (data) tables such as S_ORG_EXT

Our Linked Group “Siebel Oracle CRM Experts” is a wonderful place to discuss siebel, We have started a new feature to showcase the highly discussed topic in the website. Marianne Smith has initiated a wonderful topic, Lets go through it.

When is it acceptable to run SQL against Siebel database tables?

A. never
B. to insert into Siebel EIM (interface) table such as EIM_ACCOUNT
C. to drop tables from the database
D. to insert data into Siebel customer (data) tables such as S_ORG_EXT

Comments / Replies/ Answers By our expert readers

Abdallah Abdelsalam ,Siebel Consultant Outsourced to Mobily

B

David Berman ,MRM/CRM Marketing Process Lead

B & E
E:
It’s arguably (how’s that for non-commitment) acceptable to run SQL against Siebel tables, if you’re not messing with the row ids by trying to create, update or delete row ids.

Manohar Pavuluri,Sr Siebel Developer at Invesco Ltd.

As long as your comfortable, have enough knowledge of Siebel Architecture, ROW_IDs, Unique keys, Foreign keys and know what you are doing then that should be fine.

Piet Demeester CRM Consultant, RESOLAB

In case you do E, and you use Siebel remote, you will have to re-extract your remote databases, if you want your remote users to see the updates.

Paul Naish CRM Application Technologist – Oracle

Running SQL against the tables is the solution ; what was the problem? If SQL is needed then I would say B&C since you seem to mixing DML & DDL which are different problems..

For strictly data, B since it maintains the abstraction away from the data model and reduces re-work when upgrading.

That being said, in an emergency, I could see D. Siebel as with any product, is prone to bugs that need to be overcome prior to any permanent fix but care is needed as well as backups and proper oversight in these cases.

Brijesh Dhobale Senior Programmer Analyst at PTC

insert, delete, update to EIM tables –> Allowed
insert, delete to Siebel base table (starts with S_* ) –> No allowed
updates to s_* table –> Siebel does not recommend, but if you are 100% confortable you can do that.

Drop tables –> No allowed on EIM as well as S_. you can do that on temporary tables.

Jay Srinivasan Senior Siebel Consultant at Transworld

Answer is: “B”

“D” is big NO! NO! Select statements in SQL is permissible. Since the option states about Insert. One should refrain from it.

Rajasekhar M Siebel Developer at Interoute

You can insert data into Siebel Tables if you want…and if you have that patience.. hahaha

Sukesh Singh Siebel Technical Lead / Certified Consultant @TCS

Hi Marianne,
You can always use SQL to populate EIM tables (option B). As for Siebel base tables, as long as you are updating only the data values and not changing the Row_Id, FK etc, and there are no mobile users (to get the updates to their local db), it might be acceptable. Though as per Siebel guidelines, you should never use sql to modify/delete/insert data in base tables directly (EIM tables should still be ok).
Cheers
Sukesh

Umamohanarao Mudunuri Senior Technical Associate at Tech Mahindra

I go with david as we can do it in EIM but strictly we dont do it or S_* tables as the rowid creation is siebel area and also we have been using the same concept where in we access the siebel DB thru Perl so it is strictly no for S_tables.Probably any Update barring out the rowids is ok…

Stéphane MONTRI IT Specialist at IBM France

Hi everybody,
Have you heard about Optim solution from the IBM Information Management portfolio ?
This solution is able to help on those 3 topics:
– Data Growth (archiving data with restore capabilities as well)
– Test Data Management (extract subsets of data from production to insert them into Dev, Test,… environments)
– Data Privacy (comes with TDM and enables to mask sensitive data when exporting from production DB.

Optim works directly against the database and does not need EIM. It is also faster than EIM especially on delete statements.
As Optim is certified for Siebel, the Siebel data model has been opened to Optim and is already modeled in our tool to ensure that referential integrity is maintained.

It is a really powerful solution that comes from the aquisition of Princeton Softech 2 years ago.

Do not hesitate to contact me for further details if interested in.

Regards,
Stephane

Abhilash Sasidharan Sr. Siebel Consultant at Speridian Technologies

B.

Jac Beekers Business Intelligence and CRM Architect

Don’t know about you guys, but in my project we have to go through a Non-Standard Change Request @Oracle for every direct update/insert/delete against the base tables.
Using a package which understands the business model of Siebel is much better. We tried Optim, but only for archiving, but it failed due to dependencies with other systems, e.g. OracleBI and integrated systems through EAI.

Garrett Reynolds Managing Partner at Astrenica Consulting

Siebel has (almost, depending on the customer) always stated no SQL against a table. But of course during my 8 years in Siebel Expert Services and PM we used SQL directly against tables all the time. It was a matter of who was doing it and who the customer was. Basically if you know what you are doing then you can do it in the right circumstances.

* If you’re using Siebel Remote, then direct SQL would not be appropriate UNLESS…. you are re-extracting anyway (perhaps during an upgrade) or you are updating data that is not getting routed anyway. Any changes made directly to the database are not be propagated to the transaction log, so won’t be routed to the local databases, so your server will be out of sync with your remote clients.

* If, however, you’re NOT using Siebel Remote, or you’re re-extracting all your local databases anyway, then I’d argue that there is a place for direct SQL. A limited place, sure, requiring utmost care, diligence etc etc, but at the end of the day it’s just another tool to only be used when appropriate. Siebel Expert Services will happily run direct SQL when the alternatives are ridiculously cumbersome. The alternatives (e.g. EIM, EAI) are also quite capable of significantly damaging your database and also require an appropriate level diligence: developers shouldn’t lapse into thinking that just because things are being done with the ‘proper’ methods they can’t do harm (EIM Delete in the wrong mode, anyone?).

* If you use direct SQL to insert a record, then you won’t get a Siebel Row Id. The procedures to generate a Row Id are in the database and could be called directly, but this really is idiotic. (I actually worked on an implementation where we (Siebel Expert Services) generated our own ids.

* we have used SQL to update the created/last updated/ created by fields as well even though these are system fields which are not ‘supposed’ to be touched.

At the end of the day remember that SQL is just another tool, with pros and cons to be weighed against the alternatives… as long as you are diligent and knowledgeable…. and test the heck out of it first.

Mayank Arya Oracle Business Intelligence Techinical Lead at Honeywell

Out of these 4 choices, my answer is “B”. But I fire direct SQLs in following cases:
1. To clean up Workflow tables when it was hanged unexpectedly for 1 Row_Id.
2. Run data scrub to populate new column for old records in S_* tables if adding new columns in a table. I use this scenario very often.

Salvatore (Sam) Priolo Director at University HealthSystem Consortium

In my 10 years of working with Siebel I have always run select statements…even done direct DB updates when I was young and stupid.

Mike Evans Principal Architect at Fujitsu Services

For those of you who have done a Siebel 6 to Siebel 7.x upgrade, where the Siebel implementation was heavily customised… will know that a sequence of SQL statements is needed (on top of those Siebel provides in the upgrade) to make it work.

Typically because the quality of the data in the database is not aligned to the expected structure and purpose of the data by Siebel.

In ealy days, 5.x and 6.x, operational activities did sometimes include SQL statements for data fixes due to problems arising from Siebel runtime instability, problems introduced from scripting and configuration changes that no one can then follow through.

Today, 7.x and 8.x, there is too many engines running within the object manager to support direct SQL data changes in consistent fashion. For example, if the audit engine is switched on then database level changes are not recorded or if run-time events are set against data conditions then these will not be initiated with database level changes.

So unless there is a good case for a data fix, the impact of which has been assessed, then there is no good reason to make any changes to Siebel base table data. (until the next Siebel upgrade that is 😉

Rajasekhar M Siebel Developer at Interoute

Guys, SQL can allow on Siebel DB based on Situation. Think in behind application configuration, they exposed System Columns like Last Updated By and Created By fields. But User made a mistake in data entry and record became read only. in this situation how can you rectify that??????? This time you need to go for SQL. If you use any siebel utility to update this data, automatically Last Updated By will reflect developer name. which should not happend.

Madhu Kempaiah Siebel Lead at Eli Lilly

My answer would be ‘B”. But not D for any normal situation. Cleaning up bad data, updating messed up columns on a siebel created record are normal everyday situations in a major implementation.

3 comments on “When is it acceptable to run SQL against Siebel database tables?”

  1. Hua Reply

    Update database by SQL directly will not trigger LAST_UPD column change.
    This means ETL could not tracked the change and report will not refresh if it is done by incremental load.

  2. Bob Reply

    If you saw this question on the Certification Exam, which is possible, then what would be your answer?  Mine: B only.

  3. sivas Reply

    Locally unlocking a locked Project

    With multiple developers building one application it’s inevitable that multiple developers will need to change the same project at the same time. There’s pros and cons and object-locking and change-control and build-control etc etc, but sometimes deadlines are too tight and you just have to fork.
    In Siebel Tools, once one developer’s got a project locked the UI won’t let a second developer anywhere near it. To get around this you need to get into SQL Anywhere.Login to the second developer’s local database, then the following script will unlock the project locally:

    UPDATE s_project
    SET locked_flg = ‘N’
    WHERE name = <
    Project Name>

    The second developer can now log into Siebel Tools, manually lock the project as normal and do what she has to do. Of course, once build is done the two developers will need to go through a merge exercise to get their changes into the one repository, but that effort can be preferable to losing days of development time.

Leave A Reply

Your email address will not be published. Required fields are marked *

error: Content is protected !!