Dopping a schema DB2 UDB v8.1.9

[Bob Stearns]

The syntax diagram of DROP SCHEMA requires RESTRICT. Is there an easy
way to drop an old, unnecessary, but populated schema?

[Eugene F]

You can create a (shell/CLP, perl, java, etc.) script or a stored proc
that reads the database dictionary (system catalog) and dinamicaly
builds the list of DROP statements for the unnecessary objects
(remember about dependences between objects) and finally issues DROP
SCHEMA ... RESTRICT when the schema(s) empty.

-Eugene

[Serge Rielau]

Bob Stearns wrote:

The syntax diagram of DROP SCHEMA requires RESTRICT. Is there an easy
way to drop an old, unnecessary, but populated schema?

Of course :-)

DROP PROCEDURE DROPSCHEMA
@
CREATE PROCEDURE DROPSCHEMA(IN pschemaname VARCHAR(128))
BEGIN
DECLARE txt VARCHAR(1000);
DECLARE progress INTEGER;
DECLARE success SMALLINT;

REPEAT
SET progress = 0;
FOR thisObject
AS SELECT type, name FROM
(SELECT type, tabname FROM SYSCAT.TABLES WHERE tabschema =
pschemaname
UNION ALL
SELECT 's', seqname FROM SYSCAT.SEQUENCES WHERE seqschema =
pschemaname
AND seqtype = 'S'
UNION ALL
SELECT routinetype, specificname FROM SYSCAT.ROUTINES
WHERE routineschema = pschemaname
AND routinetype IN ('F', 'P')
AND origin NOT IN ('S', 'T')
UNION ALL
SELECT 't', typename FROM SYSCAT.DATATYPES WHERE typeschema =
pschemaname
UNION ALL
SELECT 'g', trigname FROM SYSCAT.TRIGGERS WHERE trigschema =
pschemaname)
AS T(type, name)
ORDER BY name
DO
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '42893', SQLSTATE '42704'
BEGIN
SET success = 0;
END;
SET success = 1;
SET txt = 'DROP '
|| CASE type
WHEN 'T' THEN 'TABLE '
WHEN 'S' THEN 'TABLE '
WHEN 'U' THEN 'TABLE '
WHEN 'N' THEN 'NICKNAME '
WHEN 'A' THEN 'ALIAS '
WHEN 's' THEN 'SEQUENCE '
WHEN 'V' THEN 'VIEW '
WHEN 'W' THEN 'VIEW '
WHEN 'F' THEN 'SPECIFIC FUNCTION '
WHEN 'P' THEN 'SPECIFIC PROCEDURE '
WHEN 't' THEN 'TYPE '
WHEN 'g' THEN 'TRIGGER'
END
|| '"' || pschemaname || '"."' || name || '"';
EXECUTE IMMEDIATE txt;
IF success = 1 THEN
SET progress = 1;
END IF;
END;
END FOR;
UNTIL progress = 0 END REPEAT;
SET txt = 'DROP SCHEMA "' || pschemaname || '" RESTRICT';
EXECUTE IMMEDIATE txt;
END
@

Talking of which... I'm looking of "beta-testers" for my backup/restore
schema developer works article-to-be. Any takers?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Bob Stearns]

Serge Rielau wrote:

Bob Stearns wrote:

The syntax diagram of DROP SCHEMA requires RESTRICT. Is there an easy
way to drop an old, unnecessary, but populated schema?

Of course :-)

DROP PROCEDURE DROPSCHEMA
@
CREATE PROCEDURE DROPSCHEMA(IN pschemaname VARCHAR(128))
BEGIN
DECLARE txt VARCHAR(1000);
DECLARE progress INTEGER;
DECLARE success SMALLINT;

REPEAT
SET progress = 0;
FOR thisObject
AS SELECT type, name FROM
(SELECT type, tabname FROM SYSCAT.TABLES WHERE tabschema =
pschemaname
UNION ALL
SELECT 's', seqname FROM SYSCAT.SEQUENCES WHERE seqschema =
pschemaname
AND seqtype = 'S'
UNION ALL
SELECT routinetype, specificname FROM SYSCAT.ROUTINES
WHERE routineschema = pschemaname
AND routinetype IN ('F', 'P')
AND origin NOT IN ('S', 'T')
UNION ALL
SELECT 't', typename FROM SYSCAT.DATATYPES WHERE typeschema =
pschemaname
UNION ALL
SELECT 'g', trigname FROM SYSCAT.TRIGGERS WHERE trigschema =
pschemaname)
AS T(type, name)
ORDER BY name
DO
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '42893', SQLSTATE '42704'
BEGIN
SET success = 0;
END;
SET success = 1;
SET txt = 'DROP '
|| CASE type
WHEN 'T' THEN 'TABLE '
WHEN 'S' THEN 'TABLE '
WHEN 'U' THEN 'TABLE '
WHEN 'N' THEN 'NICKNAME '
WHEN 'A' THEN 'ALIAS '
WHEN 's' THEN 'SEQUENCE '
WHEN 'V' THEN 'VIEW '
WHEN 'W' THEN 'VIEW '
WHEN 'F' THEN 'SPECIFIC FUNCTION '
WHEN 'P' THEN 'SPECIFIC PROCEDURE '
WHEN 't' THEN 'TYPE '
WHEN 'g' THEN 'TRIGGER'
END
|| '"' || pschemaname || '"."' || name || '"';
EXECUTE IMMEDIATE txt;
IF success = 1 THEN
SET progress = 1;
END IF;
END;
END FOR;
UNTIL progress = 0 END REPEAT;
SET txt = 'DROP SCHEMA "' || pschemaname || '" RESTRICT';
EXECUTE IMMEDIATE txt;
END
@

Talking of which... I'm looking of "beta-testers" for my backup/restore
schema developer works article-to-be. Any takers?

Cheers
Serge

Thanks a lot, I will try this later today.

I'd be glad to be a beta tester for you. This email address should work.

[aj]

Serge:

Thats a very cool procedure.

You wouldn't happen to have one that would show me all dependent
objects when I am about to drop a certain table, would you?

My reason:
I'm on LUW 8.1 FP5, so I can't drop columns yet. I want to drop
and recreate tables so that I can drop columns in them.

I don't mind so much recreating all the dependent objects by hand,
its just that *identifying* them is a pain..

Any help appreciated.

aj
Serge Rielau wrote:

Bob Stearns wrote:

The syntax diagram of DROP SCHEMA requires RESTRICT. Is there an easy
way to drop an old, unnecessary, but populated schema?

Of course :-)

DROP PROCEDURE DROPSCHEMA
@
CREATE PROCEDURE DROPSCHEMA(IN pschemaname VARCHAR(128))
BEGIN
DECLARE txt VARCHAR(1000);
DECLARE progress INTEGER;
DECLARE success SMALLINT;

REPEAT
SET progress = 0;
FOR thisObject
AS SELECT type, name FROM
(SELECT type, tabname FROM SYSCAT.TABLES WHERE tabschema =
pschemaname
UNION ALL
SELECT 's', seqname FROM SYSCAT.SEQUENCES WHERE seqschema =
pschemaname
AND seqtype = 'S'
UNION ALL
SELECT routinetype, specificname FROM SYSCAT.ROUTINES
WHERE routineschema = pschemaname
AND routinetype IN ('F', 'P')
AND origin NOT IN ('S', 'T')
UNION ALL
SELECT 't', typename FROM SYSCAT.DATATYPES WHERE typeschema =
pschemaname
UNION ALL
SELECT 'g', trigname FROM SYSCAT.TRIGGERS WHERE trigschema =
pschemaname)
AS T(type, name)
ORDER BY name
DO
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '42893', SQLSTATE '42704'
BEGIN
SET success = 0;
END;
SET success = 1;
SET txt = 'DROP '
|| CASE type
WHEN 'T' THEN 'TABLE '
WHEN 'S' THEN 'TABLE '
WHEN 'U' THEN 'TABLE '
WHEN 'N' THEN 'NICKNAME '
WHEN 'A' THEN 'ALIAS '
WHEN 's' THEN 'SEQUENCE '
WHEN 'V' THEN 'VIEW '
WHEN 'W' THEN 'VIEW '
WHEN 'F' THEN 'SPECIFIC FUNCTION '
WHEN 'P' THEN 'SPECIFIC PROCEDURE '
WHEN 't' THEN 'TYPE '
WHEN 'g' THEN 'TRIGGER'
END
|| '"' || pschemaname || '"."' || name || '"';
EXECUTE IMMEDIATE txt;
IF success = 1 THEN
SET progress = 1;
END IF;
END;
END FOR;
UNTIL progress = 0 END REPEAT;
SET txt = 'DROP SCHEMA "' || pschemaname || '" RESTRICT';
EXECUTE IMMEDIATE txt;
END
@

Talking of which... I'm looking of "beta-testers" for my backup/restore
schema developer works article-to-be. Any takers?

Cheers
Serge

[Serge Rielau]

aj wrote:

Serge:

Thats a very cool procedure.

You wouldn't happen to have one that would show me all dependent
objects when I am about to drop a certain table, would you?

My reason:
I'm on LUW 8.1 FP5, so I can't drop columns yet. I want to drop
and recreate tables so that I can drop columns in them.

I don't mind so much recreating all the dependent objects by hand,
its just that *identifying* them is a pain..

Anyone remember this alien in teh Mickey Mouse books who lived of
mothballs? His name (in the German version) was Gamma and he pulled
amazing thing out of his shorts.... anyway
Have a look at the SYSPROC.DB2LK_DEP_OF() table function.
It's used for the SYSPROC.ALTOBJ() procedure which may be just what you
really need.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Mark Townsend]

Serge Rielau wrote:

Anyone remember this alien in teh Mickey Mouse books who lived of
mothballs? His name (in the German version) was Gamma and he pulled
amazing thing out of his shorts.... anyway

Dude ..... what were you taking ? That sounds like a really, really bad
flashback.

[Serge Rielau]

Mark Townsend wrote:

Serge Rielau wrote:

Anyone remember this alien in teh Mickey Mouse books who lived of
mothballs? His name (in the German version) was Gamma and he pulled
amazing thing out of his shorts.... anyway

Dude ..... what were you taking ? That sounds like a really, really bad
flashback.

Oh contraire. Fond childhood memories when anxiety was defined by having
to wait for the next "Micky Maus Heft".

In case you meant to understood the sentense differently I must remind
you that this is not c.d.ifmx. No sheep jokes here, and rarely a clown
in sight.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[aj]

I am perfectly aware of SYSPROC.ALTOBJ(), but as I indicated in my
original message, I am on LUW 8.1 Fixpak 5. Unless I'm mistaken,
there is no SYSPROC.ALTOBJ() or SYSPROC.DB2LK_DEP_OF() -- hence my
request. I thought you might have something in your bag of gamma
tricks like that DROPSCHEMA procedure.

Thanks anyways

aj

Serge Rielau wrote:

aj wrote:

Serge:

Thats a very cool procedure.

You wouldn't happen to have one that would show me all dependent
objects when I am about to drop a certain table, would you?

My reason:
I'm on LUW 8.1 FP5, so I can't drop columns yet. I want to drop
and recreate tables so that I can drop columns in them.

I don't mind so much recreating all the dependent objects by hand,
its just that *identifying* them is a pain..

Anyone remember this alien in teh Mickey Mouse books who lived of
mothballs? His name (in the German version) was Gamma and he pulled
amazing thing out of his shorts.... anyway
Have a look at the SYSPROC.DB2LK_DEP_OF() table function.
It's used for the SYSPROC.ALTOBJ() procedure which may be just what you
really need.

Cheers
Serge

[Serge Rielau]

aj wrote:

I am perfectly aware of SYSPROC.ALTOBJ(), but as I indicated in my
original message, I am on LUW 8.1 Fixpak 5. Unless I'm mistaken,
there is no SYSPROC.ALTOBJ() or SYSPROC.DB2LK_DEP_OF() -- hence my
request. I thought you might have something in your bag of gamma
tricks like that DROPSCHEMA procedure.

Thanks anyways

aj

Serge Rielau wrote:

aj wrote:

Serge:

Thats a very cool procedure.

You wouldn't happen to have one that would show me all dependent
objects when I am about to drop a certain table, would you?

My reason:
I'm on LUW 8.1 FP5, so I can't drop columns yet. I want to drop
and recreate tables so that I can drop columns in them.

I don't mind so much recreating all the dependent objects by hand,
its just that *identifying* them is a pain..

Anyone remember this alien in teh Mickey Mouse books who lived of
mothballs? His name (in the German version) was Gamma and he pulled
amazing thing out of his shorts.... anyway
Have a look at the SYSPROC.DB2LK_DEP_OF() table function.
It's used for the SYSPROC.ALTOBJ() procedure which may be just what
you really need.

Well, they are SQL Functions. No requirement for a special build to port
them back (you'll likely need to use a different schemaname of course:

CREATE FUNCTION SYSPROC.DB2LK_DEP_FIRST
( DEPTYPE VARCHAR(1),SCHEMA VARCHAR(128),
NAME VARCHAR(128))
RETURNS TABLE(OBJECTTYPE VARCHAR(1), SCHEMA VARCHAR(128),
NAME VARCHAR(128), THIRDNAME VARCHAR(128),
DEFINER VARCHAR(128), CTIME VARCHAR(27))
SPECIFIC DB2LK_DEP_FIRST READS SQL DATA
NO EXTERNAL ACTION DETERMINISTIC
RETURN SELECT 'F', T1.ROUTINESCHEMA, T1.ROUTINENAME,
T2.SPECIFICNAME, T2.DEFINER, T2.CREATE_TIME
FROM SYSCAT.ROUTINEDEP T1, SYSCAT.ROUTINES T2
WHERE T1.BTYPE = DEPTYPE AND T1.BSCHEMA = SCHEMA
AND T1.BNAME = NAME AND T1.ROUTINESCHEMA = T2.ROUTINESCHEMA
AND T1.ROUTINENAME = T2.ROUTINENAME
UNION ALL
SELECT T1.D TYPE, T1.TABSCHEMA, T1.TABNAME,
CAST(NULL AS VARCHAR(128)), T1.DEFINER,
T2.CREATE_TIME
FROM SYSCAT.TABDEP T1, SYSCAT.TABLES T2
WHERE T1.BTYPE = DEPTYPE AND T1.BSCHEMA = SCHEMA
AND T1.BNAME = NAME AND T1.TABSCHEMA = T2.TABSCHEMA
AND T1.TABNAME = T2.TABNAME
UNION ALL
SELECT 'C', T1.TABSCHEMA, T1.TABNAME,
T1.CONSTNAME, T2.DEFINER, T3.CREATE_TIME
FROM SYSCAT.CONSTDEP T1, SYSCAT.TABCONST T2,
SYSCAT.TABLES T3
WHERE T1.BTYPE = DEPTYPE AND T1.BSCHEMA = SCHEMA
AND T1.BNAME = NAME AND T1.CONSTNAME = T2.CONSTNAME
AND T1.TABSCHEMA = T2.TABSCHEMA
AND T1.TABNAME = T2.TABNAME
AND T1.TABSCHEMA = T3.TABSCHEMA
AND T1.TABNAME = T3.TABNAME
UNION ALL
SELECT 'I', INDSCHEMA, INDNAME,
CAST(NULL AS VARCHAR(128)),
CAST(NULL AS VARCHAR(128)),
CAST(NULL AS VARCHAR(27))
FROM SYSCAT.INDEXDEP
WHERE BTYPE = DEPTYPE AND BSCHEMA = SCHEMA
AND BNAME = NAME
UNION ALL
SELECT 'X', IESCHEMA, IENAME,
CAST(NULL AS VARCHAR(128)),
CAST(NULL AS VARCHAR(128)),
CAST(NULL AS VARCHAR(27))
FROM SYSCAT.INDEXEXTENSIONDEP
WHERE BTYPE = DEPTYPE AND BSCHEMA = SCHEMA
AND BNAME = NAME
UNION ALL
SELECT 'P', PKGSCHEMA, PKGNAME, UNIQUE_ID,
CAST(NULL AS VARCHAR(128)),
CAST(NULL AS VARCHAR(27))
FROM SYSCAT.PACKAGEDEP
WHERE BTYPE = DEPTYPE AND BSCHEMA = SCHEMA
AND BNAME = NAME
UNION ALL
SELECT 'B', T1.TRIGSCHEMA, T1.TRIGNAME,
CAST(NULL AS VARCHAR(128)), DEFINER, CREATE_TIME
FROM SYSCAT.TRIGGERS T1, SYSCAT.TRIGDEP T2
WHERE T2.BTYPE = DEPTYPE AND T2.BSCHEMA = SCHEMA
AND T2.BNAME = NAME AND T2.TRIGSCHEMA = T1.TRIGSCHEMA
AND T2.TRIGNAME = T1.TRIGNAME
UNION ALL
SELECT 'C', TABSCHEMA, TABNAME, CONSTNAME, DEFINER,
CREATE_TIME
FROM SYSCAT.REFERENCES
WHERE ((REFTABSCHEMA = SCHEMA AND REFTABNAME = NAME)
OR (TABSCHEMA = SCHEMA AND TABNAME = NAME))
AND DEPTYPE IN ('T', 'N', 'U', 'S')
UNION ALL
SELECT 'I', INDSCHEMA, INDNAME,
CAST(NULL AS VARCHAR(128)),
DEFINER, CREATE_TIME
FROM SYSCAT.INDEXES
WHERE DEPTYPE IN ('T', 'U', 'N', ' S')
AND TABSCHEMA = SCHEMA AND TABNAME = NAME
UNION ALL
SELECT TYPE, TABSCHEMA, TABNAME,
CAST(NULL AS VARCHAR(128)), DEFINER, CREATE_TIME
FROM SYSCAT.TABLES
WHERE DEPTYPE IN ('T', 'U', 'N', 'S')
AND BASE_TABSCHEMA = SCHEMA AND BASE_TABNAME = NAME
UNION ALL
SELECT 'C', TABSCHEMA, TABNAME, CONSTNAME,
DEFINER, CREATE_TIME
FROM SYSCAT.CHECKS
WHERE DEPTYPE IN ('T', 'U', 'N', 'S')
AND TABSCHEMA = SCHEMA AND TABNAME = NAME
;

CREATE FUNCTION SYSPROC.DB2LK_DEP_OF
( DEPTYPE VARCHAR(1),
SCHEMA VARCHAR(128),
NAME VARCHAR(128))
RETURNS TABLE(OBJECTTYPE VARCHAR(1),
SCHEMANAME VARCHAR(128),
OBJECTNAME VARCHAR(128),
THIRDNAME VARCHAR(128),
DEFINER VARCHAR(128),
CTIME VARCHAR(27))
SPECIFIC DB2LK_DEP_OF
READS SQL DATA NO EXTERNAL ACTION DETERMINISTIC
RETURN WITH REC(LEVEL, DTYPE, DSCHEMA, DNAME, DTHIRD, DEFINER, CTIME)
AS ( SELECT 1, U.*
FROM TABLE (SYSPROC.DB2LK_DEP_FIRST(DEPTYPE, SCHEMA, NAME))
AS U
UNION ALL
SELECT LEVEL + 1, U.*
FROM REC,
TABLE(SYSPROC.DB2LK_DEP_FIRST(REC.DTYPE,
REC.DSCHEMA, REC.DNAME))
AS U
WHERE LEVEL < 1000000)
SELECT DISTINCT DTYPE, DSCHEMA, DNAME, DTHIRD,
DEFINER, CTIME
FROM REC
;

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Serge Rielau]

Serge Rielau wrote:

aj wrote:

I am perfectly aware of SYSPROC.ALTOBJ(), but as I indicated in my
original message, I am on LUW 8.1 Fixpak 5. Unless I'm mistaken,
there is no SYSPROC.ALTOBJ() or SYSPROC.DB2LK_DEP_OF() -- hence my
request. I thought you might have something in your bag of gamma
tricks like that DROPSCHEMA procedure.

Thanks anyways

aj

Serge Rielau wrote:

aj wrote:

Serge:

Thats a very cool procedure.

You wouldn't happen to have one that would show me all dependent
objects when I am about to drop a certain table, would you?

My reason:
I'm on LUW 8.1 FP5, so I can't drop columns yet. I want to drop
and recreate tables so that I can drop columns in them.

I don't mind so much recreating all the dependent objects by hand,
its just that *identifying* them is a pain..
Anyone remember this alien in teh Mickey Mouse books who lived of
mothballs? His name (in the German version) was Gamma and he pulled
amazing thing out of his shorts.... anyway
Have a look at the SYSPROC.DB2LK_DEP_OF() table function.
It's used for the SYSPROC.ALTOBJ() procedure which may be just what
you really need.

Well, they are SQL Functions. No requirement for a special build to port
them back (you'll likely need to use a different schemaname of course:

CREATE FUNCTION SYSPROC.DB2LK_DEP_FIRST
( DEPTYPE VARCHAR(1),SCHEMA VARCHAR(128),
NAME VARCHAR(128))
RETURNS TABLE(OBJECTTYPE VARCHAR(1), SCHEMA VARCHAR(128),
NAME VARCHAR(128), THIRDNAME VARCHAR(128),
DEFINER VARCHAR(128), CTIME VARCHAR(27))
SPECIFIC DB2LK_DEP_FIRST READS SQL DATA
NO EXTERNAL ACTION DETERMINISTIC
RETURN SELECT 'F', T1.ROUTINESCHEMA, T1.ROUTINENAME,
T2.SPECIFICNAME, T2.DEFINER, T2.CREATE_TIME
FROM SYSCAT.ROUTINEDEP T1, SYSCAT.ROUTINES T2
WHERE T1.BTYPE = DEPTYPE AND T1.BSCHEMA = SCHEMA
AND T1.BNAME = NAME AND T1.ROUTINESCHEMA = T2.ROUTINESCHEMA
AND T1.ROUTINENAME = T2.ROUTINENAME
UNION ALL
SELECT T1.D TYPE, T1.TABSCHEMA, T1.TABNAME,
CAST(NULL AS VARCHAR(128)), T1.DEFINER,
T2.CREATE_TIME
FROM SYSCAT.TABDEP T1, SYSCAT.TABLES T2
WHERE T1.BTYPE = DEPTYPE AND T1.BSCHEMA = SCHEMA
AND T1.BNAME = NAME AND T1.TABSCHEMA = T2.TABSCHEMA
AND T1.TABNAME = T2.TABNAME
UNION ALL
SELECT 'C', T1.TABSCHEMA, T1.TABNAME,
T1.CONSTNAME, T2.DEFINER, T3.CREATE_TIME
FROM SYSCAT.CONSTDEP T1, SYSCAT.TABCONST T2,
SYSCAT.TABLES T3
WHERE T1.BTYPE = DEPTYPE AND T1.BSCHEMA = SCHEMA
AND T1.BNAME = NAME AND T1.CONSTNAME = T2.CONSTNAME
AND T1.TABSCHEMA = T2.TABSCHEMA
AND T1.TABNAME = T2.TABNAME
AND T1.TABSCHEMA = T3.TABSCHEMA
AND T1.TABNAME = T3.TABNAME
UNION ALL
SELECT 'I', INDSCHEMA, INDNAME,
CAST(NULL AS VARCHAR(128)),
CAST(NULL AS VARCHAR(128)),
CAST(NULL AS VARCHAR(27))
FROM SYSCAT.INDEXDEP
WHERE BTYPE = DEPTYPE AND BSCHEMA = SCHEMA
AND BNAME = NAME
UNION ALL
SELECT 'X', IESCHEMA, IENAME,
CAST(NULL AS VARCHAR(128)),
CAST(NULL AS VARCHAR(128)),
CAST(NULL AS VARCHAR(27))
FROM SYSCAT.INDEXEXTENSIONDEP
WHERE BTYPE = DEPTYPE AND BSCHEMA = SCHEMA
AND BNAME = NAME
UNION ALL
SELECT 'P', PKGSCHEMA, PKGNAME, UNIQUE_ID,
CAST(NULL AS VARCHAR(128)),
CAST(NULL AS VARCHAR(27))
FROM SYSCAT.PACKAGEDEP
WHERE BTYPE = DEPTYPE AND BSCHEMA = SCHEMA
AND BNAME = NAME
UNION ALL
SELECT 'B', T1.TRIGSCHEMA, T1.TRIGNAME,
CAST(NULL AS VARCHAR(128)), DEFINER, CREATE_TIME
FROM SYSCAT.TRIGGERS T1, SYSCAT.TRIGDEP T2
WHERE T2.BTYPE = DEPTYPE AND T2.BSCHEMA = SCHEMA
AND T2.BNAME = NAME AND T2.TRIGSCHEMA = T1.TRIGSCHEMA
AND T2.TRIGNAME = T1.TRIGNAME
UNION ALL
SELECT 'C', TABSCHEMA, TABNAME, CONSTNAME, DEFINER,
CREATE_TIME
FROM SYSCAT.REFERENCES
WHERE ((REFTABSCHEMA = SCHEMA AND REFTABNAME = NAME)
OR (TABSCHEMA = SCHEMA AND TABNAME = NAME))
AND DEPTYPE IN ('T', 'N', 'U', 'S')
UNION ALL
SELECT 'I', INDSCHEMA, INDNAME,
CAST(NULL AS VARCHAR(128)),
DEFINER, CREATE_TIME
FROM SYSCAT.INDEXES
WHERE DEPTYPE IN ('T', 'U', 'N', ' S')
AND TABSCHEMA = SCHEMA AND TABNAME = NAME
UNION ALL
SELECT TYPE, TABSCHEMA, TABNAME,
CAST(NULL AS VARCHAR(128)), DEFINER, CREATE_TIME
FROM SYSCAT.TABLES
WHERE DEPTYPE IN ('T', 'U', 'N', 'S')
AND BASE_TABSCHEMA = SCHEMA AND BASE_TABNAME = NAME
UNION ALL
SELECT 'C', TABSCHEMA, TABNAME, CONSTNAME,
DEFINER, CREATE_TIME
FROM SYSCAT.CHECKS
WHERE DEPTYPE IN ('T', 'U', 'N', 'S')
AND TABSCHEMA = SCHEMA AND TABNAME = NAME
;

CREATE FUNCTION SYSPROC.DB2LK_DEP_OF
( DEPTYPE VARCHAR(1),
SCHEMA VARCHAR(128),
NAME VARCHAR(128))
RETURNS TABLE(OBJECTTYPE VARCHAR(1),
SCHEMANAME VARCHAR(128),
OBJECTNAME VARCHAR(128),
THIRDNAME VARCHAR(128),
DEFINER VARCHAR(128),
CTIME VARCHAR(27))
SPECIFIC DB2LK_DEP_OF
READS SQL DATA NO EXTERNAL ACTION DETERMINISTIC
RETURN WITH REC(LEVEL, DTYPE, DSCHEMA, DNAME, DTHIRD, DEFINER, CTIME)
AS ( SELECT 1, U.*
FROM TABLE (SYSPROC.DB2LK_DEP_FIRST(DEPTYPE, SCHEMA, NAME))
AS U
UNION ALL
SELECT LEVEL + 1, U.*
FROM REC,
TABLE(SYSPROC.DB2LK_DEP_FIRST(REC.DTYPE,
REC.DSCHEMA, REC.DNAME))
AS U
WHERE LEVEL < 1000000)
SELECT DISTINCT DTYPE, DSCHEMA, DNAME, DTHIRD,
DEFINER, CTIME
FROM REC
;

PS: befroe anyone gets the worng impression the defintion of SQL
Function is in syscat.routines.text. I'm not passing on anything anyone
in the group with FP7 installed couldn't do ;-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Bob Stearns]

Serge Rielau wrote:

Bob Stearns wrote:

The syntax diagram of DROP SCHEMA requires RESTRICT. Is there an easy
way to drop an old, unnecessary, but populated schema?

Of course :-)

DROP PROCEDURE DROPSCHEMA
@
CREATE PROCEDURE DROPSCHEMA(IN pschemaname VARCHAR(128))
BEGIN
DECLARE txt VARCHAR(1000);
DECLARE progress INTEGER;
DECLARE success SMALLINT;

REPEAT
SET progress = 0;
FOR thisObject
AS SELECT type, name FROM
(SELECT type, tabname FROM SYSCAT.TABLES WHERE tabschema =
pschemaname
UNION ALL
SELECT 's', seqname FROM SYSCAT.SEQUENCES WHERE seqschema =
pschemaname
AND seqtype = 'S'
UNION ALL
SELECT routinetype, specificname FROM SYSCAT.ROUTINES
WHERE routineschema = pschemaname
AND routinetype IN ('F', 'P')
AND origin NOT IN ('S', 'T')
UNION ALL
SELECT 't', typename FROM SYSCAT.DATATYPES WHERE typeschema =
pschemaname
UNION ALL
SELECT 'g', trigname FROM SYSCAT.TRIGGERS WHERE trigschema =
pschemaname)
AS T(type, name)
ORDER BY name
DO
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '42893', SQLSTATE '42704'
BEGIN
SET success = 0;
END;
SET success = 1;
SET txt = 'DROP '
|| CASE type
WHEN 'T' THEN 'TABLE '
WHEN 'S' THEN 'TABLE '
WHEN 'U' THEN 'TABLE '
WHEN 'N' THEN 'NICKNAME '
WHEN 'A' THEN 'ALIAS '
WHEN 's' THEN 'SEQUENCE '
WHEN 'V' THEN 'VIEW '
WHEN 'W' THEN 'VIEW '
WHEN 'F' THEN 'SPECIFIC FUNCTION '
WHEN 'P' THEN 'SPECIFIC PROCEDURE '
WHEN 't' THEN 'TYPE '
WHEN 'g' THEN 'TRIGGER'
END
|| '"' || pschemaname || '"."' || name || '"';
EXECUTE IMMEDIATE txt;
IF success = 1 THEN
SET progress = 1;
END IF;
END;
END FOR;
UNTIL progress = 0 END REPEAT;
SET txt = 'DROP SCHEMA "' || pschemaname || '" RESTRICT';
EXECUTE IMMEDIATE txt;
END
@

Talking of which... I'm looking of "beta-testers" for my backup/restore
schema developer works article-to-be. Any takers?

Cheers
Serge

I finally got around to using and studying the dropschema procedure and
had some student type (i. e. ignoramus) questions:

1) Would the WHEN logic on type work just as well in the SELECT
statements, eliminating the need for the pseudo types 's', 't', 'g', etc.?

2) Apparently the language has no reserved words and uses a context
dependent grammar? Is that true of SQL in general, especially of the
standard(s)? SELECT from from from where where where order order by by
is a correct statement:
from is a column name
from is an alias
from is from
where is a table name
where is a nickname
where is where
order is a column name
order is order
by is by
by is a column name

3) Dependency among the various objects is handled by a combination of
the REPEAT statement and the CONTINUE HANDLER, right?

4) Does the CONTINUE HANDLER have to be redeclared each time the FOR
statement is executed? Or can it be declared once outside the loop
especially since those sqlstates should only arise from statements
within the loop?

[Serge Rielau]

Bob Stearns wrote:

Serge Rielau wrote:

Bob Stearns wrote:

The syntax diagram of DROP SCHEMA requires RESTRICT. Is there an easy
way to drop an old, unnecessary, but populated schema?

Of course :-)

DROP PROCEDURE DROPSCHEMA
@
CREATE PROCEDURE DROPSCHEMA(IN pschemaname VARCHAR(128))
BEGIN
DECLARE txt VARCHAR(1000);
DECLARE progress INTEGER;
DECLARE success SMALLINT;

REPEAT
SET progress = 0;
FOR thisObject
AS SELECT type, name FROM
(SELECT type, tabname FROM SYSCAT.TABLES WHERE tabschema =
pschemaname
UNION ALL
SELECT 's', seqname FROM SYSCAT.SEQUENCES WHERE seqschema =
pschemaname
AND seqtype = 'S'
UNION ALL
SELECT routinetype, specificname FROM SYSCAT.ROUTINES
WHERE routineschema = pschemaname
AND routinetype IN ('F', 'P')
AND origin NOT IN ('S', 'T')
UNION ALL
SELECT 't', typename FROM SYSCAT.DATATYPES WHERE typeschema
= pschemaname
UNION ALL
SELECT 'g', trigname FROM SYSCAT.TRIGGERS WHERE trigschema =
pschemaname)
AS T(type, name)
ORDER BY name
DO
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '42893', SQLSTATE '42704'
BEGIN
SET success = 0;
END;
SET success = 1;
SET txt = 'DROP '
|| CASE type
WHEN 'T' THEN 'TABLE '
WHEN 'S' THEN 'TABLE '
WHEN 'U' THEN 'TABLE '
WHEN 'N' THEN 'NICKNAME '
WHEN 'A' THEN 'ALIAS '
WHEN 's' THEN 'SEQUENCE '
WHEN 'V' THEN 'VIEW '
WHEN 'W' THEN 'VIEW '
WHEN 'F' THEN 'SPECIFIC FUNCTION '
WHEN 'P' THEN 'SPECIFIC PROCEDURE '
WHEN 't' THEN 'TYPE '
WHEN 'g' THEN 'TRIGGER'
END
|| '"' || pschemaname || '"."' || name || '"';
EXECUTE IMMEDIATE txt;
IF success = 1 THEN
SET progress = 1;
END IF;
END;
END FOR;
UNTIL progress = 0 END REPEAT;
SET txt = 'DROP SCHEMA "' || pschemaname || '" RESTRICT';
EXECUTE IMMEDIATE txt;
END
@

Talking of which... I'm looking of "beta-testers" for my
backup/restore schema developer works article-to-be. Any takers?

Cheers
Serge

I finally got around to using and studying the dropschema procedure and
had some student type (i. e. ignoramus) questions:

For a moment there I thought you had a student type the questions.
Sometimes I miss capitalized nouns ;-)
1) Would the WHEN logic on type work just as well in the SELECT
statements, eliminating the need for the pseudo types 's', 't', 'g', etc.? Just as fine.. Just need then two CASE expressions. One in routines, one
in tables. Same thing.
2) Apparently the language has no reserved words and uses a context
dependent grammar? Is that true of SQL in general, especially of the
standard(s)? SELECT from from from where where where order order by by
is a correct statement:
from is a column name
from is an alias
from is from
where is a table name
where is a nickname
where is where
order is a column name
order is order
by is by
by is a column name DB2 for LUW is fairly liberal with that respect. It has aocncept called
"soft keywords". When the grammar runs into trouble because the lexer
has declared "WHERE" a keyword rather than an objectname, then it can
"re-brand". This works with a LALR(2) lookahead. So it works most times,
but not always.
Theer was a recent theread in c.d.informix where we sparred the DB2 for
LUW parser against the IDS parser. Both digested some funny statements.
The SQL standard has many more keywords than DB2 for LUW.
3) Dependency among the various objects is handled by a combination of
the REPEAT statement and the CONTINUE HANDLER, right? Correct. One of those "doh!" revelations.
The dependency graph in general is shallow (deepest probably for views
and SQL functions), but make that perhaps 5 levels at most in the 99%
case. 2 in teh 80% case.
The parser will figure out a missing object quite quickly, so it's way
safer to appraoch the problem brute force. In most cases the create
timestamp is a good telltale.
4) Does the CONTINUE HANDLER have to be redeclared each time the FOR
statement is executed? Or can it be declared once outside the loop
especially since those sqlstates should only arise from statements
within the loop?

Good question. The CONTINUE HANDLER is a static object. It's scope
dicates it's position. There is no runtime overhead.
A cleanly written SQL/PSM logic have the handle always close to
statement which it is meant to handle.
Stricly speaking the same should (?) be done with variables and cursors.
But, in contrast to handlers, neither of which can cause outright harm
when grouped "globally" at the beginning a the proecedure.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[aj]

Wow. I'm impressed that the views are backwardly compatible,
and that this works..

Thankyou very much, Serge. :)

aj

Serge Rielau wrote:

aj wrote:

I am perfectly aware of SYSPROC.ALTOBJ(), but as I indicated in my
original message, I am on LUW 8.1 Fixpak 5. Unless I'm mistaken,
there is no SYSPROC.ALTOBJ() or SYSPROC.DB2LK_DEP_OF() -- hence my
request. I thought you might have something in your bag of gamma
tricks like that DROPSCHEMA procedure.

Thanks anyways

aj

Serge Rielau wrote:

aj wrote:

Serge:

Thats a very cool procedure.

You wouldn't happen to have one that would show me all dependent
objects when I am about to drop a certain table, would you?

My reason:
I'm on LUW 8.1 FP5, so I can't drop columns yet. I want to drop
and recreate tables so that I can drop columns in them.

I don't mind so much recreating all the dependent objects by hand,
its just that *identifying* them is a pain..

Anyone remember this alien in teh Mickey Mouse books who lived of
mothballs? His name (in the German version) was Gamma and he pulled
amazing thing out of his shorts.... anyway
Have a look at the SYSPROC.DB2LK_DEP_OF() table function.
It's used for the SYSPROC.ALTOBJ() procedure which may be just what
you really need.

Well, they are SQL Functions. No requirement for a special build to port
them back (you'll likely need to use a different schemaname of course:

CREATE FUNCTION SYSPROC.DB2LK_DEP_FIRST
( DEPTYPE VARCHAR(1),SCHEMA VARCHAR(128),
NAME VARCHAR(128))
RETURNS TABLE(OBJECTTYPE VARCHAR(1), SCHEMA VARCHAR(128),
NAME VARCHAR(128), THIRDNAME VARCHAR(128),
DEFINER VARCHAR(128), CTIME VARCHAR(27))
SPECIFIC DB2LK_DEP_FIRST READS SQL DATA
NO EXTERNAL ACTION DETERMINISTIC
RETURN SELECT 'F', T1.ROUTINESCHEMA, T1.ROUTINENAME,
T2.SPECIFICNAME, T2.DEFINER, T2.CREATE_TIME
FROM SYSCAT.ROUTINEDEP T1, SYSCAT.ROUTINES T2
WHERE T1.BTYPE = DEPTYPE AND T1.BSCHEMA = SCHEMA
AND T1.BNAME = NAME AND T1.ROUTINESCHEMA = T2.ROUTINESCHEMA
AND T1.ROUTINENAME = T2.ROUTINENAME
UNION ALL
SELECT T1.D TYPE, T1.TABSCHEMA, T1.TABNAME,
CAST(NULL AS VARCHAR(128)), T1.DEFINER,
T2.CREATE_TIME
FROM SYSCAT.TABDEP T1, SYSCAT.TABLES T2
WHERE T1.BTYPE = DEPTYPE AND T1.BSCHEMA = SCHEMA
AND T1.BNAME = NAME AND T1.TABSCHEMA = T2.TABSCHEMA
AND T1.TABNAME = T2.TABNAME
UNION ALL
SELECT 'C', T1.TABSCHEMA, T1.TABNAME,
T1.CONSTNAME, T2.DEFINER, T3.CREATE_TIME
FROM SYSCAT.CONSTDEP T1, SYSCAT.TABCONST T2,
SYSCAT.TABLES T3
WHERE T1.BTYPE = DEPTYPE AND T1.BSCHEMA = SCHEMA
AND T1.BNAME = NAME AND T1.CONSTNAME = T2.CONSTNAME
AND T1.TABSCHEMA = T2.TABSCHEMA
AND T1.TABNAME = T2.TABNAME
AND T1.TABSCHEMA = T3.TABSCHEMA
AND T1.TABNAME = T3.TABNAME
UNION ALL
SELECT 'I', INDSCHEMA, INDNAME,
CAST(NULL AS VARCHAR(128)),
CAST(NULL AS VARCHAR(128)),
CAST(NULL AS VARCHAR(27))
FROM SYSCAT.INDEXDEP
WHERE BTYPE = DEPTYPE AND BSCHEMA = SCHEMA
AND BNAME = NAME
UNION ALL
SELECT 'X', IESCHEMA, IENAME,
CAST(NULL AS VARCHAR(128)),
CAST(NULL AS VARCHAR(128)),
CAST(NULL AS VARCHAR(27))
FROM SYSCAT.INDEXEXTENSIONDEP
WHERE BTYPE = DEPTYPE AND BSCHEMA = SCHEMA
AND BNAME = NAME
UNION ALL
SELECT 'P', PKGSCHEMA, PKGNAME, UNIQUE_ID,
CAST(NULL AS VARCHAR(128)),
CAST(NULL AS VARCHAR(27))
FROM SYSCAT.PACKAGEDEP
WHERE BTYPE = DEPTYPE AND BSCHEMA = SCHEMA
AND BNAME = NAME
UNION ALL
SELECT 'B', T1.TRIGSCHEMA, T1.TRIGNAME,
CAST(NULL AS VARCHAR(128)), DEFINER, CREATE_TIME
FROM SYSCAT.TRIGGERS T1, SYSCAT.TRIGDEP T2
WHERE T2.BTYPE = DEPTYPE AND T2.BSCHEMA = SCHEMA
AND T2.BNAME = NAME AND T2.TRIGSCHEMA = T1.TRIGSCHEMA
AND T2.TRIGNAME = T1.TRIGNAME
UNION ALL
SELECT 'C', TABSCHEMA, TABNAME, CONSTNAME, DEFINER,
CREATE_TIME
FROM SYSCAT.REFERENCES
WHERE ((REFTABSCHEMA = SCHEMA AND REFTABNAME = NAME)
OR (TABSCHEMA = SCHEMA AND TABNAME = NAME))
AND DEPTYPE IN ('T', 'N', 'U', 'S')
UNION ALL
SELECT 'I', INDSCHEMA, INDNAME,
CAST(NULL AS VARCHAR(128)),
DEFINER, CREATE_TIME
FROM SYSCAT.INDEXES
WHERE DEPTYPE IN ('T', 'U', 'N', ' S')
AND TABSCHEMA = SCHEMA AND TABNAME = NAME
UNION ALL
SELECT TYPE, TABSCHEMA, TABNAME,
CAST(NULL AS VARCHAR(128)), DEFINER, CREATE_TIME
FROM SYSCAT.TABLES
WHERE DEPTYPE IN ('T', 'U', 'N', 'S')
AND BASE_TABSCHEMA = SCHEMA AND BASE_TABNAME = NAME
UNION ALL
SELECT 'C', TABSCHEMA, TABNAME, CONSTNAME,
DEFINER, CREATE_TIME
FROM SYSCAT.CHECKS
WHERE DEPTYPE IN ('T', 'U', 'N', 'S')
AND TABSCHEMA = SCHEMA AND TABNAME = NAME
;

CREATE FUNCTION SYSPROC.DB2LK_DEP_OF
( DEPTYPE VARCHAR(1),
SCHEMA VARCHAR(128),
NAME VARCHAR(128))
RETURNS TABLE(OBJECTTYPE VARCHAR(1),
SCHEMANAME VARCHAR(128),
OBJECTNAME VARCHAR(128),
THIRDNAME VARCHAR(128),
DEFINER VARCHAR(128),
CTIME VARCHAR(27))
SPECIFIC DB2LK_DEP_OF
READS SQL DATA NO EXTERNAL ACTION DETERMINISTIC
RETURN WITH REC(LEVEL, DTYPE, DSCHEMA, DNAME, DTHIRD, DEFINER, CTIME)
AS ( SELECT 1, U.*
FROM TABLE (SYSPROC.DB2LK_DEP_FIRST(DEPTYPE, SCHEMA, NAME))
AS U
UNION ALL
SELECT LEVEL + 1, U.*
FROM REC,
TABLE(SYSPROC.DB2LK_DEP_FIRST(REC.DTYPE,
REC.DSCHEMA, REC.DNAME))
AS U
WHERE LEVEL < 1000000)
SELECT DISTINCT DTYPE, DSCHEMA, DNAME, DTHIRD,
DEFINER, CTIME
FROM REC
;

[Brian Tkatch]

(SET SCHEMA SESSION)

db2 => DECLARE GLOBAL TEMPORARY TABLE WHERE (FROM INT, ORDER INT, BY
INT)
DB20000I The SQL command completed successfully.
db2 => SELECT from from from where where where order order by by
SQL0104N An unexpected token "from from where" was found following
"SELECT
from ". Expected tokens may include: "<space>". SQLSTATE=42601

It fails because the statement mentions a COULMN name in the WHERE
clause, the COLUMN "By", but doesn't do anything with it.

Instead try:

DECLARE GLOBAL TEMPORARY TABLE From (SELECT INT) WITH REPLACE
SELECT SELECT SELECT FROM FROM FROM

Or try

DECLARE GLOBAL TEMPORARY TABLE From (From INT) WITH REPLACE
SELECT FROM FROM FROM FROM

Technically, five FROMs should be usuable, column, column-alias,
keyword, tablename, tablename-alias. But it fails, not know why.

db2 => DECLARE GLOBAL TEMPORARY TABLE From (From INT) WITH REPLACE
DB20000I The SQL command completed successfully.
db2 => SELECT FROM FROM FROM FROM FROM
SQL0104N An unexpected token "FROM" was found following "FROM FROM
FROM
FROM". Expected tokens may include: "END-OF-STATEMENT".
SQLSTATE=42601

How many keywords can you put in a row with no punctuation? :)

B.

[Sreeni Paidi]

Thanks for the procedure Serge!
I will be glad to be a beta-tester for the backup schema / restore
schema procedure.
My email is pa**********@gmail.com

Regards,
Sreeni Paidi
IBM Software Group
IBM Burlingame Lab

[Serge Rielau]

Sreeni Paidi wrote:

Thanks for the procedure Serge!
I will be glad to be a beta-tester for the backup schema / restore
schema procedure.

Incoming. I added COPYSCHEMA() as well now.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab