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.

Examining and Generating JD Edwards EnterpriseOne Indexes

sql ninja image - EnterpriseOne Indexes

Within the last week, Shannon Moir has had 2 great posts about EnterpriseOne indexes that have motivated me to evaluate our database:

Both are very good and extremely useful. I had to do a little bit of adjusting and add a where clause or two for our instance but they worked great.

This is what I my modified code looks like:

select TRIM(TPOBNM), JDEINDEX from 
(select tpobnm, trim(tpobnm) || '_' || tpinid  as jdeindex from PD900.f98712 where tpuniq <> 1
	and not exists
		(select 1 from all_indexes where owner = 'PRODDTA' and trim(tpobnm) || '_' || tpinid = index_name) and exists
			(select 1 from all_Tables where owner = 'PRODDTA' and table_name = trim(tpobnm))
union
select tpobnm, trim(tpobnm) || '_PK'  as jdeindex from PD900.f98712 where tpuniq = 1
	and not exists
		(select 1 from all_constraints where owner = 'PRODDTA' and trim(tpobnm) || '_PK' = constraint_name) and exists
			(select 1 from all_Tables where owner = 'PRODDTA' and table_name = trim(tpobnm))
)
order by 2 asc;


select * from all_indexes where status <> 'VALID';

The following steps are what I did to make sure my EnterpriseOne indexes and OMW specs were in sync:

  1. Running the SQLs above to get a list of tables that need to be examined.
  2. Verify the specs in OMW.
  3. Check the number of records in the table – You don’t want to impact user experience trying to make things better.
  4. If there weren’t too many records, I generated the indexes using OMW.

Yes, I know Shannon did things a little cleaner using all SQL, but my SQL-Ninja qualifications are not where his are.

R9698713 – EnterpriseOne Index Compare and Regeneration

R9698713 Processing Options - EnterpriseOne IndexesAfter getting halfway through my process, I remembered a handy, dandy UBE that does some of this for you: R9698713.

The R9698713 has the following Processing Options:

  1. Environment Name
  2. or Data Source Name
  3. Enter ‘0’ for Proof mode, ‘1’ to recreate ALL the indexes on the selected tables if there are ANY missing or mismatch indexes or ‘2’ to create only the missing and mismatch indexes on selected tables.
    • NOTE: your best options here are either ‘0’ or ‘2’. ‘1’ is very dangerous!
  4. Summary report or list all tables

Be sure to run it in proof mode to evaluate what it will be doing. Also, be sure to check the record counts in the tables that it plans to write the indexes over.

P95150 – EnterpriseOne Index Selection Tool

The P95150 application allows you to drop single or multiple EnterpriseOne indexes at a time. However, it only allows you to drop indexes that are not unique or primary keys.

The following doc explains how to drop and regenerate EnterpriseOne indexes using the P95150:  http://docs.oracle.com/cd/E14688_01/e1tools898pbr1/eng/psbooks/1tsa/chapter.htm?File=1tsa/htm/1tsa09.htm

Any tips or tricks when it comes to EnterpriseOne Indexes?

Submit a Tip or Trick

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 its 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 dont know, but it doesnt take long. There are many reasons as to why the pathcodes get out-of-sync:

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

Now, Developers, dont get upset. I love developers. They provide job security for CNC Admins! Now come on Im 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.

select sdpkgname as ESUNAME from f9671
where sdsudfut2='PY910' and sdsudet = '90'
minus
select sdpkgname as ESUNAME from f9671
where sdsudfut2='PD910' and sdsudet = '90'

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:

export ORACLE_HOME=/path/to/install/dir
export PATH=$PATH:$ORACLE_HOME/bin
sqlplus

 

 

Pause/Resume E1 Scheduler with SQL

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

--*** PAUSE SCHEDULER ***
update sy900.f91300 set SJSCHCTCD01='011' where sjschjbnm='*SCHEDULER'

--*** RESUME SCHEDULER ***
update sy900.f91300 set SJSCHCTCD01='111' where sjschjbnm='*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.

SELECT 
JCJOBQUE as JobQueue,
JCENHV as Environment,
JCUSER as User,
substr(jcfndfuf2,0,locate('_',jcfndfuf2) +
   locate('_',substr(JCFNDFUF2,posstr(jcfndfuf2,'_')+1))) as JobName,
   date(char(JCSBMDATE+1900000)) as SubmitDate,
   SUBSTR(RIGHT('0' || right(repeat('0',6) || JCSBMTIME,6), 6), 1, 2) || ':' ||
      SUBSTR(RIGHT(right(repeat('0',6) || JCSBMTIME,6), 4), 1, 2) || ':' ||
      RIGHT(right(repeat('0',6) || JCSBMTIME,6), 2) as SubmitTime,
   date(char(JCACTDATE+1900000)) as EndDate,
   SUBSTR(RIGHT('0' || right(repeat('0',6) || JCACTTIME,6), 6), 1, 2) || ':' ||
      SUBSTR(RIGHT(right(repeat('0',6) || JCACTTIME,6), 4), 1, 2) || ':' ||
      RIGHT(right(repeat('0',6) || JCACTTIME,6), 2) as EndTime,
timestampdiff(2,char(
   timestamp(date(char(JCACTDATE+1900000)),
      TIME(SUBSTR(RIGHT('0' || right(repeat('0',6) || JCACTTIME,6), 6), 1, 2) || ':' ||
         SUBSTR(RIGHT(right(repeat('0',6) || JCACTTIME,6), 4), 1, 2) || ':' ||
         RIGHT(right(repeat('0',6) || JCACTTIME,6), 2))
   )-
   timestamp(date(char(JCSBMDATE+1900000)),
      TIME(SUBSTR(RIGHT('0' || right(repeat('0',6) || JCSBMTIME,6), 6), 1, 2) || ':' ||
         SUBSTR(RIGHT(right(repeat('0',6) || JCSBMTIME,6), 4), 1, 2) || ':' ||
         RIGHT(right(repeat('0',6) || JCSBMTIME,6), 2))
   ))
) AS RUNTIME_SEC
FROM SVM812.F986110
WHERE JCSBMDATE>116121
ORDER BY 9 DESC

Here is the one for SQL Server.

Have fun!