You can use SQL scripts to create views or materialized
views for Interact. Reports uses views or materialized views to extract
reportable data.
Before you begin
Before you create views or materialized views for Interact,
verify that the language setting for the computer from where you run
the lookup_create SQL script is enabled for UTF-8
encoding. Refer to the following table as a guideline to change the language setting
to UTF-8:Table 1. Changing language setting in Oracle and DB2®This two-columned table provides information
about the database type in one column, and the guidelines in the second
column.
Database type |
Guidelines |
Oracle |
Use the following steps as a guideline if your
database is Oracle:
- Close all open Oracle sessions.
- Open the Registry Editor.
- Browse to and open the folder for your Oracle
Home, for example, KEY_OraDb10g_home1.
- Search for the NLS_LANG setting.
- Make sure the last part of the value specified is UTF8. For example:
AMERICAN_AMERICA.UTF8 .
|
DB2 |
Use the following steps as a guideline if your
database is DB2:
- From the computer that is running the script and has the DB2 client installed, run a DB2 command window. Then run the
following command:
db2set
- In the output, look for the following variable/value pair:
DB2CODEPAGE=1208
- If the
DB2CODEPAGE=1208 variable is not set,
run the following command:db2 db2set db2codepage=1208
- Close the session window so the change can take effect.
|
Procedure
Complete the following steps to create views or materialized
views for Interact:
- Locate the SQL scripts that you generated and saved previously.
- Use your database administration tools to run the appropriate
script against the appropriate application database(s) for the report
package that you are configuring.
Note: When
you run a script that creates materialized views on a DB2 database, your database might display the
following error:
"SQL20059W The materialized query table-name may
not be used to optimize the processing of queries."
However,
the materialized view is successfully created.
- Locate the tools subdirectory in the
reports package installation directory and find the lookup_create script
for your database type.
For example, the script for
SQL is named uari_lookup_create_MSSQL.sql.
- Run the lookup_create script on the Interact design
time database. Ensure that the database tool you are using commits
the changes. For example, you might need to set the auto-commit option
of the database to true.
- Browse to the <Interact_ReportPack_Installer_Home>\Cognos10\interact-ddl\ <DB
Type>\ folder.
- For DB2, set the db2set
DB2_COMPATIBILITY_VECTOR=ORA parameter.
- Run the acir_tables_<DB
Type>.sql script on the ETL database.
What to do next
Setting up data synchronizationMake sure that you
use scheduled Extraction, Transformation, and Load (ETL) method, or
any custom method to schedule regular data synchronization between
the production databases of the IBM® EMM
application and the new reporting tables.