How to expand Dataprovider SQL Query column in 360Eyes database

How to expand Dataprovider SQL Query column in 360Eyes database

Overview

  • Goal: Increase the size of the EYE_DATAPROVIDER.DATAPROVIDER_SQL_QUERY column in the 360Eyes database so that the full SQL query of each data provider is stored, instead of being truncated to 4,000 characters.
  • Target audience: 360Eyes administrator / Database administrator
  • Estimated time: 15 minutes, plus the duration of the next Webi or Crystal job run

By default, the DATAPROVIDER_SQL_QUERY column accepts 4,000 characters, which may not be enough to store the whole SQL statement of a data provider. When this happens:

  • The SQL query displayed in 360Eyes Impact Analysis reports (such as Report 52), through the 360eyes_REPORTS.unx universe object Eye Document > Dataprovider > Dataprovider Sql Query, is incomplete.
  • Searches for a specific SQL pattern or keyword miss some documents, because the end of their query is not stored.
  • The 360Eyes Webi or Crystal job logs contain entries similar to the following:
2018-10-02 11:51:32 -0400 INFO Value [SELECT ACTG_DIM.ACTG_...] has been truncated to 4000 characters but has 11526 characters in total (field EYE_DATAPROVIDER.DATAPROVIDER_SQL_QUERY)

Warnings & Prerequisites

Warning: This procedure modifies the structure of the 360Eyes database. Back up the 360Eyes database before you start.

Impact if skipped: If a statement fails halfway (especially the Oracle CLOB conversion, which drops and renames a column), the EYE_DATAPROVIDER table may be left in an inconsistent state.

Before you start, confirm:

  • You have a recent backup of the 360Eyes database.
  • No 360Eyes job is running during the change.
  • You can connect to the 360Eyes database with an account allowed to alter tables.
  • You know which database hosts 360Eyes: SQL Server, Oracle or PostgreSQL.

Procedure

Change the column for your database type, update the universe (whatever the database), then run the next job in FULL mode (see Validation & Verification below).

Case: SQL Server

Run the following statement on the 360Eyes database:

ALTER TABLE EYE_DATAPROVIDER ALTER COLUMN DATAPROVIDER_SQL_QUERY VARCHAR(MAX);

Case: Oracle

Option 1: Increase the VARCHAR2 length

This option is available for Oracle 12c and higher, is limited to 32,767 bytes, and requires the database to allow extended data types (MAX_STRING_SIZE = EXTENDED).

Run the following statement, replacing ##### with the length you need:

ALTER TABLE EYE_DATAPROVIDER MODIFY (DATAPROVIDER_SQL_QUERY VARCHAR2(#####));

Option 2: Convert the column to CLOB

Run the following statements in this order:

ALTER TABLE EYE_DATAPROVIDER ADD (DATAPROVIDER_SQL_QUERY_BIS CLOB);
UPDATE EYE_DATAPROVIDER SET DATAPROVIDER_SQL_QUERY_BIS = DATAPROVIDER_SQL_QUERY;
ALTER TABLE EYE_DATAPROVIDER DROP COLUMN DATAPROVIDER_SQL_QUERY;
ALTER TABLE EYE_DATAPROVIDER RENAME COLUMN DATAPROVIDER_SQL_QUERY_BIS TO DATAPROVIDER_SQL_QUERY;

Case: PostgreSQL

Run the following statement on the 360Eyes database. If your tables are in a specific schema, prefix the table name with it (for example myschema.eye_dataprovider):

ALTER TABLE eye_dataprovider ALTER COLUMN dataprovider_sql_query TYPE TEXT;

If PostgreSQL reports that a view depends on this column, contact the support before going further.

All databases: Update the universe

Once the column has been changed in the database, update the 360Eyes_REPORTS universe in the Information Design Tool:

  1. Refresh the data foundation: Open the data foundation and select Actions > Refresh Structure. Accept the proposed data type change for the DATAPROVIDER_SQL_QUERY column of the EYE_DATAPROVIDER table.
  2. Update the business layer: Change the data type of the Dataprovider Sql Query object (Dataprovider class) from String to Long Text.
  3. Publish the universe: Save your changes and republish the universe.

Validation & Verification

How to confirm success:

  1. Run the next 360Eyes Webi or Crystal job in FULL mode, so that the SQL queries of all reports are extracted again.
  2. Open the job logs.
    Expected output: No more has been truncated to 4000 characters messages for the EYE_DATAPROVIDER.DATAPROVIDER_SQL_QUERY field.
  3. Open an Impact Analysis report (such as Report 52) and check the Dataprovider Sql Query of a document that was previously truncated.

Success criteria: The full SQL query is displayed, and searches on SQL patterns return the expected documents.

If the issue persists, please contact the support with your Webi or Crystal job logs and the statements you ran.

See Also