EnterpriseOne – SQLs

EnterpriseOne UBE Runtime Performance Audit For IBM iSeries

The following is a SQL query that I use to audit the performance of UBEs. Stick the results in a Excel spreadsheet and analyze to your hearts desire.

Here is the one for SQL Server.

Have fun!

Better Performance On Work with Payroll Transaction Ledger (P050999) With An Index On F0618

We have been getting complaints about the Work with Payroll Transaction Ledger (P050999) grid loading performance.  So, I decided to take a look.  What I found was that upon entering the application it does an automatic find on the grid using the following SQL statement with the current G/L date in the WHERE clause.

As you can see, this SQL statement is returning every record in the F0618 that has a G/L Date less than or equal to the system’s current G/L Date.  I don’t know about your installation but the one that I was working on had 17+ million records.  Why the heck would it need to return all that?

That screen was taking 8+ minutes to present information to the user.

With the help of my coworker Geordy and Technet, I came up with an index on the F0618 that didn’t make the SQL statement finish any quicker, but it does present the user the first page of data extremely fast (LESS THAN 3 SECONDS!).

You can see from the index that all I did was create an index over the field that the SQL was sorting on (YTDWK or Date of Work) and included the rest of the fields that were in the query.

There must be other little tricks with indexes in EnterpriseOne.  Have you found any?

 

Restore SQL Server Database On A Schedule

Keep Calm and AutomateOne of our companies has a training environment in addition to the standard PS,DV, PY & PD environments. They have been working very hard refining their Procure-To-Process (P2P). Now that they know how they would like to handle this process, they need to train the users. That’s where the P2P Training (PTR) environment comes in.

We setup the environment and have had everything running smoothly for the last few months. However, last week we received a request to backup the data in that environment and then restore that backup copy every morning for the next 2 weeks.

It’s a really simple process, but kind of a pain that I had to manually do the restore.

Well, if you’ve been reading this blog for very long, you know that I can’t stand doing things more than once. A trained monkey, I am not. If it can be automated, I do it and it usually makes sense to do so. This was definitely one of those times.

What I ended up doing was making 2 files:

  1. A bat file to call the sql file
  2. The sql file

Then, I just added a scheduled task that fired off the bat file every morning.

Note: When creating the scheduled task, you will need to fill in a value for the “Start in” option. I’m not sure why but it only works if you fill that in. I was using Windows Server 2008 R2 so your mileage may vary.

Scheduled Task Start In Option

How To Move SQL Server Data and Transaction Log Files

I’ve been migrating data from an IBM AS/400 to a SQL Server a lot lately. Within the next year or so we should have completed migrating 3 more instances. Then, we will have all 7 instances on SQL Server.

In preparing for the final data migration of one of the instances, I had to move the data and transaction log files. I found a great article for this on MSSQLTips.

Below are the basic steps.

  1. Use the following commands to get the names and location of the data and transaction log files
  2. Set the database to single user mode and detach it

  3. Move the files to their new location
  4. Re-attach the database with the files at their new location

EnterpriseOne UBE Runtime Performance Audit

Yesterday, I saw a post by Shannon Moir called “Nice UBE / Batch Performance Summary“. It was great! It provided some the information needed to really evaluate the runtimes of your UBEs.

Shannon is a wiz at SQL on an Oracle database. Unfortunately for me, we don’t use Oracle databases. So, I converted Shannon’s SQL so that I can use it on SQL Server.

Thanks again, Shannon!

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.

Quick SQL To List E1 Roles With Description

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

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:

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

  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!