Communities
A collaborative platform to connect and grow with like-minded Informaticans across the globe
Product Communities
Connect and collaborate with Informatica experts and champions
Discussions
Have a question? Start a Discussion and get immediate answers you are looking for
User Groups
Customer-organized groups that meet online and in-person. Join today to network, share ideas, and get tips on how to get the most out of Informatica
Get Started
Community Guidelines
Knowledge Center
Troubleshooting documents, product guides, how to videos, best practices, and more
Knowledge Base
One-stop self-service portal for solutions, FAQs, Whitepapers, How Tos, Videos, and more
Support TV
Video channel for step-by-step instructions to use our products, best practices, troubleshooting tips, and much more
Documentation
Information library of the latest product documents
Velocity (Best Practices)
Best practices and use cases from the Implementation team
Learn
Rich resources to help you leverage full capabilities of our products
Trainings
Role-based training programs for the best ROI
Certifications
Get certified on Informatica products. Free, Foundation, or Professional
Product Learning Paths
Free and unlimited modules based on your expertise level and journey
Resources
Library of content to help you leverage the best of Informatica products
Tech Tuesdays Webinars
Most popular webinars on product architecture, best practices, and more
Product Availability Matrix
Product Availability Matrix statements of Informatica products
SupportFlash
Monthly support newsletter
Support Documents
Informatica Support Guide and Statements, Quick Start Guides, and Cloud Product Description Schedule
Product Lifecycle
End of Life statements of Informatica products
Ideas
Events
Change Request Tracking
Marketplace
English
  • PowerCenter 10.5.1
  • All Products

Table of Contents

Search

  1. Preface
  2. Product Overview
  3. Before You Begin
  4. Tutorial Lesson 1
  5. Tutorial Lesson 2
  6. Tutorial Lesson 3
  7. Tutorial Lesson 4
  8. Tutorial Lesson 5
  9. Tutorial Lesson 6
  10. Appendix A: Naming Conventions
  11. Appendix B: Glossary

Getting Started

Getting Started

Back Next

Creating Source Tables

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.
  1. Launch the Designer, double-click the icon for the repository, and log in to the repository.
    Use your user profile to open the connection.
  2. Double-click the Tutorial_
    yourname
     folder.
  3. Click
    Tools
    Target Designer
     to open the Target Designer.
  4. Click
    Targets
    Create
    .
    The
    Create Target Table
     dialog box apprears.
    You must create a dummy target definition to access the
    Generate/Execute SQL
     option.
  5. Enter any name for the target and select any database type.
  6. Click
    Create
    .
    An empty definition appears in the workspace.
  7. Click
    Done
    .
  8. Click
    Targets
    Generate/Execute SQL
    .
    The
    Database Object Generation
     dialog box gives you several options for creating tables.
  9. Click the
    Connect
     button to connect to the source database.
  10. 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.
  11. 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.
  12. Make sure the
    Output
     window is open at the bottom of the Designer.
    If it is not open, click
    View
    Output
    .
  13. Click the
    Browse
     button to find the SQL file.
    The SQL file is installed in the following directory: 
    C:\PowerCenterClientInstallationDir\client\bin
  14. 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.
  15. 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.
  16. When the script completes, click
    Disconnect
    , and click
    Close
    .
0 COMMENTS
We’d like to hear from you!