Is there an efficient way to remove each view / function / table / sp from the database?

Question

In a unified DB2 database (based on remote servers and aliases), I need to clear the model and recreate it from another database. I need to delete every database object except those servers and aliases.

I know how to get a list of objects from a SYSCAT schema. Now I need to run the DROP instructions for each. Obviously, dependencies will interfere.

A brute force approach would be to run DROP in a loop until everything works, but depending on the order (lucky or not) it can take a very long time.

Do you know a way to efficiently arrange a DROP statement so that the total time for deletion is minimal?

An ideal solution is not expected. A reasonably reasonable solution is good enough.

thanks

Answer 1

This request can order approvals according to the total number of elements on which they depend. The resulting order works almost without failures, the second pass of the brute force approach contains only a few objects (out of several thousand objects to be deleted).

The problem is, it is very slow ...

EDIT: There was a typo in the request that made it return more or less correct data, but very slowly.

WITH FIRST_LEVEL_DEPENDENCIES (BSCHEMA, BNAME, DTYPE, DSCHEMA, DNAME) AS ( SELECT T1.TABSCHEMA AS BSCHEMA, T1.TABNAME AS BNAME, T1.BTYPE, T1.BSCHEMA, T1.BNAME FROM SYSCAT.TABDEP T1 WHERE T1.TABSCHEMA NOT LIKE 'SYS%' AND T1.BTYPE <> 'N' UNION ALL SELECT T1.ROUTINESCHEMA AS BSCHEMA, T1.SPECIFICNAME AS BNAME, T1.BTYPE, T1.BSCHEMA, T1.BNAME FROM SYSCAT.ROUTINEDEP T1 WHERE T1.ROUTINESCHEMA NOT LIKE 'SYS%' AND T1.BTYPE <> 'N' UNION ALL SELECT T1.TABSCHEMA AS BSCHEMA, T1.TABNAME AS BNAME, 'T', T1.REFTABSCHEMA, T1.REFTABNAME FROM SYSCAT.REFERENCES T1 WHERE T1.TABSCHEMA NOT LIKE 'SYS%' ), RECURSIVE_DEPENDENCIES (LEVEL, BSCHEMA, BNAME, DTYPE, DSCHEMA, DNAME) AS ( SELECT 1, U.BSCHEMA, U.BNAME, U.DTYPE, U.DSCHEMA, U.DNAME FROM FIRST_LEVEL_DEPENDENCIES AS U UNION ALL SELECT LEVEL + 1, REC.BSCHEMA, REC.BNAME, U.DTYPE, U.DSCHEMA, U.DNAME FROM RECURSIVE_DEPENDENCIES REC, FIRST_LEVEL_DEPENDENCIES U WHERE LEVEL < 6 AND U.BSCHEMA = REC.DSCHEMA AND U.BNAME = REC.DNAME ) SELECT BSCHEMA, BNAME, COUNT(*) FROM RECURSIVE_DEPENDENCIES GROUP BY BSCHEMA, BNAME ORDER BY COUNT(*) 

Answer 2

You might want to see the links of each table (which you can do with syscat.references according to http://www.ibm.com/developerworks/data/library/techarticle/dm-0401melnyk/ ) and build the dependency tree yourself (should run, for example, with temporary tables if you are only limited to sql). Then you can fall from the bottom of this tree.

So, basically, my answer to your question will be that in order to do this quickly, just order tables based on the links that they have with each other before deleting. Since there should not be any dependency cycles, you should always be able to select one table that is not referenced. Drop it and repeat.

You might also want to see this (similar?) Question: DB2 cascade delete command? if you want to delete the data first.

If I am mistaken at some point, please correct. This answer is based on my experience with other databases, so it may not be entirely suitable for DB2. Although it should work;)

Answer 3

I do not have a DIRECT solution for DB2, but I can assume that:

A) In Microsoft SQL Server 2008, the problem with deleting (not DROP) tables regarding the order of foreign keys at this link was solved:

Generate a delete command from external relationships in SQL 2008?

B) In Oracle PL / SQL, the problem of deleting (not DROP) was solved taking into account the order of foreign keys at this link:

How to generate DELETE statements in PL / SQL based on FK relationship tables?

I think you can organize one of these two scenarios to get a solution for DB2.

Do you agree or not?

EDIT 1: From this link:

http://bytes.com/topic/db2/answers/183189-how-delete-tables-completely

I can read:

Robert why not just

 LOAD FROM /dev/null of del replace into tablename NONRECOVERABLE 

- It cuts the table very quickly, not sure if it by default updates statistics on space? This has the added benefit that you do not need to uninstall in the correct RI order. (although after that you will have to execute SET INTEGRITY) OK

EDIT 2: see the following:

Removing a schema and all its contents in DB2 8.x