Migrating data from the SQLQUERIES table

Deployment options: Netezza Performance Server for Cloud Pak for Data System Netezza Performance Server for Cloud Pak for Data

If you want to use stored queries on the Netezza web console, migrate data from the existing SQLQUERIES table to a new one after you install Cloud Pak for Data System 1.0.7.5.

Procedure

  1. As admin, check the version of the SQLQUERIES table on the SYSTEM database:
    select longsql from SQLQUERIES limit 1;

    You can run the query from the Query Editor on the web console.

    • If you see the following error message, the SQLQUERIES table is old and needs to be migrated.
      Netezza Error: ERROR: Attribute 'LONGSQL' not found
  2. To migrate, run the following sql queries on the System database:
    1. Rename the SQLQUERIES table to SQLQUERIESold:
      alter table SQLQUERIES rename to SQLQUERIESold;
    2. Create a new SQLQUERIES table:
      CREATE TABLE SQLQUERIES (name nvarchar(128), host varchar(256), port integer, database nvarchar(128), 
schema nvarchar(128), sql nvarchar(15000), username nvarchar(128), timestamp timestamp, longsql boolean, pieces int);
    3. Copy data from SQLQUERIESold to SQLQUERIES:
      insert into SQLQUERIES select *, false, 1 from SQLQUERIESold;
    4. Drop the SQLQUERIESold table:
      drop table SQLQUERIESold;