00689 - SAP 4.6x on V4R5: SQLPKG trouble?

00689 - SAP 4.6x on V4R5: SQLPKG trouble?

TXBHW - Original Tax Base Amount in Local Currency   PERFORM Short Reference  
This documentation is copyright by SAP AG.
SAP E-Book

SAP 4.6x on V4R5: SQLPKG trouble?

Some Temp Storage Stats from yesterday......
WRKSYSSTS after stopsap SID TST_20 3.1H 29472M
WRKSYSSTS after stopsap SID DEV_10 4.6C 16046M
WRKSYSSTS after stopsap SID T01_01 3.1H 14525M
WRKSYSSTS after stopsap SID D01_00 3.1H 12851M
WRKSYSSTS after ENDSBS R3_20 12847M
WRKSYSSTS after ENDSBS R3_10 12845M
WRKSYSSTS after ENDSBS R3_01 12842M
WRKSYSSTS after ENDSBS R3_00 12840M
WRKSYSSTS after IPL & start 3.1H D01_00 & T01_00 2827M
WRKSYSSTS after start DEV_10 4.6C 14031M

Observation; Temp Storage was still at 12840M after all R3 subsystems brought
down. We have been running 3.1H to 4.6C upgrades on this box. It is probably
the "upgrade" process which is chomping away the temp storage. It would be
reasonable to IPL after an upgrade. We will continue to monitor daily. Another
3.1H to 4.6C upgrade is scheduled any day now.

Other than that, we have been running 4.6C on V4R5 since November (DEV & TST
systems) & have not noticed an issue with Temp Storage.


-----Original Message-----
From: Ron Schmerbauch [mailto:rschmerbZu...]
Sent: Wednesday, February 07, 2001 2:03 PM
To: sap400Zm...
Subject: RE: SAP 4.6x on V4R5: SQLPKG trouble?

Sorry for the long note.
First, let's separate this. There are 2 points of discussion.
1) SQL Pkgs
2) Temp Storage

Volker mentioned one other thing and it is very important so I want to
restate it...
Please, if you are seeing something that looks like a problem, let SAP or
IBM know about it. Some of the "solutions" that some customers are
implementing, are really only hiding symptoms and just end up leaving the
community with awkward steps to follow. The root cause can never be found
unless someone steps up to IBM/SAP and works with us. I know it is easier
to put on a workaround, but I ask that you give us a shot at it if at all
possible because we'd rather fix it for everyone than have you purposely
killing off jobs and restarting instances.
Also, some of you may be implementing some workarounds mentioned on this
list as preventative measures. However, each of you has different data,
modules, releases, workloads, etc... most customers don't need to do any of
this stuff. I'd suggest waiting for a problem to actually present itself
before doing strange things to your system.

1) SQL pkgs
As Volker mentioned, there is a reported problem on V4R4 where SQLPKGs
overflow. However, this is not a problem at every V4R4 customer, has not
been reported on V4R5 and I know of code added in V4R5 that should
particularly avoid this case. IBM is engaged with a couple of customers
on this, watch for updates.

Another possible cause of the same symptom is easily remedied...remember a
year or so ago when some of you put workarounds in V4R4 QAQQINI to set
PARAMETER_MARKER_CONVERSION = *NO? (OSS note 317633) As far as we know,
PTFs have long since fixed the original problem, and you can set it back to
*DEFAULT now. With the *NO setting, more statements are put into the pkg
than when it is set to *DEFAULT. I would guess that some of you have never
changed it back to *DEFAULT. I have no reason to think it will, but if by
chance a problem appears when you set it back to *DEFAULT, please let us
know instead of putting it back to *NO.

2) Temp Storage
- DSPTMPSTG doesn't account for everything...take the results lightly.
- 4.6 uses more storage (and cpu and memory) than 4.5, which uses more than
4.0, which uses more than 3.1... on all platforms.
- There is an open problem in 4.6 where a huge amount of storage is being
consumed. SAP is trying to see if it is just how the application works or
if there is possibly a case where the kernel is allocating storage and
never returning it to the for updates regarding this.

Some of you may not be familiar with temp storage... here's some background
and hints.
There are several things in the SAP environment that can use large amounts
of temp storage. It is used both by OS/400 and by R/3.
I) SAP memory on the application server
II) Open DB files on the DB server
III) DB monitor
IV) Memory leaks (usually kernel, sometimes OS)
These 4 points are described later in the note below.

OSS Note 142023 is a very good resource for temp stg problem
determination...there are some bits of others mentioned below as well.

SAP allocates a lot storage at start up, mostly defined by the instance
profile parms.
The more jobs in the app server, the more it uses. As you go, it grows
slowly for various reasons.
As you upgrade to higher SAP releases, more app server resource is

From note 139326
"CAUTION: the use of temporary storage is heavily dependent on the profile
parameters with which your system is configured (among other things
em/initial_size_MB, abap/buffersize). Check your estimated initial use
after profile changes using the SAP test program 'sappfpar' (can be found
in the kernel library)." and "(Temporary storage consumption depends on
the number of R/3 users and the transactions used.)"
ABAP Applications that use huge internal tables can also consume a lot of has to get put somewhere.

Solution: Change the instance profile to use smaller allocations for buffer
values, start fewer server jobs, or otherwise use less resource. Stopping
and restarting wp's or the whole instance reclaims storage temporarily, but
isn't really addressing the fact that you are using too much resource.

In 3-tier application servers... SB1, you can only have 4 drives, but can
get more disk...there are PTFs available to let you put in larger(8 or
17GB) drives. SB2 allows four drives, either 8GB or 17GB. SB3 allows four,
six or eight drives; either 8GB or 17GB.

Files(aka ODPs) are left open on the DB server for performance reasons. We
are purposely trading temp space for better performance. If a file is
already open, you can fetch the next row from it with little cost. There
is an instance profile threshold value for open files...if you cross it, a
bunch are closed using a "least recently used" algorithm and it frees up
space. The more DB server jobs you have, the more open files you will have
on the machine, and the more temp stg you will use.

Solution: There are instance profile parms to set the threshold of the
number of files that can be open in a job, and how many to close when the
threshold is reached. If the space consumed is too large, lower the
threshold value and/or increase the number closed. Tuning this is
important...too many cleanups may slow your system down.
dbs/db4/odp_commit_threshold : When this threshold is reached ODPs will be
closed after a commit
dbs/db4/odp_open_threshold : When this threshold is reached ODPs will be
closed after an open
dbs/db4/odp_threshold : number of ODPs that are left open after the
Also see OSS note 125661.
If too many open files is the cause of your problem, a STOPSAP will close
all files and get all the storage back, but the problem will probably come
right back once you run R/3 for a few hours because you haven't changed the
thresholds. This is another example of a "workaround" treating the
symptom, not the cause.

SAP requests that the DB monitor is running to collect data (usually used
by the Early Watch service).
The old DB monitor (STRDBMON) sent data to a file, and could fill up disk
(non-temp space) rapidly. It is being phased out.
A new DB monitor is memory based, and filters out uninteresting queries as
it goes. However, it uses some temp storage.
Solution: Turn off the DB monitor using the instance profile parm. This
means though that you won't be collecting DB monitor data. Try solutions
from I) or II) first.

Sometimes SAP or IBM will have a bug where memory is allocated, but never
These are memory leaks and should be fixed. It can be difficult to detect
Solution: Keep up with ptfs and kernel patches. If all R/3 servers are
down on this box, but there is still a high amount of temp storage
consumed, there may be a leak. This is probably the only case where you
should need to do an IPL...for everything else, ending the jobs should do
it. Note that the system always uses some temp storage, and you will see
this even right after an IPL.

Example of a memory leak problem... text taken from Note 143205
"There is a program error in R/3 roll storage management in the KERNEL
patches on sapserv(x). This error affects the KERNEL patches for 3.1I
(from patch level 213) and 4.0B (from patch level 302). The error results
in a considerable increase in temporary storage utilization. It is possible
that up to 15 GB more storage will be used before the R/3 instance stops
and you find the following entries in the developer trace files dev_w...
As a result of the program error, it can also happen that 100% disk
utilization is reached and the AS/400 stops. In this case, the faulty R/3
kernel does not run for long enough for the error messages to be written to
the developer trace files.
Apply the latest KERNEL patch from sapserv(x), making sure that you use at
least patch level 229 (3.1I) or 318 (4.0B). "

There was a "reduce the dataset" comment that came in one of the temp
storage postings last month. This was from OSS note 147550. The point
that was being made is this...if you can ask for less data, there is less
db work to do, there is a smaller result set to pass to R/3, and a smaller
internal table for R/3 to manage - better performance, less space used.

Finally, crashes are almost always avoidable because high storage use can
be monitored and you can build yourself a safety net.
Set system value QSTGLOWLMT Auxiliary storage lower limit. Specifies the
percent of available storage remaining in the system ASP when the auxiliary
storage lower limit is reached. The QSTGLOWACN system value specifies the
action associated with this limit. The percent of storage currently used
in the system ASP is viewed with the Work with System Status (WRKSYSSTS)
command. You can set system value QSTGLOWACN (Auxiliary storage lower
limit action) to *REGFAC so the OS will automatically submit a job to
call exit programs registered for the QIBM_QWC_QSTGLOWACN exit point. gives
an example of how to register an exit point.
An exit pgm could shut down app servers.

An even easier way to build this safety net is to monitor for the
message(CPF0907) sent to qsysmsg and then have a CL pgm take some action.

I hope this helps. Again, if you think something is really wrong, monitor
the situation, try the suggestions in 142023 and the other info above. If
it still doesn't seem ok, by all means open an issue. Past experience
shows that it really helps if you can determine what job/report/action you
do that triggers the storage consumption because we'll typically need to
recreate the problem as service collects trace data that can show who is
requesting the storage.

Regards, Ron

Ron Schmerbauch 507-253-4880 rschmerbZu...
iSeries 400 - ERP Development - Rochester, MN

"Anderson, Doreen" <DAndersoZb...> on 02/06/2001 03:37:35 PM

To: "'JIM DOLL'" <JDOLLZp...>, sap400Zm...
Subject: RE: SAP 4.6x on V4R5: SQLPKG trouble?

Was just taking a look at that (too!)

We were running at about 5,500M Current Unprotect on our AS/400 which hosts
3 SID's at release 3.1H
We upgraded 1 of the SID's to release 4.6C & the Current Unprotect is
running around 29,000M. An increase of 23,500M.
DSPTMPSTG = 15,863M so guess it's time to find those emails that help
explain why Current Unprotect could be double
the DSPTMPSTG results. We bring down the SIDs every night, so that won't

We will take stats after ending all SIDs
After ending R3_nn subsystems
After IPL

Something is definitely not correct but have not pursued YET

Doreen Anderson
Operations / SAP Basis Manager
Ball Horticultural Company

-----Original Message-----
From: JIM DOLL [mailto:JDOLLZp...]
Sent: Tuesday, February 06, 2001 2:52 PM
To: sap400Zm...
Subject: SAP 4.6x on V4R5: SQLPKG trouble?

We've heard a rumor from an application service provider that several
companies live with SAP 4.6 on V4R5 have been having problems that require
them to shutdown and delete SQLPKG's regularly. Sounds like some
(even more than normal) amounts of temporary storage used, as well.

Anyone experience such problems, or can confirm or deny the rumor?

Jim Doll, Perrigo

Durban Tours - Südafrika Safari

CL_GUI_FRONTEND_SERVICES - Frontend Services   ROGBILLS - Synchronize billing plans  
This documentation is copyright by SAP AG.

Length: 14960 Date: 20231204 Time: 042340     sap01-206 ( 4 ms )