IBM Support

Logging queries executed in a stored procedure to query history DB

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":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"IBM Netezza Analytics","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Product Synonym

NPS Netezza Performance Server PureData for Anayltics PDA

Document Information

More support for:
IBM PureData System

Software version:
1.0.0

Document number:
571675

Modified date:
17 October 2019

UID

swg22016854