Shannon's JD Edwards CNC Blog: October 2009

Thursday, 22 October 2009

JDE julian date to date in Excel

=DATE(IF(0+(LEFT(A1,2))<30,2000,1900)+MID(A1,3,1),1,RIGHT(A1,3))

whack 109023 into A1 and watch that bad boy go!

21CFR11 and v$session

Okay, so I guess this is probably another big lesson in “reading the documentation” – it bores me even typing that line!

We’ve got a problem enabling 21CRF11 in prod, it seems that it’s permissions to v$session for the data owner – which is the guy that runs the trigger. So proddta user needs permissions to V$SESSION. Cannot find much doco anywhere on this, but hey – this is what we needed last time. Note that the JDE user also needs CREATE PROCEDURE and CREATE ANY TRIGGER. Good article on how to grant access to v$session http://oraclespin.wordpress.com/2008/12/18/how-to-grant-access-to-vsession-to-other-users/

I’m pretty sure that this is going to sort things out, but because I don’t have SYS permissions, I cannot do this grant, even as JDEDBA.

One of the final triggers has the following errors:

ORA-00942 table or view does not exist line 15, same again for 31.

Of course these line numbers don’t mean anything either…

current time as JDETIME (UPMT) for oracle

jdetime is HHMMSS

so in oracle

select to_number(to_char(sysdate, 'HH24MISS')) from dual ;

Handy!!

DML / SQL when enabling auditing

I’ve enabled auditing many times before. Everytime has been a nightmare. You need lots of space for big tables, this could be done MUCH better with a rename statement! Anyway, I need to stop complaining… So, back to my story, auditing enabled fine in DV, fine in PY – go for PROD – ERROR… Or more accurately – warning…

For the last statement below, I get:

892/7740 WRK:Starting jdeCallObject            Tue Oct 20 20:36:04.351001    dbperfrq.c462
    OCI0000179 - Error - ORA-24344: success with compilation error
892/7740 WRK:Starting jdeCallObject            Tue Oct 20 20:36:04.366001    Jdb_drvm.c1116
    JDB9900401 - Failed to execute db request

Of course – NO MORE INFO. SO I have to run all of this manually and try and determine why it’s not working. show err at the command line after the final trigger create statement will show the problems in SQLPLUS.

CREATE TABLE PRODDTA.F0011_ADT (ICICUT CHAR(2), ICICU NUMBER, ICIST CHAR(1), ICIAPP CHAR(1), ICAICU NUMBER, ICUSER CHAR(10), ICDICJ NUMBER(6), ICNDO NUMBER, ICBAL CHAR(1), ICBALJ CHAR(1), ICAME NUMBER, ICDOCN NUMBER, ICAUSR CHAR(10), ICPOB CHAR(1), ICIBOI CHAR(1), ICAIPT NUMBER, ICOFFP CHAR(1), ICPID CHAR(10), ICJOBN CHAR(10), ICUPMJ NUMBER(6), ICUPMT NUMBER, ICDRSP CHAR(1), IC52PP CHAR(1), ICCBP CHAR(1)) tablespace proddtat storage ( pctincrease 0 ) ;

GRANT ALL ON PRODDTA.F0011_ADT TO PUBLIC ;

CREATE UNIQUE INDEX PRODDTA.F0011_ADT_0 ON PRODDTA.F0011_ADT (ICICUT ASC, ICICU ASC) tablespace proddtai storage ( pctincrease 0 ) ;

ALTER TABLE PRODDTA.F0011_ADT ADD CONSTRAINT F0011_ADT_PK PRIMARY KEY (ICICUT, ICICU) ;

INSERT INTO PRODDTA.F0011_ADT (ICICUT, ICICU, ICIST, ICIAPP, ICAICU, ICUSER, ICDICJ, ICNDO, ICBAL, ICBALJ, ICAME, ICDOCN, ICAUSR, ICPOB, ICIBOI, ICAIPT, ICOFFP, ICPID, ICJOBN, ICUPMJ, ICUPMT, ICDRSP, IC52PP, ICCBP) SELECT ICICUT, ICICU, ICIST, ICIAPP, ICAICU, ICUSER, ICDICJ, ICNDO, ICBAL, ICBALJ, ICAME, ICDOCN, ICAUSR, ICPOB, ICIBOI, ICAIPT, ICOFFP, ICPID, ICJOBN, ICUPMJ, ICUPMT, ICDRSP, IC52PP, ICCBP FROM PRODDTA.F0011 ;

SELECT OBJECT_NAME FROM SYS.ALL_OBJECTS WHERE ( OBJECT_NAME = 'F0011' AND OBJECT_TYPE = 'VIEW' AND OWNER = 'PRODDTA' ) ;

DROP TABLE PRODDTA.F0011 ;

ALTER TABLE PRODDTA.F0011_ADT ADD (ICCFRGUID CHAR(36) DEFAULT ' ' ) ;
ALTER TABLE PRODDTA.F0011_ADT ADD (ICCFRMKEY CHAR(15) DEFAULT ' ' ) ;
ALTER TABLE PRODDTA.F0011_ADT ADD (ICCFRPID CHAR(10) DEFAULT ' ' ) ;
ALTER TABLE PRODDTA.F0011_ADT ADD (ICCFRUSER CHAR(10) DEFAULT ' ' ) ;
ALTER TABLE PRODDTA.F0011_ADT ADD (ICISDELETE CHAR(1) DEFAULT ' ' ) ;
ALTER TABLE PRODDTA.F0011_ADT ADD (ICCFRSEQN NUMBER DEFAULT 0 ) ;

CREATE VIEW PRODDTA.F0011 AS SELECT ICICUT , ICICU , ICIST , ICIAPP , ICAICU , ICUSER , ICDICJ , ICNDO , ICBAL , ICBALJ , ICAME , ICDOCN , ICAUSR , ICPOB , ICIBOI , ICAIPT , ICOFFP , ICPID , ICJOBN , ICUPMJ , ICUPMT , ICDRSP , IC52PP , ICCBP FROM PRODDTA.F0011_ADT ;

GRANT ALL ON PRODDTA.F0011 TO PUBLIC ;

DROP PACKAGE PRODDTA.F0011_CFRA_PKG ;

create or replace package PRODDTA.F0011_ADT_CFRA_PKG
as
type ridArray is table of rowid index by binary_integer;
ids ridArray;
empty ridArray;
end ;

DROP TRIGGER PRODDTA.F0011_CFRA_RIA

-- ORACLE INSERT ROW BEFORE

create or replace trigger PRODDTA.F0011_CFRA_RIA
before insert on PRODDTA.F0011_ADT
for each row
declare
guid char(36);
user char(10);
workstation char(15);
programid varchar2(48);
audittype char(1) := '1';
begin
if (:new.ICCFRSEQN = 1) then
    -- JDB insert
    guid := :new.ICCFRGUID;
    if (guid = 'OW Without GUID') then
      audittype := '2';
      user := substr(ora_login_user, 1, 10);
      workstation := substr(sys_context('userenv', 'host'), 1, 15);
      select program into programid from v$session where audsid = userenv('sessionid') and user in (select user from dual);
      :new.ICCFRUSER := user;
      :new.ICCFRMKEY := workstation;
      :new.ICCFRPID := substr(programid, 1, 10);
    else
      audittype := '1';
      user := :new.ICCFRUSER;
      workstation := :new.ICCFRMKEY;
      programid := :new.ICCFRPID;
    end if;
else
    -- 3rd party insert
    audittype := '3';
    guid := 'Third Party';
    user := substr(ora_login_user, 1, 10);
    workstation := substr(sys_context('userenv', 'host'), 1, 15);
    select program into programid from v$session where audsid = userenv('sessionid') and user in (select user from dual);
    :new.ICCFRGUID := guid;
    :new.ICCFRUSER := user;
    :new.ICCFRMKEY := workstation;
    :new.ICCFRPID := substr(programid, 1, 10);
end if;

-- write audit record for insert
insert into PRODDTA.A0011 (ICCFRGUID, ICICUT ,ICICU , ICB4ORAFTR, ICCFRACTION,
    ICIST, ICUSER, ICDICJ,
    ICAUDITTYPE, ICCFRUSER, ICCFRMKEY, ICDATETIME, ICCFRPID) values
    (guid, :new.ICICUT ,:new.ICICU , 'A', '1',
    :new.ICIST, :new.ICUSER, :new.ICDICJ,
    audittype, user, workstation,
    to_char(sysdate,'YYYY-MM-DD HH24:MI:SS')||'.000000', substr(programid, 1, 10));
end;

Tuesday, 20 October 2009

metadata kernel restarts

We just had a problem where the metadata kernel stopped performing requests for UBEs. The UBE would be ‘P’ing (hehe), yet nothing was occurring. We could not see any “introspective” data either. After running a job with logging on, we saw the message:

Oct 20 13:44:29.323150 specopen.c2949 - 585730/1 MAIN_THREAD Spec Encapsulation UBE Job Number set. Job Number = 97900
Oct 20 13:44:29.323195 specmisc.c1669 - 585730/1 MAIN_THREAD Waiting for metadata kernel to finish cache loading for job 97900.

And that is where it would stay. I found the metatdata kernel pid and kill –9’d the sucker on the enterprise server. Another one started immediately. (I did actually test the process on the DEV machine before blazing away at prod – unlike me I know).

I also made a change to the JDE.INI on the ent server to increase the number of metaData kernels to 2. This was from a metalink article. This will not take affect until the next restart.

This actually occurred because the JDE account for out PP812 environment (shares prod end server) locked out. The metadata kernel seemed to get into a spin. The only jobs that were running where those that already had a “runtimeCache” directory.

Note that you can do the same to queue kernels, go into JDE and P986130 and choose “refresh queue” for any queue on the ent server that you smashed the queue kernel. This will restart it immediately and the business will not be the wiser! (note that this is E812 queues, where they are a single kernel).

Thursday, 15 October 2009

What I know about the transaction server

So I thought I’d write down some things about the transaction server that you might or might not know.

The JDE transaction server (for WAS) uses internal MQ queues for guaranteed message delivery. All of my experience at this stage has been publishing messages out of JDE with the use of RTEs.

The RTE messages are published out of JDE with the use of jdeIEO_EventAdd() and other functions. There are some tests in the code to see if the event is active, and if it is – start firing off the messages.

The RTE code sends a trigger message to the transaction server. The transaction server then does the SELECTs and INSERTs on the F90710 based upon the trigger messages that it receives. The Enterprise Server OCM’s (server maps) for service configuration define the port and host that the RTEs are sent to. This is the incoming jdenet port in the SM configuration, not the port that the txn server is installed on.

The outbound messages are defined per environment per machine, so there is some good flexibility there. Out of the box, the message queues are only 50000 deep – so you might want to increase them if your volumes are high.

Once the transaction server gets the trigger message, it puts the message into an event queue. I believe that the once there, the code is executed to retrieve the F90710 record. once this record is received and processed and deleted, the message is moved to a WQ_SubQueue. You can see all of this in action if you download, install and configure the SIB explorer.

Transaction server installs are easy BUT WATCH YOUR PORTS. Make sure that the incoming jdenet port is unique. Don’t use ports like 9080 –> 9090, these are all used by internal WAS processing. Ensure that you run netstat on the transaction server machine to ensure that the port is free.

WM will then connect to the install port of the txn server to get messages off the txn server.

Friday, 9 October 2009

Enhancement for JDE tools idea

For Tables within OMW, have a button for generate DML for tables or indexes. This would launch notepad or copy the DML into the clipboard so that the indexes could be generated with parallelism. Now that would be a cool thing!

I hate it when I have to turn logging on, generate a table in an alternate data source, just so I can extract the CREATE INDEX / CREATE TABLE statements from the logs…

This is also important, because there is some sort of assumption that I can just press “generate indexes” in OMW on a table with 40 million rows.

Another STUPID thing is that the index name assigned to the index in the database is nothing to do with what you see in TDA. Just plain stupid. There should be an identifier in TDA which tells us what name is going to be assigned to the index in the DB for manual reconciliation.

Thursday, 8 October 2009

8.98.2.0 Update 2 announcement

WebLogic is in – WOW
Choosa mandatory fields before you can hit find - WOW

For information on the JD Edward EnterpriseOne Tools releases including the tools release process, please refer to Document ID 653448.1 on My Oracle Support.

JD Edwards EnterpriseOne Tools 8.98.2.0 contains new product enhancements and additional platform certifications.

The new product enhancements in 8.98.2.0 are:

· Resource Management in Server Manager
A set of enhancements to the Server Manager tool which provides greater insight and diagnostics into the memory and CPU usage of E1 call object and UBE kernel processes. This enables system administrators to quickly identify processes with high resource consumption and reclaim those system resources to ensure system stability. It also decreases the time required for developers to identify the root cause of resource consumption issues.

· Application Query Security
A modification to the Security Workbench application and runtime platform that enables administrators to configure required field input when users are performing queries in interactive applications and data browser. The administrator can also configure this security by application and also choose to issue an error or warning when the user has not entered in the required fields.

· Automatic Generation of Tables and Indices in ESUs
A modification to the process for applying Electronic Software Updates (ESUs) to automatically generate new tables and indices that previously required manual special instructions.

The additional platform certifications consist of:

· Support for Oracle WebLogic Server 10.3.1 (Linux 5 64-bit)
Note: This is the initial certification of the Oracle WebLogic server with JD Edwards EnterpriseOne

· Support for Oracle Database 11g R2 (Linux 5 64-bit only)

· Support for Oracle Web Services Manager 10.1.3.3

· Support for Oracle Service Bus 10.3

· Support for IBM WebSphere Portal 6.1

· Support for Microsoft Visual Studio 2008 for development and package build processes

· Support for Microsoft Windows 7 for JDE E1 web client (Internet Explorer 8 only)

The 8.98.2.0 release also includes general maintenance, including fixes included in maintenance packs 8.98.1.1 through 8.98.1.5.

Refer to the document ID 747323.1 on My Oracle Support for specific details about supported platforms.

Monday, 5 October 2009

Server manager keeps crashing… why…

I looked and looked through metalink3, could not find a thing.

Searched for server manager crash, server manager stable, SM console – everything I could think of and came up with nothing.

Talked to colleague A (let’s call them Shae") who mentioned something from colleague B (let’s call them Brendan) that there is a blog entry from associate C (Let’s call them Clayton).

http://servermanager.blogspot.com/2008/01/sm-automatically-shutsdown-on-signout.html

Looks like this is the likely candidate. Now, can I reverse engineer a search string to get this out of metalink3 ??

Aaarrrrgggghhhh, first time. Search for “installManagementConsoleService.bat” and get 3 relevant articles. That REALLY sucks. I did a lot of searches and came up with nothing. If I could find my search history, you’d have a laugh.

Anyways, this looks like the link https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&id=659575.1

Not wanting to breach any copyright, the following is a suggested solution:

There are two methods to correct this issue. The first method is recommended since it does not involve editing the registry manually and it also ensures that the Server Manager install script gets modified so if it is rerun in the future, it will add the service correctly. Method 2, however, is likely the quickest work around.

Method 1:
1) Make the following change in the installManagementConsoleService.bat which is located in your JDE_HOME\bin directory of the Server Manager machine:

Change:
"--StartParams=-Xmx512m;-Djde.home=%JDE_HOME%;-jar;oc4j.jar"

to:
"--StartParams=-Xmx512m;-Xrs;-Djde.home=%JDE_HOME%;-jar;oc4j.jar"

(note the addition of -Xrs)

This change REQUIRES that -Xrs come just after -Xmx512m.

2) Ensure that the Server Manager service is currently stopped.

3) Open a command prompt, and go to your JDE_HOME\bin directory
Run:
uninstallManagementConsoleService.bat

4) After the service uninstalls successfully, run:
installManagementConsoleService.bat PASSWORD

where PASSWORD is your original jde_admin password.

5) Start the service. It should now remain running after you log out.

Method 2:
1) Open the registry editor

2) Locate the following registry key:
HKEY_LOCAL_MACHINE\SOFTWARE\Apache Software Foundation\Procrun 2.0\SCFMngmtConsole1\Parameters\Start

where SCFMngmtConsole1 is the last part of the display name of the service

Set the "Params" to:

-Xmx512m
-Xrs
-Djde.home=C:\jde_home
-jar
oc4j.jar

(note the addition of -Xrs)

This change REQUIRES that -Xrs come just after -Xmx512m.

3) Start the service. It should now remain running after you log out.

Shannon's JD Edwards CNC Blog