Why oh why can’t we have PostgreSQL’s CREATE [TEMPORARY] SEQUENCE in Sybase ASE?

I know you can simulate it by creating a temp table with an identity column, delete old rows, getSybase the max, etc but still… PostgreSQL’s create sequence would be very handy…  btw, Oracle has it too

It would allow us to do things like pgTAP with ease..  Check out the slides for pgTAP 🙂

just my opinion… grrrr

I’ve created ISUG enhancement request # 3449 for this.

Share Button

unable to create a SQL UDF in Sybase ASE using a case statement

create function ok (@boolean bit)
returns varchar(2000) as
declare @output_string varchar(2000)
select @output_string = case when @boolean = 1 then "" else "not " end
set @output_string = @output_string + "ok"
return @output_string
end

Msg 156, Level 15, State 2
Server ‘DBADEV1’, Procedure ‘ok’, Line 7
Incorrect syntax near the keyword ‘end’.
I’m trying to write an ASE implementation of TAP. A rudimentary TAP implementation for PostgreSQL is at http://www.justatheory.com/computers/databases/postgresql/introducing_pgtap.html
I’ve been able to use case in SQL UDFs before – see http://froebe.net/blog/2007/10/10/porting-mysqls-date_format-function-to-sybase-ase-1502/
Adaptive Server Enterprise/15.0.2/EBF 15654 ESD#4/P/Linux Intel/Linux 2.4.21-47.ELsmp i686/ase1502/2528/32-bit/FBO/Sat Apr 5 05:18:42 2008
I’m just missing something blindingly simple… I just know it.

Share Button

HOWTO: Fix Sybase ASE: 17283 error when trying to extend a database segment

Did you ever receive the error 17283 “Procedure ‘sp_extendsegment’, Line 182 ‘tempdev1’ is reserved exclusively as a log device.” error? You can get the 17283 error when there is a device fragment that contains only the log segment. Part of the problem is when you extend the log segment onto a device, it will usually drop all other segments so it becomes ‘log only’.

 device_fragments               size          usage                created             free kbytes
 ------------------------------ ------------- -------------------- ------------------- ----------------
 master                                8.0 MB data and log         Dec 16 2004  4:09AM             3376
 tempdev1                           1024.0 MB data and log         Dec 16 2004  7:02AM          1044480
 tempdev1                            256.0 MB data and log         Mar  9 2005 10:49AM           261120
 tempdev1                            256.0 MB log only             Mar  9 2005 10:54AM not applicable
 tempdev1                            488.0 MB data and log         Mar  9 2005 12:10PM           497760
 tempdev1                            256.0 MB data and log         Mar 11 2005  2:03PM           261120
 tempdev1                            244.0 MB data and log         Mar 11 2005  3:09PM           248880
 tempdev1                            200.0 MB data and log         Jun  1 2005 12:33PM           204000
 tempdev1                            200.0 MB data and log         Jul  8 2005  2:55PM           203808
 tempdev1                            200.0 MB data and log         Jul 21 2005  2:54PM           204000
 tempdev1                           1000.0 MB data and log         Feb  6 2006  1:11PM          1020000

So, how to fix this? Well, Sybase doesn’t provide any way to do so without modifying the system tables.

use master
exec sp_configure "allow updates", 1
go
begin tran
go

since we are dealing with tempdb and we want data and log on all the device fragments, we can make a blanket update. We would make the update more selective if we had other requirements (update only one row or something)

update sysusages set segmap = 7 where dbid = db_id("tempdb")

if the number of rows updated exceed the number of rows for the database, issue a ‘rollback’

select segmap from sysusages where dbid = db_id("tempdb")

all good? if so, issue a ‘commit’

commit tran
go
exec sp_configure "allow updates", 0
go

Now that the system table is updated, we need to refresh the dbtable memory structure so that the changes we made become ‘live’

dbcc dbrepair(tempdb, remap)
go

That’s it! It’s fixed and running with the segmaps without having to restart ASE. Wasn’t that easy?

Share Button

Partitioned tables and update statistics

Did you know that if you have a partitioned table, that running your normal update statistics or update index statistics does not update the statistics for the partitions?  Many dbas forget to perform update partition statistics on their partitions.  Sadly, neither Sybase or Rob Verschoor say much about it.  This is rather surprising as Rob is full of useful information about such things.

Of course, you could just run update all statistics but the overhead of maintaining the statistics is a nightmare and I have yet to find any real reason to do so.

Syntax (v12.x): 

update partition statistics table_name  [partition #]

Syntax (v15.x):

update table statistics table_name [partition name]
Share Button

Eliminating Temp Table Usage in sp__dbspace

The sp__dbspace code from Ed Barlow was written 11 years and there are a number of issues that need addressing:

  1. Unnecessary use of temporary tables (#tablename) that can lead to lock contention in the system tables tempdb..sysobjects, tempdb..sysindexes, and tempdb..syscolumns.
  2. It doesn’t handle the logical page size (2k, 4k, 8k, 16k) in an intuitive manner.

For those of you that don’t know, the system variable @@pagesize is the physical page size, which is always 2K.  The @@maxpagesize is the logical page size (2K, 4K, 8K, 16K).  The column size in the master..sysusages table is specified in logical pages while the low and high columns of the master..sysdevices table is specified in physical pages.

Original code:

    /* Procedure copyright(c) 1995 by Edward M Barlow */
    /************************************************************************\
    |* Procedure Name:      sp__dbspace                                     *|
    |*                                                                      *|
    |* Author:              EMB                                       *|
    |*                                                                      *|
    |* Description:         Database/log space available/used/utilised      *|
    |*                                                                      *|
    \************************************************************************/
    :r database
    go
    :r dumpdb
    go
    if exists (select *
               from   sysobjects
               where  type = "P"
               and    name = "sp__dbspace")
    begin
        drop proc sp__dbspace
    end
    go
    create procedure sp__dbspace ( @dont_format char(1) = null )
    as
    begin
    declare @log_pgs  float
    declare @used_pgs float
    declare @pct_used float
    declare @db_size  float,@log_size float
    declare @scale  float /* for overflow */
    set nocount on
    select @db_size = sum(size), @log_size=0
            from master.dbo.sysusages u
                    where u.dbid = db_id()
                    and   u.segmap != 4
    /* Just log */
    select @log_size = sum(size)
            from master.dbo.sysusages u
                    where u.dbid = db_id()
                    and   u.segmap = 4
    select id,doampg,ioampg into #tmp from sysindexes
    select @log_pgs = reserved_pgs(i.id, doampg)
    from #tmp i
    where i.id = 8
    select @used_pgs = sum(reserved_pgs(id, doampg) + reserved_pgs(id, ioampg))
    from #tmp
    where id != 8
    /* @scale is number way to convert from pages to K  */
    /* for example -> normally 2K page size so @scale=2 and multipled results */
    select  @scale=d.low/1024
    from    master.dbo.spt_values d
    where   d.number = 1 and d.type = "E"
    having  d.number = 1 and d.type = "E"
    /* Reset If Data & Log On Same Device */
    if @log_size is null
    begin
            select @used_pgs = @used_pgs+@log_pgs,@log_pgs=0,@log_size=0
    end
    select @pct_used=(@used_pgs*100)/@db_size
    if @dont_format is not null
    begin
    select  Name             = db_name(),
            "Data MB"  = str((@db_size*@scale)/1024, 16, 0),
            "Used MB"  = str((@used_pgs*@scale)/1024, 16, 1),
            Percent    = str(@pct_used, 7, 2),
            "Log MB"   = str((@log_size*@scale)/1024, 12, 0),
            "Log Used" = str((@log_pgs*@scale)/1024, 12, 2),
            "Log Pct"  = str((@log_pgs*100)/(@log_size+1), 7, 2)
    end
    else
    begin
    select  Name             = convert(char(12),db_name()),
            "Data MB"  = str((@db_size*@scale)/1024, 13, 0),
            "Used MB"  = str((@used_pgs*@scale)/1024, 14, 1),
            Percent    = str(@pct_used, 7, 2),
            "Log MB"   = str((@log_size*@scale)/1024, 9, 0),
           "Log Used" = str((@log_pgs*@scale)/1024, 9, 2),
            "Log Pct"  = str((@log_pgs*100)/(@log_size+1), 7, 2)
    end
    end
    go
    /* Give execute privilege to users. This can be removed if you only want
       the sa to have execute privilege on this stored proc */
    grant exec on sp__dbspace to public
    go

Modified code with no temp tables:

    /* Procedure copyright(c) 1995 by Edward M Barlow */
    /************************************************************************\
    |* Procedure Name:      sp__dbspace                                     *|
    |*                                                                      *|
    |* Author:              EMB                                       *|
    |*                                                                      *|
    |* Description:         Database/log space available/used/utilised      *|
    |*                                                                      *|
    \************************************************************************/
    :r database
    go
    :r dumpdb
    go
    if exists (select *
               from   sysobjects
               where  type = "P"
               and    name = "sp__dbspace")
    begin
        drop proc sp__dbspace
    end
    go
    create procedure sp__dbspace ( @dont_format char(1) = null )
    as
    begin
    declare @log_pgs  float
    declare @used_pgs float
    declare @pct_used float
    declare @db_size  float,@log_size float
    declare @scale  float /* for overflow */
    set nocount on
    select @db_size = sum(size), @log_size=0
            from master.dbo.sysusages u
                    where u.dbid = db_id()
                    and   u.segmap != 4
    /* Just log */
    select @log_size = sum(size)
            from master.dbo.sysusages u
                    where u.dbid = db_id()
                    and   u.segmap = 4
    select @log_pgs = reserved_pgs(i.id, doampg)
    from sysindexes i
    where i.id = 8
    select @used_pgs = sum(reserved_pgs(id, doampg) + reserved_pgs(id, ioampg))
    from sysindexes i
    where id != 8
    /* @scale is number way to convert from pages to K  */
    /* for example -> normally 2K page size so @scale=2 and multipled results */
    select  @scale = @@maxpagesize / 1024
    from    master.dbo.spt_values d
    where   d.number = 1 and d.type = "E"
    having  d.number = 1 and d.type = "E"
    /* Reset If Data & Log On Same Device */
    if @log_size is null
    begin
            select @used_pgs = @used_pgs+@log_pgs,@log_pgs=0,@log_size=0
    end
    select @pct_used=(@used_pgs*100)/@db_size
    if @dont_format is not null
    begin
    select  Name             = db_name(),
            "Data MB"  = str((@db_size*@scale)/1024, 16, 0),
            "Used MB"  = str((@used_pgs*@scale)/1024, 16, 1),
            Percent    = str(@pct_used, 7, 2),
            "Log MB"   = str((@log_size*@scale)/1024, 12, 0),
            "Log Used" = str((@log_pgs*@scale)/1024, 12, 2),
            "Log Pct"  = str((@log_pgs*100)/(@log_size+1), 7, 2)
    end
    else
    begin
    select  Name             = convert(char(12),db_name()),
            "Data MB"  = str((@db_size*@scale)/1024, 13, 0),
            "Used MB"  = str((@used_pgs*@scale)/1024, 14, 1),
            Percent    = str(@pct_used, 7, 2),
            "Log MB"   = str((@log_size*@scale)/1024, 9, 0),
           "Log Used" = str((@log_pgs*@scale)/1024, 9, 2),
            "Log Pct"  = str((@log_pgs*100)/(@log_size+1), 7, 2)
    end
    end
    go
    /* Give execute privilege to users. This can be removed if you only want
       the sa to have execute privilege on this stored proc */
    grant exec on sp__dbspace to public
    go
Share Button

bit wise operations within ASE are still host dependent

On August 11th in the newsgroup sybase.public.ase.general, Tartampion raised the issue that ASE’s bitwise operations were not platform independent.

When we run the statement "select  0 | 0x00000020" on 2 of 
our servers, we obtain different results
1: sun Solaris
Sybase version: Adaptive Server Enterprise/12.5.3/EBF 13398 ESD#6 ONE-OFF/P/Sun_svr4/OS 5.8/ase1253/1947/32-bit/FBO/Tue Mar  7 04:50:16 2006

select 0 | 0x00000020
---
32

2: Linux: 
Sybase version:Adaptive Server Enterprise/12.5.3/EBF 13400
ESD#6 ONE-OFF/P/Linux Intel/Enterprise Linux/ase1253/1947/32-bit/OPT/Mon Mar  6 20:22:17 2006

select 0 | 0x00000020
-------------
536870912

This causes pb in our application behavior.

I am writing to see if this is a normal behavior or we need
to change configurations to obtain similar results.

All help is well appreciated.

Tartampion.

Sybase’s answer up to now has been that this is expected behavior and the following method should be used instead:
For safe conversions between hexadecimal and decimal use the platform independent inttohex() and hextoint():

select @@version
select  0 | 0x00000020, 0 | convert( int, 0x00000020 ), 0 | hextoint('0x00000020' )

The problem with Sybase’s response is that it is inconsistant with how Sybase pushes ASE as being the same running on any supported platform from the client application.

There is quite a bit of os/hardware specific code within ASE.  This just happens to be one such legacy chunk of code where the bitwise operation is performed in a os specific manner rather than an independent manner. Anthony Mandic, myself and others are pushing for Sybase to correct this legacy operation to be platform independent. No bug # (cr#) exists at this time but we should be receiving one within a few days.

Share Button