Options in Output tool
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Labels:
- Output
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
What I was meaning was in the "delete and append" is the "delete" really a delete or is it a truncate ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I performed a quick profile and on my SQL instance, it performed a Truncate.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks mate, we're using Netezza but I'm going to assume it works the same way :)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
Can anyone explain me how update options work?
I mean the one highlighted in yellow:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
