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

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

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

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

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

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

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

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

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 really did mess up last night.

just finished defining a trigger of this structure on LUW/v8. pretty
as
a peach. oh well.

robert