Allowed and Forbidden SQL Usage

Allowed SQL

Data Definition Language (DDL)

Use Case	Description/Comments
Test an Index	When a database performance issue is identified, a customer may request that their DBA investigate the cause, and this is often found to be something that can be fixed with a new index. In these cases, the DBA will typically create a temporary index in the physical schema to see if it solves the problem and does not introduce new ones. This may take several iterations and the DBA is permitted to do this with direct DDL. NOTE: Once the correct indexes are identified, the DBA must provide the final definition to the Siebel team to formalize in the Siebel Repository. Failure to do this will result in the index being removed automatically the next time a DDLIMP process executes.
Pre-execute DDL changes from an Oracle-provided Siebel utility.	When a process requires making changes to the database schema, such as Migration or utilities associated with Monthly Updates, the DBA may apply such changes in advance, for example: To avoid potential downtime due to table locks for high usage tables by making the changes during low usage or scheduled downtime. For many customers–either for security or other reasons–the "Siebel Team" does not have tableowner access required to execute DDL–only a DBA has that privilege (and is not negotiable with the customer). All Siebel utilities provide basic DDL for schema changes to enable this option. DBAs may modify the DDL for database storage requirements–such as specifying alternate tablespaces, extents, etc., but may not make changes to table or index structure. Note that in the cases where DBAs will be applying DDL, those DDL changes must be applied before attempting to continue to execute the remaining steps in a given process--For example, it is not acceptable to run the non-schema portions of PostInstallDBSetup before the schema portions.
View creation	The DBA may create read-only database views against any table to facilitate with integration with other systems, External Business Components, Virtual Business Components, or any other reason.
Temporary Tables	The DBA may create temporary tables as required for a temporary purpose. Examples: Assist in getting data into an EIM table, for example, SELECT from a base table into a temporary table, then manipulate that data with the eventual goal being to get it into the corresponding EIM table. Facilitating execution of an Oracle-provided Siebel utility. Note that this is a very unusual corner case, but an example is that "WFCleanup" needs a temporary table to execute and there is a known bug where that table is sometimes not automatically created.

Data Manipulation (DML)

Operation	Description	Example	Allowed
C	Create	INSERT INTO...	Inserts into EIM tables (only) are allowed. Note that since Reads are always allowed (even against base tables), it is acceptable to have a statement of the form "INSERT INTO EIM_x SELECT ... FROM S_..."
R	Read	SELECT ... FROM ...	Selects from any table are allowed (including base tables), as SELECTs do not affect data.NOTE: Too many SELECT statements may impact performance, but from an "allowed/not allowed" perspective, they are allowed.
U	Update	UPDATE ...	Updates to EIM tables (only) are allowed.
D	Delete	DELETE FROM...	Deletes from EIM tables (only) are allowed.

Forbidden SQL Usage

Data Definition Language (DDL)

Use Case	Description/Comments
Ad hoc tables, columns, etc. not defined in the Repository.	NOTE: Failure to formalize the objects in the Repository will result in the objects being dropped during execution of certain Siebel utilities.
Attempt to work around functionality not available in Siebel Tools/Web Tools	The DBA cannot create (nor modify) schema objects in a way that cannot be done in Siebel Tools/Web Tools. Rephrasing: If you cannot make a change in Siebel Tools/Web Tools, your DBA cannot do it at the physical layer via DDL. Examples: Changing the size of an OOTB column directly at the database layer. It is not possible to change the length via Tools, therefore it is not supported via DDL. Modifying an OOTB index (such as adding index columns, sort order, etc.) As of August 2024, there is no support for Function-Based Indexes on the Microsoft SQL Server platform–an attempt to work around this at the physical database layer is not supported. Modification of OOTB Unique Indexes ← this is particularly dangerous Deleting OOTB Indexes
Delete OOTB objects	An attempt to delete or modify an out-of-the-box object directly at the database level (without making a change in the Repository) will result in it being put back the next time a Siebel DB utility runs. Note: If an Index is causing a performance problem, it should be inactivated in the Repository once testing is complete; if not, it will be restored the next time a Siebel DB utility runs.
Index creation/modification at physical layer without eventually being reflected in the Repository.	This is not permitted–as noted above, it is fine to test indexes using DDL, but they must be formalized in the Repository. NOTE: Failure to formalize the index in the Repository will result in the index being dropped.

Data Manipulation (DML)

Operation	Description	Example	Forbidden
C	Create	INSERT INTO...	Inserts into non-EIM tables are never permitted.
R	Read	SELECT ... FROM ...	There are no restrictions on SELECT statements, but note that too many SELECT statements may impact performance, but from an "allowed/not allowed" perspective, there is no restriction.
U	Update	UPDATE ...	Updates to non-EIMs table are never permitted.
D	Delete	DELETE FROM...	Deletes from non-EIM tables are never permitted.Note that this includes "cleaning up" tables that tend to grow "forever", such as the Audit Log, Transaction Log, etc. Rather than delete, it is important to find out why such things are happening and eliminate the cause.