News
Abstract
IBM Pure Data for Analytics does not record queries from stored procedure into query history database.
Content
IBM Pure Data for Analytics (henceforth referred to as “Netezza”) software release 7.2.1.6-P1 introduces feature to record queries being executed from a stored procedure and it's nested stored procedure(s). These are recorded in the query history database.
To use this feature, new configuration parameter “enable_sproc_hist_logging” is introduced in postgres, that can either be set globally or set at session level. The feature is disabled by default.
Feature behavior:
When enabled, the feature starts recording internal queries executed by a stored procedure.
For example, for following stored procedure:
CREATE OR REPLACE PROCEDURE test_proc()
RETURNS INT4 LANGUAGE NZPLSQL AS
BEGIN_PROC
declare c integer;
BEGIN
RAISE NOTICE 'The customer name is beta';
create table netezza(i int);
insert into netezza values(10);
select count(*) into c from netezza;
END;
END_PROC;
Execution of this stored procedure on a system where feature is enabled logs SQLs highlighted in bold above in the stored procedure definition to the history database.
HISTDB.QUERYOWNER(ADMIN)=> select opid, sessionid, dbname, query from "$v_hist_queries" where sessionid=298716 and dbname='SYSTEM' order by opid;
OPID | SESSIONID | DBNAME | QUERY
---------+-----------+--------+-----------------------------------------------------------
2279850 | 298716 | SYSTEM | select version(), 'OS Platform: Linux', 'OS Username: nz'
2279851 | 298716 | SYSTEM | SET CLIENT_VERSION = 'Release 7.2.1.6-P1 [Build 0]'
2279852 | 298716 | SYSTEM | select current_catalog, current_schema, current_user
2279981 | 298716 | SYSTEM | set enable_sproc_hist_logging=1
2280102 | 298716 | SYSTEM | show enable_sproc_hist_logging
2280202 | 298716 | SYSTEM | call test_proc()
2280203 | 298716 | SYSTEM | create table netezza(i int)
2280207 | 298716 | SYSTEM | insert into netezza values(10)
2280209 | 298716 | SYSTEM | SELECT count(*) from netezza
The behavior differs for regular and encrypted/obfuscated stored procedures.
Case 1: Regular stored procedure call:
All internal queries and nested stored procedure(s) executions are recorded. If the nested stored procedure is encrypted/obfuscated, it's queries are not recorded.
Case 2: Obfuscated/encrypted store procedure call:
Any internal queries or nested store procedure(s) (regular or obfuscated) are not captured in the query history database.
· Enable feature:
Query history must be enabled before enabling this feature. There are two ways to enable the feature:
1. Enable globally (needs Netezza restart to take effect):
To enable the feature globally, add following configuration parameter in postgresql.conf.
enable_sproc_hist_logging = true
2. Enable feature at session level:
To enable the feature at session level, use “set” command as shown in example below:
SYSTEM.ADMIN(ADMIN)=> show enable_sproc_hist_logging;
NOTICE: ENABLE_SPROC_HIST_LOGGING is off
SHOW VARIABLE
SYSTEM.ADMIN(ADMIN)=> set enable_sproc_hist_logging=true;
SET VARIABLE
SYSTEM.ADMIN(ADMIN)=> show enable_sproc_hist_logging;
NOTICE: ENABLE_SPROC_HIST_LOGGING is on
SHOW VARIABLE
After enabling this parameter, queries from stored procedure from current session are recorded in the query history database.
· Disable feature:
There are two ways to disable the feature:
1. Disable feature globally (needs Netezza system restart):
To disable the feature globally, you can either:
o Remove “enable_sproc_hist_logging” from postgresql.conf OR
o Set following in postgresql.conf
enable_sproc_hist_logging = false
2. Disable feature at session level:
To disable the feature at session level, set
enable_sproc_hist_logging to false as shown in example below:
SYSTEM.ADMIN(ADMIN)=> show enable_sproc_hist_logging;
NOTICE: ENABLE_SPROC_HIST_LOGGING is on
SHOW VARIABLE
SYSTEM.ADMIN(ADMIN)=> set enable_sproc_hist_logging=false;
SET VARIABLE
SYSTEM.ADMIN(ADMIN)=> show enable_sproc_hist_logging;
NOTICE: ENABLE_SPROC_HIST_LOGGING is off
SHOW VARIABLE
After disabling this parameter, queries from stored procedure executed from current session are not recorded in the query history database.
NOTE:
Query history must be enabled before using this feature.
Product Synonym
NPS Netezza Performance Server PureData for Anayltics PDA
Was this topic helpful?
Document Information
More support for:
IBM PureData System
Software version:
1.0.0
Document number:
571675
Modified date:
17 October 2019
UID
swg22016854