Quick SQL To List E1 Roles With Description

Here’s a quick SQL to get a list of E1 Roles and their descriptions:


Automatically Convert EBCDIC To ASCII When Accessing A File From An iSeries Mapped Drive

When accessing log files located on an iSeries using a mapped drive the text remains in EBCDIC format.  However, by using iSeries Navigator and following the steps below, the file will be automatically converted to ASCII:

  1. Expand My Connections.
  2. Expand your connection name.
  3. Expand File Systems.
  4. Select File Shares.
  5. On the right side of the screen, right-click on the file share that will be converted.
  6. Select Properties.
  7. Select the Text Conversion tab.
  8. Select the option Allow file text conversion.
  9. Type .* (a period followed by asterisk) in the File extensions for automatic EBCDIC/ASCII text conversion: input area and click on the Add button.
  10. Click on the OK button to save the changes.

These steps can be found on IBM Document N1015165

The OCM QBE Ambush

Ok, I’ve been working as a CNC Admin for the past 14 years or so.  I’ve come across many odd and unexplainable things.  My first exposure was to JDE OneWorld Xe.  One of the most difficult issues during that time was the “fat” client hosted on Citrix and trying to control where things ran using OCMs.  So, I’ve had quite a bit of experience with the Object Configuration Manager (OCM) and how and why to set things up.

What I learned today is that I don’t know all there is to know about this seemingly simple function and the table behind it (F986101).

There is a setting on each OCM record that I have ignored in the past.  It is the “Allowed QBE” field:
 image thumb The OCM QBE Ambush

DO NOT IGNORE IT!  It will bite you when you least expect it.

There are 3 options to this setting:

  • 1 – ALL QBE Allowed
  • 2 – No QBE Allowed
  • 3 (DEFAULT) – Indexed Fields QBE Allowed

Unless there is a security requirement or performance issue, I would suggest changing all values to a 1.

You can do that with the SQL below:

 UPDATE SY910.F986101 SET OMOCM2='1' 

That right there my friends is why I have a love/hate relationship with my job as a JDE CNC Admin. The simplest little things from the least obvious place can either ruin or make your day.

How To List The Column Names Of A Table On SQL Server

We all know that most database tables in EnterpriseOne have a large number of columns. In fact, they usually push the limits of the RDBMS.

Well sometimes, you need a list of those columns for things like INSERT statements.

Below is how you can get the column names from a table in a SQL Server database:

SELECT [name] AS [Column Name]
FROM syscolumns
WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = 'F98950')

You can use either type V (views) or U (tables).

Check Windows Service Status And Restart If Necessary

Lately we have been noticing an issue where the Print Spooler service stops for some reason and users are unable to print UBE results through EnterpriseOne.  So far, we have been unable to narrow down a cause for the Spooler service to stop.

So, like any good CNC Admin, I put a band-aid on it.  I found a bat file script that I could use to check the status of the service and then restart the service it it wasn’t running. I then setup a scheduled task to run every 5 minutes.

for /F "tokens=3 delims=: " %%H in ('sc query "Spooler" ^| findstr "        STATE"') do (
if /I "%%H" NEQ "RUNNING" (
   REM Put your code you want to execute here
   REM For example, the following line
   net start "Spooler"
   ECHO Spooler has been restarted

Do you have any band-aids that you use to mitigate issues?

Unable To Check In OMW Object Or Promote OMW Project

Last week we had a developer that kept getting the following error when trying to check-in an object in OMW.  We also found that this error can happen when trying to promote a project:

saveGBRDataToConsolidated() - It could not be determined 
if the GBRSPEC record, e7426989-11ad-44f3-8697-7e7e89b4fbdd 
/ 1, exists in the repository (type = 2). Cannot insert. 

According to the Oracle support doc this error is caused by:

Corruption exists in the spec record(s) for the object related to the spec key shown in the jde.log.This could be related to an install, uninstall and then attempted reinstall of an ESU resulting in the “Insert to table F98741 failed” errors on the R98700 PDF.

There are 2 solutions given for this issue:

  1. To resolve this issue, back up the F98741 table first.  Then use SQL to remove the 198 record for EVSK:5ef15a1b-e064-11d1-9ac9-00600832fe19 from the F98741 table. Use Update Uninstall to remove all references to the ESU if it has not been applied to other environments.  See Note Document 654262.1 E1: ESU: Remove Software Updates (Update Uninstall vs. Update Clean-up) – for more details on removing ESUs.

    Delete the executable for the ESU.  Then download the ESU again.  Run the executable from the new download and apply the ESU through Software Updates application. This resolved the issue.

  2. To resolve this issue, check for the related records (F98740, F98741) for the GBRSPEC related to the spec key shown in the jde.log. Back up the table(s) where the spec records exist and delete the records to that spec key (in this case, a69a4b2e-1435-40e1-9df1-deeb8e88a605).  Reinstall the ESU to reapply the related object.

Our issue had nothing really to do with an ESU, so we opted to try solution #2.

It didn’t work… so it seemed.

We had to go through the following exercise 8+ times in order to get a successful check-in:

  1. Delete the record
    DELETE FROM DV910.F98740 WHERE ELEVSK='e7426989-11ad-44f3-8697-7e7e89b4fbdd'  
    DELETE FROM DV910.F98741 WHERE ESEVSK='e7426989-11ad-44f3-8697-7e7e89b4fbdd' 

  2. Try the check-in again
  3. Get a new GUID from the JDE.LOG

It’s not the most elegant solution, but hey, this is CNC!

Safely Delete Files From WINSXS

We started to run out of disk space on one of our production Windows Enterprise servers.  This usually isn’t a huge deal with virtual machines, however this was on the c drive.  Yes, I know those can be expanded too, however I wanted to do a little investigating before I just started dumping disk space to it.

I found that there were over 12 GB taken up by the c:\windows\winsxs directory.  I did some checking around the internet and found a blog post (Clean up Winsxs on Windows 2008 R2 after SP1 install) that provided me with a way to cleanup the WINSXS directory in a way that was supported by Microsoft:

DISM.exe /online /Cleanup-Image /spsuperseded

Now, it didn’t totally clear out the directory, but it did reduced it down to only 5 GB.

How To Find The Physical Size Of SQL Server Tables And Indexes

Inspired by Shannon’s post (give me the size of data and indexes for a file in an oracle database please), I found a SQL statement that finds the size of each table and index in a SQL Server database. I also found that there are a ton of different ways to get this information. Below is the method that I is most beneficial to me:

EXEC sp_spaceused

DECLARE @spaceUsed TABLE (
    name varchar(255), 
    rows int, 
    reserved varchar(50), 
    data varchar(50), 
    index_size varchar(50), 
    unused varchar(50))

INSERT INTO @spaceUsed
EXEC sp_MSforeachtable @command1="EXEC sp_spaceused '?'"

SELECT name, rows, left(reserved,len(reserved)-3) as [reserved (KB)], 
	left(data,len(data)-3) as [data (KB)], 
	left(index_size,len(index_size)-3) as [index_size (KB)], 
	left(unused,len(unused)-3) as [unused (KB)]
FROM @spaceUsed
order by rows desc

E1 Batch Approval Post Confusion

There is one very simple EnterpriseOne process that confuses me every stinking time I’m asked to configure it.  It’s setting up Batch Approval Post authority. 

My first frustration is that I think that this is NOT an IT function.  The business process owners know who should be able to approve who’s batches and it doesn’t have anything to do with the E1 security system. 

Ok, I’m done with that.  Now, on to the second frustration… What the heck is with the terminology used in this application?  To me, this is the consequences of early-adopter offshore development.  “Approved by Users”?  How about “Approvers”?  Wouldn’t that make way more sense?  Oh, and what about “Secured Users”?  Aren’t those just “Users” or “Batch Creators”?  Either way, I’ve never been able to setup this authorization correctly without looking at my notes that are 10+ years old.

So, here they are:

Create a new approver:

  1. Fastpath to P00241 (Batch Approval/Post Security Constants)
  2. Select the Form Exit “Approved by Users”
  3. Click the Add button: image thumb E1 Batch Approval Post Confusion
  4. Type the approver’s UserID in the Approved by Users field
  5. Type the batch creator’s UserID in the Secured User grid
    image1 E1 Batch Approval Post Confusion

Add a batch creator to the list that an approver can approve:

  1. Fastpath to P00241 (Batch Approval/Post Security Constants)
  2. Select the Form Exit “Approved by Users”
  3. Use the QBE to search for the approver
  4. Select the approver
  5. Add users to the “Secured User” grid

Hopefully, by putting it on here I won’t have to refer to my other set of notes.  At least for this issue.

Do you have anything in E1 that you just can’t get straight? 

I might look at Inclusive/Exclusive Row Security next.  What do you think should be my next post subject?

Oracle Killed JDEREF.COM

jderef.com (JDE Reference) was a great site used as a JDE EnterpriseOne table structure reference.  Unfortunately, the owner of the site received a notice from Oracle’s legal counsel asking him to shutdown the site.

Below is from the site:

Sorry, we’ve been closed down

It is with a heavy heart that I must inform you that jderef.com can no longer exist, having recently been served legal notice from Oracle regarding information copyright infringements relating to their product schemas. This is a bitter blow, not only for me personally, (having invested many hours creating this shared resource) but mostly for the users like you, who have started to depend on the presence of this resource, to use and enhance JDE and it’s satellite products – to further your knowledge and insight into Oracles flagship ERP products.

Personally, I’m of the opinion that this is a rather narrow sighted view adopted by Oracles Product Team given that;

  1. it’s supporting their product
  2. anyone with access to JDE will technically be able to obtain this information anyway, albeit in a far less convenient and expedient fashion
  3. any competitors will have long since already obtained this information

But as a private individual based in the UK – I have no choice but to concede – I don’t fancy going up against charges from Oracle’s legal teams. It was only a matter of time before my little website got the attention of Oracle – but this was not the response I was hoping for.

If you spread the word, make a little noise towards the “Legal Director/Managing Counsel (Law Practice) at Oracle Corporation” (Google it) – they may see how valuable this resource was for their users

Regards, the late jderef.com

Attn: Copyright Agent,  jderef.com

Pursuant to 17 USC 512(c)(3)(A), this communication serves as a statement that:
1.I am the duly authorized representative of Oracle Corporation and its affiliates ("Oracle"), the exclusive rights holder for the JD Edwards software including its schemas;
2.These exclusive rights are being violated by material available upon your site at jderef.com;
3.I have a good faith belief that the use of this material in such a fashion is not authorized by the copyright holder, the copyright holder's agent, or the law;
4.Under penalty of perjury in a United States court of law, I state that the information contained in this notification is accurate, and that I am authorized to act on the behalf of the exclusive rights holder for the material in question;
5.I may be contacted by the following methods:

T*** D***
Managing Counsel
500 Oracle Parkway
Redwood Shores, CA 94065

I hereby request that you remove or disable access to this material as it appears on your service in as expedient a fashion as possible. Thank you.