PSA: When WRKOBJ *all/MYTBL is not enough

​Bug Report (received at 5pm): customer is getting a message saying the
product they were trying to buy was a restricted product.

Check the tables involved, sure enough the customer was on the list as
authorized to buy the product. They shouldn't be seeing the message.

Test our order entry program, no issue.

Test our web site, no issue.

Report back to customer service that everything's fine. Next day, customer
says they got the same message again. Digging into deeper, I get the
invoice# and trace it back to the original order.

Turns out the the order came in direct from the customer's system via EDI.

What's puzzling is that the EDI orders get processed into our system by the
same set of programs that process web orders. In particular, the program
that loads the order into our order header/detail file is the one that
checks for authorization and is generating the message in question.

So, same process, works in one environment not the other. Sounds like a
library list issue of some sort. I quickly check the library list being
used by the EDI process. Looks fine. So I check for copies of the tables
or the program where they shouldn't be using

WRKOBJ *ALL/SPA* *FILE
WRKOBJ *ALL/OP021B *PGM
WRKOBJ *ALL/SPACHECK *PGM


Nope, nothing questionable.

I hand it over to the developer of the EDI process telling him something's
going wrong.

Couple of days later he's not found anything. So I get back involved.

Luckily (or unluckily), the authorization check is done using SQL. I start
digging into the SQL Plan cache and find a couple entries for the SQL
statement in question being run by his EDI process. The plan cache tells
me that the variables are being passed in correctly. Digging a little
deeper into the raw plan cache, I see that when the statement is being run,
it's accessing SPAMSTR, SPAITEM as expected. But instead of accessing
SPACUST, it's accessing a file named PRTGT.
I report this back to the developer, telling him that somewhere in his
process he must be overriding SPACUST to PRTGT. An hour and a half later,
he reports back that he can find no overrides.

A quick DSPFD on PRTGT provides the answer:
Alternative file name . . . . . . . . . . . . :
SPECIAL_PURCHASE_AUTHORIZATION_CUSTOMERS

Ah ha! So PRTGT has the same SQL name as SPACUST, which is why the SQL
statement is accessing it instead of SPACUST.

He's not sure why he created PRTGT back in February 2014; it hasn't changed
since then either. There's no references to it in his code and I know it's
not part of the rest of the system. So we delete it and all is right with
the world.

Moral of the story
- When looking for duplicates, WRKOBJ is not enough if you're using SQL
long names.
- SQL plan cache is pretty cool

One final lesson, allowing consultants to build processes outside your CMS
and with no oversight is a bad idea. Not that I didn't already know this.
Unfortunately, it was done prior to me coming back on board with the
company. I suppose I should have taken the time to bring his processes
into the CMS. But there's more than one, and I've had plenty of other
priorities. At least everything he's done for me has been within our CMS.

Charles