DROP ALIAS unable to drop existing alias?

Question

We're unable to drop aliases. So, I found an alias by running:

SELECT
    TABLE_NAME
FROM
    QSYS2.SYSTABLES
WHERE
    BASE_TABLE_NAME IN ('OE0711P','OE0712P')
    AND TABLE_TYPE = 'A'
    AND SYSTEM_TABLE_SCHEMA = 'AMPLIBX';

and picked the first one, which was OE0712PD506202270.

So I tried running DROP ALIAS AMPLIBX.OE0712PD506202270; and it gives the following error:

SQL State: 42704

Vendor Code: -204

Message: [SQL0204] OE07100001 in AMPLIBX type *FILE not found. Cause . . . . . :   OE07100001 in AMPLIBX type *FILE was not found. If the member name is *ALL, the table is not partitioned. If this is an ALTER TABLE statement and the type is *N, a constraint or partition was not found. If this is not an ALTER TABLE statement and the type is *N, a function, procedure, trigger or sequence object was not found. If a function was not found, OE07100001 is the service program that contains the function.  The function will not be found unless the external name and usage name match exactly.  Examine the job log for a message that gives more details on which function name is being searched for and the name that did not match. Recovery  . . . :   Change the name and try the request again.  If the object is a node group, ensure that the DB2 Multisystem product is installed on your system and create a nodegroup with the CRTNODGRP CL command. If an external function was not found, be sure that the case of the EXTERNAL NAME on the CREATE FUNCTION statement exactly matches the case of the name exported by the service program.

The job log only says:

OE07100001 in AMPLIBX type *FILE not found.

...but how can it not be found? I literally just found it in the metadata.

Also note: Weird behaviour when trying to delete via the IBM i Access Client Solutions app, as well.

• The summary shows the 1097 existing aliases. But when I right-click on the first one and select Definition, it has a different name from the one I selected. Both are existing (but separate) aliases according to the QSYS2.SYSTABLES.
• When I try to right-click then Delete from the summary, nothing happens. No error message, not deleted.

Am I doing something wrong? How can I drop these aliases?

Answer 1

What version of IBM i?

You may want to try running Reclaim DB Cross-Reference (RCLDBXREF) command with the option(*CHECK).

Answer 2

Apparently someone else managed to fix it by running IPL and RECSTG.