问题描述
我发现 Netezza 将数据历史存储在 HISDB 模式中.是否可以加入它们，以便我获得哪个表被什么程序修改过的历史记录?
I found that Netezza stores history of data in HISDB schema. Is it possible to join them so I would get history of which table has been modified by what procedure?
这样做的原因是我有加载 Netezza 表的 DataStage 作业，并且在 SQL 命令触发将另一组数据添加到同一个表的过程之后.我需要为数据沿袭目的记录所有事件.
Reason for this is I have DataStage job that loads Netezza table and after SQL command triggers procedures that add another set of data to that same table. I am in need to have all events documented for data lineage purpose.
我所做的当前查询返回过程的调用时间.问题在于加入 USER_HISTDB."$hist_table_access_3".唯一匹配的字段是 NPSINSTANCEID.LOGENTRYID、OPID 和 SESSIONID 具有不同的值.
Current query I made returns procedure's call time. Issue is with joining to USER_HISTDB."$hist_table_access_3". The only field that matched is NPSINSTANCEID. LOGENTRYID, OPID and SESSIONID have different value.
这阻止了我制作程序到表格的链接.
That stops me from making procedure to table link.
SELECT
b.SUBMITTIME,
b.QUERYTEXT,
b.USERNAME,
b.DBNAME,
b.SCHEMANAME,
a.*
FROM USER_HISTDB."$hist_log_entry_3" a
JOIN USER_HISTDB."$hist_query_prolog_3" b
ON a.LOGENTRYID = b.LOGENTRYID
AND a.SESSIONID = b.SESSIONID
AND a.NPSID = b.NPSID
AND a.NPSINSTANCEID = b.NPSINSTANCEID
WHERE b.QUERYTEXT like '%PROCEDURE_NAME%'
推荐答案
-- By default, information about stored procedures is not logged
-- in the query history database. To enable logging of such ...
set ENABLE_SPROC_HIST_LOGGING = on;
-------------------------------------------------------------------------
-- TABLE -- All Info About All Accesses
-- ====================================
SELECT
QP.submittime,
substr(QP.querytext, 1, 100) as SQL_STATEMENT,
xid, -- the transaction id (which might be either a CREATEXID or DELETEXID)
username,
CASE
when usage = 1 then 'SELECTED'
when usage = 2 then 'INSERTED'
when usage = 3 then 'SELECTED/INSERTED'
when usage = 4 then 'DELETED'
when usage = 5 then 'SELECTED/DELETED'
when usage = 8 then 'UPDATED'
when usage = 9 then 'SELECTED/UPDATED'
when usage = 16 then 'TRUNCATED'
when usage = 32 then 'DROPPED'
when usage = 64 then 'CREATED'
when usage = 128 then 'GENSTATS'
when usage = 256 then 'LOCKED'
when usage = 512 then 'ALTERED'
else 'other'
END AS OPERATION,
TA.dbname,
TA.schemaname,
TA.tablename,
TA.tableid,
PP.planid -- The MAIN query plan (not all table operations involve a query plan)
-- If you want to see EVERYTHING, uncomment the next line.
-- Or pick and choose the columns you want to see.
-- ,*
FROM
---- SESSION information
"$hist_session_prolog_3" SP
left outer join "$hist_session_epilog_3" SE using ( SESSIONID, npsid, npsinstanceid )
---- QUERY information (to include the SQL statement that was issued)
left outer join "$hist_query_prolog_3" QP using ( SESSIONID, npsid, npsinstanceid )
left outer join "$hist_query_epilog_3" QE using ( OPID, npsid, npsinstanceid )
left outer join "$hist_table_access_3" TA using ( OPID, npsid, npsinstanceid )
---- PLAN information
---- Not all queries result in a query plan (for example, TRUNCATE and DROP do not)
---- And some queries might result in multiple query plans (such as a GROOM statement)
---- By including these joins we might get multiple rows (for any given row in the $hist_table_access_3 table)
left outer join "$hist_plan_prolog_3" PP using ( OPID, npsid, npsinstanceid )
left outer join "$hist_plan_epilog_3" PE using ( PLANID, npsid, npsinstanceid )
WHERE
(ISMAINPLAN isnull or ISMAINPLAN = true)
---- So ...
---- If there is NO plan file (as with a truncate) ... then ISMAINPLAN will be null. Include this row.
---- If there is a plan file, include ONLY the record corresponding to the MAIN plan file.
---- (Otherwise, there could end up being a lot of duplicated information).
and TA.tableid > 200000
---- Ignore access information for SYSTEM tables (where the OBJID # < 200000)
----
----Add any other restrictions here (otherwise, this query as written will return a lot of data)
----
ORDER BY 1;
这篇关于查看IBM Netezza 中哪些程序修改了数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！