Data Analysis database setup

Set up your database so that you can use it with Data Analysis.

Before you begin

  • Create a database in one of the supported databases. The following databases are supported:
    • DB2®
    • Oracle
    • Microsoft SQL Server
    Note: Remember to set a large enough page size to view the column lengths in the target model. If the page size is too small, an error message is produced in the database during table creation.
  • Create a Data Analysis project. See Creating a Data Analysis project.
  • Analyze your sample data. See Analyzing sample XML documents.
  • Create your target model. See Creating a target model.
  • Generate your Data Analysis tools. See Creating Data Analysis tools.

Procedure

  1. In the Integration Development perspective, generate the DDL script:
    1. In the Project Explorer view, expand the project folder.
    2. In the Data Models folder, expand the .dbm file.
    3. Right-click the database name and select Generate DDL.
    4. To configure the DDL script, follow the steps in the Generate DDL wizard and click Finish.
    • Your SQL script is generated. This script shows the commands that are used to create your database tables.
    • The 'DOCID' column is added to your database. At runtime this column is populated with a unique string, to provide a map with a unique input key for database operations. Alternatively, you can set the input key yourself, by setting 
      LocalEnvironment.Database.Input.Key
      in your local environment.
    • The 'ID' column is added to your database. The 'ID' is the foreign key that links related tables together.
    Tip: If you do not have a .dbm file, open the Generate Data Analysis Tools wizard and check the Insert transformed messages into database box.
  2. If you are configuring a Microsoft SQL Server 2008, above the Create Schema line in your SQL script insert DROP SCHEMA "SCHEMA_NAME" GO. For example: 
    DROP SCHEMA "MYSCHEMA"
	GO

	CREATE SCHEMA "MYSCHEMA"
	GO

	CREATE TABLE "MYSCHEMA"."MYTABLE" (
		"COLUMN1" VARCHAR(1000) NOT NULL,
		"COLUMN2" INT NULL
	)
	GO
  3. If you are using Oracle, ensure that your schema name is the same as your username.
  4. In your database configuration manager, run the SQL script.
  5. In WebSphere® MQ, create a JDBC connection so that the schema that is specified in the map is used. See Enabling JDBC connections to the databases.