Serge Rielau wrote:
> gnuoytr@rcn.com wrote:
> > Serge Rielau wrote:
> >
> >>gnuoytr@rcn.com wrote:
> >>
> >>>rather than deal with some COBOL code, which i wouldn't write,
> >>>i thought it would be common practice to use a trigger to
> >>>update a sequence column with a trigger. i'm testing on
> >>>XDB (Mainframe Express, said to be identical to V7/390).
> >>>the sequence_no should increment as shown. but the code
> >>>errors out. none of the example triggers i can find do
> >>>this kind of fullselect, but don't say you can't either.
> >>>
> >>>is this permitted?
> >>>
> >>>Create Trigger sch.tab1_BRI
> >>>No Cascade Before Insert on sch.tab1
> >>>Referencing old as O new as N
> >>>for each Row mode db2sql
> >>>begin atomic
> >>>set n.sequence_no =
> >>>(select coalesce(max(sequence_no ), 0 ) + 1 from sch.tab1
> >>>where sch.tab1.company_code = n.company_code and
> >>>sch.tab1.record_type = n.record_type and
> >>>sch.tab1.person_id = n.person_id );
> >>>end
> >>>
> >>>thanks,
> >>>robert
> >>>
> >>
> >>What error are you getting? Just staring at it the trigger looks
> >
> > fine.
> >
> >>It would work on DB2 for LUW.
> >>
> >>Cheers
> >>Serge
> >>
> >>--
> >>Serge Rielau
> >>DB2 SQL Compiler Development
> >>IBM Toronto Lab
> >
> >
> >
> > thx.
> >
> > yeah, i couldn't see that it was wrong, either. on the other hand,
i
> > did some further digging and came up with a note that fullselect
from
> > the trigger table wasn't allowed, in the XDB notes that a colleague
> > has.
> >
> > well, just a generic XDB message that there is a syntax error. i
still
> > haven't got trigger authority on the 390 box, and we are mandated
to
> > use MFE for development. i'll go through the exercise with a LUW
db
> > when i get back to the office. may have to chalk it up as a
platform
> > difference. may have to find a COBOL coder and pucker up..........
> > robert
> >
> Does V7 support sequences? I know I'm pushing it by asking a zOS
> customer to move to V8.. but hey I do it anyway. A lot of Db2 zOS SQL

> limitations have been removed in V8. makes x-platform so much easier
:-)
>
> --
> Serge Rielau
> DB2 SQL Compiler Development
> IBM Toronto Lab

we *just* (a month or two) got one instance to 7. no s**t. the more
i see of it, the more i see why the C**Lers treat it like just a pile
of VSAM files: is there much difference :-)) not likely to see 8
in my lifetime.

and no, i don't see sequences; although the catalog table syssequences
is there; for identity columns i guess. but you can't reset them, and
this requirement can't be global to the table..... and so forth.
<sigh>
robert