Hi Cole,
I have something very similar except mine is a trigger which monitors one
file, and uses information from this to write to two other files. Code is
attached below, hopefully it will be of some use to you, although you may
need to create a stored procedure instead of dynamic SQL within a program.
I have included the table definition (PTPCHANGES), which has an identity
field as its primary key. I have also included the trigger code. I got
the code from one of the IBM manuals, it uses the VALUES line to retrieve
the value of the newly created PTPCHANGES table record.
Hope this helps.
JP
Bank of Ireland - Group IT
Please log all calls with the helpdesk first, phone: 01 638 6316.
t:+353 (0)1 616 7909
f:+353 (0)1 856 0315
Bank of Ireland incorporated in Ireland with Limited Liability.
Registered Office - Head Office, Lower Baggot Street, Dublin 2. Registered
Number - C-1
Bank of Ireland is regulated by the Financial Regulator in Ireland and
authorised by the Financial Services Authority in the UK.
Security Classification - Amber Confidential to Bank of Ireland Group Staff
and authorised third parties ; Not for further distribution.
Layout of table PTPCHANGES:
=====================================================
Create Table Fgb2Bf0Pd0/PtpChanges(
CmpCde Int Not Null,
PrtNum Int Not Null,
ChgUser VarChar(10),
SeqNumber Int NOT NULL GENERATED ALWAYS As Identity
(Start With 1, Increment By 1),
ChgDate TimeStamp Default Current_TimeStamp,
ChgFile Char(2) Not Null,
Reported Char(1) Default 'N',
Operation Char(1) Not Null,
Constraint PtpChanges_PK Primary Key (SeqNumber),
Constraint PtpChanges_CHECK1 Check
(ChgFile In ('MA', 'AD', 'CO', 'CU')),
Constraint PtpChanges_CHECK2 Check
(Reported In ('Y', 'N')),
Constraint PtpChanges_CHECK3 Check
(Operation In ('A', 'C', 'D'))
);
Label On Table Fgb2Bf0Pd0/PtpChanges Is
'Record Third Party Changes';
Comment On Table Fgb2Bf0Pd0/PtpChanges Is
'Third Party Changes';
COMMENT ON COLUMN FGB2BF0PD0/PTPCHANGES (CHGUSER IS
'Who Made The Change', SEQNUMBER IS 'Sequence Number', CHGDATE IS
'Date Changed', CHGFILE IS 'File Being Changed');
Trigger code:
================================================
Create Trigger Fgb2Bf0Pd0/PtpCustTrI
After Insert
On Fgb2bf0Pd0/PtpCust
Referencing New Row as NR
For Each Row Mode DB2Row
SET OPTION DBGVIEW =*STMT
Begin
Declare Ident Integer;
Insert Into Fgb2Bf0Pd0/PtpChanges(
CmpCde, PrtNum, ChgUser, ChgFile, Operation
)
Values(NR.Company_Code, NR.Customer_Number,
User, 'CU', 'A');
Values Identity_Val_Local() Into Ident;
Insert Into Fgb2Bf0Pd0/PtpCustChg(
SeqNumber, Image, AdrNum, SxTyp, Occup, EmpNm, EmpA1,
EmpA2
)
Values(Ident, 'A', NR.Address_Number,
NR.Male_Female_Indicator, NR.Occupation,
NR.Employer_Name, NR.Employer_Address_Line_1,
NR.Employer_Address_Line_2);
End;
|---------+------------------------------->
| | Cole Gantz |
| | <colegantz@xxxxxxxxx|
| | > |
| | Sent by: |
| | cobol400-l-bounces@m|
| | idrange.com |
| | |
| | |
| | 17/02/2010 21:13 |
| | Please respond to |
| | COBOL Programming on|
| | the iSeries/AS400 |
| | |
|---------+------------------------------->
>------------------------------------------------------------------------------------------------------------------------------|
| |
| To: COBOL400-L@xxxxxxxxxxxx |
| cc: |
| Subject: [COBOL400-L] Embeded SQL retrieving a key from an insert |
>------------------------------------------------------------------------------------------------------------------------------|
Howdy All,
I've got a table that was created via an SQL statement that looks like
this:
create
(hdrheadkey integer not null
generated always as identity
(start with 1
,increment by 1
,no maxvalue
,no cycle
,order)
,hdrdate numeric(8,0) not null default 0
,hdrtime numeric(8,0) not null default 0
,hdruser char(10) ccsid 37 not null default ''
,hdrcaption char(4) ccsid 37 not null default ''
,hdrdesc char(40) ccsid 37 not null default ''
,hdrinsname char(60) ccsid 37 not null default ''
,hdrsubject char(60) ccsid 37 not null default ''
,primary key (hdrheadkey));
I need to get the key that is created when an insert is done and this is
what I've pulled from a sample from a DB2 Cookbook but I'm getting an error
in WDSc when trying to enter it: Exec SQL Select HDRHEADKEY InTo
:HeaderKeySQL0199 Keyword TABLE not expected. Valid tokens: FOR WITH FETCH
ORDER UNION EXCEPT OPTIMIZE ValuesEnd-Exec
Am I trying to do something that can't be done? Is there another way
to get the newly created key from an insert to be used in another table?
Cole
I love deadlines. I like the whooshing sound they make as they fly by.
Douglass Adams (:NoteDate, :NoteTime, :NoteUser, ' ',
:InsuredName, ' ')) (HdrDate, HdrTime, HdrUser, HdrCaption,
HdrDesc, HdrInsName, HdrSubject) (Insert InTo NotePadHd From
Final Table table notepad/notepdhd
--
This is the COBOL Programming on the iSeries/AS400 (COBOL400-L) mailing
list
To post a message email: COBOL400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/cobol400-l
or email: COBOL400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/cobol400-l.
<PRE>
**************************
</PRE>
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify us immediately at MailMonitor@xxxxxxxxxxx and delete this E-mail from your system. Thank you. It is possible for data transmitted by email to be deliberately or accidentally corrupted or intercepted. For this reason, where the communication is by email, the Bank of Ireland Group does not accept any responsibility for any breach of confidence which may arise through the use of this medium. This footnote also confirms that this email message has been swept for the presence of known computer viruses.
<PRE>
Bank of Ireland incorporated in Ireland with Limited Liability.
Registered Office - Head Office, Lower Baggot Street, Dublin 2.
Registered Number - C-1.
</PRE>
Bank of Ireland is regulated by the Financial Regulator in Ireland and authorised by the Financial Services Authority in the UK.
<PRE>
**************************
</PRE>
midrange.com
