Informatica Session Failures and Recovering Sessions

Session Failures and Recovering Sessions Two types of errors occurs in the server - Non-Fatal - Fatal (a) Non-Fatal Errors It is an error that does not force the session to stop on its first occurrence. Establish the error threshold in the session property sheet with the stop on option. When you enable this option, the server counts Non-Fatal errors that occur in the reader, writer and transformations. Reader errors can include alignment errors while running a session in Unicode mode. Writer errors can include key constraint violations, loading NULL into the NOT-NULL field and database errors. Transformation errors can include conversion errors and any condition set up as an ERROR,. Such as NULL Input. Fatal Errors This occurs when the server can not access the source, target or repository. This can include loss of connection or target database errors, such as lack of database space to load data. If the session uses normalizer (or) sequence generator transformations, the server can not update the sequence values in the repository, and a fatal error occurs. Others Usages of ABORT function in mapping logic, to abort a session when the server encounters a transformation error. Stopping the server using pmcmd (or) Server Manager. Performing Recovery - When the server starts a recovery session, it reads the OPB_SRVR_RECOVERY table and notes the rowid of the last row commited to the target database. The server then reads all sources again and starts processing from the next rowid. - By default, perform recovery is disabled in setup. Hence it won’t make entries in OPB_SRVR_RECOVERY table. - The recovery session moves through the states of normal session schedule, waiting to run, Initializing, running, completed and failed. If the initial recovery fails, you can run recovery as many times. - The normal reject loading process can also be done in session recovery process. - The performance of recovery might be low, if o Mapping contain mapping variables o Commit interval is high. Un recoverable Sessions Under certain circumstances, when a session does not complete, you need to truncate the target and run the session from the beginning. Commit Intervals A commit interval is the interval at which the server commits data to relational targets during a session. (a) Target based commit - Server commits data based on the no of target rows and the key constraints on the target table. The commit point also depends on the buffer block size and the commit interval. - During a session, the server continues to fill the writer buffer, after it reaches the commit interval. When the buffer block is full, the Informatica server issues a commit command. As a result, the amount of data committed at the commit point generally exceeds the commit interval. - The server commits data to each target based on primary –foreign key constraints. (b) Source based commit - Server commits data based on the number of source rows. The commit point is the commit interval you configure in the session properties. - During a session, the server commits data to the target based on the number of rows from an active source in a single pipeline. The rows are referred to as source rows. - A pipeline consists of a source qualifier and all the transformations and targets that receive data from source qualifier. - Although the Filter, Router and Update Strategy transformations are active transformations, the server does not use them as active sources in a source based commit session. - When a server runs a session, it identifies the active source for each pipeline in the mapping. The server generates a commit row from the active source at every commit interval. - When each target in the pipeline receives the commit rows the server performs the commit. Reject Loading During a session, the server creates a reject file for each target instance in the mapping. If the writer of the target rejects data, the server writers the rejected row into the reject file. You can correct those rejected data and re-load them to relational targets, using the reject loading utility. (You cannot load rejected data into a flat file target) Each time, you run a session, the server appends a rejected data to the reject file. Locating the BadFiles $PMBadFileDir Filename.bad When you run a partitioned session, the server creates a separate reject file for each partition. Reading Rejected data Ex: 3,D,1,D,D,0,D,1094345609,D,0,0.00 To help us in finding the reason for rejecting, there are two main things. (a) Row indicator Row indicator tells the writer, what to do with the row of wrong data. Row indicator Meaning Rejected By 0 Insert Writer or target 1 Update Writer or target 2 Delete Writer or target 3 Reject Writer If a row indicator is 3, the writer rejected the row because an update strategy expression marked it for reject. (b) Column indicator Column indicator is followed by the first column of data, and another column indicator. They appears after every column of data and define the type of data preceding it Column Indicator Meaning Writer Treats as D Valid Data Good Data. The target accepts it unless a database error occurs, such as finding duplicate key. O Overflow Bad Data. N Null Bad Data. T Truncated Bad Data NOTE NULL columns appear in the reject file with commas marking their column. Correcting Reject File Use the reject file and the session log to determine the cause for rejected data. Keep in mind that correcting the reject file does not necessarily correct the source of the reject. Correct the mapping and target database to eliminate some of the rejected data when you run the session again. Trying to correct target rejected rows before correcting writer rejected rows is not recommended since they may contain misleading column indicator. For example, a series of “N” indicator might lead you to believe the target database does not accept NULL values, so you decide to change those NULL values to Zero. However, if those rows also had a 3 in row indicator. Column, the row was rejected b the writer because of an update strategy expression, not because of a target database restriction. If you try to load the corrected file to target, the writer will again reject those rows, and they will contain inaccurate 0 values, in place of NULL values. Why writer can reject ? - Data overflowed column constraints - An update strategy expression Why target database can Reject ? - Data contains a NULL column - Database errors, such as key violations Steps for loading reject file: - After correcting the rejected data, rename the rejected file to reject_file.in - The rejloader used the data movement mode configured for the server. It also used the code page of server/OS. Hence do not change the above, in middle of the reject loading - Use the reject loader utility Pmrejldr pmserver.cfg [folder name] [session name] Other points The server does not perform the following option, when using reject loader (a) Source base commit (b) Constraint based loading (c) Truncated target table (d) FTP targets (e) External Loading Multiple reject loaders You can run the session several times and correct rejected data from the several session at once. You can correct and load all of the reject files at once, or work on one or two reject files, load then and work on the other at a later time. External Loading You can configure a session to use Sybase IQ, Teradata and Oracle external loaders to load session target files into the respective databases. The External Loader option can increase session performance since these databases can load information directly from files faster than they can the SQL commands to insert the same data into the database. Method: When a session used External loader, the session creates a control file and target flat file. The control file contains information about the target flat file, such as data format and loading instruction for the External Loader. The control file has an extension of “*.ctl “ and you can view the file in $PmtargetFilesDir. For using an External Loader: The following must be done: - configure an external loader connection in the server manager - Configure the session to write to a target flat file local to the server. - Choose an external loader connection for each target file in session property sheet. Issues with External Loader: - Disable constraints - Performance issues o Increase commit intervals o Turn off database logging - Code page requirements - The server can use multiple External Loader within one session (Ex: you are having a session with the two target files. One with Oracle External Loader and another with Sybase External Loader) Other Information: - The External Loader performance depends upon the platform of the server - The server loads data at different stages of the session - The serve writes External Loader initialization and completing messaging in the session log. However, details about EL performance, it is generated at EL log, which is getting stored as same target directory. - If the session contains errors, the server continues the EL process. If the session fails, the server loads partial target data using EL. - The EL creates a reject file for data rejected by the database. The reject file has an extension of “*.ldr” reject. - The EL saves the reject file in the target file directory - You can load corrected data from the file, using database reject loader, and not through Informatica reject load utility (For EL reject file only) Configuring EL in session - In the server manager, open the session property sheet - Select File target, and then click flat file options Caches - server creates index and data caches in memory for aggregator ,rank ,joiner and Lookup transformation in a mapping. - Server stores key values in index caches and output values in data caches : if the server requires more memory ,it stores overflow values in cache files . - When the session completes, the server releases caches memory, and in most circumstances, it deletes the caches files . - Caches Storage overflow : - releases caches memory, and in most circumstances, it deletes the caches files . Caches Storage overflow : Transformation index cache data cache Aggregator stores group values stores calculations As configured in the based on Group-by ports Group-by ports. Rank stores group values as stores ranking information Configured in the Group-by based on Group-by ports . Joiner stores index values for stores master source rows . The master source table As configured in Joiner condition. Lookup stores Lookup condition stores lookup data that’s Information. Not stored in the index cache. Determining cache requirements To calculate the cache size, you need to consider column and row requirements as well as processing overhead. - server requires processing overhead to cache data and index information. Column overhead includes a null indicator, and row overhead can include row to key information. Steps: - first, add the total column size in the cache to the row overhead. - Multiply the result by the no of groups (or) rows in the cache this gives the minimum cache requirements . - For maximum requirements, multiply min requirements by 2. Location: -by default , the server stores the index and data files in the directory $PMCacheDir. -the server names the index files PMAGG*.idx and data files PMAGG*.dat. if the size exceeds 2GB,you may find multiple index and data files in the directory .The server appends a number to the end of filename(PMAGG*.id*1,id*2,etc). Aggregator Caches - when server runs a session with an aggregator transformation, it stores data in memory until it completes the aggregation. - when you partition a source, the server creates one memory cache and one disk cache and one and disk cache for each partition .It routes data from one partition to another based on group key values of the transformation. - server uses memory to process an aggregator transformation with sort ports. It doesn’t use cache memory .you don’t need to configure the cache memory, that use sorted ports. Index cache: #Groups ((å column size) + 7) Aggregate data cache: #Groups ((å column size) + 7) Rank Cache - when the server runs a session with a Rank transformation, it compares an input row with rows with rows in data cache. If the input row out-ranks a stored row,the Informatica server replaces the stored row with the input row. - If the rank transformation is configured to rank across multiple groups, the server ranks incrementally for each group it finds . Index Cache : #Groups ((å column size) + 7) Rank Data Cache: #Group [(#Ranks * (å column size + 10)) + 20] Joiner Cache: - When server runs a session with joiner transformation, it reads all rows from the master source and builds memory caches based on the master rows. - After building these caches, the server reads rows from the detail source and performs the joins - Server creates the Index cache as it reads the master source into the data cache. The server uses the Index cache to test the join condition. When it finds a match, it retrieves rows values from the data cache. - To improve joiner performance, the server aligns all data for joiner cache or an eight byte boundary. Index Cache : #Master rows [(å column size) + 16) Joiner Data Cache: #Master row [(å column size) + 8] Lookup cache: - When server runs a lookup transformation, the server builds a cache in memory, when it process the first row of data in the transformation. - Server builds the cache and queries it for the each row that enters the transformation. - If you partition the source pipeline, the server allocates the configured amount of memory for each partition. If two lookup transformations share the cache, the server does not allocate additional memory for the second lookup transformation. - The server creates index and data cache files in the lookup cache drectory and used the server code page to create the files. Index Cache : #Rows in lookup table [(å column size) + 16) Lookup Data Cache: #Rows in lookup table [(å column size) + 8] Transformations A transformation is a repository object that generates, modifies or passes data. (a) Active Transformation: a. Can change the number of rows, that passes through it (Filter, Normalizer, Rank ..) (b) Passive Transformation: a. Does not change the no of rows that passes through it (Expression, lookup ..) NOTE: - Transformations can be connected to the data flow or they can be unconnected - An unconnected transformation is not connected to other transformation in the mapping. It is called with in another transformation and returns a value to that transformation Reusable Transformations: When you are using reusable transformation to a mapping, the definition of transformation exists outside the mapping while an instance appears with mapping. All the changes you are making in transformation will immediately reflect in instances. You can create reusable transformation by two methods: (a) Designing in transformation developer (b) Promoting a standard transformation Change that reflects in mappings are like expressions. If port name etc. are changes they won’t reflect. Handling High-Precision Data: - Server process decimal values as doubles or decimals. - When you create a session, you choose to enable the decimal data type or let the server process the data as double (Precision of 15) Example: - You may have a mapping with decimal (20,0) that passes through. The value may be 40012030304957666903. If you enable decimal arithmetic, the server passes the number as it is. If you do not enable decimal arithmetic, the server passes 4.00120303049577 X 1019. If you want to process a decimal value with a precision greater than 28 digits, the server automatically treats as a double value. Mapplets When the server runs a session using a mapplets, it expands the mapplets. The server then runs the session as it would any other sessions, passing data through each transformations in the mapplet. If you use a reusable transformation in a mapplet, changes to these can invalidate the mapplet and every mapping using the mapplet. You can create a non-reusable instance of a reusable transformation. Mapplet Objects: (a) Input transformation (b) Source qualifier (c) Transformations, as you need (d) Output transformation Mapplet Won’t Support: - Joiner - Normalizer - Pre/Post session stored procedure - Target definitions - XML source definitions Types of Mapplets: (a) Active Mapplets - Contains one or more active transformations (b) Passive Mapplets - Contains only passive transformation Copied mapplets are not an instance of original mapplets. If you make changes to the original, the copy does not inherit your changes You can use a single mapplet, even more than once on a mapping. Ports Default value for I/P port - NULL Default value for O/P port - ERROR Default value for variables - Does not support default values Session Parameters This parameter represent values you might want to change between sessions, such as DB Connection or source file. We can use session parameter in a session property sheet, then define the parameters in a session parameter file. The user defined session parameter are: (a) DB Connection (b) Source File directory (c) Target file directory (d) Reject file directory Description: Use session parameter to make sessions more flexible. For example, you have the same type of transactional data written to two different databases, and you use the database connections TransDB1 and TransDB2 to connect to the databases. You want to use the same mapping for both tables. Instead of creating two sessions for the same mapping, you can create a database connection parameter, like $DBConnectionSource, and use it as the source database connection for the session. When you create a parameter file for the session, you set $DBConnectionSource to TransDB1 and run the session. After it completes set the value to TransDB2 and run the session again. NOTE: You can use several parameter together to make session management easier. Session parameters do not have default value, when the server can not find a value for a session parameter, it fails to initialize the session. Session Parameter File - A parameter file is created by text editor. - In that, we can specify the folder and session name, then list the parameters and variables used in the session and assign each value. - Save the parameter file in any directory, load to the server - We can define following values in a parameter o Mapping parameter o Mapping variables o Session parameters - You can include parameter and variable information for more than one session in a single parameter file by creating separate sections, for each session with in the parameter file. - You can override the parameter file for sessions contained in a batch by using a batch parameter file. A batch parameter file has the same format as a session parameter file Locale Informatica server can transform character data in two modes (a) ASCII a. Default one b. Passes 7 byte, US-ASCII character data (b) UNICODE a. Passes 8 bytes, multi byte character data b. It uses 2 bytes for each character to move data and performs additional checks at session level, to ensure data integrity. Code pages contains the encoding to specify characters in a set of one or more languages. We can select a code page, based on the type of character data in the mappings. Compatibility between code pages is essential for accurate data movement. The various code page components are - Operating system Locale settings - Operating system code page - Informatica server data movement mode - Informatica server code page - Informatica repository code page Locale (a) System Locale - System Default (b) User locale - setting for date, time, display (c) Input locale Mapping Parameter and Variables These represent values in mappings/mapplets. If we declare mapping parameters and variables in a mapping, you can reuse a mapping by altering the parameter and variable values of the mappings in the session. This can reduce the overhead of creating multiple mappings when only certain attributes of mapping needs to be changed. When you want to use the same value for a mapping parameter each time you run the session. Unlike a mapping parameter, a mapping variable represent a value that can change through the session. The server saves the value of a mapping variable to the repository at the end of each successful run and used that value the next time you run the session. Mapping objects: Source, Target, Transformation, Cubes, Dimension Debugger We can run the Debugger in two situations (a) Before Session: After saving mapping, we can run some initial tests. (b) After Session: real Debugging process Metadata Reporter: - Web based application that allows to run reports against repository metadata - Reports including executed sessions, lookup table dependencies, mappings and source/target schemas. Repository Types of Repository (a) Global Repository a. This is the hub of the domain use the GR to store common objects that multiple developers can use through shortcuts. These may include operational or application source definitions, reusable transformations, mapplets and mappings (b) Local Repository a. A Local Repository is with in a domain that is not the global repository. Use4 the Local Repository for development. (c) Standard Repository a. A repository that functions individually, unrelated and unconnected to other repository NOTE: - Once you create a global repository, you can not change it to a local repository - However, you can promote the local to global repository Server Concepts The Informatica server used three system resources (a) CPU (b) Shared Memory (c) Buffer Memory Informatica server uses shared memory, buffer memory and cache memory for session information and to move data between session threads. LM Shared Memory Load Manager uses both process and shared memory. The LM keeps the information server list of sessions and batches, and the schedule queue in process memory. Once a session starts, the LM uses shared memory to store session details for the duration of the session run or session schedule. This shared memory appears as the configurable parameter (LMSharedMemory) and the server allots 2,000,000 bytes as default. This allows you to schedule or run approximately 10 sessions at one time. DTM Buffer Memory The DTM process allocates buffer memory to the session based on the DTM buffer poll size settings, in session properties. By default, it allocates 12,000,000 bytes of memory to the session. DTM divides memory into buffer blocks as configured in the buffer block size settings. (Default: 64,000 bytes per block) Running a Session The following tasks are being done during a session 1. LM locks the session and read session properties 2. LM reads parameter file 3. LM expands server/session variables and parameters 4. LM verifies permission and privileges 5. LM validates source and target code page 6. LM creates session log file 7. LM creates DTM process 8. DTM process allocates DTM process memory 9. DTM initializes the session and fetches mapping 10. DTM executes pre-session commands and procedures 11. DTM creates reader, writer, transformation threads for each pipeline 12. DTM executes post-session commands and procedures 13. DTM writes historical incremental aggregation/lookup to repository 14. LM sends post-session emails Stopping and aborting a session - If the session you want to stop is a part of batch, you must stop the batch - If the batch is part of nested batch, stop the outermost batch - When you issue the stop command, the server stops reading data. It continues processing and writing data and committing data to targets - If the server cannot finish processing and committing data, you can issue the ABORT command. It is similar to stop command, except it has a 60 second timeout. If the server cannot finish processing and committing data within 60 seconds, it kills the DTM process and terminates the session. Recovery: - After a session being stopped/aborted, the session results can be recovered. When the recovery is performed, the session continues from the point at which it stopped. - If you do not recover the session, the server runs the entire session the next time. - Hence, after stopping/aborting, you may need to manually delete targets before the session runs again. NOTE: ABORT command and ABORT function, both are different. When can a Session Fail - Server cannot allocate enough system resources - Session exceeds the maximum no of sessions the server can run concurrently - Server cannot obtain an execute lock for the session (the session is already locked) - Server unable to execute post-session shell commands or post-load stored procedures - Server encounters database errors - Server encounter Transformation row errors (Ex: NULL value in non-null fields) - Network related errors When Pre/Post Shell Commands are useful - To delete a reject file - To archive target files before session begins Session Performance - Minimum log (Terse) - Partitioning source data. - Performing ETL for each partition, in parallel. (For this, multiple CPUs are needed) - Adding indexes. - Changing commit Level. - Using Filter trans to remove unwanted data movement. - Increasing buffer memory, when large volume of data. - Multiple lookups can reduce the performance. Verify the largest lookup table and tune the expressions. - In session level, the causes are small cache size, low buffer memory and small commit interval. - At system level, o WIN NT/2000-U the task manager. o UNIX: VMSTART, IOSTART. Hierarchy of optimization - Target. - Source. - Mapping - Session. - System. Optimizing Target Databases: - Drop indexes /constraints - Increase checkpoint intervals. - Use bulk loading /external loading. - Turn off recovery. - Increase database network packet size. Source level - Optimize the query (using group by, group by). - Use conditional filters. - Connect to RDBMS using IPC protocol. Mapping - Optimize data type conversions. - Eliminate transformation errors. - Optimize transformations/ expressions. Session: - concurrent batches. - Partition sessions. - Reduce error tracing. - Remove staging area. - Tune session parameters. System: - improve network speed. - Use multiple preservers on separate systems. - Reduce paging. Session Process Info server uses both process memory and system shared memory to perform ETL process. It runs as a daemon on UNIX and as a service on WIN NT. The following processes are used to run a session: (a) LOAD manager process: - starts a session · creates DTM process, which creates the session. (b) DTM process: - creates threads to initialize the session - read, write and transform data. - handle pre/post session opertions. Load manager processes: - manages session/batch scheduling. - Locks session. - Reads parameter file. - Expands server/session variables, parameters . - Verifies permissions/privileges. - Creates session log file. DTM process: The primary purpose of the DTM is to create and manage threads that carry out the session tasks. The DTM allocates process memory for the session and divides it into buffers. This is known as buffer memory. The default memory allocation is 12,000,000 bytes .it creates the main thread, which is called master thread .this manages all other threads. Various threads functions Master thread- handles stop and abort requests from load manager. Mapping thread- one thread for each session. Fetches session and mapping information. Compiles mapping. Cleans up after execution. Reader thread- one thread for each partition. Relational sources uses relational threads and Flat files use file threads. Writer thread- one thread for each partition writes to target. Transformation thread- one or more transformation for each partition. Note: When you run a session, the threads for a partitioned source execute concurrently. The threads use buffers to move/transform data.