473,320 Members | 1,846 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

before trigger on 390/V7

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

Nov 12 '05 #1
8 1559
gn*****@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
Nov 12 '05 #2

Serge Rielau wrote:
gn*****@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

Nov 12 '05 #3
gn*****@rcn.com wrote:
Serge Rielau wrote:
gn*****@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
Nov 12 '05 #4

Serge Rielau wrote:
gn*****@rcn.com wrote:
Serge Rielau wrote:
gn*****@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

Nov 12 '05 #5

Serge Rielau wrote:
gn*****@rcn.com wrote:
Serge Rielau wrote:
gn*****@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

Nov 12 '05 #6

Serge Rielau wrote:
gn*****@rcn.com wrote:
Serge Rielau wrote:
gn*****@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


Nov 12 '05 #7

Serge Rielau wrote:
gn*****@rcn.com wrote:
Serge Rielau wrote:
gn*****@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

Nov 12 '05 #8

Serge Rielau wrote:
gn*****@rcn.com wrote:
Serge Rielau wrote:
gn*****@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

Nov 12 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Jean-Christian Imbeault | last post by:
Following up on the discussion yesterday on how to prevent the generation on log error messages when someone tries to insert a duplicate primary key record and Nigel Andrew's suggestion of using a...
0
by: Marie G. Tuite | last post by:
I have a problem with a before trigger raising a heap_mark4update error. It happens when there are many updates on the same key. OIDs are off presumably? Am running 7.3.2 on RH 7.3. Have looked...
2
by: Jack | last post by:
We are have a question about the no cascade option on before triggers. The description stays that no other triggers will be fired by the changes of a before trigger. One of our developers is...
3
by: jbaker | last post by:
We are looking at the possibility of using a before trigger to provide the value for the partitioning key of a table. It strikes me as something that would not work, but I have been surprised...
2
by: Robert Fitzpatrick | last post by:
Anytime I create an INSERT BEFORE trigger that includes a query on the same table as the trigger is assigned, the insert does not happen without error. I get 'INSERT 0 0'. It is like the query...
2
by: mghale | last post by:
Hello, I have to create a trigger to accomplish the following: Before the insert into table A occurs, the trigger must check to see if the combination of two columns (from the insert...
3
by: Michel Esber | last post by:
Hello Environment: DB2 V8 LUW FP12. I have a function that returns a table. I am trying to use it inside a before trigger: create trigger TRG.T_MACHINE_RTM before insert on...
7
by: Shane | last post by:
I have been instructed to write a trigger that effectively acts as a foreign key. The point (I think) is to get me used to writing triggers that dont use the primary key(s) I have created the...
0
by: David Robertson | last post by:
Hello, I have a request that I thought was straightforward and would be fairly simple. After many hours of research and trying various triggers, procedures and variables, I have not been able to...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.