DB2 Integrity Checks and Exception Tables The 2019 Stack Overflow Developer Survey Results Are In Unicorn Meta Zoo #1: Why another podcast? Announcing the arrival of Valued Associate #679: Cesar Manara Come Celebrate our 10 Year Anniversary!How to find the next generated value for a auto-increment column?Optimising PHPMyAdmin configuration for massive databases and many tablesHow to change the collation of a table/column?DB2 LUW tools for diagnosing issues when the stuff hits the fanHow to find DB2 port number (note: it does not use port listed in services file and db2 listing is missing from netstat command output) in windows?No match for a database image file was found based on the source database alias “URCDB”Windows DB2 9.7 FP8 - The request failed because private virtual memory could not be allocated. SQLSTATE=57011IBM Cognos Business Intelligence 10.2.2 on WebSphere Application Server 7.0.0.29 fails to start, possibly due to a missing classpath entryExport Data from IBM DB2 into an SQL-INSERT Skript using IBM Data Studio Client or another toolMySQL Table Checksum - Before or after MySQL dump?
What can I do if neighbor is blocking my solar panels intentionally?
What happens to a Warlock's expended Spell Slots when they gain a Level?
Working through the single responsibility principle (SRP) in Python when calls are expensive
What was the last x86 CPU that did not have the x87 floating-point unit built in?
Windows 10: How to Lock (not sleep) laptop on lid close?
Mortgage adviser recommends a longer term than necessary combined with overpayments
One-dimensional Japanese puzzle
Would an alien lifeform be able to achieve space travel if lacking in vision?
How to read αἱμύλιος or when to aspirate
Match Roman Numerals
Drawing vertical/oblique lines in Metrical tree (tikz-qtree, tipa)
How do spell lists change if the party levels up without taking a long rest?
Simulating Exploding Dice
Python - Fishing Simulator
How do you keep chess fun when your opponent constantly beats you?
Identify 80s or 90s comics with ripped creatures (not dwarves)
How to determine omitted units in a publication
Huge performance difference of the command find with and without using %M option to show permissions
Do working physicists consider Newtonian mechanics to be "falsified"?
Do warforged have souls?
Student Loan from years ago pops up and is taking my salary
60's-70's movie: home appliances revolting against the owners
Is this wall load bearing? Blueprints and photos attached
Do ℕ, mathbbN, BbbN, symbbN effectively differ, and is there a "canonical" specification of the naturals?
DB2 Integrity Checks and Exception Tables
The 2019 Stack Overflow Developer Survey Results Are In
Unicorn Meta Zoo #1: Why another podcast?
Announcing the arrival of Valued Associate #679: Cesar Manara
Come Celebrate our 10 Year Anniversary!How to find the next generated value for a auto-increment column?Optimising PHPMyAdmin configuration for massive databases and many tablesHow to change the collation of a table/column?DB2 LUW tools for diagnosing issues when the stuff hits the fanHow to find DB2 port number (note: it does not use port listed in services file and db2 listing is missing from netstat command output) in windows?No match for a database image file was found based on the source database alias “URCDB”Windows DB2 9.7 FP8 - The request failed because private virtual memory could not be allocated. SQLSTATE=57011IBM Cognos Business Intelligence 10.2.2 on WebSphere Application Server 7.0.0.29 fails to start, possibly due to a missing classpath entryExport Data from IBM DB2 into an SQL-INSERT Skript using IBM Data Studio Client or another toolMySQL Table Checksum - Before or after MySQL dump?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I am working on planning a migration of a DB2 8.1 database from a horrible IBM encoding to UTF-8 to support further languages etc. I am encountering an issue that I am stuck on.
A few notes on this migration:
- We are using db2move to export and load the data and db2look to get the details fo the database (tablespaces, tables, keys etc).
- We found the loading process worked nicely with db2move import, however, the data takes 7 hours to load and this was unacceptable downtime when we actually complete the conversion on the main database.
- We are now using db2move load, which is much faster as it seems to simply throw the data in without integrity checks. Which leads to my current issue.
After completing the db2move load process, several tables are in a check pending state and require integrity checks. Integrity checks are done via the following:
set integrity for . immediate checked
This works for most tables, however, some tables give an error:
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL3603N Check data processing through the SET INTEGRITY statement has found integrity violation involving a constraint with name "blah.SQL120124110232400". SQLSTATE=23514
The internets tell me that the solution to this issue is to create an exception table based on the actual table and tell the SET INTEGRITY command to send any exceptions to that table (as below):
db2 create table blah_EXCEPTION like blah
db2 SET INTEGRITY FOR blah IMMEDIATE CHECKED FOR EXCEPTION IN blah USE blah_EXCEPTION
NOW, here is the specific issue I am having! The above forces all the rows with issues to the specified exception table. Well that's just super, buuuuuut I can not lose data in this conversion, its simply unacceptable. The internets and IBM has a vague description of sending the violations to the exception tables and then "dealing with the data" that is in the exception table. Unfortunately, I am not clear what this means and I was hoping that some wise individual knows and could help me out and let me know how I can retrieve this data from these tables and place the data in the original/proper table rather than these exception tables.
Let me know if you have any questions. Thanks!
database database-administration db2 utf-8 data-integrity
asked Feb 26 '12 at 2:31
imthefirestartrimthefirestartr
1816
add a comment |
I am working on planning a migration of a DB2 8.1 database from a horrible IBM encoding to UTF-8 to support further languages etc. I am encountering an issue that I am stuck on.
A few notes on this migration:
- We are using db2move to export and load the data and db2look to get the details fo the database (tablespaces, tables, keys etc).
- We found the loading process worked nicely with db2move import, however, the data takes 7 hours to load and this was unacceptable downtime when we actually complete the conversion on the main database.
- We are now using db2move load, which is much faster as it seems to simply throw the data in without integrity checks. Which leads to my current issue.
After completing the db2move load process, several tables are in a check pending state and require integrity checks. Integrity checks are done via the following:
set integrity for . immediate checked
This works for most tables, however, some tables give an error:
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL3603N Check data processing through the SET INTEGRITY statement has found integrity violation involving a constraint with name "blah.SQL120124110232400". SQLSTATE=23514
The internets tell me that the solution to this issue is to create an exception table based on the actual table and tell the SET INTEGRITY command to send any exceptions to that table (as below):
db2 create table blah_EXCEPTION like blah
db2 SET INTEGRITY FOR blah IMMEDIATE CHECKED FOR EXCEPTION IN blah USE blah_EXCEPTION
NOW, here is the specific issue I am having! The above forces all the rows with issues to the specified exception table. Well that's just super, buuuuuut I can not lose data in this conversion, its simply unacceptable. The internets and IBM has a vague description of sending the violations to the exception tables and then "dealing with the data" that is in the exception table. Unfortunately, I am not clear what this means and I was hoping that some wise individual knows and could help me out and let me know how I can retrieve this data from these tables and place the data in the original/proper table rather than these exception tables.
Let me know if you have any questions. Thanks!
database database-administration db2 utf-8 data-integrity
asked Feb 26 '12 at 2:31
imthefirestartrimthefirestartr
1816
Monday morning bump! Any thoughts?
– imthefirestartr
Feb 27 '12 at 15:37
add a comment |
I am working on planning a migration of a DB2 8.1 database from a horrible IBM encoding to UTF-8 to support further languages etc. I am encountering an issue that I am stuck on.
A few notes on this migration:
- We are using db2move to export and load the data and db2look to get the details fo the database (tablespaces, tables, keys etc).
- We found the loading process worked nicely with db2move import, however, the data takes 7 hours to load and this was unacceptable downtime when we actually complete the conversion on the main database.
- We are now using db2move load, which is much faster as it seems to simply throw the data in without integrity checks. Which leads to my current issue.
After completing the db2move load process, several tables are in a check pending state and require integrity checks. Integrity checks are done via the following:
set integrity for . immediate checked
This works for most tables, however, some tables give an error:
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL3603N Check data processing through the SET INTEGRITY statement has found integrity violation involving a constraint with name "blah.SQL120124110232400". SQLSTATE=23514
The internets tell me that the solution to this issue is to create an exception table based on the actual table and tell the SET INTEGRITY command to send any exceptions to that table (as below):
db2 create table blah_EXCEPTION like blah
db2 SET INTEGRITY FOR blah IMMEDIATE CHECKED FOR EXCEPTION IN blah USE blah_EXCEPTION
NOW, here is the specific issue I am having! The above forces all the rows with issues to the specified exception table. Well that's just super, buuuuuut I can not lose data in this conversion, its simply unacceptable. The internets and IBM has a vague description of sending the violations to the exception tables and then "dealing with the data" that is in the exception table. Unfortunately, I am not clear what this means and I was hoping that some wise individual knows and could help me out and let me know how I can retrieve this data from these tables and place the data in the original/proper table rather than these exception tables.
Let me know if you have any questions. Thanks!
database database-administration db2 utf-8 data-integrity
asked Feb 26 '12 at 2:31
imthefirestartrimthefirestartr
1816
I am working on planning a migration of a DB2 8.1 database from a horrible IBM encoding to UTF-8 to support further languages etc. I am encountering an issue that I am stuck on.
A few notes on this migration:
- We are using db2move to export and load the data and db2look to get the details fo the database (tablespaces, tables, keys etc).
- We found the loading process worked nicely with db2move import, however, the data takes 7 hours to load and this was unacceptable downtime when we actually complete the conversion on the main database.
- We are now using db2move load, which is much faster as it seems to simply throw the data in without integrity checks. Which leads to my current issue.
After completing the db2move load process, several tables are in a check pending state and require integrity checks. Integrity checks are done via the following:
set integrity for . immediate checked
This works for most tables, however, some tables give an error:
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned:
SQL3603N Check data processing through the SET INTEGRITY statement has found integrity violation involving a constraint with name "blah.SQL120124110232400". SQLSTATE=23514
The internets tell me that the solution to this issue is to create an exception table based on the actual table and tell the SET INTEGRITY command to send any exceptions to that table (as below):
db2 create table blah_EXCEPTION like blah
db2 SET INTEGRITY FOR blah IMMEDIATE CHECKED FOR EXCEPTION IN blah USE blah_EXCEPTION
NOW, here is the specific issue I am having! The above forces all the rows with issues to the specified exception table. Well that's just super, buuuuuut I can not lose data in this conversion, its simply unacceptable. The internets and IBM has a vague description of sending the violations to the exception tables and then "dealing with the data" that is in the exception table. Unfortunately, I am not clear what this means and I was hoping that some wise individual knows and could help me out and let me know how I can retrieve this data from these tables and place the data in the original/proper table rather than these exception tables.
Let me know if you have any questions. Thanks!
database database-administration db2 utf-8 data-integrity
database database-administration db2 utf-8 data-integrity
asked Feb 26 '12 at 2:31
imthefirestartrimthefirestartr
1816
asked Feb 26 '12 at 2:31
imthefirestartrimthefirestartr
1816
asked Feb 26 '12 at 2:31
imthefirestartrimthefirestartr
1816
asked Feb 26 '12 at 2:31
imthefirestartrimthefirestartr
1816
asked Feb 26 '12 at 2:31
imthefirestartrimthefirestartr
1816
1816
Monday morning bump! Any thoughts?
– imthefirestartr
Feb 27 '12 at 15:37
add a comment |
Monday morning bump! Any thoughts?
– imthefirestartr
Feb 27 '12 at 15:37
Monday morning bump! Any thoughts?
– imthefirestartr
Feb 27 '12 at 15:37
Monday morning bump! Any thoughts?
– imthefirestartr
Feb 27 '12 at 15:37
add a comment |
1 Answer
1
active
oldest
votes
The long and short of the solution to this issue is that there are characters in the database that violate constraints on specific tables after being converted to UTF-8 from the IBM-1252 encoding. The constraint is almost always a character limit. Once this data was fixed we did not experience an issue converting data. This is however a long and awful process.
answered Apr 16 '12 at 21:06
imthefirestartrimthefirestartr
1816
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "2"
;
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%2fserverfault.com%2fquestions%2f363798%2fdb2-integrity-checks-and-exception-tables%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The long and short of the solution to this issue is that there are characters in the database that violate constraints on specific tables after being converted to UTF-8 from the IBM-1252 encoding. The constraint is almost always a character limit. Once this data was fixed we did not experience an issue converting data. This is however a long and awful process.
answered Apr 16 '12 at 21:06
imthefirestartrimthefirestartr
1816
add a comment |
The long and short of the solution to this issue is that there are characters in the database that violate constraints on specific tables after being converted to UTF-8 from the IBM-1252 encoding. The constraint is almost always a character limit. Once this data was fixed we did not experience an issue converting data. This is however a long and awful process.
answered Apr 16 '12 at 21:06
imthefirestartrimthefirestartr
1816
add a comment |
The long and short of the solution to this issue is that there are characters in the database that violate constraints on specific tables after being converted to UTF-8 from the IBM-1252 encoding. The constraint is almost always a character limit. Once this data was fixed we did not experience an issue converting data. This is however a long and awful process.
answered Apr 16 '12 at 21:06
imthefirestartrimthefirestartr
1816
The long and short of the solution to this issue is that there are characters in the database that violate constraints on specific tables after being converted to UTF-8 from the IBM-1252 encoding. The constraint is almost always a character limit. Once this data was fixed we did not experience an issue converting data. This is however a long and awful process.
answered Apr 16 '12 at 21:06
imthefirestartrimthefirestartr
1816
answered Apr 16 '12 at 21:06
imthefirestartrimthefirestartr
1816
answered Apr 16 '12 at 21:06
imthefirestartrimthefirestartr
1816
answered Apr 16 '12 at 21:06
imthefirestartrimthefirestartr
1816
1816
add a comment |
add a comment |
Thanks for contributing an answer to Server Fault!
- 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%2fserverfault.com%2fquestions%2f363798%2fdb2-integrity-checks-and-exception-tables%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
Monday morning bump! Any thoughts?
– imthefirestartr
Feb 27 '12 at 15:37