© 2009 Informatica Corporation

er Resource in Metadata Manager 8.5 and 8.6

Configuring a PowerCent

Abstract This article shows you how to create and configure a PowerCenter 7.x and 8.x resource in Metadata Manager 8.5, 8.6,

rsions er 8.6.1

Metadata Manager 8.6

8.5

........................... 2

........................... 3

........................... 3

........................... 3

........................... 3 5

........................... 5

........................... 6

........................... 7

........................... 9

........................... 9 ............ 9

......................... 10 10

......................... 10

......................... 11 d PowerCenter Repository ........................................................................................................................... 11 onstraint Errors ............................................................................................................................................ 11

............................... 11 Folder Inclusion List.................................................................................................................................................... 11 Source Incremental Extract Window (in Days) ........................................................................................................... 11

Overview Informatica PowerCenter provides an environment that allows you to load data into a centralized location, such as a data warehouse or operational data store (ODS). You can extract data from multiple sources, transform the data

8.6.1.

Supported Ve Metadata Manag

Metadata Manager

Table of Contents Overview ................................................................................................................................................

Before You Begin...................................................................................................................................

Increase the IBM DB2 for LUW Database Heap Size .......................................................................

Configure Permissions ......................................................................................................................

Create the PowerCenter Resource........................................................................................................

Configure PowerCenter Parameter Files ..........................................................................................................................

Parameter File Requirements............................................................................................................

Assigning Parameter Files to PowerCenter Workflows .....................................................................

Troubleshooting .....................................................................................................................................

Reference ..............................................................................................................................................

Metadata Object Properties...............................................................................................................

Record Filters and Rejects ...............................................................................................................................

Scope for Data Lineage.....................................................................................................................

Case Sensitivity ..........................................................................................................................................................

Flat Files ............................................................................................................................................

Advanced External Procedure Transformations................................................................................

Versione

Unique C

Mapplets ......................................................................................................................................

2

according to business logic you build in the client application, and load the transformed data into file and relational

data Manager 8.5, 8.6, from a PowerCenter resource and loads it into a centralized metadata rehouse.

ase the IBM DB2 for Linux, UNIX, and Windows

ust g error:

ccurred. : [IBM][CLI QL0973N Not enough storage is available in the "APP_CTL_HEAP_SZ" he statement. SQLSTATE=57011

ere is not enough storage available in the database application heap to process the

ger.

server.

200 to 16384 or higher.

re Permissions etadata from a PowerCenter version 7.x or 8.x repository, the PowerCenter repository user account

s on the PowerCenter repository database:

Select view

for LUW, the user account also requires create and drop function

the PowerCenter Resource To create a PowerCenter resource: 1. On the Load page, click the New Resource icon.

The Resource Selection window appears.

2. Click Data Integration > PowerCenter.

3. Click Next.

The Properties page appears.

targets.

This article shows you how to create and configure a PowerCenter 7.x and 8.x resource in Meta8.6.1. Metadata Manager extracts metadatawarehouse called the Metadata Manager wa

Before You Begin Before you configure a PowerCenter 7.x and 8.x resource, incredatabase heap size and configure permissions.

Increase the IBM DB2 for LUW Database Heap Size When you load metadata from a PowerCenter 7.x or 8.x repository that is on an IBM DB2 for LUW database, you mmodify the heap size. Otherwise, the S_M_ELEMNT_ATTR_EXTRACT session fails with the followin

DBA2191E SQL execution error.

com.ibm.db.DataException: A database manager error oDriver][DB2/NT] Sheap to process t

This problem occurs when threquest. To increase the IBM DB2 for LUW database heap size, complete the following steps:

1. Log out of Metadata Mana

2. Stop the DB2 database

3. Change the Application Heap Size configuration parameter (APP_CTL_HEAP_SZ) from 1

4. Restart the DB2 database server.

ConfiguTo extractrequires the following permission

m

Select table

Create view

Drop view

If the PowerCenter repository is on IBM DB2permissions.

Create

3

4. he followi on:

roperty

Enter t ng informati

P Description Name n with this name. The

between 1 and 255 characters. For multibyte character sets, the een 1 and 127 characters.

Name for the resource. The resource appears in the Resource List sectioname must be unique and beresource name must be betw

It cannot include the following characters:

/ \ : * ' ? " < > | [ ]

Description Description for the resource. Description cannot exceed 4,000 characters. For multibyte character ts, the description cannot exceed 2000 characters. se

5. Click Next.

The Configurati ears.

6. re the fo

rty

on page app

Configu

Prope

llowing resource connection properties:

Description Check for unassigned connections

owerCenter repository and the underlying nections between the PowerCenter resource and

Checks for unassigned connections between the Pdatabase schemas. Select this option to make consource and target resources during the load process.

Login user Database user name for the PowerCenter repository database.

Login password Database user password for the PowerCenter repository database.

Database type tabase type can be IBM DB2 for LUW, Type of database for the PowerCenter repository. DaMicrosoft SQL Server, Oracle, or Sybase ASE.

Source JDBC U t name and the base service name. Metadata Manager uses this URL, for example, to verify connection

]:1521;SID=[sid]

e SID or edit the string to use the full service name. For

ervice name]

RL JDBC URL for the PowerCenter repository database. Enter the database hosdatainformation to the source database.

For example, if you connect to an Oracle database, the URL must be: jdbc:informatica:oracle://[host

For Oracle databases, you can enter thexample: jdbc:informatica:oracle://[host]:1521;ServiceName=[s

Connect string Native connect string for the PowerCenter repository database. Metadata Manager uses the connect string to create a source connection to the source PowerCenter repository in the

er repository associated with Metadata Manager. PowerCent

Encoding Code page for the PowerCenter repository database.

er repository, and the code page a Manager runs must be the

same.

The code page for the PowerCenter resource, the Metadata Managon the machine where the associated Integration Service for Metadat

7. Click Next.

The Parameters window appears.

8. Configure the folders to extract metadata from in the Select Folders section.

9. To add folders to the Selected Folders list, select the folder in the Available Folders list and click Add.

-or-

To add all available folders, click Add All.

10. To remove folders from the Selected Folders list, select the folder in the Selected Folders list and click Remove.

-or-

4

To remove all folders from the Selected Folders list, click Remove All.

es.

r workflow, select the parameter file, select the workflow, and click Assign.

d click Delete.

-

flows.

er file using the information from the PowerCenter source repository metadata, click

ove all changes, click Clear All.

days for the Source increment extract window.

11. Click Upload files to upload PowerCenter parameter fil

12. Configure the parameter files in the Assign parameter files section.

To assign a parameter file to a PowerCente

-or-

To remove a parameter file from the assigned workflow, select the parameter file an

-or

To assign a parameter file to all workflows, select the parameter file and click Assign to all work

-or-

To assign the source parametPreassign.

-or-

To rem

13. Configure the number of

For more information, see Source Incremental Extract Window (in Days).

14. Click Next.

The Schedules window appears.

e to the resource after you create the resource.

ces, lookups and arameter files, you can

, lookups, targets, ce.

Assign parameter files to PowerCenter workflows if you want to perform data lineage analysis between the tadata in the flat file sources, lookups, and targets, relational connections, or

y data lineage.

les to the machine running the Metadata Manager s to individual workflows or all

Parameter File Requirements To enable Metadata Manager to read parameter values from a parameter file, the file must have a .txt, .prm, or .par extension. Each parameter file must contain the following information:

Session name. Each parameter file must contain the names of the sessions in the workflow.

Parameter name. Metadata Manager Console reads and stores all parameters in the file.

15. To add a schedule, select Attach a Schedule, and select a schedule in the Schedule list.

If you have not created a schedule, you can assign a schedul

16. Click Finish.

The Resource appears in the Resource List section on the Load page.

Configure PowerCenter Parameter Files You can use parameters in PowerCenter sessions. The parameters can represent flat file sourtargets, relational connections, procedure objects, or objects in SQL overrides. If you use pconfigure Metadata Manager to read the parameter files to extract metadata for flat file sourcesrelational connections, and objects in SQL overrides when you load the PowerCenter resour

PowerCenter repository and the meobjects in SQL overrides. Metadata Manager uses the information in the parameter files to displa

To assign the parameter files, you upload the parameter fiapplication. After you upload the parameter files, you can assign the parameter fileworkflows. You also direct Metadata Manager to assign the parameter files to the appropriate workflows in the PowerCenter repository.

5

You can define parameters and variables in any section in the parameter file. If you define a workflsession section, the value of the workflow variable appli

ow variable in a es only when the session runs. For multiple parameter values

s that define each section in the parameter file and the scope e parameters and variables that you can define in each section:

Heading

in the same scope, Metadata Manager considers only the first one.

The following table describes the parameter file headingof th

Scope

All Integration Services, Integration processe

Ses, workflows, worklets, and sessions.

rvice [Global]

The named workflow and all sessionsthe

within [folder name.WF:workflow name] workflow.

The named worklet and all sessions within the [folder name.WF:workflow name.WT:worklet name] worklet.

The nested worklet and all sessions within the nested worklet.

[folder name.WF:workflow name.WT:worklet name.WT:worklet name...]

Metadata Manager ignores the following information in the parameter files:

parameters.

n parameter

ns. Metadata Manager extracts the connection for the first occurrence of the session. If you use ust identify each session instance.

e correct format.

ample connection parameter file entry: _ORCL.ST:S_M_ELMNT_ASSOC_EXTRACT]

NECTION_SOURCE=hello

sessionname1.log

Assigning Parameter Files to PowerCenter Workflows You can assign parameter files to PowerCenter workflows after you create the PowerCenter resource. Assign parameter files to PowerCenter workflows in the Parameters tab for a PowerCenter resource on the Load page. You cannot assign parameter files to individual PowerCenter sources, targets, or lookups. You assign parameter files to the workflow that contains the sources, targets, or lookups.

Workflow and service parameters. Metadata Manager does not extract workflow and service

Sessions without parameters. Metadata Manager does not require all sessions to use connectiofiles.

Duplicate sessioa reusable session multiple times, the parameter file m

Invalid parameter and value pair format. The parameter and value must be in th

Sample Parameter File The following example shows a s

[CustomerInformation.WF:WF_DB_CATALOG

$$WEIRD_CHAR=~!@#$%^&*()_+|{}[]=:";'<>?,./`

$DBConnection_source=customer_source_DB$DBCON

$DBConnection_target=customer_target_DB

$DBConnection_SARAH=sarah

$DBCONNECTION_WEED=weedy

$PMSessionLogFile=$PMRootDir/

6

The following figure shows the Parameters tab for a PowerCenter resource:

lect the parameter file in the Upload files window and click OK.

s list, select the d click Assign.

arameter file to er file and click Preassign.

Metadata Manager analyzes the PowerCenter repository to determine which

6. To remove a parameter file, select the parameter file in the Folders list and click Delete. Optionally, you can click e all parameter file assignments.

following error: tory Root

Cause:

[[REP_57140] Object does not exist.] Folder Metadata Load not found. Failed to execute updateseqgenvals.

This error occurs if an administrator deletes the Metadata Load folder after starting the Metadata Manager Service.

When you start the Metadata Manager Service, Metadata Manager creates the Metadata Load folder containing the workflows and worklets necessary for the load. Before starting the load, Metadata Manager verifies that the folder exists in the associated PowerCenter repository. If the folder does not exist in the repository, it imports the Metadata Load folder when you start the service. If an administrator deletes the folder, Metadata Manager cannot execute the load.

To assign a parameter file to a PowerCenter workflow: 1. On the Load page, select the PowerCenter resource.

2. In the Resource Properties section, click the Parameters tab.

3. To upload parameter files, click Upload files. Se

4. Select the parameter file you want to assign to a PowerCenter workflow in the Parameter FilePowerCenter folder or workflow in the Folders list, an

5. Optionally, you can select a parameter file and click Assign to All Workflows to assign the same pall workflows or select a paramet

If you preassign the parameter file,workflows require the parameter file when you load the resource.

Clear All to remov

7. To save the changes, click the Save icon.

8. To revert the changes, click the Revert icon.

Note: After you upload a parameter file, you cannot delete it from the Parameter Files list.

Troubleshooting When I run a resource load in Metadata Manager 8.5.x/8.6.x, Metadata Manager reports the

Could not update sequences generation values in PowerCenter reposi

Upload a parameter file

Parameter files Assign or delete parameter files

Folders, workflows, and assigned parameter files

7

To resolve the error: 1. Disable and enable the Metadata Manager Service.

2. Connect to the associated PowerCenter repository using the PowerCenter Client.

t the following error in the S_M_PC8X_EXPR_PARSER session:

hared/ExtProc/

erver/infa_shared/ExtProc/lnot open shared object file: No such file or directory]

common external procedure information.

yle external procedure.

[AEP_ExpressionParser]

r library in its default location. The default ibraries is $INFA_HOME$/server/bin/ExtProc.

r Administration Console, select the Metadata Manager Service.

ab.

.

the Processes tab.

ars.

ot copy the ExtProc directory, copy the following Metadata Manager specific libraries from ectory to which $PmExtProcDir points: InfExprParser, InfParamReplacer,

error: tory sessions for fetching connection information failed. For

nager Service log.

To resolve the error: 1. Enable the Metadata Manager Integration Service.

2. Refresh the connection to the PowerCenter resource.

3. From the PowerCenter Workflow Monitor, connect to the PowerCenter repository for Metadata Manager.

4. Open the Metadata Load folder.

5. Check the log for the S_M_CONNECTION_EXTRACT session.

3. Check that Metadata Manager created the Metadata Load folder.

When I loadCannot load external module.

a PowerCenter resource, I ge

EP_13008 [/dwsasprd/pmserv/pmprod/Informatica/PowerCenter8.5.1/server/infa_slibInfExprParser.so.1:

/dwsasprd/pmserv/pmprod/Informatica/PowerCenter8.5.1/sibInfExprParser.so.1: can

EP_13007 : Cannot create external module manager.

EP_13003 : Cannot prepare

EP_13003 [AEP_ExpressionParser]

EP_13005 : Cannot initialize Informatica-st

EP_13005

This error occurs when Metadata Manager cannot find the $PmExtProcDilocation for Metadata Manager l

To resolve the error: 1. In the PowerCente

2. Click the Associated Services t

3. Click the Associated Integration Service

4. Click

The General Properties page appe

5. Click Edit.

6. Change the value of $PmExtProcDir to ./ExtProc.

7. Click OK.

8. Disable and enable the Integration Service.

9. Load the PowerCenter resource again.

Note: If you cann$INFA_HOME$/server/bin/ExtProc into the dirInfSQLParser, InfTreeExpansion, and CRC32.

When I refresh the PowerCenter connections in Metadata Manager, I get the following PowerCenter reposimore information, see the Metadata Ma

8

Reference This section provides the following reference information about the metadata that Metadata Manager extracts for a

operties

s and rejects

e

l Procedure transformations

oned PowerCenter repository

int errors

the Metadata

ecords. Metadata

SQL override that e SQL override

the IBM DB2 for LUW database can store up ong three

ontains the first 2,000 characters, the second record contains the next 2,000 characters, and d record con

ta Manager only extracts the first record for any object property, Metadata Manager only extracts the first s of etadata from the IBM DB2 for LUW database table.

following table aximum number of characters extracted for a PowerCenter metadata object property ined in the giv

mum Characters Extracted

PowerCenter resource:

Metadata object pr

Record filter

Scope for lineag

Case sensitivity

Flat files

Advanced Externa

Versi

Unique constra

Mapplets

Folder Inclusion List

Source Incremental Extract Window (in Days)

Metadata Object Properties Metadata Manager extracts properties of PowerCenter metadata objects and stores their values inManager warehouse.

Metadata Manager truncates the value of an object property if the value is stored in multiple rManager extracts the value from the first record only.

For example, a PowerCenter repository resides on an IBM DB2 for LUW database and contains anis 5,000 characters long. When you create the SQL override in PowerCenter, PowerCenter stores thmetadata in three records in an IBM DB2 for LUW database table. Sinceto 2,000 characters for each field, PowerCenter splits the 5,000-character SQL override metadata amrecords. The first record cthe thir tains 1,000 characters.

Since Metada2,000 character SQL override m

The provides the mconta en database:

Database Maxi

IBM DB2 for LUW 2,000 characters

Microsoft SQL Server 4,000 characters

Oracle 2,000 characters

Sybase 4,000 characters

Record Filters and Rejects While loading PowerCenter sessions into the Metadata Manager warehouse, Metadata Manager rejects or filters the records if it cannot resolve their corresponding database columns.

Metadata Manager filters records that meet any of the following conditions:

The session instance connection is not assigned to a database schema.

9

The connection assignment is valid, Metadata Manager cannot resolve the database column, and an SQL up or Source Qualifier transformation.

not resolve the PowerCenter target structure and the corresponding database columns.

e e following objects:

n connections

instances

l lineage for each transformation

ter does not indicate if connections and databases are case sensitive. Metadata Manager assumes that the ucture name and

iles ter can use flat files as sources, targets, and lookups in mappings. Metadata Manager displays the following

files from a PowerCenter source repository in the metadata catalog:

ile

t file under a

ssign an Integration Service to run the workflow. The metadata catalog displays flat file sources and targets from a PowerCenter repository under the corresponding Integration Service. If the flat file is located in a repository with no Integration Service configured, the metadata catalog displays the flat file under the object called “Unknown Service.”

Metadata Manager determines the columns in the delimited and fixed-width flat files using source and target definitions in PowerCenter. Metadata Manager displays field-level details in data lineage for columns in delimited and fixed-width flat files. Since XML and VSAM flat files do not contain columns, Metadata Manager does not display field-level details in data lineage for these flat file types.

override exists on the reader transformation, such as Look

Metadata Manager rejects records that meet any of the following conditions:

The session instance connection is assigned to a schema, the Source Qualifier transformation does not have an SQL override, and Metadata Manager cannot resolve the database columns.

Metadata Manager can

Scope for Data LineagMetadata Manager supports data lineage for th

Local shortcuts, not global shortcuts

Relational connections, not applicatio

Lookup transformations and source

Structure-leve

Connected and unconnected transformations

Case Sensitivity PowerCendatabase storing the PowerCenter metadata is not case sensitive. Metadata Manager stores the strproperty name in uppercase letters.

Flat FPowerCentypes of flat

XML

VSAM

Delimited

Fixed-width

In PowerCenter 7.x, you can assign a PowerCenter Server to run the workflow. The metadata catalog displays flat fsources and targets from PowerCenter 7.x repositories under the corresponding PowerCenter Server. If the flat file is located in a repository with no PowerCenter Server configured, the metadata catalog displays the flaobject called “Unknown Server.”

In PowerCenter 8.x, you can a

10

Advanced External Procedure Transformations If you created Advanced External Procedure transformations in a PowerCenter 6.2.x or earlier repoupgraded the repository to PowerCenter 7.x, PowerCenter converts these

sitory, and you transformations to Custom transformations.

transformations as Custom transformations.

objects that are checked into a PowerCenter repository. Metadata cts that are checked out.

TR_EXTRACT session can reject rows due to unique rors, because the PowerCenter SourceDefinition object contains duplicate attribute names. For

it contains duplicate Tree Name, Set ID, Effective Date, and Set Control Value attributes for the

t that does not contain any instances in PowerCenter ager displays the mapplet and the mapplet ports in the data lineage diagram. Metadata

y links from the mapplet ports to other PowerCenter objects. If you launch lineage on a ger displays the links from

tput ports to the other PowerCenter objects.

data Manager warehouse.

erCe r folder into the Included Folders list. m Excluded Folders list.

s to modify a folder after loading it into the Metadata Manager warehouse:

The metadata catalog shows the upgraded

Versioned PowerCenter Repository Metadata Manager extracts the latest version of Manager does not extract obje

Unique Constraint Errors When you load a PowerCenter resource, the S_M_ELMNT_ATkey constraint erexample, SourceDefinition object. Metadata Manager extracts only one of the values.

Mapplets If you launch data lineage on a mapplet port for a mapplemappings, Metadata ManManager does not displamapplet port, and the mapplet contains instances in PowerCenter mappings, Metadata Manathe mapplet input and ou

Folder Inclusion List Use the Folder IMeta

nclusion List parameter to specify folders of PowerCenter objects that you want to load into the

To load PowerCenter objects from a Pow nter folder, move the PowerCenteTo exclude PowerCenter objects in a folder,

The following table describes the option

ove the folder into the

Option Action

Refresh the metadata associated with the folder. Retain the folder in the Metadata Manager warehouse.

Select Refresh for the folder.

Retain the folder in the Metadata Manager warehouse. Do not refresh the metadata associated with the folder.

Clear Refresh for the folder.

Remove the folder and its associated objects from the Metadata Manager warehouse.

Remove the folder from the Folder Inclusion Lfolder and click Rem

ist. Select the ove.

Source Incremental Extract Window (in Days) Metadata Manager supports incremental extractions from the PowerCenter 7.x and 8.x repositories. By default, Metadata Manager can extract metadata incrementally. Metadata Manager initially extracts metadata from the previous 4,000 days, which is longer than 10 years. After the first metadata load, decrease the time period to extract metadata that has been added, modified, or deleted recently. For subsequent loads, Metadata Manager only extracts the metadata modified in the number of days indicated in the parameter for subsequent loads.

After the initial extract completes, reset the parameter value based on the frequency at which you load the metadata for the resource. As a guideline, set the parameter value to at least twice the number of days between resource loads. For example, if you load the resource every two days, set the parameter value to four. By doubling the amount of time,

11

12

Metadata Manager extracts metadata for the current and previous time period. If the previous load fails, Metadata

reset the default parameter value, Metadata Manager extracts metadata for the past 4,000 days when you

Note: The first time you load a PowerCenter resource, Metadata Manager may reject objects created before the ntal extract period.

Ninad Wagle Senior Technical Writer

Manager extracts information for the previous period and the current period.

If you do not load the resource.

increme

Author