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. 

J&#248;rn Thyssen

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

Hi Andy,

I discussed your problem with Bart Steegmans from IBM, and while there is a undocumented serviceability IFCID 276 that contains transition variables it contains the memory address of the transition variables, not the actual values :(

 

   LOCATION: RS01IDS2                         OMEGAMON XE FOR DB2 PERFORMANCE EXPERT (V5R4M0)             PAGE: 1-1                 
GROUP: IDS2 RECORD TRACE - SHORT REQUESTED FROM: NOT SPECIFIED
MEMBER: I9A2 TO: NOT SPECIFIED
SUBSYSTEM: I9A2 ACTUAL FROM: 01/15/18 13:54:27.20
DB2 VERSION: V11 PAGE DATE: 01/15/18
PRIMAUTH CONNECT INSTANCE END_USER WS_NAME TRANSACT
ORIGAUTH CORRNAME CONNTYPE RECORD TIME DESTNO ACE IFC DESCRIPTION DATA
PLANNAME CORRNMBR TCB CPU TIME ID
-------- -------- ----------- ----------------- ------ --- --- -------------- ------------------------------------------------------
TS5941 TSO D3BE125BFB4B TS5941 TSO TS5941
TS5941 TS5941 TSO 13:54:27.20597159 2359 1 276 UNKNOWN RECORD NETWORKID: ROCKNET1 LUNAME: I9A2DB2 LUWSEQ: 24
ADB 'BLANK' N/P
|-------------------------------------------------------------------------------------------------------------------------
|0000 0152E2D8 D3C4C14E 40400000 00000000 01500000 00000000 00020000 00000000 | ..SQLDA+ .......&..............
|0020 00020000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 | ................................
|0040 00000000 00000000 00000000 00000000 00000000 01F20000 01F00000 00000000 | .....................2...0......
|0060 00000000 00000000 00040000 00000000 00000000 03248174 CF300000 00000000 | ......................a.........
|0080 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 | ................................
|00A0 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 | ................................
|00C0 00000000 00000000 00000000 00000000 00000000 01C70000 01C40000 00250000 | .....................G...D......
|00E0 00000000 00000000 000B0000 00000000 00000000 03248174 CF340000 00000000 | ......................a.........
|0100 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 | ................................
|0120 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 | ................................
|0140 00000000 00000000 00000000 00000000 0000 | ..................
|.........................................................................................................................
|0000 015601A5 C9D5E3E5 00000000 00000000 00000000 00000000 00000000 00000000 | ...vINTV........................
|0020 00000002 00080000 00000000 00000000 00000000 00000000 00000000 00000000 | ................................
|0040 00000000 00000000 00000000 00000000 00000000 0000C9D5 E3F20000 00000000 | ......................INT2......
|0060 00000000 00040000 00000000 00000000 00000000 00000000 03247F22 2AB00000 | ..........................".....
|0080 00000000 000001F2 00000000 00000000 00000000 00000000 00000000 00000000 | .......2........................
|00A0 00000000 00000003 00000000 00000000 00000000 00000000 00000000 00000000 | ................................
|00C0 00000000 00000000 00000000 00000000 00000000 0000C3C8 C1F30000 00000000 | ......................CHA3......
|00E0 00000000 000A0000 00000000 00000000 03247F22 2AB40000 03247F22 2AB50000 | ..................".......".....
|0100 00000000 000001C7 00250040 00000000 00000000 00000000 00000000 00000000 | .......G... ....................
|0120 00000000 00000001 00000000 00000000 00000000 00000000 00000000 00000000 | ................................
|0140 00000000 00000000 00000000 00000000 00000000 0000 | ......................
|-------------------------------------------------------------------------------------------------------------------------

This means you would have to open a RFE against the Db2 engine to have it include the actual values in IFCID 276 instead of a memory address and maybe a RFE against your SMF formatting tool to properly format the record.

Obviously this will not help you short term.

As mentioned earlier the best solution is probably to reproduce the issue in a development environment, remove the primary key / unique index, and then examine the rows inserted to find out what is going on.

 

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)

Thanks for the confirmation Jørn

I'll consider the RFE route.

Meanwhile, our application team are working on a solution with their ETL product of choice.  If this is unsuccessful, we can then explore the DDL options suggested by you and Phil.

Cheers

Andy

Bruce Williamson

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

FYI Join the RFE Community (Requests For Enhancement), while you're at it why not join the "All DB2 for z/OS" group?

Cheers
Bruce