ESU Cleanup

Since our installations of EnterpriseOne were installed and the data upgraded we have applied and installed thousands of ESUs. On 4 of the 7, we neglected to apply the ESUs to the Pristine pathcode (PS900/PS910). This made researching code changes a bit of a challenge.

So, the other day, I decided to rectify that and apply the many, many ESUs to Pristine. Because there were some very old ESUs and not all of them were registered with Change Assistant, it was a very time consuming. After I had finished, I noticed that there were several ESUs that had only been applied to PS & PD and others that had only been applied to PS, DV & PY. Rather than look through every ESU and risk missing some, I connected to E1LOCAL and wrote a SQL statement to get every ESU that had been applied to the system that was not in every pathcode.

Please see “Use Oracle SQL Developer To Modify The JDE LOCAL Database” for connection instructions.

SELECT * FROM (
	SELECT A.SDPKGNAME AS ESUNAME, 
		A.SDSUDFUT2 AS SYS, 
		PS.SDSUDFUT2 AS PS910, 
		DV.SDSUDFUT2 AS DV910, 
		PY.SDSUDFUT2 AS PY910, 
		PD.SDSUDFUT2 AS PD910 
	FROM F9671 A
	LEFT JOIN F9671 PS 
		ON A.SDPKGNAME = PS.SDPKGNAME 
		AND PS.SDSUDFUT2='PS910' 
		AND PS.SDSUDET = '90'
	LEFT JOIN F9671 DV 
		ON A.SDPKGNAME = DV.SDPKGNAME 
		AND DV.SDSUDFUT2='DV910' 
		AND DV.SDSUDET = '90'
	LEFT JOIN F9671 PY 
		ON A.SDPKGNAME = PY.SDPKGNAME 
		AND PY.SDSUDFUT2='PY910' 
		AND PY.SDSUDET = '90'
	LEFT JOIN F9671 PD 
		ON A.SDPKGNAME = PD.SDPKGNAME 
		AND PD.SDSUDFUT2='PD910' 
		AND PD.SDSUDET = '90'
	WHERE A.SDSUDFUT2='ALL' 
		AND A.SDSUDET ='10'
	ORDER BY A.SDPKGNAME DESC
)
WHERE PS910 IS NULL
	OR DV910 IS NULL
	OR PY910 IS NULL
	OR PD910 IS NULL

2 Ways To Prevent Java Crapware: Ask Toolbar

How-To-Geek has a great article on how to prevent the Java installer from installing the Ask Toolbar and other crapware.

Below are the 2 methods:

  1. Using the Java Control Panel: This method is only available if you already have java installed and want to avoid accidentally installing its crapware when you update it.
    1. Press the [Windows] key
    2. type “java”
    3. Click “Configure Java”
    4. At the bottom of the “Advanced” tab, put a check in the “Suppress sponsor offers when installing or updating Java” option.
       image thumb 2 Ways To Prevent Java Crapware: Ask Toolbar
  2. Use the registry to set the option even before Java is installed.
    1. Open notepad
    2. Paste the following into a new document
       Windows Registry Editor Version 5.00  [HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft] “SPONSORS”=”DISABLE”  [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\JavaSoft] “SPONSORS”=”DISABLE” 
    3. Save the document with a .reg extension
    4. Double-Click the new file

How To Get A Disk Usage Report From IBM iSeries

Below are the steps that I used to get a disk usage report from our IBM iSeries which we used to find and track the large files in our JDE EnterpriseOne data library:

  1. Type: GO DISKTASKS and press [Enter]. Then choose option 1 in the screen below:
    image thumb How To Get A Disk Usage Report From IBM iSeries
  2. Choose option 1 for when to collect the information:
    image thumb1 How To Get A Disk Usage Report From IBM iSeries
  3. On the next screen type in *CURRENT in both the Date and Time fields and press [Enter].
    image thumb2 How To Get A Disk Usage Report From IBM iSeries
  4. Press [F12] twice to get out of the Collect Disk Space Information screen.
  5. Do a WRKACTJOB and find a job similar to the one highlighted below:
    image thumb3 How To Get A Disk Usage Report From IBM iSeries
  6. The job may run for several hours depending on the configuration of your system.
  7. When the job above has finished, go to the Disk Space Tasks menu again by typing GO DISKTASKS. This time, choose option 2: Print disk space information.
  8. On the next screen choose “Library” by putting a 1 in the field as in the image below:
    image thumb4 How To Get A Disk Usage Report From IBM iSeries
  9. Enter a “Y” in the next field to indicate that you want to include info about objects in libraries.  Then press [Enter].
  10.   Fill in the library name that you would like to get information about as in the image below:
    image thumb5 How To Get A Disk Usage Report From IBM iSeries
  11.    Finally, find the report using System i Navigator and drag it to your desktop:
     image thumb6 How To Get A Disk Usage Report From IBM iSeries

I hope that helps you find the large objects in a library on an IBM iSeries.

Reference: IBM GO DISKTASKS Overview

How to Increase Your JD Edwards Performance with the New UI on 9.1.4

Gustavo Barizon is a great guy and a very knowledgeable CNC Admin.  He works for cd|group and is hosting a webinar on Friday.  I suggest you check it out.

Join CD Group for this JD Edwards Users Webinar to learn how to use the latest and greatest enhancements from JD Edwards’ User Interface 9.1.4.  In less than one hour, you will see how to: 

  • Increase your performance using CafeOne and E1 Pages 
  • Take advantage of Watchlists to pro-actively engage your processes 
  • Use the context-sensitive help to get insights on your procedures 

You will also  be able to see the iPad app in action and learn about what’s new in mobility solutions for JD Edwards. Don’t miss this opportunity to increase your knowledge about JD Edwards! 

Title: How to increase your JD Edwards performance with the new UI on 9.1.4
Date: Friday, September 12, 2014
Time: 12:00 PM – 1:00 PM EDT 
Speaker: Gustavo Barizon

Space is limited.
Register for this webinar now.

Quick SQL To List E1 Roles With Description

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

SELECT AUUSER, AUROLEDESC FROM SY900.F00926

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:

USE JDE_PY910
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.

 
ECHO OFF
for /F "tokens=3 delims=: " %%H in ('sc query "Spooler" ^| findstr "        STATE"') do (
ECHO STATE = %%H  
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?