informatica developer tips for troubleshooting...

1

Informatica Developer Tips for Troubleshooting Common IssuesPowerCenter 8 Standard Edition

Eugene GonzalezSupport Enablement Manager, Informatica

2

Agenda

• Knowledge• Understand the PowerCenter architecture and components• Learn the other building blocks in the system

• Database• Operating system

• Techniques• Identify exact error message and search Knowledgebase• Isolate the issue to a component, scenario or data element

Troubleshooting PowerCenter issues require a combination of basic PowerCenter knowledge and techniques

3

Agenda

• Troubleshooting

• Installation and configuration

• Upgrading to PowerCenter 8

• Running Workflows and Sessions

• Performance issues

4

Troubleshooting : PowerCenter 8 -Installation and configuration

5

Installation Resources

• Documentation • Installation and Configuration Guide • Administrator Guide • Release notes

• “my.informatica.com” Support tab• Documentation download• Knowledge Base• Support user tools • SupportFlash• Velocity

6

Before you Install

• Review• Minimum system and OS patches requirements • Database requirements• Code page requirements• Consistent 32 bit or 64 bit environment• Open necessary network ports

• Remove old or any PowerCenter Environment variables

• Don’t install all components at once

7

PowerCenter 8 Architecture

Node

Domain

Application Services

Sources Targets

Integration Service

Repository

Repository Service

Service Manager

(Logging, Licensing, Domain config)

Repository Metadata database

Domain configMetadata database

8

PowerCenter 8 – New concepts

• Default domain gateway node port • http protocol 6001• https protocol 8443

• Default services ports are dynamic with in default range : 6005 –6105

• New Configuration files• Domains.infa (Install Directory)• nodemeta.xml (..\server\config)

• New admin console• To assist in Administration, Configuration and Upgrades

• Service pack releases• Integrated patch process

9

Server side Installer steps

• Extract files to temp directory and Copy the Install files

• Get Domain , node and service information from user

• Create a node

• Create services

• Start Node

10

Where exactly to look for errors?

• If domain creation/startup fails• Installation logs (debug, install logs)• exceptions.log (..\server\tomcat\logs)• node.log (..\server\tomcat\logs)• catalina.out (..\server\tomcat\logs)

• If Domain is up, for service level failures• Domain and service log (Admin console)

11

Collecting log information

• InfaLogs (for PowerCenter 8x)• InfaLogs aids the user to extract meaningful and necessary

PowerCenter log files (session Logs, repository server and agentlogs, pmserver logs, tomcat logs).

• catalina. Out• node.log• services.log• exceptions.log• WebServices_Hub.log

• Logs are collected and automatically placed in a zip file.• The zip file can also be submitted to support• Use of this tool allows the user to quickly get the logs without

needing the knowledge of where the logs are located. • Download from “my.informatica” support tools.

12

Common Installation Problems

• Review the Installation steps using the log• The installation log is located in the PowerCenter installation

directory.

• Identify specific error messages and search the Support Knowledgebase • my.informatica.com – Support tab

• Firewall settings• Domain console hangs.• Domain services begin to start but shuts down right away.• Firewall port setting should not block PowerCenter ports.

13


• Environmental variables settings will prevent a domain service to start• “path” or “library path” – service may not start because a

command or library may not be found.

• Wrong 32/64 bit-type of database library• Repository service can fail to start because the database

client (oracle) pointing to the incorrect 64 bit library insteadof the 32 bit library.

14


• LANG setting in UNIX or Database• LANG, LC_CTYPE, or LC_ALL to set the UNIX code page• If the local setting is not compatible with an existing

repository code page you can not create a Repository Service. “local –a” returns the languages installed on the UNIX system. Windows “mode” command returns the windows console codepage.

• If all else fails – start over again

15

Support Scan Utility (SSCAN)

• Collects all the system, environment and the product details on the machine hosting Informatica PowerCenter. • System Information - OS, Memory, CPU, OS patches, etc.• PowerCenter Information - environment variables, configuration,

etc.• Database information – environment settings, library, database

version, etc.

• The output produced by the utility needs to be sent to Informatica Customer Support for more detailed analysis

• Download from “my.informatica” support tools.

16

Tips and Techniques

• Restart server• After installing drivers on Windows• After installing patches

• Make sure the installation folder name does not contain spaces

• Server host name resolution • DNS server information may not be up-to-date• Host file entry may need to be modified to a fully qualified entry. • Example: server123 is used instead of

server123.europe.informatica.com

17

What info to send to support?

• InfaLogs – Collects relevant logs• catalina. Out• node.log• services.log• exceptions.log

• SSCAN – Collects system environmental information.• Creates and xml file as an output• Collects system, memory, path, and patch level information

• What to look for the tools output• LANG settings• System and library path

• Database client library path• OS Patch information• Error messages

• Download tools from “my.informatica” support tools.

18

Upgrading to PowerCenter 8 Using the Upgrade Planner

19

PowerCenter 8.5 - Upgrade Planner

• Planning upgrade use the PowerCenter 8.5 multimedia tutorial.

• Available at the “my.informatica” “Online Support” page.

20


Topics Covered•e-learning module•30 min duration•Architectural overview

•Upgrade Paths•Upgrade Process•Architecture

•Upgrading from PC7.X or from PC8.1.1Functional ChangesUpgrade Resources

21


Multimedia Presentation

Use button to mover forward or backward

22


The Planner will ask questions about your environment

23


Multimedia Architectural Overview

24


Presentation showing a PC7.X objects relate to PC8.5

25


Explaining New Domain Security

26


Explaining System Requirements

27


In this example: Step by step upgrade process fromPC7.X to PC8.5

28


Explaining 32 bit and 64 bit drivers

29


Walking through the installation process

30


Explaining upgrade options:Upgrade Wizard or manually

31


Explaining Post Upgrade Steps

32


Explaining functionality changes between release levels

33


Explaining available resources

34

PowerCenter 8.5 - Upgrade Resources

• Post-Upgrade Reference Documentation• Post-Upgrade Summary e.g.

• FAQ: Global Customer Support PowerCenter 8.5 Installation and Upgrade FAQs

35

Tips and Techniques

• Review the installation process through the use of the Informatica Support Center Upgrade Planner.• 30 minuets of your time well spent.

• Take Backup! Upgrade is not reversible.

• If you are upgrading an existing a “live”repository make sure no agent is running against that repository.

36

Tips and Techniques

• Compare Utility (New) Compare Utility - test tool to validate upgrades or simply compare between 2 instances of PowerCenter.

• Some of the things you may do with the tool include: • Compare the relational and flat file outputs of any workflows pre

and post upgrade for differences. • See how your repository objects have been affected by the

upgrade. • Generate reports detailing any differences that were found.

• Download from “my.informatica” support tools.

37


• SSCAN – Collects system environmental information.• Creates and xml file as an output• Collects system, memory, path, and patch level information

• Repository service log

• Domain log

• Old repository backup (if necessary)

38

Troubleshooting PowerCenter

39

Troubleshooting Notes

• First, always verify domain is running• The domain will not run unless it can access the domain

tables

• Reasons you can’t connect to the repository:• Domain is running, but Repository Service failed to start• Domain is not running

• Reasons the Integration Service might not start:• Its associated repository is not running• Domain is running, but Integration Service failed to start• Domain is not running

40

Verify Domain is Running

• Try to log in to the Administration Console

• If you do not see a login screen, start Informatica Services

• If you still do not see a login screen, use the domain check, enter this URL:• http://<hostname>:<portnum>/coreservices/DomainService• E.g. http://p161585:6001/coreservices/DomainService• URL is case sensitive• Returns domain status

41

Domain Check

Domain is running fine in this example.

42

If Domain is Not RunningService Manager Log File

• If domain is not running, read node.log for gateway node

• /server/tomcat/logs directory

• Stores log events generated during the startup of the Service Manager on a node

• Use to learn why any Service Manager for a node failed to start• E.g., if the Service Manager cannot connect to the domain

configuration database after 30 seconds, the Service Manager fails to start

43

If Domain is Running

• Verify if application service started

• If application service did not start, check Logs tab• Tip: Click the Logs tab for the application service to quickly

search for related log events

• Read ERROR messages for cause of error

• Fix error, then disable and enable application service

44

Understanding Log Events

• The Service Manager and application services send log events to the LogManager. The Log Manager generates log events for each service type. To view the log events in the Log Viewer, youmust configure the type of log events you want to search for in the Log Viewer.

• You can view the following log event types in the Log Viewer:• Domain log events. • Repository Service log events. . • Integration Service log events. • Metadata Manager Service log events. • SAP BW Service log events. • Web Services Hub log events.

45

Understanding Log Events

• The Log Viewer categorizes events by service type or domain category based on the type of service you view.• Severity - The severity level for the log event.• Time stamp - Date and time the log event occurred.• Node - Node reporting the event.• Thread - Identification number or name of a thread started by a

Repository Service process or name of a thread started by an Integration Service process

• ProcessID - The process identification number for the Windows or UNIX service process that generated the log event.

• Message code - Troubleshooting Guide• Message – Message text for logged event.

46

Log Events

Example: Integration Service logged event

47

Application Service Log Events

Returns log events for this service only.

Use to search for any log event from domain or any service.

48


49


Display Settingsselect columns to be displayed

50

Managing Application Service Logs

Default log directory

Log Management

51

Domain Audit Reports

52

Integration Service Changes

53

Integration Service Process(es)

• Manages workflow scheduling.

• Locks and reads the workflow.

• Reads the parameter file. • Creates the workflow log. • Runs workflow tasks and

evaluates the conditional links connecting tasks.

• Starts the DTM process or processes to run the session.

• Writes historical run information to the repository.

• Sends post-session email in the event of a DTM failure.

54

Integration Service Load Balancer

• Component of the Integration Service that dispatches tasks to achieve optimal performance and scalability.

• Dispatches the Session, Command, and predefined Event-Wait tasks within the workflow.

• The Load Balancer matches task requirements with resource availability to identify the best node to run a task.

• On GRID uses dispatch mode, Round-robin, Metric-based, Adaptive

55

Integration Service DTM

Data Transformation Manager (DTM)

• Retrieves and validates session information from the repository.

• Performs pushdown optimization

• Manages dynamic partitioning.

• Manages partition groups on a grid.

• Expands the service process variables, session parameters, and mapping variables and parameters.

• Creates the session log.

• Validates source and target code pages.

• Verifies connection object permissions.

• Runs pre-session shell commands, stored procedures, and SQL.

• Sends a request to start worker DTM processes on other nodes when the session is configured to run on a grid.

• Creates and runs mapping, reader, writer, and transformation threads to extract, transform, and load data.

• Runs post-session stored procedures, SQL, and shell commands.

• Sends post-session email.

56

Integration Service DTM

• The Master thread spawns five different thread types:• Mapping• Pre/Post Processing• Reader• Transformation• Writer

Pre-SessionThread

Post-SessionThread

TransformationThread

WriterThread

ReaderThread

DTM MasterThread

MappingThread

57

Troubleshooting : workflow and session issues

58

Important session settings

• Integration service code page

• Debug settings (Test load, verbose log)

• Error tolerance (Stop on error)

• Memory settings

59

Finding Workflow and Session InformationUsing MetaQuery 1.0

• Metaquery is the tool to query against Informatica PowerCenter repository for some of the key properties in session, workflow and mapping objects.

• Saves time to gather information regarding properties set in any of these session or workflow properties across a repository

• GUI based tool.

• Report can be exported into a readable excel sheet or notepad file.

• Download the tool from “my.informatica” support page.

60

Using MetaQuery 1.0

Database Type (Currently oracle and db2)Repository Database User ID and PasswordDatabase Connect String form “//hostname:port;SID=sid”

Question: What sessions have the DTM buffer set to AUTO?

61

Using MetaQuery 1.0

Record selectionor all records

DTM Buffer Size

62

Using MetaQuery 1.0

Notepad

Excel

63

Log files

Check log files for other problems

• Session log

• Workflow log

• Integration service log

64

Tips and Techniques

• Isolate• Is it the reader, writer or the transformation ?• Has the session ever worked before?• If yes, has anything changed since then to cause the

problem?• Is it the only one session failing ?• Is it due to a specific data row/feature ?• Is the issue consistent ?• Change settings one at a time.

65

Tips and Techniques

• Most reader/writer errors are database errors

• Search knowledgebase (my.informatica.com)

• Search the web ( Google, yahoo )

• Use debugger to verify transformation issues

• Try session operation outside of PowerCenter

• Try Export and re-import

66


• Session log, workflow log

• Workflow export from repository manager

• SSCAN output

• Stack trace from core file using pmstack tool• The pmstack utility is a Unix script file that retrieves the

stack trace from a core file or hanging process and creates an output file that can be sent to Informatica support for analysis of the problem.

• Download the tool from “my.informatica” support page.

67

Troubleshooting :Session performance

68

Session environmentNode

Repository

Service ManagerSession 1

Pre-SessionThread

Post-SessionThread


WriterThread

ReaderThread

DTM MasterThread

MappingThread

Session 2

Pre-SessionThread

Post-SessionThread


WriterThread

ReaderThread

DTM MasterThread

MappingThread

69

Understanding Session Thread Statistics

• Many factors affect session performance• Hardware configuration (Network, CPUs, Memory, Disk

I/O, etc,)• Source and Target data types and configuration (database,

flat file, message que, etc.)• Session configuration (mapping strategy, transformations,

session parameters, pre/post session processing).

70

Thread Types

One thread for each partition, if a target exists in the source pipeline.

Writes to targets.

Relational targets use relational writer threads, and file targets use file writer threads.

Writer (Consumer)

One or more transformation threads for each partition.

Processes data according to the transformation logic in the mapping.Transformation

One thread for each partition for each source pipeline.

Reads from sources.

Relational sources use relational reader threads, and file sources use file reader threads.

Reader (Provider)

One thread each to perform pre- and post-session operations.Pre/Post Processing

One thread for each session.

Fetches session and mapping information.

Compiles the mapping.

Cleans up after session execution.

Mapping

71

Session architecture

***** RUN INFO FOR TGT LOAD ORDER GROUP [1], CONCURRENT SET [1] *****Thread [READER_1_1_1] created for [the read stage] of partition point [SQ_ods_invoice_summary_ff] has completed: Total Run Time = [39.875000] secs, Total Idle Time = [18.359375] secs, Busy Percentage = [53.957680].Thread [TRANSF_1_1_1] created for [the transformation stage] of partition point [SQ_ods_invoice_summary_ff] has completed: Total Run Time = [55.046875] secs, Total Idle Time = [0.562500] secs, Busy Percentage = [98.978144].Thread [TRANSF_1_2_1] created for [the transformation stage] of partition point [AGGTRANS] has completed: Total Run Time = [12.187500] secs, Total Idle Time = [2.718756] secs, Busy Percentage = [77.692261].Thread [WRITER_1_*_1] created for [the write stage] of partition point [EDW_INVOICE_SUM] has completed. The total run time was insufficient for any meaningful statistics.

Reader Transformation Transformation Writer

72

PowerCenter Performance Features

• Pipelined transformation-read/write/transform in parallel

• Parallelism at data level by partitioning

• Bulk load support/Native drivers support

• High speed, memory-based binary search for Lookup transformations

• Aggregations, sorting done fully in memory

73

PowerCenter 8 – New concepts

• Pushdown optimization

• Concurrent lookup caching

• Auto memory settings

• Lookup – any row matching, lookup override

• Session on grid

• Repagent caching

• Concurrent workflows

• Bigint Precision of 19

74

Performance Tuning – Iterative process

• Set realistic goals on Performance Tuning

• Establish a baseline before starting Performance Tuning

• Tuning requires a simple, iterative approach• 1) Find the biggest performance bottleneck• 2) Eliminate or reduce it• 3) Go back to step 1

75

Performance Tuning AreasPowerCenter Performance and Tuning Guide • Covers the following areas:

• Identify Bottlenecks

• Optimizing:• Mappings• Sources / Targets• Transformations• Sessions• Grid Deployment• Partitioning

• Use of performance counters

76

#1: Reduce Network traffic and Disk I/O

• Network traffic• Move target database to ETL server• Local disk is faster than network

• Disk I/O• Reduce paging• Reduce I/O contention (spread source, target and cache)

77

#2: Optimizing Mappings

• Extract only what you need

• Filter as soon as possible

• Minimize Error rows

• Use flat files for staging temporary data

• Reducing Disk I/O (Aggregate, Joiner)

• Use Update strategy instead of Update else Insert

• Avoid using Sequence Generators with no. of cached value as 1

78

#3: Optimizing Sessions

• Analyze session to log look for transformation cache memory usage

• Cache Memory Settings – Try ‘Auto’ first • KB article : 20919 – “HOW TO: Improve session performance

using Automatic Memory Settings in PowerCenter 8.1”

• Bulk loading – For Insert with no constraints

• Reduce Error Tracing

• Run concurrent sessions

79

#3: Optimizing Sessions - Session Logs Statistics

Thread [READER_1_1_1] created for [the read stage] of partition point [SQ_ods_invoice_summary_ff] has completed: Total Run Time = [39.875000] secs, Total Idle Time = [18.359375] secs, Busy Percentage = [53.957680].

Thread [TRANSF_1_1_1] created for [the transformation stage] of partition point [SQ_ods_invoice_summary_ff] has completed: Total Run Time = [55.046875] secs, Total Idle Time = [0.562500] secs, Busy Percentage = [98.978144].

Thread [TRANSF_1_2_1] created for [the transformation stage] of partition point [AGGTRANS] has completed: Total Run Time = [12.187500] secs, Total Idle Time = [2.718756] secs, Busy Percentage = [77.692261].

Thread [WRITER_1_*_1] created for [the write stage] of partition point [EDW_INVOICE_SUM] has completed. The total run time was insufficient for any meaningful statistics.

• Busy Percentages can identify potential bottlenecks

• Adjust your mappings to level out the bottlenecks

• Here the transformation is of bottleneck – consider partitioning

• Don’t need 100% utilization but should be high and close together

80

#4: Optimizing Sources/Targets

• Review the database constraints and indexes

• Eliminate database (transaction) logging

• Update database statistics regularly and use cost based optimizer

• Align DB partitioning with session partitions

• Create tables with optimal storage parameters

81

Performance Analyzer 1.0

• The purpose of the Performance Analyzer is: • To Read the mapping XML• To Calculate the required cache and DTM size• To Analyze the amount of memory allocated• To generate a XML, based on which the performance is

analyzed. The analysis is done at two levels Mapping and Session level.

• Mapping level – calculates cache sizes• Session level – calculates DTM buffer

82


Export mapping in xml

•Specify precision•Enter the number of row•to be processed•Enter the nubmer of partitions•Select the “Analyze Mapping”button

83


84

Summary

• Q & A

informatica developer tips for troubleshooting...

Documents