ALTER TABLE ERROR

[Randy]

I am trying to change a column to NOT NULLABLE and I get the following
message.

=SQL
GENERATED========================================= =======================
CONNECT TO TEST;
CALL SYSPROC.ALTOBJ ( 'APPLY_CONTINUE_ON_ERROR', 'CREATE TABLE
RANDMAN.ACCTADDRESS ( ACCT BIGINT NOT NULL , FY BIGINT NOT NULL , PROV
CHARACTER (2) , AMT DECIMAL (8, 2) ) IN DB2_GAST_DATA ', -1, ? );
;
CONNECT RESET;

=ERR MESSAGE
================================================== =================

[IBM][CLI Driver][DB2/NT] SQL0443N Routine "ALTOBJ" (specific
name "") has returned an error SQLSTATE with diagnostic text
"SQL0443 Reason code or token:
SYSPROC.DB2LK_TBLNDEP|DB2LK_TBL". 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).

[adik_q]

I have the same errors when any existing stored procedure used this
table. Additionally alter operation will not succeed when altered table
is used in a function. It seems like ALT_OBJ procedure doesn't get
into consideration such objects.
List of objects using a table can be get by function
sysproc.db2lk_dep_of.
i.e.: SELECT * FROM TABLE (sysproc.db2lk_dep_of('T', 'schema',
'table_name')).

You can write a SP which saves all SP's and functions that are
referencing the table being altered. Then you can save SP's and
functions, call ALT_OBJ and recreate saved objects.

I wrote some procedures that generate all DDL for specified table and
insert this into tmp tables (something like copy of syscat views). I
can use DML on this tmp tables to i.e. remove column from table. Then I
regenerate DDL for table from this tmp tables and put into ALT_OBJ
stored procedures. This is very useful when change must be propagated
in many databases. If you want to more details, please, let me know.

Regards,
Adrian

[Eric]

The problem is the extra space between after altobj ( if you can the
statement to altobj( then it will work this is a bug with altobj and is
fixed in fixpak 11

[Eric]

This is a bug with atlobj if you change the command to remove the space
between altobj ( to read altobj( this will then work. This bug is fixed
in fixpak 11