Getting Started

10.4.0
- 10.5.6
- 10.5.1
- 10.5

Back Next

Creating Source Tables

Before you continue with the other lessons, you need to create the source tables in the database. In this section, you run an SQL script in the Target Designer to create sample source tables. The SQL script creates sources with 7-bit ASCII table names and data. The source tables exist in the database if the tutorial is run multiple times.

When you run the SQL script, you create the following source tables:

CUSTOMERS

DEPARTMENT

DISTRIBUTORS

EMPLOYEES

ITEMS

ITEMS_IN_PROMOTIONS

JOBS

MANUFACTURERS

ORDERS

ORDER_ITEMS

PROMOTIONS

STORES

The Target Designer generates SQL based on the definitions in the workspace. Generally, you use the Target Designer to create target tables in the target database. In this lesson, you use this feature to generate the source tutorial tables from the tutorial SQL scripts that ship with the product. When you run the SQL script, you also create a stored procedure that you will use to create a Stored Procedure transformation in another lesson.

Launch the Designer, double-click the icon for the repository, and log in to the repository.

Use your user profile to open the connection.

Double-click the Tutorial_
yourname
folder.

Click

Tools

Target Designer

to open the Target Designer.

Click

Targets

Create

The

Create Target Table

dialog box apprears.

You must create a dummy target definition to access the

Generate/Execute SQL

option.

Enter any name for the target and select any database type.

Click

Create

An empty definition appears in the workspace.

Click

Done

Click

Targets

Generate/Execute SQL

The

Database Object Generation

dialog box gives you several options for creating tables.

Click the

Connect

button to connect to the source database.

Select the ODBC data source that you created in order to connect to the source database.

Use the information you entered in PowerCenter Source and Target.

Enter the database user name and password and click

Connect

You now have an open connection to the source database. When you are connected, the

Disconnect

button appears and the ODBC name of the source database appears in the dialog box.

Make sure the

Output

window is open at the bottom of the Designer.

If it is not open, click

View

Output

Click the

Browse

button to find the SQL file.

The SQL file is installed in the following directory:

C:\PowerCenterClientInstallationDir\client\bin

Select the SQL file appropriate to the source database platform you are using. Click

Open

The following table describes the database platform name and the corresponding SQL file:

Platform	File
Informix	smpl_inf.sql
Microsoft SQL Server	smpl_ms.sql
Oracle	smpl_ora.sql
Sybase ASE	smpl_syb.sql
DB2	smpl_db2.sql
Teradata	smpl_tera.sql

Alternatively, you can enter the path and file name of the SQL file.

Click

Execute SQL File

The database executes the SQL script to create the sample source database objects and to insert values into the source tables. While the script is running, the Output window displays the progress. The Designer generates and executes SQL scripts in Unicode (UCS-2) format.

When the script completes, click

Disconnect

, and click

Tutorial Lesson 1

Download Guide

Watch

Comments

Communities

Knowledge Base

Success Portal