SAP ASE: Using unix domain sockets for turbo bulk copying (BCP)

Cory Sane back in March SAP Sybase2013 on SCN wrote about using unix domain sockets for bulk copying of data in/out of SAP Sybase ASE 15.7.

If you don’t know what unix domain sockets are, Thomas Stover over at Tech Deviancy wrote up an excellent Demystifying Unix Domain Sockets post. Highly recommended!
As usual, Wikipedia also has an article.

What is so great about unix domain sockets? You bypass the networking layer completely. This translates into faster communication between bcp (or similar) and ASE. The downside? The bcp file(s) must be on the same host as the ASE instance and your user must have read/write access to the ‘file’.

Using unix domain sockets with a 16k packet size appears to be ideal for the bcp out (1 million rows in the test). Notice the difference of time between using normal (tcp) connection of 5K rows / second compared to 104k rows / second connecting with unix domain socket.

Unix Domain Sockets

tcp - packet size 65024
run #1 : Clock Time (ms.): total = 184821  Avg = 0 (5410.64 rows per sec.)
run #2 : Clock Time (ms.): total = 178612  Avg = 0 (5598.73 rows per sec.)

uds - packet size 2048
run #1 : Clock Time (ms.): total = 10843  Avg = 0 (92225.40 rows per sec.)
run #2 : Clock Time (ms.): total = 11012  Avg = 0 (90810.03 rows per sec.)

uds - packet size 8192
run #1 : Clock Time (ms.): total = 9823  Avg = 0 (101801.89 rows per sec.)
run #2 : Clock Time (ms.): total = 9965  Avg = 0 (100351.23 rows per sec.)

uds - packet size 12288
run #1 : Clock Time (ms.): total = 9735  Avg = 0 (102722.14 rows per sec.)
run #2 : Clock Time (ms.): total = 9745  Avg = 0 (102616.73 rows per sec.)

uds - packet size 16384
run #1 : Clock Time (ms.): total = 9587  Avg = 0 (104307.92 rows per sec.)
run #2 : Clock Time (ms.): total = 9558  Avg = 0 (104624.40 rows per sec.)

uds - packet size 32768
run #1 : Clock Time (ms.): total = 13205  Avg = 0 (75728.89 rows per sec.)
run #2 : Clock Time (ms.): total = 12961  Avg = 0 (77154.54 rows per sec.)

uds - packet size 65024
run #1 : Clock Time (ms.): total = 13254  Avg = 0 (75448.92 rows per sec.)
run #2 : Clock Time (ms.): total = 13179  Avg = 0 (75878.29 rows per sec.)

With unix domain sockets, we come close to DTU speeds without the penalty of exclusive table locks and a DTU process you can’t kill. It really is like pushing the turbo button!

You will see a larger boost with bulk copying out of data than in of data. Why? The network layer is usually not the bottleneck when inserting of data. The bottleneck tends to be more allocating/populating pages in the database or index population if you haven’t dropped the indexes. YMMV

Setting up unix domain sockets is very simple and does NOT require a reboot. Let’s me show you how:

The format of the query master lines you would add to the interfaces file is simple:

query afunix unused //hostname/directory_you_can_write_to/file_name
master afunix unused //hostname/directory_you_can_write_to/file_name

for example:

query afunix unused //myase/dbms/sybase/ASE.socket
master afunix unused //myase/dbms/sybase/ASE.socket

If you want to start the listener without restarting, use sp_listener:

sp_listener 'start', 'afunix://hostname:/directory_you_can_write_to/file_name'

for example:

sp_listener 'start', 'afunix://myase:/dbms/sybase/ASE.socket'
Share Button

SAP Sybase IQ: Index Advisor for a user

There are times when you need individual users the ability to determine what indexes are suggested by the index advisor but you don’t want or are unable to give them full dba access. All you need to do is grant the execute permission on the sp_iqindexadvice stored procedure.

Grant execute on sp_iqindexadvice to <user>;

create table test (col1 int, col2 varchar(10));
insert into test values (1, 'test1');
insert into test values (2, 'test2');
insert into test values (3, 'test3');
insert into test values (4, 'test4');
commit;

SET OPTION index_advisor = 'ON';
SET OPTION index_advisor_max_rows = 100;
commit;

select * from test where col1 =1 ;
commit;

call sp_iqindexadvice ();</user>
Share Button

A fast method of getting space utilization: sp__quickdbspace for SAP Sybase ASE

The sp__quickdbspace procedure relies on accurate metrics provided by free space accounting and/or dbcc checkalloc/tablealloc. It shouldn’t be relied upon on for exact space usage.

Much of the code comes from sp_helpdb from Sybase. er.. SAP.

sp__quickdbspace output:


1>  sp__quickdbspace mydb
2> go
 DBName               data size MB       data free MB       log free MB
 -------------------- ------------------ ------------------ ------------------
 mydb                         5025662.00          703975.00          186260.00
(return status = 0)
1>

sp__quickdbspace code:

if exists (select 1 from sysobjects where name = "sp__quickdbspace")
  drop procedure sp__quickdbspace
go

create procedure sp__quickdbspace 
@dbname varchar(255) = NULL
as
declare @numpgsmb float	/* Number of Pages per Megabyte */
declare @sptlang int
declare @log_free bigint

set nocount on

if @dbname = null
  select @dbname = db_name()

select @numpgsmb = (1048576. / v.low)
from master.dbo.spt_values v
where v.number = 1
and v.type = "E"	

select @sptlang = @@langid

if @@langid != 0
begin
  if not exists (select * from master.dbo.sysmessages where error between 17050 and 17069 and langid = @@langid)
    select @sptlang = 0
  else
    if not exists (select * from master.dbo.sysmessages where error between 17110 and 17119 and langid = @@langid)
      select @sptlang = 0
end

select 
  device_fragments = v.name,
  size = (size / @numpgsmb),
  usage = convert(char(30), m.description),
  created = convert(char(25), u.crdate, 100),
  case
    when u.segmap = 4 then null
    else (convert(bigint, curunreservedpgs(d.dbid, u.lstart, u.unreservedpgs)) / @numpgsmb)
  end "free_mb"
  into #device_usage
from master.dbo.sysdatabases d,
  master.dbo.sysusages u,
  master.dbo.sysdevices v,
  master.dbo.spt_values b,
  master.dbo.sysmessages m
where d.dbid = u.dbid
  and u.vdevno = v.vdevno
  and ((v.status & 2 = 2)  or (v.status2 & 8 = 8))
  and d.name = @dbname
  and b.type = 'S'
  and u.segmap & 7 = b.number
  and b.msgnum = m.error
  and isnull(m.langid, 0) = @sptlang
  
	if exists (select *
	    from master.dbo.sysdatabases d, master.dbo.sysusages u
		where d.name like @dbname
			and d.dbid = u.dbid
			and u.segmap = 4
			and (d.status & ( 32 + 64 + 128 + 256) = 0)
			and (d.status2 & 64) = 0)
	begin
		 select @log_free = (lct_admin("logsegment_freepages", db_id(@dbname) ) - lct_admin("reserved_for_rollbacks", db_id(@dbname))) / @numpgsmb
	end
    
    select convert(varchar(20), @dbname) as "DBName", convert(numeric(15,2), sum(size)) as "data size MB", convert(numeric(15,2), sum(free_mb)) as "data free MB", convert(numeric(15,2), @log_free) as "log free MB" from #device_usage
    
drop table #device_usage
go
Share Button

Drew Montgomery’s sp__dbsubspace for SAP Sybase ASE space usage

A few years back we were both working at a company that needed a method to obtain Sybase ASE database space usage every few minutes. The output of Ed Barlow’s sp__dbspace was fine but it was an expensive call. Drew Montgomery devised a set of stored procedures that would partially cache the results. Kudos to Drew!

With large databases, several of the calculations would overflow. I’m made only minor changes, mainly convert(numeric(19,5), … ) inside the sum()s.

sp__dbsubspace output:


1> sp__dbsubspace
2> go
 Name         Data MB       Used MB        Percent Log MB    Log Used  Log Pct Total MB
 ------------ ------------- -------------- ------- --------- --------- ------- ---------------
 mydb               4836323      4134753.0   85.49     87079  48793.15   56.03         4923402
(return status = 0)

sp_dbsubspace code:

/************************************************************************************************************************************************
 *  This process will use a persistant table called sysdbspaceinfo.  It is not a previously defined sybase system table, but  
 *  is used by the sp__dbsubspace procedure.  The database that this table resides in is defined by the "sybsystemdb" 
 *  marker.  Please replace the "sybsystemdb" marker with an actual database prior to installing this procedure.  In some    
 *  cases you may want to use something like sybsystemprocs or sybsystemdb.  You may also use tempdb, but if you do
 *  you may want to define the table in the model database as well, so it will exist when the server is restarted.  Good luck
 *  and enjoy.                                                                        Drew Montgomery
 ************************************************************************************************************************************************/

use master
go

/*  This is to set the Truncate Log on Checkpoint option for the database with the space check table on, so it doesn't fill up.
 *     This was done in case the database being used for this doesn't have its logs regularly cleaned (some sybase system
 *     databases are like that).  If the database you are going to use has a regular log cleanup procedure, you may comment 
 *     out this section.                         DM                                                                                                                       */
sp_dboption sybsystemdb, "trunc. log", true
go

use sybsystemdb
go

checkpoint
go

/* Drops the table if it already exists, as a precaution for the create */
if object_id('sysdbspaceinfo') is not null  
 drop table sysdbspaceinfo
go

create table sysdbspaceinfo 
   (dbid int, 
    DataPages numeric(19,5), 
    DataPagesUsed numeric(19,5), 
    LogPages numeric(19,5),
    LogPagesUsed numeric(19,5), 
    LogFirstPage int,
    NumberOfObjects int    )
go

/* Unique index is required for the isolation level, and makes the access faster! */
create unique index UCsysdbspaceinfo on sysdbspaceinfo(dbid)
go

/* Go to the sybsystemprocs to install the procedure */
use sybsystemprocs
go

/* Drop it first, as a precaution */
if object_id("sp__dbsubspace") is not null
  drop procedure sp__dbsubspace
go

create procedure sp__dbsubspace 
          @dont_format char(1) = NULL,          /* Flag for indicating the need to have more or less formatted information */
          @LogThreshold float = 0.01,              /* Change threshold for the amount of log "movement" before a recalculation of the data space is performed.
                                                                   The default value of 0.01 represents a 1.0% change in the amont of log space or the first page pointer of the log.
                                                                   Please note: it is theoretically possible that the log pointer could move just less than this amount and have 
                                                                   an amount of log space used that is just less than this amount, therefore it is POSSIBLE that change could be 
                                                                   two times this amount before a recalculation of the data space is performend.  Possible but not very likely.        */
           @Force varchar(10) = NULL
                                                                    
as
/* ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 *  This procedure conceived and written by Drew Montgomery - please forward any and all substantial updates to 
 *        drew_montgomery@ameritech.net (so I can make this procedure better).  Thank you.                               
 * ------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/

set nocount on
set lock nowait
set transaction isolation level 0

/* Declaration Section */
declare     @MixedFlag char,                                 /* Flag to signify if log and data are on the same device */
                @DataPages numeric(19,5),                  /* Number of data pages defined from sysusages */
                @LogPages numeric(19,5),                    /* Number of log pages defined from sysusages */
                @TotalPages numeric(19,5),                    /* Number of pages defined from sysusages */
                @DataPagesUsed numeric(19,5),           /* Number of data pages currently in use (reserved) */
                @LogPagesUsed numeric(19,5),             /* Number of log pages currently in use */
                @LogFirstPage bigint,                                /* First defined page of the transaction log */
                @NumberOfObjects int,                       /* for determining if a table has been dropped or added */
                @OldDataPages numeric(19,5),              /* Previous number of data pages defined from sysusages */
                @OldLogPages numeric(19,5),                /* Previous number of log pages defined from sysusages */
                @OldLogPagesUsed numeric(19,5),        /* Previous number of data pages being used */
                @OldDataPagesUsed numeric(19,5),       /* Previous number of log pages being used */
                @OldLogFirstPage int,                            /* Previous transaction log first page pointer */
                @OldNumberOfObjects int,                  /* Previous number of user tables in this database */
--                @LogDelta numeric(19,5),                       /* Change in the number of log pages being used */
--                @LFPDelta int,                                       /* Change in the location of the first log page */
                @scale numeric(19,5),                            /*  Multiplier used for displaying amount of space from number of pages -
                                                                                 this value is derived from the @@maxpagesize */
                @ReSize char,                                       /* Flag indicating if we need to recalculate the amount of data space being used */
                @pct_used numeric(10,2),                       /* Calculated percentage of data space used */
                @log_pct_used numeric(10,2)                  /* Calculated percentage of log space used */

set @ReSize = 'F'            /* Predefined as "No, thanks, we don't need to get a new reading of the data space being used -
                                          the old value will do just fine.   */

/* Determine the current data and log space allocated to this dabase as defined from sysusages */
select @DataPages = sum(case when segmap=4 then 0 else convert(numeric(19,5), size) end),
          @LogPages = sum(case when segmap & 4=4 then convert(numeric(19,5), size) else 0 end),
          @TotalPages = sum(convert(numeric(19,5), size))
  from master..sysusages where dbid = db_id()

/* If there are no log pages (or the value is null), then the system is defined as having Mixed data and log space -
 *   which also means that the number of potential log pages is the same as the number of data pages.              */
if isnull(@LogPages,0) = 0
begin
  set @MixedFlag = 'M'  -- Mode is Mixed
  set @LogPages=@DataPages
end
else if exists (select 1 from master..sysusages where dbid = db_id() and segmap &5 =5)
  set @MixedFlag = "C"  -- Stands for Confused
else
  set @MixedFlag = 'P'  -- Indicates Pristine

/* We are getting the number of pages being used by the syslogs table (table id = 8).
 * The first select line is "Prior to system 15 version", the second is the "System 15 or above" version-
 *   Please set the appropriate comment as to which kind of system is being used */
/*  select @LogPagesUsed = reserved_pgs(id, doampg), */
select @LogPagesUsed = reserved_pages(db_id(), id), 
          @LogFirstPage = first
  from sysindexes
 where id = 8

/* Extract the information from previous executions of sp__dbsubspace */
select @OldDataPages = DataPages, 
          @OldDataPagesUsed = DataPagesUsed,
          @OldLogPages = LogPages,
          @OldLogPagesUsed = LogPagesUsed,
          @OldLogFirstPage = LogFirstPage,
          @OldNumberOfObjects = NumberOfObjects
  from sybsystemdb..sysdbspaceinfo
where dbid = db_id()

/* Get an object count of the USER TABLES */
select @NumberOfObjects = count(*) from sysobjects where type = 'U'

/* If there are no records retrieved (first run) we need to Recalculate the size */
if @@rowcount = 0
begin
  set @ReSize = 'T'
end
else        
  if @OldDataPages != @DataPages           /* If the number of data pages changed from sysusages - Recalculate size */
  begin
    set @ReSize = 'T'
  end
  else
    if @OldLogPages != @LogPages            /* If the nubmer of log pages changed from sysusages - Recalculate size */
    begin
      set @ReSize = 'T'
    end
    else
    begin                                                   /* if the number of log pages used is greather than a percentage of the total number of log pages available - Recalc Size */
      if (@LogPagesUsed - @OldLogPagesUsed) > @LogThreshold * @LogPages
        set @ReSize = 'T'
      else
        begin                                               /* if the log's first page moved more than the threshold of the number of log pages available - Recalc Size */
        if (abs(@LogFirstPage - @OldLogFirstPage)) > @LogThreshold * @LogPages
          set @ReSize = 'T'
        else
          begin
            if (@OldNumberOfObjects != @NumberOfObjects)
              set @ReSize = 'T'
          end
        end
    end

if @Force is not null  -- This is a force option to make it update the value
  set @ReSize = 'T'

if @ReSize = 'T'                                        /* We are recalculating size, and getting back the new value of the Data Pages Used */
  exec sp__dbsubspace;2 @DataPages, @DataPagesUsed out, @LogPages, @LogPagesUsed, @LogFirstPage, @NumberOfObjects
else
begin                                                       /* or if we don't need to do that, we just use the previous value of the Data Pages Used */
  set @DataPagesUsed = @OldDataPagesUsed
end

/* Calculated scale from the maximum page size (size of the data pages, usually 2k, 4k, 8k, or 16k) */
set @scale = @@maxpagesize / 1024

/* "Borrowed" this calculation from sp__dbspace of the percentages used*/
if @MixedFlag = 'M' 
begin  /* Please note, if the mode is mixed, we will apparently have no Log Percentage Used as it is part of the data space */
  set @pct_used = convert(numeric(10,2), ((@DataPagesUsed + @LogPagesUsed) * 100) / @DataPages)
  set @log_pct_used = convert(numeric(10,2), 0)
end
else
begin
  set @pct_used = convert(numeric(10,2), (@DataPagesUsed * 100) / @DataPages) 
  set @log_pct_used = convert(numeric(10,2), (@LogPagesUsed * 100 )/(@LogPages) ) 
end

/* The @dont_format is from the sp__dbspace command - and provides the option for an abbreviated display of information */
if @dont_format is not null
begin            /* Provide the results based on the local variables - formatted first */
select  Name             = db_name(),
        "Data MB"  = str((@DataPages*@scale)/1024, 16, 0),
        "Used MB"  = str(((@DataPagesUsed + case @MixedFlag when 'M' then @LogPagesUsed else 0 end)*@scale)/1024, 16, 1),
        Percent    = str(@pct_used, 7, 2),
        "Log MB" = str((@LogPages*@scale)/1024, 12, 0),
        "Log Used"   = str(((case @MixedFlag when 'M' then 0 else @LogPagesUsed end)*@scale)/1024, 12, 2),
        "Log Pct"  = str(@log_pct_used, 7, 2),
        "Total MB" = str((@TotalPages*@scale)/1024, 18, 0)
end
else
begin          /* And unformatted */
select  Name             = convert(char(12),db_name()),
        "Data MB"  = str((@DataPages*@scale)/1024, 13, 0),
        "Used MB"  = str(((@DataPagesUsed + case when @MixedFlag = 'M' then @LogPagesUsed else 0 end)*@scale)/1024, 14, 1),
        Percent    = str(@pct_used, 7, 2),
        "Log MB"   = str((@LogPages*@scale)/1024, 9, 0),
       "Log Used" = str(((case when @MixedFlag='M' then 0 else @LogPagesUsed end)*@scale)/1024, 9, 2),
        "Log Pct"  = str(@log_pct_used, 7, 2),
        "Total MB" = str((@TotalPages*@scale)/1024, 15, 0)
end

/*  And Vola' we are done!  */
go

/* This is a subordinate procedure that gets the information from the sysindexes (or sysobjects for system 15+) and stores the information into the sysdbspaceinfo table */
create procedure sp__dbsubspace;2
  @DataPages numeric(19,5),                           /* See comments above about variables */
  @DataPagesUsed numeric(19,5) output,         /* NOTE: this value is returned to the calling procedure */
  @LogPages numeric(19,5),
  @LogPagesUsed numeric(19,5),
  @LogFirstPage bigint,
  @NumberOfObjects int

with recompile
as

  /* Prior to system 15 version */
  /* select @DataPagesUsed = sum(reserved_pgs(id, doampg) + reserved_pgs(id, ioampg))
     from sysindexes
    where id != 8
  */
/* System 15 and above version */    
   select @DataPagesUsed = sum(convert(numeric(19,5), reserved_pages(db_id(), id)))
     from sysobjects
    where id != 8
    
    
 /* Update the information in the table */
      update sybsystemdb..sysdbspaceinfo 
           set  DataPages = @DataPages,
                 DataPagesUsed = @DataPagesUsed,
                 LogPages = @LogPages,
                 LogPagesUsed = @LogPagesUsed,
                 LogFirstPage = @LogFirstPage,
                 NumberOfObjects = @NumberOfObjects
         where dbid = db_id()                 

/* if the update is not for any rows, then add a row */
        if @@rowcount = 0
          insert into sybsystemdb..sysdbspaceinfo values (db_id(), @DataPages, @DataPagesUsed, @LogPages, @LogPagesUsed, @LogFirstPage, @NumberOfObjects)
          
return  /* and we are done with this one */
go

/* You may want to grant some permissions (execute, perhaps) if the users are not sa's */
Share Button

SOLVED: SAP Sybase IQ SQL Anywhere error -203 Cannot set a temporary option for user (Rapid SQL)

Thanks to Joseph Weaver for supplying this workaround!
In Embarcadero’s Rapid SQL acceRapidSQLssing data within SAP IQ can sometimes result in a “SQL Anywhere Error -203: Cannot set a temporary option for user ‘XXXXXX’” error.  This is not an IQ issue.  

IQ-203-1

The Rapid SQL configuration needs to be updated by disabling the “Enable SET Query Options” by default:

IQ-203-2

You will need to restart Rapid SQL for the change to take effect.
Share Button