Db2 (On Premises and Cloud)

Expand all | Collapse all

Regression in DROP TABLE statement

Jump to Best Answer
  • 1.  Regression in DROP TABLE statement

    Posted 30 days ago

    There used to be a bug in DB2 where issuing "DROP TABLE alias_name" (where alias_name refers to an alias, not a table) would result in the underlying table getting dropped.  Example:


        CREATE TABLE t1 (C1 INT);


        DROP TABLE a1;


    In DB2 9.7 Fixpack 7, the DROP ALIAS statement is successful, but the table T1 is dropped, not the alias A1.


    In DB2 10.5 Fixpack 5, the DROP ALIAS statement above results in an error, SQL20509N  The module alias "IBJORHOV.A1" cannot be used as the target module of the DDL statement.  


    In the current beta drop, the previous behavior (where the underlying table is dropped, not the alias) occurs.  This appears to be a regression.

  • 2.  RE: Regression in DROP TABLE statement

    Posted 30 days ago



    I just want to be sure I clearly understand the concern.


    My expectation would be that DROP ALIAS A1 would drop the alias A1 while DROP TABLE A1 would drop the table pointed to by A1.


    Your example above shows DROP TABLE A1 but you seem to be concerned that the table was dropped... which is what I think should happen.


    Did you mean to write DROP ALIAS A1?


    I will try this out on the current code base when I get a chance....



  • 3.  RE: Regression in DROP TABLE statement

    Posted 30 days ago



    I would assert that the behavior in 10.5 Fixpack 5 is correct.  A1 is not a table, so issuing a DROP TABLE statement referring to the alias should return an error.  


    If there is some good reason that alias resolution should or needs to occur for DDL, I would think that a warning should be returned indicating that A1 is an alias (at the very least).



    Thank you,

  • 4.  RE: Regression in DROP TABLE statement

    Posted 30 days ago



    Ok, I see your concern. It looks like you want the DROP statement, specifically the DROP TABLE variant, to not support the use of aliases to avoid an unintentional drop of a table. I was confused because you explicitly said DROP ALIAS in your text but used DROP TABLE in the example.


    The documentation simply says that an alias is alternate way to refer to an object and can be used anywhere in SQL statements. And there is no distinction made between DML and DDL statements. Some relevant excerpts from the docs (http://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0000720.html#r0000720__ch2alia)


    Aliases for database objects

    An alias can be thought of as an alternative name for an SQL object. An SQL object, therefore, can be referred to in an SQL statement by its name or by an alias.


    An alias can be used wherever the object it is based on can be used. An alias can be created even if the object does not exist (although it must exist by the time a statement referring to it is compiled). It can refer to another alias if no circular or repetitive references are made along the chain of aliases. An alias can only refer to a module, nickname, sequence, table, view, or another alias within the same database.


    The effect of using an alias in an SQL statement is similar to that of text substitution. The alias, which must be defined by the time that the SQL statement is compiled, is replaced at statement compilation time by the qualified object name.



    What you describe is not a defect as far as I can recall since aliases are simply alternate ways of specifying an object and this behaviour has have been around a long time (aliases were introduced in version 2 or 5 I believe).  In fact, the behaviour in 10.5 FP5 seems incorrect and the error message definitely seems off.


    I will look into this further to see if it is an intentional or unintentional change and let you know.




  • 5.  RE: Regression in DROP TABLE statement

    Posted 30 days ago



    I'm sure you understand the issue, but let me add one more point. If I create a view called V1 and then issue the statement DROP TABLE V1, it will fail with the error:


    SQL0159N  The statement references an object that identifies an unexpected
    object type. Object: "IBJORHOV.V1". Object type: "VIEW". Expected object


    This makes sense, because V1 is a view, not a table.  Following this precedent, I believe it makes more "sense" for DROP TABLE to fail if the object is not actually a table.  

    I'm happy to open a PMR or RFE for this (since it's present in the shipping 11.1 code as well).  Let me know what you think.


    Thank you,

  • 6.  RE: Regression in DROP TABLE statement
    Best Answer

    Posted 30 days ago



    I have more information on what changed and can now explain the behaviour you are seeing.


    First of all, I need to emphasize that the original design point for aliases was to act as generic references to other objects which can be substituted into any SQL statement where object references occur. In that statement, the alias will be resolved to the underlying object and the action expressed against the alias will be taken against that base object. So the behaviour you are referring to has intentionally been there since aliases were introduced into DB2 many years ago. To act against an alias itself, one has to tell DB2 this explicitly by using the ALIAS key word.


    So, to drop an alias you issue DROP ALIAS and to drop the object pointed to by the alias you say DROP<object_type>. E.g.DROP TABLE A1 will work if A1 is defined as pointing to a table object and the table will be dropped; If A1 points to an undefined object or a non-table object (like a view), you will get an error saying "not a table".


    What changed/happened in DB2 10,5 was that another compatibility vector was added to support a different behaviour used by other databases. The new synonym usage control option was introduced for the DB2_COMPATIBILITY_VECTOR  (http://www.ibm.com/support/knowledgecenter/SSEPGG_11.1.0/com.ibm.db2.luw.apdv.porting.doc/doc/r0061820.html).


    When set, this synonym usage bit will prevent the use of synonyms in certain DDL statements like DROP TABLE. Aliases and synonyms are the same thing so aliases are blocked by this bit also.This bit is automatically set when using the ORA compatibility mode.


    So the expected behaviour is:


    synonym usage bit not set

    Aliases used in DDL will resolve to their base objects and be processed as if you specified the base object name in the first place (e.g. DROP TABLE A1 will drop the table)


    synonym usage bit set

    Aliases used in some DDL will result in an error (e.g. DROP TABLE A1 will return -20509)



    It looks like when you did your 10.5 testing, that bit must have been set which is why you saw a different behaviour. If this is the behaviour you prefer, you can regain it by setting the appropriate bit in the compatibility vector.


    Hope this is clear enough and answers your question!




  • 7.  RE: Regression in DROP TABLE statement

    Posted 29 days ago

    ​I am with Shadman on this one.

    There are two aspects to an alias. One is the DDL definition and the other is "operational usage".

    Typically we define an alias because of a limitation of a product. Examples would be:

    1) a product that can only access one defined schema and the database has multiple schemas. So an alias is created on a table with the accessible schema.

    2) A product that limits the length of table or schema name to 18 characters, and the base table/schema name exceeds 18 characters. An alias is created with a shorter name for that product.

    If I no longer need an alias, I should not have to worry about underlying ramifications of executing DDL incorrectly. It could have devastating effects on the operation of the company. 

    The DDL, is a definition of an object and should not be subject to any special treatment. In this case, I would expect the following to occur:

    create table t1 (C1 int);
    create alias A1 for table T1;

    Drop table t1: Result - only table t1 is dropped, Alias A1 is marked as invalid
    Drop alias A1: Result - only the alias is dropped
    Drop table A1: Result - Error. A1 is an alias, not a table
    Drop alias t1: Result - Error. T1 is a table, not an alias.

    The table should NEVER be dropped if the alias is dropped or an incorrect ddl statement is executed. This needs to be tightened up - in my opinion.

    Douglas Kostelnik
    Applications Architect
    The Auto Club Group
    Tampa FL