× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.