Space issue in DB2

Srinivas Gopala

Space issue in DB2
Hi,

We are migrating an application that uses Oracle as Database to DB2.
While the Data size in Oracle is 8 GB , when it is loaded on to DB2 , it uses up almost 28 3390-9 volumes.
The Tablespaces are created using an automated script.

How do I know if the VSAM Datasets Created for the Table Spaces are fully utilized? If the Dataset is over allocated , how do I reclaim the space?

Is Alter Tablespace followed by REORG the only alternative?

Regards,
Srinivas G

**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of the addressee(s). If you are not the intended recipient, please
notify the sender by e-mail and delete the original message. Further, you are not
to copy, disclose, or distribute this e-mail or its contents to any other person and
any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
every reasonable precaution to minimize this risk, but is not liable for any damage
you may sustain as a result of any virus in this e-mail. You should carry out your
own virus checks before opening the e-mail or attachment. Infosys reserves the
right to monitor and review the content of all messages sent to or from this e-mail
address. Messages sent to or from this e-mail address may be stored on the
Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Patrick Steurs

Re: Space issue in DB2
(in response to Srinivas Gopala)
Hi,



Don't use space-parameters in db2. Let SMS do the job and allocate your vsam with primary & secondary quantity equal to -1



greetings,



Patrick



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: dinsdag 24 november 2009 16:03
To: [login to unmask email]
Subject: [DB2-L] Space issue in DB2



Hi,



We are migrating an application that uses Oracle as Database to DB2.

While the Data size in Oracle is 8 GB , when it is loaded on to DB2 , it uses up almost 28 3390-9 volumes.

The Tablespaces are created using an automated script.



How do I know if the VSAM Datasets Created for the Table Spaces are fully utilized? If the Dataset is over allocated , how do I reclaim the space?



Is Alter Tablespace followed by REORG the only alternative?



Regards,

Srinivas G

**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of the addressee(s). If you are not the intended recipient, please
notify the sender by e-mail and delete the original message. Further, you are not
to copy, disclose, or distribute this e-mail or its contents to any other person and
any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
every reasonable precaution to minimize this risk, but is not liable for any damage
you may sustain as a result of any virus in this e-mail. You should carry out your
own virus checks before opening the e-mail or attachment. Infosys reserves the
right to monitor and review the content of all messages sent to or from this e-mail
address. Messages sent to or from this e-mail address may be stored on the
Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***



________________________________

IDUG - The Worldwide DB2 User Community! < http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >



-----------------------------------------
Visit our website! http://www.nbb.be

"DISCLAIMER: The content of this e-mail message should not be
construed as binding on the part of the National Bank of Belgium
(NBB) unless otherwise and previously stated. The opinions
expressed in this message are solely those of the author and do not
necessarily reflect NBB viewpoints, particularly when the content
of this message, or part thereof, is private by nature or does not
fall within the professional scope of its author."

Srinivas Gopala

Re: Space issue in DB2
(in response to Patrick Steurs)
Thanks Patrick. I will try that.

Actually , I create SMS Routines and place Datasets on Certain storage groups based on VCAT qualifiers.

The scripts are run as follows :
using VCAT and create STOGROUP stogroup-name Volume(‘*’) VCAT vcatname.

Is this fine? So now I give primary and secondary equal to -1 ?

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Steurs Patrick
Sent: Tuesday, November 24, 2009 8:58 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2

Hi,

Don't use space-parameters in db2. Let SMS do the job and allocate your vsam with primary & secondary quantity equal to -1

greetings,

Patrick

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: dinsdag 24 november 2009 16:03
To: [login to unmask email]
Subject: [DB2-L] Space issue in DB2

Hi,

We are migrating an application that uses Oracle as Database to DB2.
While the Data size in Oracle is 8 GB , when it is loaded on to DB2 , it uses up almost 28 3390-9 volumes.
The Tablespaces are created using an automated script.

How do I know if the VSAM Datasets Created for the Table Spaces are fully utilized? If the Dataset is over allocated , how do I reclaim the space?

Is Alter Tablespace followed by REORG the only alternative?

Regards,
Srinivas G

**************** CAUTION - Disclaimer *****************

This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely

for the use of the addressee(s). If you are not the intended recipient, please

notify the sender by e-mail and delete the original message. Further, you are not

to copy, disclose, or distribute this e-mail or its contents to any other person and

any such actions are unlawful. This e-mail may contain viruses. Infosys has taken

every reasonable precaution to minimize this risk, but is not liable for any damage

you may sustain as a result of any virus in this e-mail. You should carry out your

own virus checks before opening the e-mail or attachment. Infosys reserves the

right to monitor and review the content of all messages sent to or from this e-mail

address. Messages sent to or from this e-mail address may be stored on the

Infosys e-mail system.

***INFOSYS******** End of Disclaimer ********INFOSYS***


________________________________

[ http://www.idug.org/images/M_images/idug%20na3.jpg ] < http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

Visit our website! http://www.nbb.be "DISCLAIMER: The content of this e-mail message should not be construed as binding on the part of the National Bank of Belgium (NBB) unless otherwise and previously stated. The opinions expressed in this message are solely those of the author and do not necessarily reflect NBB viewpoints, particularly when the content of this message, or part thereof, is private by nature or does not fall within the professional scope of its author."

Patrick Steurs

Re: Space issue in DB2
(in response to Srinivas Gopala)
ok, but don't forget to adapt the zparm MGEXTSZ to the value "YES" + compile the DSNTIJUZ-module.



greetings,



Patrick Steurs



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: dinsdag 24 november 2009 17:14
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2



Thanks Patrick. I will try that.



Actually , I create SMS Routines and place Datasets on Certain storage groups based on VCAT qualifiers.



The scripts are run as follows :

using VCAT and create STOGROUP stogroup-name Volume(‘*’) VCAT vcatname.



Is this fine? So now I give primary and secondary equal to -1 ?



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Steurs Patrick
Sent: Tuesday, November 24, 2009 8:58 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2



Hi,



Don't use space-parameters in db2. Let SMS do the job and allocate your vsam with primary & secondary quantity equal to -1



greetings,



Patrick



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: dinsdag 24 november 2009 16:03
To: [login to unmask email]
Subject: [DB2-L] Space issue in DB2



Hi,



We are migrating an application that uses Oracle as Database to DB2.

While the Data size in Oracle is 8 GB , when it is loaded on to DB2 , it uses up almost 28 3390-9 volumes.

The Tablespaces are created using an automated script.



How do I know if the VSAM Datasets Created for the Table Spaces are fully utilized? If the Dataset is over allocated , how do I reclaim the space?



Is Alter Tablespace followed by REORG the only alternative?



Regards,

Srinivas G

**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of the addressee(s). If you are not the intended recipient, please
notify the sender by e-mail and delete the original message. Further, you are not
to copy, disclose, or distribute this e-mail or its contents to any other person and
any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
every reasonable precaution to minimize this risk, but is not liable for any damage
you may sustain as a result of any virus in this e-mail. You should carry out your
own virus checks before opening the e-mail or attachment. Infosys reserves the
right to monitor and review the content of all messages sent to or from this e-mail
address. Messages sent to or from this e-mail address may be stored on the
Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***



________________________________

IDUG - The Worldwide DB2 User Community! < http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

Visit our website! http://www.nbb.be "DISCLAIMER: The content of this e-mail message should not be construed as binding on the part of the National Bank of Belgium (NBB) unless otherwise and previously stated. The opinions expressed in this message are solely those of the author and do not necessarily reflect NBB viewpoints, particularly when the content of this message, or part thereof, is private by nature or does not fall within the professional scope of its author."



-----------------------------------------
Visit our website! http://www.nbb.be

"DISCLAIMER: The content of this e-mail message should not be
construed as binding on the part of the National Bank of Belgium
(NBB) unless otherwise and previously stated. The opinions
expressed in this message are solely those of the author and do not
necessarily reflect NBB viewpoints, particularly when the content
of this message, or part thereof, is private by nature or does not
fall within the professional scope of its author."

Steen Rasmussen

Re: Space issue in DB2
(in response to Patrick Steurs)
Using negative one for your allocation will make sure you don’t run out of extents until you hit the limit. You might get to a point where the secondary extent is so big that you waste some space (hence the name Sliding Scheme).

Since you seem to use a lot more DASD compared to Oracle – did you compress the objects (COMPRESS YES) in the DDL ?



Steen Rasmussen
CA

Sr Engineering Services Architect

IBM Certified Database Associate - DB2 9 Fundamentals

IBM Certified Database Administrator - DB2 9 DBA for z/OS





From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: Tuesday, November 24, 2009 10:14 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2



Thanks Patrick. I will try that.



Actually , I create SMS Routines and place Datasets on Certain storage groups based on VCAT qualifiers.



The scripts are run as follows :

using VCAT and create STOGROUP stogroup-name Volume(‘*’) VCAT vcatname.



Is this fine? So now I give primary and secondary equal to -1 ?



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Steurs Patrick
Sent: Tuesday, November 24, 2009 8:58 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2



Hi,



Don't use space-parameters in db2. Let SMS do the job and allocate your vsam with primary & secondary quantity equal to -1



greetings,



Patrick



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: dinsdag 24 november 2009 16:03
To: [login to unmask email]
Subject: [DB2-L] Space issue in DB2



Hi,



We are migrating an application that uses Oracle as Database to DB2.

While the Data size in Oracle is 8 GB , when it is loaded on to DB2 , it uses up almost 28 3390-9 volumes.

The Tablespaces are created using an automated script.



How do I know if the VSAM Datasets Created for the Table Spaces are fully utilized? If the Dataset is over allocated , how do I reclaim the space?



Is Alter Tablespace followed by REORG the only alternative?



Regards,

Srinivas G

**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of the addressee(s). If you are not the intended recipient, please
notify the sender by e-mail and delete the original message. Further, you are not
to copy, disclose, or distribute this e-mail or its contents to any other person and
any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
every reasonable precaution to minimize this risk, but is not liable for any damage
you may sustain as a result of any virus in this e-mail. You should carry out your
own virus checks before opening the e-mail or attachment. Infosys reserves the
right to monitor and review the content of all messages sent to or from this e-mail
address. Messages sent to or from this e-mail address may be stored on the
Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***



________________________________

IDUG - The Worldwide DB2 User Community! < http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

Visit our website! http://www.nbb.be "DISCLAIMER: The content of this e-mail message should not be construed as binding on the part of the National Bank of Belgium (NBB) unless otherwise and previously stated. The opinions expressed in this message are solely those of the author and do not necessarily reflect NBB viewpoints, particularly when the content of this message, or part thereof, is private by nature or does not fall within the professional scope of its author."

Srinivas Gopala

Re: Space issue in DB2
(in response to Steen Rasmussen)
Thanks a lot.

I will try this solution. But it will take time to implement since we have to make changes to all the scripts. We have 5+ subsystems.

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Steurs Patrick
Sent: Tuesday, November 24, 2009 10:03 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2

ok, but don't forget to adapt the zparm MGEXTSZ to the value "YES" + compile the DSNTIJUZ-module.

greetings,

Patrick Steurs

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: dinsdag 24 november 2009 17:14
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2

Thanks Patrick. I will try that.

Actually , I create SMS Routines and place Datasets on Certain storage groups based on VCAT qualifiers.

The scripts are run as follows :
using VCAT and create STOGROUP stogroup-name Volume(‘*’) VCAT vcatname.

Is this fine? So now I give primary and secondary equal to -1 ?

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Steurs Patrick
Sent: Tuesday, November 24, 2009 8:58 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2

Hi,

Don't use space-parameters in db2. Let SMS do the job and allocate your vsam with primary & secondary quantity equal to -1

greetings,

Patrick

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: dinsdag 24 november 2009 16:03
To: [login to unmask email]
Subject: [DB2-L] Space issue in DB2

Hi,

We are migrating an application that uses Oracle as Database to DB2.
While the Data size in Oracle is 8 GB , when it is loaded on to DB2 , it uses up almost 28 3390-9 volumes.
The Tablespaces are created using an automated script.

How do I know if the VSAM Datasets Created for the Table Spaces are fully utilized? If the Dataset is over allocated , how do I reclaim the space?

Is Alter Tablespace followed by REORG the only alternative?

Regards,
Srinivas G

**************** CAUTION - Disclaimer *****************

This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely

for the use of the addressee(s). If you are not the intended recipient, please

notify the sender by e-mail and delete the original message. Further, you are not

to copy, disclose, or distribute this e-mail or its contents to any other person and

any such actions are unlawful. This e-mail may contain viruses. Infosys has taken

every reasonable precaution to minimize this risk, but is not liable for any damage

you may sustain as a result of any virus in this e-mail. You should carry out your

own virus checks before opening the e-mail or attachment. Infosys reserves the

right to monitor and review the content of all messages sent to or from this e-mail

address. Messages sent to or from this e-mail address may be stored on the

Infosys e-mail system.

***INFOSYS******** End of Disclaimer ********INFOSYS***


________________________________

[ http://www.idug.org/images/M_images/idug%20na3.jpg ] < http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

Visit our website! http://www.nbb.be "DISCLAIMER: The content of this e-mail message should not be construed as binding on the part of the National Bank of Belgium (NBB) unless otherwise and previously stated. The opinions expressed in this message are solely those of the author and do not necessarily reflect NBB viewpoints, particularly when the content of this message, or part thereof, is private by nature or does not fall within the professional scope of its author."

________________________________

Visit our website! http://www.nbb.be "DISCLAIMER: The content of this e-mail message should not be construed as binding on the part of the National Bank of Belgium (NBB) unless otherwise and previously stated. The opinions expressed in this message are solely those of the author and do not necessarily reflect NBB viewpoints, particularly when the content of this message, or part thereof, is private by nature or does not fall within the professional scope of its author."

Gregory Palgrave

Re: Space issue in DB2
(in response to Srinivas Gopala)
Once upon a time I would have known how big a 3390-9 was, but I have to admit I needed to look it up this time – roughly 8.5 GB.

That’s a huge difference in space usage from the Oracle db, and it makes me wonder if possibly the Oracle db is using row/table compression and DB2 is not?

Are the data types for the tables comparable (i.e. VARCHAR to VARCHAR, not VARCHAR to CHAR etc)?
How is the data being unloaded/converted from Oracle? I have seen code in the past that would unload and pad varchars with spaces up to their maximum length and other silly stuff. There could be something similar happening.

Cheers

Greg

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: Tuesday, 24 November 2009 11:03 PM
To: [login to unmask email]
Subject: [DB2-L] Space issue in DB2

Hi,

We are migrating an application that uses Oracle as Database to DB2.
While the Data size in Oracle is 8 GB , when it is loaded on to DB2 , it uses up almost 28 3390-9 volumes.
The Tablespaces are created using an automated script.

How do I know if the VSAM Datasets Created for the Table Spaces are fully utilized? If the Dataset is over allocated , how do I reclaim the space?

Is Alter Tablespace followed by REORG the only alternative?

Regards,
Srinivas G

Patrick Steurs

Re: Space issue in DB2
(in response to Gregory Palgrave)
Hi,



Compression is also a nice feature. I use it only for tablespaces larger than 1 Gigabyte and when the space gained > 30% . ( see compression report )



Greetings,



Patrick Steurs



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Rasmussen, Steen
Sent: dinsdag 24 november 2009 17:37
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2



Using negative one for your allocation will make sure you don’t run out of extents until you hit the limit. You might get to a point where the secondary extent is so big that you waste some space (hence the name Sliding Scheme).

Since you seem to use a lot more DASD compared to Oracle – did you compress the objects (COMPRESS YES) in the DDL ?



Steen Rasmussen
CA

Sr Engineering Services Architect

IBM Certified Database Associate - DB2 9 Fundamentals

IBM Certified Database Administrator - DB2 9 DBA for z/OS





From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: Tuesday, November 24, 2009 10:14 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2



Thanks Patrick. I will try that.



Actually , I create SMS Routines and place Datasets on Certain storage groups based on VCAT qualifiers.



The scripts are run as follows :

using VCAT and create STOGROUP stogroup-name Volume(‘*’) VCAT vcatname.



Is this fine? So now I give primary and secondary equal to -1 ?



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Steurs Patrick
Sent: Tuesday, November 24, 2009 8:58 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2



Hi,



Don't use space-parameters in db2. Let SMS do the job and allocate your vsam with primary & secondary quantity equal to -1



greetings,



Patrick



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: dinsdag 24 november 2009 16:03
To: [login to unmask email]
Subject: [DB2-L] Space issue in DB2



Hi,



We are migrating an application that uses Oracle as Database to DB2.

While the Data size in Oracle is 8 GB , when it is loaded on to DB2 , it uses up almost 28 3390-9 volumes.

The Tablespaces are created using an automated script.



How do I know if the VSAM Datasets Created for the Table Spaces are fully utilized? If the Dataset is over allocated , how do I reclaim the space?



Is Alter Tablespace followed by REORG the only alternative?



Regards,

Srinivas G

**************** CAUTION - Disclaimer *****************
This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
for the use of the addressee(s). If you are not the intended recipient, please
notify the sender by e-mail and delete the original message. Further, you are not
to copy, disclose, or distribute this e-mail or its contents to any other person and
any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
every reasonable precaution to minimize this risk, but is not liable for any damage
you may sustain as a result of any virus in this e-mail. You should carry out your
own virus checks before opening the e-mail or attachment. Infosys reserves the
right to monitor and review the content of all messages sent to or from this e-mail
address. Messages sent to or from this e-mail address may be stored on the
Infosys e-mail system.
***INFOSYS******** End of Disclaimer ********INFOSYS***



________________________________

IDUG - The Worldwide DB2 User Community! < http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

________________________________

Visit our website! http://www.nbb.be "DISCLAIMER: The content of this e-mail message should not be construed as binding on the part of the National Bank of Belgium (NBB) unless otherwise and previously stated. The opinions expressed in this message are solely those of the author and do not necessarily reflect NBB viewpoints, particularly when the content of this message, or part thereof, is private by nature or does not fall within the professional scope of its author."



-----------------------------------------
Visit our website! http://www.nbb.be

"DISCLAIMER: The content of this e-mail message should not be
construed as binding on the part of the National Bank of Belgium
(NBB) unless otherwise and previously stated. The opinions
expressed in this message are solely those of the author and do not
necessarily reflect NBB viewpoints, particularly when the content
of this message, or part thereof, is private by nature or does not
fall within the professional scope of its author."

Srinivas Gopala

Re: Space issue in DB2
(in response to Patrick Steurs)
I have it confirmed. Compression was disabled in Oracle at all levels. I am clueless..

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Gregory Palgrave
Sent: Wednesday, November 25, 2009 6:47 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2

Once upon a time I would have known how big a 3390-9 was, but I have to admit I needed to look it up this time – roughly 8.5 GB.

That’s a huge difference in space usage from the Oracle db, and it makes me wonder if possibly the Oracle db is using row/table compression and DB2 is not?

Are the data types for the tables comparable (i.e. VARCHAR to VARCHAR, not VARCHAR to CHAR etc)?
How is the data being unloaded/converted from Oracle? I have seen code in the past that would unload and pad varchars with spaces up to their maximum length and other silly stuff. There could be something similar happening.

Cheers

Greg

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: Tuesday, 24 November 2009 11:03 PM
To: [login to unmask email]
Subject: [DB2-L] Space issue in DB2

Hi,

We are migrating an application that uses Oracle as Database to DB2.
While the Data size in Oracle is 8 GB , when it is loaded on to DB2 , it uses up almost 28 3390-9 volumes.
The Tablespaces are created using an automated script.

How do I know if the VSAM Datasets Created for the Table Spaces are fully utilized? If the Dataset is over allocated , how do I reclaim the space?

Is Alter Tablespace followed by REORG the only alternative?

Regards,
Srinivas G

Patrick Steurs

Re: Space issue in DB2
(in response to Srinivas Gopala)
Hi,



What is your default encoding scheme in db2 ? Verify this in your DSNHDECP-job.



greetings,



Patrick



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: woensdag 25 november 2009 14:21
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2



I have it confirmed. Compression was disabled in Oracle at all levels. I am clueless..



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Gregory Palgrave
Sent: Wednesday, November 25, 2009 6:47 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2



Once upon a time I would have known how big a 3390-9 was, but I have to admit I needed to look it up this time – roughly 8.5 GB.



That’s a huge difference in space usage from the Oracle db, and it makes me wonder if possibly the Oracle db is using row/table compression and DB2 is not?



Are the data types for the tables comparable (i.e. VARCHAR to VARCHAR, not VARCHAR to CHAR etc)?

How is the data being unloaded/converted from Oracle? I have seen code in the past that would unload and pad varchars with spaces up to their maximum length and other silly stuff. There could be something similar happening.



Cheers



Greg



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: Tuesday, 24 November 2009 11:03 PM
To: [login to unmask email]
Subject: [DB2-L] Space issue in DB2



Hi,



We are migrating an application that uses Oracle as Database to DB2.

While the Data size in Oracle is 8 GB , when it is loaded on to DB2 , it uses up almost 28 3390-9 volumes.

The Tablespaces are created using an automated script.



How do I know if the VSAM Datasets Created for the Table Spaces are fully utilized? If the Dataset is over allocated , how do I reclaim the space?



Is Alter Tablespace followed by REORG the only alternative?



Regards,

Srinivas G



-----------------------------------------
Visit our website! http://www.nbb.be

"DISCLAIMER: The content of this e-mail message should not be
construed as binding on the part of the National Bank of Belgium
(NBB) unless otherwise and previously stated. The opinions
expressed in this message are solely those of the author and do not
necessarily reflect NBB viewpoints, particularly when the content
of this message, or part thereof, is private by nature or does not
fall within the professional scope of its author."

Mick P Graley

Re: Space issue in DB2
(in response to Patrick Steurs)
Hi Srinivas,

To see if your VSAM clusters are over-allocated do a listcat and check that
the HI-A-RBA and HI-U-RBA are similar. If HI-A-RBA is much larger than
HI-U-RBA then your PRIQTY is probably way too big.

Also as I remember (it's a long time ago) in Oracle all CHARs are stored as
VARCHARs internally. This could make a big difference if all the columns
are defined as CHAR in Oracle and DB2 if they have a lot of blank padding
at the end.

Cheers,

Mick.




From: Steurs Patrick <[login to unmask email]>

To: [login to unmask email]

Date: 25/11/2009 13:46

Subject: Re: [DB2-L] Space issue in DB2






Hi,

What is your default encoding scheme in db2 ? Verify this in your
DSNHDECP-job.

greetings,

Patrick

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: woensdag 25 november 2009 14:21
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2

I have it confirmed. Compression was disabled in Oracle at all levels. I am
clueless..

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Gregory Palgrave
Sent: Wednesday, November 25, 2009 6:47 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2:

Once upon a time I would have known how big a 3390-9 was, but I have to
admit I needed to look it up this time – roughly 8.5 GB.

That’s a huge’ difference in space usage from the Oracle db, and it makes
me wonder if possibly the Oracle db is using row/table compression and DB2
is not?

Are the data types for the tables comparable (i.e. VARCHAR to VARCHAR, not
VARCHAR to CHAR etc)?
How is the data being unloaded/converted from Oracle? I have seen code in
the past that would unload and pad varchars with spaces up to their maximum
length and other silly stuff. There could be something similar happening.

Cheers

Greg

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: Tuesday, 24 November 2009 11:03 PM
To: [login to unmask email]
Subject: [DB2-L] Space issue in DB2

Hi,

We are migrating an application that uses Oracle as Database to DB2.
While the Data size in Oracle is 8 GB , when it is loaded on to DB2 , it
uses up almost 28 3390-9 volumes.
The Tablespaces are created using an automated script.

How do I know if the VSAM Datasets Created for the Table Spaces are fully
utilized? If the Dataset is over allocated , how do I reclaim the space?

Is Alter Tablespace followed by REORG the only alternative?

Regards,
Srinivas G





Visit our website! http://www.nbb.be! "DISCLAIMER: The content of this
e-mail message should not be construed as binding on the part of the
National Bank of Belgium (NBB) unless otherwise and previously stated. The
opinions expressed in this message are solely those of the author and do
not necessarily reflect NBB viewpoints, particularly when the content of
this message, or part thereof, is private by nature or does not fall within
the professional scope of its author."




Wayne Campbell

Re: Space issue in DB2
(in response to Mick P Graley)
A couple of things I would look at besides VARCHARs being packed with spaces are in the TableSpace definition the FREEPAGE, PCTFREE, and MAXROWS. MAXROWS controls how many rows will be put on a page. I’ve recently received some scripts from IBM that had MAXROWS set to 1. It came from the Server side of the house and not the Mainframe. I assumed that they were worried about lock contention, so a page lock would only lock one row. It would be a lot better to switch to row level locking for the table. The row length was around 100 characters and using a 4k page size. Set the MAXROWS to its max (255), Set PCTFREE to 5 or 10 percent to start, and FREEPAGE to 0. Adjust PCTFREE and FREEPAGE later after analyzing how the table space is getting used. If you have large rows, you might also look at using a larger page size to use the space more efficiently.

Wayne Campbell
DB2 DBA
Administrative Office of the Courts
(360) 705-5268
Email: [login to unmask email]
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: Wednesday, November 25, 2009 5:21 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2

I have it confirmed. Compression was disabled in Oracle at all levels. I am clueless..

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Gregory Palgrave
Sent: Wednesday, November 25, 2009 6:47 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2

Once upon a time I would have known how big a 3390-9 was, but I have to admit I needed to look it up this time – roughly 8.5 GB.

That’s a huge difference in space usage from the Oracle db, and it makes me wonder if possibly the Oracle db is using row/table compression and DB2 is not?

Are the data types for the tables comparable (i.e. VARCHAR to VARCHAR, not VARCHAR to CHAR etc)?
How is the data being unloaded/converted from Oracle? I have seen code in the past that would unload and pad varchars with spaces up to their maximum length and other silly stuff. There could be something similar happening.

Cheers

Greg

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: Tuesday, 24 November 2009 11:03 PM
To: [login to unmask email]
Subject: [DB2-L] Space issue in DB2

Hi,

We are migrating an application that uses Oracle as Database to DB2.
While the Data size in Oracle is 8 GB , when it is loaded on to DB2 , it uses up almost 28 3390-9 volumes.
The Tablespaces are created using an automated script.

How do I know if the VSAM Datasets Created for the Table Spaces are fully utilized? If the Dataset is over allocated , how do I reclaim the space?

Is Alter Tablespace followed by REORG the only alternative?

Regards,
Srinivas G

Philip Sevetson

Re: Space issue in DB2
(in response to Wayne Campbell)
Wayne,

Good catch. I wouldn't have thought of that until late in the process.

--Phil Sevetson

________________________________
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Campbell, Wayne
Sent: Wednesday, November 25, 2009 5:28 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2

A couple of things I would look at besides VARCHARs being packed with spaces are in the TableSpace definition the FREEPAGE, PCTFREE, and MAXROWS. MAXROWS controls how many rows will be put on a page. I've recently received some scripts from IBM that had MAXROWS set to 1. It came from the Server side of the house and not the Mainframe. I assumed that they were worried about lock contention, so a page lock would only lock one row. It would be a lot better to switch to row level locking for the table. The row length was around 100 characters and using a 4k page size. Set the MAXROWS to its max (255), Set PCTFREE to 5 or 10 percent to start, and FREEPAGE to 0. Adjust PCTFREE and FREEPAGE later after analyzing how the table space is getting used. If you have large rows, you might also look at using a larger page size to use the space more efficiently.

Wayne Campbell
DB2 DBA
Administrative Office of the Courts
(360) 705-5268
Email: [login to unmask email]
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: Wednesday, November 25, 2009 5:21 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2

I have it confirmed. Compression was disabled in Oracle at all levels. I am clueless..

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Gregory Palgrave
Sent: Wednesday, November 25, 2009 6:47 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2

Once upon a time I would have known how big a 3390-9 was, but I have to admit I needed to look it up this time - roughly 8.5 GB.

That's a huge difference in space usage from the Oracle db, and it makes me wonder if possibly the Oracle db is using row/table compression and DB2 is not?

Are the data types for the tables comparable (i.e. VARCHAR to VARCHAR, not VARCHAR to CHAR etc)?
How is the data being unloaded/converted from Oracle? I have seen code in the past that would unload and pad varchars with spaces up to their maximum length and other silly stuff. There could be something similar happening.

Cheers

Greg

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: Tuesday, 24 November 2009 11:03 PM
To: [login to unmask email]
Subject: [DB2-L] Space issue in DB2

Hi,

We are migrating an application that uses Oracle as Database to DB2.
While the Data size in Oracle is 8 GB , when it is loaded on to DB2 , it uses up almost 28 3390-9 volumes.
The Tablespaces are created using an automated script.

How do I know if the VSAM Datasets Created for the Table Spaces are fully utilized? If the Dataset is over allocated , how do I reclaim the space?

Is Alter Tablespace followed by REORG the only alternative?

Regards,
Srinivas G

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Srinivas Gopala

Re: Space issue in DB2
(in response to Philip Sevetson)
Hi Patrick,

The Default encoding is UNICODE.

AGCCSID=65534,
SCCSID=1047,
MCCSID=65534,
GCCSID=65534,
USCCSID=367,
UMCCSID=1208,
UGCCSID=1200,
ENSCHEME=UNICODE,
APPENSCH=UNICODE,


And if I specify

PRIQTY -1
SECQTY -1

Will that be ok? Since TSQTY is 7200 and IXQTY is also 7200 and MGEXTSZ=YES is in effect currently.

I am also planning to give COMPRESS YES and DEFINE NO.

In fact the current Create Tablespace statement reads like the one below. I was created by the programmers. I could not check it before since the setup here is a bit complicated. But now they have come back to me since they are having performance issues.

CREATE TABLESPACE ACS001
IN FCRMTR01
USING STOGROUP CRM_TRAN01
PRIQTY 3600
SECQTY 1800
ERASE NO
FREEPAGE 5
PCTFREE 10
DEFINE YES
LOGGED
TRACKMOD NO
SEGSIZE 16
MAXPARTITIONS 20
DSSIZE 4G
BUFFERPOOL BP32K
LOCKSIZE PAGE
LOCKMAX SYSTEM
COMPRESS NO
CLOSE NO !

The SPACE field for this Tablespace in SYSTABLESPACE is 4224

I am thinking the MAXPARTITIONS and DSSIZE is not needed.

Regards,
Srinivas G

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Steurs Patrick
Sent: Wednesday, November 25, 2009 7:13 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2

Hi,

What is your default encoding scheme in db2 ? Verify this in your DSNHDECP-job.

greetings,

Patrick

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: woensdag 25 november 2009 14:21
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2

I have it confirmed. Compression was disabled in Oracle at all levels. I am clueless..

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Gregory Palgrave
Sent: Wednesday, November 25, 2009 6:47 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2

Once upon a time I would have known how big a 3390-9 was, but I have to admit I needed to look it up this time – roughly 8.5 GB.

That’s a huge difference in space usage from the Oracle db, and it makes me wonder if possibly the Oracle db is using row/table compression and DB2 is not?

Are the data types for the tables comparable (i.e. VARCHAR to VARCHAR, not VARCHAR to CHAR etc)?
How is the data being unloaded/converted from Oracle? I have seen code in the past that would unload and pad varchars with spaces up to their maximum length and other silly stuff. There could be something similar happening.

Cheers

Greg

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: Tuesday, 24 November 2009 11:03 PM
To: [login to unmask email]
Subject: [DB2-L] Space issue in DB2

Hi,

We are migrating an application that uses Oracle as Database to DB2.
While the Data size in Oracle is 8 GB , when it is loaded on to DB2 , it uses up almost 28 3390-9 volumes.
The Tablespaces are created using an automated script.

How do I know if the VSAM Datasets Created for the Table Spaces are fully utilized? If the Dataset is over allocated , how do I reclaim the space?

Is Alter Tablespace followed by REORG the only alternative?

Regards,
Srinivas G
________________________________

Visit our website! http://www.nbb.be "DISCLAIMER: The content of this e-mail message should not be construed as binding on the part of the National Bank of Belgium (NBB) unless otherwise and previously stated. The opinions expressed in this message are solely those of the author and do not necessarily reflect NBB viewpoints, particularly when the content of this message, or part thereof, is private by nature or does not fall within the professional scope of its author."

Isaac Yassin

Re: Space issue in DB2
(in response to Srinivas Gopala)
Hi

Your BP32k may be too small and this may cause performance
problems

Isaac Yassin

---- Original message ----
>Date:   Thu, 26 Nov 2009 10:02:30 +0530
>From:   SrinivasG <[login to unmask email]>
>Subject:   Re: [DB2-L] Space issue in DB2
>To:   [login to unmask email]
>
> Hi Patrick,
>
>
>
> The Default encoding is UNICODE.
>
>
>
> AGCCSID=65534,
>
> SCCSID=1047,
>
> MCCSID=65534,
>
> GCCSID=65534,
>
> USCCSID=367,
>
> UMCCSID=1208,
>
> UGCCSID=1200,
>
> ENSCHEME=UNICODE,
>
> APPENSCH=UNICODE,
>
>
>
>
>
> And if I specify
>
>
>
> PRIQTY -1
>
>  SECQTY -1
>
>
>
> Will that be ok? Since TSQTY is 7200 and IXQTY is
> also 7200 and MGEXTSZ=YES is in effect currently.
>
>
>
> I am also planning to give COMPRESS YES and DEFINE
> NO.
>
>
>
> In fact the current Create Tablespace statement
> reads like the one below. I was created by the
> programmers. I could not check it before since the
> setup here is a bit complicated. But now they have
> come back to me since they are having performance
> issues.
>
>
>
> CREATE TABLESPACE ACS001
>
>     IN FCRMTR01
>
>     USING STOGROUP CRM_TRAN01
>
>     PRIQTY 3600
>
>     SECQTY 1800
>
>     ERASE NO
>
>     FREEPAGE 5
>
>     PCTFREE 10
>
>     DEFINE YES
>
>     LOGGED
>
>     TRACKMOD NO
>
>     SEGSIZE 16
>
>     MAXPARTITIONS 20
>
>     DSSIZE 4G
>
>     BUFFERPOOL BP32K
>
>     LOCKSIZE PAGE
>
>     LOCKMAX SYSTEM
>
>     COMPRESS NO
>
>     CLOSE NO !
>
>
>
> The SPACE field for this Tablespace in SYSTABLESPACE
> is 4224
>
>
>
> I am thinking the MAXPARTITIONS and DSSIZE is not
> needed.
>
>
>
> Regards,
>
> Srinivas G
>
>
>
> From: IDUG DB2-L [mailto:[login to unmask email] On
> Behalf Of Steurs Patrick
> Sent: Wednesday, November 25, 2009 7:13 PM
> To: [login to unmask email]
> Subject: Re: [DB2-L] Space issue in DB2
>
>
>
> Hi,
>
>
>
> What is your default encoding scheme in db2 ? Verify
> this in your DSNHDECP-job.
>
>
>
> greetings,
>
>
>
> Patrick
>
>
>
> From: IDUG DB2-L [mailto:[login to unmask email] On
> Behalf Of SrinivasG
> Sent: woensdag 25 november 2009 14:21
> To: [login to unmask email]
> Subject: Re: [DB2-L] Space issue in DB2
>
>
>
> I have it confirmed. Compression was disabled in
> Oracle at all levels. I am clueless..
>
>
>
> From: IDUG DB2-L [mailto:[login to unmask email] On
> Behalf Of Gregory Palgrave
> Sent: Wednesday, November 25, 2009 6:47 AM
> To: [login to unmask email]
> Subject: Re: [DB2-L] Space issue in DB2
>
>
>
> Once upon a time I would have known how big a 3390-9
> was, but I have to admit I needed to look it up this
> time – roughly 8.5 GB.
>
>
>
> That’s a huge difference in space usage from the
> Oracle db, and it makes me wonder if possibly the
> Oracle db is using row/table compression and DB2 is
> not?
>
>
>
> Are the data types for the tables comparable (i.e.
> VARCHAR to VARCHAR, not VARCHAR to CHAR etc)?
>
> How is the data being unloaded/converted from
> Oracle? I have seen code in the past that would
> unload and pad varchars with spaces up to their
> maximum length and other silly stuff. There could be
> something similar happening.
>
>
>
> Cheers
>
>
>
> Greg
>
>
>
> From: IDUG DB2-L [mailto:[login to unmask email] On
> Behalf Of SrinivasG
> Sent: Tuesday, 24 November 2009 11:03 PM
> To: [login to unmask email]
> Subject: [DB2-L] Space issue in DB2
>
>
>
> Hi,
>
>
>
> We are migrating an application that uses Oracle as
> Database to DB2.
>
> While the Data size in Oracle is 8 GB , when it is
> loaded on to DB2 , it uses up almost 28 3390-9
> volumes.
>
> The Tablespaces are created using an automated
> script.
>
>
>
> How do I know if the VSAM Datasets Created for the
> Table Spaces are fully utilized? If the Dataset is
> over allocated , how do I reclaim the space?
>
>
>
> Is Alter Tablespace followed by REORG the only
> alternative?
>
>
>
> Regards,
>
> Srinivas G
>
> ----------------------------------------------------
>
> Visit our website! http://www.nbb.be "DISCLAIMER:
> The content of this e-mail message should not be
> construed as binding on the part of the National
> Bank of Belgium (NBB) unless otherwise and
> previously stated. The opinions expressed in this
> message are solely those of the author and do not
> necessarily reflect NBB viewpoints, particularly
> when the content of this message, or part thereof,
> is private by nature or does not fall within the
> professional scope of its author."
Isaac Yassin
IBM Information Champion
IBM Certified Solution Expert
IBM Certified Database Administrator - DB2 for z/OS V8 & 9

Patrick Steurs

Re: Space issue in DB2
(in response to Isaac Yassin)
Hi,



We are not using UNICODE as default.



DSNHDECM CHARSET=ALPHANUM,

ASCCSID=1252,

AMCCSID=65534,

AGCCSID=65534,

SCCSID=500,

MCCSID=65534,

GCCSID=65534,

USCCSID=367,

UMCCSID=1208,

UGCCSID=1200,

ENSCHEME=EBCDIC,

APPENSCH=EBCDIC,



If you use UNICODE, you are using more diskspace than with EBCDIC. But, if your source data comes from Oracle ( & uses UNICODE data ) then you don't have a choice.



The system zparms that have an impact on allocation are TSQTY, IXQTY and MGEXTSZ. The descriptions below are from the Installation Guide.

TSQTY--Specify the amount of space in KB for primary and secondary space allocation for DB2-defined datasets for table spaces that are being created without the USING clause. A value of 0 indicates that DB2 is to use a default value of one cylinder for a non-LOB table space or 10 cylinders for a LOB table space.

IXQTY--Specify the amount of space in KB for primary and secondary space allocation for DB2-defined datasets for index spaces that are being created without the USING clause. A value of 0 indicates that DB2 is to use a default allocation of one cylinder.

MGEXTSZ--Specify whether secondary extent allocations for DB2-managed data sets are to be sized according to a sliding scale that optimizes the likelihood of reaching the maximum dataset size before secondary extents are exhausted.



WE use TSQTY = 0 and IXQTY = 0



Why would you like to use "DEFINE NO" ?



greetings,



Patrick



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: donderdag 26 november 2009 5:33
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2



Hi Patrick,



The Default encoding is UNICODE.



AGCCSID=65534,

SCCSID=1047,

MCCSID=65534,

GCCSID=65534,

USCCSID=367,

UMCCSID=1208,

UGCCSID=1200,

ENSCHEME=UNICODE,

APPENSCH=UNICODE,





And if I specify



PRIQTY -1

SECQTY -1



Will that be ok? Since TSQTY is 7200 and IXQTY is also 7200 and MGEXTSZ=YES is in effect currently.



I am also planning to give COMPRESS YES and DEFINE NO.



In fact the current Create Tablespace statement reads like the one below. I was created by the programmers. I could not check it before since the setup here is a bit complicated. But now they have come back to me since they are having performance issues.



CREATE TABLESPACE ACS001

IN FCRMTR01

USING STOGROUP CRM_TRAN01

PRIQTY 3600

SECQTY 1800

ERASE NO

FREEPAGE 5

PCTFREE 10

DEFINE YES

LOGGED

TRACKMOD NO

SEGSIZE 16

MAXPARTITIONS 20

DSSIZE 4G

BUFFERPOOL BP32K

LOCKSIZE PAGE

LOCKMAX SYSTEM

COMPRESS NO

CLOSE NO !



The SPACE field for this Tablespace in SYSTABLESPACE is 4224



I am thinking the MAXPARTITIONS and DSSIZE is not needed.



Regards,

Srinivas G



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Steurs Patrick
Sent: Wednesday, November 25, 2009 7:13 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2



Hi,



What is your default encoding scheme in db2 ? Verify this in your DSNHDECP-job.



greetings,



Patrick



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: woensdag 25 november 2009 14:21
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2



I have it confirmed. Compression was disabled in Oracle at all levels. I am clueless..



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Gregory Palgrave
Sent: Wednesday, November 25, 2009 6:47 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2



Once upon a time I would have known how big a 3390-9 was, but I have to admit I needed to look it up this time – roughly 8.5 GB.



That’s a huge difference in space usage from the Oracle db, and it makes me wonder if possibly the Oracle db is using row/table compression and DB2 is not?



Are the data types for the tables comparable (i.e. VARCHAR to VARCHAR, not VARCHAR to CHAR etc)?

How is the data being unloaded/converted from Oracle? I have seen code in the past that would unload and pad varchars with spaces up to their maximum length and other silly stuff. There could be something similar happening.



Cheers



Greg



From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: Tuesday, 24 November 2009 11:03 PM
To: [login to unmask email]
Subject: [DB2-L] Space issue in DB2



Hi,



We are migrating an application that uses Oracle as Database to DB2.

While the Data size in Oracle is 8 GB , when it is loaded on to DB2 , it uses up almost 28 3390-9 volumes.

The Tablespaces are created using an automated script.



How do I know if the VSAM Datasets Created for the Table Spaces are fully utilized? If the Dataset is over allocated , how do I reclaim the space?



Is Alter Tablespace followed by REORG the only alternative?



Regards,

Srinivas G

________________________________

Visit our website! http://www.nbb.be "DISCLAIMER: The content of this e-mail message should not be construed as binding on the part of the National Bank of Belgium (NBB) unless otherwise and previously stated. The opinions expressed in this message are solely those of the author and do not necessarily reflect NBB viewpoints, particularly when the content of this message, or part thereof, is private by nature or does not fall within the professional scope of its author."



-----------------------------------------
Visit our website! http://www.nbb.be

"DISCLAIMER: The content of this e-mail message should not be
construed as binding on the part of the National Bank of Belgium
(NBB) unless otherwise and previously stated. The opinions
expressed in this message are solely those of the author and do not
necessarily reflect NBB viewpoints, particularly when the content
of this message, or part thereof, is private by nature or does not
fall within the professional scope of its author."

[login to unmask email]

Re: Space issue in DB2
(in response to Patrick Steurs)
Srinivas G,

For a 8G table in ORACLE, the space allocation parameter PRIQTY 3600 and
SECQTY 1800 is too small for DB2.
And I just wonder, you mean 28 3390-9 volumes are used up, or the vsam
data set across 28 volumes?
It's big difference.
I believe there must be some difference in your DDL or related stuff
between DB2 and ORACLE.





SrinivasG <[login to unmask email]>
·¢¼þÈË: IDUG DB2-L <[login to unmask email]>
2009-11-26 12:32
Çë´ð¸´ ¸ø
IDUG DB2-L <[login to unmask email]>


ÊÕ¼þÈË
[login to unmask email]
³­ËÍ

Ö÷Ìâ
Re: [DB2-L] Space issue in DB2






Hi Patrick,

The Default encoding is UNICODE.

AGCCSID=65534,
SCCSID=1047,
MCCSID=65534,
GCCSID=65534,
USCCSID=367,
UMCCSID=1208,
UGCCSID=1200,
ENSCHEME=UNICODE,
APPENSCH=UNICODE,


And if I specify

PRIQTY -1
SECQTY -1

Will that be ok? Since TSQTY is 7200 and IXQTY is also 7200 and
MGEXTSZ=YES is in effect currently.

I am also planning to give COMPRESS YES and DEFINE NO.

In fact the current Create Tablespace statement reads like the one below.
I was created by the programmers. I could not check it before since the
setup here is a bit complicated. But now they have come back to me since
they are having performance issues.

CREATE TABLESPACE ACS001
IN FCRMTR01
USING STOGROUP CRM_TRAN01
PRIQTY 3600
SECQTY 1800
ERASE NO
FREEPAGE 5
PCTFREE 10
DEFINE YES
LOGGED
TRACKMOD NO
SEGSIZE 16
MAXPARTITIONS 20
DSSIZE 4G
BUFFERPOOL BP32K
LOCKSIZE PAGE
LOCKMAX SYSTEM
COMPRESS NO
CLOSE NO !

The SPACE field for this Tablespace in SYSTABLESPACE is 4224

I am thinking the MAXPARTITIONS and DSSIZE is not needed.

Regards,
Srinivas G

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Steurs Patrick
Sent: Wednesday, November 25, 2009 7:13 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2

Hi,

What is your default encoding scheme in db2 ? Verify this in your
DSNHDECP-job.

greetings,

Patrick

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: woensdag 25 november 2009 14:21
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2

I have it confirmed. Compression was disabled in Oracle at all levels. I
am clueless..

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Gregory
Palgrave
Sent: Wednesday, November 25, 2009 6:47 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2

Once upon a time I would have known how big a 3390-9 was, but I have to
admit I needed to look it up this time ¨C roughly 8.5 GB.

That¡¯s a huge difference in space usage from the Oracle db, and it makes
me wonder if possibly the Oracle db is using row/table compression and DB2
is not?

Are the data types for the tables comparable (i.e. VARCHAR to VARCHAR, not
VARCHAR to CHAR etc)?
How is the data being unloaded/converted from Oracle? I have seen code in
the past that would unload and pad varchars with spaces up to their
maximum length and other silly stuff. There could be something similar
happening.

Cheers

Greg

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: Tuesday, 24 November 2009 11:03 PM
To: [login to unmask email]
Subject: [DB2-L] Space issue in DB2

Hi,

We are migrating an application that uses Oracle as Database to DB2.
While the Data size in Oracle is 8 GB , when it is loaded on to DB2 , it
uses up almost 28 3390-9 volumes.
The Tablespaces are created using an automated script.

How do I know if the VSAM Datasets Created for the Table Spaces are fully
utilized? If the Dataset is over allocated , how do I reclaim the space?

Is Alter Tablespace followed by REORG the only alternative?

Regards,
Srinivas G

Visit our website! http://www.nbb.be "DISCLAIMER: The content of this
e-mail message should not be construed as binding on the part of the
National Bank of Belgium (NBB) unless otherwise and previously stated. The
opinions expressed in this message are solely those of the author and do
not necessarily reflect NBB viewpoints, particularly when the content of
this message, or part thereof, is private by nature or does not fall
within the professional scope of its author."

Srinivas Gopala

Re: Space issue in DB2
(in response to hhuang@DCCSH.ICBC.COM.CN)
I have found it. The LOB Tablespaces are using up most of the space. The IXQTY and TSQTY set is 3600 in ZPARM.
The average Space value for LOB Tablespace is 360448.

Thanks for all who have replied.

Regards,
Srinivas G

From: SrinivasG
Sent: Thursday, November 26, 2009 10:03 AM
To: [login to unmask email]
Subject: RE: [DB2-L] Space issue in DB2

Hi Patrick,

The Default encoding is UNICODE.

AGCCSID=65534,
SCCSID=1047,
MCCSID=65534,
GCCSID=65534,
USCCSID=367,
UMCCSID=1208,
UGCCSID=1200,
ENSCHEME=UNICODE,
APPENSCH=UNICODE,


And if I specify

PRIQTY -1
SECQTY -1

Will that be ok? Since TSQTY is 7200 and IXQTY is also 7200 and MGEXTSZ=YES is in effect currently.

I am also planning to give COMPRESS YES and DEFINE NO.

In fact the current Create Tablespace statement reads like the one below. I was created by the programmers. I could not check it before since the setup here is a bit complicated. But now they have come back to me since they are having performance issues.

CREATE TABLESPACE ACS001
IN FCRMTR01
USING STOGROUP CRM_TRAN01
PRIQTY 3600
SECQTY 1800
ERASE NO
FREEPAGE 5
PCTFREE 10
DEFINE YES
LOGGED
TRACKMOD NO
SEGSIZE 16
MAXPARTITIONS 20
DSSIZE 4G
BUFFERPOOL BP32K
LOCKSIZE PAGE
LOCKMAX SYSTEM
COMPRESS NO
CLOSE NO !

The SPACE field for this Tablespace in SYSTABLESPACE is 4224

I am thinking the MAXPARTITIONS and DSSIZE is not needed.

Regards,
Srinivas G

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Steurs Patrick
Sent: Wednesday, November 25, 2009 7:13 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2

Hi,

What is your default encoding scheme in db2 ? Verify this in your DSNHDECP-job.

greetings,

Patrick

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: woensdag 25 november 2009 14:21
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2

I have it confirmed. Compression was disabled in Oracle at all levels. I am clueless..

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Gregory Palgrave
Sent: Wednesday, November 25, 2009 6:47 AM
To: [login to unmask email]
Subject: Re: [DB2-L] Space issue in DB2

Once upon a time I would have known how big a 3390-9 was, but I have to admit I needed to look it up this time – roughly 8.5 GB.

That’s a huge difference in space usage from the Oracle db, and it makes me wonder if possibly the Oracle db is using row/table compression and DB2 is not?

Are the data types for the tables comparable (i.e. VARCHAR to VARCHAR, not VARCHAR to CHAR etc)?
How is the data being unloaded/converted from Oracle? I have seen code in the past that would unload and pad varchars with spaces up to their maximum length and other silly stuff. There could be something similar happening.

Cheers

Greg

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SrinivasG
Sent: Tuesday, 24 November 2009 11:03 PM
To: [login to unmask email]
Subject: [DB2-L] Space issue in DB2

Hi,

We are migrating an application that uses Oracle as Database to DB2.
While the Data size in Oracle is 8 GB , when it is loaded on to DB2 , it uses up almost 28 3390-9 volumes.
The Tablespaces are created using an automated script.

How do I know if the VSAM Datasets Created for the Table Spaces are fully utilized? If the Dataset is over allocated , how do I reclaim the space?

Is Alter Tablespace followed by REORG the only alternative?

Regards,
Srinivas G
________________________________

Visit our website! http://www.nbb.be "DISCLAIMER: The content of this e-mail message should not be construed as binding on the part of the National Bank of Belgium (NBB) unless otherwise and previously stated. The opinions expressed in this message are solely those of the author and do not necessarily reflect NBB viewpoints, particularly when the content of this message, or part thereof, is private by nature or does not fall within the professional scope of its author."