Unsung Siebel Command Line Heroes: dataimp
After discussing the features and options of the elusive dataexp command line utility, we today talk about dataimp, which obviously serves in the reverse process of importing data from a flat file into Siebel tables.
Before we begin, let's reiterate the words of caution from our last article:
Neither dataexp nor dataimp are fully documented. Their obvious purpose is to serve in the Siebel installation and upgrade process as a flat-file data export and import facility. Please NEVER use them to replace valid Siebel integration solutions such as EIM or EAI.
Ok, you heard me, so now let's fire up dataimp to see what arguments it requires and provides.
Obviously, you will have to provide the following arguments to run a simple import:
/U Username
/P Password
/C ODBC Data Source
/D Table Owner
/F Path to .dat file to import
So the following is an example for a valid dataimp command line:
dataimp /U SADMIN /P SADMIN /C ouisandbox /D SIEBEL /F d:\test.dat
Note that 'ouisandbox' is the name of a custom ODBC data source for testing. In your environment, you will have to use a valid ODBC data source.
Running the above command will launch dataimp and it will (try to) import the data from the file into the respective table (the table names are in the .dat files produced by dataexp). "Try to" means that if records with the same user key already exist, the utility will throw an error, which is actually good and spares us a lot of trouble with duplicates.
Now let's look at the additional arguments we can use to control the behaviour of the 'imp' ;-)
Don't Import Everything
If you set the /E argument to N, you tell dataimp to prompt for table names. Then you can either provide a list of table names as an input file (using the /I argument) or manually enter them at the prompt. As a result, even if the .dat file contains a lot of tables, you can be more selective.
Delete Target Records Before Import
The /W argument - when set to Y - allows you to specify where clauses which will actually define which records to delete from the target table. With this option, you can clean up the table from records that you probably want to reload by the import.
It is also possible to provide the WHERE clause along with the table name in an input file (using the /I argument).
Another way of clearing the table is to set the /T argument to Y which will actually truncate the target table. You sure would run a few tests before doing that on a sensitive database, wouldn't you?
Specify Start Table and Start Row
The /S argument takes the name of a table. In case the .dat file contains data from more than one table you can direct dataimp to skip the other tables in favor of the one you specify with /S.
The /R argument takes an integer and defines the start row at which the import will start, basically skipping the rows before the start row.
Handle Errors
Using the /H argument you can define how dataimp handles errors. The following settings are available:
Transactions Anyone?
The /X argument allows you to specify the transaction unit as (R)ows, (T)able or (F)ile. When you use (R)ow level transactions, the /N argument allows you to specify the number of rows after which the utility commits the transactions to the database.
DB System Columns
There are two arguments that deal with the DB system columns in the target database. These DB system columns display the DB_LAST_UPD timestamp (when the record was last updated in the source DB) and the DB_LAST_UPD_SRC (a key to identify the source system).
The /K argument - when set to Y - uses the value of DB_LAST_UPD as provided in the source file. When set to N, the current timestamp will be used.
The /J argument (default: dataimp) allows to specify a string that will be used to populate the DB_LAST_UPD_SRC column, so you can tag the records.
Miscellaneous
There are other arguments that we could discuss but those mainly deal with DB2 oddities and seem to be very internal.
Summary
The dataimp utility proves powerful for pumping data from a file produced with dataexp into database table. Again, please heed to the words of caution and DO NOT use this as a replacement for real EIM or EAI.
Do you want to share your experiences with dataexp and dataimp? Please find the comments.
have a nice day
@lex
Before we begin, let's reiterate the words of caution from our last article:
Neither dataexp nor dataimp are fully documented. Their obvious purpose is to serve in the Siebel installation and upgrade process as a flat-file data export and import facility. Please NEVER use them to replace valid Siebel integration solutions such as EIM or EAI.
Ok, you heard me, so now let's fire up dataimp to see what arguments it requires and provides.
Obviously, you will have to provide the following arguments to run a simple import:
/U Username
/P Password
/C ODBC Data Source
/D Table Owner
/F Path to .dat file to import
So the following is an example for a valid dataimp command line:
dataimp /U SADMIN /P SADMIN /C ouisandbox /D SIEBEL /F d:\test.dat
Note that 'ouisandbox' is the name of a custom ODBC data source for testing. In your environment, you will have to use a valid ODBC data source.
Running the above command will launch dataimp and it will (try to) import the data from the file into the respective table (the table names are in the .dat files produced by dataexp). "Try to" means that if records with the same user key already exist, the utility will throw an error, which is actually good and spares us a lot of trouble with duplicates.
Now let's look at the additional arguments we can use to control the behaviour of the 'imp' ;-)
Don't Import Everything
If you set the /E argument to N, you tell dataimp to prompt for table names. Then you can either provide a list of table names as an input file (using the /I argument) or manually enter them at the prompt. As a result, even if the .dat file contains a lot of tables, you can be more selective.
Delete Target Records Before Import
The /W argument - when set to Y - allows you to specify where clauses which will actually define which records to delete from the target table. With this option, you can clean up the table from records that you probably want to reload by the import.
It is also possible to provide the WHERE clause along with the table name in an input file (using the /I argument).
Another way of clearing the table is to set the /T argument to Y which will actually truncate the target table. You sure would run a few tests before doing that on a sensitive database, wouldn't you?
Specify Start Table and Start Row
The /S argument takes the name of a table. In case the .dat file contains data from more than one table you can direct dataimp to skip the other tables in favor of the one you specify with /S.
The /R argument takes an integer and defines the start row at which the import will start, basically skipping the rows before the start row.
Handle Errors
Using the /H argument you can define how dataimp handles errors. The following settings are available:
- Abort (default): Stop processing when an error occurs
- Count: Continue after an error and display a summary at the end
- Watch: Continue after an error and display the whole process on the console
- Log: Continue after an error, display on console and write to log.
Transactions Anyone?
The /X argument allows you to specify the transaction unit as (R)ows, (T)able or (F)ile. When you use (R)ow level transactions, the /N argument allows you to specify the number of rows after which the utility commits the transactions to the database.
DB System Columns
There are two arguments that deal with the DB system columns in the target database. These DB system columns display the DB_LAST_UPD timestamp (when the record was last updated in the source DB) and the DB_LAST_UPD_SRC (a key to identify the source system).
The /K argument - when set to Y - uses the value of DB_LAST_UPD as provided in the source file. When set to N, the current timestamp will be used.
The /J argument (default: dataimp) allows to specify a string that will be used to populate the DB_LAST_UPD_SRC column, so you can tag the records.
Miscellaneous
There are other arguments that we could discuss but those mainly deal with DB2 oddities and seem to be very internal.
Summary
The dataimp utility proves powerful for pumping data from a file produced with dataexp into database table. Again, please heed to the words of caution and DO NOT use this as a replacement for real EIM or EAI.
Do you want to share your experiences with dataexp and dataimp? Please find the comments.
have a nice day
@lex
תגובות
הוסף רשומת תגובה