How To Estimate Additional Space for Data Load using Load Utility

Harishkumar .Pathangay

How To Estimate Additional Space for Data Load using Load Utility

hi,
I did  small test case, but finding varying results. it will be helpful to know how are you doing it?

I have a 1GB del file - like comma separated data file - raw data.
if this needs to load into db2 database, it will not consume exactly same space in table space. obviously the data needs to be inside table space pages with internal RIDs, extent map pages, tight packing of data into pages, structure of table, meta data at page level, headers etc...
So you need a little bit more than 1GB. But how much? Is it 5%, 10% or 20% or is there a saturation point?

I have a table with 1080MB with 2 columns and n number of rows. This takes 1134MB in table space pages of 4K each. Here 5% extra space.
 I have another table with 1080 MB with 5 columns and n number of rows. This takes 1286MB in table space pages of 4K each. Here 20% extra space.
I am wondering how to calculate this additional space requirement?
How are you doing this in your environment? You can get all additional space you want, but I want to do a reasonably close calculation when requesting additional space from OS guys.

thanks
harish p

Colin Clayton

How To Estimate Additional Space for Data Load using Load Utility
(in response to Harishkumar .Pathangay)
Harish,

Check out the DB2 Admin Guide “Calculating the space required for a table”, where it describes how to estimate space.

Factors affecting row length include nullability, variable length for example.

Factors affecting number of pages include Page size, PCTFREE and FREEPAGE…

Colin


From: Harishkumar .Pathangay [mailto:[login to unmask email]
Sent: 15 May 2018 12:19
To: [login to unmask email]
Subject: [DB2-L] - How To Estimate Additional Space for Data Load using Load Utility


hi,
I did small test case, but finding varying results. it will be helpful to know how are you doing it?

I have a 1GB del file - like comma separated data file - raw data.
if this needs to load into db2 database, it will not consume exactly same space in table space. obviously the data needs to be inside table space pages with internal RIDs, extent map pages, tight packing of data into pages, structure of table, meta data at page level, headers etc...
So you need a little bit more than 1GB. But how much? Is it 5%, 10% or 20% or is there a saturation point?

I have a table with 1080MB with 2 columns and n number of rows. This takes 1134MB in table space pages of 4K each. Here 5% extra space.
I have another table with 1080 MB with 5 columns and n number of rows. This takes 1286MB in table space pages of 4K each. Here 20% extra space.
I am wondering how to calculate this additional space requirement?
How are you doing this in your environment? You can get all additional space you want, but I want to do a reasonably close calculation when requesting additional space from OS guys.

thanks
harish p

-----End Original Message-----

Larry Jardine

How To Estimate Additional Space for Data Load using Load Utility
(in response to Colin Clayton)
And don’t forget:

- Will you be using compression?

- Space required for indexes
Etc.

Larry Jardine
Aetna

From: Clayton, Colin [mailto:[login to unmask email]
Sent: Tuesday, May 15, 2018 7:53 AM
To: [login to unmask email]
Subject: [EXTERNAL] [DB2-L] - RE: How To Estimate Additional Space for Data Load using Load Utility

**** External Email - Use Caution ****
Harish,

Check out the DB2 Admin Guide “Calculating the space required for a table”, where it describes how to estimate space.

Factors affecting row length include nullability, variable length for example.

Factors affecting number of pages include Page size, PCTFREE and FREEPAGE…

Colin


From: Harishkumar .Pathangay [mailto:[login to unmask email]
Sent: 15 May 2018 12:19
To: [login to unmask email]
Subject: [DB2-L] - How To Estimate Additional Space for Data Load using Load Utility


hi,
I did small test case, but finding varying results. it will be helpful to know how are you doing it?

I have a 1GB del file - like comma separated data file - raw data.
if this needs to load into db2 database, it will not consume exactly same space in table space. obviously the data needs to be inside table space pages with internal RIDs, extent map pages, tight packing of data into pages, structure of table, meta data at page level, headers etc...
So you need a little bit more than 1GB. But how much? Is it 5%, 10% or 20% or is there a saturation point?

I have a table with 1080MB with 2 columns and n number of rows. This takes 1134MB in table space pages of 4K each. Here 5% extra space.
I have another table with 1080 MB with 5 columns and n number of rows. This takes 1286MB in table space pages of 4K each. Here 20% extra space.
I am wondering how to calculate this additional space requirement?
How are you doing this in your environment? You can get all additional space you want, but I want to do a reasonably close calculation when requesting additional space from OS guys.

thanks
harish p

-----End Original Message-----

-----End Original Message-----

This e-mail may contain confidential or privileged information. If you think you have received this e-mail in error, please advise the sender by reply e-mail and then delete this e-mail immediately. Thank you. Aetna

Roland Schock

RE: How To Estimate Additional Space for Data Load using Load Utility
(in response to Harishkumar .Pathangay)

Hi Harish,

as confirmed by the other two. It depends!  ;-)

I have experienced, that exporting data to a DEL file will have some expansion from the table, even if we don't use compression in this table. Writing out a binary stored double into ASCII chars needs more space.

The indexes are not exported. So you will save this space, depending how many indexes you have.

I assume, you want to extrapolate the size needed on disk for an EXPORT/LOAD for a transfer. So I would expect an average 20-30% bloat for the export to have a safe margin.

Cheers

Roland

Harishkumar .Pathangay

How To Estimate Additional Space for Data Load usingLoad Utility
(in response to Roland Schock)
I am talking about import or load into the data base.
Not Export. I am not looking for cross code page platforms [just ignoring code page character expansions or reductions].
I got that 20% in the test case too, but I am afraid that “can it go beyond 30%?”
What will be a good reasonable equation for this?

Thanks, Harish P

Sent from Mail for Windows 10

From: Roland Schock
Sent: 15 May 2018 18:06
To: [login to unmask email]
Subject: [DB2-L] - RE: How To Estimate Additional Space for Data Load usingLoad Utility

Hi Harish,
as confirmed by the other two. It depends!  ;-)
I have experienced, that exporting data to a DEL file will have some expansion from the table, even if we don't use compression in this table. Writing out a binary stored double into ASCII chars needs more space.
The indexes are not exported. So you will save this space, depending how many indexes you have.
I assume, you want to extrapolate the size needed on disk for an EXPORT/LOAD for a transfer. So I would expect an average 20-30% bloat for the export to have a safe margin.
Cheers
Roland


Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
** ** ** IDUG Db2 Tech Conference in Sydney, Australia 2018 ** ** **
---> Sydney, Australia 11 - 13 September, 2018 <---
http://www.idug.org/au

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2


Attachments

  • C19109F50CB940B6AFC3312240741029.png (<1k)

Harishkumar .Pathangay

How To Estimate Additional Space for Data Load usingLoad Utility
(in response to Colin Clayton)
I am looking for importing inside the database and not exporting.
In My Load Command I am using pagefreespace=0 file type modifier for tight packaging inside the pages.No Indexes, just the table space size is what I am looking at.

Will read the Infocenter for space requirements as suggested.
I am not looking for a definitive answer just some thoughts on how you are doing the estimation activity in your environment?

Thanks,
Harish P

Sent from Mail for Windows 10

From: Clayton, Colin
Sent: 15 May 2018 17:23
To: [login to unmask email]
Subject: [DB2-L] - RE: How To Estimate Additional Space for Data Load usingLoad Utility

Harish,

Check out the DB2 Admin Guide “Calculating the space required for a table”, where it describes how to estimate space.

Factors affecting row length include nullability, variable length for example.

Factors affecting number of pages include Page size, PCTFREE and FREEPAGE…

Colin


From: Harishkumar .Pathangay [mailto:[login to unmask email]
Sent: 15 May 2018 12:19
To: [login to unmask email]
Subject: [DB2-L] - How To Estimate Additional Space for Data Load using Load Utility

hi,
I did  small test case, but finding varying results. it will be helpful to know how are you doing it?
I have a 1GB del file - like comma separated data file - raw data.
if this needs to load into db2 database, it will not consume exactly same space in table space. obviously the data needs to be inside table space pages with internal RIDs, extent map pages, tight packing of data into pages, structure of table, meta data at page level, headers etc...
So you need a little bit more than 1GB. But how much? Is it 5%, 10% or 20% or is there a saturation point?
I have a table with 1080MB with 2 columns and n number of rows. This takes 1134MB in table space pages of 4K each. Here 5% extra space.
 I have another table with 1080 MB with 5 columns and n number of rows. This takes 1286MB in table space pages of 4K each. Here 20% extra space.
I am wondering how to calculate this additional space requirement?
How are you doing this in your environment? You can get all additional space you want, but I want to do a reasonably close calculation when requesting additional space from OS guys.
thanks
harish p

-----End Original Message-----


Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
** ** ** IDUG Db2 Tech Conference in Sydney, Australia 2018 ** ** **
---> Sydney, Australia 11 - 13 September, 2018 <---
http://www.idug.org/au

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2


Attachments

  • 6C00B6D938F24BD3B814AC19CDB759FD.png (<1k)

Roy Boxwell

How To Estimate Additional Space for Data Load using Load Utility
(in response to Harishkumar .Pathangay)
Major question is z/OS or LUW ???

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

From: Harishkumar .Pathangay [mailto:[login to unmask email]
Sent: Tuesday, May 15, 2018 1:19 PM
To: [login to unmask email]
Subject: [DB2-L] - How To Estimate Additional Space for Data Load using Load Utility


hi,
I did small test case, but finding varying results. it will be helpful to know how are you doing it?

I have a 1GB del file - like comma separated data file - raw data.
if this needs to load into db2 database, it will not consume exactly same space in table space. obviously the data needs to be inside table space pages with internal RIDs, extent map pages, tight packing of data into pages, structure of table, meta data at page level, headers etc...
So you need a little bit more than 1GB. But how much? Is it 5%, 10% or 20% or is there a saturation point?

I have a table with 1080MB with 2 columns and n number of rows. This takes 1134MB in table space pages of 4K each. Here 5% extra space.
I have another table with 1080 MB with 5 columns and n number of rows. This takes 1286MB in table space pages of 4K each. Here 20% extra space.
I am wondering how to calculate this additional space requirement?
How are you doing this in your environment? You can get all additional space you want, but I want to do a reasonably close calculation when requesting additional space from OS guys.

thanks
harish p

-----End Original Message-----

Harishkumar .Pathangay

How To Estimate Additional Space for Data Load usingLoad Utility
(in response to Roy Boxwell)
Hi,
It is LUW. Sorry for missing the information.

Thanks,
Harish P

Sent from Mail for Windows 10

From: Boxwell, Roy
Sent: 15 May 2018 20:01
To: [login to unmask email]
Subject: [DB2-L] - RE: How To Estimate Additional Space for Data Load usingLoad Utility

Major question is z/OS or LUW ???

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

From: Harishkumar .Pathangay [mailto:[login to unmask email]
Sent: Tuesday, May 15, 2018 1:19 PM
To: [login to unmask email]
Subject: [DB2-L] - How To Estimate Additional Space for Data Load using Load Utility

hi,
I did  small test case, but finding varying results. it will be helpful to know how are you doing it?
I have a 1GB del file - like comma separated data file - raw data.
if this needs to load into db2 database, it will not consume exactly same space in table space. obviously the data needs to be inside table space pages with internal RIDs, extent map pages, tight packing of data into pages, structure of table, meta data at page level, headers etc...
So you need a little bit more than 1GB. But how much? Is it 5%, 10% or 20% or is there a saturation point?
I have a table with 1080MB with 2 columns and n number of rows. This takes 1134MB in table space pages of 4K each. Here 5% extra space.
 I have another table with 1080 MB with 5 columns and n number of rows. This takes 1286MB in table space pages of 4K each. Here 20% extra space.
I am wondering how to calculate this additional space requirement?
How are you doing this in your environment? You can get all additional space you want, but I want to do a reasonably close calculation when requesting additional space from OS guys.
thanks
harish p

-----End Original Message-----


Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
** ** ** IDUG Db2 Tech Conference in Sydney, Australia 2018 ** ** **
---> Sydney, Australia 11 - 13 September, 2018 <---
http://www.idug.org/au

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2


Attachments

  • 855694FBED8D4238ABDF9A531B3EEAD9.png (<1k)

Roy Boxwell

How To Estimate Additional Space for Data Load usingLoad Utility
(in response to Harishkumar .Pathangay)
Very good ! Then i have no idea!

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

From: Harishkumar Pathangay [mailto:[login to unmask email]
Sent: Tuesday, May 15, 2018 4:33 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: How To Estimate Additional Space for Data Load usingLoad Utility

Hi,
It is LUW. Sorry for missing the information.

Thanks,
Harish P

Sent from Mail https://go.microsoft.com/fwlink/?LinkId=550986 for Windows 10

From: Boxwell, Roy<mailto:[login to unmask email]>
Sent: 15 May 2018 20:01
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: How To Estimate Additional Space for Data Load usingLoad Utility

Major question is z/OS or LUW ???

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

From: Harishkumar .Pathangay [mailto:[login to unmask email]
Sent: Tuesday, May 15, 2018 1:19 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - How To Estimate Additional Space for Data Load using Load Utility


hi,
I did small test case, but finding varying results. it will be helpful to know how are you doing it?

I have a 1GB del file - like comma separated data file - raw data.
if this needs to load into db2 database, it will not consume exactly same space in table space. obviously the data needs to be inside table space pages with internal RIDs, extent map pages, tight packing of data into pages, structure of table, meta data at page level, headers etc...
So you need a little bit more than 1GB. But how much? Is it 5%, 10% or 20% or is there a saturation point?

I have a table with 1080MB with 2 columns and n number of rows. This takes 1134MB in table space pages of 4K each. Here 5% extra space.
I have another table with 1080 MB with 5 columns and n number of rows. This takes 1286MB in table space pages of 4K each. Here 20% extra space.
I am wondering how to calculate this additional space requirement?
How are you doing this in your environment? You can get all additional space you want, but I want to do a reasonably close calculation when requesting additional space from OS guys.

thanks
harish p

-----End Original Message-----


-----End Original Message-----



-----End Original Message-----

Harishkumar .Pathangay

How To Estimate Additional Space for Data LoadusingLoad Utility
(in response to Roy Boxwell)
Thank You.


Sent from Mail for Windows 10

From: Boxwell, Roy
Sent: 15 May 2018 20:06
To: [login to unmask email]
Subject: [DB2-L] - RE: How To Estimate Additional Space for Data LoadusingLoad Utility

Very good ! Then i have no idea!

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

From: Harishkumar Pathangay [mailto:[login to unmask email]
Sent: Tuesday, May 15, 2018 4:33 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: How To Estimate Additional Space for Data Load usingLoad Utility

Hi,
It is LUW. Sorry for missing the information.

Thanks,
Harish P

Sent from Mail for Windows 10

From: Boxwell, Roy
Sent: 15 May 2018 20:01
To: [login to unmask email]
Subject: [DB2-L] - RE: How To Estimate Additional Space for Data Load usingLoad Utility

Major question is z/OS or LUW ???

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

From: Harishkumar .Pathangay [mailto:[login to unmask email]
Sent: Tuesday, May 15, 2018 1:19 PM
To: [login to unmask email]
Subject: [DB2-L] - How To Estimate Additional Space for Data Load using Load Utility

hi,
I did  small test case, but finding varying results. it will be helpful to know how are you doing it?
I have a 1GB del file - like comma separated data file - raw data.
if this needs to load into db2 database, it will not consume exactly same space in table space. obviously the data needs to be inside table space pages with internal RIDs, extent map pages, tight packing of data into pages, structure of table, meta data at page level, headers etc...
So you need a little bit more than 1GB. But how much? Is it 5%, 10% or 20% or is there a saturation point?
I have a table with 1080MB with 2 columns and n number of rows. This takes 1134MB in table space pages of 4K each. Here 5% extra space.
 I have another table with 1080 MB with 5 columns and n number of rows. This takes 1286MB in table space pages of 4K each. Here 20% extra space.
I am wondering how to calculate this additional space requirement?
How are you doing this in your environment? You can get all additional space you want, but I want to do a reasonably close calculation when requesting additional space from OS guys.
thanks
harish p

-----End Original Message-----


-----End Original Message-----



-----End Original Message-----


Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
** ** ** IDUG Db2 Tech Conference in Sydney, Australia 2018 ** ** **
---> Sydney, Australia 11 - 13 September, 2018 <---
http://www.idug.org/au

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2


Attachments

  • 1F10450FB0524109A7F515FE9C365023.png (<1k)

Nadir Doctor

How To Estimate Additional Space for Data Load using Load Utility
(in response to Harishkumar .Pathangay)
Hi Harish,

You may also want to consider compression and the newer adaptive
compression available in the 10.5 release and higher to obtain significant
space savings in database.

Nadir


On Tue, May 15, 2018 at 6:19 AM, Harishkumar .Pathangay <
[login to unmask email]> wrote:

> hi,
> I did small test case, but finding varying results. it will be helpful to
> know how are you doing it?
>
> I have a 1GB del file - like comma separated data file - raw data.
> if this needs to load into db2 database, it will not consume exactly same
> space in table space. obviously the data needs to be inside table space
> pages with internal RIDs, extent map pages, tight packing of data into
> pages, structure of table, meta data at page level, headers etc...
> So you need a little bit more than 1GB. But how much? Is it 5%, 10% or 20%
> or is there a saturation point?
>
> I have a table with 1080MB with 2 columns and n number of rows. This takes
> 1134MB in table space pages of 4K each. Here 5% extra space.
> I have another table with 1080 MB with 5 columns and n number of rows.
> This takes 1286MB in table space pages of 4K each. Here 20% extra space.
> I am wondering how to calculate this additional space requirement?
> How are you doing this in your environment? You can get all additional
> space you want, but I want to do a reasonably close calculation when
> requesting additional space from OS guys.
>
> thanks
> harish p
>
> -----End Original Message-----
>

Harishkumar .Pathangay

RE: How To Estimate Additional Space for Data Load using Load Utility
(in response to Nadir Doctor)

hi all,

got the formula from the page.

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0004926.html

 

thanks to colin for steering me the right way.

thanks all,

harish pathangay