Options in Output tool

[cmcclellan]

I'm trying to find more information about the Output Data tool, specifically the Output Options

I need to know specifically how the "Delete Data & Append" option works in regards to big tables (ie 1+ million records).

Does this:

a) delete the records in the table, then add the new records, OR

b) truncate the records in the table, then add the new records, OR

c) it depends on the database (in which case I'm using Netezza at the moment)

I can't find anything this specific in the help files and the processing time does have an impact at this level and I want to avoid writing the Pre Create SQL Statement if I can.

[patrick_mcauliffe]

@cmcclellan:

Delete and Append:

- The entire table's data (not the table itself/structure) will be deleted and the data feeding the Output tool will replace it.

Append Existing:

- Take the records feeding the Output tool will just be added (like a union) into the table.

Overwrite:

- Imagine it executing this SQL statement: Drop Table (TableName); Create Table (your incoming data here).  This option would be used over the Delete and Append if you wanted to change the primary keys or needed to add/remove fields

[cmcclellan]

What I was meaning was in the "delete and append" is the "delete" really a delete or is it a truncate ?

[KaneG · Accepted solution]

I performed a quick profile and on my SQL instance, it performed a Truncate.

[cmcclellan]

Thanks mate, we're using Netezza but I'm going to assume it works the same way :)

[anotherusername]

Is there any way to intercept the SQL being sent to the server from the In-DB Output Tool to confirm this?

I tried using the Dynamic output - but that can't intercept from between two in-DB tools because it connects to the back-end of an in-DB tool and then feeds it out to std tools.  (reason why I'm asking, is that if the in-DB tool performs TRUNCATE then there is no need to do the Netezza GROOMs on the tables being overwritten to recover space on large or regular overwrites - and which allows us to confirm with the DB admins that we're adhering to their best-practice - though perhaps we could ask them to monitor the server log to confirm for us)

[KaneG]

There is no way to intercept it on the In-DB tools as if you put the Dynamic Output tool after a Write In-DB, then you'll just get the read query..., monitoring the table is the only way that I know.

[anotherusername · Accepted solution]

Update:

I've done some testing with both in-DB (Write Data In-DB) and std Output tool (Output Data).  Both are set to DELETE DATA & APPEND and I am getting DELETE commands being issued to Netezza.

(the 2 tools are in the same workflow, writing data from the same source, but to 2 different tables in the DB)

Have had a server admin checking the logs for me.

This soft-delete in Netezza is probably important to take note of, as it will therefore need regular GROOMs to be performed on tables with regular/large churn.

(am using 11.7 - perhaps may differ between versions?)

[KasiaP]

Hi,

Can anyone explain me how update options work?

I mean the one highlighted in yellow:

[KasiaP]

Or can anyone direct me to the place where it is described?