By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,136 Members | 1,205 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,136 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
5 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.