Hi Everybody,
I am trying to ALTER TABLE to change the width of decimal(8,2) to decimal(15,2) but failing.
Here is the error msg.:
Code:
C:\Program Files\IBM\SQLLIB\BIN>db2 ALTER TABLE transaction_totals ALTER COLUMN
MONTHLY_TOTAL_AMT SET DATA TYPE decimal(15,2)
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token "ALTER TABLE TRANSACTION_TOTALS ALTER COLUM" was
found following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<values>". SQLSTATE=42601
But I tried at CC to change the same column there was error No2:
Code:
[IBM][CLI Driver][DB2/NT64] SQL0443N Routine "ALTOBJ" (specific
name "") has returned an error SQLSTATE with diagnostic text
"SQL0444 Reason code or token:
*_TBLNDEP|DB2LK_TBLNDEP|...QLLI". SQLSTATE=38553
Explanation:
An SQLSTATE was returned to DB2 by routine "<routine-name>"
(specific name "<specific-name>"), along with message text
"<text>". The routine could be a user-defined function or a
user-defined method.
User Response:
The user will need to understand the meaning of the error. See
your Database Administrator, or the author of the routine.
Errors that are detected by the IBM supplied functions in the
SYSFUN schema all return the SQLSTATE 38552. The message text
portion of the message is of the form:
SYSFUN:nn
where nn is a reason code meaning:
01 Numeric value out of range
02 Division by zero
03 Arithmetic overflow or underflow
04 Invalid date format
05 Invalid time format
06 Invalid timestamp format
07 Invalid character representation of a timestamp duration
08 Invalid interval type (must be one of 1, 2, 4, 8, 16, 32, 64,
128, 256)
09 String too long
10 Length or position in string function out of range
11 Invalid character representation of a floating point number
12 Out of memory
13 Unexpected error
Errors that are detected by the IBM supplied routines in the
SYSIBM or SYSPROC schemas and IBM supplied procedures in the
SYSFUN schema all return the SQLSTATE 38553. The message text
portion of the message contains a message number that may be an
SQLCODE, (for example, SQL0572N), a DBA error message (for
example, DBA4747), or some other indication from the routine as
to what error was encountered. Note that if the message for the
message number would normally contain tokens, these token values
are only available in the db2diag.log file.
sqlcode : -443
sqlstate : (the SQLSTATE returned by the routine).
by the way the SQL in CC was :
Code:
CALL SYSPROC.ALTOBJ ( 'APPLY_CONTINUE_ON_ERROR', 'CREATE TABLE DB2ADMIN.TRANSACTION_TOTALS
( TRANS_TYPE CHARACTER (2) NOT NULL , BANK_ID CHARACTER (2) NOT NULL , CARD_TYPE CHARACTER (2) NOT NULL ,
TOTAL BIGINT WITH DEFAULT 0 , TIMESTAMP TIMESTAMP , ACTIVE CHARACTER (1) NOT NULL WITH DEFAULT 'Y' ,
WEEKLY_TOTAL INTEGER NOT NULL WITH DEFAULT 0 , MONTHLY_TOTAL INTEGER NOT NULL WITH DEFAULT 0 ,
DAILY_TOTAL_AMT DECIMAL (8, 2) NOT NULL WITH DEFAULT 0 ,
WEEKLY_TOTAL_AMT DECIMAL (8, 2) NOT NULL WITH DEFAULT 0 ,
MONTHLY_TOTAL_AMT DECIMAL (15, 2) NOT NULL WITH DEFAULT 0 ,
PROCESSOR_GROUP VARCHAR (20) NOT NULL WITH DEFAULT '' ,
TOTAL_ATTEMPTS INTEGER WITH DEFAULT 0 ) IN TSD_SA ', -1, ? );
;
So at the moment I have two problems
1) Alter Table command not working V8.2 FP 11
2) Changing Column width works on mirror but not production database.
Please Help ( we cannot lock Prod databse for long time)
Thanks in Advance
DBFinder
dBforums
>
Alter Table Alter Column fails
10-23-08, 11:03
Linear Mode
