About Me

My photo
I work for Fusion5 Australia . Connect with me on linked in here . I'm raising money for a good cause at the moment, follow donate to leukemia foundation

Wednesday, 4 December 2013

JD Edwards performance testing using Oracle Application Testing Suite

I thought that I’d provide some very generic guidelines for performance testing and JD Edwards 9.1.  These figures can be used as a guide for your systems performance.  If course, these tests and scenarios are very limited.

I have a scenario, 1 web server, 1 DB server, 1 app server.  I log into E1, I launch AB, index search 1 record, close AB, open BV, run R0010P, close BV logout close browser.

I create a load test using Oracle Application Test Suite.

I configure all of my counters

I configure 20 concurrent users (to being with)

I choose to have 0 wait time, so I’m perf testing.

I run this scenario for 10 minutes.

What do you think the results might be? 

Name Value Min Max Avg
Transaction/s .827 .082 2.579 2.272
Pages/s 8.8 .667 26.2 22.68

image

 

image

 

So these are very interesting stats.  Each user is getting 1 page per second, Each user is getting 10 hits per second.  Each user is generating 58KB/s.

I find the final item interesting, as this allows you to know exactly how much a very fast moving user is using on average for E1 – this is 0 wait time, so a user could not actually achieve this (none that I know).

Like I said, this is a 1 off very simple sample.

Oracle Application Testing Suite is fantastic and is exceptionally easy to use.  Although, what you do not have (and I do) is the ability to compare values between sites and know where you can make considerable performance improvements!

Wednesday, 27 November 2013

Oracle procedures and calling them from a BSFN

We are doing some pretty edgy stuff at the moment.  We are getting JD Edwards to call oracle procedures via OCI calls in kernels.  This is some nice stuff to have complete commitment control over function and procedure calls from within E1.

We are working on all of the syntax and semantic issues at the moment.

At the end of the day we are going to have a generic BSFN that is going to take 3 parameters:

procedurename

parameters in (pipe delimited)

parameters out (pipe delimited)

Therefore, the BSFN can be nice and generic and send a bunch of parameters to the stored proc, it’ll extract them all with it’s beautiful PL/SQL logic and hand any returns back when it is done – nice!

Note that this will be timed out if there is no response to the BSFN in 60 seconds, which will be no good for interactive apps

create or replace Procedure TESTDTA.ProcTest
(procName IN varchar2, environ IN varchar2, paramList IN varchar2, resultsList OUT varchar2)
AS
  nextID number;
BEGIN
DBMS_OUTPUT.PUT_LINE('In Procedure');
SELECT testdta.ProcedureSeq.nextval INTO NextID FROM DUAL ;
insert into TESTDTA.ProcedureTest values (ProcTest.procName, ProcTest.environ, ProcTest.paramList, CAST(nextID as varchar(20) ) );
END;

Run it with the following:

declare
  ReturnVal  varchar(20);
begin
  TESTDTA.ProcTest('This is procedure name', 'DV910', 'This|Is|999|params', ReturnVal);
  DBMS_OUTPUT.ENABLE(1000000);
  dbms_output.put_line(ReturnVal) ;
END ;

Check that things get inserted. 

select * from testdta.ProcedureTEst;

image

Nice, all is working. Note that the procedure needed to be created in the same schema as the sequence and the table.  If I did not create the procedure in the same owner / schema – it was unable to find the sequence or the table even when I explicitly referenced them.

So, this is perfect.  I have a working procedure in my TESTDTA owner – now I have to code the OCI routines to call the procedure.  There are two ways of doing this:  OCI (nice, neat secure) or system out a call to a vbscript (not nice, neat or secure – but quick).  I’m going with OCI.

OCI is going to be slightly painful, for a number of reasons.  I need to use 32bit OCI libraries and I also need to do “loadlib” calls and function pointers – as the oci libs are not going to be in my lib path when compiling BSFN’s on the server (or the client).  This is somewhat painful.  I’ll get it all working standalone and then build my function pointers into a BSFN.

This might be a longer post than usual, so I’ll do it in parts.

Thursday, 21 November 2013

Promoting E1Pages not working–Work with LCM activity Rules

This is a really quick post, because I know that I’m going to forget it again.  If you are having issues promoting e1pages using P982400, you need to ensure that you have all of the security set up correctly in P982405.  This application controls who can promote e1pages (or user defined content) from one status to another.

  • Pending Review
  • Rework
  • Pending Approval
  • Approved

The above are sample from and to status that you control with this app.

Remember also that these tables are stored in the central objects data source, so any environment associated with the pathcode will see the e1page

clip_image002

Desktop BIP Installation

Getting issues with 64 bit / 32 bit combinations of office and windows?  This is based on 64 bit windows 7 and 32 bit office 2010, yes it can work!  The tricky bit is getting the version of java right for the installer.

Preparation

Close ALL office products (word, excel, powerpoint and email)

Assumption you are running 32 bit office and 64 bit windows

Download 64 bit java and install

http://java.com/en/download/manual.jsp This page, has the 64 bit download. Download and install this.

http://javadl.sun.com/webapps/download/AutoDL?BundleId=81821

clip_image002

Got control panel and type java, select it

clip_image004

Goto java tab, then view button

Ensure that a version in “Program files” is selected (this is the 64 bit location), not the version in “Program files (x86)

clip_image005

The above is 64 bit!

Install BIP desktop

Now run the bin installer \\aupdc00-jds01d\E910\BIPDesktop

Right click the exec and run as admin

clip_image006

clip_image007

Choose run

clip_image008

Ok

clip_image009

clip_image010

It’ll be done

clip_image012

Customising the login for JD Edwards 9.1 and above.

 

Change the page title

Edit this file:

D:\Oracle\Middleware\user_projects\domains\E1_Apps\servers\JWB01D_DVCMSAU_81\stage\JWB01D_DVCMSAU_81\app\webclient.war\share\Html4login.jsp

Change this

<title>"JDE CMS DV AU"</title>

<webgui:pagehead addtitle="false"/>

Note that this is really handy for favourites too!

clip_image002

Then change the icon’s and text – Just for the login page. Replace the oracle logo change the text from “JD Edwards EnterpriseOne”

Note that you need to create the oracle_logo2.png

File to edit:

D:\Oracle\Middleware\user_projects\domains\E1_Apps\servers\JWB01D_DVCMSAU_81\stage\JWB01D_DVCMSAU_81\app\webclient.war\share\loginDecoration.jspf

Changes:

if ( (companyLogoImageURL != null) && (companyLogoImageURL.length() > 0) || smallScreen )

{

if (companyLogoImageURL.length() != 0)

{

sb.append("<img src=\"");

sb.append(resourceUrlBuilder.createUrl("/share/images/rcux/oracle_logo2.png"));

sb.append("\" ALT=\"ORACLE &reg;\"");

sb.append(" width=\"219\" border=\"0\" height=\"75\">");

}

}

Then also change

sb.append("</td>");

sb.append("<td width=\"100%\" class=\"loginValignTop topbody bottomborder\">");

sb.append("<span id=\"appName\" style=\"position: absolute; top: 25px; left: 220px;\" class=\"appname\"\" > xxxx CMS Australia Development </span>");

sb.append("</td>");

See I changed the height and the title for the login only. Note that the FLAG and the workd “CMS DEV” are part of the image oracle_logo2.png. The words “XXX CMS Australia Development” are the second change that is referenced above.

clip_image006

Job is done. You now have a modified login page and no others.

This can be seen immediately with a refresh. You need to make the changes on the server (of course)

Change colours of top navigation bar:

clip_image009

\\aupdc00-jwb01d\d$\Oracle\Middleware\user_projects\domains\E1_Apps\servers\JWB01D_DVCMSAU_81\stage\JWB01D_DVCMSAU_81\app\webclient.war\share\webguistylesheet.jsp

.topnavtable {

background-color: #006600;

padding-top: 0.2em;

height: 2.5em;

white-space: nowrap;

}

Wednesday, 13 November 2013

OMW Error Codes OMW project won't transfer

Ever wanted to know what that pesky OMW transfer error message code meant, here is the list of errors and what they mean in some meaningful text.

Make sure that you select the project, then the logging exit in OMW.  Then choose the "Transfer" entry and drill down to the object error messages.

Remember, if you get 47 your data sources are not set up in the release / data source map (fix with GH9011 -> environments menu)


   Transaction was successful 
   General error 
  Transaction not supported 
  SAR integration has been disabled 
   Object already present in project 
   Object not present in project 
   User already in project 
   User is not in project 
   Attempting to add invalid object to project 
   Attempting an unauthorized action 
10     Status activity rule is inactive 
11     Transfer Activity Rule is inactive 
12     Project name is user ID 
13     Project already exists 
14     Bad handle 
15     Could not initialize user 
16     Memory errors occurred 
17     Object creation errors occurred 
18     Invalid SAR number entered 
19     Can not create default project 
20     User canceled 
21     Project is non-existent 
22     Can not rename project 
23     Invalid status change attempted 
24     Invalid string entered 
25     Invalid type entered 
26     A required field is blank 
27     Invalid user id entered 
28     TAM errors have occurred 
29     RDB errors have occurred 
30     Parameter specified does not exist 
31     Token is available 
32     Token is not available 
33     Transfer rolled back per previous call 
34     Transfer failed to commit to rollback 
35     Transfer success reference decremented 
36     Transfer success commit rollback 
37     JITI fialed 
38     Start form dynamic failed 
39     Entry point not found 
40     Not licensed to access object 
41     Deadlock has occurred 
42     Object is currently checked out 
43     Object has already been transferred 
44     Locked by vocab overrides 
45     Not authorized to install 
46     TSE not authorized 
47     Transfer not compatible 
48     Column prefix is invalid 
49     Copy errors 
50     Invalid action for project type     
51     Project is at an invalid status 
52     Invalid object ID, project ID is a duplicate 
53     Invalid object ID, project ID is too long 
54     Invalid object ID, contains invalid characters 
55     Errors occurred while cleaning up specs 
56     Object does not exist 
57     Unknown object Librarian or Version data source
58     Could not initialize processing option data 
59     User security is not authorized 
60     Notification system is disabled 
61     SAR creation error 
62     Save location specified is not found 
63     No object to restore from  
64     There is another object in save location 
65     BSFN library in use 
66     Project already exists for SAR number specifed 
67     Role already assigned 
68     Can't remove user from SAR 
69     Token is inherited 
70     Linked project is at an inconsistent status 
71     Object already exists in an open project 
72     Object has not been checked out 
73     SAR status change error 
74     Inheritance status change error 
75     No Transfer Activity Rules exist for object 
76     Log Failure 
77     Objects are checked out 
78     Specified object is not checked out to this machine 
79     Objects are in token queue 
80     Source file does not exist 
81     Bad Transfer Activity Rule 
82     Queued for deletion 
83     Object marked for deletion 
84     Object has not been modified 
85     Mandatory rule exists  
86     Transfer Limitation exists  
87     Transfer Exception exists  
88     Attempting to transfer Version before Template 
89     Path Code release and SAR release incompatible  
90     Web Only Version Successful 
91     Invalid operation performed on Web Only Version 
92     Token not available for Web Only Version 

Monday, 11 November 2013

More on unicode conversion and JD Edwards

All of the heavy lifting is done in a stop red procedure, no matter what platform you login with.  This also shows that JD Edwards has the ability to call a stored proc in the middleware (well, that is my assumption).

So this is pretty handy, you can work out the variables by reading the code and then you can just call this with all of the tips and tricks you want!  Forcing parallelism might be a good start!


CREATE OR REPLACE PROCEDURE "JDEDBA"."OWTBLCONV" (szUseridIn    in  varchar2,
                                      szPasswdIn    in  varchar2,
                                      szCatalogIn   in  varchar2,
                                      szOwnerIn     in  varchar2,
                                      szTableNameIn in  varchar2,
                                      nFlag         in  integer,
                                      szPathIn      in  varchar2
                                      /* OLEDB has problems with out varchar fields. Temporarily comment this out,
                                      szReturnDesc out varchar2*/)
       authid current_user
is
  OW_SP_SUCCESS   constant integer := 0;
  OW_SP_ERROR     constant integer := -20101;
  TAB_NOT_EXIST   constant integer := -942;

  szOwner         varchar2(30);
  szTableName     varchar2(30);

  type ColCurTyp is ref cursor;
  tab_col_info    ColCurTyp;

  col_name        varchar2(30);
  col_type        varchar2(106);
  col_length      number;
  char_length     number;
  data_precision  number;

  sql_stmt        varchar2(10000):= null;
  insert_sql_stmt varchar2(4000) := null;
  select_sql_stmt varchar2(4000) := null;
  old_tab_name    varchar2(30)   := UPPER(szTableNameIn) || '_NONUNI';
  notFirstTime       boolean     := false;
  nCharCols          integer     := 0;

  tablespace      varchar2(30);
  initextent      number;
  nextextent      number;
  pctincrease     number;

  retcode         integer;
  errMsg          varchar2(512);
  szReturnDesc    varchar2(512);

begin
  if (szOwnerIn = null) or (szTableNameIn = null) then
     szReturnDesc := 'Error - Either table owner or name is null';
    RAISE_APPLICATION_ERROR(OW_SP_ERROR, szReturnDesc);
  end if;

  szReturnDesc := 'Success';
  szOwner      := UPPER(szOwnerIn);
  szTableName  := UPPER(szTableNameIn);

  begin
    SELECT TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, PCT_INCREASE INTO tablespace, initextent, nextextent, pctincrease FROM SYS.ALL_TABLES WHERE OWNER = UPPER(szOwnerIn) AND TABLE_NAME = UPPER(szTableNameIn);
  exception
    when no_data_found then
      dbms_output.put_line('Error: Table ' || szOwner || '.' || szTableName || ' does not exist');
      szReturnDesc := 'Error - Table ' || szOwner || '.' || szTableName || ' does not exist';
      RAISE_APPLICATION_ERROR(OW_SP_ERROR, szReturnDesc);
    when others then
      szReturnDesc := 'Error - ' || sqlerrm;
      RAISE_APPLICATION_ERROR(OW_SP_ERROR, szReturnDesc);
  end;

  SELECT COUNT(*) INTO nCharCols FROM SYS.ALL_TAB_COLUMNS WHERE OWNER = UPPER(szOwnerIn) AND TABLE_NAME = UPPER(szTableNameIn) AND DATA_TYPE IN ('CHAR', 'VARCHAR2', 'CLOB', 'LONG');
  if nCharCols = 0 then
   
    szReturnDesc := 'Success - No conversion needed for table ' || szOwner || '.' || szTableName;
    return;
  end if;

  sql_stmt := 'ALTER SESSION SET NLS_NCHAR_CONV_EXCP=TRUE';
  execute immediate sql_stmt;

  begin
    sql_stmt := 'DROP TABLE ' || szOwner || '.' || old_tab_name;
    execute immediate sql_stmt;
  exception
    when others then

      if (sqlcode != TAB_NOT_EXIST) then
        szReturnDesc := 'Error - ' || sqlerrm;
        RAISE_APPLICATION_ERROR(OW_SP_ERROR, szReturnDesc);
      end if;
  end;

  sql_stmt := 'ALTER TABLE ' || szOwner || '.' || szTableName || ' RENAME TO ' || old_tab_name;

  execute immediate sql_stmt;

  sql_stmt := 'SELECT COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, CHAR_LENGTH FROM SYS.ALL_TAB_COLUMNS WHERE OWNER = :1 AND TABLE_NAME = :2 ORDER BY COLUMN_ID';

  open tab_col_info for sql_stmt using szOwner, old_tab_name;

  sql_stmt := 'CREATE TABLE ' || szOwner || '.' || szTableName || '(';

  insert_sql_stmt := 'INSERT INTO ' || szOwner || '.' || szTableName || '(';
  select_sql_stmt := ' SELECT ';
   
  loop
    fetch tab_col_info into col_name, col_type, col_length, data_precision, char_length;
    exit when tab_col_info%notfound;

    if (notFirstTime = true) then
      sql_stmt := sql_stmt || ', ';
      insert_sql_stmt := insert_sql_stmt || ', ';
      select_sql_stmt := select_sql_stmt || ', ';
    end if;

    case col_type
      when 'CHAR' then col_type := 'NCHAR';
      when 'VARCHAR2' then col_type := 'NVARCHAR2';
      when 'CLOB' then col_type := 'NCLOB';
      else
        null;
    end case;

    if (col_type = 'LONG') then
      sql_stmt := sql_stmt || col_name || ' ' || 'NCLOB';
    else
      sql_stmt := sql_stmt || col_name || ' ' || col_type;
    end if;
    insert_sql_stmt := insert_sql_stmt || col_name;

    if (col_type = 'NCHAR') or (col_type = 'NVARCHAR2') then
      sql_stmt := sql_stmt || '(' || char_length || ')';
      select_sql_stmt := select_sql_stmt || 'TO_NCHAR(' || col_name || ')';
    elsif (col_type = 'NCLOB') then
     
      select_sql_stmt := select_sql_stmt || 'TO_NCLOB(' || col_name || ')';
    elsif (data_precision is not null) and (col_type = 'NUMBER') then
      sql_stmt := sql_stmt || '(' || data_precision || ')';
      select_sql_stmt := select_sql_stmt || col_name;
    elsif (col_type = 'LONG') then
      select_sql_stmt := select_sql_stmt || 'TO_LOB(' || col_name || ')';
    else
      select_sql_stmt := select_sql_stmt || col_name;
    end if;

    notFirstTime := true;
  end loop;

  close tab_col_info;

  sql_stmt := sql_stmt || ')';
  if tablespace is not null then
    sql_stmt := sql_stmt || ' TABLESPACE ' || tablespace;
  end if;
  sql_stmt := sql_stmt || ' STORAGE (';
  if initextent >= 1000 then
    sql_stmt := sql_stmt || 'INITIAL ' || initextent;
  end if;
  if nextextent >= 1000 then
    sql_stmt := sql_stmt || ' NEXT ' || nextextent;
  end if;
  if pctincrease >= 0 then
    sql_stmt := sql_stmt || ' PCTINCREASE ' || pctincrease || ')';
  else
    sql_stmt := sql_stmt || ' PCTINCREASE 0)';
  end if;

  insert_sql_stmt := insert_sql_stmt || ')';

  execute immediate sql_stmt;

  sql_stmt := 'GRANT ALL ON ' || szOwner || '.' || szTableName || ' TO PUBLIC';
  execute immediate sql_stmt;

  sql_stmt := 'ALTER TABLE ' || szOwner || '.' || szTableName || ' NOLOGGING';
  execute immediate sql_stmt;
  sql_stmt := 'ALTER TABLE ' || szOwner || '.' || old_tab_name || ' NOLOGGING';
  execute immediate sql_stmt;

  select_sql_stmt := select_sql_stmt || ' FROM ' || szOwner || '.' || old_tab_name;

  execute immediate insert_sql_stmt || select_sql_stmt;

  retcode := jde_unicode_create_index(szOwner, old_tab_name, szOwner, szTableName, errMsg);
  if retcode = OW_SP_ERROR then
   
    szReturnDesc := errMsg;
    return;
  end if;
  if errMsg is not null then
    szReturnDesc := szReturnDesc || ' - ' || errMsg;
  end if;

  commit;

  sql_stmt := 'ALTER TABLE ' || szOwner || '.' || szTableName || ' LOGGING';
  execute immediate sql_stmt;

 
  sql_stmt := 'DROP TABLE ' || szOwner || '.' || old_tab_name;
  execute immediate sql_stmt;

  return;
exception
  when others then
    dbms_output.put_line('Error: ' || sqlerrm);
    szReturnDesc := 'Error - ' || sqlerrm;
    RAISE_APPLICATION_ERROR(OW_SP_ERROR, szReturnDesc);
end owtblconv;

Simple UDC sync when upgrading. Merge F0005 and F0004

For a multitude of reasons I have issues when doing upgrades and ensuring all of the UDC's from PS910 have been merged into my custom ones.  Generally I run a script to identify and then insert any UDC's from PS910 into my upgraded environment.  Please see the following SQL that will add any F0004 or F0005 records from PS if they are not in your target schemas:

In my example DVFINCTL has just been upgraded and is missing some records.

insert into DVFINCTL.F0004
select * from ps910ctl.f0004 t1
where not exists
(select 1 from DVFINCTL.f0004 t2
where t1.dtsy = t2.dtsy
and t1.dtrt = t2.dtrt);
commit ;

insert into DVFINCTL.F0005
select * from ps910ctl.f0005 t1
where not exists
(select 1 from DVFINCTL.f0005 t2
where t1.drsy = t2.drsy
and t1.drrt = t2.drrt
and t1.drky = t2.drky);
commit;

Another method I use for this is with IMP and EXP and oracle.  You could exp the contents of PS to a file and then import with ROWS_ONLY over the top of your other environment.  Oh oracle, you give me so many ways to skin my cat!


Saturday, 9 November 2013

P93091 unicode conversion in JD Edwards

P93091 is pretty much all you need to know.

The top button builds a list of all "Non unicode" data sources in the system.  This list is based on the "System" datasource in your ini file, therefore if you run it from the deployment server, it'll use the planner F98611.  This might not be what you want.  This run R930811, I run it locally.  It actually won't run in DEP / planner - so I recommend using a normal fat client.

The second button allows you to work with the results of the first.  Despite the confusing interface, you need to double click a row (till the green tick appears) and then tick the "build information" tick box too.  Once this is done and you select OK the job starts processing. R930912 runs and will hopefully build your list of tables to convert.  The list is called UniDataConv.xml in your install dir in a dir called script.  Note that this report works off sys tables / all_objects and not OL to list the tables, as you'll see tables mentioned that are not in OL.

If there are ANY errors in the data source that you specify, none of the tables will be written to the uniDataConv file, so you need to make sure that it's pretty clean.

Wow, it's hard to cut and paste the XML and see the actual characters...

I'll upload the file somewhere  https://drive.google.com/file/d/0B30UFGvbR-EjNEt3d001MXRmZWM/edit?usp=sharing













F964001


F96401


F96402


F9640S


F96411


F96450


F9649


F96491


F96492


F9660


F980011


F980014


F980021


F9801NEW


F9801OLD


F98231D


F98231H


F98301


F983061


F98610


F9880


F9890


F98TC002


F98TC01


F98TC03


F98TCDTL


F98TCHDR


F99102D


F99RDDS


FF31112T


FF31113T


FF3112ST


FF3113ST








Business Data - TEST








You then need to run UnicodeDataConv.exe  from your bin32 dir on the fatty that has the file in the script dir.

Thursday, 24 October 2013

When deciding on a port for a JAS instance, consider this

I generally use something like 81 - DV, 82 - PY, 83 - UA etc.  or put a 9081, 9082 etc.

It starts to get a little more complicated with BSSV and SSL, but not too much.  Leave at least 1 port between your instances if you are using SSL.

Here is a handy list of default oracle ports that you can also avoid:
Component or ServiceDefault Port NumberAllotted Port Range
Oracle Business Activity Monitoring90019000-9080
Oracle Directory Integration Platform70057005-9000
Oracle Directory Services Manager70057005-9000
Oracle Forms Services Managed Server90019001-9100
Oracle HTTP Server non-SSL Listen Port77777777-7877
Oracle HTTP Server SSL Listen Port44434443-4543
Oracle Imaging and Process Management1600016000
Oracle Identity Federation Server Managed Server74997499-9000
Oracle Internet Directory (non-SSL)30603061 to 3070,
13060 to 13070
Oracle Internet Directory (SSL)31313132 to 3141,
13131 to 13141
Oracle Management Agent (used by Fusion Middleware Control)51625162-6162
Oracle Notification Server Local Port61006100 - 6199
Oracle Notification Server Remote Port62006200 - 6299
Oracle Notification Server Request Port60036003 - 6099
Oracle Portal Managed Server90019001-9100
Oracle Reports Managed Server90019001-9100
Oracle Virtual Directory (non-SSL)65016501-6510
Oracle Virtual Directory (SSL)75017501-7510
Oracle Web Cache Administration Port77867781-7790
Oracle Web Cache Invalidation Port77887781-7790
Oracle Web Cache Listen Port77857781-7790
Oracle Web Cache SSL Listen Port77897781-7790
Oracle Web Cache Statistics Port77877781-7790
Oracle WebCenter Discussions Server88908881-8890
Oracle WebCenter Portlets88898881-8890
Oracle WebCenter Spaces88888881-8890
Oracle WebCenter Wiki and Blog Server88908881-8890
Oracle WebLogic Server Listen Port for Administration Server70017001-9000
Oracle WebLogic Server Listen Port for Managed Server80018000 - 8080
Oracle WebLogic Server Node Manager Port55565556
Oracle WebLogic Server SSL Listen Port for Administration Server70027002-9000

Monday, 14 October 2013

JD Edwards scheduled jobs table demystification

A fairly quick post.  Ever wanted to list the JD Edwards scheduled jobs, when they start, recurrence type, etc etc...  It's not as easy as you think.  There are lots of things going on in that F91300 that need to be decrypted.

Here is something that helped me:

Dump them all into excel, that is a good start.

The following can be used to decipher the recurrence type: (SJSCHRCRTYP)

01
Custom Recurrence
11
Daily - Mode 1
12
Daily - Mode 2
21
Weekly - Mode 1
31
Monthly - Mode 1
32
Monthly - Mode 2
41
Period - Mode 1
42
Period - Mode 2
51
Yearly - Mode 1
52
Yearly - Mode 2


Here are some excel formlas to help you convert the EPOCH (of course bespoke JDE version, which is in minutes) to standard date and time.

time scheduled (UTC)
JDE Vers x 60
start time (UTC)
With DST start time
17964814
1077888840
1:34:00 PM
11:34:00 PM
17964817
1077889020
1:37:00 PM
11:37:00 PM
17964933
1077895980
3:33:00 PM
1:33:00 AM
17964934
1077896040
3:34:00 PM
1:34:00 AM
17964935
1077896100
3:35:00 PM
1:35:00 AM
17964813
+I7*60
=+(J7/86400)+25569
=+K7+TIME(10,,)


The formulas are in the last row.

The combination of the recurrence type, the start time and the table below (showing you weekday / day of month runtime options will help.

Monthly will have a value for the day of the month in SJSCHDAY
Weekly look for the columns that are populated (SJTUESDAY etc)

Here are the table columns

SJSCHJBTYP                              NCHAR(2)                                                                                                                                                                                      
SJSCHJBNM                      NOT NULL NCHAR(30)                                                                                                                                                                                     
SJSCHRPTNM                              NCHAR(10)                                                                                                                                                                                     
SJSCHVER                                NCHAR(10)                                                                                                                                                                                     
SJPROCNAME                              NCHAR(10)                                                                                                                                                                                     
SJSCHSTTIME                             NUMBER                                                                                                                                                                                        
SJSCHENTIME                             NUMBER                                                                                                                                                                                        
SJNUMJOBOCC                             NUMBER                                                                                                                                                                                        
SJCURRUNCNT                             NUMBER                                                                                                                                                                                        
SJMAXRESUB                              NUMBER(15)                                                                                                                                                                                    
SJSCHJBSTAT                             NCHAR(2)                                                                                                                                                                                      
SJSCHENHV                               NCHAR(10)                                                                                                                                                                                     
SJSCHJOBSVR                             NCHAR(30)                                                                                                                                                                                     
SJSCHUSER                               NCHAR(10)                                                                                                                                                                                     
SJSCHPSWD                               NCHAR(10)                                                                                                                                                                                     
SJSCHROLE                               NCHAR(15)                                                                                                                                                                                     
SJJDELOG                                NCHAR(1)                                                                                                                                                                                      
SJTRACING                               NCHAR(1)                                                                                                                                                                                      
SJUBELOGLVL                             NCHAR(1)                                                                                                                                                                                      
SJSCHPI                                 NCHAR(1)                                                                                                                                                                                      
SJPNTR                                  NCHAR(30)                                                                                                                                                                                     
SJJOBQUE                                NCHAR(10)                                                                                                                                                                                     
SJRESBONERR                             NCHAR(2)                                                                                                                                                                                      
SJRESBTIME                              NUMBER                                                                                                                                                                                        
SJINPRTMOTPY                            NCHAR(2)                                                                                                                                                                                      
SJINPRMAX                               NUMBER                                                                                                                                                                                        
SJINPRTMOUT                             NUMBER                                                                                                                                                                                        
SJJOBEXPIRE                             NUMBER                                                                                                                                                                                        
SJSCHRCRTYP                             NCHAR(2)                                                                                                                                                                                      
SJCO                                    NCHAR(5)                                                                                                                                                                                      
SJSCHNUMMNS                             NUMBER                                                                                                                                                                                        
SJSCHNUMHRS                             NUMBER                                                                                                                                                                                        
SJSCHNUMDY                              NUMBER                                                                                                                                                                                        
SJSCHNUMWKS                             NUMBER                                                                                                                                                                                        
SJSCHNUMMN                              NUMBER                                                                                                                                                                                        
SJSCHDAY                                NCHAR(1)                                                                                                                                                                                      
SJWEEKDAY                               NCHAR(1)                                                                                                                                                                                      
SJWEEKEND                               NCHAR(1)                                                                                                                                                                                      
SJMONDAY                                NCHAR(1)                                                                                                                                                                                      
SJTUESDAY                               NCHAR(1)                                                                                                                                                                                      
SJWEDNESDAY                             NCHAR(1)                                                                                                                                                                                      
SJTHURSDAY                              NCHAR(1)                                                                                                                                                                                      
SJFRIDAY                                NCHAR(1)                                                                                                                                                                                      
SJSATURDAY                              NCHAR(1)                                                                                                                                                                                      
SJSUNDAY                                NCHAR(1)                                                                                                                                                                                      
SJTIMEZONES                             NCHAR(2)                                                                                                                                                                                      
SJDAYLIGHTSV                            NCHAR(1)                                                                                                                                                                                      
SJDSAVNAME                              NCHAR(10)                                                                                                                                                                                     
SJSCHCTCD01                             NCHAR(6)                                                                                                                                                                                      
SJSCHCTCD02                             NCHAR(6)                                                                                                                                                                                      
SJSCHCTCD03                             NCHAR(6)                                                                                                                                                                                      
SJSCHCTCD04                             NCHAR(6)                                                                                                                                                                                      
SJSCHCTCD05                             NCHAR(6)                                                                                                                                                                                      
SJSCHFU1                                NUMBER                                                                                                                                                                                        
SJSCHFU2                                NUMBER                                                                                                                                                                                        
SJSCHFU3                                NCHAR(30)                                                                                                                                                                                     
SJSCHFU4                                NCHAR(30)                                                                                                                                                                                     
SJUSER                                  NCHAR(10)                                                                                                                                                                                     
SJPID                                   NCHAR(10)                                                                                                                                                                                     
SJJOBN                                  NCHAR(10)                                                                                                                                                                                     
SJUPMJ                                  NUMBER(6)                                                                                                                                                                                     
SJUPMT                                  NUMBER                                                                                                                                                                                        
SJSCHPWD                                BLOB()