FM against Netezza Synonyms

[Michael75]

I'm working with Cognos 10.2.1 FP5 against a Netezza DM.

The DB layer in our FM project always uses (used)

Code Select Expand

select * from [datasource].table

in order to obtain minimised SQL. Our ETL team have recently made changes to more more than 100 objects in our DM. What were previously Tables or Views are now Synonyms.

Now, the same select statement results in BMT-MD-5002 The object '' of type 'table or view' does not exist in the data source when trying to Validate or Update the object. (Yes, those are two single quotes with nothing in between). I'm obliged to include the full list of columns in order to get the QS to validate FM to recognise the object.

Has anybody else come across this? Is there some small setting to tweak, or is the issue solved in a fix-pack?

Edit: This is maybe not a Netezza-specific problem. I haven't worked with synonyms before, but I'm informed that they exist in other RDBMSs.

[AnalyticsWithJay]

Hi Michael,

You should be fine in using synonyms. Are you sure the credentials being used have DB permissions on those synonyms?

Are you able to use Workbench or whatever Netezza client to traverse and query those objects using the signon credentials? 'Table or view does not exist' typically indicates a DB permissions issue.

Jay

[Michael75]

Hi Jay,

I just checked, and the credentials used in the datasource signon are the same as those I use in my Aginity tool, where select * from synonym  works. So it seems that the credentials used is not the reason.

Oh well...
Michael

PS I just wanted to say a big Thank You for your major input to this forum! You've helped many, many people in the short time you've been here. I hope you'll stay around for a while :)

[AnalyticsWithJay]

Hi Michael,

I searched the APARs after our credential test passed and it looks like we found the problem:

http://www-01.ibm.com/support/docview.wss?uid=swg1PI31395

Error description
When Schema Support is enabled in Netezza, Framework Manager
does not import synonyms during the Metadata Wizard import
unless they exist within the ADMIN schema.

Local fix
Use Netezza views rather than synonyms.


****************************************************************
* RECOMMENDATION:                                              *
* Upgrade to IBM Cognos Business Intelligence 10.2.1 Fix Pack  *
* 6                                                            *
****************************************************************

Problem conclusion
Code Fix

Comments
A fix for this APAR is targeted for IBM Cognos Business
Intelligence 10.2.1 Fix Pack 6

The Fix Pack Release Schedule can be found at
www-01.ibm.com/software/analytics/cognos/support/fixpacks.html

[AnalyticsWithJay]

PS I just wanted to say a big Thank You for your major input to this forum! You've helped many, many people in the short time you've been here. I hope you'll stay around for a while :)

That is very kind of you, thank you! As long as my clients don't keep me too busy you can be sure I'll continue to contribute :). I actually used to be part of this forum when I worked for Cognos and then for IBM, but I can't remember my login info  ;D.

Thanks again! And great job to you too in your contributions :)

Cheers,
Jay

[Michael75]

A fix for this APAR is targeted for IBM Cognos Business Intelligence 10.2.1 Fix Pack 6

Thank you! We already want to go to C10.2.1 FP10 to get other bugfixes, and having another reason to upgrade will help to get things moving.

[MFGF]

I actually used to be part of this forum when I worked for Cognos and then for IBM, but I can't remember my login info  ;D.

Can you PM me your original forum user name? I can see if we can get the password reset if you like?

Cheers!

MF.

PS. I second Michael's statement above! Thanks for all the help you give to others on these pages! :)

[AnalyticsWithJay]

Can you PM me your original forum user name? I can see if we can get the password reset if you like?

Cheers!

MF.

PS. I second Michael's statement above! Thanks for all the help you give to others on these pages! :)

Thanks MF! I no longer own the registered e-mail. It's not a problem - I'm not too attached to it. :)

[MFGF]

Thanks MF! I no longer own the registered e-mail. It's not a problem - I'm not too attached to it. :)

That wouldn't be a problem either, I don't think. Up to you - we can get you back in under your old name if you like, but no worries if not :)

MF.

[Michael75]

Not sure that this will be of interest to a large number of people, but we've found a workaround.

This is a segmented FM project, and all my DB synonyms are in one segment, which can either be accessed in FM via the main segment, which is what I was doing previously, or directly, which is what I'm now doing.

I put the DB name in the Catalog property of the segment's Data Source, and now Update Object is finding the object in the DB. All I have to do when I've finished is to set the Catalog property to spaces again to align it with the definitions in the other segments.

[the6campbells]

Netezza's implementation of synonyms has various limitations with respect to standard ODBC/JDBC API calls to get metadata.

In order to work around those limitations, queries directly against Netezza catalog tables have to use used.

When Netezza introduced multi-schema support in 7.x that cause a an issue which was resolved in Cognos BI.

Contact IBM support for more details if you need them.