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
where PARNAME = 'PHYS_MEMSIZE'
order by VERSNR desc
go
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
Troubleshooting
Review: Conclusion and more resources
