Is there any similar function with MySQL trigger in IBM Netezza?
As you can see on the title, I want to know the similar function with MySQL's trigger function. What actually I want to do is importing data from IBM Netezza Databases using sqoop incremental mode. Below is the sqoop scripts what I'm going to use.
sqoop job --create dhjob01 -- import --connect jdbc:netezza://10.100.3.236:5480/TEST
--username admin --password password
--table testm
--incremental lastmodified
--check-column 'modifiedtime' --last-value '1995-07-18'
--target-dir /user/dhlee/nz_sqoop_test
-m 1
As the official Sqoop documentation says, I can gather data from RDBs with incremental mode by making a sqoop import job and execute it recursively.
Anyway the point is, I need a function like MySQL trigger so that I can update the modified date whenever tables in Netezza are updated. And if you have any great idea that I can gather the data incrementally, please tell me. Thank you.
triggers sqoop netezza
asked Nov 26 '18 at 8:18
star7357star7357
61
add a comment |
As you can see on the title, I want to know the similar function with MySQL's trigger function. What actually I want to do is importing data from IBM Netezza Databases using sqoop incremental mode. Below is the sqoop scripts what I'm going to use.
sqoop job --create dhjob01 -- import --connect jdbc:netezza://10.100.3.236:5480/TEST
--username admin --password password
--table testm
--incremental lastmodified
--check-column 'modifiedtime' --last-value '1995-07-18'
--target-dir /user/dhlee/nz_sqoop_test
-m 1
As the official Sqoop documentation says, I can gather data from RDBs with incremental mode by making a sqoop import job and execute it recursively.
Anyway the point is, I need a function like MySQL trigger so that I can update the modified date whenever tables in Netezza are updated. And if you have any great idea that I can gather the data incrementally, please tell me. Thank you.
triggers sqoop netezza
asked Nov 26 '18 at 8:18
star7357star7357
61
You want a column to know the last time a row was updated?
– joeb
Nov 27 '18 at 0:46
Yes, Exactly. Each row in modifiedtime column should be updated when the 'UPDATE' query executed for that row.
– star7357
Nov 27 '18 at 1:13
You'll need to accomplish this by changing yourUPDATEstatements to explicitly set a column toCURRENT_TIMESTAMP, as per my answer below.
– joeb
Nov 28 '18 at 18:01
add a comment |
As you can see on the title, I want to know the similar function with MySQL's trigger function. What actually I want to do is importing data from IBM Netezza Databases using sqoop incremental mode. Below is the sqoop scripts what I'm going to use.
sqoop job --create dhjob01 -- import --connect jdbc:netezza://10.100.3.236:5480/TEST
--username admin --password password
--table testm
--incremental lastmodified
--check-column 'modifiedtime' --last-value '1995-07-18'
--target-dir /user/dhlee/nz_sqoop_test
-m 1
As the official Sqoop documentation says, I can gather data from RDBs with incremental mode by making a sqoop import job and execute it recursively.
Anyway the point is, I need a function like MySQL trigger so that I can update the modified date whenever tables in Netezza are updated. And if you have any great idea that I can gather the data incrementally, please tell me. Thank you.
triggers sqoop netezza
asked Nov 26 '18 at 8:18
star7357star7357
61
As you can see on the title, I want to know the similar function with MySQL's trigger function. What actually I want to do is importing data from IBM Netezza Databases using sqoop incremental mode. Below is the sqoop scripts what I'm going to use.
sqoop job --create dhjob01 -- import --connect jdbc:netezza://10.100.3.236:5480/TEST
--username admin --password password
--table testm
--incremental lastmodified
--check-column 'modifiedtime' --last-value '1995-07-18'
--target-dir /user/dhlee/nz_sqoop_test
-m 1
As the official Sqoop documentation says, I can gather data from RDBs with incremental mode by making a sqoop import job and execute it recursively.
Anyway the point is, I need a function like MySQL trigger so that I can update the modified date whenever tables in Netezza are updated. And if you have any great idea that I can gather the data incrementally, please tell me. Thank you.
triggers sqoop netezza
triggers sqoop netezza
asked Nov 26 '18 at 8:18
star7357star7357
61
asked Nov 26 '18 at 8:18
star7357star7357
61
asked Nov 26 '18 at 8:18
star7357star7357
61
asked Nov 26 '18 at 8:18
star7357star7357
61
asked Nov 26 '18 at 8:18
star7357star7357
61
61
You want a column to know the last time a row was updated?
– joeb
Nov 27 '18 at 0:46
Yes, Exactly. Each row in modifiedtime column should be updated when the 'UPDATE' query executed for that row.
– star7357
Nov 27 '18 at 1:13
You'll need to accomplish this by changing yourUPDATEstatements to explicitly set a column toCURRENT_TIMESTAMP, as per my answer below.
– joeb
Nov 28 '18 at 18:01
add a comment |
You want a column to know the last time a row was updated?
– joeb
Nov 27 '18 at 0:46
Yes, Exactly. Each row in modifiedtime column should be updated when the 'UPDATE' query executed for that row.
– star7357
Nov 27 '18 at 1:13
You'll need to accomplish this by changing yourUPDATEstatements to explicitly set a column toCURRENT_TIMESTAMP, as per my answer below.
– joeb
Nov 28 '18 at 18:01
You want a column to know the last time a row was updated?
– joeb
Nov 27 '18 at 0:46
You want a column to know the last time a row was updated?
– joeb
Nov 27 '18 at 0:46
Yes, Exactly. Each row in modifiedtime column should be updated when the 'UPDATE' query executed for that row.
– star7357
Nov 27 '18 at 1:13
Yes, Exactly. Each row in modifiedtime column should be updated when the 'UPDATE' query executed for that row.
– star7357
Nov 27 '18 at 1:13
You'll need to accomplish this by changing your
UPDATE statements to explicitly set a column to CURRENT_TIMESTAMP, as per my answer below.– joeb
Nov 28 '18 at 18:01
You'll need to accomplish this by changing your
UPDATE statements to explicitly set a column to CURRENT_TIMESTAMP, as per my answer below.– joeb
Nov 28 '18 at 18:01
add a comment |
3 Answers
3
active
oldest
votes
Unfortunately there isn't anything similar to triggers available. I would recommend modifying the relevant UPDATE commands to include setting a column to CURRENT_TIMESTAMP
answered Nov 27 '18 at 2:53
joebjoeb
2,20611519
add a comment |
In Netezza you have something even better:
- Deleted records is still possible to see http://dwgeek.com/netezza-recover-deleted-rows.html/
- the INSERT- and DELETE-TXID are a rising number (and visible on all records as described above)
- updates are really a delete plus an insert
Can you follow me?
answered Nov 27 '18 at 6:49
Lars G OlsenLars G Olsen
71938
It means that I can see the rows that I deleted for each table... right? Then how about the updated rows and inserted rows? I've tested each case on my netezza but I cannot see any row as below. I really need your help.
– star7357
Nov 27 '18 at 7:46
The key is to run it before you GROOM the table and run it in the SAME session as the ‘set show_deleted_records = true;’ statement... Aginity creates a new session each time you run a statement (by default) so does many other tools
– Lars G Olsen
Nov 27 '18 at 17:09
add a comment |
enter image description here
This is the screen shot that I've got after I inserted and deleted some rows.
answered Nov 27 '18 at 7:47
star7357star7357
61
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53477044%2fis-there-any-similar-function-with-mysql-trigger-in-ibm-netezza%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
Unfortunately there isn't anything similar to triggers available. I would recommend modifying the relevant UPDATE commands to include setting a column to CURRENT_TIMESTAMP
answered Nov 27 '18 at 2:53
joebjoeb
2,20611519
add a comment |
Unfortunately there isn't anything similar to triggers available. I would recommend modifying the relevant UPDATE commands to include setting a column to CURRENT_TIMESTAMP
answered Nov 27 '18 at 2:53
joebjoeb
2,20611519
add a comment |
Unfortunately there isn't anything similar to triggers available. I would recommend modifying the relevant UPDATE commands to include setting a column to CURRENT_TIMESTAMP
answered Nov 27 '18 at 2:53
joebjoeb
2,20611519
Unfortunately there isn't anything similar to triggers available. I would recommend modifying the relevant UPDATE commands to include setting a column to CURRENT_TIMESTAMP
answered Nov 27 '18 at 2:53
joebjoeb
2,20611519
answered Nov 27 '18 at 2:53
joebjoeb
2,20611519
answered Nov 27 '18 at 2:53
joebjoeb
2,20611519
answered Nov 27 '18 at 2:53
joebjoeb
2,20611519
2,20611519
add a comment |
add a comment |
In Netezza you have something even better:
- Deleted records is still possible to see http://dwgeek.com/netezza-recover-deleted-rows.html/
- the INSERT- and DELETE-TXID are a rising number (and visible on all records as described above)
- updates are really a delete plus an insert
Can you follow me?
answered Nov 27 '18 at 6:49
Lars G OlsenLars G Olsen
71938
It means that I can see the rows that I deleted for each table... right? Then how about the updated rows and inserted rows? I've tested each case on my netezza but I cannot see any row as below. I really need your help.
– star7357
Nov 27 '18 at 7:46
The key is to run it before you GROOM the table and run it in the SAME session as the ‘set show_deleted_records = true;’ statement... Aginity creates a new session each time you run a statement (by default) so does many other tools
– Lars G Olsen
Nov 27 '18 at 17:09
add a comment |
In Netezza you have something even better:
- Deleted records is still possible to see http://dwgeek.com/netezza-recover-deleted-rows.html/
- the INSERT- and DELETE-TXID are a rising number (and visible on all records as described above)
- updates are really a delete plus an insert
Can you follow me?
answered Nov 27 '18 at 6:49
Lars G OlsenLars G Olsen
71938
It means that I can see the rows that I deleted for each table... right? Then how about the updated rows and inserted rows? I've tested each case on my netezza but I cannot see any row as below. I really need your help.
– star7357
Nov 27 '18 at 7:46
The key is to run it before you GROOM the table and run it in the SAME session as the ‘set show_deleted_records = true;’ statement... Aginity creates a new session each time you run a statement (by default) so does many other tools
– Lars G Olsen
Nov 27 '18 at 17:09
add a comment |
In Netezza you have something even better:
- Deleted records is still possible to see http://dwgeek.com/netezza-recover-deleted-rows.html/
- the INSERT- and DELETE-TXID are a rising number (and visible on all records as described above)
- updates are really a delete plus an insert
Can you follow me?
answered Nov 27 '18 at 6:49
Lars G OlsenLars G Olsen
71938
In Netezza you have something even better:
- Deleted records is still possible to see http://dwgeek.com/netezza-recover-deleted-rows.html/
- the INSERT- and DELETE-TXID are a rising number (and visible on all records as described above)
- updates are really a delete plus an insert
Can you follow me?
answered Nov 27 '18 at 6:49
Lars G OlsenLars G Olsen
71938
answered Nov 27 '18 at 6:49
Lars G OlsenLars G Olsen
71938
answered Nov 27 '18 at 6:49
Lars G OlsenLars G Olsen
71938
answered Nov 27 '18 at 6:49
Lars G OlsenLars G Olsen
71938
71938
It means that I can see the rows that I deleted for each table... right? Then how about the updated rows and inserted rows? I've tested each case on my netezza but I cannot see any row as below. I really need your help.
– star7357
Nov 27 '18 at 7:46
The key is to run it before you GROOM the table and run it in the SAME session as the ‘set show_deleted_records = true;’ statement... Aginity creates a new session each time you run a statement (by default) so does many other tools
– Lars G Olsen
Nov 27 '18 at 17:09
add a comment |
It means that I can see the rows that I deleted for each table... right? Then how about the updated rows and inserted rows? I've tested each case on my netezza but I cannot see any row as below. I really need your help.
– star7357
Nov 27 '18 at 7:46
The key is to run it before you GROOM the table and run it in the SAME session as the ‘set show_deleted_records = true;’ statement... Aginity creates a new session each time you run a statement (by default) so does many other tools
– Lars G Olsen
Nov 27 '18 at 17:09
It means that I can see the rows that I deleted for each table... right? Then how about the updated rows and inserted rows? I've tested each case on my netezza but I cannot see any row as below. I really need your help.
– star7357
Nov 27 '18 at 7:46
It means that I can see the rows that I deleted for each table... right? Then how about the updated rows and inserted rows? I've tested each case on my netezza but I cannot see any row as below. I really need your help.
– star7357
Nov 27 '18 at 7:46
The key is to run it before you GROOM the table and run it in the SAME session as the ‘set show_deleted_records = true;’ statement... Aginity creates a new session each time you run a statement (by default) so does many other tools
– Lars G Olsen
Nov 27 '18 at 17:09
The key is to run it before you GROOM the table and run it in the SAME session as the ‘set show_deleted_records = true;’ statement... Aginity creates a new session each time you run a statement (by default) so does many other tools
– Lars G Olsen
Nov 27 '18 at 17:09
add a comment |
enter image description here
This is the screen shot that I've got after I inserted and deleted some rows.
answered Nov 27 '18 at 7:47
star7357star7357
61
add a comment |
enter image description here
This is the screen shot that I've got after I inserted and deleted some rows.
answered Nov 27 '18 at 7:47
star7357star7357
61
add a comment |
enter image description here
This is the screen shot that I've got after I inserted and deleted some rows.
answered Nov 27 '18 at 7:47
star7357star7357
61
enter image description here
This is the screen shot that I've got after I inserted and deleted some rows.
answered Nov 27 '18 at 7:47
star7357star7357
61
answered Nov 27 '18 at 7:47
star7357star7357
61
answered Nov 27 '18 at 7:47
star7357star7357
61
answered Nov 27 '18 at 7:47
star7357star7357
61
61
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53477044%2fis-there-any-similar-function-with-mysql-trigger-in-ibm-netezza%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You want a column to know the last time a row was updated?
– joeb
Nov 27 '18 at 0:46
Yes, Exactly. Each row in modifiedtime column should be updated when the 'UPDATE' query executed for that row.
– star7357
Nov 27 '18 at 1:13
You'll need to accomplish this by changing your
UPDATEstatements to explicitly set a column toCURRENT_TIMESTAMP, as per my answer below.– joeb
Nov 28 '18 at 18:01