You can use the bcp utility to process and load initial-state data into a Microsoft SQL Server database. The bcp utility can only be used with the Event Replicator Target Adapter to load initial-state data; it cannot be used for transactional data.
The following prerequisites must be met before the bcp utility can be used to load initial-state data to a Microsoft SQL Server database, via the Event Replicator Target Adapter:
The Microsoft SQL Server utilities supporting the bcp utility must be installed on the same machine as the Event Replicator Target Adapter. The bcp utility version 9 or greater must be installed.
Be sure there is enough space to accommodate the *.BCPDAT files that will be created by this processing.
You can use the bcp utility to load initial-state data only if:
Composite keys are not used in the data.
To enable bcp utility initial-state processing, complete the
following steps:
Using the Administration tool, configure a target definition for your Microsoft SQL Server database. For more information, read Configuring Target Definitions for Event Replicator Target Adapter.
Using the Administration tool, create a specific target processing option definition for your Microsoft SQL Server database.
In the Loader options area, check the box Use Loader and provide a value for the Path to Loader Executable field, identifying the full path of the Microsoft SQL Server bcp utility executable. For example:
C:\Program Files\Microsoft SQL Server\90\Tools\Binn\bcp.exe
Optionally, in the Loader options area of the target processing option definition panel, specify the MS SQL Server schema name if your schema name is different from the MS SQL Server default schema name. Prior to the release of MS SQL Server 2005, all tables created by MS SQL Server used the user login name as the default schema name; in MS SQL Server 2005 and later versions, Microsoft uses "dbo" as the default schema name (which is different from the user login name). If you have used any MS SQL Server schema name that is not the same as the MS SQL Server default schema name, specify your schema name in the Oracle Service or Teradata Tdpip field.
Specify the following options, as required for your installation, in the Options field.
| Option | Description |
|---|---|
| -b batchsize |
Use the -b (batch size) option in this field to specify the number of rows per batch of imported data. Each batch is imported and logged as a separate transaction that imports the whole batch before being committed. By default, all rows in the data file are imported as one batch. To distribute the rows among multiple batches, use the -b option to specify a batch size that is smaller than the number of rows in the data file. If the transaction for any batch fails, only insertions from the current batch are rolled back. Batches already imported by committed transaction are unaffected by a later failure. Do not use this option in conjunction with the -h ("ROWS_PER_BATCH=bb") option. |
Note:
The Environment File, and
Use Stream fields in the Loader options area have no
effect on the bcp utility, so they should not be specified.
Save the target processing option definition with the same name as the target definition you created in Step 1.
When all processing options are specified, start initial-state processing, directing the output to the Event Replicator Target Adapter via a GFFT you created (using the Data Mapping Tool or the Adabas Event Replicator Subsystem) for the Microsoft SQL Server database. For more information on this, read Requesting Initial-State Data. The Event Replicator Target Adapter will collect the initial-state records and send them to the bcp utility to be loaded to your database.
The output from this processing includes:
Various bcp utility processing files (*.FMT files)
bcp utility log files from the processing (*.BCPERR and *.BCPOUT files)
Binary data files (*.BCPDAT files) containing the initial-state data received by the Event Replicator Target Adapter prior to being processed by the bcp utility, and possibly *.DBGDAT files (if the debug logging option is turned on)
An output file (*.out file) containing output messages from the final loader process run.
All output files are written to the \logs subdirectory of your Event Replicator Target Adapter installation
The Event Replicator Target Adapter's bcp utility processing creates three processing files per table loaded. The following processing files are stored in the \logs subdirectory of your Event Replicator Target Adapter installation:
A single xxxx_yyyymmdd_hhmmss.BAT file for all bcp utility processing (where xxxx is the base table name, yyyymmdd is the date, and hhmmss is the time: This .BAT file is used to run the bcp utility. It generates the format file (*.FMT) for each table and loads the binary *.BCPDAT file into the database.
One xxxx_yyyymmdd_hhmmss.FMT file
for each table (where xxxx is the table name): This is the
format file for the bcp utility processing.
One or more xxxx_yyyymmdd_hhmmss.BCPDAT file for each table (where xxxx is the table name, yyyymmdd is the date, and hhmmss is the time): These files contain the data to be loaded to the database tables by the bcp utility. These files are in compressed binary format, but can still take a good deal of space, so make sure that there is enough space to accommodate them and be sure to manually delete them when initial-state processing has completed successfully.
If the file debug logging option is turned on, the resulting debugging information is stored in a debug file with a name in the format xxxx_yyyymmdd_hhmmss.DBGDAT, where xxxx is the table name, yyyymmdd is the date of the debug file, and hhmmss is the time the debug file was last updated. Multiple debug files may be produced for a single table.
Several kinds of log files are produced by Event Replicator Target Adapter bcp utility processing: a primary log file and multiple processing log files. All log files are stored in the \logs subdirectory of your Event Replicator Target Adapter installation.
The bcp utility primary log file makes references to the processing log files. The primary log file has a name in the format xxxx_yyyymmdd_hhmmss_nnn.out, where xxxx is the base table name, yyyymmdd is the date of the log file, and hhmmss_nnn is the time (hours, minutes, seconds and milliseconds) the log file was last updated. A single primary log file is created for all bcp utility processing. If bcp utility processing fails for some reason, we recommend reviewing these primary *.out log files first when resolving the problem, proceeding then to the processing log files, as necessary.
Multiple processing log files are created, one for each bcp process executed by the Event Replicator Target Adapter. These processing log files have names in the format xxxx_yyyymmdd_hhmmss.BCPOUT, where xxxx is the table name, yyyymmdd is the processing date (year, month, and day) and hhmmss represents the processing time (hours, minutes, and seconds).
Any errors encountered during bcp utility processing will be written to error files with names in the format xxxx_yyyymmdd_hhmmss.BCPERR, where xxxx is the table name, yyyymmdd is the processing date (year, month, and day) and hhmmss represents the processing time (hours, minutes, and seconds). A single*.BCPERR file is created for each table.