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

Google farted, so i'll try again, and briefer.

no v8 any time soon. just got v7.

yes, syssequences, but no documented CREATE SEQUENCE, only identity,
and
they're not resetable.
how *do* they stand it? the C**lers, that is??? :-))

robert