ALOG 84038 - 20141209 - NARJXH5A - N/A - IN13853638

Paul Beltrandi Jr

ALOG 84038 - 20141209 - NARJXH5A - N/A - IN13853638
Quyen, I forgot to send this out last week.. This query that is in
program ITDB27S1 is using an INDEX.. It is the CLUSTERED INDEX and is
UNIQUE. I did run a REORG with runstats just to make sure. It is the
only SQL in the program

I did notice EXPLAIN was set to NO. I did REBINDS and now EXPLAIN says
YES.. I know when I first got here EXPLAINS were no. I believe we
changed EXPLAINS to YES somewhere in the process.. I like EXPLAINS YES
just for HISTORY.
Remember we can run EXPLaIN ONLY just see what the access paths are before
performing a REBIND or BIND.
Also the table only has 482,000 records. Not large table. This termed a
small table.. Access should be quick.
==================================================================================================================================================================
David Laaker, can we get an APA on this program? I also listed the 43
programs from Quyen below. The ones marked in BLUE need APA I believe...







PROGRAM-ID.  ITDB27S1.


EXEC SQL


    SELECT


        MSG_ID,


        CASH_REFND_CD,


        ADJ_RCPT_DT,


        XREF_SCCF_ID,


        PREV_XREF_SCCF_ID,


        PART_KEY_NUM,


        CRT_TS,


        LAST_UPDT_TS


    INTO


        :B27-MSG-ID,


        :B27-CASH-REFND-CD,


        :B27-ADJ-RCPT-DT:B27-ADJ-RCPT-DT-NI,


        :B27-XREF-SCCF-ID:B27-XREF-SCCF-ID-NI,


        :B27-PREV-XREF-SCCF-ID:B27-PREV-XREF-SCCF-ID-NI,


        :B27-PART-KEY-NUM,


        :B27-CRT-TS,


        :B27-LAST-UPDT-TS


    FROM IPP_MSG_ADJ


    WHERE MSG_ID = :B27-MSG-ID


END-EXEC.


============================================================================================


David,


There are 43 programs listed of which 12 programs have DB2. Can we gat APA's on these 12


ITCP4026 NO DB2
ITCP9174 NO DB2
ITCP9242 NO DB2
ITDRTE00 YES
ITDE02U1 YES
ITCP9164 NO DB2
ITCP9166 NO DB2
ITCP9178 NO DB2
ITCP9180 NO DB2
ITCP9182 NO DB2
ITCP9282 NO DB2
ITCP9646 NO DB2
ITDS01S1 YES
ITCP9208 NO DB2
ITCP2216 NO DB2
ITCP9410 NO DB2
ITCP4900 NO DB2
ITCP4152 NO DB2
ITCP4108 NO DB2
ITCQRQU0 NO DB2

ITDE02U1 YES
ITDS02S1 YES
ITDB22S1 YES
ITCP9107 NO DB2
ITPFM1S1 NO DB2
ITPFM2S1 NO DB2
ITCP2162 NO DB2
ITPFM3S1 NO DB2
ITPFM4S1 NO DB2
ITPFM5S1 NO DB2
ITDC01S1 YES
ITDBLUNF YES
ITPP01U1 NO DB2
ITDP01S1 YES
ITTDBP00 NO DB2
ITDP01U1 YES
ITUDBP00 NO DB2
ITCP9176 NO DB2
ITCP9184 NO DB2
ITCP9952 NO DB2

ITDUTLS1 YES
ITPEX1S1 YES
ITPEX4S1 NO DB2


NO










Thanks,
Paul Beltrandi Jr
Development DB2 DBA and Team Leader
AMS DBA Service Center
IBM Corporation
275 Grandview Avenue
Suite 201/Floor 2/Office 202E
Camp Hill, PA 17011
Tieline 8-254-7084 or 717-547-7084
[login to unmask email]



From: "Dao, Quyen" <[login to unmask email]>
To: Paul Beltrandi [login to unmask email], "O'Leary, Jim"
<[login to unmask email]>
Cc: "Thompson, David" <[login to unmask email]>, "Kerley, Duke"
<[login to unmask email]>, "Raimondi, Gerard"
<[login to unmask email]>
Date: 12/10/2014 05:48 PM
Subject: RE: ALOG 84038 - 20141209 - NARJXH5A - N/A - IN13853638



Hi Paul,

ITCP4026 is not a DB2 program so you won’t find the package in DB2 for this
program. Attached is all the possible SQL that could be invoked by this
program thru the sub-program call chain listed here.

ITCP4026 NO DB2
ITCP9174 NO DB2
ITCP9242 NO DB2
ITDRTE00 YES
ITDE02U1 YES
ITCP9164 NO DB2
ITCP9166
ITCP9178
ITCP9180
ITCP9182
ITCP9282
ITCP9646
ITDS01S1
ITCP9208
ITCP2216
ITCP9410
ITCP4900
ITCP4152
ITCP4108
ITCQRQU0
ITDE02U1
ITDS02S1
ITDB22S1
ITDB27S1
ITCP9107
ITPFM1S1
ITPFM2S1
ITCP2162
ITPFM3S1
ITPFM4S1
ITPFM5S1
ITDC01S1
ITDBLUNF
ITPP01U1
ITDP01S1
ITTDBP00
ITDP01U1
ITUDBP00
ITCP9176
ITCP9184
ITCP9952
ITDUTLS1
ITPEX1S1
ITPEX4S1

Thanks,

Quyen Dao
System Engineer
NASCO

Desk: 717-728-9453
Email: [login to unmask email]

From: Paul Beltrandi Jr [mailto:[login to unmask email]
Sent: Wednesday, December 10, 2014 8:35 AM
To: Dao, Quyen
Cc: Thompson, David; Kerley, Duke; Raimondi, Gerard
Subject: Re: ALOG 84038 - 20141209 - NARJXH5A - N/A - IN13853638



Quyen, I need some help with this ALOG. Job NARJXH5A ran long. HELP

SDSF OUTPUT DISPLAY NARJXH5A JOB36881 DSID 110 LINE 0 COLUMNS 02-
81
COMMAND INPUT ===> SCROLL ===>
CSR
********************************* TOP OF DATA
**********************************
DSN

RUN PROGRAM(ITCP4026) PLAN(ITSPRODB)

DSN

******************************** BOTTOM OF DATA
********************************

Can you tell me what the name of the DB2 program that is in composite
ITCP4026. I have to run EXPLAINS on it..

Thanks,
Paul Beltrandi Jr
Development DB2 DBA and Team Leader
AMS DBA Service Center
IBM Corporation
275 Grandview Avenue
Suite 201/Floor 2/Office 202E
Camp Hill, PA 17011
Tieline 8-254-7084 or 717-547-7084
[login to unmask email]

Inactive hide details for "Thompson, David" ---12/10/2014 07:38:00
AM---======================================================="Thompson,
David" ---12/10/2014 07:38:00
AM---===========================================================================
ALOG #: 84038


From: "Thompson, David" <[login to unmask email]>
To: Paul Beltrandi [login to unmask email]
Date: 12/10/2014 07:38 AM
Subject: ALOG 84038 - 20141209 - NARJXH5A - N/A - IN13853638




===========================================================================

ALOG #: 84038 CYCLE DATE: 12/9/2014
ASSIGNED TO: Patil, Subodh ONCALL SE: Reeves, Dan
ABEND JOB: NARJXH5A ABEND CODE: N/A JOB/PROC STEP:
ISM #: IN13853638 CSR#: XXXXXXXX
PLANS IMPACTED:
DSSR? No DATA UPDATED? No CODE CHANGED? No
===========================================================================

PROBLEM:
NARJXH5A had over double the normal volume tonight and caused the NARJX9M9
Home Late timestamps to be missed.


Last night's input file - PNASC.HRBAT.HOMEA5SS.MI.G1883V00
2145 Tracks
Tonight's input file - PNASC.HRBAT.HOMEA5SS.MI.G1884V00
4515 Tracks
===========================================================================

CYCLE FIX:
NARJX5HA only ran for 35 minutes Monday night but tonight ir ran for 111
minutes - starting at 22.54.17 and finishing at 00.45.14.


NARJX9M9 posting ended at 00.47.49.


Notified the DM - Dirk Banning. Also called the DBA on-call (Satysh) to
have him look at the job while it was running. Overall CPU was not
constrained and under 50% most of the time XH5A was running.


===========================================================================

RESOLUTION:
Notes from Tammy:


here are some details I just researched --wanted to see if we missed
something--nothing really strange, ITS was running about 15 min behind
yesterday with no issues -it really should not have been an issue --UNTIL
XH5A


No abends, no contentions, CPU at about 50% most of the night
start times leading up to Xh5A
xhf0-(M 22:16- T 22:33)
xh50-(M 22:37- T 22:53)
xh5a-(M 22:38- T 22:54)


NARJx9MR=ITS done-(M 23:16- T ?)
===========================================================================

NOTES:
TLD:this info will be needed for the DSSR
PNASC.HRBAT.CYCSYITM.FDBCOUNT.G1171V00


it is from NARJYITM
has info
HOME LATE CA 12/10/2014 00:46:20 0
HOME LATE NJ 12/10/2014 00:46:31 70,963
HOME LATE MO 12/10/2014 00:47:00 0
HOME LATE NY 12/10/2014 00:46:19 0
HOME LATE MI 12/10/2014 00:46:31 117,148
HOME LATE DC 12/10/2014 00:47:11 5,654
HOME LATE MD 12/10/2014 00:46:19 15,047
HOME LATE MA 12/10/2014 00:46:33 73,277
HOME LATE KY 12/10/2014 00:47:01 0
HOME LATE CM 12/10/2014 00:46:15 391,357
HOME LATE FL 12/10/2014 00:46:20 0
HOME LATE LA 12/10/2014 00:46:31 0
HOME LATE MC 12/10/2014 00:46:41 2,310
HOME LATE CT 12/10/2014 00:46:20 0
HOME LATE GA 12/10/2014 00:46:31 0
HOME LATE PB 12/10/2014 00:46:41 0
HOME LATE AO 12/10/2014 00:46:50 0
===========================================================================




----------------------------------------- EMAIL PRIVACY NOTICE: The
information in this message (and the documents attached to it, if any) is
confidential, and may be legally privileged. It is intended solely for the
addressee. Access to this message by anyone else is unauthorized. If you
are not the intended recipient, any disclosure, copying, distribution or
any action taken, or omitted to be taken in reliance on it is prohibited
and may be unlawful. If you have received this message in error, please
delete all electronic copies of this message (and the documents attached to
it, if any), destroy any hard copies you may have created and notify me
immediately. Thank you.(See attached file: SQL.TXT)

Attachments

  • graycol.gif (<1k)
  • SQL.TXT (110.8k)