RE: [EXTERNAL] RE: Problem with view that uses an SQL function created from a procedure within a service program

Hi Alan,

I think Kevin's suggestion is a great one. In the past, when trying to debug issues like this, I have found creating a quick table and writing the pertinent details to it is a fast and easy way to track down the last good record processed. This should help you find the next one, which presumably has your problem in it.

Thanks,
Karl.

              

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Alan Shore via MIDRANGE-L
Sent: Friday, October 30, 2020 12:16 PM
To: Kevin Bucknum <Kevin@xxxxxxxxxxxxxxxxxxx>; midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Alan Shore <ashore@xxxxxxxx>
Subject: RE: [EXTERNAL] RE: Problem with view that uses an SQL function created from a procedure within a service program

Thanks for your reply Kevin
Unfortunately the SQL doesn’t stop (like in a msgw)– giving me something to look at, it finishes I have thought of changing the procedure – and that is definitely an option – but if I change this procedure- its definitely going to impact some other projects – and I don’t know how much You HAVE given me an idea though Maybe I can collect the rrn as well as the data to another file Let me try that

Alan Shore
E-mail : ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
‘If you're going through hell, keep going.’
Winston Churchill

From: Kevin Bucknum [mailto:Kevin@xxxxxxxxxxxxxxxxxxx]
Sent: Friday, October 30, 2020 3:08 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Alan Shore <ashore@xxxxxxxx>
Subject: Re: [EXTERNAL] RE: Problem with view that uses an SQL function created from a procedure within a service program

Just throwing stuff against the wall here.
When you get the error, can you look at the open files in the job and see the record number?
Can you add some error checking in you service program to trap it and send some messages, write some logs etc?
It's running to a certain point. Can you see the last good record it wrote? Remove the call to the function, run it again, and see what the record right after the good one was?

On Fri, 2020-10-30 at 18:46 +0000, Alan Shore via MIDRANGE-L wrote:

Thanks for your reply Rob

The honest answer is - I have never used RDi for debugging anything

I will need to see what needs to be done to accomplish this

I was hoping to use something that I am comfortable with

This probably means that I have to get comfortable with debugging in RDi

Anyone any suggestions as to web sites that explains this?



Alan Shore

E-mail :
<mailto:ASHORE@xxxxxxxx>

ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>




Phone [O] : (631) 200-5019

Phone [C] : (631) 880-8640

‘If you're going through hell, keep going.’

Winston Churchill





-----Original Message-----

From: MIDRANGE-L [mailto:
<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>

midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>


] On Behalf Of Robert Rogerson

Sent: Friday, October 30, 2020 2:07 PM

To: Midrange Systems Technical Discussion < <mailto:midrange-l@xxxxxxxxxxxxxxxxxx>

midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>

Subject: Re: [EXTERNAL] RE: Problem with view that uses an SQL function created from a procedure within a service program



Alan, can't you use a SEP in RDi to debug the service program?



Rob



On Fri, Oct 30, 2020 at 12:56 PM Alan Shore via MIDRANGE-L < <mailto:midrange-l@xxxxxxxxxxxxxxxxxx>

midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>

wrote:

Thanks for your reply Peter

That's the problem

I don't know which record is causing the problem That's one of the

pieces of information I want to obtain Im also having problems in

getting the SQL debugger to work and my google searches on SQL

debugger are not successful





Alan Shore

E-mail :
<mailto:ASHORE@xxxxxxxx>

ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>




Phone [O] : (631) 200-5019

Phone [C] : (631) 880-8640

'If you're going through hell, keep going.'

Winston Churchill





-----Original Message-----

From: MIDRANGE-L [mailto:
<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>

midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>


] On

Behalf Of Peter Dow

Sent: Friday, October 30, 2020 12:49 PM

To:
<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>

midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>




Subject: Re: [EXTERNAL] RE: Problem with view that uses an SQL

function created from a procedure within a service program



Hi Alan,



If you know which record is causing the problem, you can set a break

point when that record is read, e.g. "br stmt# when recfld = somevalue".



--

*Peter Dow* /

Dow Software Services, Inc.

909 793-9050
<mailto:petercdow@xxxxxxxxx>

petercdow@xxxxxxxxx<mailto:petercdow@xxxxxxxxx>


<mailto:
<mailto:petercdow@xxxxxxxxx>

petercdow@xxxxxxxxx<mailto:petercdow@xxxxxxxxx>

<mailto:pdow@xxxxxxxxxxxxxx>

pdow@xxxxxxxxxxxxxx<mailto:pdow@xxxxxxxxxxxxxx>




<mailto:
<mailto:pdow@xxxxxxxxxxxxxx>

pdow@xxxxxxxxxxxxxx<mailto:pdow@xxxxxxxxxxxxxx>

/

On 10/30/2020 6:43 AM, Alan Shore via MIDRANGE-L wrote:

Hmmm

Interesting

I tried it again - now I am through

Its going to be one of those days folks



Alan Shore

E-mail :
<mailto:ASHORE@xxxxxxxx>

ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>




Phone [O] : (631) 200-5019

Phone [C] : (631) 880-8640

'If you're going through hell, keep going.'

Winston Churchill





-----Original Message-----

From: MIDRANGE-L [mailto:
<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>

midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>


] On

Behalf Of Alan Shore via MIDRANGE-L

Sent: Friday, October 30, 2020 9:42 AM

To: Midrange Systems Technical Discussion

<
<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>

midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>

Cc: Alan Shore <
<mailto:ashore@xxxxxxxx>

ashore@xxxxxxxx<mailto:ashore@xxxxxxxx>

Subject: RE: [EXTERNAL] RE: Problem with view that uses an SQL

function created from a procedure within a service program



I found this article
<https://www.itjungle.com/2020/06/08/guru-graphical-debugging-through>

https://www.itjungle.com/2020/06/08/guru-graphical-debugging-through




-a

cs/



That I attempted to follow

The problem is - its telling me that I have a communication error

with my system But that's all its telling me



Alan Shore

E-mail :
<mailto:ASHORE@xxxxxxxx>

ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>




Phone [O] : (631) 200-5019

Phone [C] : (631) 880-8640

'If you're going through hell, keep going.'

Winston Churchill





-----Original Message-----

From: Alan Shore

Sent: Friday, October 30, 2020 9:28 AM

To: Midrange Systems Technical Discussion

<
<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>

midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>

Subject: RE: [EXTERNAL] RE: Problem with view that uses an SQL

function created from a procedure within a service program



Thanks for your reply Birgitta

Ive never tried that

Let me look and see how to use it



Alan Shore

E-mail :
<mailto:ASHORE@xxxxxxxx>

ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>




Phone [O] : (631) 200-5019

Phone [C] : (631) 880-8640

'If you're going through hell, keep going.'

Winston Churchill





-----Original Message-----

From: MIDRANGE-L [mailto:
<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>

midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>


] On

Behalf Of Birgitta Hauser

Sent: Friday, October 30, 2020 9:26 AM

To: 'Midrange Systems Technical Discussion'

<
<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>

midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>

Subject: [EXTERNAL] RE: Problem with view that uses an SQL function

created from a procedure within a service program



Did you try to use the SQL Debugger in ACS?



Mit freundlichen Grüßen / Best regards



Birgitta Hauser





"Shoot for the moon, even if you miss, you'll land among the stars."

(Les

Brown)

"If you think education is expensive, try ignorance." (Derek Bok)

"What

is worse than training your staff and losing them? Not training them

and keeping them!"

"Train people well enough so they can leave, treat them well enough

so they don't want to." (Richard Branson)





-----Original Message-----

From: MIDRANGE-L <
<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>

midrange-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx>

On Behalf

Of Alan Shore via MIDRANGE-L

Sent: Freitag, 30. Oktober 2020 13:55

To:
<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>

midrange-l@xxxxxxxxxxxxxxxxxx<mailto:midrange-l@xxxxxxxxxxxxxxxxxx>




Cc: Alan Shore <
<mailto:ashore@xxxxxxxx>

ashore@xxxxxxxx<mailto:ashore@xxxxxxxx>

Subject: Problem with view that uses an SQL function created from a

procedure within a service program



Hi everyone

We are on V7r3

We have a view that is created using a couple of SQL functions

created

from procedures within one service program Whenever the view is

accessed For example Select * from view It aborts with QRY2293 Query

cannot be run - see lower level messages Looking at the job log, I see Decimal data error.

Application error. MCH1202 unmonitored by QC2UTIL1 at statement

0000000005, instruction X'0000'.

User-defined function error on member VIEW.

F1 on Decimal data error doesn't show me anything

F1 on User-defined function displays that the user defined function

is

ITEMPHYSICAL, which is the SQL function from the procedure in the

service program So I tried STRDBG SRVPGM(PRODPA/ITMMSTSP) Which is the

service program, pressing F10 I can see that it goes into the service

program, where I press F12 to continue - hoping that it will stop in

the service program where the problem is But it doesn't Does anyone

know how to debug this situation?

As always - all replied gratefully accepted







Alan Shore

E-mail :
<mailto:ASHORE@xxxxxxxx>

ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>


<mailto:
<mailto:ASHORE@xxxxxxxx>

ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>

Phone [O] : (631) 200-5019

Phone [C] : (631) 880-8640

'If you're going through hell, keep going.'

Winston Churchill



--

This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing list To post a message email:
<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>




To subscribe, unsubscribe, or change list options,

visit:
<https://lists.midrange.com/mailman/listinfo/midrange-l>

https://lists.midrange.com/mailman/listinfo/midrange-l




or email:
<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>




Before posting, please take a moment to review the archives at <https://archive.midrange.com/midrange-l>

https://archive.midrange.com/midrange-l


.



Please contact
<mailto:support@xxxxxxxxxxxxxxxxxxxx>

support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx>


for any subscription

related questions.



Help support midrange.com by shopping at amazon.com with our

affiliate

link:
<https://amazon.midrange.com>

https://amazon.midrange.com






--

This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing list To post a message email:
<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>




To subscribe, unsubscribe, or change list options,

visit:
<https://lists.midrange.com/mailman/listinfo/midrange-l>

https://lists.midrange.com/mailman/listinfo/midrange-l




or email:
<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>




Before posting, please take a moment to review the archives at <https://archive.midrange.com/midrange-l>

https://archive.midrange.com/midrange-l


.



Please contact
<mailto:support@xxxxxxxxxxxxxxxxxxxx>

support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx>


for any subscription

related questions.



Help support midrange.com by shopping at amazon.com with our

affiliate

link:
<https://amazon.midrange.com>

https://amazon.midrange.com






--

This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing list To post a message email:
<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>




To subscribe, unsubscribe, or change list options,

visit:
<https://lists.midrange.com/mailman/listinfo/midrange-l>

https://lists.midrange.com/mailman/listinfo/midrange-l




or email:
<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>




Before posting, please take a moment to review the archives at <https://archive.midrange.com/midrange-l>

https://archive.midrange.com/midrange-l


.



Please contact
<mailto:support@xxxxxxxxxxxxxxxxxxxx>

support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx>


for any subscription

related questions.



Help support midrange.com by shopping at amazon.com with our

affiliate

link:
<https://amazon.midrange.com>

https://amazon.midrange.com








--

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list To post a message email:
<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>


To

subscribe, unsubscribe, or change list options,

visit:
<https://lists.midrange.com/mailman/listinfo/midrange-l>

https://lists.midrange.com/mailman/listinfo/midrange-l




or email:
<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>




Before posting, please take a moment to review the archives at <https://archive.midrange.com/midrange-l>

https://archive.midrange.com/midrange-l


.



Please contact
<mailto:support@xxxxxxxxxxxxxxxxxxxx>

support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx>


for any subscription

related questions.



Help support midrange.com by shopping at amazon.com with our affiliate

link:
<https://amazon.midrange.com>

https://amazon.midrange.com






--

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list To post a message email:
<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>


To

subscribe, unsubscribe, or change list options,

visit:
<https://lists.midrange.com/mailman/listinfo/midrange-l>

https://lists.midrange.com/mailman/listinfo/midrange-l




or email:
<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>




Before posting, please take a moment to review the archives at <https://archive.midrange.com/midrange-l>

https://archive.midrange.com/midrange-l


.



Please contact
<mailto:support@xxxxxxxxxxxxxxxxxxxx>

support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx>


for any subscription

related questions.



Help support midrange.com by shopping at amazon.com with our affiliate

link:
<https://amazon.midrange.com>

https://amazon.midrange.com






--

This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email:
<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxxx>


To subscribe, unsubscribe, or change list options,

visit:
<https://lists.midrange.com/mailman/listinfo/midrange-l>

https://lists.midrange.com/mailman/listinfo/midrange-l




or email:
<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>

MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx>




Before posting, please take a moment to review the archives at <https://archive.midrange.com/midrange-l>

https://archive.midrange.com/midrange-l


.



Please contact
<mailto:support@xxxxxxxxxxxxxxxxxxxx>

support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxxx>


for any subscription related questions.



Help support midrange.com by shopping at amazon.com with our affiliate link:
<https://amazon.midrange.com>

https://amazon.midrange.com





[https://www.medtronsoftware.com/img/MedtronMinilogo.bmp] Kevin Bucknum
Senior Programmer Analyst
MEDDATA / MEDTRON
120 Innwood Drive
Covington LA 70433
Local: 985-893-2550
Toll Free: 877-893-2550
https://www.medtronsoftware.com


CONFIDENTIALITY NOTICE


This document and any accompanying this email transmission contain confidential information, belonging to the sender that is legally privileged. This information is intended only for the use of the individual or entity named above. The authorized recipient of this information is prohibited from disclosing this information to any other party and is required to destroy the information after its stated need has been fulfilled. If you are not the intended recipient, or the employee of agent responsible to deliver it to the intended recipient, you are hereby notified that any disclosure, copying, distribution or action taken in reliance on the contents of these documents is STRICTLY PROHIBITED. If you have received this email in error, please notify the sender immediately to arrange for return or destruction of these documents.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com