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:

• Loading D&B Data on Oracle
• Loading D&B Data on DB2
• Loading D&B Data on Microsoft SQL Server

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

1. Verify that the column order of your D&B file matches the column requirements in the CTL file before running the loading script.
2. 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).

3. 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
4. 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

1. Verify that the column order of your D&B file matches the column requirements in the SQL file before running the loading script.
2. 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).

3. 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
4. Run DB2 IMPORT.
5. (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

1. Verify that the column order of your D&B file matches the column requirements in the SQL file before running the loading script.
2. 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).

3. 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
4. Run bcp or another data loading utility.