Topic
  • 1 reply
  • Latest Post - ‏2012-10-04T16:50:23Z by nivanov1
SystemAdmin
SystemAdmin
17917 Posts

Pinned topic Problem droping Alias for module

‏2012-10-04T03:22:02Z |
Hi,

I was developing a module, and I created an alias for it. After that, I wanted to drop it, but it was unsuccesful. What do you think the problem is?

I tested this script in DB2 10.1 in Windows x64 and Linux x64, Express-C and Enterprise editions.

This is the script I run (script.sql):


CREATE MODULE TESTS;   CREATE ALIAS T2 FOR MODULE TESTS;   CREATE PUBLIC ALIAS T1 FOR MODULE TESTS;   DROP ALIAS T2;   DROP PUBLIC ALIAS T1;   DROP MODULE TESTS;   DROP ALIAS T2;   DROP PUBLIC ALIAS T1;


These are the results of the execution: db2 -tvf script.sql


CREATE MODULE TESTS DB20000I  The SQL command completed successfully.   CREATE ALIAS T2 FOR MODULE TESTS DB20000I  The SQL command completed successfully.   CREATE PUBLIC ALIAS T1 FOR MODULE TESTS DB20000I  The SQL command completed successfully.   DROP ALIAS T2 DB21034E  The command was processed as an SQL statement because it was not a valid Command Line Processor command.  During SQL processing it returned: SQL0204N  
"DB2INST1.T2" is an undefined name.  SQLSTATE=42704   DROP PUBLIC ALIAS T1 DB21034E  The command was processed as an SQL statement because it was not a valid Command Line Processor command.  During SQL processing it returned: SQL0204N  
"SYSPUBLIC.T1" is an undefined name.  SQLSTATE=42704   DROP MODULE TESTS DB20000I  The SQL command completed successfully.   DROP ALIAS T2 DB21034E  The command was processed as an SQL statement because it was not a valid Command Line Processor command.  During SQL processing it returned: SQL0204N  
"DB2INST1.T2" is an undefined name.  SQLSTATE=42704   DROP PUBLIC ALIAS T1 DB21034E  The command was processed as an SQL statement because it was not a valid Command Line Processor command.  During SQL processing it returned: SQL0204N  
"SYSPUBLIC.T1" is an undefined name.  SQLSTATE=42704


I checked the catalog, and I can see the aliases:


db2 
"select * from syscat.modules where moduleschema = 'DB2INST1'"   MODULESCHEMA MODULENAME MODULEID DIALECT OWNER    OWNERTYPE MODULETYPE BASE_MODULESCHEMA BASE_MODULENAME CREATE_TIME                REMARKS DB2INST1     T2         18               DB2INST1 U         A          DB2INST1          TESTS           2012-10-03-18.35.40.571962 -


As you can see, it is not possible drop a module.
Updated on 2012-10-04T16:50:23Z at 2012-10-04T16:50:23Z by nivanov1
  • nivanov1
    nivanov1
    231 Posts

    Re: Problem droping Alias for module

    ‏2012-10-04T16:50:23Z  
    If you check the DROP statement syntax you'll see that you need to specify what alias type you want to drop, like "drop alias t2 for module". If you don't, DB2 assumes you're dropping a table alias, which, obviously, does not exist.