Creating the Master Index Database

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 MySQL, 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 MIDM fields and to create drop-down lists for MIDM 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

• Step 2: Create a Master Index Database and User

• Step 3: Define Master Index Database Indexes

• Step 4: Define Master Index External Systems

• Step 5: Define Master Index Code Lists

• Step 6: Define Master Index User Code Lists

• Step 7: Create Custom Master Index Database Scripts

• Step 8: Create the Master Index Database Structure

• Step 9: Specify a Starting EUID for a Master Index

You can also delete the database for testing purposes using the supplied script. See Dropping Master Index Database Tables for more information.

Step 1: Analyze the Master Index Database Requirements

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.

For additional information and guidelines about how to set up your database, see Master Index Database Scripts and Design.

Step 2: Create a Master Index Database and User

Before beginning this step, complete Step 1: Analyze the Master Index Database Requirements. After you create the database instance and user, continue to Step 3: Define Master Index Database Indexes if you want to define additional database indexes; otherwise skip to Step 4: Define Master Index External Systems.

For this step you need to create a database in which the master index database instance will be created. Use the tools provided by your database vendor 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 MIDM 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.

For MySQL, you can grant the user access to all permissions, or you can assign the individual permissions listed below. For example:

CREATE USER 'username'@'server_name’ IDENTIFIED BY 'password'; 
GRANT ALL ON database TO 'username'@'server_name’;
GRANT SELECT, INSERT ON database TO 'username'@'server_name’;

where username is the login ID for the user, server_name is the name of the database server, password is the login password, and database is the database name.

If you prefer to assign individual permissions to the user instead of roles, the following permissions are needed.

Step 3: Define Master Index Database Indexes

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 create.sql or create a new script. Before you begin this step, complete Step 2: Create a Master Index Database and User.

To Define an Index

1. Do one of the following:
   • Under the master index project in the Projects window, expand Database Scripts and then open create.sql in the NetBeans editor.
   • On your computer, navigate to project_home/src/DatabaseScript, where project_home is the location of the master index project files. Open create.sql in a text editor.
2. Do any of the following:
   • Remove an existing index definition (not recommended).
   • Create new index definitions for the required fields.
   • Modify an existing index definition.
3. Save and close the file.
4. Continue to Step 4: Define Master Index External Systems.

Step 4: Define Master Index External Systems

A SQL script is automatically created to insert the systems you specified in the wizard. These statements are provided in systems.sql in the master index project. Before you begin this step, complete Step 2: Create a Master Index Database and User and, optionally, Step 3: Define Master Index Database Indexes.

To Define an External System

1. Do one of the following:
   • Under the master index project in the Projects window, expand Database Scripts and then open systems.sql in the NetBeans editor.
   • On your computer, navigate to project_home/src/DatabaseScript, where project_home is the location of the master index project files. Open systems.sql in a text editor.
2. For each system, create an INSERT statement using the column descriptions in Master Index Database Table Description for sbyn_systems to define the VALUES clause.

   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’, 8, ’[0-9]{8}’, ’DD-DDD-DDD’, 
   ’DD^DDD^DDD’, sysdate, 'admin’);

3. Delete any default INSERT statements you do not need.
4. Save and close the file.
5. Continue to Step 5: Define Master Index Code Lists.

Master Index Database Table Description for sbyn_systems

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.

Step 5: Define Master Index Code Lists

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 incodelist.sql (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.

To Customize Common Table Data for MySQL

1. Do one of the following:
   • Under the master index project in the Projects window, expand Database Scripts and then open codelist.sql in the NetBeans editor.
   • On your computer, navigate to project_home/src/DatabaseScript, where project_home is the location of the master index project files. Open codelist.sql in a text editor.
2. Scroll to the first line that begins with “— **** “.

   The statements following this line must be customized.

3. In the first code list stanza, change “module description” in the first line to a brief description of the code type.

   For example:

   -- ****         PHONTYPE   ****
   insert into tCodeList values(’L’, ’PHONTYPE’, ’TELEPHONE TYPE’);

4. Create the entries for that module using the following syntax:

   insert into tCodeList 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 Master Index Data Manager windows. For example:

   -- ****         PHONTYPE   ****
   insert into tCodeList values(’L’, ’PHONTYPE’, ’TELEPHONE TYPE’);
   insert into tCodeList values(’V’, ’H’, ’HOME’);
   insert into tCodeList values(’V’, ’C’, ’CELL’);
   insert into tCodeList values(’V’, ’F’, ’FAX’);
   insert into tCodeList values(’V’, ’O’, ’OFFICE’);
   insert into tCodeList values(’V’, ’HB’, ’HOME BUSINESS’);

5. Repeat the previous two steps for each code list type defined in the file.
6. If you specified additional code list fields in object.xml and midm.xml after the database scripts were generated, add a new stanza for each new code type.
7. Save and close the file.
8. Do one of the following:
   • If you need to define user code lists, continue to Step 6: Define Master Index User Code Lists.
   • If you need to create a custom database script, skip to Step 7: Create Custom Master Index Database Scripts.
   • If you are ready to create the master index database structure, skip to Step 8: Create the Master Index Database Structure.

To Customize Common Table Data for Oracle

1. Do one of the following:
   • Under the master index project in the Projects window, expand Database Scripts and then open codelist.sql in the NetBeans editor.
   • On your computer, navigate to project_home/src/DatabaseScript, where project_home is the location of the master index project files. Open codelist.sql in a text editor.
2. Scroll to the following line.

   codes tCodeList := tCodeList(

   The statements following this line must be customized.

3. In the first code list stanza, change “module description” in the first line to a brief description of the code type.

   For example:

   -- ****         PHONTYPE   ****
   tCode(’L’, ’PHONTYPE’, ’TELEPHONE TYPE’),

4. Create the entries for the module using the following syntax:

   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 Master Index 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’),

5. Repeat the previous two steps for each code list type defined in the file.
6. If you specified additional code list fields in object.xml and midm.xml after the database scripts were generated, add a new stanza for each new code type.
7. In the last code module stanza, make sure each line except the last contains a comma at the end.

   For example:

   -- ****         ADDRTYPE   ****
   tCode(’L’, ’ADDRTYPE’, ’ADDRESS TYPE’),
   tCode(’V’, ’H’, ’HOME’),
   tCode(’V’, ’B’, ’BUSINESS’),
   tCode(’V’, ’M’, ’MAILING’)

8. Save and close the file.
9. Do one of the following:
   • If you need to define user code lists, continue to Step 6: Define Master Index User Code Lists.
   • If you need to create a custom database script, skip to Step 7: Create Custom Master Index Database Scripts.
   • If you are ready to create the master index database structure, skip to Step 8: Create the Master Index Database Structure.

To Customize Common Table Data for SQL Server

1. Do one of the following:
   • Under the master index project in the Projects window, expand Database Scripts and then open codelist.sql in the NetBeans editor.
   • On your computer, navigate to project_home/src/DatabaseScript, where project_home is the location of the master index project files. Open codelist.sql in a text editor.
2. Scroll to the following line.

   begin

   The statements following this line must be customized.

3. In the first code list stanza, change “module description” in the first line to a brief description of the code type.

   For example:

   -- ****         PHONTYPE   ****
   insert into @codelist values(’L’, ’PHONTYPE’, ’TELEPHONE TYPE’)

4. Create the entries for the module using the following syntax:

   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 Master Index 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’)

5. Repeat the previous two steps for each code list type defined in the file.
6. If you specified additional code list fields in object.xml and midm.xml, add a new stanza for each new code type.
7. Save and close the file.
8. Do one of the following:
   • To define user code lists, continue to Step 6: Define Master Index User Code Lists.
   • To create a custom database script, skip to Step 7: Create Custom Master Index Database Scripts.
   • To create the master index database structure, skip to Step 8: Create the Master Index Database Structure.

Step 6: Define Master Index User Code Lists

If you specified a value for the Constrained 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. Complete Step 5: Define Master Index Code Lists before beginning this step.

To Define a User Code List

1. Create a new SQL file to contain the user code SQL statements.
2. Use the above sample to define a value for the user code drop-down list and the required format for the dependent fields.
3. Repeat the above step for each drop-down list value and type (for example you might have one list for credit cards and another for postal codes and their corresponding cities).
4. Save and close the file.
5. Continue to Step 7: Create Custom Master Index Database Scripts or to Step 8: Create the Master Index Database Structure if you do not need to create any custom database scripts.

Master Index Database Table Description for sbyn_user_code

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.

Step 7: Create Custom Master Index Database Scripts

You can insert additional information into the database by creating a custom script. For information about the structure of the master index database, see Oracle Java CAPS Master Index Processing Reference.

To Create a Custom Script

1. Create a new text file to contain the SQL statements.
2. In the text editor, create the SQL script to insert the custom data.
3. Save and close the file.
4. Continue to Step 8: Create the Master Index Database Structure.

Step 8: Create the Master Index Database Structure

After you create the database instance and customize the database scripts, you can create the master index tables and insert the custom data.

To Create the Database Structure

1. Open a standard SQL editor or SQL command line and connect to the master index database using the user you created in Step 2: Create a Master Index Database and User. For MySQL, you can run the files from the mysql prompt.
2. Do one of the following:
   • Open create.sql in the NetBeans editor, copy and paste the entire text of the file into the SQL editor, and run the script against the database.
   • From the SQL editor or command line, run create.sql directly. The file is located at project_home/src/DatabaseScript.

     ---

     Tip - To run this script for Oracle or SQL Server, the command is @create.sql. For MySQL, the command is source create.sql.

     ---

3. Repeat the previous two steps for systems.sql, codelist.sql, and any custom scripts you created.

Step 9: Specify a Starting EUID for a Master Index

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 Oracle Java CAPS Master Index Configuration Reference ). 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’;