10 Using Oracle GoldenGate Utilities
This topic includes the following:
Generating Data Definitions with DEFGEN
When capturing, transforming, and delivering data across disparate systems and databases, you must understand both the source and target layouts. Understanding column names and data types is instrumental to the data synchronization functions of Oracle GoldenGate.
The DEFGEN
utility produces a file defining the source files and tables' layouts. These definitions are used by the Collector and by Replicat. In some cases, Extract also uses a definition file containing the target layouts when transformation operations are required on the source system.
The output definitions are written and saved to a text file and transferred to all target systems in text format. When they start, Replicat and the Collector read the definitions to interpret the data formats read from Oracle GoldenGate trails.
Note:
Do not modify the text file that is output from DEFGEN
.
Once you have generated your definitions, you must specify their location in your process' parameter file. Replicat uses the SOURCEDEFS
parameter to indicate which source definition file to use. Collector uses the –d
argument at startup to specify which source definition file to use.
Run DEFGEN
interactively or using a batch obey script.
TACL> RUN DEFGEN [/INcommand_file
/] [EXCLUDESYSTEM] [EXPANDDDLoptions
] [RECORDNAMEPROMPTING]
For more information, see DEFGEN Arguments.
A Sample Definitions File
Definition for table $DATA1.GGSSOU.TCUSTMER Record length: 198 Syskey: 0 Columns: 13 TS 134 8 0 0 0 0 1 8 8 0 0 0 0 1 0 1 RECNUM 132 4 8 0 0 0 1 4 4 4 0 0 0 0 1 0 1 SYSNAME 1 8 12 0 0 0 0 8 8 8 0 0 0 0 1 0 0 TEXT 0 64 20 0 0 0 0 64 64 64 0 0 0 0 1 0 0 VAL1 134 8 84 0 0 0 1 8 8 8 0 0 0 0 1 0 0 VAL2 134 8 92 0 0 0 1 8 8 8 0 0 0 0 1 0 0 COL_COMPUTE 134 8 100 0 0 0 1 8 8 8 0 0 0 0 1 0 0 I16 130 2 108 0 0 0 1 2 2 2 0 0 0 0 1 0 0 I32 132 4 110 0 0 0 1 4 4 4 0 0 0 0 1 0 0 I64 134 8 114 0 0 0 1 8 8 8 0 0 0 0 1 0 0 I32_TOTAL 132 4 122 0 0 0 1 4 4 4 0 0 0 0 1 0 0 JTS 134 8 126 0 0 0 1 8 8 8 0 0 0 0 1 0 0 JTS_TEXT 0 64 134 0 0 0 0 64 64 64 0 0 0 0 1 0 0 End of definition
Running DEFGEN to Use Existing Definitions
Multiple tables that have the same structure (identical columns, column order, and data types) can use the same definition. To run DEFGEN
for these tables, use the RECORDNAMEPROMPTING
argument.
In response to the prompts, enter information similar to the following example:
For the prompt: | Enter: |
---|---|
Enter definitions file name (or Exit): |
$DATA1.GGSDEF.CUSTDEF |
File/Table to create definition for (or Exit): |
$DATA1.GGSSOU.ECUSTMER |
Use record name for definition file (Y/N)? |
Y |
Record or definition name to be used: |
CUSTOMER-DEF |
File/Table to create definition for (or Exit) |
EXIT |
Creating Target Database DDL
DDLGEN
generates table definitions for target databases based on existing Enscribe and NonStop SQL definitions. It can also use the output from the DEFGEN
utility (see above). DDLGEN
reduces the work necessary to create databases on platforms such as UNIX or Windows, and enables the creation of NonStop SQL databases based on Enscribe definitions. Target templates are provided for NonStop SQL, Oracle, SQL Server, DB2, and Sybase.
DDLGEN
is run either interactively by supplying responses to user prompts, or in batch mode by supplying an input file.When running interactively, the user is supplied several prompts. For batch execution, the answers to the prompts are supplied in the obey file. It is recommended that the user runs the process in interactive mode to better understand the replies to specify in the obey file.
The result of running DDLGEN
is a text file containing the create table statements. Transfer this file, as a text file, to your target system.
For more information, see DDLGEN Arguments in the Reference for Oracle GoldenGate on HP NonStop Guardian.
The DDLGEN
syntax is:
TACL> RUN DDLGEN [/INcommand_file
/] [-dDEFGEN_output
]
Argument | Description |
---|---|
IN |
If you have created and saved a file of responses using the NonStop editor, enter the name of that file. See the information on configuring |
-d |
Instructs |
Addressing Enscribe DDL Peculiarities
Enscribe record definitions often contain the following items that do not map directly to SQL environments:
-
OCCURS
itemsSQL columns cannot have multiple occurrences, while Enscribe fields can.
-
Group level items
There is no grouping hierarchy in SQL, while an Enscribe record definition can contain fields that are redundant until qualified at the group level. This means that redundant column names can be created when mapping Enscribe definitions to SQL. For example, an Enscribe record might contain the field
YEAR
twice, once within theBEGIN-DATE
group and once within theEND-DATE
group.
To get around these conditions, you can run the DEFGEN
utility with various EXPANDDDL
options set, as described in Reference for Oracle GoldenGate on HP NonStop Guardian. Use the -d
parameter to specify the definitions file created by DEFGEN
as input to DDLGEN
.
Understanding the Template File
Templates are provided with each version of Oracle GoldenGate. A template file specifies how to generate the target definitions based on the source definitions. Each template file contains the following items:
-
Literal text to output for each table definition.
-
Source to target data type conversion specifications.
-
Column name substitution specifications.
-
Miscellaneous run-time parameters.
-
Section headers.
-
Comments, which begin with a pound sign (
#
). -
Session parameters, which are resolved at run-time by user prompts and applied during the entire
DDLGEN
session. Session parameters begin with a question mark (?). -
Per-table parameters, which are input by the user for each table definition generated. Per-table parameters begin with a percent sign (
%
). -
Calculated parameters, which include information determined by
DDLGEN
. Calculated parameters include the following:Calculated Parameter Description ?TABLE
The file name portion of the source table or file.
?COLUMNS
A list containing each column, its target data type, precision and scale (if any), and null/not null syntax.
?KEYCOLUMNS
A list containing each column in the primary key.
?MAXPAGES ?MAXMEGS ?CURPAGES ?CURMEGS
The maximum and current number of 2048-byte pages and megabytes in the source table.
Sample Template File
This sample (TMPLORA
), is a template file for converting HP NonStop DDL to Oracle DDL.
The sections are:
-
Table creation section specifying operations for creating and managing tables. Note that in this section
?TABLE
,?COLUMNS
and?KEYCOLUMNS
are resolved byDDLGEN
.?TABLE_SPACE
is prompted for once and will apply to every table, while%NEXT_SIZE
is prompted for on a per-table basis. -
Column name mapping section containing source NonStop and target Oracle column names. This section maps the source column names to the target names. In this example, any occurrences of
ROWID
in the NonStop database will be changed toROWID_
in the Oracle definition. If Oracle keywords appear in your NonStop database definitions, add entries to this list. -
Miscellaneous parameters section. Specify instructions for column formatting.
-
Column type mapping section. Determines how NonStop types are defined in Oracle.
Precision
andScale
definitions can beYES
,NO
or a constant, positive value.
Note in this example, there are two entries for both CHAR
and VARCHAR
. Because Oracle allows a maximum of 255 characters in a VARCHAR2
, we specify that all instances of CHAR
and VARCHAR
with length greater than 255 should become LONGS
.
# Table Creation Section # DROP TABLE ?TABLE; CREATE TABLE ?TABLE ( ?COLUMNS ,CONSTRAINT PK_?TABLE PRIMARY KEY ( ?KEYCOLUMNS ) USING INDEX TABLESPACE ?TABLE_SPACE ) TABLESPACE ?TABLE_SPACE STORAGE (INITIAL 50K NEXT %NEXT_SIZE); # # Column Name Mapping Section # # NonStop column name Oracle target name # ROWID ROWID_ SYSDATE SYSDATE_ # # Miscellaneous Parameters Section # INCLUDENULL # # Column Type Mapping Section # Max Max #NonStop Type Target DB Type Precision Scale Prec. Scale # CHAR VARCHAR2 Y N 255 N CHAR LONG Y N N N VARCHAR VARCHAR2 Y N 255 N VARCHAR LONG N N N N REAL NUMBER N N N N DOUBLE NUMBER N N N N NUMERIC NUMBER Y Y N N SMALLINT NUMBER Y N N N INTEGER NUMBER Y N N N LARGEINT NUMBER Y N N N DECIMAL NUMBER Y Y N N DATE DATE N N N N TIME DATE N N N N TIMESTAMP DATE N N N N DATETIME VARCHAR2 Y N N N INTERVAL VARCHAR2 Y N N N
Sample NonStop SQL Table Definition
An example for creating Oracle table DDL is shown below.
CREATE TABLE TCUSTORD ( CUST_CODE CHAR (4) NOT NULL, ORDER_DATE DATETIME YEAR TO SECOND NOT NULL, PRODUCT_CODE CHAR (8) NOT NULL, ORDER_ID NUMERIC (18) NOT NULL, PRODUCT_PRICE DECIMAL (8,2), PRODUCT_AMOUNT DECIMAL (6,0), TRANSACTION_ID NUMERIC (18) NOT NULL, DESCRIPTION CHAR (400), PRIMARY KEY (CUST_CODE, ORDER_DATE, PRODUCT_CODE, ORDER_ID) );
Modifying the Sample Template File
In this example, you can make three modifications:
- Add a
DATE_MODIFIED
column to each table and to the primary key. - Calculate the
NEXT
value based on the table's current size. Note that any parameter value that evaluates to a numeric value can be multiplied or divided. - Substitute the column name
ORDER_NUM
for instances ofORDER_ID
.
The following is the template file, with modifications shown in bold.
# # Table Creation Section # DROP TABLE ?TABLE; CREATE TABLE ?TABLE ( ?COLUMNS , DATE_MODIFIEDDATE NOT NULL , CONSTRAINT PK_?TABLE PRIMARY KEY ( ?KEYCOLUMNS , DATE_MODIFIED ) USING INDEX TABLESPACE ?TABLE_SPACE ) TABLESPACE ?TABLE_SPACE STORAGE (INITIAL 50K NEXT ?CURPAGES/5K); # # Column Name Mapping Section # # NonStop column name Oracle target name # ROWID ROWID_ SYSDATE SYSDATE_ ORDER_ID ORDER_NUM
Generating the Sample Definition
The following example generates an Oracle definition:
1> RUN DDLGEN Output file for table DDL (or Exit): ORADDL DDL template file name (or Exit): TMPLORA Value for param TABLE_SPACE: USERS Source File/Table (or Exit): $DATA1.SAMPLE.TCUSTORD Source File/Table (or Exit): EXIT
The following is an example of the contents of ORADDL
after DDLGEN
is run.
DROP TABLE TCUSTORD; CREATE TABLE TCUSTORD ( CUST_CODE VARCHAR2(4) NOT NULL , ORDER_DATE DATE NOT NULL , PRODUCT_CODE VARCHAR2(8) NOT NULL , ORDER_NUM NUMBER(18) NOT NULL , PRODUCT_PRICE NUMBER(8,2) NULL , PRODUCT_AMOUNT NUMBER(6,0) NULL , TRANSACTION_ID NUMBER(18) NULL , DATE_MODIFIED DATE NOT NULL , CONSTRAINT PK_TCUSTORD PRIMARY KEY ( CUST_CODE , ORDER_DATE , PRODUCT_CODE , ORDER_NUM , DATE_MODIFIED ) USING INDEX TABLESPACE USERS ) TABLESPACE USERS STORAGE (INITIAL 50K NEXT 470K);
Using Syncfile
Syncfile manages non-database file duplication. For example, you may want to replicate configuration files that are small and change infrequently. This is a common requirement for maintaining a secondary system that has frequent database changes, but infrequent configuration file changes.
Syncfile can copy almost any type of file, making it suitable for other scenarios that require only infrequent, off-hours copying. By default, Syncfile uses the NonStop FUP DUP
utility to perform file duplication; however, it can also run user-written TACL scripts to perform more specialized file duplication, such as FTP over TCP/IP.
You implement Syncfile by defining its parameters. The two main parameters include a file list to duplicate, and one to many schedules. A file set can be a file name, a wildcarded file name, or a file exclude list. The schedules are events which can be as frequent as you want, such as every day, every hour, or every ten minutes.
Syncfile options control the following:
-
The files to duplicate
-
The schedule for determining when files should be duplicated
-
The method for duplication (FUP, FTP, etc.)
-
Whether files should be always duplicated, or only when modified
A Syncfile parameter file can contain multiple schedules and file sets. In addition, you can create multiple Syncfile processes to support duplication for different applications or other requirements.
Syncfile processes are persistent. If a Syncfile process goes down unexpectedly, Manager automatically restarts it.
Implementing Syncfile
See GGSCI Commands for details about the GGSCI Syncfile commands and the Syncfile parameters.