Bruce Perens’ Open Source Series

There are several full copies of technical books that can be downloaded for free.  For those of you that don’t know it, Bruce Perens several years ago convinced Prentice Hall to release books in an open source manner.  Give them out for free. 
Prentice Hall PTR is proud to publish the Bruce Perens’ Open Source Series with Bruce Perens, Series Editor. This Series focuses on Linux and Open Source technologies, including new and emerging technologies. It targets professional software developers, system and network administrators, and power users.

The Bruce Perens’ Open Source Series is designed to give a voice to up-and-coming Open Source authors. Each book in the Series is published under the Open Publication License, an Open Source compatible book license. Electronic versions will be made available at no cost several months after each book’s publication.

Share Button

disappearing alert override table SOLVED!

At work, I created a web page that would raise alerts depending on various criteria and could be overridden by an entry in a db table.

Server Name Override Start Override End Authorized By Comments
my_server Nov 20 2006 3:24PM CST Dec 4 2006 12:00PM CST RG add space

Well, I solved it!  When you create a subroutine in Mason, it is stored in the HTML::Mason::Commands name space.  The problem is that some of our monitoring web pages would over write the subroutine in the name space.  So, I was looking for the wrong type of override!  Icorrected it by creating a single subroutine call (the subroutines were 99% identical).

I knew this but somehow overlooked it!  argh!

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

Eliminating Temp Table Usage in sp__diskdevice

The sp__diskdevice 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:  diskdevice
|*
\************************************************************************/
:r database
go
:r dumpdb
go
if exists (select *
   from   sysobjects
   where  type = "P"
   and  name = "sp__diskdevice_old")
begin
drop proc sp__diskdevice_old
end
go
create procedure sp__diskdevice_old (@devname char(30)=NULL, @dont_format char(1)=null )
as
declare @msg varchar(255)
declare @numpgsmb float  /* Number of Pages per Megabytes */
declare @tapeblocksize int
set nocount on
select @numpgsmb = (1048576. / v.low)
from master.dbo.spt_values v
where v.number = 1 and v.type = "E"
create table #dev_tbl
(
  name  char(30),
  phyname    char(255),
  disk_size float  null,
  status   int  null,
  disk_used float  null,
  mirrored   char(1)  null
)
insert  #dev_tbl
select  name=d.name,
    phyname = d.phyname,
    disk_size=0,
    status=status,
    disk_used=0,
    mirrored=NULL
  from master.dbo.sysdevices d
  where name=isnull(@devname,name)
/* Parallel */
update #dev_tbl
  set mirrored="P"
  where status & 64 = 64
/* Serial */
update #dev_tbl
  set mirrored="S"
  where status & 32 = 32
/* Disabled */
update #dev_tbl
  set mirrored="?"
  where status & 256 = 256
/* Confused */
update #dev_tbl
  set mirrored="?"
  where status & 32 = 32
    and   status & 512 != 512
/*  Add in its size in MB.  */
update #dev_tbl
  set disk_size = (1. + (d.high - d.low)) / @numpgsmb
  from master.dbo.sysdevices d, #dev_tbl
  where d.status & 2 = 2
    and #dev_tbl.name = d.name
update #dev_tbl
  set disk_used = ( select sum(size)
  from master.dbo.sysusages u, master.dbo.sysdevices d
  where d.status & 2 = 2
    and vstart between low and high
    and #dev_tbl.name = d.name
  group by name ) / @numpgsmb
update #dev_tbl
  set name=rtrim(name)+" ("+mirrored+")"
  where mirrored is not null
/*
if @devname is null
begin
  if @dont_format is not null
  begin
    print ""
    print "****** PHYSICAL DISK DEVICES (Mirror info after device name) ******"
  end
end
*/
update #dev_tbl set disk_used=0 where disk_used is null
if @dont_format is not null
  select
      "Device Name"=name,
      "Physical Name"=phyname,
      size=str(disk_size,7,1)+"MB",
      alloc=str(disk_used,7,1)+"MB",
      free=str(disk_size-disk_used,7,1)+"MB"
  from  #dev_tbl
  where status & 2 = 2
else
  select
      "Device Name"=substring(name,1,18),
      "Physical Name"=substring(phyname,1,31),
      size=str(disk_size,6,0)+"MB",
      alloc=str(disk_used,6,0)+"MB",
      free=str(disk_size-disk_used,6,0)+"MB"
  from  #dev_tbl
  where status & 2 = 2
if @devname is not null
begin
  if exists (select  *
        from master.dbo.sysdatabases d, master.dbo.sysusages u,
          master.dbo.sysdevices dv
        where d.dbid = u.dbid
        and dv.low < = size + vstart
        and dv.high >= size + vstart - 1
        and dv.status & 2 = 2
        and dv.name=@devname
  )
  begin
       select  db_name=d.name,
            size = size / @numpgsmb,
            usage = convert(char(18),b.name)
       from master.dbo.sysdatabases d, master.dbo.sysusages u, master.dbo.sysdevices dv,
            master.dbo.spt_values b
       where d.dbid = u.dbid
            and dv.low < = size + vstart
            and dv.high >= size + vstart - 1
            and dv.status & 2 = 2
            and b.type = "S"
            and u.segmap & 7 = b.number
            and dv.name=@devname
       order by db_name,usage
  end
  else
  begin
      if @dont_format is not null
        print "****** Device Unused By Any Databases ******"
  end
end
return (0)
go
/* Give execute privilege to users. This can be removed if you only want
   the sa to have excute privilege on this stored proc */
grant exec on sp__diskdevice_old to public
go
exit

No more temp table code:

/* Procedure copyright(c) 1995 by Edward M Barlow */
/************************************************************************\
|* Procedure Name:  diskdevice
|*
\************************************************************************/
:r database
go
:r dumpdb
go
if exists (select *
     from   sysobjects
     where  type = "P"
     and  name = "sp__diskdevice")
begin
  drop proc sp__diskdevice
end
go
create procedure sp__diskdevice (@devname char(30)=NULL, @dont_format char(1)=null )
as
declare @msg varchar(255)
declare @numpgsmb float  /* Number of Pages per Megabytes */
declare @tapeblocksize int
set nocount on
if @devname is null
begin
  select d.name as "Device Name",
    d.phyname as "Physical Name"
  from master.dbo.sysdevices d
    where d.status & 2 != 2
end
select
  case
  when (@dont_format is null)
    then
    substring(d.name, 1, 18)
  else
    d.name
  end as "Device Name",
  case
  when (@dont_format is not null)
    then
    substring(d.phyname, 1, 31)
  else
    d.phyname
  end as "Physical Name",
  convert(varchar(7),
  convert(int, ((1. + (d.high - d.low))*@@pagesize) / 1048576.)
  ) + "MB" as "size",
  convert(varchar(7),
  (select convert(int, sum(su.size) / (1024 / (@@maxpagesize/1024)))
    from master.dbo.sysusages su, master.dbo.sysdevices sd
    where sd.status & 2 = 2
    and su.vstart between sd.low and sd.high
    and d.name = sd.name)
  ) + "MB" as "alloc",
  convert(varchar(7),
  convert(int,
    ( ((1. + (d.high - d.low))*@@pagesize) / 1048576.) -
    (select convert(int, sum(su.size) / (1024 / (@@maxpagesize/1024)))
    from master.dbo.sysusages su, master.dbo.sysdevices sd
    where sd.status & 2 = 2
    and su.vstart between sd.low and sd.high
    and d.name = sd.name)
  )
  ) + "MB" as "free"
from master.dbo.sysdevices d
where d.status & 2 = 2
  and name=isnull(@devname,name)
if @devname is not null
begin
  if exists (select  *
    from master.dbo.sysdatabases d, master.dbo.sysusages u,
      master.dbo.sysdevices dv
    where d.dbid = u.dbid
    and dv.low < = size + vstart
    and dv.high >= size + vstart - 1
    and dv.status & 2 = 2
    and dv.name=@devname
  )
  begin
     select  db_name=d.name,
      size =  convert(int, u.size / (1024 / (@@maxpagesize/1024))),
      usage = convert(char(18),b.name)
     from master.dbo.sysdatabases d, master.dbo.sysusages u, master.dbo.sysdevices dv,
      master.dbo.spt_values b
     where d.dbid = u.dbid
      and dv.low < = size + vstart
      and dv.high >= size + vstart - 1
      and dv.status & 2 = 2
      and b.type = "S"
      and u.segmap & 7 = b.number
      and dv.name=@devname
     order by db_name,usage
  end
  else
  begin
    if @dont_format is not null
    print "****** Device Unused By Any Databases ******"
  end
end
return (0)
go
/* Give execute privilege to users. This can be removed if you only want
   the sa to have excute privilege on this stored proc */
grant exec on sp__diskdevice to public
go
exit
Share Button

FW: HOWTO: BlackBerry As Modem For Laptop

 

Introduction
Did you know that BlackBerry can now be used just like an external modem for a laptop computer, so that a laptop can have full Internet access? This is useful when you need to do certain things that a BlackBerry cannot yet do. Increasing numbers of success reports have caused me to post this message. (Note: Mark Rejhon has not himself yet successfully used a BlackBerry as a modem; I am aggregating information). Sometimes, it is still tricky to set up a BlackBerry as a modem for a laptop, but this feature is becoming more and more common in cellphones, and BlackBerry models are following suit.

 

Read more 

Share Button

Who is “She Who Must Be Obeyed,” and where did the phrase come from?

Rumpole covertly refers to his wife, Hilda, as “She Who Must Be Obeyed,”She,” or “S.W.M.B.O.” The phrase is from H. Rider Haggard‘s adventure novel, “She.” The title character, Ayesha, Queen of Kor, is known to hersubjects as SWMBO. (She, is available freely at Project Gutenberg)

In other words, tzurriz is my “SWMBO” 🙂

Share Button

Sybase ASE/RepServer monitoring

I originally posted this as a reply to Nicolaw on Livejournal

After a great deal of inspection, Nagios won’t work for what I need but I’m taking specific design ideas from it. Not that the design of Nagios is all that novel, but some parts of it are well thought out while other parts you just want to take the CLUE BAT (TM) to the developers. One of the big problems with Nagios, and similarily Big Brother, is that they don’t scale very well at all without significant customization.

I’m in the design phase of monitoring only about 100 Sybase ASE and Replication servers so while we aren’t talking about a lot of servers, the amount of information we are going to gather relating to performance, space utilization, diagnostics, and maintenance is considerable. Since this is a project for work, it is unlikely to see the light of the outside world 🙁 Maybe I’ll be able to convince the Powers that Be that releasing would be good PR. maybe..

Maybe on my spare time, I’ll create a lightweight Nagios plugin for the Sybase ASE and Sybase Replication server.

Oh, found out the ieee1394 driver in 2.6.17 doesn’t like a flakey external ieee1394 (firewire) harddrive too well. It causes my machine to restart. The enclosure also has a usb2 connection so I’ll try that this weekend. My other external harddrive (same model enclosure and hd model) works fine using ieee1394. I prefer ieee1394, because it is:

  1. faster than usb2
  2. less load on my box
  3. I use my firewire card for something other than the Dazzle Hollywood Bridge

Update:

One of my biggest gripes about Nagios is that it doesn’t really have a repository (back end database) where the data can be mined. I’m sure I can add a repository for all the data but I’m unsure whether it is worth the effort if I’m not able to release the code back to the Nagios people.

Share Button

FW: Sybase Workspace for ASE: Learn to Love SQL Development

 I received the following email from Sybase.  I’ve been testing Workspace and while there is quite a bit of work for Sybase to do before it is a viable tool for me, it does have potential.  It is based on the open source Eclipse project.

Are you tired of writing SQL by hand? Slogging through long stored procedures to find out where they may be failing? Sybase WorkSpace is just the tool you need to make your ASE development a breeze. WorkSpace is a development environment for the Sybase platform that combines industry-leading data modeling with visual SQL building, query analysis and sophisticated editing and debugging.

Continue reading “FW: Sybase Workspace for ASE: Learn to Love SQL Development”

Share Button

ISUG V-SIG for WorkSpace

ISUG V-SIG for WorkSpace

ISUG now has a Virtual Special Interest Group (V-SIG) for Sybase WorkSpace. SIGs are a great way to learn more about the product and collaborate with fellow users. Every month, there will be a presentation on a new development topic, including database development, implementing services and mobile development. This is also an opportunity to ask questions of Sybase engineering and product management. Put it on your calendar today.

Upcoming Schedule and Topics:

Sept. 14, 2006 Introduction to the SIG and WorkSpace
Oct. 12, 2006 Getting to Know the WorkSpace Environment
Nov. 9, 2006 Database Development
Jan. 11, 2007 Services Development
Feb. 8, 2007 Basic Business Process Orchestration
March 8, 2007 Web Application Development
April 12, 2007 XML Handling and Messaging
May 10, 2007 Team Development

All calls are at 11 a.m. PST / 2 p.m. EST. Phone and LiveMeeting information will be sent out via the mailing list, so be sure to sign up!

Contact Information:

Questions: WorkSpace@isug.com
Web: my.isug.com/workspace
Mailing List: sig-workspace@lists.isug.com
Dial-In Info: sig-info@isug.com
Share Button

Crazy driving on Cicero Ave this morning between 159th S St and 122 S St

This morning at about 9:30 AM CST, a white GMC Tahoe II ran a solid red stop light and proceeded to weave through traffic running a second solid red stop light.  At one point going under the Metra train tracks (Midlothian sp) underpass, this person pushed a red pickup into the left lane – in the path of oncoming traffic.  The driver of the Tahoe flipped off the driver of the red pickup and wove again through traffic, cutting people off and driving like she was drunk or high or just plain stupid.

I was able to keep relatively near her due to heavy traffic (I wasn’t weaving or even speeding).  Guess where this so-called lady in such a hurry to get to?  Dunkin Donuts

The photo was taken as she pulled into the Dunkin Donuts drive thru.

Illinois License Plate: DJR 500

Share Button