Basis challenge #3: Getting system profile parameters

Learn Basis best practices and how to overcome common administrator problems in this new checklist from SAP Basis guru Giovanni Davila.

Find out if a specific profile parameter exists in all SAP servers. Get the latest parameter value, the profile where it's assigned and the profile version. The output should go to a file called "parameter.txt".

Parameter in question: PHYS_MEMSIZE

SQL Script
The following script queries table TPFET as this table holds all the profile parameters. We are interested in these fields:

  • PARNAME Profile parameter name
  • PVALUE Value of the profile parameter
  • PFNAME Name of profile for system parameterization
  • VERSNR Version number

Since we want to get the latest value of the parameter we will sort by version number (field VERSNR) in descending order.

SQL code:

set nocount on 
select top 1 
  substring(PARNAME,1,15) as [Parameter], 
  substring(PVALUE,1,28) as [Value], 
  PFNAME as [Profile], 
  VERSNR as [Version], 
  'server' as [Server] 
from dbo.TPFET 
order by VERSNR desc 
Batch file
The batch file is basically structured the same way as the first one we used earlier. The main difference is in the SQL statements. But, let's walk through it.

  • The output filename is set in this variable: set outfile=parameter.txt
  • Field PFNAME is 25 characters long and field PVALUE is 128. Since some parameters and values are never that long we can display only part of the entire strings. This is why we have these variables: set ParameterString=25 set ValueString=128

Also, the SQL statements within the batch file to display those fields look like these: echo substring^(PARNAME,1,%ParameterString%^) as [Parameter], >>%SQLcmd% echo substring^(PVALUE,1,%ValueString%^) as [Value], >>%SQLcmd%

As explained earlier, the "^" symbol is necessary to escape the parenthesis symbols "()" in the echo commands.

  • We run OSQL as follows: osql –S %%a –d %%b –b –n –E –s "|" –I %SQLcmd% -w 250 >>%outfile%

    As you can see, the OSQL command looks almost the same as in the first script. However, we use a wider column (250) and we append to the output file (>>%outfile) instead of sending the output to individual files.

This script produces an output similar to the one below:

Overcoming Basis challenges by reaching inside the database

 Home: Introduction
 Basis challenge #1: Reporting logon information
 Basis challenge #2: Gathering SQL Server information
  Basis challenge #3: Getting system profile parameters
 Review: Conclusion and more resources
This was last published in December 2005

Dig Deeper on SAP Basis

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.