MetaQuery is a tool that can be used to query against PowerCenter repository for some of the key properties in sessions, workflows, mappings, transformations, and permission objects.
MetaQuery requires the user to give the repository database connection information to connect to the database (using JDBC) to retrieve data. The following are some of the features of this tool:
- Supports repository in Oracle, DB2, and SQLServer.
- Generates reports on Consistency check and Optimization check on repositories.
- Displays a list of properties for sessions, workflows, and mappings that can be used for querying purposes, on the left pane (currently, it supports different properties).
- A MetaQuery can be generated on any of the properties selected in the left pane. The report is displayed in the right pane.
- The data can be exported to CSV format.
- Has two-level access privileges.
- It requires a minimum of 4 GB RAM.
- It requires x86 chip set.
- It requires 200 MB for the installation. However, ensure that there is at least 2 GB free space, as the Database is based in file-system and may require higher disk space, depending on the configuration and usage.
- The following browsers are supported :
- Internet Explorer 10
- Internet Explorer 11
- Microsoft Edge
- Mozilla Firefox
- Google Chrome
Supported Informatica Products
- Informatica 9.x
- Informatica 10.x
- Windows 2012 Server (64-bit)
- Windows 7 (64-bit)
- Windows 10 (64-bit)
- Red Hat Enterprise Linux Server release 6 (64-bit)
- Red Hat Enterprise Linux Server release 7 (64-bit)
Downloads are available at tsftp.informatica.com in the folder /updates/Metaquery
For more information, see How to access Informatica TSFTP server to upload and download files
You can login to tsftp.informatica.com using your default credentials. MetaQuery can be downloaded for the following platforms:
- File name for Informatica MetaQuery 10.5 Linux 64-Bit:
- File name for Informatica MetaQuery 10.5 Windows 64-Bit:
How to Install
View the attachment for a detailed installation process for Windows and Linux (See MetaQuery Installation Guide.pdf in the Attachments section below).
You can access Informatica MetaQuery using the URL: http://hostname:portnumber. The default port number is 25000, which can be changed during the installation. For more details on installation, see the Pre-Requisites and Installation Guide.
The default Username and Password to login to Informatica MetaQuery is admin/admin. First time login prompts the user to change the password. Password must contain at least 8 characters, an uppercase, a lowercase, a number, and a special character.
When you log in to Informatica MetaQuery, a default admin user Id is displayed. This Id cannot be deleted. However, you can modify the password.
Types of Users
There are two types of users:
Admin has permissions to:
- Create, modify, or delete another user.
- Add other admin users to the system.
- Create, modify, and delete Informatica repository connections.
- Assign privileges to non-admin users to connect to a repository.
An Admin can control user access to various features. Some features may not be available to the User.
|Add / Modify / Delete user||Yes||No|
|MetaQuery (execute queries)||Yes||Yes (if permission granted by Admin)|
User has permissions to:
- Execute queries for the repository.
The User does not have Edit or Delete options displayed for each repository. They can only connect the repository.
To create users, do the following:
- Click Manage.
- Click Configure Users.
- Click Create.
- In the Configure User dialog-box, enter a User Name.
- Select a User Type.
- Enter a Password (Password must contain atleast 8 characters, an uppercase, a lowercase, a number, and a special character).
- Re-enter the password in Retype Password.
- Enter Email ID.
- Click Next to associate Repository Permissions.
- Select one or more Repositories to provide access to the selected user.
Note: Only users with this privilege can connect and run the queries.
- Click Save.
- In the User Management dialog-box, click Done.
- Click the Modify or Delete buttons to edit or delete an existing user.
Note: The User Name or Email ID cannot be modified.
Adding a Repository
Only the Admin can add a repository.
To add a repository, do the following:
- Click Add.
- In the Add Repository dialog-box, specify the Connection Name, Database Username, Password, and Database Type (Oracle, DB2, or MSSQL).
- Select a Connection Type.
Connection can be of two types; Basic and URL.
By default, the Basic connection type is selected. For Basic connection type, enter the predefined input parameters required for database connection such as, Host name, Port number (Service ID for Oracle and Database for DB2)
- Click Test, to test the repository connection. Note: If the test connection is successful, the Repository name and status (Versioned/Non versioned) are displayed.
- Click Save to save the Repository connection details.
- Click Connect to connect to the Repository.
Note: You can also connect to an existing Repository by clicking theicon next to the listed repository.
Connecting to a Repository
An Admin can connect to a repository. However, if the Admin has assigned privileges for a repository, the User can also connect to a repository.
To connect to a configured repository, do the following:
- In My Repositories, select a repository to connect.
- Clicknext to the listed Repository.
- Thesign indicates that the Repository is connected.
Executing pre-defined Queries
Both the Admin and User can execute queries.
To execute pre-defined queries, do the following:
- Connect to the Repository.
- Each Repository displays the following parameters:
- To get details of the property, select the property and click Generate.
- Select Session > Target Load Type.
- To filter your search, you can choose to run the MetaQuery on a specific folder. Depending on the property, different parameters are displayed. Select the value(s) accordingly.
- Property name and Description are displayed.
- The Query results are displayed.
- Every page displays 10 records. Use pagination to go to the required page.
- Use the filter on individual columns to narrow down search results.
- Use theto show or hide columns that the result contains.
- Use the Export option to download the data in CSV format.
Editing a Repository
The Admin can edit a repository.
- Click onnext to the connected repository.
- Edit the Database Password.
- Click Test to test the connection.
- Click Save.
Deleting a Repository
The Admin can delete a repository.
The Repository is now deleted and cannot be accessed.
Inconsistences in a repository might occur due to the following reasons:
- Non-standard practices in the deployment of objects
- Current bugs within the application
These inconsistences can impact other dependent objects and cause unexpected service shutdowns, conflicts in deployment while running sessions.
The purpose of the Consistency Check is to determine the metadata inconsistencies in an Informatica Repository. The initial Consistency Check determines metadata inconsistencies in the following:
- Source, Target, Mappings, and Mapplets
- Folders and Shortcuts
- Transformations and User-Defined Functions (UDFs)
- Tasks, Workflows, Session configurations, and Schedulers
Running a Consistency Check
Please refer to the attached MetaQuery User Guide for details regarding running a Consistency check.
- Download the consistency check report, unzip and view the consistency_check.pdf.
- The zip file also contains the Excel documents with details of inconsistent data, which helps the Informatica technical support to identify and fix them.
- Send the zip file and the repository backup to Informatica Global Customer Support (GCS) for a quicker resolution.
In case of longer use of a repository without maintenance, it might accumulate a lot of data in the run history tables, deleted versions, and unused objects. This could cause performance delays in accessing the repository while connecting or running the jobs.
The purpose of the Optimization Check is to determine if the repository could be optimized.
Optimization Check determines the performance of the following:
- Task run history table
- The oldest version in the repository
- Unused objects
- Deleted objects
- List of indexes
Running an Optimization Check
Please refer to the attached MetaQuery User Guide for details regarding running an Optimization check.
- Download the Optimization Check Report, unzip and view the Optimization _check.pdf.
- This report contains the count against metrics. The Count indicates the number of objects that could be optimized.
Refer to the following Knowledge Base (KB) article for actions that could be taken against each metric:
Supported Properties in MetaQuery
Some of the properties supported under the available parameters in MetaQuery are as follows [For a comprehensive list, see the Attachments section]:
- Target Load Type
- Stop on Errors
- DTM Buffer Size
- Buffer Block Size
- Enable Test Load
- Enable HA Recovery
- Suspend on Error
- Automatically Recover terminated tasks
- Parameter File Name
- Workflow Log Directory
- SQL Override in SQ
- SQL Override in Lookup
- Source Table
- Target Table
- Owner Name for source
- Source File delimiter
- Target File delimiter
- SQ SQL override
- Lookup SQL override
- SQLs from SQL Transformations
- Permission on Folders
- Permission on Labels
- Permission on Deployment Groups
- Permission on Queries
- Permission on Connection objects
- DB Connection
- FTP Connection
- Loader Connection
- Application connection
- Sessions with Teradata connection
- Database connection variable
- $Source Variable
- $Target Variable
- Occurance of $PMIntegrationServiceName
- Internet Explorer import package issue.
- Feature not supported in Internet Explorer 9.
- Supported in Internet Explorer 10, 11, Microsoft Edge and the latest versions of Google Chrome and Mozilla FireFox.
- Internet Explorer 11 logout session issue.
- Normal logout works smoothly on all browsers.
- Logout on reload has an issue in Internet Explorer 9, 10, 11. However, it works on Informatica Edge.
- Windows 7 and Windows 10, auto-restart issue.
- On uninstalling, the system may restart without prompt.
- Non-English character issues.
- On running a query and exporting the output in a CSV file, the non-English characters may not display in the CSV file.
MetaQuery version 10.2.2 Release Notes
- Session > OnSuccess or OnFailure email configuration
- Session > Insert-Update Strategy for Target
- Session > Delete-Update Strategy for Target
- Workflow > Parameter file at Workflow Run Instance
- Workflow > Workflow Variables
- Transformations > Output Is Deterministic
- Transformations > Output is Repeatable
- Transformation > Column name in PowerCenter Transformations
- Readers/Writers > Sales Force Reader Bulk Query enabled
- Generic > Version comments
New Consistency checks
- Source > Source and Shortcut exists with the same name in the same folder
- Target > Target and Shortcut exists with the same name in the same folder
- Transformation > Transformation and Shortcut exists with the same name in the same folder
- Mapping > Mapping and Shortcut exists with the same name in the same folder
- Task > Attributes are duplicated in the Task Attributes table.
- Task > Session Current Log count attribute has inconsistent data.
- UI Session timeout is made configurable
- UI filters are made applicable for Export data
- Not able to login to UI after a Repository Deletion operation
- Fixed Query Export not working issue due to ‘/’ character in the label
- Generated Health Report PDFs not opening
Upgrade Installer with support for importing existing connections/users configurations.
MetaQuery version 10.5 Release Notes
- Session > DTM Buffer Size
- Workflow > Invalid Tasks
- Workflow > Scheduler
- Generic > More than one Session Config
- Upgraded Jre to OpenJdk Jre 184.108.40.2062.
- Upgraded Tomcat to version 220.127.116.11.
- Export result failing when filters are applied
- Export result showing truncated queries
- Session on Grid Query > added “ENABLED” option
- Tomcat security vulnerability > masked default 404 error
- Query output truncating when output is a single row and column
- Fixes in Queries
- Workflow > Concurrent Workflow: Added workflow run instance type, name and parameter files details
- Session > Session Throughput and Load Time: Modified query to display load details of source, the target that has parameterized connections
- Workflow > Workflow-Session-Mapping-Trans-connection: Modified query logic to retrieve valid results
- Session > Stop on Errors: Modified query logic to retrieve valid results
- Transformation > List of Stored Procedures: Modified query logic to retrieve valid results
- Transformation > SQ SQL Override: Modified Oracle query to resolve the JDBC error
- Session > Session Run Status: Added functionality to filter the output by date
- Session > Truncate Table Option: Modified query logic to retrieve valid results
- Workflow > Commands in Command Task: Increased the limit on the length of characters for the column COMMAND
- Support for importing existing connections/user configurations.
- Support for upgrade from MetaQuery version 5.0 onwards.