E1 Tips

As some may have noticed, the latest version of FireFox breaks Oracle JDEdwards EnterpriseOne. 

Well, we found a fix on JDEList posted by cpb (Charles Baker):

Add the following to JAS server browscap.ini:

;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; MOZILLA FIREFOX 20.0

[MOZILLA FIREFOX 20.0]
parent=MOZILLA FIREFOX 10.0
version=20.0
majorver=20
minorver=0

[*(X11*) Gecko/* Firefox/2?.*]
parent=MOZILLA FIREFOX 20.0
platform=Linux

[*(Mac*) Gecko/* Firefox/2?.*]
parent=MOZILLA FIREFOX 20.0
platform=Mac

[*(Win*) Gecko/* Firefox/2?.*]
parent=MOZILLA FIREFOX 20.0
platform=Win 

You will want to make this change in the browscap.ini file located in your Server Manager Agent target directory, which may be something like this:

E:\jde_home\SCFHA\targets\pd83-1\owl_deployment\webclient.ear\app\webclient.war\WEB-INF\classes

On his blog, Shannon Moir has a great post, ”Simple JDE BSFN to send an email with an attachment”, about how to send email attachments using a custom Business Function (BSFN).

From the post:

It’s a BSFN that takes a few parameters:

• to
• From
• file to attach
• name of attachment
• subject
• Body
• CC
• BCC

The source code can be found here.

Great post, Shannon!  Thanks!

I’ve been moving several EnterpriseOne databases to SQL Server lately.  In order to do a quick check to make sure that all the data was transferred, I like to compare the number of rows on all the tables.  Below are the SQL queries that I use to get a row count on all tables in a database and all files in a library:

iSeries – SQL query to get a row count on all files in an iSeries library:

 SELECT TABLE_SCHEMA, TABLE_NAME,NUMBER_ROWS 
FROM QSYS2.SYSTABLESTAT 
WHERE TABLE_SCHEMA = 'PRODDTA' 
AND NUMBER_ROWS > 0 

SQL Server – SQL query to get a row count on all tables in an SQL Server database:
  
SELECT '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' AS fulltable_name,
SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name,i.rows 
FROM sys.tables AS t 
INNER JOIN sys.sysindexes AS i ON t.object_id = i.id 
AND i.indid < 2 
WHERE i.rows > 0 


NOTE: the iSeries SQL query is easiest to do from System i Navigator because you can easily copy the records into Excel.
Are there any methods that you use to do a quick data integrity check?

I’ve been doing a lot of work with SQL Server lately and have found the following SQL that can be used to drop (delete) all tables in an SQL Server database:

  EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"  

Do you have any really handy SQLs?  Post them in the comments.

We have noticed many orphaned subsystem jobs piling up in the F986113.

In tracking them down we found that the way we stop the subsystem jobs each night for things like backups, nightly processing, etc. may have been causing jobs to just sit there and not be processed. The process that was in place just deleted the subsystem processing job records from the F986113.

I did some research and found that an article on JDEList.com says to essentially use SQL to set the subsystem processing jobs to an “ERROR” state.

That didn’t seem quite right to me either. So, I set out to find out what the application does to end the subsystem jobs and found that it inserts a record into the F986113 that triggers the job to end.

Below is the that can be used to end the subsystem jobs gracefully:

 insert into svm900.f986113 (SSPID,SSVERS,SSSBMDATE,SSSBMTIME,SSJOBSTS,SSOPCR,SSENHV,SSJOBNBR,SSUSER,SSORGHOST,SSJOBPTY,SSEXEHOST)                                                        select SSPID,SSVERS,SSSBMDATE,SSSBMTIME+1,'R','W',SSENHV,SSJOBNBR,'JDEPKG',SSORGHOST,'0',SSEXEHOST FROM SVM900.F986113 WHERE SSPID='R43500' 

For a while now we have had an issue where our subsystem Purchase Order Print job (R43500) will not run in JPD900 but it will in PD900. After going through the debug log I found a something:

--UBE--[0]-- SS0000009:Subsystem Limit is reached. 
--UBE--[0]-- SS:Cannot start new subsystem.

Above this notification I found an SQL was ran looking for subsystem jobs in the F986113:

SELECT COUNT(*) 
FROM SVM900/F986113 
WHERE ( SSPID = R43500 AND SSVERS = MOD0009 AND SSENHV = JPD900 
AND SSEXEHOST = MODENT AND SSJOBSTS = S )

The results of this SQL was 3. Which is the UBE Subsystem Limit specified in the Enterprise Servers JDE.INI [UBE] section (Configurations – Batch Processing from Oracle Server Manager). 3 is also the default value.
However, there is a problem with this SQL querie. It only finds the number of subsystem jobs that have been submitted. It doesnt check to see if they are running. If all subsystem jobs have been stopped properly, there would be zero records returned from this SQL. However, if you have had any issues with subsystem jobs or even if things dont get cleaned up correctly when bouncing services, you can end up with subsystem jobs in an error state.
The only way to clean up these records is to go into the database and remove them:

DELETE FROM SVM900/F986113 
WHERE (SSPID = R43500 AND SSVERS = MOD0009 AND SSENHV = JPD900 
AND SSEXEHOST = MODENT AND SSJOBSTS = S AND SSOPCR=E)

Do you have any tips for SubSystem jobs? If so, please leave them in the comments.

When building a package for a new 9.1 installation, I noticed the following errors in the jde.log:

7032/7768 WRK:Starting jdeCallObject Wed Feb 27 06:26:15.517000 SpecPkgBld.c4280 
SPC0000502 - Could not determine database type for data source. Data source is probably invalid. (Datasource: Local - PLANNER Specs) 

7032/7768 WRK:Starting jdeCallObject Wed Feb 27 06:26:15.517001 SpecOpen.c2633 
jdeSpecOpenLocalOpt completed in error - JDESPECRESULT_FAILED.

That doesn’t really seem right. How could the “Local – PLANNER Specs” datasource be “invalid”? Well, not to worry. Here is what I found on Oracle Support:

E1: PKG: JDE.LOG Error Messages When Building Packages – SPC0000502 – Could Not Determine Database Type For Data Source [ID 651676.1]

These messages do NOT affect the package build process. 
Development is aware and some SARs as Bug  8293709 has been entered to clean up the messages in the jde.log. This seems to be caused due to a timing issue during datasource cache load and refresh. This is always reported during package build. Package Build creates and drops internal datasources during the build process. When a new datasource(DS) is created, it calls JDB to refresh DS cache. At around the same time,  Busbuild process is loading the DS cache – and it fails to get the DS type.
However, the jde.log messages are harmless and can be ignored.

Just more scary error messages that can be ignored :-/

The other day, we were trying to refresh a data library on the iSeries and were unable to remove several journal files (*JRN and *JRNRCV).  I knew that I written about how to start journaling but wasn’t sure how to stop/delete them.

So, I found the following iSeries command that ends the journal receiver:

ENDJRNPF FILE(*ALL) JRN(LIBRARY/FILE)

After running this command, we were able to then delete the objects.

I found this command at mrc’s Tech Blog: Removing AS400 Journals and Journal Receivers

Product Code: H90 – EnterpriseOne TOOLS
User Defined Codes: FP – ActivEra FastPath

You can use the below SQL to add an EnterpriseOne environment to every role or group in your installation:

INSERT INTO SY910/F0093 (LLUSER,LLLL,LLSEQ,LLMNI)
SELECT ULUSER, 'PD910','1.00','' FROM SY910/F0092