MetaQuery 10.2.2

Version 26

    Introduction

    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.

     

    Functionality

    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.

     

    Prerequisites

    • 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

     

    Supported Platforms

    • Windows 2008 Server (32-bit and 64-bit)
    • Windows 2012 Server (64-bit)
    • Windows 7 (64-bit)
    • Windows 10 (64-bit)
    • Red Hat Enterprise Linux Server release 6 (32-bit and 64-bit)
    • Red Hat Enterprise Linux Server release 7 (32-bit and 64-bit)

     

    Download

    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 (KB 497394)

     

    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.2.2 Linux 32-Bit:
      Metaquery10.2.2_Lin32.tar.gz

     

    • File name for Informatica MetaQuery 10.2.2 Linux 64-Bit:
      Metaquery10.2.2_Lin64.tar.gz

     

    • File name for Informatica MetaQuery 10.2.2 Windows 32-Bit:
      Metaquery10.2.2_Win32.zip

     

    • File name for Informatica MetaQuery 10.2.2 Windows 64-Bit:
      Metaquery10.2.2_Win64.zip

    How to Install

    View the attachment for detailed installation process for Windows and Linux (See MetaQuery Installation Guide.pdf in the Attachments section below).

    Accessing MetaQuery

    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.

     

    Login Credentials

     

    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.

    Login Screen 1.PNG

     

    Configuring Users

     

    When you login 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.

    FeatureAdminUser
    Add / Modify / Delete userYesNo
    Manage RepositoriesYesNo
    MetaQuery (execute queries)YesYes (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.

    Normal User rights.jpg

     

    Creating Users

    To create users, do the following:

    1. Click Manage manage icon.png.
    2. Click Configure Users.
    3. Click Create.
    4. In the Configure User dialog-box, enter a User Name.
    5. Select a User Type.
    6. Enter a Password (Password must contain atleast 8 characters, an uppercase, a lowercase, a number, and a special character).
    7. Re-enter the password in Retype Password.
    8. Enter Email ID.

      Configure User 1.PNG
    9. Click Next to associate Repository Permissions.
    10. Select one or more Repositories to provide access to the selected user.
      Note: Only users with this privilege can connect and run the queries.

      Configure User 2.PNG
    11. Click Save.
    12. In the User Management dialog-box, click Done.
    13. 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:

    1. Click Add add icon.png.
    2. In the Add Repository dialog-box, specify the Connection Name, Database Username, Password, and Database Type (Oracle, DB2, or MSSQL).
    3. 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)

      Add Repository.PNG
    4. Click Test, to test the repository connection. Note: If the test connection is successful, the Repository name and status (Versioned/Non versioned) are displayed.
    5. Click Save to save the Repository connection details.
    6. Click Connect to connect to the Repository.
      Note: You can also connect to an existing Repository by clicking the connect.pngicon 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:

    1. In My Repositories, select a repository to connect.
    2. Click connect.png next to the listed Repository.
    3. The green connect.pngsign 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:

    1. Connect to the Repository.
    2. Each Repository displays the following parameters:
      • Session
      • Connection
      • Variable
      • Workflow
      • Mapping
      • Transformation
      • Permissions
      • Generic

        Repository Parameters.PNG

    3. To get details of the property, select the property and click Generate.

     

    Example

    • 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.

      Session Target Load Type.PNG
    • Property name and Description are displayed.
    • The Query results are displayed.

      Query Result.PNG
    • Every page displays 10 records. Use pagination to go to the required page.
    • Use the filter on individual columns to narrow down search results.
      Filter.PNG
    • Use the dot dot dot.pngto 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.

    1. Click on dot dot dot.pngnext to the connected repository.
    2. Click Edit Repository.PNG.
    3. Edit the Database Password.
    4. Click Test to test the connection.
    5. Click Save.

     

    Deleting a Repository

    The Admin can delete a repository.

     

    1. Click on dot dot dot.pngnext to the connected repository.
    2. Click Delete Repository.PNG.

     

    The Repository is now deleted and cannot be accessed.

     

    Consistency Check

    Inconsistences in a repository might occur due to the following reasons:

    • Non-standard practices in 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.

     

    Interpreting Results

    • 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.

     

    Optimization Check

    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
    • 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.

     

    Interpreting results

    • Download the Optimization Check Report, unzip and view the Optimization _check.pdf.
    • This report contains 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]:

    Session
    1. Target Load Type
    2. Stop on Errors
    3. DTM Buffer Size
    4. Buffer Block Size
    5. Enable Test Load

     

    Workflow
    1. Enable HA Recovery
    2. Suspend on Error
    3. Automatically Recover terminated tasks
    4. Parameter File Name
    5. Workflow Log Directory

     

    Mapping
    1. SQL Override in SQ
    2. SQL Override in Lookup
    3. Source Table
    4. Target Table
    5. Owner Name for source

     

    Transformation
    1. Source File delimiter
    2. Target File delimiter
    3. SQ SQL override
    4. Lookup SQL override
    5. SQLs from SQL Transformations

     

    Permissions
    1. Permission on Folders
    2. Permission on Labels
    3. Permission on Deployment Groups
    4. Permission on Queries
    5. Permission on Connection objects

     

    Connection
    1. DB Connection
    2. FTP Connection
    3. Loader Connection
    4. Application connection
    5. Sessions with Teradata connection

     

    Variable
    1. Database connection variable
    2. $Source Variable
    3. $Target Variable
    4. Occurance of $PMIntegrationServiceName

     

    Known Limitations

    • Internet Explorer import package issue.
      • Feature not supported in Internet Explorer 9.
      • Supported in Internet Explorer 10, 11, Microsoft Edge and latest versions of Google Chrome and Mozilla FireFox.
    • Internet Explorer 11 logout session issue.
      • Normal logout works smooth 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, 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

     

    New Metaqueries

      • 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 Task Attributes table.
      • Task > Session Current Log count attribute has inconsistent data.

     

    Enhancements

      1. UI Session timeout is made configurable
      2. UI filters are made applicable for Export data

     

    Bug Fixes

      1. Not able to login to UI after a Repository Deletion operation
      2. Fixed Query Export not working issue due to ‘/’ character in the label
      3. Generated Health Report PDFs not opening

     

    Upgrade Installer with support for importing existing connections/users configurations.