Alter table problem

Amit Agarwal

Alter table problem
DB2 OS/390 V5
Is it possible to change a column attribute in a emply table
from NULL to NOT NULL with default?

I was trying
ALTER TABLE CAN.TSTATE_REF
ALTER COLUMN CAN_REGION_C SET DATA TYPE CHAR(2)
NOT NULL WITH DEFAULT;

but got an error.

Thanks

Amit



--------------------------------------------------------


The information contained in this message is intended only for the recipient, and may be a confidential attorney-client communication or may otherwise be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, please be aware that any dissemination or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify us by replying to the message and deleting it from your computer.

Thank you,

Standard & Poor's

--------------------------------------------------------



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Isaac Yassin

Re: Alter table problem
(in response to Amit Agarwal)
NO
Not in V5

Isaac Yassin
IBM Certified solution expert
DB2 V7 for OS/390 & Z/OS

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of
Agarwal, Amit
Sent: Monday, December 22, 2003 6:03 PM
To: [login to unmask email]
Subject: Alter table problem

DB2 OS/390 V5
Is it possible to change a column attribute in a emply table
from NULL to NOT NULL with default?

I was trying
ALTER TABLE CAN.TSTATE_REF
ALTER COLUMN CAN_REGION_C SET DATA TYPE CHAR(2)
NOT NULL WITH DEFAULT;

but got an error.

Thanks

Amit



--------------------------------------------------------


The information contained in this message is intended only for the recipient,
and may be a confidential attorney-client communication or may otherwise be
privileged and confidential and protected from disclosure. If the reader of this
message is not the intended recipient, or an employee or agent responsible for
delivering this message to the intended recipient, please be aware that any
dissemination or copying of this communication is strictly prohibited. If you
have received this communication in error, please immediately notify us by
replying to the message and deleting it from your computer.

Thank you,

Standard & Poor's

--------------------------------------------------------



--------------------------------------------------------------------------------
-
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page
at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or
Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL
command to [login to unmask email] The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences at
http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Avram Friedman

Re: Alter table problem
(in response to Isaac Yassin)
First of all, the question you should be asking is can anyone give me advice on getting off an unsupported version of DB2 ... There is probally nothing more important to your DB2 environment than moving ahead in this area.

I am pasting in a line from the DB2 V7 SQL manual to answer your original question.

"ALTER COLUMN column-alteration
Alters the definition of a column. Only the length attribute of an existing column with a VARCHAR data type can be changed. A column cannot be altered if it is
used in a referential constraint or a view or has a field procedure routine. It also cannot be altered if it belongs to a table that has edit or validation routine, is
defined with DATA CAPTURE CHANGES, or is a created temporary table."




"Agarwal, Amit" wrote:

> DB2 OS/390 V5
> Is it possible to change a column attribute in a emply table
> from NULL to NOT NULL with default?
>
> I was trying
> ALTER TABLE CAN.TSTATE_REF
> ALTER COLUMN CAN_REGION_C SET DATA TYPE CHAR(2)
> NOT NULL WITH DEFAULT;
>
> but got an error.
>
> Thanks
>
> Amit
>
>
>
> --------------------------------------------------------
>
>
> The information contained in this message is intended only for the recipient, and may be a confidential attorney-client communication or may otherwise be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, please be aware that any dissemination or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify us by replying to the message and deleting it from your computer.
>
> Thank you,
>
> Standard & Poor's
>
> --------------------------------------------------------
>
>
>
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

--
NOTICE: If received in error, please destroy and notify sender. Sender does not waive confidentiality or privilege, and use is prohibited.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Chi-Yun (Angela) Fung

Re: Alter table problem
(in response to Venu Varma)
Amit,

No. Not even in V7. You can not alter column NULL attributes. The SET DATA TYPE only apply to VARCHAR length.

You have to drop and recreate the table when you change column from NULL to NOT NULL WITH DEFAULT. That's what we have been doing.

Angela

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Agarwal, Amit
Sent: Monday, December 22, 2003 11:03 AM
To: [login to unmask email]
Subject: Alter table problem


DB2 OS/390 V5
Is it possible to change a column attribute in a emply table
from NULL to NOT NULL with default?

I was trying
ALTER TABLE CAN.TSTATE_REF
ALTER COLUMN CAN_REGION_C SET DATA TYPE CHAR(2)
NOT NULL WITH DEFAULT;

but got an error.

Thanks

Amit



--------------------------------------------------------


The information contained in this message is intended only for the recipient, and may be a confidential attorney-client communication or may otherwise be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, please be aware that any dissemination or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify us by replying to the message and deleting it from your computer.

Thank you,

Standard & Poor's

--------------------------------------------------------



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Venu Varma

Re: Alter table problem
(in response to Avram Friedman)
You need to drop and recreate the table with new attributes.



"Agarwal, Amit"
<[login to unmask email] To: [login to unmask email]
DPOORS.COM> cc:
Sent by: DB2 Data Base Subject: Alter table problem
Discussion List
<[login to unmask email]>


12/22/2003 10:03 AM
Please respond to DB2
Database Discussion list
at IDUG






DB2 OS/390 V5
Is it possible to change a column attribute in a emply table
from NULL to NOT NULL with default?

I was trying
ALTER TABLE CAN.TSTATE_REF
ALTER COLUMN CAN_REGION_C SET DATA TYPE CHAR(2)
NOT NULL WITH DEFAULT;

but got an error.

Thanks

Amit



--------------------------------------------------------


The information contained in this message is intended only for the
recipient, and may be a confidential attorney-client communication or may
otherwise be privileged and confidential and protected from disclosure. If
the reader of this message is not the intended recipient, or an employee or
agent responsible for delivering this message to the intended recipient,
please be aware that any dissemination or copying of this communication is
strictly prohibited. If you have received this communication in error,
please immediately notify us by replying to the message and deleting it
from your computer.

Thank you,

Standard & Poor's

--------------------------------------------------------



---------------------------------------------------------------------------------

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
can be reached at [login to unmask email] Find out the latest on
IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

David Seibert

Re: Alter table problem
(in response to Chi-Yun (Angela) Fung)
>Is it possible to change a column attribute in a emply table from NULL to
NOT NULL with default?

Hello Amit,

No you can't alter the NULL characteristics of a column in v5, 6, or 7.

And it appears you won't be able to in v8 where lots more characteristics
are alterable.
The NULL characteristics are still not one of the advertised changes
allowed.

Dave





The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: Alter table problem
(in response to David Seibert)
and you can't make this change, even in Version 8 either

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Avram Friedman
Sent: Monday, December 22, 2003 4:23 PM
To: [login to unmask email]
Subject: Re: Alter table problem


First of all, the question you should be asking is can anyone give me advice on getting off an unsupported version of DB2 ... There is probally nothing more important to your DB2 environment than moving ahead in this area.

I am pasting in a line from the DB2 V7 SQL manual to answer your original question.

"ALTER COLUMN column-alteration
Alters the definition of a column. Only the length attribute of an existing column with a VARCHAR data type can be changed. A column cannot be altered if it is
used in a referential constraint or a view or has a field procedure routine. It also cannot be altered if it belongs to a table that has edit or validation routine, is
defined with DATA CAPTURE CHANGES, or is a created temporary table."




"Agarwal, Amit" wrote:

> DB2 OS/390 V5
> Is it possible to change a column attribute in a emply table
> from NULL to NOT NULL with default?
>
> I was trying
> ALTER TABLE CAN.TSTATE_REF
> ALTER COLUMN CAN_REGION_C SET DATA TYPE CHAR(2)
> NOT NULL WITH DEFAULT;
>
> but got an error.
>
> Thanks
>
> Amit
>
>
>
> --------------------------------------------------------
>
>
> The information contained in this message is intended only for the recipient, and may be a confidential attorney-client communication or may otherwise be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, please be aware that any dissemination or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify us by replying to the message and deleting it from your computer.
>
> Thank you,
>
> Standard & Poor's
>
> --------------------------------------------------------
>
>
>
> ---------------------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

--
NOTICE: If received in error, please destroy and notify sender. Sender does not waive confidentiality or privilege, and use is prohibited.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Dave Nance

Re: Alter table problem
(in response to Phil Grainger)
Does anyone else see the problem in this type of alter? Iknow you can't alter the default, but I think its something more of should complain to IBM about. Why can't you change your default? I'm not saying alter the data type or anything like that, just what default value gets in there at insert time. Why can't I have a job set up to change the default value on a daily basis, take the following for instance.
column name data type length values
c_day_week char 03 'MON','TUE',WED',et...
What harm would be caused by changing the default on a daily basis to the day of the week? To take care of something like this in the database I would have to create a UDF or change my insert programs to figure out the day of the week. So be sure to put this on your Christmas wish list to IBM. Thanks and have a happy holidays.

Dave Nance
First Health Services, Corp.
(804)527-6841


>>> [login to unmask email] 12/22/03 11:26:18 AM >>>
Amit,

No. Not even in V7. You can not alter column NULL attributes. The SET DATA TYPE only apply to VARCHAR length.

You have to drop and recreate the table when you change column from NULL to NOT NULL WITH DEFAULT. That's what we have been doing.

Angela

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Agarwal, Amit
Sent: Monday, December 22, 2003 11:03 AM
To: [login to unmask email]
Subject: Alter table problem


DB2 OS/390 V5
Is it possible to change a column attribute in a emply table
from NULL to NOT NULL with default?

I was trying
ALTER TABLE CAN.TSTATE_REF
ALTER COLUMN CAN_REGION_C SET DATA TYPE CHAR(2)
NOT NULL WITH DEFAULT;

but got an error.

Thanks

Amit



--------------------------------------------------------


The information contained in this message is intended only for the recipient, and may be a confidential attorney-client communication or may otherwise be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, please be aware that any dissemination or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify us by replying to the message and deleting it from your computer.

Thank you,

Standard & Poor's

--------------------------------------------------------



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: Alter table problem
(in response to Dave Nance)
Sounds like a reasonable request, except that altering the default value would have to invalidate all plans, packages and cached statements requiring a rebind (or auto-rebind) after the alter.......

You could still ask though - this is still a simpler request than altering from NOT NULL to NOT NULL WITH DEFAULT (probably)

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of David Nance
Sent: Monday, December 22, 2003 4:43 PM
To: [login to unmask email]
Subject: Re: Alter table problem


Does anyone else see the problem in this type of alter? Iknow you can't alter the default, but I think its something more of should complain to IBM about. Why can't you change your default? I'm not saying alter the data type or anything like that, just what default value gets in there at insert time. Why can't I have a job set up to change the default value on a daily basis, take the following for instance.
column name data type length values
c_day_week char 03 'MON','TUE',WED',et...
What harm would be caused by changing the default on a daily basis to the day of the week? To take care of something like this in the database I would have to create a UDF or change my insert programs to figure out the day of the week. So be sure to put this on your Christmas wish list to IBM. Thanks and have a happy holidays.

Dave Nance
First Health Services, Corp.
(804)527-6841


>>> [login to unmask email] 12/22/03 11:26:18 AM >>>
Amit,

No. Not even in V7. You can not alter column NULL attributes. The SET DATA TYPE only apply to VARCHAR length.

You have to drop and recreate the table when you change column from NULL to NOT NULL WITH DEFAULT. That's what we have been doing.

Angela

-----Original Message-----
From: DB2 Data Base Discussion List [ mailto:[login to unmask email]On
Behalf Of Agarwal, Amit
Sent: Monday, December 22, 2003 11:03 AM
To: [login to unmask email]
Subject: Alter table problem


DB2 OS/390 V5
Is it possible to change a column attribute in a emply table
from NULL to NOT NULL with default?

I was trying
ALTER TABLE CAN.TSTATE_REF
ALTER COLUMN CAN_REGION_C SET DATA TYPE CHAR(2)
NOT NULL WITH DEFAULT;

but got an error.

Thanks

Amit



--------------------------------------------------------


The information contained in this message is intended only for the recipient, and may be a confidential attorney-client communication or may otherwise be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, please be aware that any dissemination or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify us by replying to the message and deleting it from your computer.

Thank you,

Standard & Poor's

--------------------------------------------------------



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Amit Agarwal

Re: Alter table problem
(in response to Phil Grainger)
David,
I agree with you on this. Other databases I work on allow this easily, and quite frankly ,I was surprised to know that its not allowed even in V8.

Thanks
Amit
-----Original Message-----
From: David Nance [mailto:[login to unmask email]
Sent: Monday, December 22, 2003 11:43 AM
To: [login to unmask email]
Subject: Re: Alter table problem


Does anyone else see the problem in this type of alter? Iknow you can't alter the default, but I think its something more of should complain to IBM about. Why can't you change your default? I'm not saying alter the data type or anything like that, just what default value gets in there at insert time. Why can't I have a job set up to change the default value on a daily basis, take the following for instance.
column name data type length values
c_day_week char 03 'MON','TUE',WED',et...
What harm would be caused by changing the default on a daily basis to the day of the week? To take care of something like this in the database I would have to create a UDF or change my insert programs to figure out the day of the week. So be sure to put this on your Christmas wish list to IBM. Thanks and have a happy holidays.

Dave Nance
First Health Services, Corp.
(804)527-6841


>>> [login to unmask email] 12/22/03 11:26:18 AM >>>
Amit,

No. Not even in V7. You can not alter column NULL attributes. The SET DATA TYPE only apply to VARCHAR length.

You have to drop and recreate the table when you change column from NULL to NOT NULL WITH DEFAULT. That's what we have been doing.

Angela

-----Original Message-----
From: DB2 Data Base Discussion List [ mailto:[login to unmask email]On
Behalf Of Agarwal, Amit
Sent: Monday, December 22, 2003 11:03 AM
To: [login to unmask email]
Subject: Alter table problem


DB2 OS/390 V5
Is it possible to change a column attribute in a emply table
from NULL to NOT NULL with default?

I was trying
ALTER TABLE CAN.TSTATE_REF
ALTER COLUMN CAN_REGION_C SET DATA TYPE CHAR(2)
NOT NULL WITH DEFAULT;

but got an error.

Thanks

Amit



--------------------------------------------------------


The information contained in this message is intended only for the recipient, and may be a confidential attorney-client communication or may otherwise be privileged and confidential and protected from disclosure. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, please be aware that any dissemination or copying of this communication is strictly prohibited. If you have received this communication in error, please immediately notify us by replying to the message and deleting it from your computer.

Thank you,

Standard & Poor's

--------------------------------------------------------



---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.
--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Avram Friedman

Re: Alter table problem
(in response to Amit Agarwal)
There are many problems with altering a column to change its default
value.

Just to discuss one ...

Say the column in question was created on a non empty table with an add
column that is not null with default. According to the DB2 ADMIN guide,
when a row is retrived that pre-existed the add column, by a program
that is senistive to the new column the default value is returned. Now
lets say it was permitted to change the default value ... the data
retuned to the user changes for an existing record which ofcourse is
wrong.

David Nance wrote:

> Does anyone else see the problem in this type of alter? Iknow you
> can't alter the default, but I think its something more of should
> complain to IBM about. Why can't you change your default? I'm not
> saying alter the data type or anything like that, just what default
> value gets in there at insert time. Why can't I have a job set up to
> change the default value on a daily basis, take the following for
> instance.column name data type length
> valuesc_day_week char 03
> 'MON','TUE',WED',et... What harm would be caused by changing the
> default on a daily basis to the day of the week? To take care of
> something like this in the database I would have to create a UDF or
> change my insert programs to figure out the day of the week. So be
> sure to put this on your Christmas wish list to IBM. Thanks and have a
> happy holidays. Dave Nance
> First Health Services, Corp.
> (804)527-6841
>
>
> >>> [login to unmask email] 12/22/03 11:26:18 AM >>>
> Amit,
>
> No. Not even in V7. You can not alter column NULL attributes. The SET
> DATA TYPE only apply to VARCHAR length.
>
> You have to drop and recreate the table when you change column from
> NULL to NOT NULL WITH DEFAULT. That's what we have been doing.
>
> Angela
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
> Behalf Of Agarwal, Amit
> Sent: Monday, December 22, 2003 11:03 AM
> To: [login to unmask email]
> Subject: Alter table problem
>
>
> DB2 OS/390 V5
> Is it possible to change a column attribute in a emply table
> from NULL to NOT NULL with default?
>
> I was trying
> ALTER TABLE CAN.TSTATE_REF
> ALTER COLUMN CAN_REGION_C SET DATA TYPE CHAR(2)
> NOT NULL WITH DEFAULT;
>
> but got an error.
>
> Thanks
>
> Amit
>
>
>
> --------------------------------------------------------
>
>
> The information contained in this message is intended only for the
> recipient, and may be a confidential attorney-client communication or
> may otherwise be privileged and confidential and protected from
> disclosure. If the reader of this message is not the intended
> recipient, or an employee or agent responsible for delivering this
> message to the intended recipient, please be aware that any
> dissemination or copying of this communication is strictly prohibited.
> If you have received this communication in error, please immediately
> notify us by replying to the message and deleting it from your
> computer.
>
> Thank you,
>
> Standard & Poor's
>
> --------------------------------------------------------
>
>
>
> -
> -------------------------------------------------------------------------------
>
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
> page select "Join or Leave the list". If you will be out of the
> office, send the SET DB2-L NO MAIL command to
> [login to unmask email] The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG
> conferences at http://conferences.idug.org/index.cfm
>
>
> --------------------------------------------------------------------------------
>
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
> page select "Join or Leave the list". If you will be out of the
> office, send the SET DB2-L NO MAIL command to
> [login to unmask email] The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG
> conferences at http://conferences.idug.org/index.cfm
> This message, including any attachments, is intended solely for the
> use
> of the named recipient(s) and may contain confidential and/or
> privileged information. Any unauthorized review, use, disclosure or
> distribution of this communication(s) is expressly prohibited.
> If you are not the intended recipient, please contact the sender by
> reply e-mail and destroy any and all copies of the original message.
> Thank you.
> ----------
> ----------------------------------------------------------------------
> Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
> home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
> page select "Join or Leave the list". If you will be out of the
> office, send the SET DB2-L NO MAIL command to
> [login to unmask email] The IDUG List Admins can be reached at
> [login to unmask email] Find out the latest on IDUG
> conferences at http://conferences.idug.org/index.cfm

--
NOTICE: If received in error, please destroy and notify sender. Sender
does not waive confidentiality or privilege, and use is prohibited.


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm