Reorganization of update date not allowed in batch

Or the story of how a spelling error in an error message can send you on a wild goose chase.
Today, one of my colleagues came to me and was worried about a job, SAP_REORG_UPDATERECORDS, that seemed to cancel everytime it ran, along with several other jobs.I started my investigation, and I found that we had a LOT of DB13 jobs and SAP standard jobs that had apparently been scheduled with no regard to what was actually needed. Some jobs even attempted to start an online backup. I started deleting the superflouos ones, but wondered a bit about the SAP_REORG_UPDATERECORDS. Surely a reorganization job would still need to run. I looked at the job log, and lo and behold, there was a nice and shiny error following the very first jobstep:



So I figured, hey, this is ofcourse somewhere in an OSS note. But nooooo. My dissapointment was obvious. untill I found note 1558304, which describes that a translation error had taken place:

Wrong English Text: Reorganization of update date not allowed in batch
Correct English Text: Reorganization of update data is not allowed in batch

Armed with the knowledge that this was infact just a regular update that wasn't allowed in batch, and had nothing at all to do with the date, I referenced note 67014 which basically states that you shouldn't really do update reorganizations at all. And I could now easily argue the fact that someone apparently forgot to delete the SAP_REORG_UPDATERECORDS job from the list of standard jobs in SM36. So I deleted all scheduled jobs, and deleted the job definition from the list of standard jobs, as suggested in note 16083.

Isn't it nice when SAP actually has notes for all of the stuff ? Even though sometimes there's a really long path leading there ;)

Performance tuning on homemade views

I normally get assigned the performance related problems in any organization I have ever worked in, and frequently I need to create new indexes, change programming and similar things.
The other day, however, I ran into an issue whereby a simple notification in SAP altering the attached repair order was extremely slow.
I ran a ST05 trace and found the following:

SELECT STATEMENT ( Estimated Costs = 5.717 , Estimated #Rows = 1 )

       14 COUNT STOPKEY
          Filter Predicates

           13 HASH JOIN
              ( Estim. Costs = 5.717 , Estim. #Rows = 1 )
              Estim. CPU-Costs = 671.874.273 Estim. IO-Costs = 5.672
              Access Predicates

               10 HASH JOIN
                  ( Estim. Costs = 4.260 , Estim. #Rows = 21 )
                  Estim. CPU-Costs = 653.871.901 Estim. IO-Costs = 4.217
                  Access Predicates

                   5 NESTED LOOPS
                     ( Estim. Costs = 2 , Estim. #Rows = 21 )
                     Estim. CPU-Costs = 20.973 Estim. IO-Costs = 2

                       2 TABLE ACCESS BY INDEX ROWID QMEL
                         ( Estim. Costs = 1 , Estim. #Rows = 1 )
                         Estim. CPU-Costs = 4.877 Estim. IO-Costs = 1

                           1 INDEX SKIP SCAN QMEL~0
                             Search Columns: 2
                             Estim. CPU-Costs = 3.059 Estim. IO-Costs = 0
                             Access Predicates

                       4 TABLE ACCESS BY INDEX ROWID COBRB
                         ( Estim. Costs = 2 , Estim. #Rows = 21 )
                         Estim. CPU-Costs = 16.096 Estim. IO-Costs = 2

                           3 INDEX RANGE SCAN COBRB~1
                             Search Columns: 2
                             Estim. CPU-Costs = 3.899 Estim. IO-Costs = 0
                             Access Predicates

                   9 VIEW index$_join$_002
                     ( Estim. Costs = 4.255 , Estim. #Rows = 250.747 )
                     Estim. CPU-Costs = 621.161.707 Estim. IO-Costs = 4.214

                       8 HASH JOIN
                         Access Predicates

                           6 INDEX FAST FULL SCAN AUFK~ZWR
                             ( Estim. Costs = 1.764 , Estim. #Rows = 250.747 )
                             Estim. CPU-Costs = 82.670.583 Estim. IO-Costs = 1.759
                             Filter Predicates
                           7 INDEX FAST FULL SCAN AUFK~HOB
                             ( Estim. Costs = 1.941 , Estim. #Rows = 250.747 )
                             Estim. CPU-Costs = 76.894.097 Estim. IO-Costs = 1.936

               12 TABLE ACCESS BY INDEX ROWID AFKO
                  ( Estim. Costs = 1.456 , Estim. #Rows = 88 )
                  Estim. CPU-Costs = 10.379.050 Estim. IO-Costs = 1.456

                   11 INDEX SKIP SCAN AFKO~P
                      ( Estim. Costs = 1.448 , Estim. #Rows = 88 )
                      Search Columns: 1
                      Estim. CPU-Costs = 10.309.628 Estim. IO-Costs = 1.447
                      Access Predicates Filter Predicates

I immediately looked at the code snippet and found a single select statement which looked quite innocent. It just selected an order number from its notification number over a view. However, the view was custom. So I decided to have a look. It joined some of the heaviest tables in a manufacturing system, like ours. QMEL and AUFK, and doing so via COBRB and AFKO.
this in itself isn't much of a problem. But I was wondering why there were a lot of joins and skip scans rather than nested loops and unique scans.Also I really wanted to avoid the fast index scan.
It turned out that the view didn't have the client specified. So the primary key was not unique, making the statement look for a lot of entries in a lot of clients most of which didn't have the data i needed.
so I simply added the client (MANDT) to the list of fields. And hey presto, my index skip scans were gone...




However, I still had a lot of index join and hash joins I didn't like the looks (or costs) of. So I reasoned that join conditions in the view were also equally unable to decide on what data to use, so I decided to extend the client number throughout the join condition, thusly:


I had high hopes, and they were fullfilled.  My execution plan now looked a lot better: the hash joins were gone and replaced with normal nested loops. The index join was gone entirely. I was in fact rather happy.


SELECT STATEMENT ( Estimated Costs = 6 , Estimated #Rows = 1 )

       13 COUNT STOPKEY
          Filter Predicates

           12 NESTED LOOPS

               10 NESTED LOOPS
                  ( Estim. Costs = 5 , Estim. #Rows = 1 )
                  Estim. CPU-Costs = 45.471 Estim. IO-Costs = 5

                   8 NESTED LOOPS
                     ( Estim. Costs = 4 , Estim. #Rows = 3 )
                     Estim. CPU-Costs = 35.473 Estim. IO-Costs = 4

                       5 NESTED LOOPS
                         ( Estim. Costs = 2 , Estim. #Rows = 3 )
                         Estim. CPU-Costs = 21.185 Estim. IO-Costs = 2

                           2 TABLE ACCESS BY INDEX ROWID QMEL
                             ( Estim. Costs = 1 , Estim. #Rows = 1 )
                             Estim. CPU-Costs = 4.877 Estim. IO-Costs = 1

                               1 INDEX UNIQUE SCAN QMEL~0
                                 Search Columns: 2
                                 Estim. CPU-Costs = 3.059 Estim. IO-Costs = 0
                                 Access Predicates

                           4 TABLE ACCESS BY INDEX ROWID COBRB
                             ( Estim. Costs = 2 , Estim. #Rows = 3 )
                             Estim. CPU-Costs = 16.308 Estim. IO-Costs = 2
                             Filter Predicates

                               3 INDEX RANGE SCAN COBRB~1
                                 Search Columns: 2
                                 Estim. CPU-Costs = 3.899 Estim. IO-Costs = 0
                                 Access Predicates

                       7 TABLE ACCESS BY INDEX ROWID AUFK
                         ( Estim. Costs = 1 , Estim. #Rows = 1 )
                         Estim. CPU-Costs = 4.763 Estim. IO-Costs = 1
                         Filter Predicates

                           6 INDEX RANGE SCAN AUFK~HOB
                             Search Columns: 2
                             Estim. CPU-Costs = 3.059 Estim. IO-Costs = 0
                             Access Predicates

In fact, the estimated costs were now at roughly 1/1000th of the original costs. Translated into user time, this meant a decrease from 90 seconds wait time to less than 1 second for this one select statement..

The morale of the story ? Remember that you're working in a client, and that the client number is a part of the key. Regardless if you have only 1 production client, 500 QA clients,  or anywhere in between. there are still a few clients reserved for SAP use (000, 001 and 066), so a unique key means you HAVE to include your client in your views, when the client is also a part of the key of the tables you are creating a view over.

What is "netweaver"

I recently started a new job. As part of my first assignments I was asked to participate in the "installation of netweaver". and I was puzzled. Very puzzled in fact, because in my mind the first netweavers were released in 2004. And I was certain the company wasn't THAT far behind in their release strategy.

So I googled "netweaver", and I looked at my result list. Apparently, the "what is netweaver" is very high on the list of results. So I summized that many people who do not work with SAP daily, might not know what netweaver is. So to avoid further confusion, I created a powerpoint presentation explaining what netweaver is.

From SAPs perspective it's presented as such:

In this picture, the Application Platform is the ABAP and JAVA stack systems that are usually a part of the business suite systems, the Process Integration is the PI system (more or less), Information Integration is a slew of systems and technologies to present stored data differently, like BI, TREX etc. And the People Integration is all of the "user generated data" technology, like mobility, portal etc.

The original idea from SAP was to create a technology in which you could seamlessly move data between systems, SAP or not. Not unlike what was later released as the XI (now PI) system. But in reality, the entire business suite is today a part of the netweaver concept. Anything newer than 4.7 will be "netweaver enabled".
If you think of the SAP business suite as an office pack, where ERP, SRM etc. are the products (like word excel etc) then netweaver will be the API/ language you use to communicate between the products. External tools can also do this, most frequently you will find people describing .NET and websphere as such tools, but in reality many tools exists that supports the netweaver integration.

It ended up with one of my colleagues handing me the installation guide for the software they were talking about. It was from "Netweaver Application Server Java 7.3".... So the confusion was apparently caused by SAP using one of their buzzwords just a slight bit too much.... Again :D

MSSQL error 14262 on a SAP CCMS job

I got some weird calls on this error in my system log on several SAP systems:

BY  2 Database error 14262 at EXE
BY  0 > [Microsoft][SQL Native Client][SQL Server]The specified
BY  0 > @job_name ('SAP CCMS Check Database XXX
BY  0 > [20120102153140-6-060000]') does not exist.

Everything worked, so noone really bothered to fix the problem, untill it started to wake up too many people at really odd hours (one of those people being me who likes to sleep at night), so I decided to investigate....
After searching a lot, and talking to a lot of people I found out that apparently this job is run as a part of the DBchecks that DBAcockpit needs to do, one of the DBCC jobs. The downside seems to be that a new job is scheduled every once in a while (at least once a year until you implement a fix for the ccms_check_db_hist_YYYY.txt file issue), and the old jobs doesn't seem to "go away" from MSSQL even though the job has been deleted from within SAP.

so I investigated a little bit, and SAP has a solution in note 1413688. But starting to create and run huge scripts on our databases seems like a very poorly thought out solution to what clearly must be a simple problem (namely that the job isn't deleted correctly).
So I went in search of the cause of the error. In MSSQL server management, I did find a lot of interesting jobs:



Lo and behold. There was a job that matched my error, as I expected. Now, the job was disabled (by our very competent sysadmin who also created a nice, shiny new job that did all the work it was supposed to do, which is why everyone ignored the error), so I was wondering why the old job showed up in my system log, but more interesting was, why didn't the other disabled jobs do the same ?
After a cursory examination of the jobs on a few systems with the same problem, I found a commonality: Apparently the job owner(s) was set as a user that didn't have ora_dba privileges (!)
So I changed the owner in the job properties, and hey presto, problem gone....







Now I completely understand that ideally, the jobs shouldn't even be checked from within SAP, and of course they should be deleted from the DB when they're deleted within SAP, but this way fixed my problem in a hurry, and without invoking any huge alterations in my system, so I was happy. Today I'll enjoy some nice weather, and I'll spend another day wondering who changes the ownership of a running database, because I know that question will make me quite unhappy...

automating SAP/Oracle backups on windows systems

The other day I was looking at a windows/Oracle flavor that had to get a new backup system up and running.
While I normally wouldn't want to run Oracle on windows, I nevertheless had no choice in the matter here.

So my first step would be to ensure that I dont start a backup if one is already running. On a unix system I'd have to either create a lockfile that tells me that the backup is running, or I'd have to do some serious grep'ing in the processlist, because one thing unix is good at is running a lot of databases on one huge server. Unfortunately, windows isn't very good at that. So I decided to use that in my favor.
I noticed that each system only had one database instance. Which meant that I could simply look for the presence of my executable in the tasklist, and skip all of my steps if I "got lucky".... (ok, so I really should return an error message somewhere, but I'll fix that later). With brbackup it got as simple as this:

REM first determine if a backup is running - I shouldn't run a backup if it is already started.....
tasklist /FI "IMAGENAME eq brbackup.exe">NUL | find /I /N "brbackup.exe">NUL
if %ERRORLEVEL%==0 goto :errormsg
REM if we reach this point program brbackup.exe is not running and we can continue

And now I'm off to figure out how to implement the "/ as sysdba" privilege when my script is being called by a service. Because services are definitely one thing windows excels at being bad at ;)

Sometimes windows is a funny thing

Once in a while I run across a few oddities, I think most of us do, in my daily tasks. One of them was this one....
A filesystem with the SAP workfiles was full, and I had to do some cleanup to get things going. Just deleting the recyclebin didn't really cut it, so I had to deelte some files.
Going to the workdir and selecting the oldest files, will usually net me a couple of outdated files I can delete, so I tried to delete these and got this funny error from windows:

  

Apparently I need to delete some files in order to be able to delete files ;)

Fortunately the fix is easy, the error actually means that I cannot send the files selected to the recyclebin. So simply clicking shift+delete will work.

Meanwhile I'm still working on some automated cleanup tasks.... It would seem there's still systems around here that need it. And now I have even more reason to laugh at Microsofts error messages....

finding contents of hidden unix files

I've never really gotten the hang of which environment file contains what when fixing environment variables on a unix system. Well, I do know, I just spend way too much time doing this stuff, because there's always a lot of places I miss.

So while I normally can simply grep for the contents of a file, grepping for the contents of a hidden file, however, is slightly more tricky. Selecting .* doen't really seem to cut it with the find or grep commands. Luckily I found a way to cheat it.....

grep 'string' ./.*

This cheats grep into searching the local directory . for files of type .*
So now I can just grep for my variablename and find all occurances in the profile files without actually having to think about it.....