DROP TABLE, VIEW, ... only IF EXISTS

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gregor Kovač - 06 Jul 2006 14:49 GMT

Hi!

Is it possible do to something like this:
DROP TABLE MY_TABLE ONLY IF EXISTS
and the same for views, indexes, procedures, .... ?

Best regards,
Kovi

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Reply to this Message

Konstantin Andreev - 06 Jul 2006 15:13 GMT

"Gregor Kovac" <gregor.kovac@mikropis.si> wrote: news:6y8rg.4173$oj5.1414452@news.siol.net...

> Is it possible do to something like this: DROP TABLE MY_TABLE ONLY IF EXISTS
> and the same for views, indexes, procedures, .... ?

Long and cumbersome way. Just drop and ignore errors. But, if you insist...
For table:
--------------
Begin atomic
if( exists(
select 1 from syscat.tables where tabschema = 'MYSCHEMA' and tabname = 'MYTABLE'
)) then
drop table MYSCHEMA.MYTABLE;
end if;
End

--------------
For index: syscat.table -> syscat.indexes + 2 extra conditions: indschema & indname.
... and so on...

Cheers,
--
Konstantin Andreev.

Reply to this Message

Gregor Kovač - 06 Jul 2006 15:19 GMT

> Begin atomic
> if( exists(
[quoted text clipped - 3 lines]
> end if;
> End

Hmm.. but it does not work for me.
I've saved this into a file TEST.SQL:
Begin atomic
if (exists(select 1 from syscat.tables where tabschema = 'MYSCHEMA' and
tabname = 'MYTABLE')) then
drop table MYSCHEMA.MYTABLE;
end if;
End
#

ran it like:
db2 -td# -f TEST.SQL

and got:
B21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0104N An unexpected token " " was found following "Begin atomic ".
Expected tokens may include: "<space>". LINE NUMBER=2. SQLSTATE=42601

I've tried this approach, but to no avail.

Best regards,
Kovi

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Reply to this Message

Paul Peters - 06 Jul 2006 15:18 GMT

> Hi!
>
[quoted text clipped - 9 lines]
> | Experience Linux. |
> -~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Use the db2perf_quiet_drop procedure. The source code is available on

ftp://ftp.software.ibm.com/ps/products/db2/tools/db2perf-1.0.1.zip

In the file db2perf_utils.db2 there is the procedure db2perf_quiet_drop
which suppresses the 'not found' message.

The call :

CALL db2perf_quiet_drop( 'procedure db2perf_crmsg' )@

drops the procedure db2perf_crmsg if this procedure exists.

Kind regards,
Paul

Reply to this Message

Gregor Kovač - 06 Jul 2006 15:24 GMT

> Use the db2perf_quiet_drop procedure. The source code is available on
>
[quoted text clipped - 11 lines]
> Kind regards,
> Paul

Cool, I like it :)) thanks.

Best regards,
Kovi

Signature

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

| Gregor Kovac | Gregor.Kovac@mikropis.si |

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| In A World Without Fences Who Needs Gates? |
| Experience Linux. |

-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

Reply to this Message