ALTER TABLE ... ALTER COLUMN ... SET DATA TYPE

[Jane]

Does any one know why this statement is failing?

db2 => ALTER TABLE ELMT_T ALTER COLUMN CDTY_CD SET DATA TYPE VARCHAR(51)

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0901N The SQL statement failed because of a non-severe system error.
Subsequent SQL statements can be processed. (Reason "this->qunqtbssc.op() !=
NULLP".) SQLSTATE=58004

Thanks,
-Jane

[Blair Adamache]

Your syntax is correct, at least for a table on Linux/UNIX/Windows if
its current data type is varchar with a length of 50 or less:

db2 create TABLE ELMT_T (CDTY_CD varchar(50))
DB20000I The SQL command completed successfully.

db2 ALTER TABLE ELMT_T ALTER COLUMN CDTY_CD SET DATA TYPE VARCHAR(51)
DB20000I The SQL command completed successfully.
Jane wrote:

Does any one know why this statement is failing?

db2 => ALTER TABLE ELMT_T ALTER COLUMN CDTY_CD SET DATA TYPE VARCHAR(51)

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0901N The SQL statement failed because of a non-severe system error.
Subsequent SQL statements can be processed. (Reason "this->qunqtbssc.op() !=
NULLP".) SQLSTATE=58004

Thanks,
-Jane

[Douglas Doole]

SQLCODE -901 generally indicates something's wrong internally. You should
contact DB2 support.
--
__________________________________________________ ___________________
Doug Doole
DB2 Universal Database Development
IBM Toronto Lab

Visit the DB2 UDB and DB2 Connect Online Support site at:
http://www.ibm.com/software/data/db2...s2unix/support

[Jane]

Currently the cdty_cd is varchar(48). I am trying to extend it to varchar(51).

Column Type Type
name schema name Length Scale Nulls
----- --------- ------------------ -------- ----- -----

CDTY_CD SYSIBM VARCHAR 48 0 No

Could this be a environment issue? Is this a bug in DB2?
The 'ALTER' statement works for some tables and not for others.

Thanks
-Jane

[Mark A]

"Jane" <ja**********@i2.com> wrote in message
news:75**************************@posting.google.c om...

Currently the cdty_cd is varchar(48). I am trying to extend it to varchar(51).
Column Type Type
name schema name Length Scale Nulls
----- --------- ------------------ -------- ----- -----

CDTY_CD SYSIBM VARCHAR 48 0 No

Could this be a environment issue? Is this a bug in DB2?
The 'ALTER' statement works for some tables and not for others.

Thanks
-Jane

What is your fixpak level?

[Jane]

I have opened a PMR for this issue. We are on DB2 7.2 Fix Pack 6. We
are running on AIX.

Thanks,
-Jane

[Serge Rielau]

Jane,

My turf :-( Can you send me a note with the PMR#?
Alternatively have support contact me (Serge Rielau, sr*****@ca.ibm.com).
Make sure to pass a repro script to support (no need for data or stats).
db2trc would be helpful as well.
Cheers
Serge

[Blair Adamache]

The PMR is 76910,004,000

Serge Rielau wrote:

Jane,

My turf :-( Can you send me a note with the PMR#?
Alternatively have support contact me (Serge Rielau, sr*****@ca.ibm.com).
Make sure to pass a repro script to support (no need for data or stats).
db2trc would be helpful as well.
Cheers
Serge

[Jane]

Spoke to someone from IBM and I am told that if there is a view on the
table, the table columns cannot be extended.

Solution:
-Drop the view
-ALTER the table and Extend the varchar column.

Nothing is easy in DB2 ...

Thanks for everyone's input.
-Jane

[Sean McKeough]

Jane,

What you were told is not true in general. This should only be the case
if you have a cyclical view, or if something like implicit type
promotion (such as varchar->long varchar due to concats) causes the
column type to change where you have a udf in the view, and implicit
recreate would cause the semantics of the view to change (conservative
bind)...I just did a quick test locally, and was able to extend the
column length with a view on the table...

My ddl was:
create table t1(c1 varchar(100))
insert some data...
create view foo(c1) as select c1 from t1
alter table t1 alter column c1 set data type varchar (200)

Ran ok...do you know if the view in question fit into one of the cases I
describe above?

Jane wrote:

Spoke to someone from IBM and I am told that if there is a view on the
table, the table columns cannot be extended.

Solution:
-Drop the view
-ALTER the table and Extend the varchar column.

Nothing is easy in DB2 ...

Thanks for everyone's input.
-Jane

[Blair Adamache]

This should be easy in DB2. The statements below work in v8. If they
fail on your v7 system, you should have the PMR reopened and get an APAR:

C:\tmp> db2 create TABLE ELMT_T (CDTY_CD varchar(48))
DB20000I The SQL command completed successfully.

C:\tmp>db2 create view elem2 as select cdty_cd from elmt_t
DB20000I The SQL command completed successfully.

C:\tmp>db2 ALTER TABLE ELMT_T ALTER COLUMN CDTY_CD SET DATA TYPE VARCHAR(51)
DB20000I The SQL command completed successfully.

Jane wrote:

Spoke to someone from IBM and I am told that if there is a view on the
table, the table columns cannot be extended.

Solution:
-Drop the view
-ALTER the table and Extend the varchar column.

Nothing is easy in DB2 ...

Thanks for everyone's input.
-Jane