如何在Informatica中查找文件夹内近6个月未运行的映射？

Sure thing! There are solid, practical ways to track down mappings in an Informatica folder that haven’t been executed in the last 6 months. I’ll walk you through the most common approaches used in real-world environments, depending on your comfort with GUIs, SQL, or command-line tools:

This is the most user-friendly method if you prefer point-and-click operations:

• Log into the Informatica Administrator Console and navigate to your target folder.
• Switch to the Monitoring tab, then select either the Session Logs or Task Runs view.
• First, export two lists:
  1. From the Navigator pane, right-click your folder and export all mappings to a CSV file (this gives you the full list of mappings in the folder).
  2. In the Monitoring view, set a filter for run times within the last 6 months, then export the list of sessions that ran. Each session is tied to a single mapping, so you can extract the associated mapping names from this export.
• Use a tool like Excel to compare the two lists: any mapping in the full list that doesn’t appear in the "recent runs" list is one that hasn’t been executed in 6 months (including mappings that never ran at all).

If you have access to the Informatica repository database (typically Oracle, SQL Server, or PostgreSQL), this is the fastest, most scalable method. The repository stores all metadata about mappings and their run history in relational tables.

Here’s an example SQL query (adjusted for Oracle; tweak date functions for other databases):

SELECT 
    m.MAPPING_NAME,
    m.FOLDER_NAME
FROM 
    REP_ALL_MAPPINGS m
LEFT JOIN 
    REP_TASK_INSTANCE ti ON m.MAPPING_ID = ti.MAPPING_ID
LEFT JOIN 
    REP_SESSION_RUN sr ON ti.TASK_INSTANCE_ID = sr.TASK_INSTANCE_ID
WHERE 
    m.FOLDER_NAME = 'Your_Target_Folder' -- Replace with your folder name
    AND (sr.END_TIME IS NULL OR sr.END_TIME < ADD_MONTHS(SYSDATE, -6))
GROUP BY 
    m.MAPPING_NAME, m.FOLDER_NAME
ORDER BY 
    m.MAPPING_NAME;

• What this does: It pulls all mappings from your target folder, then checks if they have any session runs in the last 6 months. Mappings with no run history (sr.END_TIME IS NULL) or runs older than 6 months are included in the results.
• For SQL Server, replace ADD_MONTHS(SYSDATE, -6) with DATEADD(month, -6, GETDATE()).

If you need to automate this process or work in a headless environment, use Informatica’s built-in pmcmd utility:

1. Export the full list of mappings in your folder:

pmcmd listmappings -d <Your_Domain> -u <Your_Username> -p <Your_Password> -f <Target_Folder> > all_mappings.txt

2. Export session run history for the last 6 months (you’ll need to define the start/end dates):

pmcmd getsessionhistory -d <Your_Domain> -u <Your_Username> -p <Your_Password> -f <Target_Folder> -starttime "01/01/2024 00:00:00" -endtime "06/30/2024 23:59:59" > recent_runs.txt

3. Use a script (Python, Shell, etc.) to parse both files, extract mapping names from the recent runs, and find the differences with the full mapping list.

For custom workflows or integrating into your internal tools, use the Informatica Cloud REST API (or PowerCenter REST API for on-prem):

• Call the GET /api/v2/mappings endpoint to fetch all mappings in your target folder.
• Call the GET /api/v2/session-runs endpoint with a filter for run times in the last 6 months.
• Match mappings to their session runs using the mappingId field, then filter out any mappings that have no recent runs.

• Permissions: Ensure you have the right access levels (e.g., monitoring access in the console, read access to the repository database, execute permissions for pmcmd).
• Never-run mappings: Don’t forget that mappings that have never been executed also qualify as "not run in 6 months"—all the methods above include these.
• Session-Mapping Links: Most sessions are tied to one mapping, but if you have complex workflows (like worklets with multiple mappings), you may need to adjust queries/scripts to account for those.

内容的提问来源于stack exchange，提问作者PANDIA LAKSHMANAN