spugbrap’s blog

Today I needed to rename a column in a Sybase ASE 12.5.1 database, and couldn’t seem to find the right syntax. My first few search results gave me syntax that was not actually relevant for my database. Two different pages led me to believe that some variation of this might work:

ALTER TABLE [table_name] RENAME [old_column_name] TO [new_column_name]

But that did not work for me. Upon closer inspection, I noticed that those two pages were for Sybase ASA, not ASE. I refined my search terms a bit, and found this:

sp_rename ‘[table_name].[old_column_name]’,'[new_column_name]’

Thanks to Rob Verschoor for posting this on dbforums.com!

I’m a programmer, not a DBA, but I do have to be able to perform basic database configuration and maintenance tasks. One thing that’s come up, a couple times, over the past few years, is a transaction log getting full.

There are a couple of reasons this can happen:

• poorly written queries that run for a very long time
• applications neglecting to end transactions, when they encounter error conditions

Here are a few useful commands I’ve found that deal with transaction logs (Note: I’m using Sybase ASE 12.5.1):

check for long-running transactions (found here):
use master;

SELECT * FROM syslogshold
WHERE dbid = db_id(’MyDbName’);

transaction log space free:
SELECT CONVERT(CHAR,
(lct_admin('logsegment_freepages', 4) -
lct_admin('reserved_for_rollbacks', 4)) * 2)

transaction log space total:
SELECT SUM(size)
FROM master..sysusages
WHERE dbid = db_id('MyDbName')
AND segmap & 4 = 4 -- logsegment

dump the transaction log (clears it out, so things can get moving again!):
***WARNING*** Use this command at your OWN RISK! Please do some additional reading before executing the following statement, so that you fully understand the implications. The transaction log is there for a reason, and, from what I understand, you should not tamper with it unless you know what you’re doing. On the occasions that I’ve used this, it was a last resort. It solved my problems, with no ill effects, but it might not solve yours!

dump tran myDbName with truncate_only;
commit;

For additional information/reference, Sybase’s online books sometimes come in handy. When trying to find out how to dump transaction logs, I referred to their dump transaction page.

Another site I’ve found useful*, on numerous occasions, is Sybase 101. I found a related tip, there, for tips, there, for Dealing With Server Failure, which addresses what to do when your log fills up completely, such that you can’t even restart the server.

*Beware: I just noticed that Sybase 101 has become less useful than it used to be, because useful pages are automatically redirecting to one main start page. If they were just trying to prevent deep linking from the outside world, I could understand this behavior a little more; but it’s even happening when I click other links from their own site! Navigation works fine with javascript disabled, though.

I recently had to reinstall Tomcat on my development server at work, because my previous installation was having major issues for unknown reasons. When I finished installing it, and deploying my webapps to it, I ran into this annoying character set issue (which I was not getting with the previous installation).

I could swear I’d seen this message before, and several of my co-workers thought they recognized it, too, but nobody knew what to do about it. Every time I started the tomcat service, it was spitting out these three stack traces, for every webapp, in my exception log:

Class: net.sourceforge.jtds.jdbc.ConnectionJDBC2
Method: loadCharset
SQL STATE: 2C000
SQL ERROR CODE: 0
STACKTRACE:
java.sql.SQLException: Charset cp850/Cp850 is not supported by the JVM.
[...]

Class: org.apache.catalina.valves.JDBCAccessLogValve
Method: start
EXCEPTION WRAPPER MESSAGE:
[ERROR] ContainerBase.addChild: start:
STACKTRACE:
LifecycleException:
java.sql.SQLException: Charset cp850/Cp850 is not supported by the JVM.
[...]

Class: org.apache.catalina.core.ContainerBase
Method: addChildInternal
EXCEPTION WRAPPER MESSAGE:
[ERROR] Error deploying web application archive [foo].war
STACKTRACE:
java.lang.IllegalStateException: ContainerBase.addChild: start:
LifecycleException: java.sql.SQLException:
Charset cp850/Cp850 is not supported by the JVM.
[...]

NOTE: These stack traces only show the top line and context info, to avoid cluttering up the blog post too much. [View full stack traces here]

To troubleshoot this, I first tried cleaning out all the already-compiled classes/jars from my source code tree, and rebuilding/redeploying all the webapps, but the same behavior continued. Next, I wiped out my whole source code tree, checked it all out from CVS again, and rebuilt/redeployed all the webapps. Still got the stupid cp850 exceptions.

Before, during, and after all of that rebuilding stuff, I spent hours doing web searches on various search engines, tweaking my queries, and not turning up much of anything.

At one point, it seemed like it had to be a problem with my Sybase ASE 12.5 database configuration. I didn’t like the sound of that, though, because it’s much easier to reinstall an app server and redeploy my webapps, than it is to rebuild my database and recreate the necessary test data.

I thought maybe our build script wasn’t deploying the right versions of all the jars that needed to be deployed, so I slowly started copying the jars from my backup of my previous tomcat installation, and copying all the configuration files, and so forth. Eventually, my entire tomcat directory tree had been replaced with the backed-up tomcat installation, but the evil cp850 exceptions still lingered!

Now it was *really* looking like a database issue! I searched all the files on the machine for ‘cp850′ (case-insensitive search), and turned up nothing useful. When I searched the windows registry, I found one thing that was intriguing:

KEY: HKEY_LOCAL_MACHINE\
SOFTWARE\SYBASE\Server\
[MyServerName]_BS\Parameters\
VALUE NAME: Arg5
VALUE TYPE: REG_SZ
VALUE DATA: -Jcp850

I asked my co-workers to check their registries, but they had the same thing, and their servers were behaving just fine. Besides, this was just the backup server (BCKServer), not the actual SQLServer. So that was a dead end.

I poked around for a while longer, and *finally* figured out that it was the JVM that the Tomcat service was configured to use. When installing it, I had chosen the default JVM that it had selected, because it looked right.

The installer defaulted to the JVM that was installed with the Java plugin for web browsers and such:
C:\Program Files\Java\jre1.5.0_06\bin\server\jvm.dll

But I needed to use the one that was installed with the JDK I compiled with:
C:\Program Files\Java\jdk1.5.0_06\jre\bin\server\jvm.dll

I believe the reason for this is because when I downloaded the JDK, it either only came in one package, which included international support, or I happened to choose the package that included international support. For the Java plugin download, though, I must have selected the US/English-only package, because it was smaller or something.

That was a nightmare to track down, and I hope that by posting this, I can save someone else the time and trouble!

This is extremely simple, but it’s something that I always forget, and I’m always happy to find it in my text file of miscellaneous useful snippets:

SELECT @@version

I’ve had a sticky note on my cube wall for almost a year now, but it keeps falling off, and the information is not useful in my everyday work now anyways, so I decided to post it here and throw the sticky note away.

First, the formulas:
Pixels (x-axis) = PBU / 4.571429
Pixels (y-axis) = PBU / 4

Second, some 4NT aliases that use the formulas:
pxc=echos %@eval[%@int[%@eval[%1/4.571429]]+
%@if[%@eval[%@int[%@eval[(%1/4.571429-
%@int[%@eval[%1/4.571429]])*100]]] gt 50,1,0]] >clip:

pyc=echos %@eval[%@int[%@eval[%1/4]]+
%@if[%@eval[%@int[%@eval[(%1/4-
%@int[%@eval[%1/4]])*100]]] gt 50,1,0]] >clip:

Finally, an explanation:
I was doing some PowerBuilder to Jasper Reports conversion manually last year, and this meant creating new reports in iReport (a GUI front-end for JasperReports), and creating all the various column headers/data fields/groupings/etc. I could have just eyeballed it, visually comparing the old report to the new one, and being content with “looks about the same”, but I’m a bit too anal for that.

I wanted to make each element the exact same size and place it at the exact same {x, y} coordinates. The problem was that the old version of PowerBuilder I was using would only give me these attributes (width, height, x, y) in some funky unit of measurement called PowerBuilder Units (PBU). I researched PBU for quite a while, hoping to find a formula to make the conversion simple. But, from what I read, these units are designed to adjust to the platform that the report is being created or viewed on, or something like that.

There was a way to make PowerBuilder show me the values in pixels, but it involved a multi-step process with multiple mouse clicks, menu selections, etc. for every element, which was just too annoying. So, I ended up creating a new element in a PowerBuilder report, and giving it dimensions that were powers of 10, so I could then switch to the pixel view one time (for just this one test element), and derive a formula for the unit conversion. I think I created one or two more elements with much higher and much lower values, to make sure the formula was actually linear, and it was.

I ended up coming up with the two formulas (shown above), which I promptly stuck on a sticky note for easy reference. I also made some 4NT aliases (also shown above), to make the calculations more convenient. This allowed me to simply paste the PBU values into my 4NT window, using an alias like: “pxc 375“, and the pixel value would be copied to my clipboard for easy pasting into iReport.

One more thing to note is that, for some reason, some (but not all?!) of the values needed to be reduced to 3/4 scale. I don’t remember exactly why, now, but there was some sort of pattern as to which values required scaling. I had a few more 4NT aliases for these cases:
scale34c=echos %@eval[%@int[%@eval[%1*0.75]]+
%@if[%@eval[%@int[%@eval[(%1*0.75-
%@int[%@eval[%1*0.75]])*100]]] gt 50,1,0]] >clip:

px34=scale34 %@eval[%@int[%@eval[%1/4.571429]]+
%@if[%@eval[%@int[%@eval[(%1/4.571429-
%@int[%@eval[%1/4.571429]])*100]]] gt 50,1,0]]

py34=scale34 %@eval[%@int[%@eval[%1/4]]+
%@if[%@eval[%@int[%@eval[(%1/4-
%@int[%@eval[%1/4]])*100]]] gt 50,1,0]]

I hope this is useful to someone. I wish I could have found these simple formulas posted somewhere last year, even if they required a disclaimer like this one: I will not claim that these formulas are correct in all cases, since I do not completely understand PBUs. But, I can say that they worked for me, for every element on every report that I converted, and the formula was consistent on 3 different machines that I tried it on, which all had different screen resolutions (among other differences).