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 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
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 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
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
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
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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...
| |