Data Express - DBA tasks for working with the Extension Technology

Created On: 23 December 2010

Problem:

What are the requirements that apply to both Windows and UNIX platforms, when using DBA tasks for working with the Data Express Extension Technology?

Resolution:

1. New Installations

Before using either the ODBC or Oracle Extension, a Run-Time Knowledge Base needs to be created within your source data store(s). The Run-Time Knowledge Base drives the operation and performance of Data Express' masking and subsetting processes. The Run-Time Knowledge Base consists of tables and indexes, and needs to be created using the schema name DEKB.

For your convenience, the attached file Scripts.zip contains scripts which have already been tailored for working with the following data stores:  

-- IBM DB2 LUW

-- Microsoft SQL Server

-- Oracle

   -- Sybase

   -- XDB

   -- XDBC

 
If your data store is not listed, you should use the createkb.sql script, provided within the config directory of your installation, as the basis for the creation of the Run-Time Knowledge Base. This script contains "canonical" DDL (Data Definition Language) statements, describing the Run-Time Knowledge Base tables and indexes.

There are some modifications that will be required before executing these scripts. All the scripts described above require some changes prior to execution:

IBM DB2 LUW: The CONNECT statement must be updated to connect to the appropriate database, and connection criteria.

Microsoft SQL Server: The USE statement must be updated to specify the appropriate database within which to create and populate the Run-Time Knowledge Base.

Oracle: The script assumes that a database user, DEKB, has been created and has appropriate privileges to create and populate the Run-Time Knowledge Base.

createkb.sql: createkb.sql is not guaranteed to execute correctly for all source data stores as-is. Each data store can have slightly different syntax when it comes to CREATE TABLE and CREATE INDEX statements. You should assess what changes are necessary to allow the successful creation of the DEKB schema, and will have the appropriate authority to create and populate that schema.

2. Product Upgrades

If upgrading your installation from Data Express 4.0.4 or earlier, you will need to update all instances of the Run-Time Knowledge Base to update the definition of the HSURDFIL table. In the attached  AlterKB.zip  file you will find scripts tailored for use with the following, and which should be used to update your Run-Time Knowledge Base:

-- IBM DB2 LUW
-- Microsoft SQL Server
-- Oracle

   -- Sybase

   -- XDB

   -- XDBC

If upgrading your installation from Data Express 4.0.8 or earlier, you will need to update all instances of the Run-Time Knowledge Base to add the definition of the HSLOGTAB table. In the attached  AlterKB1.zip  file you will find scripts tailored for use with the following, and which should be used to update your Run-Time Knowledge Base: 

   -- IBM DB2 LUW

-- Microsoft SQL Server

-- Oracle

   -- Sybase

   -- XDB

   -- XDBC

If upgrading your installation from Data Express 4.0.19 or earlier, you will need to update all instances of the Run-Time Knowledge Base to update the definition of the ANURDFLF and HSENVELB table. In the attached AlterKB2.zip  file you will find scripts tailored for use with the following, and which should be used to update your Run-Time Knowledge Base:  

-- IBM DB2 LUW

-- Microsoft SQL Server

-- Oracle

   -- Sybase

   -- XDB

   -- XDBC

 If you are working with any other source data store, you should assess what changes are required to allow the updating of the table definition, and will have the appropriate authority to update the DEKB schema.

All scripts:

a. Must be executed by a database user with appropriate DBA privileges.
b. Will by default grant the necessary privileges to PUBLIC. Depending on your security requirements, you may want to change this, to limit privileges solely to the user ID(s) used to connect to your source data store(s).

It will be necessary to define user credentials for the Data Express Extension Technology. Users of the ODBC or Oracle Extension require appropriate access to source and target data stores, as well as the Run-Time Knowledge Base, which is located in the source data store(s).

The table below lists the pertinent objects and specific privileges required to mask and subset data:

 6283.Scripts.zip

 7713.AlterKB.zip

 0574.AlterKB1.zip

 3414.AlterKB2.zip