SAP Sybase Replication Server ERROR -99999 Severity 5 Values exceed buffer length – SOLVED

Running SAP Sybase SAP SybaseReplication Server has always been interesting and rather frustrating that in its fragility. Today’s lesson is not exactly that clear. Take the following error message:

ERROR #1027 DSI EXEC(104(1) repdb_svr.rep_db) - /dsiutil.c(390)
    Open Client Client-Library error: Error: -99999, Severity 5 -- 'Values exceed buffer length'.
ERROR #5215 DSI EXEC(104(1) repdb_svr.rep_db) - /dsiutil.c(393)
    The interface function 'SQLPrepare' returns FAIL for database 'repdb_svr.rep_db'. The errors are retryable. The DSI thread will restart automatically. See messages from the interface function for more information.

RANT: While Replication Server says it is retryable, it never actually retries.
It is for the DSI connection but which buffer?? Replication Server doesn’t list any “buffers” for the DSI explicitly. There are a myriad of caches for the DSI connection. In the error message I see two hints to narrow it down: “Values exceed” and “SQLPrepare”. The most likely cache candidates, to me, would be the batch size (dsi_cmd_batch_size) and the dynamic sql cache (dynamic_sql_cache_size).
A simple check would be to disable dynamic SQL and see if we get the same error message:

suspend connection to repdb_svr.rep_db
go
alter connection repdb_svr.rep_db set dynamic_sql to 'off'
go
resume connection to repdb_svr.rep_db
go

Within a few seconds, I received the same message, so that wasn’t the culprit. Before we do anything else, let’s reenable the dynamic sql:

suspend connection to repdb_svr.rep_db
go
alter connection repdb_svr.rep_db set dynamic_sql to 'on'
go

That leaves the batch size as the most likely culprit. So let’s increase that and see what happens:

suspend connection to repdb_svr.rep_db
go
admin who, dsi, repdb_svr, rep_db
go
--  record Cmd_batch_size (default is 8192
--  Increase dsi_cmd_batch_size 
alter connection repdb_svr.rep_db set dsi_cmd_batch_size to '32768'
go
resume connection to repdb_svr.rep_db
go

The error message did not reoccur and I see replication moving by monitoring the admin,sqm in rep server and the rs_lastcommit table in the replicate database to ensure we’re moving.

You may ask what changed that would require increasing the batch size. Well, a very large transaction that was trying to insert data that already existed but since not all the data already existed, we needed to change INSERT into DELETE followed by INSERT:

alter connection to repdb_svr.rep_db set dsi_command_convert to 'i2di'

Why would that cause it to go boom? Well, the dsi_command_convert is applied AFTER replication server slices and dices the transactions into batches.

Share Button

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

SAP SYBASE IQ 16.0 SP8 MANUALS FIXED TO WORK WITH TABLET/PHONE PDF READERS LIKE ALDIKO

Aldiko Book Reader Premium Full v2.2.3.apkMany PDF readers for smart phones (Android/iphone) and tablets manage the pdf files Calibre_Logobased solely on the Title and Author fields in the PDF file. While for this is fine for your average book, it is not all that helpful with manuals that tend to have abbreviated or no data in the title/author fields. In the case of the manuals for Sybase IQ, I’m unable to load the manuals for say v15.4 and v16.0 as they have the same Title/Author data.

How to fix? Easy. Go get Calibre. Drop the PDF files on to the running Calibre. Edit them by hitting the E key.

In my case, I edited the “Title”, “Author”, “Tags”, “Publisher” and “Languages”:

Calibre

Calibre doesn’t modify the PDF files themselves so I will need to export the files to a custom directory. In Calibre nomenclature, this is “Saving”. Highlight all the titles you want to export and hit “S” twice. Why twice? No idea. Choose the directory.

You can now copy the exported PDF files to your phone, tablet, whatever without fear of the v16.0 version of the P&T Guide being rejected by Aldiko because the v15.4 version is already added.

Here are the IQ v16.0 SP8 manuals that I’ve ‘fixed’ to work with Aldiko. They are identical to the PDFs on sybooks with the exception of the PDF fields I mentioned previously.

No copyright infringement is intended. SAP/Sybase, please feel free to take these and host them.

IQ 16.0 SP8 – SAP IQ Cockpit – SAP, Inc_
IQ 16.0 SP8 – Installation and Configuration Guide [ Windows ] – SAP, Inc_
IQ 16.0 SP8 – Installation and Configuration Guide [ Solaris ] – SAP, Inc_
IQ 16.0 SP8 – Installation and Configuration Guide [ HP-UX ] – SAP, Inc_
IQ 16.0 SP8 – Administration_ User Management and Security – SAP, Inc_
IQ 16.0 SP8 – Reference_ Statements and Options – SAP, Inc_
IQ 16.0 SP8 – User-Defined Functions – SAP, Inc_
IQ 16.0 SP8 – Administration_ In-Memory Row-Level Versioning – SAP, Inc_
IQ 16.0 SP8 – Reference_ Building Blocks, Tables, and Procedures – SAP, Inc_
IQ 16.0 SP8 – Reference_ Programming – SAP, Inc_
IQ 16.0 SP8 – Release Bulletin [ Windows ] – SAP, Inc_
IQ 16.0 SP8 – Release Bulletin [ Linux ] – SAP, Inc_
IQ 16.0 SP8 – Administration_ Multiplex – SAP, Inc_
IQ 16.0 SP8 – What’s New in SAP IQ 16.0 – SAP, Inc_
IQ 16.0 SP8 – Migration [ Windows ] – SAP, Inc_
IQ 16.0 SP8 – Administration_ Load Management – SAP, Inc_
IQ 16.0 SP8 – Guide to Licensed Options – SAP, Inc_
IQ 16.0 SP8 – Migration [ Linux and UNIX ] – SAP, Inc_
IQ 16.0 SP8 – Quick Start [ Windows ] – SAP, Inc_
IQ 16.0 SP8 – Quick Start [ Linux and UNIX ] – SAP, Inc_
IQ 16.0 SP8 – Administration_ Spatial Data – SAP, Inc_
IQ 16.0 SP8 – Performance and Tuning Guide – SAP, Inc_
IQ 16.0 SP8 – Release Bulletin [ IBM AIX ] – SAP, Inc_
IQ 16.0 SP8 – Release Bulletin [ HP-UX ] – SAP, Inc_
IQ 16.0 SP8 – Release Bulletin [ Solaris ] – SAP, Inc_
IQ 16.0 SP8 – Administration_ Database – SAP, Inc_
IQ 16.0 SP8 – Unstructured Data Analytics – SAP, Inc_
IQ 16.0 SP8 – Utility Guide – SAP, Inc_
IQ 16.0 SP8 – Administration_ Backup, Restore, and Data Recovery – SAP, Inc_
IQ 16.0 SP8 – Installation and Configuration Guide [ IBM AIX ] – SAP, Inc_
IQ 16.0 SP8 – Administration_ Globalization – SAP, Inc_
IQ 16.0 SP8 – Installation and Configuration Guide [ Linux ] – SAP, Inc_
IQ 16.0 SP8 – Interactive SQL Guide – SAP, Inc_
IQ 16.0 SP8 – Introduction to SAP IQ – SAP, Inc_

Share Button

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
test #1  : Clock Time (ms.): total = 184821  Avg = 0 (5410.64 rows per sec.)
test #2  : Clock Time (ms.): total = 178612  Avg = 0 (5598.73 rows per sec.)

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

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

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

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

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

uds - packet size 65024
test #13 : Clock Time (ms.): total = 13254  Avg = 0 (75448.92 rows per sec.)
test #14 : 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!

Obviously, if you are bulk copying out of a view that uses a monster index you you may not see a huge difference, if any, because the majority of the time will transverse that index.

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

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

SAP Sybase ASE Unable to shutdown with there are not enough ‘user connections’ Error: 1601, Severity: 17, State: 3

If you’re trying to shutdown ASE and you’re not able to log in to the ASE instance, you can shutdown the instance with “kill -15 ” on Linux/Unix:

server  Error: 1601, Severity: 17, State: 3
server  There are not enough 'user connections' available to start a new process. Retry when there are fewer active users, or ask your System Administrator to reconfigure ASE with more user connections

Obtain the os PID simply by running showserver:

$ showserver
USER          PID %CPU %MEM   SZ  RSS    TTY STAT    STIME  TIME COMMAND
sybase    542123 15.1  1.0 52220 93356  pts/2 A    11:15:35  8:34 /sybase/ASE-15_0/bin/dataserver -d/dev/rmaster -e/sybase/ASE-15_0/install/errorlog -c/sybase/ASE-15_0/sybase.cfg -isybase -ssybase -M/sybase/mem 

Kill the dataserver process with “kill -15” triggering a “shutdown with nowait” within ASE:

$ kill -15 542123

Only as a last resort, use “kill -9”.

  • If you need to, verify with “ipcs -m” that the shared memory segments are released, if not use “ipcrm” to release it.
  • Verify with “netstat -an |grep ” that the bound port(s) that ASE uses are released. If not, you may need to restart the machine to release them.
Share Button

SAP Sybase IQ: How to Restore Your Backups to Another system

SAP/Sybase’s documentation isn’t very clear for new IQ dbas and developers. Once such item is sdbply restoring an IQ database on to another system. Unlike ASE, you need to specify the new file locations if they are different than the source server.

Assumptions:

  1. IQ software has been installed
  2. The new dbfile locations are symbolic links to raw partitions OR the path exists but not the files
  3. You have a valid SYSAM license for the new IQ instance.
  4. The new IQ instance name is set (via -n instance)
  5. The old directory for the .db, .log and .mir exists (use a symbolic link if you wish)

Obtain dbspace file names with sp_iqfile:

select DBFileName, Path, DBFileSize from sp_iqfile();
DBFileName	Path	DBFileSize
'IQ_SYSTEM_MAIN'	'/database/old_iq/devices/IQ_MAIN/old_iqmain001.iq'	'32G'
'IQ_USER_MAIN_FILE_01'	'/database/old_iq/devices/IQ_USER_MAIN/old_iqusermain001.iq'	'1024G'
'IQ_SYSTEM_TEMP'	'/database/old_iq/devices/IQ_TEMP/old_iqtemp001.iqtmp'	'32G'
'IQ_SYSTEM_TEMP_002'	'/database/old_iq/devices/IQ_TEMP/old_iqtemp002.iqtmp'	'32G'</code></pre>

Create a restore.sql file renaming the DBFileName to the new locations:
<pre class="prettyprint" ><code class="language-sql">restore database 'new_iq'
FROM '/backups/old_iq.20140423100111.17760.IQfullbkp'
RENAME IQ_SYSTEM_MAIN TO '/database/new_iq/devices/IQ_MAIN/new_iqmain001.iq'
RENAME IQ_SYSTEM_TEMP TO '/database/new_iq/devices/IQ_TEMP/new_iqtemp001.iq'
RENAME IQ_SYSTEM_TEMP_002 TO '/backups/new_iqtemp002.iq'
RENAME IQ_SYSTEM_MSG TO '/database/new_iq/IQLOG/new_iq.iqmsg'
RENAME IQ_USER_MAIN_FILE_01 TO '/database/new_iq/devices/IQ_USER_MAIN/new_iqusermain001.iq';

Stop the destination IQ instance if it is running and start the utility database:

stop_iq
Checking system ...

The following 1 server(s) are owned by 'sybdba'

## Owner          PID   Started  CPU Tdbe  Additional Information
-- ---------  -------  --------  --------  ------------------------------------
1: sybdba       13909     Apr24  00:43:46  SVR:new_iq DB:new_iq PORT:58116
              /database/new_iq/sybase/IQ-16_0/bin64/iqsrv16 @/database/new_iq/sybdb/new_iq.cfg /database/new_iq/sybdb/new_iq.db -gn 65 -o /database/new_iq/sybase/IQ-16_0/logfiles
${SYBASE}/IQ-16_0/bin64/start_iq -n utility_db -gu dba -c 48m -gc 20 -iqgovern 30 \
        -gd all -gl all -gm 10 -gp 4096 -ti 4400 -z -zr all -zo $SYBASE/IQ-16_0/logfiles/utility_db.out \
        -o $SYBASE/IQ-16_0/logfiles/utility_db.srvlog -iqmc 100 -iqtc 100 -x "tcpip{port=9000}"
Starting server utility_db on localhost at port 9000 (04/30 09:37:16)

Run Directory       : /database/new_iq/sybdb
Server Executable   : /database/new_iq/sybase/IQ-16_0/bin64/iqsrv16
Server Output Log   : /database/new_iq/IQLOG/utility_db.srvlog
Server Version      : 16.0.0.653/sp03 16.0.0/Linux 2.6.18-194.el5
Open Client Version : N/A
User Parameters     : '-n' 'utility_db' '-gu' 'dba' '-c' '48m' '-gc' '20' '-iqgovern' '30' '-gd' 'all' '-gl' 'all' '-gm' '10' '-gp' '4096' '-ti' '4400' '-z' '-zr' 'all' '-zo' '/database/new_iq/IQLOG/utility_db.out' '-o' '/database/new_iq/IQLOG/utility_db.srvlog' '-iqmc' '100' '-iqtc' '100' '-x' 'tcpip{port=9000}'
Default Parameters  : -gn 25
….

Remove the db, log and mir files:

rm instance.db instance.log instance.mir

Restore the full backup:

dbisql -c "uid=dba;pwd=sql;eng=utility_db;dbn=utility_db" -port 9000 -host $( hostname ) -nogui "restore.sql"

Restore the incremental backup(s):

dbisql -c "uid=dba;pwd=sql;eng=utility_db;dbn=utility_db" -port 9000 -host $( hostname ) -nogui "restore_incrementals.sql"

Stop the utility database:

stop_iq

Start the IQ server to ensure it comes up then shut it back down.

If the name of the server has changed (e.g. old_iq -> new_iq), then we need to update the log and mir files. First let’s find out where the log and mir files are currently set to in the db file:

dblog new_iq.db
SQL Anywhere Transaction Log Utility Version 16.0.0.653
"new_iq.db" is using log file "/database/old_iq/sybdb/old_iq.log"
"new_iq.db" is using log mirror file "/database/old_iq/sybdb/old_iq.mir"
Transaction log starting offset is 0702994164
Transaction log current relative offset is 0000397583

Set the log file to “new_iq.log”:

dblog -t new_iq.log new_iq.db
SQL Anywhere Transaction Log Utility Version 16.0.0.653
"new_iq.db" was using log file "/database/old_iq/sybdb/old_iq.log"
"new_iq.db" is using log mirror file "/database/old_iq/sybdb/old_iq.mir"
"new_iq.db" is now using log file "new_iq.log"
Transaction log starting offset is 0702994164
Transaction log current relative offset is 0000397625

We need to clear the mir file(s) before we can assign a new one:

dblog -r new_iq.db
SQL Anywhere Transaction Log Utility Version 16.0.0.653
"new_iq.db" is using log file "new_iq.log"
"new_iq.db" was using log mirror file "/database/old_iq/sybdb/db00.mir"
"new_iq.db" is now using no log mirror file
Transaction log starting offset is 0702994164
Transaction log current relative offset is 0000397625

Set the mir file:

dblog -m new_iq.mir new_iq.db
SQL Anywhere Transaction Log Utility Version 16.0.0.653
"new_iq.db" is using log file "new_iq.log"
"new_iq.db" was using no log mirror file
"new_iq.db" is now using log mirror file "new_iq.mir"
Transaction log starting offset is 0702994164
Transaction log current relative offset is 0000397625

Start your IQ instance.

Share Button

SAP Sybase ASE 16.0 major features

Sybase .. er… SAP will be releasing Adaptive Server Enterprise 16 within in the next few months (currently expected in Q2 2014). SAP has made the ASE 16.0 manuals available.

Kevin Sherlock sums up the major new features quite well:

  • create or replace functionality
  • multiple triggers
  • monitoring threshold based events
  • configuration tracking history
  • partition level locking
  • log space usage tracking
  • CIS to HANA

While the number of major features may be a bit lacking on first glance to justify being a major release, Jeff Tallman of SAP provides a bit of reasoning on what was really changed:

Hidden under the covers of ASE 16 is a lot of rewrites to spinlock areas of code – so, while you are seeing what looks to be a scattering of features, the main work was done in scaling and eliminating contention – both on high core counts as well as lower core counts – the later especially with proc cache and ELC configuration – as well as log semaphore contention and eliminating the problem of buffer unpinning. Some of these changes required linking in machine code – which means only supporting current releases of certain platforms/OS’s – which by itself often dictates a new platform number. However, there are a number of new features – if you read the NFG, you can see a laundry list – one of which may or may not be mentioned there is HADR mode – which more tightly integrates ASE & SRS – not only is there a synchronous RepAgent (requires an new SRS SP to be launched later), standby ASE is readonly for normal users (ASE actually detects it is standby – and unless you are a privileged user such as RS maint or sa_role, writes are blocked), but ASE also now supports client failover from primary to standby ASE without OpenSwitch – in short term, available for Business Suite – later this year (perhaps) custom apps.

However, with regard to Full Database Encryption…..from a data security standpoint, you can think of it as filling a gap between column level encryption and standard MAC/DAC controls – especially with predicated permissions in the mix. Remember, in column level encryption, we decrypted data at the materialization stage (and encrypted it in normalization) which meant that the data was encrypted both in memory as well as on disk. This was important, because, when you have database users with different access requirements – and especially if you want to keep DBA’s from seeing the data, you need to encrypt the data in memory as well as on disk – and with different users/different requirements, you also need to be able to encrypt different columns with different keys. As a result of encryption, some common database performance techniques – such as leaf/range scans on encrypted cols – were penalized as the index was sorted by the encrypted value (otherwise, it would be security hole) – and no real secure encryption techniques exist that would preserve the lexigraphical sequence. As a result, often times a different index was used for the query or if that index was selected, it was a full leaf scan followed by decryption & sorting – quite a bit of overhead compared to the unencrypted leaf scan. Of course, Encrypted Columns took a bit of effort to set up as someone had to go through and identify every column of sensitive data, determine which Column Encryption Key to use and who should have access – some planning.

Encrypted Columns = data at rest and in memory fully encrypted – and only select designated users could see the data – others saw a default literal value.

Full Database Encryption is intended to solve the problem of ensuring the data at rest is encrypted, but sort of assumes that all legitimate users of the database have the same access rights to the data. Since all users have the same access rights, there is no need to encrypt in memory, use different keys for different columns, etc. As a result, the encryption happens just prior to being written to disk – or just after being read from disk – and on a page basis vs. individual column basis. As a result, index key values, etc. are in their normal sorted order – meaning there is no penalty for leaf scans/range scans any more. Yes, the PIOs may take a slight bit longer but I would be willing to wager we could encrypt the data far faster than traditional disk-based storage can either write it to disk or read it from disk. The time aspect may be very very slightly noticeable on large physical read driven queries. Of course, encryption does use CPU – that might be more noticeable – depending on how much physical IO you are doing. However, since most apps operate on 95%+ cache hit rates, it might not be that noticeable. Remember as well, for write intensive apps, it is often not your SPID doing the writes – it is the HK Wash, checkpoint, someone else pushing your page through wash marker, etc. Keep in mind that one of the drivers for this was SAP ERP applications – where performance is extremely critical due to the way the applications tend to operate (a lot of client side joins to avoid temp tables due to vendor incompatibilities with respect to tempdb). As a result, performance was a key consideration. Level of effort for implemenation is minimal – set up your keys and encrypt the database. Voila!

Full Database Encryption = data at rest fully encrypted – all legitimate users have access.

Hopefully, this not only addresses the speed question, but also the differences. — Jeff Tallman in response to ASE 16: When and what major features?

SAP has overhauled ASE bringing it up to modern performance and scalability. It’s far too early to determine whether the rebuilt engine will live up to the our expectations.

Share Button

FW: ASE 15.7: Create a Remote Server to SAP IQ 16.0

ASE 15.7: Create a Remote Server to SAP IQ 16.0
March 12, 2014

Create the remote server entry in the interfaces file; sql.ini (Windows) or interfaces (UNIX).

[myIQServer]
master=TCP,myIQHost,4091
query=TCP,myIQHost,4091

Read more at ARBIME
syLogo

Share Button