*** QUIZ *** F0911 "Update"

Christian Audet

Christian Audet

Moderator
Staff member
*** QUIZ *** F0911 \"Update\"

Hi Forum,

Here is the Question :

What will be the generated SQL statement for the code listed below?

EVENT: Button Clicked
------------------------------------------------------------------
0001 VA frm_cNewValue_EV01 = "X"
0002 VA frm_mnBatchNumber_ICU = 1178
0003 F0911.Update
VA frm_mnBatchNumber_ICU = TK Batch Number
VA frm_cNewValue_EV01 -> TK G/L Posting Code - Alternate T

If you think that the answer is simple, think twice !

Christian Audet
(I will post the answer in a later post)

Implementing B7333 (Xe) SP14.1, SQL
(Support B732, B7331 and B7332)
 
Re: *** QUIZ *** F0911 \"Update\"

I forgot to tell you the Alias in my last post.

Batch Number is "GLICU"
G/L Posting Code - Alternate is "GLALTT"

Good Luck

Implementing B7333 (Xe) SP14.1, SQL
(Support B732, B7331 and B7332)
 
Re: *** QUIZ *** F0911 \"Update\"

Ok !

here is the answer :

UPDATE F0911 SET GLICU=1178.000000,GLALTT='X' WHERE ( GLPOST = ' ' )

Ok, I know it's not right, but try it on your system and give me some feedback about it.

I have tried it under Xe and B733.2 SP11.1 and that's the result that I have in the jdedebug.log

The expected result should be:

UPDATE F0911 SET GLALTT='X' WHERE GLICU=1178.000000

You can also assume that my F0911 is not good anymore !!!

Please comment !

Christian Audet

Implementing B7333 (Xe) SP14.1, SQL
(Support B732, B7331 and B7332)
 
Re: *** QUIZ *** F0911 \"Update\"

Hi Christian,

Which index of F0911 did you selected for the update?
I haven't found any index for F0911 which begins with GLICU.
Is it a custom one at your side? If yes, describe this index for us.

Your example is an update with a selection based on a (supposly) partial key.
As far as I know it could work properly only when you define your selection starting from the first field of the index. You don't have to specify selection for all fields of the index but you can not skip fields, namely define selection for:
1st,
1st + 2nd
1st + 2nd + 3rd
etc.
fields of the index.

Try to create an index for GLICU, generate it and use this one in the update statement.

Good luck,
Zoltán
P.S.: Really hope, you have a backup copy on your F0911.

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 
Re: *** QUIZ *** F0911 \"Update\"

Hi Zoltan !

Yes I have a backup of F0911 and it's already restored.

For my issue I took a existing index on Batch Type, Batch Number, Document Type ...
Yes I did it with partial key, but that is the first time that I see that kind of bad translation. I took numerous partial key update before without any problem.

Even if I did it with partial key, the UPDATE in the jdedebug.log is very dangerous, can you explain it or is it documented somewhere ?

I will try on my demo machine to add a new index only on ICU and I will see, but I think it will work. Is there any rules somewhere for partial key update like your example 1st field+2nd field+3rd field.

Thank you for your help !

Christian Audet

Implementing B7333 (Xe) SP14.1, SQL
(Support B732, B7331 and B7332)
 
Re: *** QUIZ *** F0911 \"Update\"

Hi Christian,

First let me some question:
Q1.) Have you really ever made successful patial key update when you haven't made selection for the first field of the index, just for the second or third, etc.?

Q2.) Have you used really the "Batch Type, Batch Number, Document Type ..." index instead one which begins with "Post Code" (not with "Batch Number"?

Here is a suggestion:
Try to define something dummy selection for the fields in the index before Batch Number which will really select ALL and won't be exclude anything. e.g.
evt_ICUT = " "
evt_ICU = 1178
F0911.Update
evt_ICUT >= TK ICUT
evt_ICU = TK ICU
Please, let us know the results.

Unfortunately I didn't know about any documentation on partial key update. Although I have never used Table I/Os in C++ BSFNs, I checked the JDBase APIs in the Published APIs On-Line help and found something interesting. Most of the keyed API calls contain a parameter:
"nNumKeys" with the following explanation "Number of keys to be used. If ON then the request is valid for caching." It tells me that you can not determine which fields do you use when you use the index partially, just how many fields from the beginning the index. Since I suppose Table I/O ER statements will be compiled (interpreted as) C level JDBase API calls, OW can not parse correctly the ER statement for patial key updates when you do not define selection for the first fields of the index.
I do not really know that is it right or not. Maybe somebody C expert OW developer can confirm it or deny.

Regards,
Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 
Re: *** QUIZ *** F0911 \"Update\"

Hi Christian,

I recently found something about partial key update on the Knowledge Garden. Please, check the 5329016, 5322691 and 5329075 SARs on the KG.

It seems to me that SP17 will resolve this issue, although this is not explicitly described in the mentioned document.

How do you evaluate these SARs. I am curious to it.

Regards,
Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 
Re: *** QUIZ *** F0911 \"Update\"

The answer to your 2 question is yes.

About my partial key update they are still running at other client site and they are LIVE with it. But with this new issue, I think that I should evaluated the jdedebug.log and I will probably find strange SQL translation.

Maybe it's working because the wrong translation does not matter in there case, but in the future "I WILL BE VERY CAREFUL WITH IT". Do you know if it only apply to UPDATE, or you know simillar issue with other Table I/O.

I will check right now the SAR that you mention.

Thank !

Christian Audet

Implementing B7333 (Xe) SP14.1, SQL
(Support B732, B7331 and B7332)
 
Re: *** QUIZ *** F0911 \"Update\"

Hi Christian,

If my memory serves me well, I have never used partial key updates, only partial key deletes but that times always very carefully, making selection for a contiguous range of fields starting from the beginning of the index. I was always afraid of using partial keys.

About the SARs. I wasn't able to determine when, namely from which release or SP level is this problem exist and exactly what or which will correct it. Were you able to determine?

Regards,
Zoltán



B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 
Re: *** QUIZ *** F0911 \"Update\"

Excuse me. I forgot to add that the status of the mentioned SARs is Completed.

Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 
Re: *** QUIZ *** F0911 \"Update\"

Zoltan,

The three SAR you mention seems to describe part of my problem. unfortunatly I'm not waiting for SP17, so in the futur I will always build a new index starting with the required field. I don't want stupid SQL translation anymore !!

About the update that you are asking me to test :

You said :
"Here is a suggestion:
Try to define something dummy selection for the fields in the index before Batch Number which will really select ALL and won't be exclude anything. e.g.
evt_ICUT = " "
evt_ICU = 1178
F0911.Update
evt_ICUT >= TK ICUT
evt_ICU = TK ICU
Please, let us know the results."


I have tried it but the problem is that a UPDATE statement in table I/O you can't use ">=" only "=", Good try !

Christian Audet

Implementing B7333 (Xe) SP14.1, SQL
(Support B732, B7331 and B7332)
 
Re: *** QUIZ *** F0911 \"Update\"

Forgive my ignorance here - I'm not a JDE developer, but I do have substantial database programming experience.

What is all this talk about "partial key updates"??

From a database standpoint, I've never heard of Where clauses having to be limited to the defined indexes on the table. Generally speaking, the following SQL would be perfectly valid:

UPDATE Customers SET ZipCode='55555' WHERE City='Tulsa'

regardless of whether or not City was defined in ANY key or in what position in those keys. The issue would be only one of database efficiency, as it is always in where clauses.

Now, of course, we're talking about a situation where we want to update one OR MORE records that match those criteria. If we're wanting to update only a specific record, the only legit Where clause is one using the primary index.

Are we talking strictly about a JDE limitation...er, I mean feature, because this conversation doesn't make sense to me from a dba standpoint??

---------------------------------
OneWorld Xe SP15
Clustered Windows 2000 + SQL 2000
 
Re: *** QUIZ *** F0911 \"Update\"

Hi Daniel,

You are totally right from database standpoint.
Yes, we are talking strictly about a JDE limitation and OneWorld is an other animal.

I try to describe the Update statement of OneWorld ER:

1.) If you want to issue an update then you HAVE TO select one index of the table.

2.) You will get a "mapping table" for the table in the update statement where the fields of the selected index will be marked with an asterisk.

3.) If you map something to an index field then it will mean automatically a selection criteria and only the EUQUAL is valid.

4.) If you map something to a non-index field then it will mean automatically an assignment (SET).

Summarized: You can define selection only for the fields of a selected index, you can update only the fields which are not part of the selected index.

You can see, SQL is much more free to define an update than OneWorld ER.

Thank you for your update, remarks.

Zoltán


B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 
Re: *** QUIZ *** F0911 \"Update\"

Hi Daniel,

I'm not a DBA, but YES from a database point of view your point make sense !

The problem here with JDE developement is that when we use the JDE language NER (Named Event Rules) we don't have the nice option to use SQL Language Like SELECT and UPDATE. Table I/O from NER is one of the option to SELECT or UPDATE from SQL. Table I/O is then interpreted into native JDE language and it's seem that JDE have trouble to translate "partial key" update.

From a JDE developer point of view a "partial key" update is a update where you are not using all the key to update the table, I know in SQL it's not mandatory, but it's seem that in One World, the Rules is : "You need to use a key entirely or if use are using it only partially, use contiguous field starting from the first one in the key"

So that is all about this discussion !

I hope you now have a better understanding of the limitation that we have under JDE developement.

Christian Audet

Implementing B7333 (Xe) SP14.1, SQL
(Support B732, B7331 and B7332)
 
Re: *** QUIZ *** F0911 \"Update\"

Slight correction to my prevous statement... the only legit where clause to update only one record would be one using a UNIQUE index, not necessarily the primary one...

now having said that... let me see if I have this straight. If I wanted to update a group of records I have to perform a query to get the records, then loop through them and update them, rather than issue an update statement that updates the group of records in one swoop. Man, that is horribly inefficient!

---------------------------------
OneWorld Xe SP15
Clustered Windows 2000 + SQL 2000
 
Re: *** QUIZ *** F0911 \"Update\"

Daniel,

Correction : We are able to update a group of record in NER using only one line of code. It's just that we have the limitation to use all the field in the key starting from the first field.

Example : one of the key for table F4211 is (KCOO, DOCO, DCTO, LNID)

We are able to update the table using (KCOO, DOCO, DCTO) or (KCOO and DOCO) but not DOCO only and this is just because of a "OneWorld translator limitation".

Of course most of those error can be fix using new index on the field of your query.

Hope this help to understand our point.

Christian Audet

Implementing B7333 (Xe) SP14.1, SQL
(Support B732, B7331 and B7332)
 
Re: *** QUIZ *** F0911 \"Update\"

Hi Daniel and Christian,

Daniel is right, updating a group of records one by one in a loop is not too efficient.

Also no too efficient solution (and not too sphisticated) to create a new index for it, mainly on a huge table like F0911. Everybody knows, it has preformance disadvantage on all Update, Insert, Delete Table I/O and of course requires extra storage space too.

Zoltán

B7332 SP11, ESU 4116422, Intel NT4, SQL 7 SP1
(working with B7321, B7331, XE too)
 
RE: *** QUIZ *** F0911 \"Update\"

I think you will find the original thinking behind the approach is that
OneWorld is Database independent. It has to be able to generate Update code
for a number of different databases. The origins of OneWorld can be found in
World which runs on the AS400, so just as the Database structure is based on
the original World File structure, so the File I/O procedures have evolved
from Flat file manipulations, rather than

OW733.3 Xe SP 14.2
Enterprise Server - Intel NT + Oracle 8.0.6
Client - Citrix TSE + 4 NT PC's for development
 
Re: RE: *** QUIZ *** F0911 \"Update\"

The AS400 database is not flat files, but is a SQL relational database.
JDE World is a relational database, very similar to the OneWorld database, but OneWorld has diverged somewhat, because it is able to make better use of the relational structure in some cases.
A very long time ago, JDE ran on S/36's, which use a hierarchical database.
All modern versions of JDE software run only on SQL relational databases.

Programming wise, there is almost nothing in common between World and OneWorld software. JDE did not run a slimer or conversion over the RPG code to generate OneWorld. The structure of the logic, and the methods of IO has very little in common. It is possible to see the influence of RPG programmers on some of the names given to certain processes though. Even so, you still do a FETCHSINGLE, not a CHAIN, a FETCH, not a SETLL, and a FETCHNEXT, not a READE.

I think you'll find the limitations imposed by OneWorld are more to do with C programming and passing data structure pointers, and with attempting to build in enforcements of performance considerations, than with any legacy file manipulation system.
 
RE: RE: *** QUIZ *** F0911 \"Update\"

Hello list:

I have a question that is in regard to the post on the evolution of JDE
for S/36 to Xe.

My Platform is AS/400 V4R4 (Georgia and Arkansas -- see below).

We just converted our operation in Georgia from O/W to Xe. Now the
SAVCHGOBJ does not pick up much of anything on the nightly backup. For
instance the F0911 on a WRKOBJ shows no changes (Georgia's Xe only).
However the DSPFD shows records were changed within seconds of the
execution of the DSPFD F0911 *MBRLIST.

It looks as if Xe is using outboard processes (i.e. ODBC or whatever) to
perform the maintenance and since a process access group (PAG) is _not_
being used the WRKOBJ does not show any changes being made. Therefore
the SAVCHGOBJ fails to include the F0911 in the nightly backup.

This is what is happening and what the AS/400 is reporting in Georgia
with Xe.

The SAVCHGOBJ worked with O/W in Georgia before the upgrade and World
Vision in Arkansas.

Since this is a developers forum, I assume someone running AS/400's has
encountered this issue -- however, if I am in the wrong place, where
should I post this question?

TIA (Thanks in advance)

Fred

JDE Applications Programmer
Alltel Information Services
501 220-5387





Fred Short
Senior Systems Engineer
Alltel Information Services
World Vison 7.3
One World B7.33
 
Back
Top