[DB2 V10 z/OS] Trace class/IFCID which captures executing SQL

Philip Sevetson

[DB2 V10 z/OS] Trace class/IFCID which captures executing SQL
z/OS people,

We have a need, for a particular remote requester, to capture the SQL which is participating in deadlocks in our production system. We know the plan (DISTSERV) and the authID, and would like to -START TRACE for that combination.

However, I don't know what _class_ and _IFCID_ (or IFCIDS) to use. Does anyone know the answer to this? I've done a fair bit of web searching and looking through DSNWMSGS without success.

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

Attachments

  • image001.png (3.3k)

Jay Reavill

[DB2 V10 z/OS] Trace class/IFCID which captures executing SQL
(in response to Philip Sevetson)
Looks like Performance trace Class 3 IFCID 250.

Thanks,
Jay

Jay Reavill
Database Administrator
NACard Solutions
* Office: 727.227.2144
* Cell: 727.215.5794
*: [login to unmask email]<mailto:[login to unmask email]>
FIS | Empowering the Financial World [cid:[login to unmask email] <https://www.facebook.com/FIStoday> [cid:[login to unmask email] <https://twitter.com/FISGlobal> [cid:[login to unmask email] <https://www.linkedin.com/company/fis>

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Friday, January 06, 2017 11:46 AM
To: [login to unmask email]
Subject: [DB2-L] - [DB2 V10 z/OS] Trace class/IFCID which captures executing SQL

z/OS people,

We have a need, for a particular remote requester, to capture the SQL which is participating in deadlocks in our production system. We know the plan (DISTSERV) and the authID, and would like to -START TRACE for that combination.

However, I don't know what _class_ and _IFCID_ (or IFCIDS) to use. Does anyone know the answer to this? I've done a fair bit of web searching and looking through DSNWMSGS without success.

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]


-----End Original Message-----
The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
Attachments

  • image001.png (<1k)
  • image002.png (<1k)
  • image003.png (<1k)

Philip Sevetson

[DB2 V10 z/OS] Trace class/IFCID which captures executing SQL
(in response to Jay Reavill)
Thanks, Jay, I'll give that a look.

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Reavill, Jay [mailto:[login to unmask email]
Sent: Friday, January 06, 2017 12:01 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: [DB2 V10 z/OS] Trace class/IFCID which captures executing SQL

Looks like Performance trace Class 3 IFCID 250.

Thanks,
Jay

Jay Reavill
Database Administrator
NACard Solutions
* Office: 727.227.2144
* Cell: 727.215.5794
*: [login to unmask email]<mailto:[login to unmask email]>
FIS | Empowering the Financial World [cid:[login to unmask email] <https://www.facebook.com/FIStoday> [cid:[login to unmask email] <https://twitter.com/FISGlobal> [cid:[login to unmask email] <https://www.linkedin.com/company/fis>

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Friday, January 06, 2017 11:46 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - [DB2 V10 z/OS] Trace class/IFCID which captures executing SQL

z/OS people,

We have a need, for a particular remote requester, to capture the SQL which is participating in deadlocks in our production system. We know the plan (DISTSERV) and the authID, and would like to -START TRACE for that combination.

However, I don't know what _class_ and _IFCID_ (or IFCIDS) to use. Does anyone know the answer to this? I've done a fair bit of web searching and looking through DSNWMSGS without success.

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]


-----End Original Message-----
The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
-----End Original Message-----
Attachments

  • image001.png (3.3k)

Philip Sevetson

[DB2 V10 z/OS] Trace class/IFCID which captures executing SQL
(in response to Philip Sevetson)
Jay,
What I ran was
-STA TRACE(PERFM) DEST(SMF) PLAN(DISTSERV) AUTHID(xxxxxxx) CLASS(3) IFCID(250)
-- which is valid syntax. My next thing is to run some transactions from that user and figure out how to get a look at the SQL field in that record...

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Friday, January 06, 2017 12:04 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [DB2 V10 z/OS] Trace class/IFCID which captures executing SQL

Thanks, Jay, I'll give that a look.

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Reavill, Jay [mailto:[login to unmask email]
Sent: Friday, January 06, 2017 12:01 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: [DB2 V10 z/OS] Trace class/IFCID which captures executing SQL

Looks like Performance trace Class 3 IFCID 250.

Thanks,
Jay

Jay Reavill
Database Administrator
NACard Solutions
* Office: 727.227.2144
* Cell: 727.215.5794
*: [login to unmask email]<mailto:[login to unmask email]>
FIS | Empowering the Financial World [cid:[login to unmask email] <https://www.facebook.com/FIStoday> [cid:[login to unmask email] <https://twitter.com/FISGlobal> [cid:[login to unmask email] <https://www.linkedin.com/company/fis>

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Friday, January 06, 2017 11:46 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - [DB2 V10 z/OS] Trace class/IFCID which captures executing SQL

z/OS people,

We have a need, for a particular remote requester, to capture the SQL which is participating in deadlocks in our production system. We know the plan (DISTSERV) and the authID, and would like to -START TRACE for that combination.

However, I don't know what _class_ and _IFCID_ (or IFCIDS) to use. Does anyone know the answer to this? I've done a fair bit of web searching and looking through DSNWMSGS without success.

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]


-----End Original Message-----
The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
-----End Original Message-----

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

  • image001.png (3.3k)

Venkat Srinivasan

RE: [DB2 V10 z/OS] Trace class/IFCID which captures executing SQL
(in response to Philip Sevetson)

What?....

You shouldn't trace 250. IFICD 250 traces connect and disconnect from gb pool ?...... He probably meant 350 which is the complete SQL statement which also has the stmt id.

Most online monitors that have the ability to display deadlock / timeout victims should show the statemt id as part of the information and once statement id is known they do optionally hyperlink that to access statement cache itself assuming statement is still cached there.

If your monitor does not provide that, ificd 172 is the deadlock trace. That will have statement id. Explain stmt cache and look for stmt id matching the info in ifcid 172. Far easier but assumes stmt will be cached until you get the chance to look.

Trace ificid 172 and 350, reproduce the failure and match the stmt id on both records assuming you have a reporting tool.

If workload is static the plan package and statement no are all externalized in 172.

To include timeouts you will need 196.

Venkat

 

 


 
In Reply to Philip Sevetson:

Jay,
What I ran was
-STA TRACE(PERFM) DEST(SMF) PLAN(DISTSERV) AUTHID(xxxxxxx) CLASS(3) IFCID(250)
-- which is valid syntax. My next thing is to run some transactions from that user and figure out how to get a look at the SQL field in that record...

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Friday, January 06, 2017 12:04 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [DB2 V10 z/OS] Trace class/IFCID which captures executing SQL

Thanks, Jay, I'll give that a look.

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Reavill, Jay [mailto:[login to unmask email]
Sent: Friday, January 06, 2017 12:01 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: [DB2 V10 z/OS] Trace class/IFCID which captures executing SQL

Looks like Performance trace Class 3 IFCID 250.

Thanks,
Jay

Jay Reavill
Database Administrator
NACard Solutions
* Office: 727.227.2144
* Cell: 727.215.5794
*: [login to unmask email]<mailto:[login to unmask email]>
FIS | Empowering the Financial World [cid:[login to unmask email] <https://www.facebook.com/FIStoday> [cid:[login to unmask email] <https://twitter.com/FISGlobal> [cid:[login to unmask email] <https://www.linkedin.com/company/fis>

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Friday, January 06, 2017 11:46 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - [DB2 V10 z/OS] Trace class/IFCID which captures executing SQL

z/OS people,

We have a need, for a particular remote requester, to capture the SQL which is participating in deadlocks in our production system. We know the plan (DISTSERV) and the authID, and would like to -START TRACE for that combination.

However, I don't know what _class_ and _IFCID_ (or IFCIDS) to use. Does anyone know the answer to this? I've done a fair bit of web searching and looking through DSNWMSGS without success.

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]


-----End Original Message-----
The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
-----End Original Message-----

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

Philip Sevetson

[DB2 V10 z/OS] Trace class/IFCID which captures executing SQL
(in response to Philip Sevetson)
...and, I did some more research, and now color me puzzled.

First, I didn't find anything anywhere which captured the text for every execution of a SQL statement. Thinking about it, that makes sense, because the SQL isn't actually referenced when a statement is executed; the last time we see the source text moving through the DB2 address spaces is during a bind.

Jay, the DSNWMSGS section on 0250 doesn't appear to be a general SQL statement register. Short version is that it is associated with connect/disconnect from a coupling facility.

0250 QW0250 ________________IFCID 0250______________________________ 204010

0250 QW0250 (RMID 10) 204020

0250 QW0250 204030

0250 QW0250 IFCID 0250 IS WRITTEN WHEN DB2 TRIES TO CONNECT TO OR 204040

0250 QW0250 DISCONNECT FROM A COUPLING FACILITY CACHE STRUCTURE 204050

0250 QW0250 (DB2 GROUP BUFFER POOL). DB2 USES A GROUP BUFFER POOL 204060

0250 QW0250 TO CACHE DATA THAT IS OF INTEREST TO MORE THAN ONE DB2 204070

0250 QW0250 IN THE DB2 DATA SHARING GROUP. 204080

Second, I finally did find content in DSNWMSGS which talked about the actual text of a SQL statement.

IFCID 0350 does capture a SQL statement, of indefinite length, at bind time (for either dynamic or static statements):

0350 QW0350 ________________IFCID 0350______________________________ 234720
0350 QW0350 (RMID 22) 234730
0350 QW0350 IFCID 0350 RECORDS THE COMPLETE TEXT OF A PARSED 234740
0350 QW0350 SQL STATEMENT. THESE RECORDS ARE WRITTEN WHEN A 234750
0350 QW0350 STATIC OR DYNAMIC SQL STATEMENT IS BOUND. 234760
0350 QW0350 THE MAXIMUM LENGTH OF ANY TRACE FIELD IS 5000 BYTES. 234770
0350 QW0350 EACH TRACE RECORD CONSISTS OF ZERO OR MORE REPEATING 234780
0350 QW0350 GROUPS. THE LENGTH OF EACH GROUP IS 5000 BYTES. IF 234790
0350 QW0350 QW0350FL IS ON, THE FIXED-LENGTH FIELDS ARE FOLLOWED BY 234800
0350 QW0350 A VARIABLE-LENGTH FIELD THAT CONTAINS THE END OF THE 234810
0350 QW0350 SQL STATEMENT. IF THE SQL STATEMENT DOES NOT FIT IN A 234820
0350 QW0350 SINGLE TRACE RECORD, A SERIES OF IFCID 0350 RECORDS 234830
0350 QW0350 ARE WRITTEN. QW0350FF AND QW0350FL IDENTIFY THE FIRST 234840
0350 QW0350 AND LAST RECORDS. IF BOTH BITS ARE ON, A SINGLE RECORD 234850
0350 QW0350 CONTAINS THE ENTIRE SQL STATEMENT. 234860
0350 QW0350 YOU CAN ACTIVATE THIS TRACE BY STARTING PERFORMANCE 234870
0350 QW0350 TRACE CLASS 32 FOR IFCID 0350. 234880
0350 QW0350 --------------------------------------------------------- 234890
0350 QW0350OT PARSER OPTIONS AND HOST LANGUAGE: 234900
0350 QW0350OQ X'80': 0=APOST 1=QUOTE 234910
0350 QW0350OD X'40': 0=PERIOD 1=COMMA 234920
0350 QW0350OR X'20': 0=APOSTSQL 1=QUOTESQL 234930
0350 QW0350OM X'10': 0=MIXED=NO 1=MIXED=YES 234940
0350 QW0350OH X'07': HOST LANGUAGE BIT MASK: 234950
0350 QW0350OA 1=ASSEMBLER 234960
0350 QW0350OC 2=COBOL 234970
0350 QW0350OP 3=PL/I 234980
0350 QW0350OS 4=DYNAMIC SQL 234990
0350 QW0350OF 5=FORTRAN 235000
0350 QW0350O2 6=COB2 235010
0350 QW035007 7=LOOK AT QW0350HL TO DETERMINE THE HOST LANGUAGE 235020
0350 QW0350HL HOST LANGUAGE SECOND FIELD. THIS FIELD MUST BE USED TO 235030
0350 QW0350HL DETERMINE THE HOST LANGUAGE IF THE HOST LANGUAGE BIT MASK 235040
0350 QW0350HL IS 7, AND CAN OPTIONALLY BE USED IF THE HOST LANGUAGE 235050
0350 QW0350HL BIT MASK IS NOT 7. POSSIBLE VALUES ARE: 235060
0350 QW0350HB 'B'=ASSEMBLER 235070
0350 QW0350HC 'C'=COBOL 235080
0350 QW0350HD 'D'=C 235090
0350 QW0350HF 'F'=FORTRAN 235100
0350 QW0350HP 'P'=PL/I 235110
0350 QW0350H2 '2'=COBOL II 235120
0350 QW0350H3 '3'=IBM COBOL 235130
0350 QW0350H4 '4'=C++ 235140
0350 QW0350FG FLAG BYTE. IF EITHER OF THE FOLLOWING TWO BITS IS OFF, 235150
0350 QW0350FG THIS RECORD CONTAINS ONLY A PORTION OF THE SQL STATEMENT: 235160
0350 QW0350FF X'80': THIS IS THE FIRST SQL STATEMENT SEGMENT. 235170
0350 QW0350FL X'40': THIS IS THE LAST SQL STATEMENT SEGMENT. 235180
0350 QW0350TL TOTAL LENGTH OF THE SQL STATEMENT. 235190
0350 QW0350TY TYPE OF SQL STATEMENT. POSSIBLE VALUES ARE: 235200
0350 QW0350DY X'8000': STATEMENT IS DYNAMIC. 235210
0350 QW0350SC X'4000': STATEMENT IS STATIC. 235220
0350 QW0350SI STATEMENT IDENTIFIER. 235230
0350 QW0350CC ORIGINAL SOURCE CCSID. 235240
0350 QW0350 --------------------------------------------------------- 235250
0350 QW0350 -- SQL STATEMENT DATA. THIS SECTION IS LOCATED BY QWT02R2O 235260
0350 QW0350 -- IF QW0350FL IS OFF, OR IF THE NUMBER OF SELF DEFINING 235270
0350 QW0350 -- DATA AREAS, WHICH IS IN FIELD QWHSNSDA, IS 3. THIS 235280
0350 QW0350 -- SECTION CONTAINS QWT02R2N REPEATING GROUPS. 235290
0350 QW0350SPL LENGTH OF THE FOLLOWING FIELD, PLUS 2. 235300
0350 QW0350SP %U ALL OR PART OF THE SQL STATEMENT THAT IS BEING 235310
0350 QW0350SP PARSED. THE TOTAL LENGTH OF THE STATEMENT IS IN QW0350TL. 235320
0350 QW0350SP HOST VARIABLES IN THIS FIELD ARE REPRESENTED BY :H. 235330
0350 QW0350 --------------------------------------------------------- 235340
0350 QW0350 -- SQL STATEMENT DATA. THIS SECTION EXISTS IF QW0350FL IS 235350
0350 QW0350 -- ON. IT IS LOCATED BY QWT02R2O OR QWT02R3O AND IS THE 235360
0350 QW0350 -- END OF THE SQL STATEMENT. 235370
0350 QW0350SEL LENGTH OF THE FOLLOWING FIELD, PLUS 2. 235380
0350 QW0350SE %U ALL OR THE END OF THE SQL STATEMENT THAT IS BEING 235390
0350 QW0350SE PARSED. THE TOTAL LENGTH OF THE STATEMENT IS IN QW0350TL. 235400
0350 QW0350SE HOST VARIABLES IN THIS FIELD ARE REPRESENTED BY :H. 235410

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Friday, January 06, 2017 12:10 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [DB2 V10 z/OS] Trace class/IFCID which captures executing SQL

Jay,
What I ran was
-STA TRACE(PERFM) DEST(SMF) PLAN(DISTSERV) AUTHID(xxxxxxx) CLASS(3) IFCID(250)
-- which is valid syntax. My next thing is to run some transactions from that user and figure out how to get a look at the SQL field in that record...

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Friday, January 06, 2017 12:04 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [DB2 V10 z/OS] Trace class/IFCID which captures executing SQL

Thanks, Jay, I'll give that a look.

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Reavill, Jay [mailto:[login to unmask email]
Sent: Friday, January 06, 2017 12:01 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: [DB2 V10 z/OS] Trace class/IFCID which captures executing SQL

Looks like Performance trace Class 3 IFCID 250.

Thanks,
Jay

Jay Reavill
Database Administrator
NACard Solutions
* Office: 727.227.2144
* Cell: 727.215.5794
*: [login to unmask email]<mailto:[login to unmask email]>
FIS | Empowering the Financial World [cid:[login to unmask email] <https://www.facebook.com/FIStoday> [cid:[login to unmask email] <https://twitter.com/FISGlobal> [cid:[login to unmask email] <https://www.linkedin.com/company/fis>

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Friday, January 06, 2017 11:46 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - [DB2 V10 z/OS] Trace class/IFCID which captures executing SQL

z/OS people,

We have a need, for a particular remote requester, to capture the SQL which is participating in deadlocks in our production system. We know the plan (DISTSERV) and the authID, and would like to -START TRACE for that combination.

However, I don't know what _class_ and _IFCID_ (or IFCIDS) to use. Does anyone know the answer to this? I've done a fair bit of web searching and looking through DSNWMSGS without success.

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]


-----End Original Message-----
The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
-----End Original Message-----

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

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

  • image001.png (3.3k)

Philip Sevetson

[DB2 V10 z/OS] Trace class/IFCID which captures executing SQL
(in response to Venkat Srinivasan)
Captured. Thanks, Venkat. We have BMC MV/DB2, which does hyperlink to the DSC if it’s being tracked.

This has been re-prioritized (as no longer critical) so I’m now trying to get everything written down in case we need it again.

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Venkat Srinivasan [mailto:[login to unmask email]
Sent: Friday, January 06, 2017 2:01 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: [DB2 V10 z/OS] Trace class/IFCID which captures executing SQL


What?....

You shouldn't trace 250. IFICD 250 traces connect and disconnect from gb pool ?...... He probably meant 350 which is the complete SQL statement which also has the stmt id.

Most online monitors that have the ability to display deadlock / timeout victims should show the statemt id as part of the information and once statement id is known they do optionally hyperlink that to access statement cache itself assuming statement is still cached there.

If your monitor does not provide that, ificd 172 is the deadlock trace. That will have statement id. Explain stmt cache and look for stmt id matching the info in ifcid 172. Far easier but assumes stmt will be cached until you get the chance to look.

Trace ificid 172 and 350, reproduce the failure and match the stmt id on both records assuming you have a reporting tool.

If workload is static the plan package and statement no are all externalized in 172.

To include timeouts you will need 196.

Venkat






In Reply to Philip Sevetson:
Jay,
What I ran was
-STA TRACE(PERFM) DEST(SMF) PLAN(DISTSERV) AUTHID(xxxxxxx) CLASS(3) IFCID(250)
-- which is valid syntax. My next thing is to run some transactions from that user and figure out how to get a look at the SQL field in that record...

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Friday, January 06, 2017 12:04 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [DB2 V10 z/OS] Trace class/IFCID which captures executing SQL

Thanks, Jay, I'll give that a look.

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Reavill, Jay [mailto:[login to unmask email]
Sent: Friday, January 06, 2017 12:01 PM
To: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Subject: [DB2-L] - RE: [DB2 V10 z/OS] Trace class/IFCID which captures executing SQL

Looks like Performance trace Class 3 IFCID 250.

Thanks,
Jay

Jay Reavill
Database Administrator
NACard Solutions
* Office: 727.227.2144
* Cell: 727.215.5794
*: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
FIS | Empowering the Financial World [cid:[login to unmask email] <https://www.facebook.com/FIStoday><https://www.facebook.com/FIStoday%3e> [cid:[login to unmask email] <https://twitter.com/FISGlobal><https://twitter.com/FISGlobal%3e> [cid:[login to unmask email] <https://www.linkedin.com/company/fis><https://www.linkedin.com/company/fis%3e>

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Friday, January 06, 2017 11:46 AM
To: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Subject: [DB2-L] - [DB2 V10 z/OS] Trace class/IFCID which captures executing SQL

z/OS people,

We have a need, for a particular remote requester, to capture the SQL which is participating in deadlocks in our production system. We know the plan (DISTSERV) and the authID, and would like to -START TRACE for that combination.

However, I don't know what _class_ and _IFCID_ (or IFCIDS) to use. Does anyone know the answer to this? I've done a fair bit of web searching and looking through DSNWMSGS without success.

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]


-----End Original Message-----
The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
-----End Original Message-----

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

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

  • image001.png (3.3k)

Jay Reavill

[DB2 V10 z/OS] Trace class/IFCID which captures executing SQL
(in response to Philip Sevetson)
Apologies for the mis-information… Got it from the v9 CA Reference Guide I had handy. Doesn’t even show a 350. Good to know…

Thanks,
Jay

Jay Reavill
Database Administrator
NACard Solutions
• Office: 727.227.2144
• Cell: 727.215.5794
•: [login to unmask email]<mailto:[login to unmask email]>
FIS | Empowering the Financial World [cid:[login to unmask email] <https://www.facebook.com/FIStoday> [cid:[login to unmask email] <https://twitter.com/FISGlobal> [cid:[login to unmask email] <https://www.linkedin.com/company/fis>

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Friday, January 06, 2017 2:42 PM
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - RE: [DB2 V10 z/OS] Trace class/IFCID which captures executing SQL

Captured. Thanks, Venkat. We have BMC MV/DB2, which does hyperlink to the DSC if it’s being tracked.

This has been re-prioritized (as no longer critical) so I’m now trying to get everything written down in case we need it again.

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Venkat Srinivasan [mailto:[login to unmask email]
Sent: Friday, January 06, 2017 2:01 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: [DB2 V10 z/OS] Trace class/IFCID which captures executing SQL


What?....

You shouldn't trace 250. IFICD 250 traces connect and disconnect from gb pool ?...... He probably meant 350 which is the complete SQL statement which also has the stmt id.

Most online monitors that have the ability to display deadlock / timeout victims should show the statemt id as part of the information and once statement id is known they do optionally hyperlink that to access statement cache itself assuming statement is still cached there.

If your monitor does not provide that, ificd 172 is the deadlock trace. That will have statement id. Explain stmt cache and look for stmt id matching the info in ifcid 172. Far easier but assumes stmt will be cached until you get the chance to look.

Trace ificid 172 and 350, reproduce the failure and match the stmt id on both records assuming you have a reporting tool.

If workload is static the plan package and statement no are all externalized in 172.

To include timeouts you will need 196.

Venkat






In Reply to Philip Sevetson:
Jay,
What I ran was
-STA TRACE(PERFM) DEST(SMF) PLAN(DISTSERV) AUTHID(xxxxxxx) CLASS(3) IFCID(250)
-- which is valid syntax. My next thing is to run some transactions from that user and figure out how to get a look at the SQL field in that record...

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Friday, January 06, 2017 12:04 PM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: [DB2 V10 z/OS] Trace class/IFCID which captures executing SQL

Thanks, Jay, I'll give that a look.

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Reavill, Jay [mailto:[login to unmask email]
Sent: Friday, January 06, 2017 12:01 PM
To: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Subject: [DB2-L] - RE: [DB2 V10 z/OS] Trace class/IFCID which captures executing SQL

Looks like Performance trace Class 3 IFCID 250.

Thanks,
Jay

Jay Reavill
Database Administrator
NACard Solutions
* Office: 727.227.2144
* Cell: 727.215.5794
*: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
FIS | Empowering the Financial World [cid:[login to unmask email] <https://www.facebook.com/FIStoday><https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.facebook.com%2FFIStoday%253e&data=01%7C01%7Cjay.c.reavill%40fisglobal.com%7C73855a4313cb4000d40208d4366cab16%7Ce3ff91d834c84b15a0b418910a6ac575%7C0&sdata=kXC19seqlSYO8HWz02yrmJ9LM8YEPwgAyGL37qNitvo%3D&reserved=0> [cid:[login to unmask email] <https://twitter.com/FISGlobal><https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftwitter.com%2FFISGlobal%253e&data=01%7C01%7Cjay.c.reavill%40fisglobal.com%7C73855a4313cb4000d40208d4366cab16%7Ce3ff91d834c84b15a0b418910a6ac575%7C0&sdata=tjBhEtsR8P%2FfMciXYac39%2BB5L%2BUL5PRJgMpS7jOibyc%3D&reserved=0> [cid:[login to unmask email] <https://www.linkedin.com/company/fis><https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.linkedin.com%2Fcompany%2Ffis%253e&data=01%7C01%7Cjay.c.reavill%40fisglobal.com%7C73855a4313cb4000d40208d4366cab16%7Ce3ff91d834c84b15a0b418910a6ac575%7C0&sdata=blMsqLUbVWKSgBkKeOP2zXnkRj41bboCAsa%2F0xFUhFo%3D&reserved=0>

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Friday, January 06, 2017 11:46 AM
To: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]%3e>
Subject: [DB2-L] - [DB2 V10 z/OS] Trace class/IFCID which captures executing SQL

z/OS people,

We have a need, for a particular remote requester, to capture the SQL which is participating in deadlocks in our production system. We know the plan (DISTSERV) and the authID, and would like to -START TRACE for that combination.

However, I don't know what _class_ and _IFCID_ (or IFCIDS) to use. Does anyone know the answer to this? I've done a fair bit of web searching and looking through DSNWMSGS without success.

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]


-----End Original Message-----
The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
-----End Original Message-----

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

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

-----End Original Message-----
The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you.
Attachments

  • image001.png (<1k)
  • image002.png (<1k)
  • image003.png (<1k)