COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Framework Manager => Topic started by: Cognos8 on 30 Dec 2009 06:56:10 AM

Title: Audit table error
Post by: Cognos8 on 30 Dec 2009 06:56:10 AM
I did import audit table from FM, but when test i got this below error. my tables referes as Audit..dbo.COGIPF_RUNJOB.

Error
------
This query contains an error and can not be executed.

It is recommended that you view the query feedback on the "Query Information" tab.

RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-201'.
UDA-SQL-0107 A general exception has occurred during the operation "columns".
ORA-00942: table or view does not exist

UDA-SQL-0196 The table or view "COGIPF_ACTION" was not found in the dictionary
Title: Re: Audit table error
Post by: blom0344 on 30 Dec 2009 07:41:08 AM
Is the cognos datasource 'audit' pointing to the right database? Do you have multiple connections configured for the datasource? If you can import the definition into FM with a certain combo you should be able to read data..
Title: Re: Audit table error
Post by: Cognos8 on 30 Dec 2009 09:27:34 AM
Hi ,
Yes it is pointing to right database.
I am using the default audit package.so it is already imported properly.
i am able to import the tables. but when i do test it throws  that error.
Title: Re: Audit table error
Post by: RudiHendrix on 04 Jan 2010 02:50:29 AM
I have a similar issue.

After some work we finally got the audit records to be written to the database, but now the package doesn't seem to work.

@Cognos8: have you tried to open the CPF file for the package in FM? Try to do that. In my case I saw that the model (and thus the package) was using (for example) "Select * from [Audit].COGIPF_ACTION". Upon hitting the test tab I get the same error.

If I recreate the query by dragging the table from the data source (which is obviously called "Audit") it displays "Select * From [Audit1].COGIPF_ACTION COGIPF_ACTION". I don't know where this Audit1 is coming from!
If I copy "Select * From [Audit1].COGIPF_ACTION COGIPF_ACTION", cancel out, open the query subject again and immediately paste "Select * From [Audit1].COGIPF_ACTION COGIPF_ACTION" it returns an error as well! "The source of the query subject aBmtQuerySubject is invalid."

I've raised an issue of this with Cognos as well.
Title: Re: Audit table error
Post by: blom0344 on 04 Jan 2010 04:55:10 AM
Quote from: Plantje on 04 Jan 2010 02:50:29 AM
I have a similar issue.

After some work we finally got the audit records to be written to the database, but now the package doesn't seem to work.

@Cognos8: have you tried to open the CPF file for the package in FM? Try to do that. In my case I saw that the model (and thus the package) was using (for example) "Select * from [Audit].COGIPF_ACTION". Upon hitting the test tab I get the same error.

If I recreate the query by dragging the table from the data source (which is obviously called "Audit") it displays "Select * From [Audit1].COGIPF_ACTION COGIPF_ACTION". I don't know where this Audit1 is coming from!
If I copy "Select * From [Audit1].COGIPF_ACTION COGIPF_ACTION", cancel out, open the query subject again and immediately paste "Select * From [Audit1].COGIPF_ACTION COGIPF_ACTION" it returns an error as well! "The source of the query subject aBmtQuerySubject is invalid."

I've raised an issue of this with Cognos as well.

Cognos FM automatically adds another datasource (audit1) with an index when you import objects for the second time. This , is however just an aliased object pointing to the contentstore audit datasource. You can modify the select expression (change audit1 to audit ) and then delete the audit1 object.
Title: Re: Audit table error
Post by: RudiHendrix on 04 Jan 2010 06:15:07 AM
Ahum... ok

As I understand it the topic starter has the Audit package up and running.

Although my connection is called "Audit" the reports based on the Audit package do not work. Testing the connection does work.
If I use the package in Query Studio I also get an error "The table or view "COGIPF_USERLGON" was not found in the dictionary".

That's why I started looking to the FM model in the first place. There as well the Audit data source works.

I think the first occurrence of the error is when testing the Query Subjects in the Database View.
Title: Re: Audit table error
Post by: RudiHendrix on 04 Jan 2010 06:19:17 AM
Ahum...  :-X

Found it... Out of the box the data source schema is set to "dbo". Obviously I had to change this!
Title: Re: Audit table error
Post by: RudiHendrix on 04 Jan 2010 06:40:44 AM
And even more... After importing the Audit zip I had to update the package and publish it to the exact same location. This way the package (with the schema dbo) is overwritten without overwriting the rest of the content (reports etc.)