The issue is now resolved. I believe the problem started because journalling was started before the Relational Database Entry was changed to replicate our Production Server.
The JDE.LOG showed the following:
DB0000066 - Unable to prepare statement: CALL STRJOURNAL (?, ?, ?).
Solution:
1. We ended journalling on all objects. Then we removed the SQL Stored Procedures in OWJRNL Schema because we found 4 entries and our Production box only had 2. Check the entries using: select * from qsys2.sysprocs where specific_schema='OWJRNL'
We also cleaned up other duplicate stored procedures with strange names ending in 00001.
2. Deleted the journal and receivers in OWJRNL.
3. Tried to follow solution ID 200990245, but this program failed to start journalling on all the objects.
4. Manually started Journalling on E1 libraries using OS400 commands. (This is noted at the bottom of the Solution Doc)
Below is the Solution ID 200990245
Applies to:
JD Edwards EnterpriseOne Tools - Version: S13 - Release: S13
IBM i on POWER Systems
This document was previously published as Customer Connection Solution 200990245
Symptoms
Some EnterpriseOne applications like OMW, P0911, etc. may have STRJOURNAL error in JDE.LOG if EnterpriseOne journal OW_JRNL and journal receiver OW_JRNL000 were not created or started during EnterpriseOne installation.
Some of the errors in JDE.LOG:
509/325 Thu Mar 13 08:40:41 2003JDBODBC.C5066
ODB0000167 - Unable to prepare statement: CALL STRJOURNAL (?, ?, ?).
509/325 Thu Mar 13 08:40:41 2003JDBODBC.C5067
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0204 - STRJOURNAL in type *N not found. - SQLSTATE: S0002
509/325 Thu Mar 13 08:40:41 2003JDBODBC.C2128
ODB0000183 - SQLExecute failed
509/325 Thu Mar 13 08:40:41 2003JDBODBC.C2128
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7008 - F9202 in DD810 not valid for operation. - SQLSTATE: S1000
Cause
Not Applicable
Solution
Perform the following steps to create or recreate journal and journal receiver if they are damaged/unusable:
1. For this operation make sure the user ONEWORLD has *ALLOBJ and *JOBCTL for special authority.
2. Log in as ONEWORLD that has EnterpriseOne system library in the library list, Ex: E810SYS
3. Issue this command if there is no journal receiver found in OWJRNL library :
CRTJRNRCV JRNRCV(OWJRNL/OW_JRNL000) THRESHOLD(60000)
4. Issue this command if there is no journal found in OWJRNL library :
CRTJRN JRN(OWJRNL/OW_JRNL) JRNRCV(OWJRNL/OW_JRNL000) RCVSIZOPT(*MAXOPT2)
5. Stop EnterpriseOne services
6. Log in as OneWorld
7. Run ADDLIBLE OWJRNL
8. Run INSTALLSP SPNAME(STRJOURNAL) LIB(OWJRNL) UID(PSFT) PWD(PSFT) JRNLIB(OWJRNL) JRNRCVLIB(OWJRNL)
*** If you are NOT at EnterpriseOne release 8.10, 8.11 or 8.11 SP1, perform command below (use JDE user id):
INSTALLSP SPNAME(STRJOURNAL) LIB(OWJRNL) UID(JDE) PWD(JDE) JRNLIB(OWJRNL) JRNRCVLIB(OWJRNL)
***NOTE: issue DSPJOBLOG to make sure there is no error message on this command.
9. DSPF FILE(OWJRNL/OWJRNLINFO) and look for the following:
Information for Stored Procedure
---------------------------------------------
JOURNAL='OW_JRNL '
JOURNALLIBRARY='OWJRNL'
JOURNALRECEIVER='OW_JRNL000'
JOURNALRECEIVERLIB='OWJRNL '
JOURNALRECEIVERSIZE='60000'
10. On client workstation, under Control Panel/ Administrative Tools/ Data Sources (ODBC) and under System DSN, search for OWJRNL and open it up to make sure it points to OWJRNL library.
11. Start EnterpriseOne
12. From a fat client log in to EnterpriseOne, launch OMW, put an object in a project and check it out and then check it back in. If the process works journaling changes have taken place correctly
13. Perform these 2 steps below, just to confirm the journaling is working fine.
a) From the iSeries command issue -> DSPJRN JRN(OWJRNL/OW_JRNL) and you should have some tables listed that just got journaled in the OWJRNL library.
b) Issue the following query to make sure system tables got updated with the storage procedure STRJOURNAL pointing to the new journal library
RUNQRY *N QSYS2/SYSPROCS
***NOTE: DSPLIB OWJRNL library should have these objects, and none of them should be deleted:
INSTALLSP *PGM
INSTALLSP *CMD
OWTBLCONV *PGM
STRJOURNAL *PGM
OW_JRNL000 *JRNRCV } This is the current *ACTIVE journal receiver attached
OW_JRNL *JRN } Use WRKJRNA command to check the journal attributes
OWJRNLINFO *FILE } This file is created after INSTALLSP is successfully done.
WORKAROUND:
If there is nothing wrong with journal and journal receiver files, most of the applications are running fine, and issue occurs to 1 or 2 files that could not be started for journaling (this happens occasionally) by EnterpriseOne application, the following IBM command can be used to start particular file eg F9202:
STRJRNPF FILE(DD810/F9202) JRN(OWJRNL/OW_JRNL) IMAGES(*BOTH) OMTJRNE(*OPNCLO)
