Why is my alter table command succesful on INcreasing the datatype
length, but not on DEcreasing the datatype length?

Currently: Dummy = VARCHAR(10)

ALTER TABLE MyTable ALTER COLUMN Dummy SET DATA TYPE VARCHAR(100)
The SQL command completed successfully.

ALTER TABLE MyTable ALTER COLUMN Dummy SET DATA TYPE VARCHAR(200)
The SQL command completed successfully.

ALTER TABLE MyTable ALTER COLUMN Dummy SET DATA TYPE VARCHAR(199)
Error: [IBM][iSeries Access ODBC Driver]Operation cancelled.

--
Twan

"Twan" <no-reply (AT) skb (DOT) nl> wrote

Don't ask us. Why are you using DB2 iSeries instead of a real database?

On Oct 13, 7:37*pm, "Mark A" <no... (AT) nowhere (DOT) com> wrote:
Yep, this behaviour ****.

Mark, which one do you propose? ;-)

--
Frederik Engelen

Oh, sorry I bothered you! :-?
I didn't know you are representing all members of this newsgroup.

By the way.... if someone else is dealing with the same problem, just
for the record:
such changes (decreasing the data type of a column) should be done with
SYSPROC.ALTOBJ encapsulation.

see:
http://publib.boulder.ibm.com/infoce...n/r0011934.htm

Twan




"Mark A" <noone (AT) nowhere (DOT) com> wrote

"Twan" <no-reply (AT) skb (DOT) nl> wrote

Lighten up, just some database humor.

The reason why I say it is not a real database is that one can access data
(in readable format) on an iSeries machine with the native file system
access commands, or with SQL (which is just layer on top of the OS file
access system).

The fact is that not many people know anything about DB2 for iSeries, which
isn't even mentioned here:
http://www-01.ibm.com/software/data/db2/
DB2 for iSeries is different than DB2 for LUW or DB2 for z/OS. Best not to
confuse them when asking technical question and you should state that your
question and proposed answer are for DB2 iSeries (you should also mention
what release you are using).

I am not sure why you quoting a link from the DB2 LUW V8 InfoCenter Doc
instead of the DB2 iSeries Info Center (not to mention that DB2 LUW V8 is no
longer supported by IBM).

On 10/13/2010 12:11 PM, Twan wrote:
The last ALTER TABLE has the potential to truncate data by shortening a
column.
ALTER TABLE MyTable ALTER COLUMN Dummy SET DATA TYPE VARCHAR(199)

DB2 for i detects this condition, and the server job sends an inquiry
message asking if truncation is acceptable.
CPD32CC (Diag) : Change to field DUMMY may result in data loss.
CPA32B2 (Inquiry) : Change of file MyTable may cause data to be lost.
(C I)
Because the ODBC interface has no mechanism for answering, the default
reply - C for Cancel - is taken, resulting in SQL0952 returned to the
ODBC client (Processing of the SQL statement ended.)

One way to handle this is to temporarily provide a message "reply list
entry", to set 'I' (for Ignore) reply for the CPA32B2 message. For
example if running the ALTER using Navigator Run SQL Scripts:

cl: CHGJOB INQMSGRPY(*SYSRPYL);
cl: ADDRPYLE SEQNBR(15) MSGID(CPA32B2) RPY('I') ;
-- if you wanted to further narrow the scope of the reply list entry you
could also specify CMPDTA so that it only applies to a specific schema
or table.
-- now perform your ALTER
cl: RMVRPYLE SEQNBR(15);

For more expertise on DB2 for i, you might try this newsgroup:
comp.sys.ibm.as400.misc.

Links of possible interest:
www.ibm.com/systems/i/software/db2/
publib.boulder.ibm.com/iseries/
http://www.ibm.com/developerworks/da...cle/db2common/

--
Karl Hanson