473,893 Members | 1,499 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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
Nov 12 '05 #1
10 160588
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


Nov 12 '05 #2
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
Nov 12 '05 #3
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
Nov 12 '05 #4
"Jane" <ja**********@i 2.com> wrote in message
news:75******** *************** ***@posting.goo gle.com...
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?
Nov 12 '05 #5
I have opened a PMR for this issue. We are on DB2 7.2 Fix Pack 6. We
are running on AIX.

Thanks,
-Jane
Nov 12 '05 #6
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
Nov 12 '05 #7
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


Nov 12 '05 #8
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
Nov 12 '05 #9
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


Nov 12 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
21277
by: Bruce | last post by:
Hi, I want to change the datatype of an existing column from char to varbinary. When I run the "Alter Table" statement, I get the following error message - Disallowed implicit conversion from data type char to data type varbinary, table 'test.dbo.testalter', column 'col1'. Use the CONVERT function to run this query.
2
21364
by: me | last post by:
I would like to add an Identity to an existing column in a table using a stored procedure then add records to the table and then remove the identity after the records have been added or something similar. here is a rough idea of what the stored procedure should do. (I do not know the syntax to accomplish this can anyone help or explain this? Thanks much, CBL
3
22651
by: Prince Kumar | last post by:
Is there any way I can define an Unique constraint or unique index which allows more than one null values for the same column combination in DB2? ie, If my index is defined on (col3, col4) where both columns allow nulls, I want col3 + col4 to be unique, if one or both the columns have values. If both columns have nulls, it should allow more than one such rows. ex,
1
6701
by: p175 | last post by:
People, I have the following table and I need to add a column that uses a the listed UDF to count the number of days within specific quarters by year. CREATE TABLE HISTORY_MB ( ID INTEGER NOT NULL, DAYS_RL INTEGER NOT NULL, DAYS_RH INTEGER NOT NULL, DAYS_RB INTEGER NOT NULL, QTR SMALLINT NOT NULL,
5
49705
by: Mike L | last post by:
Hello all I'm trying to migrate a BE from Access to SQL Server. I've been making changes to the Access BE from the FE with SQL statements, and want to do the same with the BE in SQL Server. When running the statement "ALTER TABLE tblTest DROP COLUMN TestColumn3" from the FE with a SQL Server BE, it works OK. Running the statement "ALTER TABLE tblTest ADD COLUMN TestColumn3
3
9261
by: Gregor KovaĨ | last post by:
Hi! I have a table with a column of BIGINT and I want to change the type to SMALLINT. Command ALTER TABLE TABLE1 ALTER COLUMN COL1 SET DATA TYPE SMALLINT does not work since I cannot specify SMALLINT in set data type clause (acording to http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0000888.htm) Then I thought I could drop the column: ALTER TABLE TABLE1 DROP COLUMN COL1 but running...
1
10490
by: jmarr02s | last post by:
I am trying to change my Amount column Data Type from Integer to Decimal (precision 9 digits, scale 3, that is 6 digits to the left of decimal and 3 digits to the right of decimal. Here is the error message I received SQL0443N Routine "SYSPROC.ALTOBJ" I sense this is a security issue, something my DBA must resolve and some aspect I do not have permission to do? Thanks,
0
1476
by: Daniel | last post by:
how does ado.net SqlDataReader.GetString() know which encoding to read the data into a string as? Does sql sever set this at the column data type level, server wide encoding setting, os encoding?
2
5779
by: bogie | last post by:
Hello I have some problem to change my column data type in my table property from character(15) to character(100). I use Postgresql 7.4. The problem is I try to make new column with charcater(100) then i copy the data from teh original column to the new column, then i try to drop the column but i get the following ERROR : Cannot drop table invention column invention_cite because the other objects depend on it. Is any body can help me to...
0
9982
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11243
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10927
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10469
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9645
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8021
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
4684
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4280
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3289
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.