Cognos Analytics

 View Only

  • 1.  Retrieve data sources/connections for packages and data modules

    Posted 3 days ago

    Hi,

    would anyone have a SQL to retrieve the list of packages and data modules along with the data sources they are using from the Cognos content store (ver 11.1.7)?

    I tried the free Motio which appears to have this for packages - but for a lot of them the data source list contains only an error like: Error occurred while loading data sources: RSV-SRV-0002 The document contains an error at line '1', column ' 137'.

    For data modules, it does not seem to provide any connection information.

    Thanks



    ------------------------------
    Tomáš Polakovič
    ------------------------------


  • 2.  RE: Retrieve data sources/connections for packages and data modules

    Posted 13 hours ago
    Edited by Thiago Teixeira 10 hours ago

    Hi Tomáš ! I don´t have CM on 11.1.7 on my Lab to test it, but this SQL could be a good starting point :

    ------

    SELECT 
        obj_names.name AS object_name,
        obj.search_path,
        CASE 
            WHEN obj.objectclass = 8 THEN 'Package'
            WHEN obj.objectclass = 3036 THEN 'Data Module'
            ELSE 'Other'
        END AS object_type,
        ds.name AS data_source_name,
        ds.search_path AS data_source_path
    FROM 
        cmobjects obj
    JOIN 
        cmobjectnames obj_names ON obj.id = obj_names.object_id
    LEFT JOIN 
        cmobjprops26 ref26 ON obj.id = ref26.id
    LEFT JOIN 
        cmobjects ds ON ref26.refobjid = ds.id
    WHERE 
        obj.objectclass IN (8, 3036) -- 8 = Package, 3036 = Data Module
    ORDER BY 
        object_type, object_name;

    ----

    cmobjects: Main table containing all Cognos content objects.
    objectclass = 8: Represents Packages.
    objectclass = 3036: Represents Data Modules.
    cmobjprops26: Table used for reference properties (i.e., links to data sources).
    cmobjectnames: Provides readable names for objects.




    ------------------------------
    Thiago Teixeira
    IBM Champion
    CTO - RCI Analytics Intelligence
    ------------------------------

    Original Message


  • 3.  RE: Retrieve data sources/connections for packages and data modules

    Posted 8 hours ago

    Hi, 

    thanks for the attempt, but most of those objects are called differently (ID ~ CMID) or don't exist at all (search_path). Class 8 is not package, but namespaceFolder‬, 3036 does not exist.

    Was this generated with LLM? I tried that path and it lead me nowhere.

    Thanks

    Tomas



    ------------------------------
    Tomáš Polakovič
    ------------------------------

    Original Message


  • 4.  RE: Retrieve data sources/connections for packages and data modules

    Posted 7 hours ago

    I'm setting up a lab with the same Cognos version to apply the necessary SQL updates and provide better support. Which Content Store database are you using-DB2, SQL, or Oracle?



    ------------------------------
    Thiago Teixeira
    IBM Champion
    CTO - RCI Analytics Intelligence
    ------------------------------

    Original Message


  • 5.  RE: Retrieve data sources/connections for packages and data modules

    Posted 6 hours ago

    DB2.

    Thank you



    ------------------------------
    Tomáš Polakovič
    ------------------------------

    Original Message


  • 6.  RE: Retrieve data sources/connections for packages and data modules

    Posted 6 hours ago

    Follow the SQL adjusted for CM on version 11.

    SELECT

    srcNames.name AS source_object_name,

    src.classid AS SourceClassID,

    refNames.name AS referenced_object_name,

    ref.classid AS ReferenceClassID

    FROM

    CMOBJECTS src

    JOIN CMOBJNAMES srcNames

    ON src.cmid = srcNames.cmid

    LEFT JOIN CMREFORD1 r

    ON src.cmid = r.cmid

    LEFT JOIN CMOBJECTS ref

    ON r.refcmid = ref.cmid

    LEFT JOIN CMOBJNAMES refNames

    ON ref.cmid = refNames.cmid

    WHERE

    r.refcmid IS NOT NULL AND refNames.ISDEFAULT=1 AND srcNames.ISDEFAULT=1

    ORDER BY

    source_object_name;

    Simply review the results, then filter by the SOURCECLASSID and REFERENCECLASSID that correspond to your Packages and Data Modules. 
    You can identify those class IDs by looking at the object names.
    Let me know if that helps.



    ------------------------------
    Thiago Teixeira
    IBM Champion
    CTO - RCI Analytics Intelligence
    ------------------------------

    Original Message