Re: Drop Trigger IF EXISTS trigger_name

Hi Rob,

Your suggestion makes sense but I'm still wondering how others get around
the issue.

For another example, say I create a table as a work file in qtemp. When the
job runs in batch in runs fine. But if I want to debug the program
interactively and I run it a second time (without first dropping the table)
it will fail when trying to create the table which already exists.

I'm aware there are many ways around this, I'm really just wondering how
others get around the situation where an IF EXISTS would fit perfectly. In
CL we could CrtPf or CrtDupObj with a MonMsg after to trap the situation
where the file already exists.

Thanks,

Robert Rogerson

On Wed, Mar 9, 2011 at 10:18 AM, <rob@xxxxxxxxx> wrote:

Why do you DROP TABLE instead of ALTER TABLE to add a column?


Rob Berendt
--
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Robert Rogerson <rogersonra@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 03/09/2011 10:05 AM
Subject: Re: Drop Trigger IF EXISTS trigger_name
Sent by: midrange-l-bounces@xxxxxxxxxxxx



Sorry to hijack your thread Dan, but I have a similiar question and I am
interested in how others handle the situation.

When developing I may create a new table and save the SQL script.
At a later point I realize that I need another column. So in order to
rerun
the CREATE TABLE script I have to manually drop the table.
If I had an IF EXISTS I could condition the DROP TABLE statement.

How do others handle this and similiar situations?

Thanks,

Robert Rogerson

On Wed, Mar 9, 2011 at 9:31 AM, Dan <dan27649@xxxxxxxxx> wrote:

Hi Vern,

I considered that as well. Unfortunately, the scripts from multiple
projects are collected and executed by a DBA, and I have no control over
anything outside of my script.

The script is actually used to create a new trigger, and the idea behind
the
"Delete if exists" is, in case this script is run multiple times, for
reasons outside of my control, that it won't bomb if I try to delete a
trigger that doesn't exist. Or bomb when creating a trigger that

already

exists. My request is in the realm of "would be nice if we could". We
will
deal with the problem if it arises. My guess is that they activate
journaling before running the scripts, and can roll back changes if
necessary.

Thanks!
- Dan

On Tue, Mar 8, 2011 at 6:23 PM, Vern Hamberg <vhamberg@xxxxxxxxxxx>

wrote:

Dan

You can set the severity higher on RUNSQLSTM - I assume that is the
command you are using? Other interfaces probably have a similar

setting

somewhere.

Prompt RUNSQLSTM and press F10 or F9 to see more parameters, including
severity.

HTH
Vern

On 3/8/2011 3:11 PM, Dan wrote:

Drop Trigger on a trigger that no longer exists causes an SQL0204

message

with severity 30 (<trigger_name> in *LIBL not found) and causes the

script

to abort.

MySQL allows "IF EXISTS" to be used in a Drop Trigger statement. It

does

not appear that we can use this in DB2. Presuming that to be the

case,

is

there an easy way to do this without resorting to CL (MONMSG) or an

SQL

stored procedure?

TIA,
- Dan

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.