Siebel Applications Administration Guide > D&B Integration > Integrating D&B Data >
Loading D&B Data Using Sample Scripts
This topic provides some guidance about how to use sample scripts for:
CAUTION: Before running scripts, check them against the D&B data layout documentation and against the S_DNB_UPDATE table columns. Then, modify the scripts as required. For information about the S_DNB_UPDATE table columns, see the applicable Siebel Data Model Reference on My Oracle Support.
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 (required only if loading the SIC file)
- s_dnb_mrc.ctl (required only if loading the MRC file)
NOTE: This procedure assumes 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. For more information, 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 by 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 (required only if loading the SIC file)
- imp_mrc_new.sql (required only if loading the MRC file)
NOTE: This procedure assumes 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. For more information, 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 by 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 assumes 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. For more information, 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 by 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.
|