473,395 Members | 1,595 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,395 software developers and data experts.

bulk loading into a table with "not null" column constraints

Hello,

I have a db2load script I'm using to populate a large table.

Ideally, my target table is required to have "Not Null" constraints on
a number of different columns.

I've noticed a huge performance hit when I load the target table with
"Not Null" constraints as compared to loading a target table without
the constraints.

Is there anyway to alleviate some of this performance hit when loading
a target table with "Not Null" constraints through bulk load? I can
confirm with 100% confidence that the source data will never have
nulls, is there any way I can inform the loader that it can overlook
this constraint at bulk load time?

I can't alter the table after load right? As far as I know, DB2
doesn't allow me to add a column not null constraint after the table
is created/populated.

Thanks in advance,

Jun 11 '07 #1
5 6815
On Jun 11, 11:13 am, yeo...@gmail.com wrote:
Hello,

I have a db2load script I'm using to populate a large table.

Ideally, my target table is required to have "Not Null" constraints on
a number of different columns.

I've noticed a huge performance hit when I load the target table with
"Not Null" constraints as compared to loading a target table without
the constraints.

Is there anyway to alleviate some of this performance hit when loading
a target table with "Not Null" constraints through bulk load? I can
confirm with 100% confidence that the source data will never have
nulls, is there any way I can inform the loader that it can overlook
this constraint at bulk load time?

I can't alter the table after load right? As far as I know, DB2
doesn't allow me to add a column not null constraint after the table
is created/populated.

Thanks in advance,
Try MODIFIED BY FASTPARSE.

Jun 11 '07 #2
On Jun 11, 11:25 am, shenan...@gmail.com wrote:
On Jun 11, 11:13 am, yeo...@gmail.com wrote:
Hello,
I have a db2load script I'm using to populate a large table.
Ideally, my target table is required to have "Not Null" constraints on
a number of different columns.
I've noticed a huge performance hit when I load the target table with
"Not Null" constraints as compared to loading a target table without
the constraints.
Is there anyway to alleviate some of this performance hit when loading
a target table with "Not Null" constraints through bulk load? I can
confirm with 100% confidence that the source data will never have
nulls, is there any way I can inform the loader that it can overlook
this constraint at bulk load time?
I can't alter the table after load right? As far as I know, DB2
doesn't allow me to add a column not null constraint after the table
is created/populated.
Thanks in advance,

Try MODIFIED BY FASTPARSE.

I tried with MODIFIED BY FASTPARSE and I still have horrible
performance.

I've done a bit more investigation into the issue;
Our source text file is of type del, and is comma delimited.

The source data typically has "{tab} {tab}", or tab-space-tab type
data.

I think, my load file is confusing this as a null value.

Here is my load file instructions:
OPMODE=insert
DATAFILE=D:\Program Files\Informatica PowerCenter 7.1.2\Server
\User_Share_Prod\TgtFiles\CMS\target_table.out
DATABASE=CMSDEV
ERRLOG=D:\Program Files\Informatica PowerCenter 7.1.2\Server
\User_Share_Prod\TgtFiles\CMS\target_table.out.ldr log
USERID=cmsdb
PASSWORD=passw0rd
TABLENAME=TARGET_SCHEMA.TARGET_TABLE
COLNAMES=ACCTTC, CMNOTC, CMDTTC, TIM4TC, CMCMTC, DATETC, TIMETC,
GMNGTC, PREATC, GRTYTC, JGRCTC, FUNCTC
COLUMNS=12
RECOVERABLE=no
TIMESTAMP=no
FILETYPE=DEL
COLDEL=

(after the COLDEL= there is a tab... you can't see it in this post
however)

Also, I should point out that i'm on DB2 v8.2 fixpack 11, and the
script above (as well as the data extract script) was originally
generated by informatica powercenter 7.1 (An extract/load tool that
generates scripts... )
Any help appreciated.


Jun 11 '07 #3
On Jun 11, 11:25 am, yeoj <yeo...@gmail.comwrote:
On Jun 11, 11:25 am, shenan...@gmail.com wrote:


On Jun 11, 11:13 am, yeo...@gmail.com wrote:
Hello,
I have a db2load script I'm using to populate a large table.
Ideally, my target table is required to have "Not Null" constraints on
a number of different columns.
I've noticed a huge performance hit when I load the target table with
"Not Null" constraints as compared to loading a target table without
the constraints.
Is there anyway to alleviate some of this performance hit when loading
a target table with "Not Null" constraints through bulk load? I can
confirm with 100% confidence that the source data will never have
nulls, is there any way I can inform the loader that it can overlook
this constraint at bulk load time?
I can't alter the table after load right? As far as I know, DB2
doesn't allow me to add a column not null constraint after the table
is created/populated.
Thanks in advance,
Try MODIFIED BY FASTPARSE.

I tried with MODIFIED BY FASTPARSE and I still have horrible
performance.

I've done a bit more investigation into the issue;
Our source text file is of type del, and is comma delimited.

The source data typically has "{tab} {tab}", or tab-space-tab type
data.

I think, my load file is confusing this as a null value.

Here is my load file instructions:
OPMODE=insert
DATAFILE=D:\Program Files\Informatica PowerCenter 7.1.2\Server
\User_Share_Prod\TgtFiles\CMS\target_table.out
DATABASE=CMSDEV
ERRLOG=D:\Program Files\Informatica PowerCenter 7.1.2\Server
\User_Share_Prod\TgtFiles\CMS\target_table.out.ldr log
USERID=cmsdb
PASSWORD=passw0rd
TABLENAME=TARGET_SCHEMA.TARGET_TABLE
COLNAMES=ACCTTC, CMNOTC, CMDTTC, TIM4TC, CMCMTC, DATETC, TIMETC,
GMNGTC, PREATC, GRTYTC, JGRCTC, FUNCTC
COLUMNS=12
RECOVERABLE=no
TIMESTAMP=no
FILETYPE=DEL
COLDEL=

(after the COLDEL= there is a tab... you can't see it in this post
however)

Also, I should point out that i'm on DB2 v8.2 fixpack 11, and the
script above (as well as the data extract script) was originally
generated by informatica powercenter 7.1 (An extract/load tool that
generates scripts... )

Any help appreciated.- Hide quoted text -

- Show quoted text -
On the 2nd question you posted,
you CAN add new column with NOT NULL in db2 provided you add the
<b>default</b keyword with that.
eg., alter table <add name varchar(25) not null default ''
will populate the existing rows with 'nothing'(which is different from
null in db2 world)

For this , can you please copy/paste a row in this thread?
I normally use the ascii load when i have tabs in my input file, and
not the del type.
This has proven to be extremely fast..

Jun 11 '07 #4
Ian
yeoj wrote:
On Jun 11, 11:25 am, shenan...@gmail.com wrote:
>On Jun 11, 11:13 am, yeo...@gmail.com wrote:
>>Hello,
I have a db2load script I'm using to populate a large table.
Ideally, my target table is required to have "Not Null" constraints on
a number of different columns.
I've noticed a huge performance hit when I load the target table with
"Not Null" constraints as compared to loading a target table without
the constraints.
Is there anyway to alleviate some of this performance hit when loading
a target table with "Not Null" constraints through bulk load? I can
confirm with 100% confidence that the source data will never have
nulls, is there any way I can inform the loader that it can overlook
this constraint at bulk load time?
I can't alter the table after load right? As far as I know, DB2
doesn't allow me to add a column not null constraint after the table
is created/populated.
Thanks in advance,
Try MODIFIED BY FASTPARSE.


I tried with MODIFIED BY FASTPARSE and I still have horrible
performance.

I've done a bit more investigation into the issue;
Our source text file is of type del, and is comma delimited.

The source data typically has "{tab} {tab}", or tab-space-tab type
data.

I think, my load file is confusing this as a null value.

Here is my load file instructions:
OPMODE=insert
DATAFILE=D:\Program Files\Informatica PowerCenter 7.1.2\Server
\User_Share_Prod\TgtFiles\CMS\target_table.out
DATABASE=CMSDEV
ERRLOG=D:\Program Files\Informatica PowerCenter 7.1.2\Server
\User_Share_Prod\TgtFiles\CMS\target_table.out.ldr log
USERID=cmsdb
PASSWORD=passw0rd
TABLENAME=TARGET_SCHEMA.TARGET_TABLE
COLNAMES=ACCTTC, CMNOTC, CMDTTC, TIM4TC, CMCMTC, DATETC, TIMETC,
GMNGTC, PREATC, GRTYTC, JGRCTC, FUNCTC
COLUMNS=12
RECOVERABLE=no
TIMESTAMP=no
FILETYPE=DEL
COLDEL=

(after the COLDEL= there is a tab... you can't see it in this post
however)

Also, I should point out that i'm on DB2 v8.2 fixpack 11, and the
script above (as well as the data extract script) was originally
generated by informatica powercenter 7.1 (An extract/load tool that
generates scripts... )
The information above is not for a the DB2 LOAD utility.

Informatica comes with its own utility (called 'db2load') to load
DB2. I believe it uses the CLI Load functionality supported by DB2
(instead of using the db2Load() API), so I wonder if the bottleneck
could be related to to it.

Have you tried loading the file directly with the db2 LOAD utility?
Jun 11 '07 #5
On 11 Giu, 17:13, yeo...@gmail.com wrote:
Hello,

I have a db2load script I'm using to populate a large table.

Ideally, my target table is required to have "Not Null" constraints on
a number of different columns.

I've noticed a huge performance hit when I load the target table with
"Not Null" constraints as compared to loading a target table without
the constraints.

Is there anyway to alleviate some of this performance hit when loading
a target table with "Not Null" constraints through bulk load? I can
confirm with 100% confidence that the source data will never have
nulls, is there any way I can inform the loader that it can overlook
this constraint at bulk load time?

I can't alter the table after load right? As far as I know, DB2
doesn't allow me to add a column not null constraint after the table
is created/populated.

Thanks in advance,
Check also this utility to load data:

DataTime Documentation
http://cam70.sta.uniroma1.it/DataTimeDocumentation/

DataTime Universal (downloadable)
http://cam70.sta.uniroma1.it/DataTimeUniversal/

Jun 11 '07 #6

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

Similar topics

3
by: B. Harwood | last post by:
This is probably a simple question, but I'm stumped. I cannot figure out a way to do the following. For example, say I have two tables - one of used toys and one of broken toys. Say I wanted to...
235
by: napi | last post by:
I think you would agree with me that a C compiler that directly produces Java Byte Code to be run on any JVM is something that is missing to software programmers so far. With such a tool one could...
3
by: olanorm | last post by:
I have a query where one or more of the columns returned is a result from a subquery. These columns get their own alias. I want to filter out the rows containing NULL from the subqueries but it...
13
by: gary | last post by:
Hi, We all know the below codes are dangerous: { int *p = new int; delete p; delete p; } And we also know the compilers do not delete p if p==NULL. So why compilers do not "p = NULL"...
3
by: Branco Medeiros | last post by:
Hi all, Using SQL Server 2000, SP4. I have a table of street names (Rua) whose ids (cod_rua) are foreign keys into a consumer table (Consumidor). It turns out that the "Rua" table has many...
4
by: psyconia | last post by:
Hi, I am trying to run a query which whill pull the data from fields if the are "not empty" however what happens is if any of the records are empty then it does not show that record. I have...
13
by: ThePrinceIsRight | last post by:
I have a problem with using a subquery in MS Access. The purpose of the sub-query is to create a list of people who have had doctor exams in the past 6 months and exclude them from the main query....
1
by: wbosw | last post by:
I created the following Sub to fill a DataSet with the Table named Developer. It binds to the Grid with no problems. Public Sub SearchSkills() Dim sqlcmd3 As SqlCommand = New...
1
by: veaux | last post by:
Question deals with linking tables in queries. I'm not a code writer so use the GUI for all my queries. Table 1 - Master Table 2 - Sub1 Table 3 - Sub 2 All 3 tables have the same key field....
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
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,...
0
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...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

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.