SAP Note 22945 - OnArchive: requests remain 'Executing'

Symptom:

OnArchive requests retain the status "Executing" during processing. The following message is written in the log file of the oncatlgr process:
(inf668), (inf710) ARC-F-01217 Error message received from cataloger
ARC-E-01285 Request not modified
Cursor Status # -668
Error Description: Error during call to Informix Statement EXECUTE
Function name: CDI_UPDATE_MODIF_REQ

In "/tmp/oncatlgr.out.", error messages of the following type can often be found:
"System command not found"
"Cannot execute system command"

Additional key words

OnArchive, oncatlgr, CAI_DBINTFC, inf668, CDT_UPDATE_MODIFY_REQ
ARC-F-01217, ARC01217, ARC1217, INF01217, INF1217
ARC-E-01285, ARC01285, ARC1285, INF01285, INF1285

Cause and prerequisites
As of Release 2.1I/2.2C:

All OnArchive requests are logged in the file system, so that if the database collapses completely, the allocation of tapes to the archive requests is retained.

For this, a trigger was installed in the database which is activated during the update of the OnArchive table "arc_request". This trigger calls a shell script via a stored procedure which logs the OnArchive events. These log files can be found under:
"$INFORMIXDIR/sapreorg/recover.rpt".

For the symptom listed, it is not possible to start the system call in the stored procedure.

As of Release 3.0x in connection with Online >= 7.2x:

As of version 7.2x, Online provides a more stable log feature. It is recommended that you no longer use the technique (trigger) described above but rather the "ACTIVITYLOG" feature.

Solution
For Releases 2.1I/2.2C to 3.0x:

The SQL script used for creating the new STORED PROCEDURE (create.sql) and the shell script (infarclog.csh) are to be replaced.

Two files are to be created or transferred by ftp. The files are called "create.sql.22945" and "infarclog.csh.22945" and can be found in "/general/3rdparty/informix/bar/os-indep" on any sapservX ftp server of SAP. They can also be found at the end of this note. Rename the files to "create.sql" resp. "infarclog.csh" on your machine.

Follow the steps below:

• Name the files "create.sql" and "infarclog.csh"

• Change in the file "create.sql"

"<> "by your system SID (upper case!, line 8)

• Check in the file "create.sql" whether the following command is actually on one physical line.

LET='... ';

• Execute the following as user "informix"

dbaccess sysmaster create.sql

• In the file "infarclog.csh" (line 20), replace

"TZ" with your time zone, for example "GMT".

• Execute the following as user "adm"
  (Here, replace "" with your system SID.)

cp infarclog.csh /usr/sap//SYS/exe/run/infarclog.csh
chmod 755 /usr/sap//SYS/exe/run/infarclog.csh

• Execute the following as user "adm"

cd
touch archive.log
chgrp informix archive.log
chmod 664 archive.log

• If a file "/tmp/infarclog.log" exists remove it as owner of the file (adm or informix)

rm -f /tmp/infarclog.log

• Execute the following as user "adm"

touch /tmp/infarclog.log
chgrp informix /tmp/infarclog.log
chmod 664 /tmp/infarclog.log

• Test the stored procedure as adm using the command

dbaccess sysmaster <execute procedure inf_arc_log (0);
EOF

• If you execute this you can find and remove the following message at the end of the file "~adm/archive.log":

SQL Error: 100. command failed to find arc_request record 0:
ISAM error: duplicate value for a record with unique key.

• You can remove the requests in the status 'Executing' as follows

• Find out the numbers of the requests with this status

• Execute the following as user "informix".
  Here, replace ,,.., with the numbers of the requests.

dbaccess sysmaster <update arc_request
set req_status = 'FA' where req_rid in (,,..,);
EOF

• Execute the following as user who has create the requests

onarchive 'remove/failed_request'

------- BEGIN --- infarclog.csh --- CUT HERE -------- #! /bin/csh -f
#
# @(#)infarclog.csh 20.2.5.1 SAP 95/07/06
#
# File : infarclog.csh
# Company: INFORMIX Software GmbH
# Date : 05-07-1995
#
# SAP DBA - Informix Database Archiving
# Script to set a working environment for the program infarclog.
#
set log=/tmp/infarclog.log
# set log=/dev/null

# ensure that minimal path exists
set path=( $path /usr/ucb /usr/bin /bin )
set dir=`dirname $0`

# try to set TZ
set tz=''
set tzcheck='Z>'
set tzcheck="if ( ! $?TZ ) then
if ( "X$tz" == "X$tzcheck" ) then
setenv TZ GMT
else
setenv TZ $tz
endif
endif

# get SID (extract sid from call path)
set SID=`echo $dir | sed -e 's/\/usr\/sap\///' -e 's/\/SYS\/exe.*$//'`
set sid=`echo $SID | tr '[A-Z]' '[a-z]'`

# get homedirectory information
set sidhome=~${sid}adm
set infhome=~informix

# get resource file
set rcfile=""
if ( -r $sidhome/.dbenv.csh ) then
set rcfile=$sidhome/.dbenv.csh
else
if ( -r $sidhome/.informix.csh ) then
set rcfile=$sidhome/.informix.csh
else
if ( -r $infhome/.dbenv.csh ) then
set rcfile=$infhome/.dbenv.csh
else if ( -r $infhome/.informix.csh ) then
set rcfile=$infhome/.informix.csh
endif
endif
endif

# set environment
if ( "X$rcfile" == "X" ) then
echo "no readable resource-file found\!"
setenv INFORMIXDIR /informix/$SID
setenv INFORMIX_DBID $sid
setenv INFORMIXSERVER `uname -n`${sid}shm
setenv INFORMIXSQLHOSTS $INFORMIXDIR/etc/sqlhosts.soc
setenv DBNLS 2
setenv LANG C
set path=( $path $INFORMIXDIR/bin )
else
source $rcfile
endif

# set logging directory
if ( -w $sidhome/archive.log ) then
setenv HOME $sidhome
else
setenv HOME $infhome
endif

# call infarclog and exit
$dir/infarclog $1 >>& $log
if ( $status != 0 ) then
echo "infarclog.csh (`date`):" >> $log
echo "infarclog $1 failed with status ${status}." >> $log
endif
exit 0
------- END --- infarclog.csh --- CUT HERE --------

------- BEGIN --- create.sql --- CUT HERE --------
DATABASE sysmaster;

DROP PROCEDURE inf_arc_log;
DROP TRIGGER arc_request_upd;

CREATE PROCEDURE inf_arc_log(req_rid LIKE arc_request.req_rid)
DEFINE command CHAR(256);
LET command = 'csh -cf "/usr/sap//SYS/exe/run/infarclog.csh ' || req_rid || ' >>& /tmp/infarclog.log"';
SYSTEM command ;
END PROCEDURE;

CREATE TRIGGER arc_request_upd
UPDATE OF req_status
ON arc_request
REFERENCING OLD AS pre
NEW AS post
FOR EACH ROW WHEN ( pre.req_status = "EX" )
(EXECUTE PROCEDURE sysmaster:inf_arc_log(pre.req_rid));
------- END --- create.sql --- CUT HERE --------

For Release >= 3.0x in connection with Online >= 7.2x:

Procedure how to use the "ACTIVITYLOG" feature:

1. Drop the trigger:

arc_request_upd on table arc_request in sysmaster

2. Activate/Change the ACTIVITYLOG log
in $INFORMIXDIR/etc/config.arc or $ARC_CONFIG

Example:

! Logfile for all OnArchive eventsg.arc or
#ACTIVITYLOG = /informix//etc/archive.log
ACTIVITYLOG = /informix/T3D/etc/archive.log

3. Restart OnCataloger OnArchive events:

stop_oncatlgr
start_oncatlgr

4. Check:

Check whether the backup and restore actions are now logged in ACTIVITYLOG.