Triggers, errors, and fixpacks... 
Author Message
 Triggers, errors, and fixpacks...

Hi All

I have a few questions here:

DB2 7.1 UDB FP 2
W2K Pro

1) Does the APAR list for fixpack 2 list all the modifications in the
fixpack?

2) Should my html documentation have been updated when I installed
FP2? How can I tell (cause it doesnt look like anything was changed)?

3) Was there changes to what we can do with triggers?

4) I have about 300 stored procedures all written in SQL. Why does
this:

SELECT PROCNAME,  VALID
FROM SYSCAT.PROCEDURES
WHERE
  PROCSCHEMA = 'MYSCHEMA'
ORDER BY VALID

have all NULLS in the valid column, the docs say thats the column is
null when it is not a SQL procedure?

5) Does compilling Triggers invalidate procedures or vise versa?

6) Can I have multiple WHEN clases in triggers each having a different
conditions, ie:

CREATE TRIGGER LYNX.MYTRIGGER
NO CASCADE BEFORE INSERT ON MYTABLE
REFERENCING NEW AS N
FOR EACH ROW
MODE DB2SQL
WHEN (SOME CONDITION(S))
   DO SOMETHING
WHEN (SOME OTHER CONDITION(S)))
  DO SOMETHING ELSE
ETC...

I have tried withou success to do something like this, nor have I seen
an example. And when I put many triggers on the same event I get "Too
Complex..." errors

7) From a performance perspective is it better to try and keep logic
in procedures instead of triggers?

Thanks
Jeff Bendixsen



Tue, 15 Jul 2003 08:51:06 GMT
 Triggers, errors, and fixpacks...

Quote:

> Hi All

> I have a few questions here:

> DB2 7.1 UDB FP 2
> W2K Pro

> 1) Does the APAR list for fixpack 2 list all the modifications in the
> fixpack?

It lists all the defects that were fixed.  Functional changes are in the
release notes (SQLLIB\release.txt on W2K).

Quote:

> 2) Should my html documentation have been updated when I installed
> FP2? How can I tell (cause it doesnt look like anything was changed)?

No - all documentation changes in FP2 are limited to the release notes and
the fixpak readme.

Quote:

> 3) Was there changes to what we can do with triggers?

No.
Quote:

> 4) I have about 300 stored procedures all written in SQL. Why does
> this:

> SELECT PROCNAME,  VALID
> FROM SYSCAT.PROCEDURES
> WHERE
>   PROCSCHEMA = 'MYSCHEMA'
> ORDER BY VALID

> have all NULLS in the valid column, the docs say thats the column is
> null when it is not a SQL procedure?

> 5) Does compilling Triggers invalidate procedures or vise versa?

> 6) Can I have multiple WHEN clases in triggers each having a different
> conditions, ie:

> CREATE TRIGGER LYNX.MYTRIGGER
> NO CASCADE BEFORE INSERT ON MYTABLE
> REFERENCING NEW AS N
> FOR EACH ROW
> MODE DB2SQL
> WHEN (SOME CONDITION(S))
>    DO SOMETHING
> WHEN (SOME OTHER CONDITION(S)))
>   DO SOMETHING ELSE
> ETC...

> I have tried withou success to do something like this, nor have I seen
> an example. And when I put many triggers on the same event I get "Too
> Complex..." errors

> 7) From a performance perspective is it better to try and keep logic
> in procedures instead of triggers?

> Thanks
> Jeff Bendixsen



Tue, 15 Jul 2003 10:20:12 GMT
 Triggers, errors, and fixpacks...
Hi Jeff,

Many questions... let's see what I can answer...

Quote:

> 1) Does the APAR list for fixpack 2 list all the modifications in the
> fixpack?

AFAIK, the APAR list contains only fixe to customer reported problems.
There are release notes which indicate what feature changes were made in
FP2.

Quote:
> 2) Should my html documentation have been updated when I installed
> FP2? How can I tell (cause it doesnt look like anything was changed)?

The only documentation delivered are the release notes. I don't know
whether these plug into the html docs...

Quote:
> 3) Was there changes to what we can do with triggers?

No, not in this fixpack....

Quote:
> 4) I have about 300 stored procedures all written in SQL. Why does
> this:

> SELECT PROCNAME,  VALID
> FROM SYSCAT.PROCEDURES
> WHERE
>   PROCSCHEMA = 'MYSCHEMA'
> ORDER BY VALID

> have all NULLS in the valid column, the docs say thats the column is
> null when it is not a SQL procedure?

This field is for future use. It has currently no meaning. To find the
information you woudl have to hop to the package tied to the SQL
procedure.

Quote:
> 5) Does compilling Triggers invalidate procedures or vise versa?

Creating a trigger will invalidate all packages which contain affected
update delete or insert operations.
They are automatically been rebound on the next call.
Triggers do not have their own packages. They are integrated into the
package which contains the triggering statement. So rebinding/recompiling
a stored procedure will also recompile the used triggers.

Quote:
> 6) Can I have multiple WHEN clases in triggers each having a different
> conditions, ie:

> CREATE TRIGGER LYNX.MYTRIGGER
> NO CASCADE BEFORE INSERT ON MYTABLE
> REFERENCING NEW AS N
> FOR EACH ROW
> MODE DB2SQL
> WHEN (SOME CONDITION(S))
>    DO SOMETHING
> WHEN (SOME OTHER CONDITION(S)))
>   DO SOMETHING ELSE
> ETC...

> I have tried withou success to do something like this, nor have I seen
> an example. And when I put many triggers on the same event I get "Too
> Complex..." errors

Have you increased the statement heap? You should be able to chain "many"
of these before triggers, since
they cannot be recursive...
IF THEN ELSE logic is currently only possible through chaining of
triggers.

Quote:
> 7) From a performance perspective is it better to try and keep logic
> in procedures instead of triggers?

That depends. Most of the performance degradation you see is due to
increased compile time.
My thumbrule would be: If it's dynamic SQL and a short running statement,
the logic is better handled in the procedure. If the statement runs long,
paying the compile price of the trigger is likely to pay of.

Here are some things that don't go together well:
- Single row inserts (short query)
- With constants (can't exploit cache, unless through CLI)
- Unnecessary procedural logic in the trigger which can be written in SQL

An example of performance poison is this in CLP:
CREATE TABLE T(c1 int, c2 int, c3 int)%
CREATE TRIGGER trg1 NO CASCADE BEFORE INSERT ON T
REFERENCING NEW AS new FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
 SET new.c1 = CASE WHEN new.c1 IS NULL THEN 0 ELSE new.c1 END;
 SET new.c2 = CASE WHEN new.c2 IS NULL THEN 0 ELSE new.c2 END;
 SET new.c3 = CASE WHEN new.c3 IS NULL THEN 0 ELSE new.c3 END;
END%

INSERT INTO T VALUES (1, 2, 3)%
INSERT INTO T VALUES (2, 3, 4)%
INSERT INTO T VALUES (3, 4, 5)%
INSERT INTO T VALUES (4, 5, 6)%

Here is how this beast gets optimized (aside from using WITH DEFAULT 0 of
course ;-)

CREATE TRIGGER trg1 NO CASCADE BEFORE INSERT ON T
REFERENCING NEW AS new FOR EACH ROW MDOE DB2SQL
BEGIN ATOMIC
 SET new.c1 = COALESCE(new.c1, 0),
     new.c2 = COALESCE(new.c2, 0),
     new.c3 = COALESCE(new.c3, 0);
END%

INSERT INTO T VALUES (1, 2, 3),
                     (2, 3, 4),
                     (3, 4, 5),
                     (4, 5, 6)%

or
prepare once: INSERT INTO T VALUES (?,?,?)
And then execute it n times

Other general performnce tips for SQL procdures include:
Don't create, use and drop persistent tables within a stored procedure.
It's like pulling the carpet under your feet (i.e. a package invalidating
itself).
If you have to use global temporary tables. That only pulls the carpet
under the statements that use the temp.
Best don't drop these either.

Hope some of my answers were helpful.

Cheers
Serge

--
Visit the DB2 UDB and DB2 Connect Online Support site at:
http://www.ibm.com/software/data/db2/udb/winos2unix/support



Tue, 15 Jul 2003 10:36:37 GMT
 Triggers, errors, and fixpacks...
Hi Jeff,

Quote:

> 2) Should my html documentation have been updated when I installed
> FP2? How can I tell (cause it doesnt look like anything was changed)?

I don't think it does, but release.txt contains a heap of corrections /
additions that apply to the doco and presumably will be incorporated in
the next major release

Quote:
> 6) Can I have multiple WHEN clases in triggers each having a different
> conditions, ie:
> [snip]
> I have tried withou success to do something like this, nor have I seen
> an example. And when I put many triggers on the same event I get "Too
> Complex..." errors

It is possible, but limited by the tuning of your database.  The problem
with going complex in triggers is that the entire trigger effectively
becomes part of the action that triggers it.  You might be performing a
simple insert or update, but AFAIK any triggered actions will be lumped in
(entirely) with your statement for optimisation and execution.  This means
that some limits on complexity of operation apply actually to the sum of
your command plus any related triggers.

Quote:
> 7) From a performance perspective is it better to try and keep logic
> in procedures instead of triggers?

Depends on complexity and nature of logic.  Some logic works well
implemented as constraints.  Triggers are good for automatically chaining
a small action onto other actions.  I guess the reason stored procedures
are call stored procedures, is that they are, well, stored procedures...

Hope that helps.
--greg



Tue, 15 Jul 2003 10:58:36 GMT
 Triggers, errors, and fixpacks...
Thanks everyone for the replies, you guys are great :)

Jeff Bendixsen



Wed, 16 Jul 2003 00:18:21 GMT
 Triggers, errors, and fixpacks...


Quote:
>Hi Jeff,

>Many questions... let's see what I can answer...

Thanks for the reply Serge, I save them up so I can bombard you guys
(and gals) all at once. At least they are relativly easy question
though :)


Wed, 16 Jul 2003 00:29:18 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Running FixPack caused error

2. Errors: 7.1 FixPack 2 under WinNT

3. Help: Invalid Trigger Error on Valid trigger

4. Paradox in Win/OS2 fixpack 26

5. problem with importing blobs in DB2 7.2 fixpack 7

6. which fixpack is which?

7. Better(quicker) procedure for applying Fixpacks/maintenance software...

8. splash screen messed up etc with Fixpacks

9. UDB/DB2 Fixpack 7 / JDBC : executeBatch problem

10. Latest Fixpack for Linux

11. DB2 v7.1 Fixpack 7 experiences?

12. TSM Backups failing after Fixpack 5


 
Powered by phpBB® Forum Software