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 permissable activities that a DBA can do in a database, refer to the section Limitations on Use of Direct SQL Against Siebel Databases in the Configuration 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 would 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 |
|---|---|---|---|---|
| 0 | Use Crossload | N/A | N/A | This function is used to set crossload attribute for 2 V8 the load message file "load.msg" is written to current directory. |
| ⁄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 | DB2390 specific | ||
| ⁄8 | Module List Filename | DB2390 specific | ||
| ⁄9 | Support Descending index | Obsolete | ||
| ⁄# | New Install | N | Used in the case that this is a new Siebel Database. | |
| ⁄$ | Drop Indexes | 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. | |
| ⁄@ | 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 . |
| ⁄a | Schema Name | N/A | DB2390 specific- Specifies the schema name | |
| ⁄b | Default Tablespace | Oracle⁄DB2 | N/A | The default Tablespace for storing tables |
| ⁄c | ODBC Data Source | Yes | Env Variable SIEBEL_DATA_SOURCE | The ODBC source for the database connection. This must already exist. |
| ⁄d | Drop Extraneous Tables | N | Obsolete; has no effect | |
| ⁄e | Stop on DDL Error | Y | Stop if there is any error. | |
| ⁄f | DDL Filename | Yes | 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. | |
| ⁄g | Grantee for Tables | Yes | The Group⁄Role that gives users access to the Siebel Database tables (typically SSE_ROLE) | |
| ⁄h | Force Rebuild instead of Alter | N/A | N | Obsolete; has no effect |
| ⁄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" |
| ⁄j | Rebuild Unalterable tables | N/A | Y | Obsolete; has no effect |
| ⁄k | 16K Page Tablespace | DB2 | N/A | DB2 specific: The default 16k Page Tablespace |
| ⁄l | Log Filename | N/A | ddlimp.log | Determines the log file name for this execution of the utility |
| ⁄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. |
| ⁄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. |
| ⁄o | Ignore Alter | N/A | N | Obsolete; has no effect |
| ⁄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. |
| ⁄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. |
| ⁄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. |
| ⁄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. |
| ⁄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" |
| ⁄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. |
| ⁄v | 32K Page Tablespace | DB2 | N/A | DB2 specific: The default 32k Page Tablespace |
| ⁄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: This is for use with Oracle Databases. |
| ⁄x | Default Indexspace | Oracle⁄DB2 | N/A | The default Indexspace for storing indexes |
| ⁄y | Table Groupings Filename | N/A | N/A | DB2390 specific--The name of the Storage Control File (formerly know as the "Table Groupings Filename") |
| ⁄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: This is for use with Oracle Databases. |
| ⁄~ | Use ONLINE Clause | N/A | Y | Allows DDLIMP to run without downtime in live databases--for more information, see How DDLIMP Prevents Downtime During PostInstallDBSetup, RepositoryUpgrade, and Migration |
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 is common that the Siebel team members will not have access to the Tableowner password and therefore cannot apply DDL changes. In that situation, the Siebel team can still generate the DDL to support Siebel by running the dataimp utility with the "/Q" flag and specifying a filename. 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 are 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.