Wanted: SAP manuals in ePub format

Every time SAP comes out with a new set ofSybase SAP pdf manuals, the meta data has to be corrected. Often the stored titles, description, etc are wildly wrong. Very sloppy and unprofessional for a mega corp the size of SAP.

The ePub book format has been out for many years and has many features that make it tablet, phone, PC, whatever friendly. Reading a SAP manual at night? No problem, change the font color to white on black so you don’t wake your spouse. The font is too small? No problem, choose a larger or different font. You can’t do any of that with a PDF. Try reading the ASE Admin guides on a 4″ iPhone. I dare you. You might as well pour salt in your eye sockets.

Share Button

Sybase ASE: A simple way to let a user backup a database using a stored procedure

I wrote this simple method to backup a database using a stored procedure. This allows for a user to restore a database with no knowledge of the dump/load commands. Related to Sybase ASE: A simple way to let a user restore a database using a stored procedure

use sybsystemprocs
go

if exists (select 1 from sybsystemprocs..sysobjects where name = "sp_dump_userdb")
	drop procedure sp_dump_userdb
go

CREATE PROCEDURE dbo.sp_dump_userdb
@dbName varchar(100) IN
AS 
BEGIN
	if (@dbName in ('master', 'tempdb', 'tempdb2', 'tempdb3', 'sybsystemprocs', 'sybsystemdb'))
	begin
		print "sp_load_userdb only works with user databases."
	end 
	else
	if exists (select 1 from master..sysdatabases where name = @dbName)
	begin
		DECLARE @DAYofMonth smallint
		DECLARE @dbNamePath varchar(255)
		DECLARE @out varchar(255)

		select @DAYofMonth = datepart(day, getdate())
		select @dbNamePath = "/backups/user_backups/" + @dbName + "_" + convert(varchar(10), @DAYofMonth) + ".dmp"
		select @out = "Backing up database '" + @dbName + "' to " + @dbNamePath
		print @out

		dump database @dbName to @dbNamePath with init,compression=3

		/*  Remove old dump files (older than 2 weeks) */
		exec xp_cmdshell "find /backups/user_backups -mtime +14 -exec rm {} \;"

		print "Backup complete!"
	end 
	else
	begin
		select "'" + @dbName + "' is an unknown database.  Please verify name."
	end 
END
go
exec sp_dump_userdb jf_test
Backing up database 'jf_test' to /some_dir/jf_test_5.dmp
Backup Server session id is: 95. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 4.41.1.1: Creating new disk file /some_dir/jf_test_5.dmp.
(1 row affected)
Backup Server: 6.28.1.1: Dumpfile name 'jf_test131560B97B' section number 1 mounted on disk file '/some_dir/jf_test_5.dmp'
Backup Server: 4.188.1.1: Database jf_test: 600 kilobytes (1%) DUMPED.
Backup Server: 4.188.1.1: Database jf_test: 1134 kilobytes (1%) DUMPED.
Backup Server: 4.188.1.1: Database jf_test: 18242 kilobytes (5%) DUMPED.
Backup Server: 4.188.1.1: Database jf_test: 31492 kilobytes (11%) DUMPED.
Backup Server: 4.188.1.1: Database jf_test: 38072 kilobytes (19%) DUMPED.
Backup Server: 4.188.1.1: Database jf_test: 38416 kilobytes (36%) DUMPED.
Backup Server: 4.188.1.1: Database jf_test: 38930 kilobytes (61%) DUMPED.
Backup Server: 4.188.1.1: Database jf_test: 45854 kilobytes (75%) DUMPED.
Backup Server: 4.188.1.1: Database jf_test: 46204 kilobytes (92%) DUMPED.
Backup Server: 3.43.1.1: Dump phase number 1 completed.
Backup Server: 3.43.1.1: Dump phase number 2 completed.
Backup Server: 3.43.1.1: Dump phase number 3 completed.
Backup Server: 4.188.1.1: Database jf_test: 46390 kilobytes (100%) DUMPED.
Backup Server: 3.42.1.1: DUMP is complete (database jf_test).
 xp_cmdshell                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              
 --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

(0 rows affected)
Backup complete!
(return status = 0)
Share Button

Sybase ASE: A simple way to let users restore a database using a stored procedure

I wrote this simple method to restore a database using a stored procedure. This allows for a user to restore a database with no knowledge of the dump/load commands. Related: Sybase ASE: A simple way to let users restore a database using a stored procedure

use sybsystemprocs
go

if exists (select 1 from sybsystemprocs..sysobjects where name = "sp_load_userdb")
	drop procedure sp_load_userdb
go

CREATE PROCEDURE dbo.sp_load_userdb
@dbName varchar(100) = NULL, 
@DAYofMonth smallint = NULL
AS 
BEGIN
	if (@dbName is NULL or not exists select 1 from (master..sysdatabases where name = @dbName))
	begin
		print "Please specify a database and day of month to restore from."
		print "sp_load_userdb d1folio1, 15"
	end
	else
	if (@dbName in ('master', 'tempdb', 'tempdb2', 'tempdb3', 'sybsystemprocs', 'sybsystemdb'))
	begin
		print "sp_load_userdb only works with user databases."
	end 
	else
	if (@DAYofMonth is NULL or @DAYofMonth < 1 or @DAYofMonth > 31)
	begin
		DECLARE @cmd varchar(255)

		print "Dump file not found!"
		select @cmd = "find /some_dir/user_backups -name " + @dbName + "_*" + " -type f -exec basename {} \;"
		print @cmd
		exec xp_cmdshell @cmd
	end
	else
	begin
		if (db_name() = "master")
		begin
			DECLARE @dbNamePath varchar(255)

			exec kill_user_connections @dbName
			select @dbNamePath = "/some_dir/user_backups/" + @dbName + "_" + convert(varchar(10), @DAYofMonth) + ".dmp"
			load database @dbName from @dbNamePath
			online database @dbName
		end 
		else
			print "Please run sp_load_userdb from the master database."
	end 
END
go
exec sp_load_userdb jf_test, 5
-----------------------------------------------------------------------
There is no user connections in database jf_test

------------------------------------------------------------------------------------
Please log out if you currently connected to database jf_test
Backup Server session id is: 68. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 'jf_test131560B97B' section number 1 mounted on disk file '/somedir/jf_test_5.dmp'

(1 row affected)
Backup Server: 4.188.1.1: Database jf_test: 25480 kilobytes (4%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 53770 kilobytes (10%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 98316 kilobytes (18%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 141328 kilobytes (27%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 184848 kilobytes (35%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 227860 kilobytes (43%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 271380 kilobytes (52%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 314392 kilobytes (60%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 357912 kilobytes (69%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 387100 kilobytes (74%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 430620 kilobytes (83%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 473120 kilobytes (91%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 516640 kilobytes (99%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 518178 kilobytes (100%) LOADED.
Backup Server: 4.188.1.1: Database jf_test: 518186 kilobytes (100%) LOADED.
Backup Server: 3.42.1.1: LOAD is complete (database jf_test).
Caution:  You have set up this database to include space on disk 36 for both data and the transaction log.  This can make recovery impossible if that disk fails.
Started estimating recovery log boundaries for database 'jf_test'.
Database 'jf_test', checkpoint=(224579, 10), first=(224579, 10), last=(224579, 11).
Completed estimating recovery log boundaries for database 'jf_test'.
Started ANALYSIS pass for database 'jf_test'.
Completed ANALYSIS pass for database 'jf_test'.
Started REDO pass for database 'jf_test'. The total number of log records to process is 2.
Completed REDO pass for database 'jf_test'.
Use the ONLINE DATABASE command to bring this database online; ASE will not bring it online automatically.
Started estimating recovery log boundaries for database 'jf_test'.
Database 'jf_test', checkpoint=(224579, 10), first=(224579, 10), last=(224579, 11).
Completed estimating recovery log boundaries for database 'jf_test'.
Started ANALYSIS pass for database 'jf_test'.
Completed ANALYSIS pass for database 'jf_test'.
Recovery of database 'jf_test' will undo incomplete nested top actions.
Database 'jf_test' is now online.
(return status = 0)
exec sp_load_userdb
Please specify a database and day of month to restore from.
sp_load_userdb d1folio1, 15
(return status = 0)
Share Button

Big things afoot at Sybase, Inc.

Sybase

It’s coming…

48 hours from now something big is coming!

Share Button

It’s here! The very first issue of MyDatabases!

MyDatabases Volume 1 Issue 1 (July 2008)

MyDatabases Volume 1 Issue 1

Geek Spotlight:  Michael Peppler
Sybase ASE on Ubuntu 8.04
Multicore Processors
VMware Virtualization
Logical Process Manager

Share Button

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

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

Share Button

Looking for a bug fix release schedule for ASE?

Many of you may not know that Sybase openly lists the release schedules for ASE 12.5 and 15.0. The release schedules list not only when the estimated release date of the patches, but the list of bug fixes that are expected to be in each patch.

Targeted ASE 12.5.x Release Schedule and CR list Information

Targeted ASE 15.0 Release Schedule and CR list Information

How cool is that? Sybase has been providing this information for a number of years as you can still see the release schedule for the old 12.0 releases!

A word of caution: The release schedule and the bugs listed to be fixed in the patches are tentative and are not ‘set in stone’. Sometimes during testing issues are found with bug fixes (it might cause other issues) or some other thing might come up that might delay the bug fix or release of the patch.

Share Button

Sybase ASE Backup Server ‘compress::X::’ vs ‘with compression = X’ syntax

Starting in ASE 12.5.2, Sybase added the with compression = X syntax, prior releases required the compress::X:: syntax. What is the difference?

In releases prior to 12.5.4 and 15.0.2, the two were functionally the same. Since 12.5.4 and 15.0.2, they are different:

dump database model to "compress::3::/tmp/model.dmp"

ASE tells the backup server to load the zlib library and dump the database model to the file /tmp/model.dmp through the compress filter. The entire data stream is compressed. If you look at the dump file in a hex editor, then you will see the entire file is compressed.

dump database model to “/tmp/model.dmp” with compression = 3

ASE tells the backup server to load the zlib library and dump database model to the file /tmp/model.dmp but only the data volume is actually compressed. If you look at the dump file in a hex editor, you will see the first two volumes are mostly empty data (binary 0’s) but the embedded third volume is compressed.

You need to understand that whenever you dump a database to a file using this syntax, there are three volumes that are dumped. Two are administrative and the third is the data of your database.

In ASE 12.5.4 and 15.0.2, the Archive Database functionality was added

ASE requires that the archive file, your database dump file, is dumped with the “with compression = X” format, if it is compressed, as it needs access to the administrative volumes.

So, how does this affect us as DBAs? If you dump a database with ‘with compression = X’ on 12.5.4 esd5, you will NOT be able to load it into 12.5.3 esd 5 without using the 12.5.4 backup server. This has bitten us in the past when we attempted to load the customers’ database dumps.

Don’t forget to look at Sybase’s manual from time to time ;-).

Share Button

903 error when attempting to load a database

Scenario: Disaster Recovery Test :

  1. Unix group restores the filesystems and creates the necessary raw devices for Sybase ASE (12.5.4 esd 5 64bit on Solaris)
  2. We manually build the master (dataserver ….) and change the sort order to no_case iso_1 so we can load the master backup
  3. We manually update sysdatabases.status for load (32) and sysdatabases.status2 for don’t recover (16) for the databases we wish to recover as we can’t load the database since the databases are marked suspect. restart, etc. Note that this is the only way for us to load the other databases without recreating each database (which can take hours of build time that we don’t have)
  4. Dataserver starts properly noting that the databases are marked for load and don’t recover. We are now able to load the database backups.
  5. Load database results in a 903 on an index for a sysobjects before the rpc is sent to the backupserver to start the backup – which doesn’t exist because the raw devices haven’t been written to yet. This occurs only in the v12.5x versions and doesn’t appear to occur in the v15 series.

DR Test involves a ‘virgin’ machine and SAN. As this was during a disaster recovery test, no errorlogs are available (no outside access) and the physical machines have been recycled for other DR tests.

Workaround: set sysdatabases.status to 320 and drop the database using “dbcc dbrepair(<dbname>, dropdb)”… then recreating the databases manually. Ed Barlow‘s sp__revdb is a life saver 🙂

I’ve opened a case with Sybase Technical Support but I’m not very hopeful they will be able to reproduce the issue.

UPDATE!  SOLUTION:  The status2 column wasn’t being updated in the script file.  Damn.  Looks like a typo in the slightly modified script.
As there is much confusion within TechSupport as to why this method is necessary let me explain:

The time required to perform a “dbcc dbrepair(, dropdb)” and a “create database …. for load” can take hours or even days depending on the size of the databases and the speed of the devices. This is in no way acceptable for a real disaster recovery environment.

One might argue, “Why not just use Repserver?” (or equivalent?) Well, that’s all fine and dandy but what happens if your DR site and the primary are gone (flood, fire, terrorism, etc)?  You have to restore at an unknown DR site (we have dozens of *potential* DR sites) that you normally wouldn’t have access to.

I realize that this scenario is as foreign from what the TSEs are trained for at Sybase TechSupport as you can get. However, the old ways of a single DR site are long gone. Good or bad, depends on your point of view.

Share Button

How to install a Sybase patch when it says Sybase isn’t installed

When you try to install a Sybase patch for Openclient, ASE, ASIQ or a myriad of other Sybase products, you may receive an error such as:

“ASE 12.5.4 is NOT installed”

This can appear if you delete the $SYBASE/installed and/or the $SYBASE/uninstall directories. (Why would you delete those directories? They are not needed for the operation of any Sybase product, therefore can be deleted for space reasons.)

So how to install the patch? Set INSTALL_ALL_PATCH to “1″ prior to running setup:

export INSTALL_ALL_PATCH=1

Share Button