by Jean Driscoll
Senior Technical Consultant, Syntax
Sometimes you come in on a Monday morning and nothing is working. Suddenly you have a P1 and many users and their managers calling. Most IT workers have been called in the middle of the night or have arrived at work walking into this situation.
Data growth is usually gradual and can be monitored and disks cleaned or added to keep operations moving. You don’t expect a disk issue.
I never like to see the SQL0901 message(s) when it points to a database issue of some sort. Hopefully, this discussion will be beneficial by describing some of the commands that will help you understand your database and journaling a bit more.
To keep track of SQL additions, changes, and deletions, and to allow a SQL rollback operation (in case the update was not allowed), JD Edwards Enterprise One starts journaling on the iSeries database. The journal is defined in the JDE.INI file. It uses journal receiver objects to hold the before and after images of record updates.
Normally, these journal receivers are managed by the system – saving and deleting them at defined intervals. However, issues can arise that don’t allow the journal receivers to be saved and/or deleted. If no one is watching or aware of this issue, these journal receivers can build up in the system and cause havoc.
When using an iSeries as the enterprise server and database, the JDE services stop working if the disk usage goes above a specific user-defined system percentage. Users will receive messages when trying to sign in and the logs will show error SQL0901. This message can signify that you are running out of disk space, or that there are issues with the journaling on the database.
When you receive any SQL messages in Enterprise One logs, the code can be looked up on the iSeries using the command: I
Enterprise One Web log showing SQL0901
Enterprise One enterprise server log showing SQL0901
If you look at the system operator messages, you will see messages like this that tell you your disk is full. These messages should be set up on an iSeries monitor.
CPI099B 90 Critical storage condition exists.
CPI099C 90 Critical storage lower limit reached.
DSPMSG QSYSOPR:
Use WRKSYSSTS to view the disk usage % (if using multiple ASPs use DSPASPBRM).
To find out what is using the disk, an iSeries system report should be generated. There are two processes necessary to get this report – RTVDSKINF/PRTDSKINF.
- The RTVDSKINF command should be set up on the iSeries job scheduler – WRKJOBSCDE job QEZDKWKMTH. If this job is not setup on a scheduler, this command needs to be run from the command line. Even if the job is running on the scheduler, to see the disk usage report updated to current information, this command needs to be run.
- The PRTDSKINF command gives you the report, based on the data saved by the RTVDSKINF command. You might want to run this command first, to see the disk space that will be used, and then run the RTVDSKINF, and then run this PRTDSKINF a second time to see the current usage.PRTDSKINF RPTTYPE(*LIB) MINSIZE(1000)
- WRKJOB Option 4: After running the report helps you find the report. Page Down through the report to see the size of journal receivers on the system and then the size of libraries.
- After cleaning up the journal receivers, submit the RTVDSKINF and PRTDSKINF commands once more to see the difference.
- The disk full issue may also be due to a report that is running out of control. You will see this as “Spool” on the report. Use WRKOUTQ to view all the output queues and select each queue with files and review the number of pages on the reports to ensure this is not the issue.
At the top of the report you can see what date the RTVDSKINF was run. Page Down to see where the disk is being used.
In most cases of disk problems on iSeries for a JD Edwards installation, the issue is that some program added/updated/deleted a bunch of data and the journal receivers grew too quickly or maybe the backup has not been run so a message exists somewhere saying the journal receivers could not be deleted.
The default journal used for tables updated through JDE can be found in the JDE.INI on the enterprise server. Use WRKLNK ‘E920SYS/INI’ and option 5 to view the jde.ini file. Put ‘Journ’ on the Control line and press F16 to find the Default Journal.
However, that is not always the journal being used by the tables. If the customer uses MIMIX replication, they may have assigned a different journal for different libraries or objects. Use the DSPFD command on the table(s) that were added/updated/deleted to find out which journal is being used.
DSPFD CRPDTA/F0911:
Roll down or put ‘ourn’ in the find line and press F16 to find which journal is being used.
Once you know which journal is being used, use the WRKJRNA command to find which library the journal receivers reside.
WRKJRNA OWJRNL/OW_JRNL
F17 – journal receiver attributes – to find in which library to find the journal receivers.
To view the receivers and determine if this is an issue use the WRKOBJ command and option 8 to view the size. Page Down through some screens to determine how many of these exist.
WRKOBJ OWJRNL/*all *JRNRCV:
To delete the receivers, use the DLTJRNRCV command.
DLTJRNRCV JRNRCV(OWJRNL/O*) DLTOPT(*IGNINQMSG)
You may receive message “CPA7025 – Receiver OWJRNR0000 in OWJRNL Never Fully Saved. (I C)”.
Depending on what the journal receivers are being used for, you may not want to delete the receivers immediately. If the receivers are being used in MIMIX to update data on a separate system, you may need to allow the data application to complete before you can delete the receivers.
Reply with “I” to ignore the message and delete the journal receiver.
Journal receivers are numbered from 0000 to 9999. The receivers must be deleted in order. The DLTJRNRCV command will try to delete them starting at 0000.
However, once the 9999 receiver is created and is full, it will try to create 0000 again so the journal receivers may get out of sequence. In this example I tried to delete one journal receiver, OWJRNR0008. Since 0001-0007 still exist I cannot do this one.
To see the full error, put the cursor directly on the message and press F1.
Maintaining Journal Receivers:
For the WRKJRNA command above there is an option to set how the receivers are managed. This should be set to *SYSTEM and the Delete Receivers option should be set to *YES. Use the CHGJRN command to set these. If you had to delete many journal receivers, make sure they are being backed up and deleted on a regular basis.
CHGJRN JRN(OWJRNL/OW_JRNL) MNGRCV(*SYSTEM) DLTRCV(*YES)
WRKJRNA OWJRNL/OW_JRNL
Check the backup to make sure the receivers are being backed up.
DSPLOGBRM TYPE(*BKU) PERIOD((*AVAIL 121022))
SQL0901 in Retrospect:
The SQL0901 error can occur for other reasons than just that the disk is full. At one customer this error occurred when the size of the journal receivers grew larger than the threshold allowance. In this case you would need to delete journal receivers or update the threshold to allow more.
Sometimes a new table will be added manually instead of through the EnterpriseOne development client. The way a table is created may not start the journaling on the table. This will also generate a SQL0901 error.
When I see this error, the first item I check is the disk space. If it is not a disk space issue, you will need to capture the job with the error. In many cases this is a database job like QSQSRVR or QZDASOINIT. You can look for any of these jobs whose job status is MSGW, but sometimes they are hidden and you have to keep looking.
Program Output/Reports:
If deleting the journal receivers does not give you the disk space back, use the WRKOUTQ command to view the output queues. Sometimes a job will go crazy and start creating many spool files or one big spool file that needs to be deleted.
Roll through and use option 5 to view specific output queues that may have spool files to delete.
Conclusion:
EnterpriseOne requires tables have journaling turned on when using iSeries as a database. Therefore, these journal receivers need to be maintained. Whether it be manual or by the system, someone should be watching them, by monitor messages or manual checks.
Commands that are useful to understand possible database issues:
WRKMSGD, MSGID (SQLxxxx), MSGF(QSQLMSG)
DSPMSG, QSYSOPR
WRKSYSSTS
RTVDSKINF, PRTDSKINF
WRKLNK ‘E920SYS/INI’
DSPFD CRPDTA/F0911
WRKJRNA OWJRNL/OW_JRNL
WRKOBJ OWJRNL/*all *JRNRCV
DLTJRNRCV JRNRCV(OWJRNL/O*) DLTOPT(*IGNINQMSG)
CHGJRN JRN(OWJRNL/OW_JRNL) MNGRCV(*SYSTEM) DLTRCV(*YES)
WRKJRNA OWJRNL/OW_JRNL
DSPLOGBRM TYPE(BKU) PERIOD
WRKOUTQ