Applications Administration Guide > D&B Integration > Process of Integrating D&B Data >
Loading D&B Data Using Sample Scripts
This section provides some guidance on how to use sample scripts for:
The sample scripts are not provided as part of the Siebel product, but may be obtained from Siebel Professional Services. CAUTION: Before running scripts, you should check them against the D&B data layout documentation and against the S_DNB_UPDATE table columns documented in Siebel Data Model Reference. Then, modify the scripts as required.
Loading D&B Data on Oracle
Oracle installations require you to run SQLLOADER control files. You need the following control files:
- s_dnb_update.ctl (for loading Marketing Data file into S_DNB_UPDATE table)
- s_dnb_sic.ctl (only required if loading the SIC file)
- s_dnb_mrc.ctl (only required if loading the MRC file)
NOTE: This procedure is based on the assumption that you understand how to run SQLLOADER. SQLLOADER is described in the Oracle documentation.
To load D&B data if you are using Oracle
- Verify that the column order of your D&B file matches the column requirements in the CTL file before running the loading script.
- Truncate the appropriate staging tables (see Table 74).
To optimize loading performance, drop all indexes on the S_DNB_UPDATE table and re-create them after the scripts have completed. This can be done using Siebel utilities ddlexp (to back up the indexes before dropping) and ddlimp (to re-create them).
- Modify the SQL file as appropriate for:
- The name of your database
- The D&B filename and directory location
- The database table owner
- The user ID and password (with loading privileges) that you are using to load the data
- Run SQLLOADER.
Loading D&B Data on DB2
DB2 installations require you to run DB2 IMPORT. You need the following SQL files:
- imp_update_new.sql (for loading Marketing Data file into S_DNB_UPDATE table)
- imp_sic_new.sql (only required if loading the SIC file)
- imp_mrc_new.sql (only required if loading the MRC file)
NOTE: This procedure is based on the assumption that you understand how to run DB2 IMPORT. DB2 IMPORT is described in the DB2 documentation.
To load D&B data if you are using DB2
- Verify that the column order of your D&B file matches the column requirements in the SQL file before running the loading script.
- Truncate the appropriate staging tables (see Table 74).
To optimize loading performance, drop all indexes on the S_DNB_UPDATE table and re-create them after the scripts have completed. This can be done using Siebel utilities ddlexp (to back up the indexes before dropping) and ddlimp (to re-create them).
- Modify the SQL file as appropriate for:
- The name of your database
- The D&B filenames and directory location
- The database table owner
- The user ID and password (with loading privileges) that you are using to load the data
- Run DB2 IMPORT.
- Optional: After running the import scripts, update the DB2 catalog statistics by executing RUNSTATS on the three tables that received the D&B data (S_DNB_MRC, S_DNB_SIC, S_DNB_UPDATE).
Loading D&B Data on Microsoft SQL Server
Microsoft SQL Server installations require you to run SQL scripts. You need the following format files and SQL scripts:
- For loading the Marketing Data file:
- S_DNB_UPDATE.fmt—a format file to load the data into the tempS_DNB_UPDATE table
- S_DNB_UPDATE.sql—an SQL script that loads the data into S_DNB_UPDATE table
- For loading the SIC file:
- S_DNB_SIC.fmt—a format file to load the data into the tempS_DNB_SIC table
- S_DNB_SIC.sql—an SQL script that loads the data into the S_DNB_SIC table
- For loading the MRC file:
- S_DNB_MRC.fmt—a format file to load the data into the tempS_DNB_MRC table
- S_DNB_MRC.sql—an SQL script that loads the data into the S_DNB_MRC table
NOTE: This procedure is based on the assumption that you understand how to run bcp (bulk copy process) or similar data-loading utility. The bcp utility is described in the Microsoft SQL Server documentation.
To load D&B data if you are using Microsoft SQL Server
- Verify that the column order of your D&B file matches the column requirements in the SQL file before running the loading script.
- Truncate the appropriate staging tables (see Table 74).
To optimize loading performance, drop all indexes on the S_DNB_UPDATE table and re-create them after the scripts have completed. This can be done using Siebel utilities ddlexp (to back up the indexes before dropping) and ddlimp (to re-create them).
- Modify the SQL file as appropriate for:
- The name of your database
- The D&B filename and directory location
- The database table owner
- The user ID and password (with loading privileges) that you are using to load the data
- Run bcp or another data loading utility.
|