Re: LTPUR not recalculated after changing ITEMBL with SQL (yet more advice)

Dear Dale and everyone else,

Thank you for your reply. I've gained a lot of knowledge this week! Everything kind of went per my plan. I was cautious, I did think about the fields I was updating, I did the update in test, and I was smart enough to query the MAPICS-L for input on my approach!

For what it's worth, we'll be on XAR9 by the end of this quarter. In fact, I'm doing another trial upgrade this Sunday from XAR5.5 to XAR6 to XAR9. It will be our six or seventh trial run.

What prompted me to learn SQL? I always see our affiliates using it! Going back about 15 years ago when I worked with Michael on the "Beloit" project, to now, when the affiliate helping us with the XAR9 implementation uses it for troubleshooting.

Have a nice day.

Bryan




On , Dale Gindlesperger <dale_gindlesperger@xxxxxxxxx> wrote:

Hello Bryan,

Well, you've had the advisory feedback on the hazards of using SQL to directly update XA databases, and they are all absolutely correct (Kevin and Michael are excellent resources for this thread). And you really should look very, very hard at upgrading to the latest level. That's far and away the best option - R9 capabilities and the price/performance levels on the new POWER systems are beyond belief based on the old V5.5 days.

But as we all know, that's not always decided at the technical level. So here's some advice for you at your level from someone that was stuck at a lower level of XA for far too long....

1. Look very hard at using the V5.5 Off-line loads for mass updates. That's an assured safe way of doing it (after all, it's the "official MAPICS way"), and it's not very difficult to do. It's not just for initial loads, but can be used for maintenance also. Data can easily be maintained in Excel or Access and uploaded if desired (I can help you with that if you wish), or can be created on the AS/400 outside of XA. The biggest drawback is that a lot of the offline loads require a dedicated XA environment to run (or at least there many, many conflicts). You can run a pre-edit check to make sure all your updates are correct. You own it, it's guaranteed safe, and while it can be a little awkward, it works.

2. You can use SQL or other direct updates (I did it many times over the years) but you have to be EXTREMELY cautious, and you should only use it on non-related fields, as in on fields that don't tie out to other fields or files. Things like item class, item description, user-defined fields, etc. are OK (usually) but not always. Otherwise, as Kevin and Michael put so well, you run the risk of seriously corrupting the databases. Some can be recovered, some will seriously hose you. As I said earlier, I did it a lot, but you have to be really careful. Actually, I frequently updated lead times as you did (using calculated values based on actual vs. expected delivery times), but I did a lot of research up front to make sure I updated all the related fields correctly, and tested it thoroughly in my test environment first. There's a lot of excellent information available in the manuals and on Infor's web site on how some of the values are

calculated, with actual field names.

3. Or (of course) you could ask your affiliate for assistance. Odds are that they have already helped someone else with similar things over time and they could save you some grief. And they might be able to help you convince your management that you really should upgrade.

Dale (Cork) Gindlesperger, CPIM

Click here for my professional profile on LinkedIn

"If I'd asked my customers what they wanted, they'd have said a faster horse." - Henry Ford

A Pessimist Sees The Difficulty in Every Opportunity;

An Optimist Sees The Opportunity in Every Difficulty;

An Analyst Sees How to Achieve the Opportunity!

>________________________________

> From: Bryan Burns bryanburns599@xxxxxxxxx>

>To: mapics-l@xxxxxxxxxxxx

>Sent: Monday, February 6, 2012 9:02 AM

>Subject: [MAPICS-L] LTPUR not recalculated after changing ITEMBL with SQL

>

>In an XA5.5 test environment, I updated LTVEN and LTSAF using SQL; but

>LTPUR has not been re-calculated for those items I updated.

>

>I went into ITEMBL file maintenace on a record and I was able to get it

>re-calculated that way.

>

>Two questions:

>

>1. This was my first accomplishment using SQL to do an update and I'd like

>to know if I did any damage, and what other file/fields can be damaged with

>even the most careful use of SQL?

>

>2. How can I automagically get the LTPUR recalculated for about 130 records?