How would I drop a schema and all of its contents, using SQL, in DB2 8.x without knowing what the content is?
+1
A:
use the comand centre-GUI to drop all contents of the schema, then DROP SCHEMA
Peter Miehle
2009-03-05 14:39:06
+3
A:
I do not have a schema to drop at hand, but the infocenter (http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/t0005230.htm) says that a DROP SCHEMA [schema name] shall work on DB2 8.x
EDIT: Ok, the Infocenter also says: "Before dropping a schema, all objects that were in that schema must be dropped themselves or moved to another schema"
So, need to drop all the objects in the schema first. The objects (tables, views, triggers, procedures, indexes...) can be listed quering the catalog views in SYSIBM schema.
E.g. to get all tables that belong to schema, run:
select table_name from sysibm.tables where table_schema = '[your schema name]'
Check the other sysibm views to get all objects that belong into a schema. the views are: sysibm.views, sysibm.triggers, sysibm.routines, sysibm.indexes, ... - consult IBM's Infocenter (the link above) for details.
david a.
2009-03-05 20:16:52
what is the difference between sysibm.tables and syscat.tables? I always look in syscat.tables for information.
Peter Miehle
2009-03-19 13:09:33
AFAIK SYSCAT just contains views that are based on SYSIBM tables and are mean to provide a DB catalog to the user. So, yes, your approach is probably better - I just got used to sysibm view already (and it does not make much difference in the above example)
david a.
2009-03-19 14:16:06