Jun 26

How to install and run Sybase Adaptive Server Enterprise 12.5.4 and 15.0x on Ubuntu Linux 8.04 JEOS (Hardy Heron) using chroot environment

ASE, Backup Server, Databases, How To, Linux, OS, Replication Server, Sybase 2 Comments »

Because Sybase has yet to address the GLIBC (LD_POINTER_GUARD) issue with any of their products on Linux, we are forced to find alternative ways of getting Sybase software to run on modern Linux distribution.  In this situation, we will be using the Ubuntu 8.04 Just Enough OS (Ubuntu JEOS) Linux distribution.

After installing Ubuntu 8.04 Server JEOS on the physical or virtual machine, download the Ubuntu 7.04 Server iso image.  Why Ubuntu 7.04?  Ubuntu 7.04 is the last Ubuntu release that has an old enough GLIBC that Sybase software will work with.

% sudo debootstrap feisty /home/sybase file:///media/cdrom0

  • Change the home directory of the sybase user to be /dbms/sybase by editing the /etc/passwd file

% sudo cp /etc/resolv.conf /home/sybase/etc/resolv.conf

% sudo cp /etc/hosts /home/sybase/etc/hosts

% sudo cp /etc/passwd /home/sybase/etc/passwd

% sudo cp /etc/shadow /home/sybase/etc/shadow

% sudo `cat /etc/apt/sources.list | sed -e ’s/hardy/feisty/g’ > /home/sybase/etc/apt/sources.list`

% sudo mount –bind /dev /home/sybase/dev

% sudo mount –bind /proc /home/sybase/proc

% sudo mount -t devpts none /home/sybase/dev/pts

% sudo mount –bind /tmp /home/sybase/tmp

% sudo aptitude update

% sudo aptitude upgrade

% sudo apt-get install locales dialog wget debconf devscripts gnupg

% sudo aptitude install libstdc++5 libstdc++6 libaio

  • If you’re installing v12.5x software, you will need to install an even older copy of libstdc++ (not needed for v15 or higher software):

% aptitude install libstdc++2.10-glibc2.2

  • Install Sybase software into /dbms/sybase (as root in the chroot environment which you should still be in)

% sudo cp /dbms/sybase/SYBASE.sh /dbms/sybase/SYBASE.env

% sudo echo DSQUERY=MYASE >> /dbms/sybase/.SYBASE.env

% sudo echo sa_password > /dbms/sybase/.sapwd

% sudo chmod go-rwx /dbms/sybase/.sapwd

% sudo chmod u-wx /dbms/sybase/.sapwd

% sudo chown -R sybase /dbms/sybase

  • Install the sybase_chroot script into /usr/local/bin and make it executable by root
  • Install the sybase_ase rc script into /etc/init.d and make it executable by root
  • Exit the chroot environment by typing ‘exit’
  • create a symbolic link outside of the chroot environment to make /home/sybase/dbms appear as /dbms.  This will allow you to access the Sybase software, notably OpenClient, as a normal user outside of the chroot environment.

% sudo ln -s /home/sybase/dbms /dbms

You are now able to start Sybase ASE using sudo /etc/init.d/sybase_ase start and stop Sybase ASE using sudo /etc/init.d/sybase_ase stop.  Starting Sybase Replication Server, OpenServer, or similar Sybase software is simply a matter of copying the /etc/init.d/sybase_ase and tweaking the script copy.

If you feel this is way complicated and Sybase should just fix their software, let them know.  Please point them to this page and refer to CR455393.

UPDATE: The GLIBC issue is fixed in v15.0.2 esd 4.  It doesn’t seem to be working for everyone though.  More information is needed about those systems where esd 4 doesn’t resolve the matter.  No fix is available for 12.5x from Sybase.

UPDATE: Keep in mind, that the Sybase ASE 15.0.2 esd 4 patch is available only to Sybase customers with a current maintenance contract with Sybase.

Listen to this podcast Listen to this podcast
Jun 20

An old Sybase TechWave presentation of mine from 2004: Migration to Linux

ASE, Databases, Linux, OS, Sybase, TechWave 1 Comment »

ASE103 Adaptive Server Enterprise Migration to Linux:  PDF, Power Point, flash, OpenOffice

So much has changed in four years!  I see so many things that the presentation doesn’t address..  I/O schedulers for instance aren’t even touched upon, nor is virtual memory management to any degree.

I did a number of TechWave presentations over the years that I’ve misplaced.  I know of at least one ASE on Linux that isn’t in the archives.  No idea where that one went to…  I feel old ;-)

Listen to this podcast Listen to this podcast
Jun 06

The first issue of “My Databases” coming next week!

ASE, Databases, IBM DB2, Microsoft, My Databases, MySQL, Oracle, Postgres, SQL Server, SQLite, Sybase 3 Comments »

As many of you know, I’ve been working on a free magazine regarding various database systems (dbms) called My Databases.  I hope to have multiple authors in future issues covering all sorts of open source and proprietary databases.

I should have the first issue done Sunday night.  I’m using OpenOffice, Scribus, Gimp, and Inkscape.

Listen to this podcast Listen to this podcast
May 19

Synchronize a Sybase ASE database sysusers with master..syslogins

ASE, Sybase No Comments »

Not sure who was the original author of this short synchronization script but kudos to whomever it is!

isql -Usa -S server -D my_db -i sync_users.sql

sync_users.sql

EXEC SP_CONFIGURE "allow updates", 1
go

/* delete names not found in master syslogins */
DELETE FROM sysusers
WHERE suid > 1
 and uid < =16383
 and uid > 1
 and name not in
  (SELECT name FROM master..syslogins)
go

/* update sysusers and resync all uids from the existing suids */
IF exists(SELECT 1
   FROM sysobjects
   WHERE name = "sysusers_holding"
    and type = "U")
DROP TABLE sysusers_holding
go

CREATE TABLE sysusers_holding (
 id NUMERIC(6,0) IDENTITY PRIMARY KEY,
 suid INT null,
 uid INT null,
 gid INT null,
 name CHAR(30) null,
 environ VARCHAR(255) null)
go

/* populate the holding table */
INSERT INTO sysusers_holding (suid, gid, name, environ)
SELECT suid, gid, name, environ
 FROM sysusers
 WHERE suid > 1
  and uid < = 16383
  and uid > 1
go

/* update the uids. */
DECLARE syncuser CURSOR FOR
 SELECT name, uid
 FROM sysusers_holding
 FOR UPDATE OF uid, suid
go

DECLARE @name CHAR(30), @uid INT, @suid INT
 SELECT @suid = 9999
  OPEN syncuser
  FETCH syncuser INTO @name, @uid
  WHILE (@@sqlstatus != 2)
  BEGIN
   IF exists(SELECT 1
    FROM master..syslogins
    WHERE name = @name)
    /* got the name - update the uid */
   BEGIN
    SELECT @suid = suid
     FROM master..syslogins
     WHERE name = @name
    UPDATE sysusers_holding SET uid = @suid, suid = @suid WHERE CURRENT OF syncuser

    IF @@ERROR != 0
    BEGIN
     ROLLBACK TRANSACTION
     BREAK
    END

    SELECT @suid = 9999
   END
  FETCH syncuser INTO @name, @uid
  END
 CLOSE syncuser
go

DEALLOCATE CURSOR syncuser
go      

/* now move records over */
DELETE FROM sysusers
 WHERE suid > 1
  and uid < =16383
  and uid > 1
go

INSERT INTO sysusers
 SELECT suid, uid, gid, name, environ
 FROM sysusers_holding
go

/* sp_helpuser will show any mis-matches) */
EXEC SP_HELPUSER
go

EXEC SP_CONFIGURE "allow updates", 0
go

Listen to this podcast Listen to this podcast
May 01

tricky: Getting Sybase ASE 12.5.4 to run on Linux PowerPC 64bit

ASE, Databases, Linux, OS, Sybase 1 Comment »

For the past few days, we’ve been struggling with getting Sybase’s ASE DBMS to start on our new Linux PowerPC (a virtual machine running on top of AIX). We were able to install the software with no problems. We weren’t able to get past the __gxx_personality_v0 error:

symbol lookup error: /opt/ibmcmp/lib64/libibmc++.so.1: undefined symbol: __gxx_personality_v0

The error is a linking issue with IBM’s Visual Age runtime (a c/c++ runtime). I was able to confirm with Sybase that ASE will NOT run with Visual Age 8.x or 9.x and that it requires the 7.0 runtime. I downloaded the 7.0 runtime from IBM’s website but still received the same error.

ASE’s linking looked okay however:

[sybase@sybase ASE-12_5]$ ldd -v bin/dataserver
libbtsymbols.so => /sybase/ASE-12_5/lib/libbtsymbols.so (0×0000008000002000)
librt.so.1 => /lib64/tls/librt.so.1 (0×0000008000647000)
libm.so.6 => /lib64/tls/libm.so.6 (0×0000008081560000)
libnsl.so.1 => /lib64/libnsl.so.1 (0×0000008000674000)
libdl.so.2 => /lib64/libdl.so.2 (0×0000008081510000)
libibmc++.so.1 => /opt/ibmcmp/lib64/libibmc++.so.1 (0×00000080006a4000)
libpam.so.0 => /lib64/libpam.so.0 (0×00000080006b7000)
libpthread.so.0 => /lib64/tls/libpthread.so.0 (0×00000080006d3000)
libaio.so.1 => /lib/libaio.so.1 (0×00000080006ff000)
libc.so.6 => /lib64/tls/libc.so.6 (0×00000080812f0000)
/lib64/ld64.so.1 (0×00000080812b0000)
libaudit.so.0 => /lib64/libaudit.so.0 (0×0000008000711000)

I worked with Sybase & IBM to track down the cause but it was IBM’s Brian Shen that was able to identify the culprit. We need to preload the libstdc++.so.5 library:

export LD_PRELOAD=/usr/lib64/libstdc++.so.5

Once we did that, we verified that the library was being picked up:

[sybase@sybase install]$ ldd $SYBASE/$SYBASE_ASE/bin/dataserver
/usr/lib64/libstdc++.so.5 (0×0000008000002000)
libbtsymbols.so => /sybase/ASE-12_5/lib/libbtsymbols.so (0×0000008000146000)
librt.so.1 => /lib64/tls/librt.so.1 (0×000000800078c000)
libm.so.6 => /lib64/tls/libm.so.6 (0×0000008081560000)
libnsl.so.1 => /lib64/libnsl.so.1 (0×00000080ef740000)
libdl.so.2 => /lib64/libdl.so.2 (0×0000008081510000)
libibmc++.so.1 => /opt/ibmcmp/lib64/libibmc++.so.1 (0×00000080007ba000)
libpam.so.0 => /lib64/libpam.so.0 (0×00000080ef6a0000)
libpthread.so.0 => /lib64/tls/libpthread.so.0 (0×00000080007cd000)
libaio.so.1 => /lib/libaio.so.1 (0×00000080007fa000)
libc.so.6 => /lib64/tls/libc.so.6 (0×00000080812f0000)
libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0×000000800080b000)
/lib64/ld64.so.1 (0×00000080812b0000)
libaudit.so.0 => /lib64/libaudit.so.0 (0×00000080ef6c0000)

If you receive the following error, you can safely ignore it:

ERROR: ld.so: object ‘/usr/lib64/libstdc++.so.5′ from LD_PRELOAD cannot be preloaded: ignored

Don’t forget to set up your environment to include the IBM Visual Age runtime:

export LD_LIBRARY_PATH=/opt/ibmcmp/lib64:${LD_LIBRARY_PATH}
export PATH=$PATH:/opt/ibmcmp/lib64

Listen to this podcast Listen to this podcast
Apr 24

35383: DBD::Sybase calls the broken sp_tables stored procedure

ASE, DBI, Databases, Perl, Sybase No Comments »

35383: DBD::Sybase calls the broken sp_tables stored procedure

Centos 5
Linux dba-dev1 2.6.22.9-default #1 SMP Mon Oct 1 11:26:48 CDT 2007 i686
athlon i386 GNU/Linux

Perl v5.8.8 built for i386-linux-thread-multi
DBD::Sybase 1.07 but 1.08 also has the problem.

Due to Sybase CR 497173, sp_tables can not be relied upon to retrieve
table information correctly for ASE 15.

(ASE 15 stored procedure “sp_tables” doesn’t work with SQL UDFs)

We need to query the system tables instead. Obviously, the below code
would work in v12.0 and higher. A temp table could probably be used for
prior to 12.0 for the TABLE_TYPE to be filled correctly.

$ diff ../DBD-Sybase-1.07/Sybase.pm Sybase.pm
218c218
< my $sth = $dbh->prepare(”sp_tables $table, $schema, $catalog,
$type”);
—

> # my $sth = $dbh->prepare(”sp_tables $table, $schema, $catalog,

$type”);
219a220,238
> my $sth = $dbh->prepare( q{
> select TABLE_QUALIFIER = db_name()
> , TABLE_OWNER = u.name
> , TABLE_NAME = o.name
> , TABLE_TYPE =
> case o.type
> when “U” then “TABLE”
> when “V” then “VIEW”
> when “S” then “SYSTEM TABLE”
> end
> , REMARKS = NULL
> from sysobjects o
> , sysusers u
> where o.type in (’U', ‘V’, ‘S’)
> and id >99
> and o.uid = u.uid
>
> });
>

Listen to this podcast Listen to this podcast
Apr 24

35381: SQL::Translator::Parser::DBI::Sybase needs to strip the carriage return from stored procs/views sql

ASE, Databases, Perl, Sybase 1 Comment »

35381: SQL::Translator::Parser::DBI::Sybase needs to strip the carriage return from stored procs/views sql

Centos 5
Linux dba-dev1 2.6.22.9-default #1 SMP Mon Oct 1 11:26:48 CDT 2007 i686
athlon i386 GNU/Linux

Perl v5.8.8 built for i386-linux-thread-multi
SQL::Translator::0.09000

Sybase ASE will often store the SQL text for stored procedures and views
with the carriage return embedded (\r). We need to strip that out else
we will receive “^M” in the output.

Sybase_orig.pm Sybase_new.pm

108a109

>

109a111,112

> $stuff->{view}->{$_->[0]}->{text} =~ s/\r//g
> if (defined ($stuff->{view}->{$_->[0]}->{text}));

155a159,161
> $stuff->{procedures}->{$_->[0]}->{text} =~ s/\r//g
> if (defined ($stuff->{procedures}->{$_->[0]}->{text} ));
>

Listen to this podcast Listen to this podcast
Apr 24

SQL::Translator::Parser::DBI::Sybase bug (35380) causes script to fail when sp_helpindex doesn’t return a result set

ASE, DBI, Perl, Sybase 1 Comment »

35380: SQL::Translator::Parser::DBI::Sybase fails when sp_helpindex is empty

Centos 5
Linux dba-dev1 2.6.22.9-default #1 SMP Mon Oct 1 11:26:48 CDT 2007 i686
athlon i386 GNU/Linux

Perl v5.8.8 built for i386-linux-thread-multi
SQL::Translator::0.09000

Sybase ASE’s sp_helpindex will normally return a result set containing
the column “INDEX_NAME” which parse() requires. When no index exists,
sp_helpindex returns a error string similar to “Object does not have any
indexes.” but no result set.

The following workaround simply wraps the selectall_hash with an eval,
if the eval results in no error, then try to use the hash.

$ diff Sybase_orig.pm Sybase_new.pm
252,259c252,259
< my $h = $dbh->selectall_hashref("sp_helpindex
< $table_info->{TABLE_NAME}", ‘INDEX_NAME’);
< foreach (values %{$h}) {
<                     my $fields = $_->{‘INDEX_KEYS’};
< $fields =~ s/\s*//g;
<                     my $i = $table->add_index(
< name   =>
< $_->{INDEX_NAME},
—
>                 my $h;
>                 eval { $h = $dbh->selectall_hashref("sp_helpindex $table_info->{TABLE_NAME}", ‘INDEX_NAME’) };
>                               unless ($@) {
>                   foreach (values %{$h}) {
>                       my $fields = $_->{‘INDEX_KEYS’};
>                       $fields =~ s/\s*//g;
>                       my $i = $table->add_index(
>                                                 name   => $_->{INDEX_NAME},
262,263c262,263
< if ($_->{‘INDEX_DESCRIPTION’} =~ /unique/i) {
< $i->type(‘unique’);
—
>                       if ($_->{‘INDEX_DESCRIPTION’} =~ /unique/i) {
>                           $i->type(‘unique’);
265,276c265,277
< # we could make this a primary key if there
<                         # isn’t already one defined and if there
<                         # aren’t any nullable columns in thisindex.
<
<                         if (!defined($table->primary_key())) {
< $table->primary_key($fields)
< unless grep {
<                                     $table->get_field($_)->is_nullable()
< } split(/,\s*/, $fields);
<                         }
<                     }
<                 }
—
>                           # we could make this a primary key if there
>                           # isn’t already one defined and if there
>                           # aren’t any nullable columns in thisindex.
>  
>                           if (!defined($table->primary_key())) {
>                               $table->primary_key($fields)
>                                   unless grep {
>                                       $table->get_field($_)->is_nullable()
>                                   } split(/,\s*/, $fields);
>                           }
>                      }
>                  }
>               }
 

Listen to this podcast Listen to this podcast
Apr 23

Is Sybase’s ASE SQL92 (ANSI SQL2) or SQL99 (ANSI SQL3) compliant?

ASE, Databases, IBM DB2, Microsoft, MySQL, Oracle, Postgres, SQL Server, SQLite, Sybase No Comments »

ASE implements a subset of SQL 92 and isn’t 100% compliant with the SQL 92 standard (no DBMS on the planet is btw).

SQL99 compliance isn’t seriously being looked at by the major commercial DBMS vendors. Disregarding the fact the the SQL standards aren’t all they are cracked up to be, the vendors have too much invested in their own proprietary SQL variants (and other components) to be 100% compliant. If they were 100% compliant with the SQL92/99/whatever standard, then wholesale migrations from one vendor to another would take place.

I believe as time goes forward the opensource DBMSs (PostgreSQL, MySQL, etc) may become far more compliant with the standards than the commercial vendors as vendor lock in doesn’t mean as much to them.

Look into what the vendors (Oracle, MS, IBM,etc) are saying what constitutes as “compliance”. Ask each vendor what parts of the SQL99 standard they will be implementing and which parts they won’t be. If any vendor says that they are 100% compliant with the SQL92 or SQL99 ANSI standard, then that particular person is lying to you. Granted, that person may have been told their DBMS was 100% compliant and believes it. An honest vendor says that they comply with features X,Y and Z of the SQL 92 or SQL 99 standards.

Personally, I have found no significant movement by any of the commercial DBMS vendors to implement the SQL99 standard. So far it has just been lip service IMHO.

Listen to this podcast Listen to this podcast
Apr 16

Replacing Sybase ASE’s Historical Server: Using OpenSource

ASE, DBI, Databases, Linux, OS, Perl, SQLite, Sybase No Comments »

I’m working on a Historical Server replacement.  I need to check with my employer as to whether I can open source it or not but the guts of it consist of:

  1. Perl POE/DBD::Sybase for the daemon/ASE db connections (using FreeTDS instead of OpenClient)
  2. SQLite for the local storage (way lower overhead than ASE or SQL Anywhere)
  3. Perl Catalyst / Template::Toolkit web front end with REST web services

All of this goes into a VMWare Appliance running Ubuntu 7.10 Server… a drop-in solution that can even be run on a laptop.  I’m hoping that my employer lets me release it as then I can zip it up and put it on sourceforge so everyone can benefit.

Listen to this podcast Listen to this podcast
Apr 11

ASE 15.0.2 esd 4 is out!

ASE, Databases, Sybase No Comments »

Andrew Sigmund over on the ISUG sybase-l mailing list, let us know that Sybase released ASE 15.0.2 esd 4 today. Go and get them! :)

Listen to this podcast Listen to this podcast
Apr 09

ASE 15 stored procedure “sp_tables” doesn’t work with SQL UDFs

ASE, Sybase 2 Comments »

The ASE 15 stored procedure sp_tables doesn’t work with SQL UDFs. sp_tables has the udfs coming back as system tables.

repro:
1) create a sql udf in a db
2) run sp_tables, you will see the UDF as a “SYSTEM TABLE”

DBADEV1.sybase_dba.1> sp_tables;
table_qualifier table_owner table_name table_type remarks
--------------- ----------- ---------- ---------- -------
sybase_dba dbo DATE_FORMAT SYSTEM TABLE NULL

In the above example, the DATE_FORMAT UDF is the one at Porting MySQL’s date_format function to Sybase ASE 15.0.2

I’ve opened a case with Sybase to get this fixed - I’ll post the CR # when I get it.

UPDATE:  Sybase assigned CR 497173 to this :)

Listen to this podcast Listen to this podcast
Apr 08

Flourish 2008 Conference Roundup

ASE, Events, Gadgets, Linux, News, OS, Perl, Sybase, Training, Windows, misc 1 Comment »

This year’s Flourish Conference was held at the University of Illinois in downtown Chicago (UIC). The Flourish web site describes Flourish as: Flourish 2008 Conference

The goal of the Flourish Conference is to promote the use and adoption of Free Libre Open Source Software (FLOSS) by demonstrating the practical applications of FLOSS in the business and academic world. While everyone is familiar with businesses that focus on creating non-free proprietary software, the same cannot be said for those who specialize in developing and supporting FLOSS software, and an Open Culture.

I missed a part of Bruce Perens’ Innovation Goes Public presentation due toBruce Perens Chicago’s closing several sidewalks that go over or under the tristate. I’m not quite sure I agree with him that the road a head for open source is all rosey. Perhaps I misunderstood him but it seemed to me that the idea of simply giving the source of an application to the public would allow the programmers to still be paid via the support/service agreements.  This may work for some applications, but for others there wouldn’t be a sufficient number of support contracts to financially support the programmers Personally, I prefer open source but I will also pay for commercial proprietary software. I use what works.

The company I work for, Sungard Investment Systems, uses open source for many internal projects. One such product uses VMware and Linux for a rPathportable monitoring system so I was highly interested in rBuilder Online from rPath. I attended the Appliance Development and Conary session presented by Stephanie Watson from rPath. I’m currently investigating on how well and easy it is to make a VMware appliance that would house a Sybase ASE server (DBMS) with no unnecessary os files. If the testing turns out well, I will ask my fellow Sybase ASE DBAs whether Sybase need certify it or whether we are ‘on our own’. The monitoring machine itself really doesn’t need Sybase ASE on it but since we have the license….

Dr. John Waclawsky, from Motorolla, gave a feel good presentation about the state of communication between wireless gadgets. The Revolution at the Edge of the Network Abstract. Short version: things are going to get very interesting in the next year :)

James Gray, from IBM, presented IBM’s Open Source Strategy - Turning Threat into Opportunity. WhileJames Gray, IBM essentially a case study of how IBM has interacted as a semi-liason between the business executives and the open source community. IBM, to me, has seemed to be stuck in a love/hate relationship with open source and a dislike/hate relationship with the Free Software Foundation. That being said, it is necessary to give IBM credit giving credence to the open source community with the business executives. The IT folks no longer had to quietly use open source in the business but were free to use it extensively in many places. Linux, in particular, is no longer the wild west in many CTOs’ eyes but a integral part of their business. Tony Wong of Chicago Perl Mongers

I met Tony Wong in the Chicago Perl Mongers ‘booth’. We discussed the upcoming Yet Another Perl Conference (YAPC::NA 2008) that is being held in Chicago and the horrible documentation of the Catalyst MVC web framework. I informed him that the only way to learn Catalyst was the new book Catalyst by Jonathan Rockway (ISBN: 978-1-847190-95-6). I also warned him that the book should have gone through another round of proofreading prior to being published due to excessive example code errors, etc. I playfully suggested that Joshua McAdams be tormented because he wasn’t able to make it Friday morning.

The panel discussion on Friday afternoon was highly entertaining and a bit of a kangaroo court for Bobby Bobby Moore of MicrosoftMoore of Microsoft. I do not believe Mr. Moore was expecting Bruce Perens to repeatedly ask very pointed and informed questions. Moore did an excellent job of remaining cool and listening with an open mind to the criticism of Microsoft as a company. He explained that, yes, Microsoft did some not so nice things in the past but they are attempting to work with the open source community. Bruce Perens, Jon “Maddog” Hall, and James Bottomley brought up the pending MS Office OpenXML standard format issue where much of the format is proprietary and the published format won’t actually be used by Microsoft. Moore conceded that it was unlikely that Microsoft would use the published format as-is but the Office developers would likely base the actually used format off of the published format. Perens, Hall, Bottomley and James Gray all expressed their opinions that this was very typical of Microsoft. (side note: Microsoft has been accused of having the attitude of Embrace, Extend, Eliminate when it comes to industry standards.)

Perens, Hall and James Bottomley, as well as members of the audience, pummeled both James Gray of IBM and Bobby Moore of Microsoft for their companies’ approach and support of software patents.

Unfortunately, I was only able to attend the first day as my mother was in town from North Dakota to visit my three month old daughter (and the rest of us). If only I could have cloned myself… Don’t forget to check out the Flickr photo group for the Flourish 2008 Conference in Chicago!

Listen to this podcast Listen to this podcast
Feb 08

ISUG: Just sent off the outstanding ASE enhancements list to Sybase Product Manager

ASE, Databases, ISUG, Sybase No Comments »

As you may remember, I’m a board director in the International Sybase Users’ Group (ISUG).  I’ve recently been appointed the ‘chair’ of the Enhancements Committee.  The committee currently has two people: Chris Pollach and me.  We are responsible for maintaining the enhancements list on the ISUG website and ensuring that the enhancements are reviewed/commented upon by Sybase Product Management.

I’ve just sent off the current list of Enhancements (for ASE) to the PM.  Other products will follow shortly — this was just the first product I emailed off so don’t worry :)

Listen to this podcast Listen to this podcast
Jan 21

ASE 15 Cluster Edition Webcast

ASE, Databases, Events, News, Sybase No Comments »

INTRODUCING: ASE 15 Cluster Edition

January 29, 2008
1 p.m. ET / 10 a.m. PT

Please join us on Tuesday, January 29th to learn how ASE 15 Cluster Edition:

· Maximizes resource utilization and reduces infrastructure costs
· Ensures continuous application availability with shared-disk clusters
· Improves performance through transparent load balancing
· Simplifies administration tasks with automatic workload management

PRESENTER BIOS:

David Jonker
Sr. Product Marketing Manager; Sybase, Inc.

As a Senior Product Marketing Manager at Sybase, David Jonker is focused on marketing strategy for the Sybase ASE and Mirror Activator product lines. His career includes more than 10 years as a software developer and product manager before joining the Sybase ASE and Mirror Activator marketing team.

Mr. Jonker is an active speaker and writer about Sybase products and technologies. Some of his recent speaking engagements include CTIA Wireless IT & Entertainment, Gartner Wireless & Mobile, and Best Practices for Building Frontline Applications seminar series. He holds a Bachelor of Applied Science degree in Systems Design Engineering from the University of Waterloo.

Peter Thawley
Senior Director/Architect; Sybase, Inc.

Peter brings over 22 years of experience in software architecture and engineering, product management, and worldwide product & technology sales & services spanning both systems software products and business applications. He recently rejoined Sybase as a Senior Director and Architect in the Technology Evangelist team for Data Management products where he leads a group of engineers who help customers understand and implement Sybase’s technology stack.

Peter’s experience with Sybase products dates to 1990 when, working for a Sybase customer, he led projects to replace a back office brokerage trading system that accounted for 4% of the NYSE daily share volume using Sybase ASE on OS/2 & AIX, Open Server, and PowerBuilder. Peter is a household name with Sybase customers seen by his reputation for standing-room-only presentations at conferences around the world.

Don’t forget to sign up for the webcast!

Listen to this podcast Listen to this podcast
Dec 31

Having trouble locking shared memory in physical RAM? (Sybase ASE & Linux)

ASE, Databases, Linux, OS, Sybase No Comments »

Depending on which distribution of Linux you’re running, it seems that locking Sybase ASE’s shared memory into physical RAM can be a bit tricky.  Simply specifying “lock shared memory” in ASE (sp_configure) isn’t enough.

In many distributions, you can simply add the line into /etc/security/limits.conf to enable locking of shared memory up to 2GB for the sybase user:

sybase  -    memlock 2147483648

Unfortunately this doesn’t always work so you need to specify the memlock limit in the $SYBASE/$SYBASE_ASE/install/RUN_server file by adding the following above the dataserver line:

ulimit -l  2147483648

Whether you are able to lock the memory or not will depend on your environment (you do have enough free memory right?).   You can check if the memory is locked by simply checking the /proc/<pid of a dataserver process>/status:

grep VmLck /proc/<pid>/status

VmLck:   1140000 kB

Keep in mind that you won’t have a 1 to 1 memory size match between the locked memory (VmLck) and the total memory VmSize allocated simply because Sybase ASE only locks the shared memory and not the process:

VmPeak:  1182520 kB
VmSize:  1182516 kB
VmLck:   1140000 kB
VmHWM:   1155340 kB
VmRSS:   1155340 kB
VmData:    11488 kB
VmStk:        84 kB
VmExe:     23364 kB
VmLib:      3944 kB
VmPTE:      1168 kB

If all else fails, your distribution may need to run ASE as root to lock the shared memory.

Listen to this podcast Listen to this podcast
Dec 24

Sybase ASE 15.0.2 MDA Poster!

ASE, Databases, Sybase No Comments »

Thanks goes to Jeff Tallman for creating the Sybase ASE 15.0.2 MDA poster. Don’t forget to check out the legend for the poster as well!

Listen to this podcast Listen to this podcast
Dec 11

Duplicate rows in sysusers??

ASE, Databases, Sybase No Comments »

If you’re like me, you’ve run into lots of different issues with the Sybase ASE DBMS over the years. Today, Drew Montgomery looked at what happens when the 12.5.x -> 12.5.4 upgrade goes bad:

If you receive the 8419 error on sysusers after applying ASE 12.5.4 (or any other upgrade for that matter) such as the following:

01:00000:00472:2007/12/10 14:46:03.86 server Error: 8419, Severity: 20, State: 3
01:00000:00472:2007/12/10 14:46:03.86 server Could not find index descriptor for objid 10, indid 3 in dbid 13.
01:00000:00472:2007/12/10 14:46:03.88 kernel ************************************
01:00000:00472:2007/12/10 14:46:03.88 kernel SQL causing error : select * from sysusers
01:00000:00472:2007/12/10 14:46:03.88 kernel ************************************
01:00000:00472:2007/12/10 14:46:03.88 server SQL Text: select * from sysusers
01:00000:00472:2007/12/10 14:46:03.89 kernel curdb = 13 tempdb = 9 pstat = 0×10000
01:00000:00472:2007/12/10 14:46:03.89 kernel lasterror = 8419 preverror = 0 transtate = 1

Fear not because Drew has you covered with a simple script that will fix the problem. Note that it involves a dump and load of the affected database but you could just restart the ASE server if you don’t mind the down time:

SP_CONFIGURE "allow updates", 1
go
USE db_with_8419
go

SELECT * INTO tempdb..temp_sysusers FROM sysusers
go

SELECT uid, cnt= COUNT(*)
  INTO tempdb..temp_uid
  FROM tempdb..temp_sysusers
 GROUP BY uid
 HAVING COUNT(*) > 1
go

SELECT s.uid, s.suid
  INTO tempdb..temp_fixuid
  FROM tempdb..temp_sysusers s, tempdb..temp_uid t
 WHERE s.uid = t.uid
go

SELECT uid, MIN(suid) suid
  INTO tempdb..temp_rmuid
  FROM tempdb..temp_fixuid
 GROUP BY uid
go

DELETE tempdb..temp_fixuid
  FROM tempdb..temp_fixuid f, tempdb..temp_rmuid r
 WHERE f.uid = r.uid
   and f.suid = r.suid
go

ALTER TABLE tempdb..temp_fixuid ADD cntr INT IDENTITY
go

DECLARE @max_uid INT
SELECT @max_uid = MAX(uid)
  FROM tempdb..temp_sysusers
 WHERE suid > 16300

UPDATE sysusers
   SET uid = @max_uid + cntr
  FROM sysusers s, tempdb..temp_fixuid f
 WHERE s.uid = f.uid
   and s.suid = f.suid
go

USE master
go

DUMP DATABASE db_with_8419 TO "compress::N::/DumpLocation/dumpfilename.dmp"
go
LOAD DATABASE db_with_8419 FROM "compress::/DumpLocation/dumpfilename.dmp"
go
online DATABASE db_with_8419
go

SP_CONFIGURE "allow updates", 0
go

Bau-jen Liu over at Sybase came up with the following method but it does require rebooting ASE twice:

SP_CONFIGURE "allow updates", 1
go
UPDATE ..sysobjects
SET sysstat = 115, type = "U"
WHERE name = "sysusers"
go

Shutdown ASE, reboot, and log back in as a user with sa_role.

USE dbname
go

CREATE UNIQUE INDEX ncsysusers2 ON sysusers(uid)
go

The above CREATE INDEX command may fail due to the presence of duplicate key rows in sysusers. If this occurs, recreate the index as non-unique first:

CREATE INDEX ncsysusers2 ON sysusers(uid)
go

Now identify any duplicates that may exist in sysusers:

SELECT uid, name, COUNT(*)
FROM sysusers
GROUP BY uid
HAVING COUNT(*) > 1
go

Remove any duplicates found using the DELETE command and try the CREATE INDEX again:

CREATE UNIQUE INDEX ncsysusers2 ON sysusers(uid)
go

Now, clean up after yourself:

UPDATE ..sysobjects
SET sysstat = 113, type = "S"
WHERE name = "sysusers"
go

SP_CONFIGURE "allow updates", 0
go

Sybase has created bug # 298620 for this issue.

Listen to this podcast Listen to this podcast
Nov 12

Update on the missing Podcast: Art of ASE Performance & Tuning

ASE, Databases, News, Sybase No Comments »

Over on the ISUG forums, I asked Stefan Karlsson whether the Art of ASE Performance & Tuning podcast was abandoned as there hasn’t been a new episode since July 2007.   Stefan has been busy with high priority tasks within Sybase and playing around with different software packages to improve the quality of the podcast.  He has asked us to be patient.

Listen to this podcast Listen to this podcast
Oct 19

FW: Microsoft SQL Server - Sort Values Ascending But NULLS Last

ASE, Databases, Microsoft, SQL Server, Sybase 3 Comments »
This is a frequent request in newsgroups and fora. People want to sort the column in ascending order but don’t want the NULLS at the beginning.Oracle has this syntax:

ORDER BY ColumnName NULLS LAST;

SQL Server does not have this. But there are 2 ways to do this. The first one is by using case and the second one by using COALESCE and the maximum value for the data type in the order by clause.

Read more at SQL Server Code,Tips and Tricks, Performance Tuning. These two methods will also work on Sybase ASE if you use a derived table or temp table instead of the memory table. :)

Listen to this podcast Listen to this podcast
Previous Entries