Strange Anomoly with F0911LQ (A8.1)

[tpayne]

We upgraded our World system from A8.1 cum5 to cum6 yesterday, and it went reasonably smooth with one exception, the GL Posting jobs (J09800) started to take 4+ hours to run instead of minutes.

It took us a few hours to find the cause, but I was curious to whether any other companies have the strange anomoly as we do with F0911LQ... I'm not sure if this is just an A8.1 file or if it exists also in A7.3...

This logical has selected all records where GLPOST = 'P' and GLALT4 <> 'P', ie: all posted transactions where one of the Alternate Posting flags is not showing Posted.

In our production system prior to the upgrade, as well as all the other copies of the file we have, including the objects from JDE, the index contains zero records, however in production it now shows 78 million records. Either use DSPFD F0911LQ or WRKF F0911LQ and option 8 - the number of entries in the Index is shown near the end of the list. If the file is corrupted, the number of index entries should be showing as zero.

It looks as if this file was shipped corrupted all along, the source member and object dates dating back to 1999, however doing a CRTDUBOBJ as part of the upgrade the index now "correctly" includes the records that it should. Of course that now means the first posting job is running programs P51801 and P51802 as part of J09800 and taking hours to run, processing through 78 million records...

 

[PageR]

This=20happened=20to=20me=20some=20years=20back=20on=20A7.3,=20and=20I=20m anaged=20to=20fix=20it.=20The=20answer=20lies=20somewhere=20between=20your =20Commitment=20Setup/Rebuilds=20settings=20(G43B411)=20which=20have=20a=20 bearing=20on=20the=20use=20of=20the=20F0911LQ=20in=20the=20P09800=20post=20 program,=20and=20updating=20the=20F0911LQ=20by=20WW=20or=20other=20means=20 to=20make=20sure=20GLALT4=20is=20always=20=3D=20'P',=20on=20a=20fairly=20r egular=20basis.=20That=20way=20(from=20poor=20memory)=20the=20time
P51801=20etc=20takes=20to=20index=20F0911LQ=20is=20drastically=20reduced.

However,=20of=20course=20you=20must=20consider=20your=20own=20environment=20 and=20module=20useage,=20for=20instance=20we=20were=20not=20using=20Job=20 Cost,=20but=20were=20using=20the=20PA/PU=20ledgers.=20Hopefully=20that=20w ill=20give=20you=20a=20few=20things=20to=20look=20for...=20

 

[PageR]

also=20see=20http://download-uk.oracle.com/docs/cd/B28738_01/jded/acrobat/ a73ceapo.pdf=20=20=20page=20423

 

[John_Dickey]

Tony,

This file is in A7.3. However, when I look on my system, I have a
'P' in the GLALT4 field in my records, so the records are being
correctly excluded from the number of index entries. This may just be
an isolated situation at your location with this logical file being
corrupted.

John Dickey
Financial Systems Administrator
White-Rodgers, division of Emerson Electric
8100 West Florissant Ave.
P.O. Box 36922
St. Louis, MO 63136-9022
314-553-3067
[email protected]

 

[dhashimura]

Tony,

We're at A7.3 on V5R2, & our index entries are the # of rcds that are selected via the S/O specs. Our F0911LQ has no rcds and index entries = 0. I verified the zero count via Query Mgr selection on the F0911. The logicals whose index entries = # of rcds in the physical are those with different keys/indices only. Most logicals w/ S/O specs have index entries <> # of physical file rcds. Is it different on V5R3?

Thank you, Debbie

 

[PageR]

This happened to me some years back on A7.3, and I managed to fix it. The answer lies somewhere between your Commitment Setup/Rebuilds settings (G43B411) which have a bearing on the use of the F0911LQ in the P09800 post program, and updating the F0911LQ by WW or other means to make sure GLALT4 is always = 'P', on a fairly regular basis. That way (from poor memory) the time
P51801 etc takes to index F0911LQ is drastically reduced.

However, of course you must consider your own environment and module useage, for instance we were not using Job Cost, but were using the PA/PU ledgers. Hopefully that will give you a few things to look for...

also see http://download-uk.oracle.com/docs/cd/B28738_01/jded/acrobat/a73ceapo.pdf page 423

 

[Eric Lehti]

yes, F0911LQ is on our A73 Cum9 system, and was created in 2000. A query/SQL select returns zero records. I copied the source member to F0911LQEWL and compiled that for you today, and that returns zero records also. F0911LQ source is:
R I0911 PFILE(F0911
TEXT('LF -use
Weekly Accti
K GLCO
K GLMCU
K GLOBJ
K GLSUB
K GLDGJ
S GLPOST COMP(EQ 'P')
GLALT4 COMP(NE 'P')
** End of data **************************

 

[dlk1999]

Hi,
Not sure if this is the same issue. I once saw this in A73. I used this doc WFN-99-0033 : -
---------------------------------------
Post - Extensive Time for Completion
Abstract: Describes the problem and resolution when the post runs for an extended amount of time.


--------------------------------------------------------------------------------

How it Works
If the post program is taking an unusually long time to complete, verify the setup of your Job Cost Constants (P0026). Even if you did not purchase the job cost system, you will still have the Job Cost Constants program since it has been coded to system 00. The problem arises when the Commitment Relief and the Projected Final Cost flags are set to Y or blank in the Job Cost Constants. Leaving these two fields blank will have the default value of a ‘Y’ from the data dictionary.

If the Commitment Relief flag is set to Y, the post program calls Update Commitments (P51801). This program attempts to update the ALT4 posted code on the F0911 records to a value of P. Since, P51801 is also coded to system 00 (all clients have this program), it will attempt to process based on the flag in the constants. If, however, the Projected Final Cost flag is also set to Y, then Update Projected Final (P51802) is called, instead of P51801. This program updates the ALT4 field to a P, but it is only available to clients with system 51 for Job Cost.

The post program is attempting to update the ALT4 posted code on all the F0911 records to a P based on the values in the Job Cost Constants. The F0911LQ contains records where the posted code is P, but the ALT4 post code is not equal to P. If using Commitments, both fields (GLPOST and GLALT4) should be P. The F0911LQ should not contain any records after a post is completed. The following explains the sequence of events that may cause this:

The Post (P09800) calls Update Commitments (P51801). P51801 is coded to system 00, so all clients will have it. P51801 looks at the Job Cost Constants file (F0026-this file is also coded to system 00) and reads the commitment relief flag. If this flag is set to Y, the P51801 will update the ALT4 code to a value of C and then to a value of P. However, if the Projected Final Cost Flag in the constants is set to Y, then P51801 doesn’t update the ALT4 post code. It calls Update Projected Final Costs (P51802) and then ends.
P51802 is coded to system 51 so only job cost clients will have it. P51802 updates the ALT4 posted code in the F0911 to a P.
Possible problems with the ALT4 posted code:
If Job Cost is not being used, but the Projected Final Flag in the F0026 set to Y, the ALT4 post code will never be updated to P. The P51801 program won’t update the field because it expects P51802 to be called which will update the field.
If Job Cost is not being used, and the Commitment Relief Flag is set to N, neither P51801 or P51802 will update the ALT4 post code to P.
For clients who have the Commitment Relief Flag set to Y, the post will continue to run until all F0911 records have been updated with a P in the ALT4 field.
To correct this problem either:

Update the ALT4 post code to a value of P by either executing QAD Conversion to Set ALT4 to P (P51994) from a versions list, or by using World Writer or SQL to update the F0911LQ ALT4 post code to P.
OR

Delete and recreate the F0911LQ.
-----------------------------------


Hope this is helpful.Sorry for the long listing..dlk1999

 

[tpayne]

Thanks for your help everyone. All the advice you gave was relevant.
We now have the situation fixed, and it looks like it has been a combination of things. Since we did the upgrade on a Sunday and our F0911 has 96 million records, any processing takes forever, and we could not prove until yesterday exactly what happened.

It looks as if F0911LQ must have got corrupted in production sometime in the last 3 years, there it had no records, and the P51801 and P51802 programs that run during posting did nothing. However, following the upgrade on Sunday, the logical got recopied, and sudeenly ended up with 78 million records, so the first posting job that went through was taking more than 5 hours instead of 2 minutes.

We could not figure out why we did not get this problem when we ran the upgrade on our test environment at the end of November and spent ages trying to determine how the upgrade fixed the logical now but not before, but fortunately we had a copy of a joblog from the original upgrade in November showing a posting job that ran ad midnight and took forever. Nobody noticed this, so of course when they posted batches in test they ran quickly.

Thanks also for the document from JDE regarding the setup of Extensive Time For Posting Job Completion. JDE sent us a copy of this yesterday in fact, and it has helped to clarify a few things.

Yesterday afternoon we ran an SQL to set the GLALT4 flag to 'P' for all but 2006 transactions, which reduced the size of F)911LQ somewhat, then we set off a posting job at 6pm last night, which took most of the night to run, but as of this morning F0911LQ has zero records, which is what we were hoping to see.

Thanks again for all your help....