Generating view or table creation scripts

When you generate reports, you extract reportable data from the reporting views or tables. You can create reporting views or tables by using the view or table creation scripts. Use the Reports SQL Generator to create view or table creation scripts.

About this task

Note: For eMessage reports, make sure that you run the SQL scripts against the Campaign database to create the staging tables and batch procedures. For more information, see For eMessage only: Creating stored procedures, staging tables, and indexes.

To create view or table creation scripts, complete the following steps.

Procedure

  1. Log in to HCL® Marketing Software as the user who has the ReportsSystem role.

    If you used the default JNDI names for JDBC data sources, continue to step 3.

  2. If you did not use the default JNDI names for JDBC data sources, complete steps a and b:
    1. Select Settings > Configuration > Reports > Schemas > ProductName.
    2. Change the default values of the JNDI property to match the JNDI names that you used for the JDBC connections.
    Important: If the JNDI data source names are incorrect or have not been configured, the SQL Generator cannot validate the SQL scripts that create tables.
  3. Select Settings > Reports SQL Generator.
  4. In the Product field, select the appropriate HCL Marketing Software application.
  5. In the Schema field, select one or more reporting schemas.
  6. Select the Database Type.
  7. In the Generate Type field, select the appropriate option for your database type.
    • If the database type is Microsoft™ SQL Server, you cannot select materialized views.
    • For eMessage only:
      • For Oracle and IBM DB2®, eMessage requires materialized views.
      • For SQL Server, eMessage requires views.

      For more information about creating views or materialized views for eMessage, see For Campaign and eMessage only: Creating views or materialized views.

  8. Ensure that Generate Drop Statement is set to No.
  9. If you want to examine the SQL script that is generated, click Generate.
    The SQL Generator creates the script and displays it in the browser window.
  10. Click Download.

    The SQL Generator creates the script and prompts for a location in which to save the file. If you selected a single reporting schema, the script name matches the name of schema, for example eMessage_Mailing_Performance.sql. If you selected more than one reporting schema, the script name uses the product name, for example Campaign.sql.

    Note: When you run a script that creates materialized views on a DB2 database, you might see 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.

  11. Specify the location where you want to save the script and click Save. If you change the name of the file, make sure to use a name that clearly indicates the schemas that you selected.
  12. Repeat steps 5 through 11 for each script that you want to generate.
    Note: The Interact reporting schemas reference multiple data sources. Generate a separate SQL script for each data source.