High Elapsed time in SP call statements

Gautam Sahu

High Elapsed time in SP call statements
Hi All,

I am trying to tune a CICS transaction which calls some DB2 stored
procedures. I see most of the CALL statements to the SPs have high elapsed
times even though the actual SPs are taking much lesser time. The CA
Detector data shows that the unaccounted time for these CALL statements are
particularly high. My initial suspicion was that the dispatching priority
of the DB2 WLM environment could be less than the CICS task which is why
its taking more time for the SPs from getting kicked off. But the system
performance guys confirmed they are running with same priority. What else
could cause high elapsed time for a CALL statement?
Given below is the data for one of the call statements and the
corresponding SP it calls.

SQL_CALL INDB2_TIME UNACC_TIME INDB2_CPU SP_TOT_TIME SP_DB2_TIME
--------------- ------------------ -------------------
----------------- -------------------- --------------------
CALLSTMT 05:06.351989 04:05.057742 00:15:057742 04:50.746834
04:05.746834

PROGRAM INDB2_TIME INDB2_CPU UNACC_TIME
--------------- ------------------ ----------------- -------------------
<SP1> 00:48.717782 00:38.849809 00:01.347630

Thank you for your time.
--
Thanks,
Gautam

Peter Conlin

High Elapsed time in SP call statements
(in response to Gautam Sahu)
Hi Gautam,
Is it possible you have a low NUMTCB for the WLM proc?
I don't know the reporting specifics of this package, but it looks like you spend most if the time waiting (WLM queued?) to get into Db2. Once in Db2, you get a good chunk of CPU for time there.
________________________________________
From: Gautam Sahu [[login to unmask email]
Sent: Tuesday, July 9, 2019 2:11 AM
To: [login to unmask email]
Subject: [DB2-L] - High Elapsed time in SP call statements

Hi All,

I am trying to tune a CICS transaction which calls some DB2 stored procedures. I see most of the CALL statements to the SPs have high elapsed times even though the actual SPs are taking much lesser time. The CA Detector data shows that the unaccounted time for these CALL statements are particularly high. My initial suspicion was that the dispatching priority of the DB2 WLM environment could be less than the CICS task which is why its taking more time for the SPs from getting kicked off. But the system performance guys confirmed they are running with same priority. What else could cause high elapsed time for a CALL statement?
Given below is the data for one of the call statements and the corresponding SP it calls.

SQL_CALL INDB2_TIME UNACC_TIME INDB2_CPU SP_TOT_TIME SP_DB2_TIME
--------------- ------------------ ------------------- ----------------- -------------------- --------------------
CALLSTMT 05:06.351989 04:05.057742 00:15:057742 04:50.746834 04:05.746834

PROGRAM INDB2_TIME INDB2_CPU UNACC_TIME
--------------- ------------------ ----------------- -------------------
<SP1> 00:48.717782 00:38.849809 00:01.347630

Thank you for your time.
--
Thanks,
Gautam


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

Gautam Sahu

High Elapsed time in SP call statements
(in response to Peter Conlin)
Hi Pete,

The NUMTCB is set to 20. This particular transaction runs at 5 AM in the
morning and only one instance of the transaction runs. Pretty sure there
are no CPU issues at that time. I am asking the developer to change the
call statements to normal sub module calls.

Regards,
Gautam

On Tue, Jul 9, 2019 at 4:48 PM Conlin, Pete <[login to unmask email]> wrote:

> Hi Gautam,
> Is it possible you have a low NUMTCB for the WLM proc?
> I don't know the reporting specifics of this package, but it looks like
> you spend most if the time waiting (WLM queued?) to get into Db2. Once in
> Db2, you get a good chunk of CPU for time there.
> ________________________________________
> From: Gautam Sahu [[login to unmask email]
> Sent: Tuesday, July 9, 2019 2:11 AM
> To: [login to unmask email]
> Subject: [DB2-L] - High Elapsed time in SP call statements
>
> Hi All,
>
> I am trying to tune a CICS transaction which calls some DB2 stored
> procedures. I see most of the CALL statements to the SPs have high elapsed
> times even though the actual SPs are taking much lesser time. The CA
> Detector data shows that the unaccounted time for these CALL statements are
> particularly high. My initial suspicion was that the dispatching priority
> of the DB2 WLM environment could be less than the CICS task which is why
> its taking more time for the SPs from getting kicked off. But the system
> performance guys confirmed they are running with same priority. What else
> could cause high elapsed time for a CALL statement?
> Given below is the data for one of the call statements and the
> corresponding SP it calls.
>
> SQL_CALL INDB2_TIME UNACC_TIME INDB2_CPU SP_TOT_TIME SP_DB2_TIME
> --------------- ------------------ -------------------
> ----------------- -------------------- --------------------
> CALLSTMT 05:06.351989 04:05.057742 00:15:057742 04:50.746834
> 04:05.746834
>
> PROGRAM INDB2_TIME INDB2_CPU UNACC_TIME
> --------------- ------------------ ----------------- -------------------
> <SP1> 00:48.717782 00:38.849809 00:01.347630
>
> Thank you for your time.
> --
> Thanks,
> Gautam
>
>
> -----End Original Message-----
>
> -----End Original Message-----
>
>

--
Thanks,
Gautam

Daniel Luksetich

High Elapsed time in SP call statements
(in response to Gautam Sahu)
Make sure the stored procedures are stay resident. Regardless if the stored procedures are infrequently used there could be start up overhead for the WLM address spaces. If so, I suggest moving them to a more active environment.

Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Past-Chairman

IDUG DB2-L Administrator

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Gautam Sahu <[login to unmask email]>
Sent: Tuesday, July 9, 2019 6:38 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: High Elapsed time in SP call statements



Hi Pete,



The NUMTCB is set to 20. This particular transaction runs at 5 AM in the morning and only one instance of the transaction runs. Pretty sure there are no CPU issues at that time. I am asking the developer to change the call statements to normal sub module calls.



Regards,

Gautam



On Tue, Jul 9, 2019 at 4:48 PM Conlin, Pete <[login to unmask email] <mailto:[login to unmask email]> > wrote:

Hi Gautam,
Is it possible you have a low NUMTCB for the WLM proc?
I don't know the reporting specifics of this package, but it looks like you spend most if the time waiting (WLM queued?) to get into Db2. Once in Db2, you get a good chunk of CPU for time there.
________________________________________
From: Gautam Sahu [[login to unmask email] <mailto:[login to unmask email]> ]
Sent: Tuesday, July 9, 2019 2:11 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - High Elapsed time in SP call statements

Hi All,

I am trying to tune a CICS transaction which calls some DB2 stored procedures. I see most of the CALL statements to the SPs have high elapsed times even though the actual SPs are taking much lesser time. The CA Detector data shows that the unaccounted time for these CALL statements are particularly high. My initial suspicion was that the dispatching priority of the DB2 WLM environment could be less than the CICS task which is why its taking more time for the SPs from getting kicked off. But the system performance guys confirmed they are running with same priority. What else could cause high elapsed time for a CALL statement?
Given below is the data for one of the call statements and the corresponding SP it calls.

SQL_CALL INDB2_TIME UNACC_TIME INDB2_CPU SP_TOT_TIME SP_DB2_TIME
--------------- ------------------ ------------------- ----------------- -------------------- --------------------
CALLSTMT 05:06.351989 04:05.057742 00:15:057742 04:50.746834 04:05.746834

PROGRAM INDB2_TIME INDB2_CPU UNACC_TIME
--------------- ------------------ ----------------- -------------------
<SP1> 00:48.717782 00:38.849809 00:01.347630

Thank you for your time.
--
Thanks,
Gautam


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

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






--

Thanks,
Gautam



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

David Alban

RE: High Elapsed time in SP call statements
(in response to Gautam Sahu)

Hi Gautam,

Take a look at the SECURITY parameter of the procedure. If the setting is USER and the call count is high you can reduce elapse times by using DB2 instead. There are security implications to changing this and you have to grant additional access to the WLM STC id, so take that into consideration.

V9 Redbook - Through the Call and Beyond:

"Interaction with external security products
The SECURITY parameter of the CREATE PROCEDURE statement specifies how the stored
procedure interacts with external security products, such as RACF, to control access to
non-SQL resources. If a stored procedure does not require an external security product to
protect access to non-SQL resources, then you should specify SECURITY DB2, which is the
default. SECURITY DB2 causes access to external resources to be performed using the
authid of the stored procedure address space. If the stored procedure does require an
external security product to access non-SQL resources, such as a VSAM file, you can specify
either SECURITY USER or SECURITY DEFINER to control access to the resource.
SECURITY USER will cause the external security product to use the authid of the user who
invoked the stored procedure. SECURITY DEFINER will cause the external security product
to use the authid of the owner of the stored procedure."

--David Alban

Gautam Sahu

High Elapsed time in SP call statements
(in response to Daniel Luksetich)
Hi Dan,

The SPs are not stay resident currently probably because these SPs are not
called very often. But I will explore that option. The WLM address space
are active when these SPs are run. Thanks for the suggestion.

Regards,
Gautam

On Tue, 9 Jul 2019 at 6:03 PM, Daniel L Luksetich <[login to unmask email]>
wrote:

> Make sure the stored procedures are stay resident. Regardless if the
> stored procedures are infrequently used there could be start up overhead
> for the WLM address spaces. If so, I suggest moving them to a more active
> environment.
>
> Dan
>
>
>
> Daniel L Luksetich
>
> DanL Database Consulting
>
>
>
> IBM GOLD Consultant
>
> IBM Champion for Analytics
>
> IDUG Content Committee Past-Chairman
>
> IDUG DB2-L Administrator
>
> IBM Certified Database Adminstrator – DB2 11 DBA for z/OS
>
> IBM Certified System Administrator – DB2 11 for z/OS
>
> IBM Certified Application Developer – DB2 11 for z/OS
>
> IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX
> and Windows
>
>
>
> *From:* Gautam Sahu <[login to unmask email]>
> *Sent:* Tuesday, July 9, 2019 6:38 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: High Elapsed time in SP call statements
>
>
>
> Hi Pete,
>
>
>
> The NUMTCB is set to 20. This particular transaction runs at 5 AM in the
> morning and only one instance of the transaction runs. Pretty sure there
> are no CPU issues at that time. I am asking the developer to change the
> call statements to normal sub module calls.
>
>
>
> Regards,
>
> Gautam
>
>
>
> On Tue, Jul 9, 2019 at 4:48 PM Conlin, Pete <[login to unmask email]> wrote:
>
> Hi Gautam,
> Is it possible you have a low NUMTCB for the WLM proc?
> I don't know the reporting specifics of this package, but it looks like
> you spend most if the time waiting (WLM queued?) to get into Db2. Once in
> Db2, you get a good chunk of CPU for time there.
> ________________________________________
> From: Gautam Sahu [[login to unmask email]
> Sent: Tuesday, July 9, 2019 2:11 AM
> To: [login to unmask email]
> Subject: [DB2-L] - High Elapsed time in SP call statements
>
> Hi All,
>
> I am trying to tune a CICS transaction which calls some DB2 stored
> procedures. I see most of the CALL statements to the SPs have high elapsed
> times even though the actual SPs are taking much lesser time. The CA
> Detector data shows that the unaccounted time for these CALL statements are
> particularly high. My initial suspicion was that the dispatching priority
> of the DB2 WLM environment could be less than the CICS task which is why
> its taking more time for the SPs from getting kicked off. But the system
> performance guys confirmed they are running with same priority. What else
> could cause high elapsed time for a CALL statement?
> Given below is the data for one of the call statements and the
> corresponding SP it calls.
>
> SQL_CALL INDB2_TIME UNACC_TIME INDB2_CPU SP_TOT_TIME SP_DB2_TIME
> --------------- ------------------ -------------------
> ----------------- -------------------- --------------------
> CALLSTMT 05:06.351989 04:05.057742 00:15:057742 04:50.746834
> 04:05.746834
>
> PROGRAM INDB2_TIME INDB2_CPU UNACC_TIME
> --------------- ------------------ ----------------- -------------------
> <SP1> 00:48.717782 00:38.849809 00:01.347630
>
> Thank you for your time.
> --
> Thanks,
> Gautam
>
>
> -----End Original Message-----
>
> -----End Original Message-----
>
>
>
>
> --
>
> Thanks,
> Gautam
>
>
> -----End Original Message-----
>
> -----End Original Message-----
>
--
Thanks,
Gautam