Bookshelf Home | Contents | Index | Search | PDF

Applications Administration Guide > D&B Integration >

Loading D&B Data Using Sample Scripts

---

This section provides some guidance on how to load D&B data on Oracle, DB2, and Microsoft SQL Server platforms using sample scripts. These 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

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 (see Table 31).

   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).

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 (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

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 (see Table 31).

   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).

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 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

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 (see Table 31).

   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).

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.

Bookshelf Home | Contents | Index | Search | PDF