Sql-server – Netezza Equivalent to T-SQL ROWVERSION or PL/SQL ORA_ROWSCN

change-data-capturenetezzaoraclesql server

Is there a Netezza equivalent to the MSSQL ROWVERSION or the Oracle ORA_ROWSCN? Essentially I'd like to know if a record changed and be confident that it changed. Using a checksum isn't reliable 100% of the time.

Best Answer

I'm not sure about the ROWVERSION behaviour, but ORA_ROWSCN I believe would still change if you were to update a row with completely identical values. In other words, the row contents would remain the same but its SCN would be different.

The only practically reliable way to detect the actual change in column values would be to calculate the row checksum or hash.

To answer your question though, consider using the createxid special column in Netezza: http://pic.dhe.ibm.com/infocenter/ntz/v7r0m3/topic/com.ibm.nz.dbu.doc/r_dbuser_data_types_ntz_internal.html

And again, a changed createxid value would not necessarily mean that any column values have changed.

Related Solutions

Netezza Error – Record Size Exceeds Internal Limit of 65535 Bytes

I suppose the data type of the result of the function SUBSTR(PARAM, ...) has the same length as the column PARAM itself. Since you have two of those in your select list, if the length of PARAM is large enough it's possible that the combined length of two values in the select list exceeds some sort of internal limit.

Try explicitly casting the values to shorter data types, e.g.

SELECT 
  cast(
    substr(PARAM, (instr (PARAM , 'Name":"' )+7), (instr(PARAM, 'StepName":"'))-21)
  as varchar (100)) as Name, 
  cast(
    substring(PARAM, (instr (PARAM , 'StepName":"'))+14, (instr(PARAM, 'StepData":"'))-(instr(PARAM, 'StepName":"'))-17) 
  as varchar (100)) as StepName 
FROM Table

Netezza: What schema contains all the system tables / views

I don't know of a way to access such a list via a query but IBM provide a list of user views and system views in their Knowledge Centre. I assume those pages are public because their website tells me I'm not currently logged in (but it's not the most user-friendly of sites in terms of authentication so I may be wrong).

As CraigBoyd pointed out in a comment, SELECT * FROM _V_SYS_VIEW; will give you the list of system views. SELECT * FROM _V_VIEW; will also list some of the system views plus any user-defined views. I can't tell why some views are included in _V_SYS_VIEW but not in _V_VIEW.

Best Answer

Related Solutions

Netezza Error – Record Size Exceeds Internal Limit of 65535 Bytes

Netezza: What schema contains all the system tables / views

Related Question