Wednesday, February 27, 2013

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...

Monday, February 18, 2013

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 ;)

Tuesday, February 12, 2013

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....

Monday, February 4, 2013

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.....

Tuesday, January 22, 2013

selecting objects in DB2 with a slash in them

Not having worked with DB2 for a while, I found myself in a bind today.
One of the developers had tried installing a package from somewhere, and it created a lot of objects with slashes in them. Like /DEV1/WEIRD_OBJECT and /DEV/WEIRD_OBJECT_V

After an attemt at uninstalling these I was asked to help. And I was kind of puzzled at first about why someone would use a command interpreted character in an objectname. But overcomming this obstacle was nonetheless my task. A simple command db2 "drop table tablename" didn't really do the trick. Looking at the manual just gave me todays first headache. Searching with bing and google returned no viable results, other than a lot of critism about using slashes in objectnames and some helpful hints for Oracle. Had this been oracle, the problem would have been much simpler. Just use double quotations.

I didn't have this option in DB2, because I have to send the command in the double quatation to begin with !

In the end I ended up using single AND double quotations like: db2 "select viewschema,viewname from syscat.views where viewname like '/DEV1/WEIRD%' "
That gave me a nice overview of the views (you can guess the command to view the tables :D ) and then it's just rinse and repeat for the drop commands.....

Headache is still here, but now at least the problem is gone....

Monday, January 14, 2013

Altering SQL procedures in an SAP system

The other day I was stupified by a note from SAP. I had an SQL error 8115

SQL 8.115: [Microsoft][SQL Server Native Client 10.0][SQL server]
Arithmetic overflow error converting numeric to data type varchar.

This was caused by dbacockpit. And I even found a nice note about it, number 1638567, which states that I just had to be on a certain release level (and lets face it, who is ?), but if all else fails, I could just go to transaction SE38 and fix it in the report MSSPROCS.

Now the problem was the this report no longer showed me the MSSQL procedures (!)

But I found that it was still possible to change the procuderes by going to transaction DB02, and expanding the "configuration" view. Under here there's a menuitem "SQL Script Maintenance". Here I found the script I needed "sap_dbcc_opentran", and just pressed change. Voila. 


There was the magic number that I needed to change. And presto.... Gone was the error.
It's still weird though, that SAP doesn't update their notes with relevant information about how to implement their changes. Espcially, if they remove previously available options.

Thursday, December 6, 2012

Reasons for reorganizing an Oracle database

I've been the witness of many discussions about what benefit reorganizing a database has.
First in the days of old, when doing so would cost more more work then what was saved
Second when someone started using indexes and said "now we never need to reorganize our data again"
Again when someone said that reorganizing is just "reclaiming diskspace, and that's pretty cheap now"
There there was the "LMTS will reuse all the diskspace easily"
I've even heard someone use the excuse that: "The data is on a SAN, it will reorganize the data internally", and the latest one: "I'm using data compression, so there will be no wasted space"
I'm pretty sure with the move on SAP HANA, I'll soon hear the argument: "its in-memory computing"

But the fact of the matter is that for all databases, indexed or not,  if there's a lot of changes in the data, it will get jumbled around. So there'll always be a need (how big will depend on the business) to reorganize the data IF one of the following conditions are true:

1) Reporting reads data sequentially.
2) Your index consists mainly of inserts in one side and thus becomes uneven.
3) You need to reclaim diskspace after deleting data
4) Select statements issued can make use of your data being sorted
5) You need to move data around

And there are other conditions too, but these are the ones I most commonly run into.

The reason for (1) is simply that once data has ben entered into an Oracle database, a highwater mark is set. And a sequential read will even borther to read the empty blocks (I have yet to figure out why this behavior is, but I'm sure there's a good reason to be found somewhere). Thus if you remove and insert a lot of data (like say during a BW load) into your database, you may end up with a lot of empty blocks that sometimes get read, when they dont have to. This will cause performance degredation. Arguably, you can also reclaim wasted space, but if you DO have all these changes continually, you'll likely not be saving a lot of space in the long run. Also, it's worth noting that HOW your inserts are made makes a difference as to weather or not the highwatermark is being pushed.
As for (2), one can make the argument that you really only need to reorganize your index. But in reality, an index is just a table too. And an index that is very heavily loaded with data on only one side (say financial postings by date, will likely consist mainly of newer and newer dates), will get slower and slower to use, as your index becomes deeper. Reorganizing the index will save you absolutely no space, but will likely give you a small performance boost. One could also make an argument that data sorted by date can benefit from partitioning. And this is oftentimes true, but that's a matter for another post.
(3) is pretty selfexplanatory. Oftentimes large dataloads, exports or other is run against a database just once. And if the diskspace is needed for other use, there's no reason why we as administrators shouldn't remove the excess empty space, and reclaiming space from the filesystem requires that the datafiles become smaller, for them to be smaller, we need to shrink (and move) the tables, for that to happen, we need to reorganize. This is the case even if you're using datacompression on your data or indices. Because it is passive cmompression. Once data gets deleted or moved, you will still have a hole in your block.
If at all possible, when someone comes to me in advance and tells me of their temporary need, I create a new tablespace on a new LUN, if this is possible, so that I can easily reclaim the space later, and even drop the LUN back into the diskpool.
For performance reasons, it's sometimes a good idea to reorg your data sorted, which leads to (4). Logically, if your select statement makes good use of data being sorted (say with a "between" statement), it would help if your data was sorted by whatever makes sense. The downside to reorganizing this way is that we as administrators needs to know a lot about the application and what querries are being run against it. Because what's good for the goose, might not be good for the gander. Sorting data one way, without looking at how it was previously sorted, might even cause a performance degradation.
Moving data (5) can frequently be done by setting a datafile offline, moving it, and bringing it online again in a new place. But this requires downtime (at the very least on the filelevel). With reorganization tools like Quest livereorg, or using a reasonably new version of Oracle, there's a redefinition package that will allow us as administrators to recreate a table in a different tablespace, easily. Moving individual datafiles (online) is still a problem, but even as painfully slow as it is, you can actually move the data pieces (tables) inside a datafile one at a time. If you need to. Once someone creates a product that can move ONLY the pieces inside a particular datafile, I'll be more than happy to endorse or even use their product. Meanwhile, for me, it's still wishfull thinking whenever I have to (online) move data to a new storage area or similar.

On top of all of this there's the whole issue of read vs write performance. What's more important ? There's the entire cost angle. If I spend 10 hours making the system save y number of CPU cycles/day, or x amount of I/O operations/day, is that profitable ? After a couple of months the users will get used to slow responsetimes anyway....

So in conclusion, yes, I can find many good arguments for reorganizing a database, but none of them are "because someone said it was a good idea". Reorganization is a reasonably big thing to do, and it will impact performance. But if used correctly, it can save your users a lot of time and griefs. Because as a strategic tool, reorganization can increase the performance of your system.

And now I'm off to report on the last reorganization effort :D