About DDL Import
The Data Definition Language Import utility, commonly known as "DDLIMP", synchronizes the physical database scheme to match the logical schema definition as defined in the Siebel Repository. If there are discrepancies, the logical definition will win. For example, if an index were manually added to the database by a DBA, it will be removed.
All schema items must therefore be defined in the logical schema. For more information on permissible activities that a DBA can do in a database, see Limitations on Use of Direct SQL Against Siebel Databases in the Configuring Siebel Business Applications guide.
Basic Usage of DDLIMP
The purpose of DDLIMP is to load or update the physical schema in the database. Examples of this include:
- Installing a fresh Siebel Database.
- In preparation for upgrading a Siebel Database.
- During the installation of a Monthly Update (via "PostInstallDBSetup")
- When importing new features made available in a Monthly Update (via "RepositoryUpgrade")
- As part of Migration of Repository changes to a Test or Production environment
Beyond the basic parameters you'd expect, such as usernames and passwords, the key input is the desired schema definition, which is found in a file typically named ddl.ctl, meaning a DDL Control File. This file can come from several places:
- For fresh installations and Development Upgrades, a full ddl.ctl file is provided by Oracle with the desired schema definition (for upgrades, this is additive only).
- For Test and Production Upgrades, the ddl.ctl files are generated from the customized Development environment.
- During Migration, the underlying process generates a CTL file that contains only the changes required based on recent configuration.
For more information on CTL files, see About DDLDICT.
Command Line Arguments
| Flag | Parameter | Required | Default | Description |
|---|---|---|---|---|
| ⁄u | Table Owner Username | Yes | N/A | This must be the tableowner's username, rather than a Siebel user (such as SADMIN) as this utility makes changes to the physical schema. |
| ⁄p | Password | Yes--see Description | N/A |
The tableowner's password. Either this or the following parameter is required. See, Process of Using Siebel Migration to Migrate Data. |
| ⁄@ | Encrypted Password | Yes--see Description | N/A |
The tableowner's password. Either this or the previous parameter is required. See the section Process of Using Siebel Migration to Migrate Data. |
| ⁄c | ODBC Data Source | Yes | Env Variable SIEBEL_DATA_SOURCE | The ODBC source for the database connection. This must already exist. |
| ⁄f | DDL Filename | Yes | N/A | The Control (CTL) file with the requested changes. This would typically be the output of a previous call to "DDLDICT", see the section About DDLDICT. |
| ⁄e | Stop on DDL Error | N/A | Y | Stop if there is any error. |
| ⁄m | Generate DDL for merge | N/A | Y | Determines whether to merge tables with any existing table of the same name. If this is set to "N" and a table already exists, DDLIMP will fail. Oracle recommends leaving this as the default. |
| /d | Drop Extraneous Tables | N/A | N | Removes any physical database tables that aren't defined in the Siebel Repository |
| ⁄n | Don't Drop Columns | N/A | Y | Whether or not to drop columns that do not appear in the logical Siebel Repository. Note: While the default is "Y" to avoid data loss, it is Oracle's recommendation that this be set to "N". Only columns defined in the logical Repository should exist in the physical schema. |
| ⁄$ | Drop Indexes | N/A | Y | Whether or not to drop indexes that do not appear in the logical Siebel Repository. By default, indexes in the physical schema that are missing from the logical Repository will be dropped. |
| ⁄# | New Install | N/A | N | Used in the case that this is a new Siebel Database. |
| ⁄t | Import Tables | N/A | Y | Indicates that tables should be imported. If this is not desired, for example, to separate storage of tables and indexes, this could be set to "N". |
| ⁄i | Create and Merge Indexes | N/A | Y | Indicates that indexes should be imported. If this is not desired--for example, to separate storage of tables and indexes, this could be set to "N". |
| ⁄g | Grantee for Tables | Yes | N/A | The Group⁄Role that gives users access to the Siebel Database tables (typically SSE_ROLE). |
| ⁄r | Regrant Tables | N/A | N | Whether or not DDLIMP should regrant permissions on existing tables. This should typically be the default ("N") because once a table has been granted, adding a column nor index will require a regrant. However, if permissions are out of sync, setting this flag can force them to be reset. |
| ⁄b | Default Tablespace | Oracle⁄DB2 | N/A | The default Tablespace for storing tables. |
| ⁄x | Default Indexspace | Oracle⁄DB2 | N/A | The default Indexspace for storing indexes. |
| ⁄k | 16K Page Tablespace | DB2 | N/A | DB2 specific: The default 16k Page Tablespace. |
| ⁄v | 32K Page Tablespace | DB2 | N/A | DB2 specific: The default 32k Page Tablespace. |
| ⁄q | SQL Filename | N/A | N/A | If a filename is provided, the sequence SQL statements will be written to that file instead of being executed. |
| ⁄l | Log Filename | N/A | ddlimp.log | Determines the log file name for this execution of the utility. |
| ⁄o | Ignore Alter | N/A | N | Obsolete; has no effect |
| ⁄j | Rebuild Unalterable tables | N/A | Y | Obsolete; has no effect |
| ⁄h | Force Rebuild instead of Alter | N/A | N | Obsolete; has no effect |
| ⁄~ | Use ONLINE Clause | N/A | Y | Allows DDLIMP to run without downtime in live databases, for more information, see How DDLIMP Avoids Downtime Due to Database Contention. |
| ⁄w | UTF8 Database | N/A | N |
Specifies that the database is UTF8. If this is set to "Y", the ⁄Z parameter must be "N" or omitted. Note that this is for use with Oracle Databases. |
| ⁄z | UCS2 Database | `N/A | Y |
Specifies that the database is Unicode enabled. If this is set to "Y", the ⁄W parameter must be "N" or omitted. Note that this is for use with non-Oracle Databases. |
| ⁄s | Parallelism and Nologging | N/A | Y | Adds database parameters "Parallel" and "Nologging" for Oracle Databases if "Y". Consult with an Oracle DBA about the impact of these parameters. |
| ⁄y | Table Groupings Filename | N/A | N/A | DB2390 specific--The name of the Storage Control File (formerly know as the "Table Groupings Filename") |
| ⁄a | Schema Name | N/A | N/A | DB2390 specific--Specifies the schema name |
| ⁄1 | Use Load⁄Unload | N/A | N/A | DB2390 specific |
| ⁄2 | File Name for Load Script | N/A | N/A | DB2390 specific |
| ⁄3 | File Name for Unload Script | N/A | N/A | DB2390 specific |
| ⁄4 | One Sub System | N/A | N/A | DB2390 specific |
| ⁄5 | Create and Merge Databases⁄Tablespaces | N/A | N/A | DB2390 specific |
| ⁄6 | Commit Frequency | N/A | N/A | DB2390 specific – Determines how many DDL statements will be run before a COMMIT. |
| ⁄7 | 2nd SubSystem Schema Name | N/A | N/A | DB2390 specific |
| ⁄8 | Module List Filename | N/A | N/A | DB2390 specific |
| ⁄9 | Support Descending index | N/A | N/A | Obsolete |
| ⁄0 | Use Crossload | N/A | N/A | DB2390 specific |
Asynchronous Execution of DDL
In many environments, the Siebel CRM database is maintained by a Database Administrator (DBA) outside of the Siebel team. In those scenario, it's common that the Siebel team members to not have access to the Tableowner password and therefore can't apply DDL changes. In this situation, the Siebel team can still generate the DDL to support Siebel by running the dataimp utility with the "/Q" flag and specifying a file name. This will generate DDL into that file that can be given to a DBA for manual execution, and the DBA can let the Siebel team know when the changes have been implemented so that they can continue forward with the larger process that they're performing (such as migration).
How DDLIMP Avoids Downtime Due to Database Contention
When DDL is executed against an existing table, it is possible that the changes for that table will cause a table lock, preventing users from continuing to use the system until the DDL changes are committed. To avoid this, DDLIMP has been designed to leverage the capabilities of modern database platforms (for Oracle and Microsoft SQL Server) to make DDL changes in live (or "online") mode, therefore eliminating the possibility of such table contention.