SAP Restart to Delete SQL Packages

[christian_bartels]

I am starting this discussion on behalf of a client:

Especially the "veterans" of AS/400 remember, that in the beginning of SAP on AS/400 it was often necessary to stop the SAP instance and delete the SQL packages in order to overcome database errors that usually showed up as SQL -901 errors. This way of "fixing" SQL -901 became like a habit, so that support employees often recommended to stop the SAP instance and delete SQL packages when a simple restart of the failing work process would have fixed the problem. Later we recommended to use profile parameter rdisp/wp_auto_restart to restart work processes automatically once a day (for example), and that seemed to have solved the problem. However, my client is not quite convinced that this works and does not want to "play" with the production system. They are using an ECC 6.0 system with a 10 TB database and about 4000 active users.

Question to this group: Do you have systems with a similary size that run several weeks (or even months) without interruption for deleting SQL packages? If so, do you use profile parameter rdisp/wp_auto_restart, and what value is configured? Also "negative" answers are welcome, i.e. if you have the experience that the SQL packages must be deleted on a regular base for the sytem to work properly, please let us know, too.

Thanks a lot for your cooperation.

Kind regards,

Christian Bartels.

[Former Member · Accepted solution]

That sounds familiar! In our case, we had different tables and SAP had the anwer, but first we increased the size, then still ran out, then put in the hints.

Looks like your work processes are restarting once a week, vs. our once a day. But I'm not familiar with "rdisp/noptime". Documentation says it should be greater than wp_auto_restart, but our noptime is zero. It sounds like that is the trigger for checking if the wp_auto_restart time is exceeded. Does that mean I'm checking constantly, or not at all?

We have a CL that we run that looks for SQL packages that are approaching the limit--that gives us a little bit of warning to come up with the "hint" before a job or transaction locks up at the SQL package size limit. Crude, but it has been triggered a couple of times and helped us out. It generates a file with object descriptions for all *SQLPKG objects. Then it copies that file to another file, including only records with sizes past a threshold. If the result is > 0, an email alert is sent, and we go check the contents of the second file.

[Former Member · Accepted solution]

Hello Jim,

nice to hear from you )

What is the idea for rdisp/noptime ?

It is the number in s when it checks "if the WP wants to die".

0 => never

But, it ALWAYS checks after each transaction end ... => when you are restarting SAP during night with 0, it will most likely restart all the WPs when a lot of users show up in the office as then WPs are used ... that is, what you typically want to prevent ...

=> it should be a bit larger than wp_auto_restart => the restart stays more or less in the area of wp_auto_restart ...

(as most customers are using 86400 = 1 day, this stays pretty often at night ...)

Regards

Volker Gueldenpfennig, consolut international ag

http://www.consolut.com http://www.4soi.de http://www.easymarketplace.de

[Former Member · Accepted solution]

Hello Volker, Jim, Christian,

As I recall we set rdisp/wp_auto_restart to 604800 (1 week) a little over two years ago. We chose one week since we are global and did not want to disrupt users. I'm not aware of any SQL pack issues in that two year period.

Craig

[christian_bartels]

Thanks for all the feedback.

From the answers and other discussions I got the impression, that we currently have no general problems when SQL packages are not deleted on a regular basis.

There are some cases where individual SQL packages reach their size limit (512 MB or 1 GB, depending on QAQQINI parameter SQL_INCREASE_PKG_LIMIT). Possible solutions for these problems are discussed in SAP Note 54028 and related SAP Notes.

As always, is is highly recommended to delete SQL packages after major maintenance work, such as installation of cumulative PTF packages, release upgrades or SAP upgrades (see SAP Note 63037).

Again, thanks to all who participated in this survey.

Kind regads,

Christian Bartels.

[heming1]

Hi All,

Just want to add one more case - our Production system ECC 6.0 without EHP, kernel 720_ext, SP level 401; 6 TB + size, DB400 release V7R1, DBSL version 720, patch level 322. rdisp/wp_auto_restart = 24H.

We have many end users who are running SQ01 queries in the system. Recently we always got the issue of "database error - 904 with PRE access to table MARC" for end user's SQ01 query jobs. After restarted the system, the problem was still showing up; then did the system stop, IPL, the issue was still not resolved; the last try was that stop the system, deleted all SQL packages - it resolved the error!

I'm asking for all "veterans" for suggestions and help, so we always have to do the regular system restart, and delete all the SQL packages to remove this kind of database errors for as400 system even get high DB release? Do we have any resolutions without shutdown the system?

Thanks and regards,

Heming

[volker_gldenpfennig]

Hi,

I would suggest the following - that is a great feature for you ;-))

Please add the following 2 profile parameters:

dbs/db4/dbsl_tablehint0	Prevent overflowing SQL Packs… (use dbs/db4/single_execution_threshold as well !)	notes: 1109771, 1536572	Table = 'MARC' & nMarkers >= 20 ? SingleExecution = true

dbs/db4/single_execution_threshold

Limit the max. number of SQLs per SQL Pack for “single execution”

note 729136

200

btw:

you should update your no longer maintained kernel 7.20 ... the best might be to 7.22 - otherwise to 7.21 ...

Regards,

Volker Gueldenpfennig, consolut international ag

[dorothea_stein]

Hi Hemig, hi Volker,

I suggest to enhance Volker's suggestion like that, :

dbs/db4/dbsl_tablehint0 = Table = 'MARC' & nMarkers >= 20 & NoSTMTID = true ? SingleExecution = true

An alternative might be

dbs/db4/dbsl_tablehint0 = Table = 'MARC' & NoSTMTID = true ? PackageDispersion = 7

Some background:

The reason for that is that such statements that do have a so-called "statement ID" are usually static statements in ABAP meaning that one line in ABAP results in one SQL statement, e.g.

SELECT VERSION INTO WA FROM SVERS.

ENDSELECT.

The number of those statements is limited, and they are thus very well performing handled through our package concept, even if the number of host variables was very large.

Critical statements are typically those for which there is no statement ID. They typically result from ABAP statements, with dynamic elements such as dynamic WHERE clauses or use of RANGES TABLES. Especially for the latter, one line in ABAP can result in thousands of different SQL statements with increasing number of host variables in an IN list depending on the number of entries in the RANGES TABLE at execution time:

       SELECT VERSION FROM SVERS WHERE VERSION IN RANGETAB.

       ENDSELECT.

results in

SELECT VERSION FROM SVERS WHERE VERSION IN (?, ?)

SELECT VERSION FROM SVERS WHERE VERSION IN (?, ?, ?)

...

SELECT VERSION FROM SVERS WHERE VERSION IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ... , ?, ?)

The above suggestion turns all statements with more than 20 "?" into single_execution, which typically is a good choice as the likelihood that those statements get executed frequently is low. Paying the penalty of having to re-prepare a statement in those cases is thus acceptable. But there are exceptions. And in those cases the better choice might be to apply the following table hint that distributes contents of a package intopackages (e.g. 7):

dbs/db4/dbsl_tablehint0 = Table = 'MARC' & NoSTMTID = true ? PackageDispersion = 7

How do you know?

Before you delete a package you might want to save it to a save file to analyse it later using the new SQL package catalogs: The following statement can give you an overview over what statements are in a package (here R3SAPX0000/MARC) and how often they have been used so far. It can help you to determine a good value for nMarkers, or to decide to use PackageDispersion instead of single_execution if most statements are executed many times already:

   SELECT NUMBER_TIMES_EXECUTED, CAST(STATEMENT_TEXT AS VARCHAR(30000))

         FROM QSYS2.SYSPACKAGESTMTSTAT

          WHERE SYSTEM_PACKAGE_SCHEMA = 'R3SAPX0000' AND

                         SYSTEM_PACKAGE_NAME = 'MARC'

          ORDER BY STATEMENT_TEXT

Best regards,

Dorothea

[heming1]

Hi Doro/Volker,

Thanks a lot for your detailed information! I learned A LOT! SAP support developer with my message also mentioned both of your names! She also suggested me to use your solutions.

I'll apply them in my system then and will update at here.

Best regards!

Heming

[Former Member]

Our production system is not quite that large: 3.5TB and about 2,700 users. We probably qualify as "veterans", but we've rarely had to delete SQL packages except for either kernel or OS maintenance (usually both at the same time). We've seen occasional issues (non-ECC SAP systems) where an SQL package has exceeded the maximum size--we scan for that each week to monitor for that, and there is an SAP note that shows how to add hints to help minimize the SQL package size.

We still use the restart parameter. In our case, we have rdisp/wp_auto_restart set to 86400, so each work process restarts each 24 hours. We do bring down the entire system once a month for period-end processing, so maybe that helps as well. I believe I have seen documentation indicating the restart is no longer necessary, but it is still working for us, and we've not taken the time to research or test making the change.

[Former Member]

Hi Christian and Jim,

Our production database is 8 TB with 3400 named users (1,000 or so concurrent). We still are "old school" and delete the SQL packs during normal maintenance twice a month (either two or three week interval). I know we have gone longer if we were having a lot of go-lives but I don't think it has happened since we upgraded to ECC 6.0.

We did have some SQL pack issues a while ago but it turned out to be they were reaching the maximum size for table IBSYMBOL. Since then we set the QAQQINI value "SQL_INCREASE_PKG_LIMIT = *YES " which doubled the allowable size to 1GB I believe. We still were reaching the max and we ended up inserting a profile parameter.

"dbs/db4/dbsl_tablehint0 = Table = 'IBSYMBOL' & nMarkers > 100 ? SingleExecutionReuse"

Credits - Steve Tlusty of IBM came up with the fix

During that round of fun, Steve also had us adjust the following parameters -

rdisp/noptime = 605800

rdisp/wp_auto_restart = 604800

Note - even though we are at 6.1.1 now, the adjustments were made on 4.7x200 while running under V5R4 so I don't know if they are still required.

Have I rambled too long?

Craig