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.