F0911 Query Processing Time

[Gopal]

All,

I have a specific Issue. I am trying to Run Cash Requirement report, which inturn Calls Account Balance Businessfunction. Account Balance Business function issues a fetch to f0911.

The fetch to F0911 is taking hours to get a response from our Oracle Database.

The same Fetch executed from SQl Worksheet fetches the data within a minute.

We also have the same issue when we have some custom reports going behind F0911, F0902,F0411 Tables.

Typical Log is like this..

> Cash Requirment Report Log-R04431 Log
>
> Mar 02 17:25:56.185 - 4584/5024 Entering JDB_SelectKeyedPosition
> Mar 02 17:25:56.185 - 4584/5024 ORACLE DBInitReq conn=03021D58 requ=04300A38 BTS01OR02 (DVPSDB)
new [ 9]
> Mar 02 17:25:56.185 - 4584/5024 SELECT GLDGJ, GLPOST, GLCO, GLAID, GLMCU, GLSBL, GLSBLT, GLLT,
GLPN, GLCTRY, GLFY, GLCRCD, GLAA, GLU, GLALT1, GLBCRC, GLCRRM FROM TSIADTA.F0911 WHERE ( GLPOST
>= 'P' AND GLAID >= '03304372' AND GLLT >= 'AA' AND GLCTRY >= 20.000000 AND GLFY >= 5.000000 AND
GLPN >= 3.000000 AND GLDGJ >= 105060 ) ORDER BY GLPOST ASC,GLAID ASC,GLLT ASC,GLCTRY ASC,GLFY
ASC,GLPN ASC,GLDGJ ASC
> Mar 02 21:20:34.831 - 4584/5024 Exiting JDB_SelectKeyedPosition with Success
> Mar 02 21:20:34.831 - 4584/5024 Entering JDB_FetchMatchingKey
> Mar 02 21:20:34.831 - 4584/5024 ORACLE DBFetch: Invoke OCI Fetch fetchNumRows = 100

-----------
It took 4 hours to retun the value. Any Suggestions on this is Highly Appreciated.

Thanks
Gopal.

 

[Sebastian Sajaroff]

Gopal,

Have you checked that your JDE server log is disabled?

 

[ccamponuevo]

Hi Gopal,
We have the same problem. If you repair the WHERE clause of the select doesn't make too much sense
WHERE (
GLPOST >= 'P'
AND GLAID >= '03304372'
AND GLLT >= 'AA'
AND GLCTRY >= 20.000000
AND GLFY >= 5.000000
AND GLPN >= 3.000000
AND GLDGJ >= 105060

we changed the BSFN to work correctly removing the unnecessary >= to = and it will work better.

 

[ccamponuevo]

Hi Gopal,
There is a case opened in Peoplesoft for this issue. Case # 3818704
The change is quite big because all the F0911 selects were replaced by JDB_Fetch and the selection was done SELECTSTRUCT.

 

[ccamponuevo]

Gopal,
SAR 7564731 is created for this issue.
Now is just wait for the ESU. There are a lot of objects with performance issues.


P09210 Trial Balance / Ledger Compari
P09214 Trial Balance by Object
P09215 Trial Balance by Subledger
P09216 Trial Balance by Company
P09218A Statutory Account Balance Inqu
R04431 Cash Requirements Report
R04431A Cash Requirements Report with
R093022 Variable Numerator Compute and
R093022 Variable Numerator Compute and
R09415 Monetary Account Valuation
R09420 G/L by Business Unit
R09421 G/L by Object Account
R09470 General Ledger by Category Cod
R09520 G/L Cash Forecasting
R74S560 G/L by Object Account - Spain
R76B420 G/L by Business Unit

 

[Gopal]

Thanks for the reply.
For time being ive changed the selection criteria as yo have mentioned.

Ive Noted The ESU too..

Thanks again

Gopal.

 

[dschlieder]

Carlos,

You stated that you changed the BSFN by removing the unnecessary >= to =

I'm stumped as to where in the BSFN you can make those changes - we are having the same issues and I would like to put in a temporary fix.

But my c++ skills aren't that great the there is so much hidden behind all the JDE functions, I just can't put my finger on where and what to change.

Could you point me in some direction at least as to where these changes can be made?

Or maybe the issue for me is that we are on different versions which is why I can't seem to find where to make any changes.

Thanks,

Dave

 

[Gopal]

If you carefully look at the CBSFN, There will be a command,
JDB_Fetchkeyedposition, Which means it will bring everything>=, JDB_FetchKeyedPosition has to replaced with JDB_FetchKeyed, I am replying from top of my head i may not be accurate with the JDE API Command. If you still couldnt find it, let me know.
I will let you know the exact API.

Thanks,
Gopal

 

[dschlieder]

Thanks!

I think the API you believe I should replace is JDB_SelectKeyedPosition based on what I have seen in the code.

I assume that JDB_FetchKeyed is the proper API call you are suggesting I change it to.

Guess I don't understand how this also handles where the date needs the >=.

I guess that's were the JDB_FetchMatchingKey comes into play - or there is more to change than just the API calls.

Is there any documentation on these API calls? My online from JDE does not have all of these calls, just a few of them. It may be older documention than the version we are on.

Thanks again!

Dave

 

[Gopal]

You are right about the API.

Ive asked the Functional Consultant to see what the change is if we have the date field criteria set to = instead of >=, they are happy with it and i didnt dig into further.

I am attaching the JDE APIs. Hope it will of help to you.

change the file extension to .chm and open then.

Thanks

 

[dschlieder]

Thanks again and sorry to keep pestering...

FWIW, the attachment comes up with a page not found error.

Could you e-mail it to me directly?

I've tried to find something on the knowledge garden with no luck..

Thanks again,

Dave

 

[dschlieder]

One more note.

I replaced the API call in the 8 places in the code where it goes after the F0911 and did some testing.

I get the wrong answer when the request is for the end of the period and there are transactions in the beginning of the period.

As of 4/15 through 4/30 gives me the wrong balance forward amount if there are transactions from 4/1 through 4/14 with these changes, with the way it is, it gives me the correct answer.

So there is something more than simply making these changes that I don't understand.

May I should just wait for the ESU to come out.

 

[Gopal]

You may be right. Better to wait for the ESU.

Thanks
Gopal.

 

[jde72]

we are trying to escalate the issue. changing >= to = will not really solve the problem.

does anybody has any other code change for this issue.

 

[dschlieder]

Assuming you are working with the same piece of code - B0900008 - and the same version or close to it - there are 8 places that you want to change JDB_SelectKeyedPosition to JDB_SelectKeyed AND you need to change the 4th parameter by reducing the value by 1 in each of those 8 places.

Failure to change that 4th parameter will cause you issues as the select becomes = for as many fields as are specified in that parameter for the index specified in the 3rd parameter.

Which means it will select out to the GL date with = - by reducing the number of fields used in the select, it removes the GL date from the select. Then the fetch matching key and the remaining code will select out the records needed for the date range.

I believe this will work base on my testing and what the debug log showed as the select statement after I made this change.

After making this change, I walked through a single business unit/object account one day at a time for a month and checked the values from day to day to day - it all seemed to work and balance out.

I have it it PY for the finance guys to hack at it and see what they think but they won't get to it till next week.

Their input was that this was useless they way it was and they would only run it for one business unit/object account at a time when they thought they might have an issue - ant then it might still take hours to run.

We have had this as an issue for years actually. Just the users never complained - I got started on it as there was an issue where they needed to run it and it took hours.

No guarantee that this is the correct solution! My choice of API to use and what to change is not based on a lot of experience in this area - it's based on what I got out of API documentation and some WAGs and testing on my part.

But when they get around to fixing it, I hope they have a decent record set to work with - no a handful of records so that they realize it is an issue once you get a lot of business units, object accounts a a few million records that get selected every time....

Hope this helps,

Dave Schlieder

 

[jde72]

for trial balance it goes after a different BSFN b0900090. but its same code which is taking long time.
what i found is

1. if data is around 3 million it comes back fast.

2. but when we have data around 6 million it takes a long time.

 

[dschlieder]

I'm trying to escalate the SAR on this issue - I just opened a case with JDE this morning and have been going round and round with why I want it escalted, what impact does it have, what does it cost me... Have a good analyst as my contact.

I just emailed my contact that there is another BSFN with the same issue. It looks like the same developer had his hands in both of these so maybe we can get both fixed at the same time...

I would try to open a case to ask JDE to escalate this - my fix might work but then it might not.

Regards,

Dave

 

[dschlieder]

Just got an email from JDE support - they are going to escalate SAR 7564731:

"I discussed the sar with the app tech who'd entered it and she agrees it should be escalated... because of this, my manager approved the escalation. The ball is now rolling."

Hopefully they will include B0900090 in this SAR as well or create another one for it.

Have a great weekend!

Dave

 

[dschlieder]

Some good news for anyone following this thread and having this issue.

The current status of SAR 7564731 is 21 - Programming/Pgmr. Test with a date planned if 5/4!

Persistance pays off.

B0900090 is mentioned in the beginning of the SAR but not in the final disposition comments... Hopefully they won't forget that one or they will create a new SAR for it..

Have a great weekend,

Dave