how to debug QM database access problems.. help
We have our applications deployed on different servers
JTS
CCM
RM
QM
and this is mirrored in DR.
this is using a common Oracle provided database service. both Prod and DR access the same DB.
or DBAs created ADMIN level userids for database setup (during CLM install)
and USER level userids for daily operation.
to go to full production, access to the ADMIN level userids is removed.
but it appears the database schema was labeled with the ADMIN user userid.
we are using Websphere for the data sources.
we cannot rename the schema (as far as I know)
and for some reason cannot access the ADMIN schema from the USER context..(altho the DBA's tell us we should be able to)
we would like to take a DB access error log to our DBAs to fix this problem, but can't seem to find a way to get that level of detail from the clm apps.. (we are using QM to debug, cause we don't yet have any users)..
any guidance welcomed..
Accepted answer
If I understand correctly what you said, I believe a mistake has been made with regard to the database configuration.
You cannot use two user accounts in the way that you describe - one to create the tables and the other to access the tables' content, because the user and the schema can be considered as the same thing in Oracle. It can be made possible if we can specify the schema name for all the SQL statements - say, with user "clmadmin" creating all the tables, we specify the schema name "clmadmin" for all SQL statements when logging on as user "clmuser", so we will look for "clmadmin.table" instead of "clmuser.table". BUT, I don't think we have this option in CLM.
To fix the problem, you just ignore the "USER level userids", and remove the DBA privileges from the "ADMIN level userids" to make them "USER level". In other words, you need to use the same set of user account all the way through.
More details can be found in this document.
https://jazz.net/wiki/bin/view/Main/MoreControlOverTheOracleDataWarehouseSetup
You cannot use two user accounts in the way that you describe - one to create the tables and the other to access the tables' content, because the user and the schema can be considered as the same thing in Oracle. It can be made possible if we can specify the schema name for all the SQL statements - say, with user "clmadmin" creating all the tables, we specify the schema name "clmadmin" for all SQL statements when logging on as user "clmuser", so we will look for "clmadmin.table" instead of "clmuser.table". BUT, I don't think we have this option in CLM.
To fix the problem, you just ignore the "USER level userids", and remove the DBA privileges from the "ADMIN level userids" to make them "USER level". In other words, you need to use the same set of user account all the way through.
More details can be found in this document.
https://jazz.net/wiki/bin/view/Main/MoreControlOverTheOracleDataWarehouseSetup
Comments
thank you. we will see. the standards here are pretty strict, user level userids are not allowed to create tables, etc.. and using admin level userids on a production platform for a runtime is not allowed. whether they will allow the admin rights to be removed and retain the admin user name is questionable
I think you can show the document to your DBA and see what can be done. If an application is custom-built with this restriction in mind, it should not have any problems with specifying where to get the tables. But CLM is just not such an application.