EnterpriseOne – SQLs

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

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

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!

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
GO

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

How To Redeploy A Package To A Development Client (Fat Client)

I needed to redeploy a full package and all the update packages associated with the pathcode.  So, I used the SQL below to do the job:

 update sy900.f98825 set upinpkst=20 where upmkey='<DEV CLIENT NAME>' and uppathcd='<PATHCODE>' and upjdepkgnm <> '<PACKAGE NAME>' 

Cool Data Selection When Purging Work Submitted Jobs

Normally, we purge out all records from the Work Submitted Jobs screen that are at least 45 days old.

However, one of our clients requested to have all versions of R43510 purged after only one day.

While setting up the R9861101 data selection, I found that the Literal –> Range of Values method worked in a interesting way.  I was able to enter the following range on the F986110|RNDFUF2 (Foundation – Future Use 2):

R5543500_MRI0000_0000_PDF - R5543500_MRI9999_9999_PDF

That is about the most robust string/integer range I’ve seen.

Then, I thought, “How the heck is this happening?  Is it some kind of special JDE E1 processing that is parsing through the characters and integers?” 

Well, as some of you already know, SQL has a “BETWEEN” SQL operator

The SQL equivilant looks something like this:

select * from svm910.F986110<br>where JCFNDFUF2 between 'R5543500_MRI0000_0000_PDF' and 'R5543500_MRI9999_9999_PDF'

SQL To Get The Email Address From The Address Book Who’s Who For Each User Profile

Below is the SQL used to get the emails address from the Address Book Who’s Who (F01151) for each user profile (F0092):

 SELECT A.ULUSER,A.ULAN8, B.EAEMAL  FROM JDE910.SY910.F0092 A, JDE_PRODUCTION.PRODDTA.F01151 B  WHERE ULUGRP<>'*GROUP' AND B.EAAN8 IN (SELECT ABAN8 FROM TESTDTA.F0101 WHERE ABAT1='E') AND A.ULAN8=B.EAAN8 ORDER BY ULAN8 

Use Oracle SQL Developer To Modify The JDE LOCAL Database

During our upgrade process, we apply all of the ESUs to become “code current” before mirroring the upgraded code to the other pathcodes.

The process of doing this does not automatically update the Electronic Software Updates application to reflect that all of the ESUs are in all the pathcodes.

There a couple of different way to update the grid:

  1. Apply the ESUs to the other environments.  This can be a very long and tedious endeavor because there could be thousands of ESUs to apply.
  2. Update the JDE LOCAL database.

I have done option 1 in the past but found it kind of a pain and since I knew that I didn’t really want the process to really do anything except update the table, I chose to come up with a process to do option 2.

Here is what I did:

  1. Install and configure Oracle SQL Developer.  This is really the hardest part, but really not much of an issue.
  2. Create a connection to the JDESY910 database.  Remember, not only is JDESY910 the name of the database, but by default it is also the UserID and Password.
    image thumb Use Oracle SQL Developer To Modify The JDE LOCAL Database
  3. Run the following SQL
     INSERT INTO F9671 (SDPKGNAME,SDSUDET,SDSUDS,SDSUDATE, SDSUTIME,SDSUDFUT1,SDSUDFUT2,SDSUDFUT3,SDUSER,SDPID, SDUPMJ,SDUPMT,SDJOBN) SELECT SDPKGNAME,90,15,113284,111111,' ','DV910',0, 'JDE','SQL',113284,111111,'&lt;deployment name server&gt;' FROM F9671 WHERE SDSUDET=90 AND SDSUDFUT2='PD910' 
    • In this SQL, we upgraded PD910 and then moved the code to DV & PY.
    • We only need the “status 90” records because those are the ones that the grid uses.

Hope this helps.