SAP Note 21797 - Explain SQL with trace list without result (ST05)

Symptom:

When you choose the function 'Explain SQL' during evaluation of the SQL trace (ST05, 'List Trace'), no query plan is output.
Only the following message is displayed:
'Execution plan of a select statement (Online optimizer)'
or
'sqexplain.out cannot be read'

Cause and prerequisites

Access to the operating system file 'sqexplain.out' failed.

Possible causes for this are:

1. Error in program RSINF100 (only in SAP Release <2.1J or <2.2D):
The file 'sqexplain.out' is only read in the current directory.
Therefore, the call of 'Explain SQL' fails on the application systems.

2. UNIX:
The home directory of user 'sapr3' on the database server is not mounted on all application systems (in all SAP releases).
NT:
The share "SQEXPLN" on directory %INFORMIXDIR%\sqexpln is not available on the DB server.

3. In the home directory of user 'sapr3' on the DB server (or in directory /usr/sap//D*/work on the DB server) there is a file 'sqexplain.out' to which user 'adm' or user 'sapr3' has no read or write access.

4. The profile parameters SAPDBHOST and SAPLOCALHOST contain different entries although you are working on the database server.

5. In rare cases no Explain is possible on the application servers (if necessary, a Rabbax exists), but on the database servers everthing works.

Solution

If 'Explain' is requested from an application server, the plan is written to the file 'sqexplain.out' in the home directory of the user (for R/3, this is always sapr3) on the database server.
If the function is started from the database server, the file 'sqexplain.out' is stored in the current directory (/usr/sap//D*/work) on the database server. If a file 'sqexplain.out' already exists in the corresponding directory, the system will try to update this file.

ad 1)
With SAP Release 2.1J or 2.2D, the error in the RSINF100 program was corrected.
You can also download a corrected version of the report from sapserv3/4/5:

• Download the file with ftp. '/dist/permanent/INFORMIX_22C/explain/rsinf100 ' from sapserv3/4/5.

• Import the program with the command
  "R3trans -i rsinf100" (as user 'adm').
  CAUTION: The database must be active in this case.
  
  

ad 2)

• DB server is UNIX:
  
  Make sure that the home directory of the user sapr3 and the path is (physically) the same on all participating computers. 'Yellow Pages' must also be considered.
  

If the application server is an NT server,

• a modified report RSXPLINF must be used,

• the home directory of user 'sapr3' must be accessible from this server.
  

To import the modified report, use ftp to download
the file 'RSXPLINF.230698'
from the sapserv servers in directory
/general/3rdparty/informix/ddic/
Import this report with the command
"R3trans -i RSXPLINF.230698" (as the user 'adm').
CAUTION: the database must be active when you do this.

To make the home directory of user 'sapr3' available on the NT server, you must create a 'share' called 'homesapr3' for this directory (with Samba) on the database server.
CAUTION: the name 'homesapr3' is obligatory (for technical reasons).
On the NT server, you must then use the command
'net use * \\\homesapr3'
to make this directory available.

DB server is NT:

Create the missing share on the DB server using the command
'net share SQEXPLN=%INFORMIXDIR%\sqexpln'



ad 3)

• Make sure that the file 'sqlexplain.out' contained in the directories '~/sapr3' or '/usr/sap//D*/work' has the approriate authorizations:

• User 'adm' and user 'sapr3' must be able to read and write the file. The authorizations should be '-rw-rw-rw-' (666).
  
  

ad 4)

• In this case the Explain cannot be carried out on the database server until the profile parameters have been corrected. The Explain function is also possible from the application servers.
  As a workaround you can delete the existing file 'sqlexplain.out' in the directory '/usr/sap//D*/work' and create a link to a file 'sqlexplain.out' in the directory '~sapr3':
  
  cd /usr/sap//D*/work
  ln -s ~sapr3/sqlexplain.out sqlexplain.out
  
  
  

ad 5)

• The problem is caused by the fact that the command 'cd ~sapr3' cannot be executed. In this case you must change report 'RSXPLINF'.

In two places the line

command = 'cd ~sapr3; pwd'.

must be replaced in report RSXPLINF with

command = 'csh -c ''cd ~sapr3; pwd'''.