XML and NASA

"A key to addressing the data heterogeneity issue is the adoption and enforcement of standards across information systems. While XML is clearly emerging to be the lingua franca for databases and internet systems, it is encouraging to see the enforcement of standards even at the level of Microsoft® Word and Microsoft® Powerpoint® documents"
-- National Aeronautics and Space Administration (NASA)

XML is so wonderful that I created this blog to tell you about it. You don't need to be a Rocket Scientist to understand how powerful XML is but apparently it doesn't hurt. Recently I Googled "NASA Business Intelligence" and found an article posted on stanford.edu titled, The NASA Program Management Tool: A New Vision in Business Intelligence. So, what technology sits at the core of Business Intelligence (BI) at NASA? An elite team of Data Scientists from MIT, Berkeley, Cornell and Stanford determined that only XML would do. Of course. Below is a link to the article but let me share a few highlights:

This paper describes a novel approach to business intelligence and program management for large technology enterprises like the U.S. National Aeronautics and Space Administration (NASA). Two key distinctions of the approach are that 1) standard business documents are the user interface, and 2) a “schema-less” XML database enables flexible integration of technology information for use by both humans and machines in a highly dynamic environment. The implementation utilizes patent-pending NASA software called the NASA Program Management Tool (PMT) and its underlying “schema-less” XML database called Netmark.

...a 'schema-less' XML database enables integration and query-based document composition, eliminating the need for database administration by automating the integration of information that have diverse schemas.

...Netmark supports the XML standard for metadata and information interchange thus making it an open system that is interoperable with a wide variety of other information systems and tools.

It is worth noting that NASA uses the Xalan XSLT processor to transform their XML. Nice.

--ab

Additional Reading:

http://infolab.stanford.edu/~maluf/papers/ieeepmt06.pdf
http://www.nasa.gov/pdf/560758main_Program%20Management%20Tool.pdf

SP_WHO3

Wouldn't it be nice to have the ability to be able to pass more parameters to SP_WHO or SP_WHO2? Well I just wrote SP_WHO3 which allows you to pass the following parameters: @DBName, @SPID, @Status, @Login, @hostName, @Command, @CPUtime_floor, @DiskIO_floor, @ProgramName and @precision_bit.

Furthermore, you can set @precision_bit=0 to get a close match (see comments in the code). Enjoy!

SP_WHO3:

CREATE PROC sp_who3

(      @exact_flag          bit=1,

       @DBName              varchar(100)='*All*',

       @SPID                int=-1,

       @Status              varchar(40)='*All*',

       @Login               varchar(50)='*All*',

       @hostName            varchar(100)='*All*',

       @Command             varchar(200)='*All*',

       @CPUtime_floor       bigint=-1,

       @DiskIO_floor        bigint=-1,

       @ProgramName         varchar(100)='*All*'

)

AS

/******************************************************************

 Author:      Alan Burstein

 Created on:  9/5/2012

 Updated on:  4/19/2013

 

 Use:

 Returns the same columns as SP_WHO2 but you can pass parameters

 and filter the result set.

 

 With @exact_flag=1 (default) you only get records where there

 is an EXACT (=) match. For example, when @exact_flag=1 and

 @Command='TASK' nothing will be returned. If exact_match_flag=0

 and @Command='TASK' then records where Command="TASK MANAGER",

 Command="BRKR TASK", etc will be returned.

******************************************************************/

BEGIN

 SET NOCOUNT ON;

 

 -- Create table to hold the results of EXEC SP_WHO2

 DECLARE @sp_who3 TABLE

 (     SPID          int,

       [Status]      varchar(40),

       [Login]       varchar(50),

       HostName      varchar(100),

       BlkBy         varchar(4),

       DBName        varchar(100),

       Command       varchar(200),

       CPUtime       bigint,

       DiskIO        bigint,

       LastBatch     varchar(100),

       ProgramName   varchar(100),

       SPID2         int,

       REQUESTID     int);

 

 INSERT INTO @sp_who3 EXEC SP_WHO2;

 

 -- output

 IF @exact_flag=1

 BEGIN

  SELECT * FROM @sp_who3

  WHERE (DBName=@DBName OR @DBName='*All*') AND

    ([Status]=@Status OR @Status='*All*') AND

    (SPID=@SPID OR @SPID=(-1)) AND

    ([Login]=@Login OR @Login='*All*') AND

    (HostName=@hostName OR @hostName='*All*') AND

    (Command=@Command OR @Command='*All*') AND

    (CPUtime>=@CPUtime_floor OR @CPUtime_floor=(-1)) AND

    (DiskIO>=@DiskIO_floor OR @DiskIO_floor=(-1)) AND

    (ProgramName=@ProgramName OR @ProgramName='*All*')

  ORDER BY LastBatch DESC

 END

 ELSE

 BEGIN

  SELECT @DBName='%'+CAST(@DBName as varchar(100))+'%',

    @Status='%'+CAST(@Status as varchar(40))+'%',

    @Login='%'+CAST(@Login AS varchar(50))+'%',

    @hostName='%'+CAST(@hostName AS varchar(100))+'%',

    @Command='%'+CAST(@Command AS varchar(200))+'%',

    @ProgramName='%'+CAST(@ProgramName AS varchar(100))+'%'

 

  SELECT * FROM @sp_who3

  WHERE (DBName LIKE @DBName OR @DBName='%*All*%') AND

    ([Status] LIKE @Status OR @Status='%*All*%') AND

    (SPID=@SPID OR @SPID=(-1)) AND

    ([Login] LIKE @Login OR @Login='%*All*%') AND

    (HostName LIKE @hostName OR @hostName='%*All*%') AND

    (Command=@Command OR @Command='%*All*%') AND

    (CPUtime>=@CPUtime_floor OR @CPUtime_floor=(-1)) AND

    (DiskIO>=@DiskIO_floor OR @DiskIO_floor=(-1)) AND

    (ProgramName LIKE @ProgramName OR @ProgramName='%*All*%')

  ORDER BY LastBatch DESC

 END

END

GO

--SP_WHO3 @exact_flag=0, @ProgramName='repl', @CPUtime_floor=10000

 

Example:

Epilogue

Before posting this I googled SP_WHO3. It turns out I was not the first to come up with this idea. Bummer. My SP_WHO3 is still great.

--ab

Last Updated: 4/19/2013 (code cleanup)