Skip Navigation Links | |
Exit Print View | |
Developing Oracle Java CAPS Master Indexes (Repository) Java CAPS Documentation |
Developing Oracle Java CAPS Master Indexes (Repository)
Oracle Java CAPS Master Index Overview
About Oracle Java CAPS Master Index
Oracle Java CAPS Master Index Features
Master Index Repository Components
Match Engine Configuration Files
Outbound Object Type Definition (OTD)
Learning about the Master Index Runtime Environment
Functions of the Runtime Environment
Features of the Runtime Environment
Master Index Runtime Components
Object Persistence Service (OPS)
Objects in an Enterprise Record
Working with Project Components
Copying, Cutting, and Pasting Files
Master Index Development Process Overview (Repository)
The Master Index Framework and the Runtime Environment (Repository)
Before You Begin Developing a Master Index (Repository)
Preliminary Data Analysis for a Master Index (Repository)
Planning a Master Index Project (Repository)
Master Index Project Initiation Checklist (Repository)
Creating a Master Index Application (Repository)
Step 1: Create a Project and Start the Wizard (Repository)
Step 2: Name the Master Index Application (Repository)
To Name the Master Index Application
Step 3: Define Source Systems (Repository)
Step 4: Define the Deployment Environment (Repository)
To Define the Deployment Environment
Step 5: Define Parent and Child Objects (Repository)
Creating Objects from a Template
Deleting an Object from the Structure
Step 6: Define the Fields for Each Object (Repository)
Step 7: Generate the Project Files (Repository)
To Generate the Configuration Files
Step 8: Review the Configuration Files (Repository)
Master Index Wizard Field Properties and Name Restrictions (Repository)
Master Index Wizard Field Name Restrictions (Repository)
Master Index Wizard General Field Properties (Repository)
Master Index Wizard EDM Field Properties (Repository)
Custom Plug-ins for Master Index Custom Transaction Processing (Repository)
Master Index Update Policy Plug-ins (Repository)
Master Index Field Validation Plug-ins (Repository)
Master Index Field Masking Plug-ins (Repository)
Master Index Match Processing Logic Plug-ins (Repository)
Custom Match Processing Logic Methods
Custom Match Processing Logic Plug-in Requirements
Custom Match Processing Configuration (Repository)
Master Index Custom Plug-in Exception Processing (Repository)
Custom Plug-Ins for Master Index Custom Components (Repository)
Master Index Survivor Calculator Plug-ins (Repository)
Master Index Query Builder Plug-ins (Repository)
Master Index Block Picker Plug-ins (Repository)
Master Index Pass Controller Plug-ins (Repository)
Match Engine Plug-ins (Repository)
Standardization Engine Plug-ins (Repository)
Phonetic Encoders Plug-ins for a Master Index (Repository)
Implementing Master Index Custom Plug-ins (Repository)
Creating Master Index Custom Plug-ins (Repository)
Building Master Index Custom Plug-ins (Repository)
Generating the Master Index Application (Repository)
To Generate the Application for the First Time
Master Index Database Scripts and Design (Repository)
Master Index Database Scripts (Repository)
Master Index Database Requirements (Repository)
Database Platform Requirements
Master Index Database Structure (Repository)
Designing the Master Index Database (Repository)
Designing for Performance Optimization
Creating the Master Index Database (Repository)
Step 1: Analyze the Master Index Database Requirements (Repository)
Step 2: Create a Master Index Database and User (Repository)
Step 3: Define Master Index Database Indexes (Repository)
Step 4: Define Master Index External Systems (Repository)
Master Index Database Table Description for sbyn_systems (Repository)
Step 5: Define Master Index Code Lists (Repository)
To Customize Common Table Data for Oracle
To Customize Common Table Data for SQL Server
Step 6: Define Master Index User Code Lists (Repository)
Master Index Database Table Description for sbyn_user_code (Repository)
Step 7: Create Custom Master Index Database Scripts (Repository)
Step 8: Create the Master Index Database Structure (Repository)
To Create the Database Structure
Step 9: Specify a Starting EUID for a Master Index (Repository)
Deleting Master Index Database Tables (Repository)
To Delete Database Tables (Repository)
Defining a Database Connection Pool Through the Application Server
Step 1: Add the Oracle Driver to the Application Server
Step 2: Create the JDBC Connection Pools
To Create the JDBC Connection Pools
Once you have customized the configuration files and generated the master index application, you can create the master index database. Before you begin, make sure you have Oracle or SQL Server installed on the database server.
During this process you can define custom startup data, such as code lists and source systems. The wizard defines SQL statements in the Code List and Systems SQL files (codelist.sql and system.sql) to insert startup data into the database based on information you specify, and you can customize the statements to insert the data relevant to your object structure. The code lists you define are used to translate processing codes from incoming messages into descriptions for the EDM fields and to create drop-down lists for EDM fields. System information is required in order to add records to the master index application.
Follow these steps to create the database.
Step 1: Analyze the Master Index Database Requirements (Repository)
Step 2: Create a Master Index Database and User (Repository)
Step 7: Create Custom Master Index Database Scripts (Repository)
Step 8: Create the Master Index Database Structure (Repository)
Step 9: Specify a Starting EUID for a Master Index (Repository)
You can also delete the database for testing purposes using the supplied script. See Deleting Master Index Database Tables (Repository) for more information.
Before you begin to create the master index database, perform an analysis of the structure of the legacy data to be stored in the database and determine the amount of data that will be processed daily. During the analysis, be sure to define the processing codes that need to be stored in the common maintenance tables and the systems that will share data with the master index application. You should also know the length and format of the local IDs assigned by each system.
A database administrator who is familiar with your data and processing requirements should perform this task. After this task is complete, continue to Step 2: Create a Master Index Database and User (Repository).
For additional information and guidelines about how to set up your database, see Master Index Database Scripts and Design (Repository).
Before beginning this step, complete Step 1: Analyze the Master Index Database Requirements (Repository). After you create the database instance and user, continue to Step 3: Define Master Index Database Indexes (Repository) if you want to define additional database indexes; otherwise skip to Step 4: Define Master Index External Systems (Repository).
For this step you need to create a database in which the master index database instance will be created. Use your Oracle or SQL Server tools to create the database. Using these tools, you define tablespaces, including their sizes and locations; extents; and dump file, log file, and rollback file sizes and locations. Make sure these issues have been thoroughly analyzed and designed before creating the database.
Once you create the database, you can use standard SQL to create the master index application user for the database. The user you create in this step will be used to create the database structure and to connect to the database through the EDM and through the application server.
For Oracle, assign the user to the “connect” and “resource” roles for the master index tablespaces. For example:
create user username identified by password; grant connect, resource to username; commit;
where username is the login ID of the administrator user and password is the login password of the administrator user.
For SQL Server, assign this user to the “db_owner” role. You need to create the server login, create the user, and then assign the user to the role. For example:
CREATE LOGIN loginname WITH PASSWORD = ’password’, DEFAULT_DATABASE = database; CREATE USER username FOR LOGIN loginname; USE database; EXECUTE sp_addrolemember ’db_owner’, ’ username’ GO
where loginname is the login ID for the administrator user, password is the login password, database is the database name, and username is the owner of the database tables created in the master index database.
Note - SQL Server allows Windows Authentication, where only a user name is required. Java CAPS products require full authentication, including both a user name and password. You need to create a database user specifically for the master index application.
If you prefer to assign individual permissions to the user instead of roles, the following permissions are needed.
|
To optimize data processing in the master index application, you can define additional indexes for the database tables that store object data. Best practice is to define indexes for each field used for searching, blocking, or matching. You can define these indexes in the Create Application_name Database file file or create a new script. Before you begin this step, complete Step 2: Create a Master Index Database and User (Repository).
A SQL script is automatically created to insert the systems you specified in the wizard. These statements are provided in the Systems file in the master index project. Before you begin this step, complete Step 2: Create a Master Index Database and User (Repository) and, optionally, Step 3: Define Master Index Database Indexes (Repository).
For example:
INSERT into sbyn_systems (systemcode, description, status, id_length, format, input_mask, value_mask, create_date, create_userid) VALUES (’ARS’, ’Automated Registration System’, ’A’, 10, ’[0-9]{10}’, ’DDD-DDD-DDDD’, ’DDD^DDD^DDDD’, sysdate, ”admin’);
The following table lists and describes the columns in the sbyn_systems database table so you can create SQL statements to insert source system information into the master index database.
You only need to perform this step if you defined any fields in the object structure to have a code module. The SQL script for entering processing codes and descriptions into the database is written in PL/SQL. The wizard creates a stanza in the Code List file (located under the Database Script node of the project) for each code list you specified in the field properties. You need to customize the file by defining the entries for each code list. This script inserts data into two tables: sbyn_common_header, which lists the types of common table data, and sbyn_common_detail, which lists each common table data element. Before you begin this step, complete Step 4: Define Master Index External Systems (Repository).
Note - The codes you specify in this file can be no longer than eight characters (the codes are the second value in the value list for each common table data type and data element).
codes tCodeList := tCodeList(
The statements following this line must be customized.
For example:
-- **** PHONTYPE **** tCode(’L’, ’PHONTYPE’, ’TELEPHONE TYPE’),
tCode(’V’, ’code’, ’code description’),
where “code” is the processing code of the data element and “code description” is the description of the element as you want it to appear on the Enterprise Data Manager windows. For example:
-- **** PHONTYPE **** tCode(’L’, ’PHONTYPE’, ’TELEPHONE TYPE’), tCode(’V’, ’H’, ’HOME’), tCode(’V’, ’C’, ’CELL’), tCode(’V’, ’F’, ’FAX’), tCode(’V’, ’O’, ’OFFICE’), tCode(’V’, ’HB’, ’HOME BUSINESS’),
For example:
-- **** ADDRTYPE **** tCode(’L’, ’ADDRTYPE’, ’ADDRESS TYPE’), tCode(’V’, ’H’, ’HOME’), tCode(’V’, ’B’, ’BUSINESS’), tCode(’V’, ’M’, ’MAILING’)
begin
The statements following this line must be customized.
For example:
-- **** PHONTYPE **** insert into @codelist values(’L’, ’PHONTYPE’, ’TELEPHONE TYPE’)
insert into @codelist values(’V’, ’code’, ’code description’)
where “code” is the processing code of the data element and “code description” is the description of the element as you want it to appear on the Enterprise Data Manager windows. For example:
-- **** PHONTYPE **** insert into @codelist values(’L’, ’PHONTYPE’, ’TELEPHONE TYPE’) insert into @codelist values(’V’, ’H’, ’HOME’) insert into @codelist values(’V’, ’C’, ’CELL’) insert into @codelist values(’V’, ’F’, ’FAX’) insert into @codelist values(’V’, ’O’, ’OFFICE’) insert into @codelist values(’V’, ’HB’, ’HOME BUSINESS’)
If you specified a value for theConstrained By and User Code properties of a field, you must define the user code values for those fields. Below is a sample insert statement for the sbyn_user_code table.
insert into sbyn_user_code (code_list, code, descr, format, input_mask, value_mask) values (’AUXIDDEF’, ’CC’, ’CREDIT CARD’, ’[0-9]{16}’, ’DDDD-DDDD-DDDD-DDDD’, ’DDDD^DDDD^DDDD^DDDD’);
To learn more about the sbyn_user_code table, see Master Index Database Table Description for sbyn_user_code (Repository). Complete Step 5: Define Master Index Code Lists (Repository) before beginning this step.
The file opens in the NetBeans editor.
The following table lists and describes the columns in the sbyn_user_code database table so you can create SQL statements to insert user code data into the master index database.
|
You can insert additional information into the database by creating a custom script under the Database Script node. For information about the structure of the master index database, see Understanding Oracle Java CAPS Master Index Processing (Repository).
The new script appears under the Database Script node.
The text editor appears.
After you create the database instance and customize the database scripts, you can create the master index tables and insert the custom data.
For example:
jdbc:oracle:thin:@localhost:1521:IndexDB
jdbc:sqlserver://server:port;databaseName=database
where server is the address of the database server, port is the port number on which SQL Server is listening, and database is the name of the database. You can use “localhost” for the hostname if the database resides on the same machine as NetBeans.
Note - Make sure you enter the database logon credentials for the administrator user you created. You cannot use the logon credentials for the default system user (the database tables will be created, but the master index application will not function correctly).
By default, the EUIDs assigned by the master index application start with “0”, with padded zeroes added to the left to make the EUID number the correct length (for more information, see Understanding Oracle Java CAPS Master Index Configuration Options (Repository)). You can modify this numbering format by changing the value of the seq_name column of the sbyn_seq_table database table where the sequence name is “EUID”. For example:
update sbyn_seq_table set seq_count=1000000001 where seq_name=’EUID’;