Db2 11 z/OS - capture transition variable values

Andy Smith

Db2 11 z/OS - capture transition variable values

We have an ETL process failing with -803 on a duplicate insert from a trigger.
Our Application Performance product of choice can capture Host Variables values used for 'normal' INSERTs, but in this case they are Transition Variables for the triggered INSERT, and the product does not capture these.
The vendor has told us that could be a Request for Enhancement, but is unlikley to be delivered in the time that we need it (we need to get this data processed asap).
The application team are looking at what they can do from their side, but can anybody suggest any ideas about how we (Db2) might be able to help them capture the values being used for the transition variables in the triggered INSERT causing the -803?
I've read about IFCID 247 but again this appears to be for Host Variables, not Transition Variables.
Thanks
Andy

Paul Ogborne

Db2 11 z/OS - capture transition variable values
(in response to Andy Smith)
Andy,


Do you have any flavour of Log Analyser. Finding and interpreting a rolled back UR without would be time consuming.


Another method I might try is Omegamon for DB2 or DB2PE. It is also likely to be in SMF somewhere too ....


Others may hopefully have other ideas too.


Good luck!


Regards,
Paul Ogborne



-----Original Message-----
From: Andy Smith <[login to unmask email]>
To: DB2-L <[login to unmask email]>
Sent: Fri, 12 Jan 2018 15:07
Subject: [DB2-L] - Db2 11 z/OS - capture transition variable values



We have an ETL process failing with -803 on a duplicate insert from a trigger.
Our Application Performance product of choice can capture Host Variables values used for 'normal' INSERTs, but in this case they are Transition Variables for the triggered INSERT, and the product does not capture these.
The vendor has told us that could be a Request for Enhancement, but is unlikley to be delivered in the time that we need it (we need to get this data processed asap).
The application team are looking at what they can do from their side, but can anybody suggest any ideas about how we (Db2) might be able to help them capture the values being used for the transition variables in the triggered INSERT causing the -803?
I've read about IFCID 247 but again this appears to be for Host Variables, not Transition Variables.
Thanks
Andy



Site Links: < /span> View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription

This email has been sent to: [login to unmask email]
Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
http://www.ESAIGroup.com/idug


Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2



David Baldon

Db2 11 z/OS - capture transition variable values
(in response to Paul Ogborne)
If you have BMC tools, Log Master could produce SQL from the log records produced by the insert which would show you the values.

...David

From: Paul Ogborne [mailto:[login to unmask email]
Sent: Friday, January 12, 2018 11:28 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Db2 11 z/OS - capture transition variable values

Andy,

Do you have any flavour of Log Analyser. Finding and interpreting a rolled back UR without would be time consuming.

Another method I might try is Omegamon for DB2 or DB2PE. It is also likely to be in SMF somewhere too ....

Others may hopefully have other ideas too.

Good luck!

Regards,
Paul Ogborne

-----Original Message-----
From: Andy Smith <[login to unmask email]<mailto:[login to unmask email]>>
To: DB2-L <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Fri, 12 Jan 2018 15:07
Subject: [DB2-L] - Db2 11 z/OS - capture transition variable values
We have an ETL process failing with -803 on a duplicate insert from a trigger.
Our Application Performance product of choice can capture Host Variables values used for 'normal' INSERTs, but in this case they are Transition Variables for the triggered INSERT, and the product does not capture these.
The vendor has told us that could be a Request for Enhancement, but is unlikley to be delivered in the time that we need it (we need to get this data processed asap).
The application team are looking at what they can do from their side, but can anybody suggest any ideas about how we (Db2) might be able to help them capture the values being used for the transition variables in the triggered INSERT causing the -803?
I've read about IFCID 247 but again this appears to be for Host Variables, not Transition Variables.
Thanks
Andy

-----End Original Message-----

Philip Sevetson

Db2 11 z/OS - capture transition variable values
(in response to Andy Smith)
Andy,

Well… you could be a real barbarian, and

1) Add LAST_UPD_TIMESTAMP TIMESTAMP NOT NULL WITH DEFAULT to the table

2) Drop the unique index

3) Rerun the work and look at the resulting duplicates

The problem with this approach is the amount of cleanup you might need to do afterwards, but it’ll give you the data you’re looking for. (Your vendor might wind up a touch upset, too.)

--Phil

From: Andy Smith [mailto:[login to unmask email]
Sent: Friday, January 12, 2018 10:08 AM
To: [login to unmask email]
Subject: [DB2-L] - Db2 11 z/OS - capture transition variable values


We have an ETL process failing with -803 on a duplicate insert from a trigger.
Our Application Performance product of choice can capture Host Variables values used for 'normal' INSERTs, but in this case they are Transition Variables for the triggered INSERT, and the product does not capture these.
The vendor has told us that could be a Request for Enhancement, but is unlikley to be delivered in the time that we need it (we need to get this data processed asap).
The application team are looking at what they can do from their side, but can anybody suggest any ideas about how we (Db2) might be able to help them capture the values being used for the transition variables in the triggered INSERT causing the -803?
I've read about IFCID 247 but again this appears to be for Host Variables, not Transition Variables.
Thanks
Andy

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

J&#248;rn Thyssen

RE: Db2 11 z/OS - capture transition variable values
(in response to Andy Smith)

Hi Andy,

I tried to run a Db2 trace for IFCID(0247) which is host variables & SQLDA, but I don't get any values collected.

I will contact Db2 development which trace is needed to collect trigger transition variables.

 

Otherwise my suggestion is similar to Philip's:

If you can reproduce the problem in your development or test environment you can modify the trigger and/or table for debugging purposes.

 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal. 

J&#248;rn Thyssen

RE: Db2 11 z/OS - capture transition variable values
(in response to David Baldon)

Excellent idea!

Db2 writtes a log record for the INSERT on the main table, and then a compensation record once the trigger fails.

Here's what I see with IBM Db2 Log Analysis Tool in a small example:

_______________________________________________________________________________   
RECORD IDENTIFIER: 2

ACTION DATE TIME TABLE OWNER TABLE NAME
------ ---------- -------- ------------ -------------------------
INSERT 2018-01-12 14.32.08 TS5941 TEST6

URID(R) DATABASE TABLESPACE DBID PSID OBID AUTHID
-------------------- -------- ---------- ----- ----- ----- --------
0000000000099C9BC000 DSN00300 TEST6 00610 00002 00003 TS5941

PLAN CONNTYPE LRSN MEMID CORRID CONNID
-------- -------- -------------------- ----- ------------ --------
ADB BATCH 00D3BA99AB2C6B8F2A00 00001 TS5941 TSO

LUW=NETID/LUNAME/UNIQUE/COMMIT PAGE/RID PARTITION
----------------------------------- ----------- ---------
ROCKNET1/I9A2DB2 /D3BA368DFD01/003C 00000002/02

ROW STATUS KEY
----------- ---
POST-CHANGE +1
PRE-CHANGE -

_______________________________________________________________________________
RECORD IDENTIFIER: 3

ACTION DATE TIME TABLE OWNER TABLE NAME
------ ---------- -------- ------------ -------------------------
DEL-CR 2018-01-12 14.32.08 TS5941 TEST6

URID(R) DATABASE TABLESPACE DBID PSID OBID AUTHID
-------------------- -------- ---------- ----- ----- ----- --------
0000000000099C9BC000 DSN00300 TEST6 00610 00002 00003 TS5941

PLAN CONNTYPE LRSN MEMID CORRID CONNID
-------- -------- -------------------- ----- ------------ --------
ADB BATCH 00D3BA99AB2D9F574A00 00001 TS5941 TSO

LUW=NETID/LUNAME/UNIQUE/COMMIT PAGE/RID PARTITION
----------------------------------- ----------- ---------
ROCKNET1/I9A2DB2 /D3BA368DFD01/003C 00000002/02

ROW STATUS KEY
----------- ---
POST-CHANGE -
PRE-CHANGE +1

 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal. 

Andy Smith

RE: Db2 11 z/OS - capture transition variable values
(in response to Jørn Thyssen)

We do have a log tool so I could explore that, but unfortunately this is an INSTEAD OF trigger, so I do not believe that the original insert actually fires, so there'll be nothing on the log.  I suspect that what I really need is the IFCID record holding the transition variable values in use at the time of the -803 duplicate insert.....

J&#248;rn Thyssen

RE: Db2 11 z/OS - capture transition variable values
(in response to Andy Smith)

Hi Andy,

Yes, I think you're right. I did a quick test with an INSTEAD OF trigger and I see nothing on the log when my application receives sqlcode -723 (as a result of an sqlcode -803 inside the trigger).

 

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal.