obtaining SQLCA area

william giannelli

obtaining SQLCA area

I am trying to debug a 00E70082, TYPE OF RESOURCE 3002 (maximum SPs). For this can I obtain the SQLCA area from a trace? I developer says in order to provide the SQLCA requires a production code change.

thanks

Bill

Jørn Thyssen

RE: obtaining SQLCA area
(in response to william giannelli)

Yes, SQLCA is part of IFCID 58 (Db2 performance trace class(3)):

 

   LOCATION: RS01IDS2                         OMEGAMON XE FOR DB2 PERFORMANCE EXPERT (V5R4M0)             PAGE: 1-53                 
GROUP: IDS2 RECORD TRACE - LONG REQUESTED FROM: NOT SPECIFIED
MEMBER: I9A2 TO: NOT SPECIFIED
SUBSYSTEM: I9A2 ACTUAL FROM: 10/02/19 00:23:05.23
DB2 VERSION: V11 PAGE DATE: 10/02/19
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 D6CF2C1FDE09 TS5941 TSO TS5941
TS5941 TS5941 TSO 00:23:15.98056923 197 2 58 END SQL <-- NETWORKID: ROCKNET1 LUNAME: I9A2DB2 LUWSEQ: 3
ADB 'BLANK' 0.00922626
|-------------------------------------------------------------------------------------------------------------------------
|LOCATION NAME : RS01IDS2
|PKG COLLECTION ID : ADBL
|PROGRAM NAME : ADBMAIN
|CONSISTENCY TOKEN : X'1A25E75A06C456BC' SECTION NUMBER : N/A
|STATEMENT NUMBER : 8515 QUERY COMMAND ID : X'0000000000000000' QUERY INSTANCE ID: X'0000000000000000'
|SQL REQUEST TYPE : 129 EXPANSION REASON : NO EXPANSION
| SQLCODE : -204 SQLSTATE: 42704
|SQLCAID: SQLCA SQLCABC 136 SQLERRP : DSNXOTL SQLEXT : 42704
|SQLERRD1 -500 SQLERRD2 0 SQLERRD3 0 SQLWARN0: SQLWARN1: SQLWARN2: SQLWARN3:
|SQLERRD4 -1 SQLERRD5 0 SQLERRD6 0 SQLWARN4: SQLWARN5: SQLWARN6: SQLWARN7:
| SQLWARN8: SQLWARN9: SQLWARNA:
|SQLERRM: BLA.BLA
|.........................................................................................................................
|DATA TYPE INDX ROW PROC 0 ROW EXAM 0 STG1-QUAL 0 STG2-QUAL 0 ROW INSRT 0
|ROW UPDTE 0 ROW DELET 0 PAGES 4 RI SCAN 0 RI DELET 0
|LOB SCAN 0 LOB UPDTE 0
|-------------------------------------------------------------------------------------------------------------------------

 

Note that most SQL level monitors like IBM Db2 Query Monitor can capture SQLCA.

2019/10/01 20:32:42  --------------------------------------------------- SQLCA Display 
Option ===> __________________________________________________________________________

DB2 SSID: I9A2 Plan: ADB DBRM: ADBMAIN Coll: ADBL
Cursor: C_PARM Section: 3
Stmt: 8,515 Type: PREPARE
---------------------------------------------------------------------------------------
DSNT408I SQLCODE = -204, ERROR: BLA.BLA IS AN UNDEFINED NAME
DSNT418I SQLSTATE = 42704 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOTL SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -500 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFE0C' X'00000000' X'00000000'
X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION

SQLCAID SQLCA
SQLCABC 136
SQLCODE -204
SQLERRML 07
SQLERRMC BLA.BLA
SQLERRP DSNXOTL
SQLERRD(1) X'FFFFFE0C' -500
SQLERRD(2) X'00000000' 0
SQLERRD(3) X'00000000' 0
SQLERRD(4) X'FFFFFFFF' -1
SQLERRD(5) X'00000000' 0
SQLERRD(6) X'00000000' 0
SQLWARN0
SQLWARN1
SQLWARN2
SQLWARN3
SQLWARN4
SQLWARN5
SQLWARN6
SQLWARN7
SQLWARN8
SQLWARN9
SQLWARNA
SQLSTATE 42704

BMC AppTune probably have a similar feature.

In Reply to william giannelli:

I am trying to debug a 00E70082, TYPE OF RESOURCE 3002 (maximum SPs). For this can I obtain the SQLCA area from a trace? I developer says in order to provide the SQLCA requires a production code change.

thanks

Bill



 

Best regards,

Jørn Thyssen

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

2019 IBM Champion.

Views are personal. 

Roy Boxwell

obtaining SQLCA area
(in response to Jørn Thyssen)
I do hope you do not put the IFCI 58 on in production!



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich



From: Jørn Thyssen [mailto:[login to unmask email]
Sent: Wednesday, October 2, 2019 2:34 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: obtaining SQLCA area



Yes, SQLCA is part of IFCID 58 (Db2 performance trace class(3)):



LOCATION: RS01IDS2 OMEGAMON XE FOR DB2 PERFORMANCE EXPERT (V5R4M0) PAGE: 1-53
GROUP: IDS2 RECORD TRACE - LONG REQUESTED FROM: NOT SPECIFIED
MEMBER: I9A2 TO: NOT SPECIFIED
SUBSYSTEM: I9A2 ACTUAL FROM: 10/02/19 00:23:05.23
DB2 VERSION: V11 PAGE DATE: 10/02/19
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 D6CF2C1FDE09 TS5941 TSO TS5941
TS5941 TS5941 TSO 00:23:15.98056923 197 2 58 END SQL <-- NETWORKID: ROCKNET1 LUNAME: I9A2DB2 LUWSEQ: 3
ADB 'BLANK' 0.00922626
|-------------------------------------------------------------------------------------------------------------------------
|LOCATION NAME : RS01IDS2
|PKG COLLECTION ID : ADBL
|PROGRAM NAME : ADBMAIN
|CONSISTENCY TOKEN : X'1A25E75A06C456BC' SECTION NUMBER : N/A
|STATEMENT NUMBER : 8515 QUERY COMMAND ID : X'0000000000000000' QUERY INSTANCE ID: X'0000000000000000'
|SQL REQUEST TYPE : 129 EXPANSION REASON : NO EXPANSION
| SQLCODE : -204 SQLSTATE: 42704
|SQLCAID: SQLCA SQLCABC 136 SQLERRP : DSNXOTL SQLEXT : 42704
|SQLERRD1 -500 SQLERRD2 0 SQLERRD3 0 SQLWARN0: SQLWARN1: SQLWARN2: SQLWARN3:
|SQLERRD4 -1 SQLERRD5 0 SQLERRD6 0 SQLWARN4: SQLWARN5: SQLWARN6: SQLWARN7:
| SQLWARN8: SQLWARN9: SQLWARNA:
|SQLERRM: BLA.BLA
|.........................................................................................................................
|DATA TYPE INDX ROW PROC 0 ROW EXAM 0 STG1-QUAL 0 STG2-QUAL 0 ROW INSRT 0
|ROW UPDTE 0 ROW DELET 0 PAGES 4 RI SCAN 0 RI DELET 0
|LOB SCAN 0 LOB UPDTE 0
|-------------------------------------------------------------------------------------------------------------------------



Note that most SQL level monitors like IBM Db2 Query Monitor can capture SQLCA.

2019/10/01 20:32:42 --------------------------------------------------- SQLCA Display
Option ===> __________________________________________________________________________

DB2 SSID: I9A2 Plan: ADB DBRM: ADBMAIN Coll: ADBL
Cursor: C_PARM Section: 3
Stmt: 8,515 Type: PREPARE
---------------------------------------------------------------------------------------
DSNT408I SQLCODE = -204, ERROR: BLA.BLA IS AN UNDEFINED NAME
DSNT418I SQLSTATE = 42704 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOTL SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -500 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFE0C' X'00000000' X'00000000'
X'FFFFFFFF' X'00000000' X'00000000' SQL DIAGNOSTIC
INFORMATION

SQLCAID SQLCA
SQLCABC 136
SQLCODE -204
SQLERRML 07
SQLERRMC BLA.BLA
SQLERRP DSNXOTL
SQLERRD(1) X'FFFFFE0C' -500
SQLERRD(2) X'00000000' 0
SQLERRD(3) X'00000000' 0
SQLERRD(4) X'FFFFFFFF' -1
SQLERRD(5) X'00000000' 0
SQLERRD(6) X'00000000' 0
SQLWARN0
SQLWARN1
SQLWARN2
SQLWARN3
SQLWARN4
SQLWARN5
SQLWARN6
SQLWARN7
SQLWARN8
SQLWARN9
SQLWARNA
SQLSTATE 42704

BMC AppTune probably have a similar feature.

In Reply to william giannelli:

I am trying to debug a 00E70082, TYPE OF RESOURCE 3002 (maximum SPs). For this can I obtain the SQLCA area from a trace? I developer says in order to provide the SQLCA requires a production code change.

thanks

Bill





Best regards,

Jørn Thyssen

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

2019 IBM Champion.

Views are personal.



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

Attachments

  • smime.p7s (5.1k)

Todd Burch

RE: obtaining SQLCA area
(in response to Roy Boxwell)

You can capture IFCID 58 without the overhead of a full performance trace class 3.  Yes, IFCID 58 is bundled with that trace and class, but you can quite easily capture just IFCID 58 if you want.

-START TRACE(P or A or S, doesn't matter) CLASS(30 or 31 or 32) IFCID(58) AUTHID(xxx) DEST ......  

Todd

James Campbell

obtaining SQLCA area
(in response to william giannelli)
You could try DSN1SDMP.

There's an example of getting a dump after an sqlcode -904.

James Campbell

On 1 Oct 2019 at 9:59, william giannelli wrote:

> I am trying to debug a 00E70082, TYPE OF RESOURCE 3002 (maximum SPs). For this can I obtain the SQLCA area from a trace? I developer says in order to provide the SQLCA requires a production code change.
> thanks
> Bill
>
>

--
This email has been checked for viruses by AVG.
https://www.avg.com

william giannelli

RE: obtaining SQLCA area
(in response to James Campbell)

Thank you all for your help! I really appreciate it!!!!

Yes I have run DSN1SDMP which gave me a dump. What is a convenient way of formatting and reading the dump? for example reading the SCLCA? I was trying to use ISPF IPCS  panels but I was not sure.

thanks

Bill

Todd Burch

RE: obtaining SQLCA area
(in response to william giannelli)

With an SVC dump, IPCS is your only option. 

We document how to find the SQLCA in an IPCS dump here: 

https://www.ibm.com/support/knowledgecenter/SSEPEK_12.0.0/trbshoot/src/tpc/db2z_trbshootfindsqlca.html

The doc at that location says it applies to 00E7.... (RDS) dumps, but it applies to DSN1SDMPs too.   The CT (Cursor Table control block) can usually be found at R13 +xF0.   It has an eyecatcher so you can always confirm you are there.  You can use member hlq.SDSNIVPD(DSNWCBDZ) to find the offets to the CTRDSP and other fields. 

Todd Burch

RE: obtaining SQLCA area
(in response to Todd Burch)

We do distribute a dump formatting program - DSNWDMP.    So, once you go into IPCS, with your SDSNLOAD concatenated properly, you can issue

IP VERBX DSNWDMP 'subsys=cccc,rs=3' and that will format the SQLCA.    Replace cccc with your subsystem ID.

Todd Burch

RE: obtaining SQLCA area
(in response to Todd Burch)

I just realized the link I provided has 2 bugs on it for V12.  

1) The CTRDSP field is at CT +X'30', not X'1C0'. 

2) The SQLCA can found at RDASQLCA.  RDASQLCA is at RDA +X'158', not at +X'08'. 

I've notified the doc people to fix it. 

James Campbell

RE: obtaining SQLCA area
(in response to william giannelli)

Browse the dump looking for the text string "SQLCA"?

James Campbell