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č
------------------------------

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:
Sent: Fri March 07, 2025 10:35 AM
From: Tomáš Polakovič
Subject: Retrieve data sources/connections for packages and data modules

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č
------------------------------

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

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:
Sent: Fri March 07, 2025 10:35 AM
From: Tomáš Polakovič
Subject: Retrieve data sources/connections for packages and data modules

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č
------------------------------

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?

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

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:
Sent: Fri March 07, 2025 10:35 AM
From: Tomáš Polakovič
Subject: Retrieve data sources/connections for packages and data modules

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č
------------------------------

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?

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

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:
Sent: Fri March 07, 2025 10:35 AM
From: Tomáš Polakovič
Subject: Retrieve data sources/connections for packages and data modules

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č
------------------------------

Follow the SQL adjusted for CM on version 11.

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.

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?

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

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:
Sent: Fri March 07, 2025 10:35 AM
From: Tomáš Polakovič
Subject: Retrieve data sources/connections for packages and data modules

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č
------------------------------

Thanks...

I was able to find the schema used in the data modules, but not the data source. That is, my data source name was not present in either  source_object_name or referenced_object_name, but I could seem the data module and the schemas it was using.

Unfortunately, for packages, there was no data source listed - that is - the package name could only be found in the referenced_object_name, with the source_object_name containing the report which is using the package.

Follow the SQL adjusted for CM on version 11.

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.

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?

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

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:
Sent: Fri March 07, 2025 10:35 AM
From: Tomáš Polakovič
Subject: Retrieve data sources/connections for packages and data modules

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č
------------------------------

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

Hey Tomáš,

You can use MetaManager (a similar tool to Motio) to obtain this information.  For packages, we can easily pull the datasource information, for data modules, rather than the datasource, those are often documented via deployment references (external to the data module, not necessarily external to Cognos).  

I can set you up with a free key and walk you through this.  epleiss@microstrat.com or just visit us online, https://www.bspsoftware.com/products/metamanager/#mm-download, download the tool and request a trial key.  Tell them Eric sent you.

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