Troubleshooting Guide

Troubleshooting Guide

Informatica PowerCenter® (Version 7.1.1)

Informatica PowerCenter Troubleshooting Guide Version 7.1.1 August 2004 Copyright (c) 1998–2004 Informatica Corporation. All rights reserved. Printed in the USA. This software and documentation contain proprietary information of Informatica Corporation, they are provided under a license agreement containing restrictions on use and disclosure and is also protected by copyright law. Reverse engineering of the software is prohibited. No part of this document may be reproduced or transmitted in any form, by any means (electronic, photocopying, recording or otherwise) without prior consent of Informatica Corporation. Use, duplication, or disclosure of the Software by the U.S. Government is subject to the restrictions set forth in the applicable software license agreement as provided in DFARS 227.7202-1(a) and 227.7702-3(a) (1995), DFARS 252.227-7013(c)(1)(ii) (OCT 1988), FAR 12.212(a) (1995), FAR 52.227-19, or FAR 52.227-14 (ALT III), as applicable. The information in this document is subject to change without notice. If you find any problems in the documentation, please report them to us in writing. Informatica Corporation does not warrant that this documentation is error free. Informatica, PowerMart, PowerCenter, PowerChannel, PowerCenter Connect, MX, and SuperGlue are trademarks or registered trademarks of Informatica Corporation in the United States and in jurisdictions throughout the world. All other company and product names may be trade names or trademarks of their respective owners. Portions of this software are copyrighted by DataDirect Technologies, 1999-2002. Informatica PowerCenter products contain ACE (TM) software copyrighted by Douglas C. Schmidt and his research group at Washington University and University of California, Irvine, Copyright (c) 1993-2002, all rights reserved. Portions of this software contain copyrighted material from The JBoss Group, LLC. Your right to use such materials is set forth in the GNU Lesser General Public License Agreement, which may be found at http://www.opensource.org/licenses/lgpl-license.php. The JBoss materials are provided free of charge by Informatica, “as-is”, without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Portions of this software contain copyrighted material from Meta Integration Technology, Inc. Meta Integration® is a registered trademark of Meta Integration Technology, Inc. This product includes software developed by the Apache Software Foundation (http://www.apache.org/). The Apache Software is Copyright (c) 1999-2004 The Apache Software Foundation. All rights reserved. DISCLAIMER: Informatica Corporation provides this documentation “as is” without warranty of any kind, either express or implied, including, but not limited to, the implied warranties of non-infringement, merchantability, or use for a particular purpose. The information provided in this documentation may include technical inaccuracies or typographical errors. Informatica could make improvements and/or changes in the products described in this documentation at any time without notice.

Table of Contents Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . vii New Features and Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . viii PowerCenter 7.1.1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . viii PowerCenter 7.1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x PowerCenter 7.0 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiv About Informatica Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xx About this Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxi Document Conventions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxi Other Informatica Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxii Visiting Informatica Customer Portal . . . . . . . . . . . . . . . . . . . . . . . . . xxii Visiting the Informatica Webzine . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxii Visiting the Informatica Web Site . . . . . . . . . . . . . . . . . . . . . . . . . . . xxii Visiting the Informatica Developer Network . . . . . . . . . . . . . . . . . . . . xxii Obtaining Technical Support . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxiii

Chapter 1: Client Error Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Business Components Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Mapping Variable Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Metadata Extension Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Repository Server Administration Console Messages . . . . . . . . . . . . . . . . . . . 6 Repository Upgrade Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Session Log Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Validation Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 Workflow Log Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Workflow Manager Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 XML Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18

Chapter 2: pmcmd Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 pmcmd Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

Chapter 3: pmrep Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 pmrep Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26

iii

Chapter 4: Exporting and Importing Objects Messages . . . . . . . . . .29 Exporting and Importing Objects Messages . . . . . . . . . . . . . . . . . . . . . . . . . 30

Chapter 5: SDK Client Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55 SDK Client Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56

Chapter 6: Log File Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 Message Codes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 Log Files and Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Session Log Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Workflow Log Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 PowerCenter Server Log Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 Repository Server Log Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 Repository Agent Log Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65

Chapter 7: BLKR Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 BLKR Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68

Chapter 8: CMN Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 CMN Messages

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72

Chapter 9: DBG Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95 DBG Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96

Chapter 10: DBGR Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 DBGR Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100

Chapter 11: EP Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105 EP Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106

Chapter 12: FR Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117 FR Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118

iv

Table of Contents

Chapter 13: FTP Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127 FTP Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128

Chapter 14: HIER Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131 HIER Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132

Chapter 15: LM Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135 LM Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136

Chapter 16: NTSERV Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145 NTSERV Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146

Chapter 17: OBJM Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153 OBJM Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154

Chapter 18: ODL Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157 ODL Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158

Chapter 19: PMF Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163 PMF Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 164

Chapter 20: REP Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167 REP Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168

Chapter 21: RR Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191 RR Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192

Chapter 22: RS Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195 RS Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196

Chapter 23: SDKC Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199 SDKC Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200

Table of Contents

v

Chapter 24: SDKS Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201 SDKS Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202

Chapter 25: SF Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 203 SF Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204

Chapter 26: SORT Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207 SORT Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 208

Chapter 27: TE Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211 TE Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212

Chapter 28: TM Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217 TM Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218

Chapter 29: TT Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233 TT Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234

Chapter 30: VAR Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245 VAR Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246

Chapter 31: WRT Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255 WRT Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 256

Chapter 32: WSP Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275 WSP Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 276

Chapter 33: XMLW Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 283 XMLW Messages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 284

Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 291

vi

Table of Contents

Preface Welcome to PowerCenter, Informatica’s software product that delivers an open, scalable data integration solution addressing the complete life cycle for all data integration projects including data warehouses and data marts, data migration, data synchronization, and information hubs. PowerCenter combines the latest technology enhancements for reliably managing data repositories and delivering information resources in a timely, usable, and efficient manner. The PowerCenter metadata repository coordinates and drives a variety of core functions, including extracting, transforming, loading, and managing data. The PowerCenter Server can extract large volumes of data from multiple platforms, handle complex transformations on the data, and support high-speed loads. PowerCenter can simplify and accelerate the process of moving data warehouses from development to test to production.

vii

New Features and Enhancements This section describes new features and enhancements to PowerCenter 7.1.1, 7.1, and 7.0.

PowerCenter 7.1.1 This section describes new features and enhancements to PowerCenter 7.1.1.

Data Profiling ♦

Data sampling. You can create a data profile for a sample of source data instead of the entire source. You can view a profile from a random sample of data, a specified percentage of data, or for a specified number of rows starting with the first row.

♦

Verbose data enhancements. You can specify the type of verbose data you want the PowerCenter Server to write to the Data Profiling warehouse. The PowerCenter Server can write all rows, the rows that meet the business rule, or the rows that do not meet the business rule.

♦

Session enhancement. You can save sessions that you create from the Profile Manager to the repository.

♦

Domain Inference function tuning. You can configure the Data Profiling Wizard to filter the Domain Inference function results. You can configure a maximum number of patterns and a minimum pattern frequency. You may want to narrow the scope of patterns returned to view only the primary domains, or you may want to widen the scope of patterns returned to view exception data.

♦

Row Uniqueness function. You can determine unique rows for a source based on a selection of columns for the specified source.

♦

Define mapping, session, and workflow prefixes. You can define default mapping, session, and workflow prefixes for the mappings, sessions, and workflows generated when you create a data profile.

♦

Profile mapping display in the Designer. The Designer displays profile mappings under a profile mappings node in the Navigator.

PowerCenter Server

viii

Preface

♦

Code page. PowerCenter supports additional Japanese language code pages, such as JIPSEkana, JEF-kana, and MELCOM-kana.

♦

Flat file partitioning. When you create multiple partitions for a flat file source session, you can configure the session to create multiple threads to read the flat file source.

♦

pmcmd. You can use parameter files that reside on a local machine with the Startworkflow command in the pmcmd program. When you use a local parameter file, pmcmd passes variables and values in the file to the PowerCenter Server.

♦

SuSE Linux support. The PowerCenter Server runs on SuSE Linux. On SuSE Linux, you can connect to IBM, DB2, Oracle, and Sybase sources, targets, and repositories using native drivers. Use ODBC drivers to access other sources and targets.

♦

Reserved word support. If any source, target, or lookup table name or column name contains a database reserved word, you can create and maintain a file, reswords.txt, containing reserved words. When the PowerCenter Server initializes a session, it searches for reswords.txt in the PowerCenter Server installation directory. If the file exists, the PowerCenter Server places quotes around matching reserved words when it executes SQL against the database.

♦

Teradata external loader. When you load to Teradata using an external loader, you can now override the control file. Depending on the loader you use, you can also override the error, log, and work table names by specifying different tables on the same or different Teradata database.

Repository ♦

Exchange metadata with other tools. You can exchange source and target metadata with other BI or data modeling tools, such as Business Objects Designer. You can export or import multiple objects at a time. When you export metadata, the PowerCenter Client creates a file format recognized by the target tool.

Repository Server ♦

♦

pmrep. You can use pmrep to perform the following functions: −

Remove repositories from the Repository Server cache entry list.

−

Enable enhanced security when you create a relational source or target connection in the repository.

−

Update a connection attribute value when you update the connection.

SuSE Linux support. The Repository Server runs on SuSE Linux. On SuSE Linux, you can connect to IBM, DB2, Oracle, and Sybase repositories.

Security ♦

Oracle OS Authentication. You can now use Oracle OS Authentication to authenticate database users. Oracle OS Authentication allows you to log on to an Oracle database if you have a logon to the operating system. You do not need to know a database user name and password. PowerCenter uses Oracle OS Authentication when the user name for an Oracle connection is PmNullUser.

Web Services Provider ♦

Attachment support. When you import web service definitions with attachment groups, you can pass attachments through the requests or responses in a service session. The document type you can attach is based on the mime content of the WSDL file. You can attach document types such as XML, JPEG, GIF, or PDF.

Preface

ix

♦

Pipeline partitioning. You can create multiple partitions in a session containing web service source and target definitions. The PowerCenter Server creates a connection to the Web Services Hub based on the number of sources, targets, and partitions in the session.

XML ♦

Multi-level pivoting. You can now pivot more than one multiple-occurring element in an XML view. You can also pivot the view row.

PowerCenter 7.1 This section describes new features and enhancements to PowerCenter 7.1.

Data Profiling ♦

Data Profiling for VSAM sources. You can now create a data profile for VSAM sources.

♦

Support for verbose mode for source-level functions. You can now create data profiles with source-level functions and write data to the Data Profiling warehouse in verbose mode.

♦

Aggregator function in auto profiles. Auto profiles now include the Aggregator function.

♦

Creating auto profile enhancements. You can now select the columns or groups you want to include in an auto profile and enable verbose mode for the Distinct Value Count function.

♦

Purging data from the Data Profiling warehouse. You can now purge data from the Data Profiling warehouse.

♦

Source View in the Profile Manager. You can now view data profiles by source definition in the Profile Manager.

♦

PowerCenter Data Profiling report enhancements. You can now view PowerCenter Data Profiling reports in a separate browser window, resize columns in a report, and view verbose data for Distinct Value Count functions.

♦

Prepackaged domains. Informatica provides a set of prepackaged domains that you can include in a Domain Validation function in a data profile.

Documentation ♦

Web Services Provider Guide. This is a new book that describes the functionality of Real-time Web Services. It also includes information from the version 7.0 Web Services Hub Guide.

♦

XML User Guide. This book consolidates XML information previously documented in the Designer Guide, Workflow Administration Guide, and Transformation Guide.

Licensing Informatica provides licenses for each CPU and each repository rather than for each installation. Informatica provides licenses for product, connectivity, and options. You store x

Preface

the license keys in a license key file. You can manage the license files using the Repository Server Administration Console, the PowerCenter Server Setup, and the command line program, pmlic.

PowerCenter Server ♦

64-bit support. You can now run 64-bit PowerCenter Servers on AIX and HP-UX (Itanium).

♦

Partitioning enhancements. If you have the Partitioning option, you can define up to 64 partitions at any partition point in a pipeline that supports multiple partitions.

♦

PowerCenter Server processing enhancements. The PowerCenter Server now reads a block of rows at a time. This improves processing performance for most sessions.

♦

CLOB/BLOB datatype support. You can now read and write CLOB/BLOB datatypes.

PowerCenter Metadata Reporter PowerCenter Metadata Reporter modified some report names and uses the PowerCenter 7.1 MX views in its schema.

Repository Server ♦

Updating repository statistics. PowerCenter now identifies and updates statistics for all repository tables and indexes when you copy, upgrade, and restore repositories. This improves performance when PowerCenter accesses the repository.

♦

Increased repository performance. You can increase repository performance by skipping information when you copy, back up, or restore a repository. You can choose to skip MX data, workflow and session log history, and deploy group history.

♦

pmrep. You can use pmrep to back up, disable, or enable a repository, delete a relational connection from a repository, delete repository details, truncate log files, and run multiple pmrep commands sequentially. You can also use pmrep to create, modify, and delete a folder.

Repository ♦

Exchange metadata with business intelligence tools. You can export metadata to and import metadata from other business intelligence tools, such as Cognos Report Net and Business Objects.

♦

Object import and export enhancements. You can compare objects in an XML file to objects in the target repository when you import objects.

♦

MX views. MX views have been added to help you analyze metadata stored in the repository. REP_SERVER_NET and REP_SERVER_NET_REF views allow you to see information about server grids. REP_VERSION_PROPS allows you to see the version history of all objects in a PowerCenter repository.

Preface

xi

Transformations ♦

Flat file lookup. You can now perform lookups on flat files. When you create a Lookup transformation using a flat file as a lookup source, the Designer invokes the Flat File Wizard. You can also use a lookup file parameter if you want to change the name or location of a lookup between session runs.

♦

Dynamic lookup cache enhancements. When you use a dynamic lookup cache, the PowerCenter Server can ignore some ports when it compares values in lookup and input ports before it updates a row in the cache. Also, you can choose whether the PowerCenter Server outputs old or new values from the lookup/output ports when it updates a row. You might want to output old values from lookup/output ports when you use the Lookup transformation in a mapping that updates slowly changing dimension tables.

♦

Union transformation. You can use the Union transformation to merge multiple sources into a single pipeline. The Union transformation is similar to using the UNION ALL SQL statement to combine the results from two or more SQL statements.

♦

Custom transformation API enhancements. The Custom transformation API includes new array-based functions that allow you to create procedure code that receives and outputs a block of rows at a time. Use these functions to take advantage of the PowerCenter Server processing enhancements.

♦

Midstream XML transformations. You can now create an XML Parser transformation or an XML Generator transformation to parse or generate XML inside a pipeline. The XML transformations enable you to extract XML data stored in relational tables, such as data stored in a CLOB column. You can also extract data from messaging systems, such as TIBCO or IBM MQSeries.

Usability ♦

Viewing active folders. The Designer and the Workflow Manager highlight the active folder in the Navigator.

♦

Enhanced printing. The quality of printed workspace has improved.

Version Control You can run object queries that return shortcut objects. You can also run object queries based on the latest status of an object. The query can return local objects that are checked out, the latest version of checked in objects, or a collection of all older versions of objects.

Web Services Provider

xii

Preface

♦

Real-time Web Services. Real-time Web Services allows you to create services using the Workflow Manager and make them available to web service clients through the Web Services Hub. The PowerCenter Server can perform parallel processing of both requestresponse and one-way services.

♦

Web Services Hub. The Web Services Hub now hosts Real-time Web Services in addition to Metadata Web Services and Batch Web Services. You can install the Web Services Hub on a JBoss application server.

Note: PowerCenter Connect for Web Services allows you to create sources, targets, and

transformations to call web services hosted by other providers. For more informations, see PowerCenter Connect for Web Services User and Administrator Guide.

Workflow Monitor The Workflow Monitor includes the following performance and usability enhancements: ♦

When you connect to the PowerCenter Server, you no longer distinguish between online or offline mode.

♦

You can open multiple instances of the Workflow Monitor on one machine.

♦

You can simultaneously monitor multiple PowerCenter Servers registered to the same repository.

♦

The Workflow Monitor includes improved options for filtering tasks by start and end time.

♦

The Workflow Monitor displays workflow runs in Task view chronologically with the most recent run at the top. It displays folders alphabetically.

♦

You can remove the Navigator and Output window.

XML Support PowerCenter XML support now includes the following features: ♦

Enhanced datatype support. You can use XML schemas that contain simple and complex datatypes.

♦

Additional options for XML definitions. When you import XML definitions, you can choose how you want the Designer to represent the metadata associated with the imported files. You can choose to generate XML views using hierarchy or entity relationships. In a view with hierarchy relationships, the Designer expands each element and reference under its parent element. When you create views with entity relationships, the Designer creates separate entities for references and multiple-occurring elements.

♦

Synchronizing XML definitions. You can synchronize one or more XML definition when the underlying schema changes. You can synchronize an XML definition with any repository definition or file used to create the XML definition, including relational sources or targets, XML files, DTD files, or schema files.

♦

XML workspace. You can edit XML views and relationships between views in the workspace. You can create views, add or delete columns from views, and define relationships between views.

♦

Midstream XML transformations. You can now create an XML Parser transformation or an XML Generator transformation to parse or generate XML inside a pipeline. The XML transformations enable you to extract XML data stored in relational tables, such as data stored in a CLOB column. You can also extract data from messaging systems, such as TIBCO or IBM MQSeries.

Preface

xiii

♦

Support for circular references. Circular references occur when an element is a direct or indirect child of itself. PowerCenter now supports XML files, DTD files, and XML schemas that use circular definitions.

♦

Increased performance for large XML targets. You can create XML files of several gigabytes in a PowerCenter 7.1 XML session by using the following enhancements: −

Spill to disk. You can specify the size of the cache used to store the XML tree. If the size of the tree exceeds the cache size, the XML data spills to disk in order to free up memory.

−

User-defined commits. You can define commits to trigger flushes for XML target files.

−

Support for multiple XML output files. You can output XML data to multiple XML targets. You can also define the file names for XML output files in the mapping.

PowerCenter 7.0 This section describes new features and enhancements to PowerCenter 7.0.

Data Profiling If you have the Data Profiling option, you can profile source data to evaluate source data and detect patterns and exceptions. For example, you can determine implicit data type, suggest candidate keys, detect data patterns, and evaluate join criteria. After you create a profiling warehouse, you can create profiling mappings and run sessions. Then you can view reports based on the profile data in the profiling warehouse. The PowerCenter Client provides a Profile Manager and a Profile Wizard to complete these tasks.

Data Integration Web Services You can use Data Integration Web Services to write applications to communicate with the PowerCenter Server. Data Integration Web Services is a web-enabled version of the PowerCenter Server functionality available through Load Manager and Metadata Exchange. It is comprised of two services for communication with the PowerCenter Server, Load Manager and Metadata Exchange Web Services running on the Web Services Hub.

Documentation

xiv

Preface

♦

Glossary. The Installation and Configuration Guide contains a glossary of new PowerCenter terms.

♦

Installation and Configuration Guide. The connectivity information in the Installation and Configuration Guide is consolidated into two chapters. This book now contains chapters titled “Connecting to Databases from Windows” and “Connecting to Databases from UNIX.”

♦

Upgrading metadata. The Installation and Configuration Guide now contains a chapter titled “Upgrading Repository Metadata.” This chapter describes changes to repository

objects impacted by the upgrade process. The change in functionality for existing objects depends on the version of the existing objects. Consult the upgrade information in this chapter for each upgraded object to determine whether the upgrade applies to your current version of PowerCenter.

Functions ♦

Soundex. The Soundex function encodes a string value into a four-character string. SOUNDEX works for characters in the English alphabet (A-Z). It uses the first character of the input string as the first character in the return value and encodes the remaining three unique consonants as numbers.

♦

Metaphone. The Metaphone function encodes string values. You can specify the length of the string that you want to encode. METAPHONE encodes characters of the English language alphabet (A-Z). It encodes both uppercase and lowercase letters in uppercase.

Installation ♦

Remote PowerCenter Client installation. You can create a control file containing installation information, and distribute it to other users to install the PowerCenter Client. You access the Informatica installation CD from the command line to create the control file and install the product.

PowerCenter Metadata Reporter PowerCenter Metadata Reporter replaces Runtime Metadata Reporter and Informatica Metadata Reporter. PowerCenter Metadata Reporter includes the following features: ♦

Metadata browsing. You can use PowerCenter Metadata Reporter to browse PowerCenter 7.0 metadata, such as workflows, worklets, mappings, source and target tables, and transformations.

♦

Metadata analysis. You can use PowerCenter Metadata Reporter to analyze operational metadata, including session load time, server load, session completion status, session errors, and warehouse growth.

PowerCenter Server ♦

DB2 bulk loading. You can enable bulk loading when you load to IBM DB2 8.1.

♦

Distributed processing. If you purchase the Server Grid option, you can group PowerCenter Servers registered to the same repository into a server grid. In a server grid, PowerCenter Servers balance the workload among all the servers in the grid.

♦

Row error logging. The session configuration object has new properties that allow you to define error logging. You can choose to log row errors in a central location to help understand the cause and source of errors.

♦

External loading enhancements. When using external loaders on Windows, you can now choose to load from a named pipe. When using external loaders on UNIX, you can now choose to load from staged files. Preface

xv

♦

External loading using Teradata Warehouse Builder. You can use Teradata Warehouse Builder to load to Teradata. You can choose to insert, update, upsert, or delete data. Additionally, Teradata Warehouse Builder can simultaneously read from multiple sources and load data into one or more tables.

♦

Mixed mode processing for Teradata external loaders. You can now use data driven load mode with Teradata external loaders. When you select data driven loading, the PowerCenter Server flags rows for insert, delete, or update. It writes a column in the target file or named pipe to indicate the update strategy. The control file uses these values to determine how to load data to the target.

♦

Concurrent processing. The PowerCenter Server now reads data concurrently from sources within a target load order group. This enables more efficient joins with minimal usage of memory and disk cache.

♦

Real time processing enhancements. You can now use real-time processing in sessions that also process active transformations, such as the Aggregator transformation. You can apply the transformation logic to rows defined by transaction boundaries.

Repository Server ♦

Object export and import enhancements. You can now export and import objects using the Repository Manager and pmrep. You can export and import multiple objects and objects types. You can export and import objects with or without their dependent objects. You can also export objects from a query result or objects history.

♦

pmrep commands. You can use pmrep to perform change management tasks, such as maintaining deployment groups and labels, checking in, deploying, importing, exporting, and listing objects. You can also use pmrep to run queries. The deployment and object import commands require you to use a control file to define options and resolve conflicts.

♦

Trusted connections. You can now use a Microsoft SQL Server trusted connection to connect to the repository.

Security

xvi

Preface

♦

LDAP user authentication. You can now use default repository user authentication or Lightweight Directory Access Protocol (LDAP) to authenticate users. If you use LDAP, the repository maintains an association between your repository user name and your external login name. When you log in to the repository, the security module passes your login name to the external directory for authentication. The repository maintains a status for each user. You can now enable or disable users from accessing the repository by changing the status. You do not have to delete user names from the repository.

♦

Use Repository Manager privilege. The Use Repository Manager privilege allows you to perform tasks in the Repository Manager, such as copy object, maintain labels, and change object status. You can perform the same tasks in the Designer and Workflow Manager if you have the Use Designer and Use Workflow Manager privileges.

♦

Audit trail. You can track changes to repository users, groups, privileges, and permissions through the Repository Server Administration Console. The Repository Agent logs security changes to a log file stored in the Repository Server installation directory. The

audit trail log contains information, such as changes to folder properties, adding or removing a user or group, and adding or removing privileges.

Transformations ♦

Custom transformation. Custom transformations operate in conjunction with procedures you create outside of the Designer interface to extend PowerCenter functionality. The Custom transformation replaces the Advanced External Procedure transformation. You can create Custom transformations with multiple input and output groups, and you can compile the procedure with any C compiler. You can create templates that customize the appearance and available properties of a Custom transformation you develop. You can specify the icons used for transformation, the colors, and the properties a mapping developer can modify. When you create a Custom transformation template, distribute the template with the DLL or shared library you develop.

♦

Joiner transformation. You can use the Joiner transformation to join two data streams that originate from the same source.

Version Control The PowerCenter Client and repository introduce features that allow you to create and manage multiple versions of objects in the repository. Version control allows you to maintain multiple versions of an object, control development on the object, track changes, and use deployment groups to copy specific groups of objects from one repository to another. Version control in PowerCenter includes the following features: ♦

Object versioning. Individual objects in the repository are now versioned. This allows you to store multiple copies of a given object during the development cycle. Each version is a separate object with unique properties.

♦

Check out and check in versioned objects. You can check out and reserve an object you want to edit, and check in the object when you are ready to create a new version of the object in the repository.

♦

Compare objects. The Repository Manager and Workflow Manager allow you to compare two repository objects of the same type to identify differences between them. You can compare Designer objects and Workflow Manager objects in the Repository Manager. You can compare tasks, sessions, worklets, and workflows in the Workflow Manager. The PowerCenter Client tools allow you to compare objects across open folders and repositories. You can also compare different versions of the same object.

♦

Delete or purge a version. You can delete an object from view and continue to store it in the repository. You can recover or undelete deleted objects. If you want to permanently remove an object version, you can purge it from the repository.

♦

Deployment. Unlike copying a folder, copying a deployment group allows you to copy a select number of objects from multiple folders in the source repository to multiple folders in the target repository. This gives you greater control over the specific objects copied from one repository to another.

Preface

xvii

♦

Deployment groups. You can create a deployment group that contains references to objects from multiple folders across the repository. You can create a static deployment group that you manually add objects to, or create a dynamic deployment group that uses a query to populate the group.

♦

Labels. A label is an object that you can apply to versioned objects in the repository. This allows you to associate multiple objects in groups defined by the label. You can use labels to track versioned objects during development, improve query results, and organize groups of objects for deployment or export and import.

♦

Queries. You can create a query that specifies conditions to search for objects in the repository. You can save queries for later use. You can make a private query, or you can share it with all users in the repository.

♦

Track changes to an object. You can view a history that includes all versions of an object and compare any version of the object in the history to any other version. This allows you to see the changes made to an object over time.

XML Support PowerCenter contains XML features that allow you to validate an XML file against an XML schema, declare multiple namespaces, use XPath to locate XML nodes, increase performance for large XML files, format your XML file output for increased readability, and parse or generate XML data from various sources. XML support in PowerCenter includes the following features: ♦

XML schema. You can use an XML schema to validate an XML file and to generate source and target definitions. XML schemas allow you to declare multiple namespaces so you can use prefixes for elements and attributes. XML schemas also allow you to define some complex datatypes.

♦

XPath support. The XML wizard allows you to view the structure of XML schema. You can use XPath to locate XML nodes.

♦

Increased performance for large XML files. When you process an XML file or stream, you can set commits and periodically flush XML data to the target instead of writing all the output at the end of the session. You can choose to append the data to the same target file or create a new target file after each flush.

♦

XML target enhancements. You can format the XML target file so that you can easily view the XML file in a text editor. You can also configure the PowerCenter Server to not output empty elements to the XML target.

Usability

xviii

Preface

♦

Copying objects. You can now copy objects from all the PowerCenter Client tools using the copy wizard to resolve conflicts. You can copy objects within folders, to other folders, and to different repositories. Within the Designer, you can also copy segments of mappings to a workspace in a new folder or repository.

♦

Comparing objects. You can compare workflows and tasks from the Workflow Manager. You can also compare all objects from within the Repository Manager.

♦

Change propagation. When you edit a port in a mapping, you can choose to propagate changed attributes throughout the mapping. The Designer propagates ports, expressions, and conditions based on the direction that you propagate and the attributes you choose to propagate.

♦

Enhanced partitioning interface. The Session Wizard is enhanced to provide a graphical depiction of a mapping when you configure partitioning.

♦

Revert to saved. You can now revert to the last saved version of an object in the Workflow Manager. When you do this, the Workflow Manager accesses the repository to retrieve the last-saved version of the object.

♦

Enhanced validation messages. The PowerCenter Client writes messages in the Output window that describe why it invalidates a mapping or workflow when you modify a dependent object.

♦

Validate multiple objects. You can validate multiple objects in the repository without fetching them into the workspace. You can save and optionally check in objects that change from invalid to valid status as a result of the validation. You can validate sessions, mappings, mapplets, workflows, and worklets.

♦

View dependencies. Before you edit or delete versioned objects, such as sources, targets, mappings, or workflows, you can view dependencies to see the impact on other objects. You can view parent and child dependencies and global shortcuts across repositories. Viewing dependencies help you modify objects and composite objects without breaking dependencies.

♦

Refresh session mappings. In the Workflow Manager, you can refresh a session mapping.

Preface

xix

About Informatica Documentation The complete set of documentation for PowerCenter includes the following books:

xx

Preface

♦

Data Profiling Guide. Provides information about how to profile PowerCenter sources to evaluate source data and detect patterns and exceptions.

♦

Designer Guide. Provides information needed to use the Designer. Includes information to help you create mappings, mapplets, and transformations. Also includes a description of the transformation datatypes used to process and transform source data.

♦

Getting Started. Provides basic tutorials for getting started.

♦

Installation and Configuration Guide. Provides information needed to install and configure the PowerCenter tools, including details on environment variables and database connections.

♦

PowerCenter Connect® for JMS® User and Administrator Guide. Provides information to install PowerCenter Connect for JMS, build mappings, extract data from JMS messages, and load data into JMS messages.

♦

Repository Guide. Provides information needed to administer the repository using the Repository Manager or the pmrep command line program. Includes details on functionality available in the Repository Manager and Administration Console, such as creating and maintaining repositories, folders, users, groups, and permissions and privileges.

♦

Transformation Language Reference. Provides syntax descriptions and examples for each transformation function provided with PowerCenter.

♦

Transformation Guide. Provides information on how to create and configure each type of transformation in the Designer.

♦

Troubleshooting Guide. Lists error messages that you might encounter while using PowerCenter. Each error message includes one or more possible causes and actions that you can take to correct the condition.

♦

Web Services Provider Guide. Provides information you need to install and configure the Web Services Hub. This guide also provides information about how to use the web services that the Web Services Hub hosts. The Web Services Hub hosts Real-time Web Services, Batch Web Services, and Metadata Web Services.

♦

Workflow Administration Guide. Provides information to help you create and run workflows in the Workflow Manager, as well as monitor workflows in the Workflow Monitor. Also contains information on administering the PowerCenter Server and performance tuning.

♦

XML User Guide. Provides information you need to create XML definitions from XML, XSD, or DTD files, and relational or other XML definitions. Includes information on running sessions with XML data. Also includes details on using the midstream XML transformations to parse or generate XML data within a pipeline.

About this Book The Troubleshooting Guide is written for all PowerCenter users. It contains troubleshooting information for all aspects of data warehouse development, including source analysis, transformation development, mapping development, and running sessions. This guide assumes you have knowledge about your operating systems, relational database concepts, and the interface requirements for your supporting applications. The material in this book is available for online use.

Document Conventions This guide uses the following formatting conventions: If you see…

It means…

italicized text

The word or set of words are especially emphasized.

boldfaced text

Emphasized subjects.

italicized monospaced text

This is the variable name for a value you enter as part of an operating system command. This is generic text that should be replaced with user-supplied values.

Note:

The following paragraph provides additional facts.

Tip:

The following paragraph provides suggested uses.

Warning:

The following paragraph notes situations where you can overwrite or corrupt data, unless you follow the specified procedure.

monospaced text

This is a code example.

bold monospaced text

This is an operating system command you enter from a prompt to run a task.

Preface

xxi

Other Informatica Resources In addition to the product manuals, Informatica provides these other resources: ♦

Informatica Customer Portal

♦

Informatica Webzine

♦

Informatica web site

♦

Informatica Developer Network

♦

Informatica Technical Support

Visiting Informatica Customer Portal As an Informatica customer, you can access the Informatica Customer Portal site at http:// my.informatica.com. The site contains product information, user group information, newsletters, access to the Informatica customer support case management system (ATLAS), the Informatica Knowledgebase, Informatica Webzine, and access to the Informatica user community.

Visiting the Informatica Webzine The Informatica Documentation team delivers an online journal, the Informatica Webzine. This journal provides solutions to common tasks, detailed descriptions of specific features, and tips and tricks to help you develop data warehouses. The Informatica Webzine is a password-protected site that you can access through the Customer Portal. The Customer Portal has an online registration form for login accounts to its webzine and web support. To register for an account, go to http://my.informatica.com. If you have any questions, please email [email protected].

Visiting the Informatica Web Site You can access Informatica’s corporate web site at http://www.informatica.com. The site contains information about Informatica, its background, upcoming events, and locating your closest sales office. You will also find product information, as well as literature and partner information. The services area of the site includes important information on technical support, training and education, and implementation services.

Visiting the Informatica Developer Network The Informatica Developer Network is a web-based forum for third-party software developers. You can access the Informatica Developer Network at the following URL: http://devnet.informatica.com

xxii

Preface

The site contains information on how to create, market, and support customer-oriented addon solutions based on Informatica’s interoperability interfaces.

Obtaining Technical Support There are many ways to access Informatica technical support. You can call or email your nearest Technical Support Center listed below or you can use our WebSupport Service. WebSupport requires a user name and password. You can request a user name and password at http://my.informatica.com. North America / South America

Africa / Asia / Australia / Europe

Informatica Corporation 2100 Seaport Blvd. Redwood City, CA 94063 Phone: 866.563.6332 or 650.385.5800 Fax: 650.213.9489 Hours: 6 a.m. - 6 p.m. (PST/PDT) email: [email protected]

Informatica Software Ltd. 6 Waltham Park Waltham Road, White Waltham Maidenhead, Berkshire SL6 3TN Phone: 44 870 606 1525 Fax: +44 1628 511 411 Hours: 9 a.m. - 5:30 p.m. (GMT) email: [email protected] Belgium Phone: +32 15 281 702 Hours: 9 a.m. - 5:30 p.m. (local time) France Phone: +33 1 41 38 92 26 Hours: 9 a.m. - 5:30 p.m. (local time) Germany Phone: +49 1805 702 702 Hours: 9 a.m. - 5:30 p.m. (local time) Netherlands Phone: +31 306 082 089 Hours: 9 a.m. - 5:30 p.m. (local time) Singapore Phone: +65 322 8589 Hours: 9 a.m. - 5 p.m. (local time) Switzerland Phone: +41 800 81 80 70 Hours: 8 a.m. - 5 p.m. (local time)

Preface

xxiii

xxiv

Preface

Chapter 1

Client Error Messages This chapter covers the following error message topics: ♦

Overview, 2

♦

Business Components Messages, 3

♦

Mapping Variable Messages, 4

♦

Metadata Extension Messages, 5

♦

Repository Server Administration Console Messages, 6

♦

Repository Upgrade Messages, 7

♦

Session Log Messages, 11

♦

Validation Messages, 12

♦

Workflow Log Messages, 14

♦

Workflow Manager Messages, 16

♦

XML Messages, 18

1

Overview The PowerCenter Client tools display messages in the status bar as you perform tasks, such as opening or copying objects. The Designer displays error messages when you perform tasks that are not permitted, such as importing an invalid XML file. This chapter contains messages that may display in the PowerCenter Client applications. Messages are listed alphabetically by category, such as validation or mapping variables. You can use this chapter to help determine what causes error messages to appear and what measures you can take to correct the problems. If you follow the suggested instructions, but the problem persists, please contact Informatica Technical Support for additional help. For information on error messages the PowerCenter Client might display when you export or import an object, see “Exporting and Importing Objects Messages” on page 30.

2

Chapter 1: Client Error Messages

Business Components Messages The following business component messages may appear in the Designer Output window, status bar, or a message box of the PowerCenter Client: Cannot copy/move directory: The destination directory is a subdirectory of the source directory.

Cause:

You tried to move a directory into a subdirectory.

Action:

Check the directory structure before moving directories.

Cannot create a business component at the root level: please select a directory to use.

Cause:

You tried to put an object directly in a business components node.

Action:

You can put an object in a directory in the business components node, but not directly in the business components node.

Only Sources and Mapplets can be placed in Business Components.

Cause:

You tried to place a transformation, cube, mapping, or target in a business components directory.

Action:

Business component directories support sources and mapplets only.