6.68 GENLOADFILES

Valid For

Replicat

Description

Use the GENLOADFILES parameter when using the file-to-database-utility initial load method to generate run and control files that are compatible with:

Oracle's SQL*Loader utility
Microsoft's BCP, DTS, or SQL Server Integration Services (SSIS) utility
IBM's Load Utility (LOADUTIL).

A run file and a control file are generated for each MAP statement in the Replicat parameter file. Replicat stops after generating the control and run files and does not process data.

Use the run and control files with a data file that contains the data to be loaded into the target. To generate the data file, use the FORMATASCII parameter in the Extract parameter file. Use the SQLLOADER option of FORMATASCII for the Oracle and DB2 for z/OS utilities and use the BCP option for the Microsoft utility.

FORMATASCII outputs the table data to an Oracle GoldenGate trail or file in external ASCII format, which is compatible with the load utility. You can generate multiple data files by specifying multiple files.

Note:

For IBM's Load Utility, you will need to specify the -E and -d defs_file . These parameters are necessary to convert ASCII to EBCDIC and to specify the source-definitions file.

By default, GENLOADFILES creates the following file names:

The SQL*Loader run file is named source_table.run, and the control file is named source_table.ctl, where source_table is the name of a source table specified in the MAP statement.
The BCP/DTS/SSIS run file is named target_table.bat, and the control file is named target_table.fmt, where target_table is the name of a target table specified in the MAP statement.
The Load Utility run file is named target_table.run, and the control file is named target_table.ctl, where target_table is the name of a target table specified in the MAP statement.

Control Files

The control file contains load parameters that are generated based on a template. Oracle GoldenGate provides default templates for SQL*Loader, BCP/DTS/SSIS, and Load Utility. You can modify the templates as needed to change the load rules, or you can create new templates.

The following are examples of the Oracle GoldenGate templates, which contain placeholders for the target tables, the data file(s) produced by FORMATASCII, and other run parameters. Oracle GoldenGate replaces the placeholders with values based on parameters specified in the Replicat parameter file.

Example 6-1 SQL*Loader Template sqlldr.tpl

# File Names
controlfile ?target.ctl
runfile     ?target.run
#
# Run File Template
sqlldr userid=?pw control=?target log=?target direct=true
#
# Control File Template
unrecoverable
load data
infile ?source.dat
truncate
into table ?target

Example 6-2 BCP/DTS/SSIS Template bcpfmt.tpl

# Run File Template
# Substitute your database name for db
bcp db..?target in ?source.dat -U ?user -P ?pw -f ?target.fmt -e ?target.err
#
# Control File Template
# The value below must specify the BCP or Microsoft SQL Server 
#version. "bcp -v" can be used to
# determine the correct version number.
12.0

Example 6-3 Load Utility Template db2cntl.tpl

# File Names
controlfile ?target.ctl
runfile     ?target.run
#
# Run File Template
odb2 load
#
# Control File Template
LOAD REPLACE INTO TABLE ?target

Run Files

The run files contain the input parameters for starting the load. To execute the files, issue one of the following commands.

Execute the SQL*Loader run file from the UNIX command shell.
```
% table.run
```
Execute the BCP run file from the DOS shell.
```
> table.bat
```
Execute the Load Utility run file with a JCL script to load the data to the DB2 for z/OS table. Add other environment-related parameters to the job script as needed.

Note:

A setting of DYNAMIC for the WILDCARDRESOLVE parameter is not compatible with the GENLOADFILES parameter. Oracle GoldenGate defaults to IMMEDIATE when GENLOADFILES is specified.

Note that Oracle GoldenGate does not support multi-byte characters when the operating system and database character set are different, or when fixed length output format is used.

For step-by-step instructions on configuring Oracle GoldenGate to output the load files and performing the initial load, see the Administering Oracle GoldenGate.

Default

None

Syntax

GENLOADFILES [template_file]
[CHARSET value]

template_file

The fully qualified name of the template file. The default template file is sqlldr.tpl for SQL*Loader, bcpfmt.tpl for BCP, DTS, or SSIS, and db2cntl.tpl for DB2 on z/OS, all located in the Oracle GoldenGate home directory.

CHARSET set

(Oracle SQL*Loader) Specifies the encoding of ASCII characters in Oracle NCHAR columns. Valid value is UTF8. Required if using CHARSET option of FORMATASCII.

CHARSET allows the load to include character-length semantics when the source table contains NCHAR data and variable-length characters set to UTF-8. Currently, Oracle SQL*Loader uses byte-length semantics and is not compatible with character-length semantics.

Note:

If both NCHAR and CHAR columns contain 8-bit ASCII characters, the generated file will contain a mix of operating system-native 8-bit ASCII character coding and UTF-8 coding, and the load will not succeed.

Example

GENLOADFILES sqlldr.tpl