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:
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.
- Microsoft SQL Server
- 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.
- In the Integration Development
generate the DDL script:
- In the Project Explorer view, expand the project folder.
- In the Data Models folder, expand the .dbm file.
- Right-click the database name and select Generate DDL.
- 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
in your local environment.
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.
- The 'ID' column is added to your database. The 'ID' is the foreign key that links related tables together.
- If you are configuring a Microsoft SQL
Server 2008, above the
Create Schemaline 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
- If you are using Oracle, ensure that your schema name is the same as your username.
- In your database configuration manager, run the SQL script.
- In IBM® MQ, create a JDBC connection so that the schema that is specified in the map is used. See Enabling JDBC connections to the databases.