SQL Statements

The EnterpriseOne SQL Statements category will include all posts and pages pertaining to SQL statements that can be used in the administration and configuration of EnterpriseOne.

ESU Installation Comparison Using SQL

oracle-sql-developer

If your JDE EnterpriseOne installation has been installed for more than… oh, 2 weeks… your pathcodes have obviously gotten out-of-sync. Ok, maybe it’s not that bad, but DV and PY can become much different than PD faster than a three-legged fox in a hen house. Is that fast? I don’t know, but it doesn’t take long. There are many reasons as to why the pathcodes get out-of-sync:

  • Testing an ESU without cleaning up after it didn’t resolve the issue
  • Forgetting about an ESU that was installed in DV or PY
  • Developers being slow to do retro-fits

Now, Developers, don’t get upset. I love developers. They provide job security for CNC Admins! Now come on… I’m just hackin’ on ya.

You can use SQL Developer to connect to the E1Local database.

The SQL I used to compare the ESU installations between pathcodes is below. This example tells us which ESUs are in PY910 but not in PD910. The SDSUDET=’90’ indicates that the ESU has been installed in that pathcode.

Any other quick and dirty SQLs that you use to report on ESUs? Please leave them in the comments below.

How To Start SQLPlus From The Linux Command Line

Run the following commands from the Linux command line to start SQLPlus:

 

 

Pause/Resume E1 Scheduler with SQL

Below are SQL statements that can be used to Pause and Resume the E1 Job Scheduler:

 

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?