DB2 for z/OS Version 8 - ALTER TABLE ALTER COLUMN

Jim McAlpine

DB2 for z/OS Version 8 - ALTER TABLE ALTER COLUMN
I've RTFM regarding the above and the description includes the following
-

"Only future values of the
| column are affected by the changes made with an ALTER TABLE ALTER
| COLUMN statement."

If I want to increase the length of a char column does this mean that
for rows that existed before the change that DB2 will return the
original value padded to the right with spaces.

Jim McAlpine
Email: [login to unmask email]
Direct Dial: +44 (0)191 4827856

CedarOpenAccounts
PrismTech House
5th Avenue Business Park
Team Valley
Gateshead
Tyne and Wear
NE11 ONG
United Kingdom
Tel 0191 482 7900
Fax 0191 482 7901
www.cedaropenaccounts.com


This email and any files transmitted with it are confidential
and intended solely for the use of the individual or entity to
whom they are addressed. Any unauthorised distribution or
copying is strictly prohibited. Whilst COA takes steps to
prevent the transmission of viruses via e-mail,
we cannot guarantee that any email or attachment is free from
computer viruses and you are strongly advised to undertake your
own anti-virus precautions. COA grants no
warranties regarding performance, use or quality of any e-mail
or attachment and undertakes no liability for loss or damage,
howsoever caused.


COA is a trading name of COA Solutions Limited, which is registered in England
and Wales No: 3214465

Registered Office: Munro House, Portsmouth Road, Cobham, Surrey, KT11 1TF
For more information on COA and our products, please visit our web site at http://www.coasolutions.com

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Mike Bell

Re: DB2 for z/OS Version 8 - ALTER TABLE ALTER COLUMN
(in response to Jim McAlpine)
My experience is

1. AFTER the ALTER - no rows are changed but results come back padded
with blanks if CHAR and old length if VARCHAR.

2. New INSERT will build the full length if CHAR, and specified length
if VARCHAR.

3. REORG will expand all the CHAR fields to new length.



Results are even more fun if the tablespace is compressed but the SQL
answers stay the same.



Mike

HLS Technologies







From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Jim McAlpine
Sent: Monday, December 17, 2007 8:30 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS Version 8 - ALTER TABLE ALTER COLUMN



I've RTFM regarding the above and the description includes the following -

"Only future values of the
| column are affected by the changes made with an ALTER TABLE ALTER
| COLUMN statement."

If I want to increase the length of a char column does this mean that for
rows that existed before the change that DB2 will return the original value
padded to the right with spaces.

Jim McAlpine
Email: [login to unmask email]
Direct Dial: +44 (0)191 4827856

CedarOpenAccounts
PrismTech House
5th Avenue Business Park
Team Valley
Gateshead
Tyne and Wear
NE11 ONG
United Kingdom
Tel 0191 482 7900
Fax 0191 482 7901
<file:///\\www.cedaropenaccounts.com> www.cedaropenaccounts.com

_____

This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.
Any unauthorised distribution or copying is strictly prohibited. Whilst COA
takes steps to prevent the transmission of viruses via e-mail, we cannot
guarantee that any email or attachment is free from computer viruses and you
are strongly advised to undertake your own anti-virus precautions. COA
grants no warranties regarding performance, use or quality of any e-mail
or attachment and undertakes no liability for loss or damage, howsoever
caused.

COA is a trading name of COA Solutions Limited, which is registered in
England and Wales No: 3214465
Registered Office: Munro House, Portsmouth Road, Cobham, Surrey, KT11 1TF
For more information on COA and our products, please visit our web site at
< http://www.coasolutions.com > http://www.coasolutions.com

_____




The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list
archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no
cost, click on Member Services


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Mike Bell

Re: DB2 for z/OS Version 8 - ALTER TABLE ALTER COLUMN
(in response to Mike Bell)
I hate to do double answers but I forgot something on the first answer.

DB2 will only bring back the answer padded with blanks if the target
variable is big enough.

If you have an old program that still has the old length, the answer will be
truncated to match the variable length.

This is normally NOT a good thing.



Mike

HLS Technologies



From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Jim McAlpine
Sent: Monday, December 17, 2007 8:30 AM
To: [login to unmask email]
Subject: [DB2-L] DB2 for z/OS Version 8 - ALTER TABLE ALTER COLUMN



I've RTFM regarding the above and the description includes the following -

"Only future values of the
| column are affected by the changes made with an ALTER TABLE ALTER
| COLUMN statement."

If I want to increase the length of a char column does this mean that for
rows that existed before the change that DB2 will return the original value
padded to the right with spaces.

Jim McAlpine
Email: [login to unmask email]
Direct Dial: +44 (0)191 4827856

CedarOpenAccounts
PrismTech House
5th Avenue Business Park
Team Valley
Gateshead
Tyne and Wear
NE11 ONG
United Kingdom
Tel 0191 482 7900
Fax 0191 482 7901
<file:///\\www.cedaropenaccounts.com> www.cedaropenaccounts.com

_____

This email and any files transmitted with it are confidential and intended
solely for the use of the individual or entity to whom they are addressed.
Any unauthorised distribution or copying is strictly prohibited. Whilst COA
takes steps to prevent the transmission of viruses via e-mail, we cannot
guarantee that any email or attachment is free from computer viruses and you
are strongly advised to undertake your own anti-virus precautions. COA
grants no warranties regarding performance, use or quality of any e-mail
or attachment and undertakes no liability for loss or damage, howsoever
caused.

COA is a trading name of COA Solutions Limited, which is registered in
England and Wales No: 3214465
Registered Office: Munro House, Portsmouth Road, Cobham, Surrey, KT11 1TF
For more information on COA and our products, please visit our web site at
< http://www.coasolutions.com > http://www.coasolutions.com

_____




The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list
archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no
cost, click on Member Services


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms