Decision Support Systems… how far we’ve come :)

At the dawn of Data Warehouse thinking, primitive decision support systems were poking their collective heads out of the murky depths of the early 1980s. Twenty three years later we have real data warehouses such as Sybase’s IQ. An episode of the Computer Chronicles on the Internet Archive provides a time machine:

[flashvideo width=”320″ height=”240″ filename=”http://www.archive.org/download/Decision1984/Decision1984.flv” returnpage=”http://froebe.net/blog/2007/09/28/decision-support-systems-how-far-weve-come/” /]

Decision Support Systems (2/2/1984)

Expert systems software and artificial intelligence applications.
Host: Stewart Cheifet
Guests: Gary Kildall, DRI; Mike Thoma, Mgmt Decisn Sys; Steve Weyl, Syntelligence; Jim Chapman, Human Edge SW
Products/Demos: Strategic Simulations Epidemic, Prospector, CompuStat, Easy Scan, Sales Edge
This item is part of the collection: Computer Chronicles
Keywords: Episode year: 1984

Share Button

Having trouble starting ASE 12.5 / 15 on Linux with multiple engines?

If you’re getting error messages similar to:

01:00000:00000:2007/05/04 19:09:10.28 kernel  os_attach_region: shmat(425984): Invalid argument
01:00000:00000:2007/05/04 19:09:10.28 kernel  kbattach: couldn't attach to Kernel region
01:00000:00000:2007/05/04 19:09:10.28 kernel  kestartup: couldn't attach to shared memory: -ONLINE:1,0,0xf500c2b8, 0x413ea000, 0x76c00000, 0x1159
00:00000:00000:2007/05/04 19:09:10.28 kernel  engine 1, os pid 27586  exited
00:00000:00000:2007/05/04 19:09:10.28 kernel  Process 27586 exited with status 1
00:00000:00000:2007/05/04 19:09:10.28 kernel  Task with kpid 0 running on failed engine
01:00000:00000:2007/05/04 19:09:10.29 kernel  os_attach_region: shmat(425984): Invalid argument
01:00000:00000:2007/05/04 19:09:10.29 kernel  kbattach: couldn't attach to Kernel region
01:00000:00000:2007/05/04 19:09:10.29 kernel  kestartup: couldn't attach to shared memory: -ONLINE:1,0,0xf500c2b8, 0x413ea000, 0x76c00000, 0x1159
00:00000:00000:2007/05/04 19:09:10.29 kernel  engine 1, os pid 27587  exited
00:00000:00000:2007/05/04 19:09:10.29 kernel  Process 27587 exited with status 1
00:00000:00000:2007/05/04 19:09:10.29 kernel  Task with kpid 0 running on failed engine
00:00000:00009:2007/05/04 19:09:10.29 kernel  A listener with protocol tcp, host dba-prodsupport1, port 5000, engine 1 already exists.
01:00000:00000:2007/05/04 19:09:10.30 kernel  os_attach_region: shmat(425984): Invalid argument
01:00000:00000:2007/05/04 19:09:10.30 kernel  kbattach: couldn't attach to Kernel region
01:00000:00000:2007/05/04 19:09:10.30 kernel  kestartup: couldn't attach to shared memory: -ONLINE:1,0,0xf500c2b8, 0x413ea000, 0x76c00000, 0x1159
00:00000:00000:2007/05/04 19:09:10.30 kernel  engine 1, os pid 27588  exited
00:00000:00000:2007/05/04 19:09:10.30 kernel  Process 27588 exited with status 1

....

00:00000:00010:2007/05/04 19:09:13.04 kernel  kesetown: engine 1 not online
00:00000:00010:2007/05/04 19:09:13.04 kernel  upsetaffinity: can't affinity to engine 1 for kpid 851981
00:00000:00010:2007/05/04 19:09:13.04 kernel  upsetaffinity: can't affinity to engine 1 for kpid 85198

You will need to modify your /etc/sysctl.conf (make it live with sysctl -p) and disable exec-shield:

kernel.exec-shield=0
kernel.exec-shield-randomize=0

Some kernels don’t have the kernel.exec-shield* options but have no fear, we have another option:

kernel.randomize_va_space=0
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

Replication Server Exception List Deleter (updated)

As you may remember, my good friend Ken Rearick created an excellent stored procedure, rs_del_all_exception, that safely clears out old exceptions from the RSSD database. Ken has just sent an updated version to me. B-)

IF OBJECT_ID('dbo.rs_del_exception') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.rs_del_exception
    IF OBJECT_ID('dbo.rs_del_exception') IS NOT NULL
        PRINT '< << FAILED DROPPING PROCEDURE dbo.rs_del_exception >>>'
    ELSE
        PRINT '< << DROPPED PROCEDURE dbo.rs_del_exception >>>'
END
go

-- rs_del_exception [xactid] [,xactid]
-- no transaction id just list exceptions
-- single xactid delete that xact
-- range of xactid's delete the full range of xact

create proc rs_del_exception
@xacts int = NULL,
@xacte int = NULL

as

declare @systran binary(8)
declare @cnt int, @err int
declare @ccnt char(8)
declare @cxact char(8)
declare @rsname varchar(30)
declare @cmdcount char(9)
declare @msg varchar(255)
declare @tab_name varchar(30)
declare @row_cnt int

set nocount on

/* find RS name */

select @rsname = charvalue
from   rs_config
where  optionname = "oserver"

/* Build temp table #tab1 */

create table #tab1
(    orig_site varchar(30),
    orig_db   varchar(30),
    orig_user varchar(30),
    orig_time datetime,
    error_site varchar(30),
    error_db varchar(30),
    log_time datetime,
    reccount int null,
    sys_trans_id binary(8),
    app_usr varchar(30)
)

insert into #tab1 (orig_site, orig_db,
           orig_user, orig_time,
           error_site, error_db,
           log_time, sys_trans_id,
           app_usr)
select
    orig_site,
    orig_db,
    orig_user,
    orig_time,
    error_site,
    error_db,
    log_time,
    sys_trans_id,
    app_usr
from    rs_exceptshdr exh

/* add logged command counts to table */

update #tab1
  set reccount = (select max(src_cmd_line)
                    from   rs_exceptscmd exc
                      where exc.sys_trans_id = #tab1.sys_trans_id
                    group by exc.sys_trans_id)
/* print summary */

begin
  select @cnt = count(sys_trans_id)
  from rs_exceptshdr

  select @ccnt = convert(char(8), @cnt)

  if (@cnt = 0)
  begin
    print " "
    /* 20500,"         There are 0 Logged Transactions." */
    exec rs_get_msg 20500, @msg output
    print @msg
    print " "
    return
  end

  print " "
  /* 20501,"         Summary of Logged Transactions on '%1!'", @rsname */
  exec rs_get_msg 20501, @msg output
  print @msg, @rsname
  print " "
  print " "

  if (convert(int, 0x0000100) = 65536)
  begin
    select
      "Xact ID" = convert(int, reverse(substring(sys_trans_id, 5, 8))),
      "Org Site" = substring(rtrim(orig_site)+"."+rtrim(orig_db), 1, 15),
      "Org User" = substring(orig_user, 1, 8),
      "Org Date " = convert(char(11), orig_time),
      "Dest Site" = substring(rtrim(error_site)+"."+rtrim(error_db), 1, 15),
      "# Recs/Xact" = reccount
    from #tab1
  end
  else
  begin
    select
      "Xact ID" = convert(int, substring(sys_trans_id, 5, 8)),
      "Org Site" = substring(rtrim(orig_site)+"."+rtrim(orig_db), 1, 15),
      "Org User" = substring(orig_user, 1, 8),
      "Org Date " = convert(char(11), orig_time),
      "Dest Site" = substring(rtrim(error_site)+"."+rtrim(error_db), 1, 15),
      "# Recs/Xact" = reccount
    from #tab1
  end

  print " "
  print " "
  /* 20502,"         To Delete a Specific Logged Xact., type 'rs_del_exception {Xact ID}'" */
  exec rs_get_msg 20502, @msg output
  print @msg
  print " "
end

if (@xacts = null)
  return 0
  
select @row_cnt = 1

while (@row_cnt > 0)
begin

set rowcount 1

  if (@xacte = null)
  begin
    if (convert(int, 0x0000100) = 65536)
      select @systran = sys_trans_id from #tab1 where convert(int, reverse(substring(sys_trans_id, 5, 8))) = @xacts
    else
      select @systran = sys_trans_id from #tab1 where convert(int, substring(sys_trans_id, 5, 8)) = @xacts
  end
  else
  begin
    if (convert(int, 0x0000100) = 65536)
      select @systran = sys_trans_id from #tab1 where convert(int, reverse(substring(sys_trans_id, 5, 8))) between @xacts and @xacte
    else
      select @systran = sys_trans_id from #tab1 where convert(int, substring(sys_trans_id, 5, 8)) between @xacts and @xacte
  end
  
  select @row_cnt = @@rowcount

  delete #tab1 where @systran = sys_trans_id

  set rowcount 0

  if (@row_cnt = 0)
  begin
    print " "
    print "Exceptions deleted"
    print " "
    return
  end

  if (convert(int, 0x0000100) = 65536)
    select @cxact = convert(char(8), convert(int, reverse(substring(@systran, 5, 8))))
  else
    select @cxact =  convert(char(8), convert(int, substring(@systran, 5, 8)))

  /* if logged transaction exists, delete it. */

  select @cmdcount = rtrim(convert(char(9), reccount))
  from   #tab1
  where  sys_trans_id = @systran

  print " "
  /* 20505," Deleting %1! Commands in Logged Transaction # %2! on '%3!'" */
  exec rs_get_msg 20505, @msg output
  print @msg, @cmdcount, @cxact, @rsname

  begin transaction

  delete  rs_systext
  from    rs_exceptscmd exc,
          rs_exceptshdr exh,
          rs_systext sys
  where   exc.sys_trans_id = exh.sys_trans_id
  and     exc.cmd_id = sys.parentid
  and     sys.texttype = "C"
  and     exh.sys_trans_id  =  @systran

  select @err = @@error

  if (@err != 0)
  begin
    select @tab_name = 'rs_systext'
    /* 20506,"Deleting %1! table failed. Transaction Rolled Back." */
    exec rs_get_msg 20506, @msg output
    print @msg, @tab_name
    rollback transaction
  end
  else
  begin
    delete  rs_exceptscmd
    where   sys_trans_id  =  @systran

    select @err = @@error

    if (@err != 0)
    begin
      select @tab_name = 'rs_exceptscmd'
      /* 20506,"Deleting %1! table failed. Transaction Rolled Back." */
      exec rs_get_msg 20506, @msg output
      print @msg, @tab_name
      rollback transaction
    end
    else
    begin
      delete  rs_exceptshdr
      where   sys_trans_id  =  @systran

      select @err = @@error

      if (@err != 0)
      begin
        select @tab_name = 'rs_exceptshdr'
        /* 20506,"Deleting %1! table failed. Transaction Rolled Back." */
        exec rs_get_msg 20506, @msg output
        print @msg, @tab_name
        rollback transaction
      end
      else
      begin
        commit transaction

        select @err = @@error

        if (@err !=0)
        begin
          /* 20509,"Executing 'commit transaction'  failed. Transaction Rolled Back." */
          exec rs_get_msg 20509, @msg output
          print @msg
          rollback transaction
        end
        else
        begin
          print " "
          /* 20510,"         Logged Transaction # %1! Successfully Deleted. Truncate RSSD Transaction Log if Necessary." */
          exec rs_get_msg 20510, @msg output
          print @msg, @cxact
          print " "
        end
      end
    end
  end
end


go
EXEC sp_procxmode 'dbo.rs_del_exception','unchained'
go
IF OBJECT_ID('dbo.rs_del_exception') IS NOT NULL
    PRINT '< << CREATED PROCEDURE dbo.rs_del_exception >>>'
ELSE
    PRINT '< << FAILED CREATING PROCEDURE dbo.rs_del_exception >>>'
go

Download: rs_del_exception stored procedure

Share Button

The code that fixes the Sybase Tech Talk podcast

Yeah, I know it isn’t pretty but it works. 
Continue reading “The code that fixes the Sybase Tech Talk podcast”

Share Button