473,887 Members | 2,315 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

When will LOAD utility check consistaint

The following is a question of LOAD utility.
Question:
How does the DB2 enforce table check constraints for data added to
table with the LOAD utility?
A. With the BUILD phase of LOAD
B. With the SET INTEGRITY statement
C. With the DELETE phase of the LOAD
D. With the UPDATE CONSTRAINTS statement

Answer is A

I am confused why the correct answer is A. In BUILD phase, LOAD utility
only maintain index. I think LOAD utility check consistaint in LOAD
phase, not BUILD phase.

Is my thought right?

Thanks

James

Sep 18 '06 #1
1 5966
Actually, the answer is not A, it's B. Load just shoves data to the
target table. SET INTEGRITY validates it against any constraints.

See below. The table BLAH has a check constraint that col1 can't be
greater than 3. The input file to the load utility has values up to 6.
The load utility loads all six records, it's the SET INGERITY that
discovers the constraint violation, and refuses to give it a stamp of
approval as long as the offending rows are left in the table:

audi$ db2 "create table blah (col1 integer)"
DB20000I The SQL command completed successfully.
audi$ db2 create table blah2 like blah
DB20000I The SQL command completed successfully.
audi$ db2 "alter table blah add constraint chk_blah check (col1 <= 3)"
DB20000I The SQL command completed successfully.
audi$ cat loadme
1
2
3
4
5
6
audi$ db2 load from loadme of del insert into blah
SQL3501W The table space(s) in which the table resides will not be
placed in
backup pending state since forward recovery is disabled for the
database.

SQL3109N The utility is beginning to load data from file
"/home/blahblah/loadme".

SQL3500W The utility is beginning the "LOAD" phase at time "09/18/2006

11:22:55.673720 ".

SQL3519W Begin Load Consistency Point. Input record count = "0".

SQL3520W Load Consistency Point was successful.

SQL3110N The utility has completed processing. "6" rows were read
from the
input file.

SQL3519W Begin Load Consistency Point. Input record count = "6".

SQL3520W Load Consistency Point was successful.

SQL3515W The utility has finished the "LOAD" phase at time "09/18/2006

11:22:55.822691 ".
Number of rows read = 6
Number of rows skipped = 0
Number of rows loaded = 6
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 6

audi$ db2 set integrity for blah immediate checked
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
"BLAHBLAH.BLAH. CHK_BLAH".
SQLSTATE=23514
audi$ db2 set integrity for blah immediate checked for exception in
blah use blah2
SQL3602W Check data processing found constraint violations and moved
them to
exception tables. SQLSTATE=01603
audi$ db2 "select * from blah"

COL1
-----------
1
2
3

3 record(s) selected.

audi$ db2 "select * from blah2"

COL1
-----------
4
5
6

3 record(s) selected.

/T

hu*****@gmail.c om wrote:
The following is a question of LOAD utility.
Question:
How does the DB2 enforce table check constraints for data added to
table with the LOAD utility?
A. With the BUILD phase of LOAD
B. With the SET INTEGRITY statement
C. With the DELETE phase of the LOAD
D. With the UPDATE CONSTRAINTS statement

Answer is A

I am confused why the correct answer is A. In BUILD phase, LOAD utility
only maintain index. I think LOAD utility check consistaint in LOAD
phase, not BUILD phase.

Is my thought right?

Thanks

James
Sep 18 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
2175
by: Mark Miller | last post by:
I have a scheduled job that uses different XSL templates to transform XML and save it to disk. I am having problems with the code below. The problem shows up on both my development machine (Windows XP Pro SP 1, .Net Framework 1.1) and on our production server (Windows 2K SP 4, .Net Framework 1.1). I have simplified the code and data to isolate the problem. When I use the xsl:strip-space (Line 12) declaration in conjunction with the xsl:sort...
7
19635
by: Eric.Jones | last post by:
I've encountered a strange error with loading delimited files from a Samba (SMB) network drive, has anyone else seen this before? (Platform: WinXP Pro, UDB PE 8015, level 02060106, SAMPLE db) db2 load from M:\org1.del of del replace into org SQL3109N The utility is beginning to load data from file "M:\org1.del". SQL2036N The path for the file or device "M:\org1.del" is not valid. db2diag.log:
5
2186
by: Konstantin Andreev | last post by:
Recently I became interested, - Are the data, bulk loaded in the table with LOAD utility, consume the same disk space as loaded with IMPORT utility? The answer turned out to be NOT ! Here is a nutshell description of the test. The testing was done at "DB2/LINUX 8.2.3". Tables for tests: F4106 has 5203 rows, 32 columns. F42199 has 1399252 rows, 245 columns.
7
2226
by: clilush | last post by:
I'm trying to fix up a database on my server by transferring all the readable data to a new database, and then upgrading it from db2-7 to db2-8. I'm using the export command to dump the tables that I'm having a hard time with to a file, then running the LOAD command on those exports to load them back into the new clean database table by table. I'm wondering if there is anything that needs to be done after all the LOAD commands are...
0
1954
by: abrahamvk | last post by:
Hi, How to load data into Japanese DB2 Database using DB2 Load Utility, where the table column names are in Japanese in windows environment. We could successfully load Japanese data into a table which has English column names only. The DB2 Load Utility could not understand a table that has column names in Japanese. We tried loading data after setting the following 1. Setting the DB2CodePage to 1208 2. Setting the environment...
25
2906
by: Andy_Khosravi | last post by:
I just recently changed my database that I'm running from a monolithic DB to a split FE/BE. The front end resides on the client machine and the BE resides on a network drive. I'm experimenting with a utility developed by Tony Toews to handle the distribution and subsequent updates of the software. I'm having some trouble with the overall upgrade process I've implemented, and I'm hoping one of you may have an idea how to go about fixing...
2
9403
by: venkateswari | last post by:
Need Information on ‘how to terminate a load utility?’ I am getting the following error while loading a table: DB2 OBJECT DEFINITION(S) HAVE CHANGED PRIOR TO UTILITY RESTART. UNABLE TO PROCEED UTILITY EXECUTION TERMINATING, RETURN CODE = 8
2
3057
by: mike.lopiano | last post by:
There is a significant gap (~ 3.5 minutes) in the time our LOAD command indicates that the BUILD phase is complete and the time we have control returned to our script. I ran the test below on a dedicated system with no other processes running. Our tablespaces are on Veritas filesystems. Any insight/ suggestions welcome... From the LOAD output:
1
2849
by: Tom | last post by:
My unsigned DLL works in my project that references it as long as I set Copy Local = true. Now I have signed the DLL with the sn.exe generated keys but have not yet moved the DLL into the GAC. Can I use a strong named DLL outside the GAC with Copy Local = false by providing a reference to the public key? If yes to above, please tell me how to reference the public key within
0
9957
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9799
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10770
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9593
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7988
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5809
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6010
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4632
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4236
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.