HowTo: Comparing two tables and returning the # of rows that match FAST

The most intuitive way to write a comparison of two tables and spit out the # of rows that are the same involves a left join:

select 'Investment' as TableName, count(*) as RowCount
from Investment_A a, Investment_B b
where
  a.col1 = b.col1
  AND a.col2 = b.col2
  AND a.col3 = b.col3
  AND a.col4 = b.col4

This returns the correct answer but is very slow. Is there a better way? Of course!

select 'Investment' as TableName, count(*) as RowCount
from
(
   select 1 as num
   FROM (
      select * from Investment_A
      UNION ALL
      select * from Investment_B
   ) tmp
   GROUP BY col1, col2, col3, col4
   HAVING COUNT(*) > 1
) tmp2

By pushing the comparison off into the GROUP BY, we leverage the DBMS engine far more efficiently. There are two drawbacks:

  1. Readability of the SQL code
  2. Far more temporary storage is used for the GROUP BY. There is a real risk of running out of temporary storage if the tables are large.

Wanted: Technical Support Analyst – WebSphere Linux SQL (2 positions available)

Position Responsibilities

POSITION OVERVIEW

The Technical Support Analyst for SunGard InvestOne’s global clients will be responsible for hands-on technical troubleshooting and support across a variety of platforms for a broad customer client base. This position is part of a global team with local responsibilities from 8am to 8pm.

The Analyst will be responsible for Tier 1 level support, which includes break/fix management and communications to internal stake holders regarding status and problem tracking.

He/She will also be responsible for performance, availability and customer satisfaction for all levels of a series of n-tier distributed web based applications.

PRIMARY RESPONSIBILITIES

• Monitor error alerts from various systems
• Document incidents as warranted
• Answer client questions relating to technical aspects of the application
• Adhere to change control policies
• Responsible for first level technical system support for multiple customers along with implementation of network connectivity for new and existing customers
• Position is 80% technical and 20% project/customer management
• Participation on an on-call rotation is required
• Occasional weekend work is required

Position Requirements

• Bachelor’s degree, preferably in an IT-related discipline, or the equivalent of education and experience
• Minimum of five years of related, technical experience, preferably in the financial systems industry
• Experience with troubleshooting, diagnosis and resolution of complex technical issues as well as escalation for complex web based applications
• Knowledge of Windows and Linux administration
• Knowledge of WebSphere
• Experience with Transact SQL, preferably Sybase
• Excellent communications skills and ability to translate technical concepts
• Excellent interpersonal, analytical, business and technical judgment, negotiation, problem solving, verbal and written communication skills
• Critical thinking skills and a mature approach to troubleshooting is critical
• Knowledge of investment accounting principles is a big plus
• Previous experience as Java developer is desired
• ITIL certification is desired but not required

Interested? (say I referred you on the application form) or this one

Should ORMs Insulate Developers from SQL?

I’ve been sick the past few days, so I have been hitting the “Stumble” button a lot on my laptop. Last night I ran across a very interesting blog post by Daniel Spiewak on his Code Commit blog:

This is a question which is fundamental to any ORM design.  And really from a philosophical standpoint, how should ORMs deal with SQL?  Isn’t the whole point of the ORM to sit between the developer and the database as an all-encompassing, object oriented layer?

read more…

What do you think?  Is he on to something?

unable to create a SQL UDF in Sybase ASE using a case statement

create function ok (@boolean bit)
returns varchar(2000) as
declare @output_string varchar(2000)
select @output_string = case when @boolean = 1 then "" else "not " end
set @output_string = @output_string + "ok"
return @output_string
end

Msg 156, Level 15, State 2
Server ‘DBADEV1’, Procedure ‘ok’, Line 7
Incorrect syntax near the keyword ‘end’.
I’m trying to write an ASE implementation of TAP. A rudimentary TAP implementation for PostgreSQL is at http://www.justatheory.com/computers/databases/postgresql/introducing_pgtap.html
I’ve been able to use case in SQL UDFs before – see http://froebe.net/blog/2007/10/10/porting-mysqls-date_format-function-to-sybase-ase-1502/
Adaptive Server Enterprise/15.0.2/EBF 15654 ESD#4/P/Linux Intel/Linux 2.4.21-47.ELsmp i686/ase1502/2528/32-bit/FBO/Sat Apr 5 05:18:42 2008
I’m just missing something blindingly simple… I just know it.

FW: Learn to Love SQL Development Webcast (Sybase Workspace)

From the Sybase Workspace 2.0 team:

Thursday, February 14, 2008
1:00 pm EDT / 10:00 am PDT

Join us on Valentine’s Day and you will learn how to:

  • Visually debug stored procedures and triggers
  • Visually create and edit tables, stored procedures and events
  • Navigate and manipulate database objects in the enterprise
  • Export and import database objects and services to database server

Don’t forget to sign up for the webcast! 🙂  Sybase will send the dial in/web address to your email so you can participate.

I’m not sure if this is the same presentation as Sybase WorkSpace for ASE:  Learn to Love SQL Development Webinar that was hosted by Samir Nigam, Director of Engineering at Sybase, last month.

Tying it all together: Extracting SQL Batches, Highlighting Code with Kate, and Sending an email with attachments

In How to read batches of SQL from a file in Perl, we learned how to read a SQL file and extract individual batches and created the Perl module dbS::Sybase::Parse::SQL_File. In How to colorize your code using Perl and Syntax::Highlight::Engine::Kate, we learned how to make HTML highlighted arbitrary code and created Perl module dbS::misc. Finally, in Sending email with attachments from Perl – the easy way, we learned how to send email with attachments and created Perl module dbS::EMail.

Now, we are going to tie them all together so we can send ourselves colorized SQL code and a screenshot of an application going screwy!

#!/usr/bin/perl

use strict;
use warnings;

use File::Basename;

use dbS::EMail;
use dbS::misc;
use dbS::Sybase::Parse::SQL_File;

our $PROC = basename($0);

$|++;


my $message = "";

if ( my $sql_batch = dbS::Sybase::Parse::SQL_File::get_batch("/dbms/sybase/ASE-15_0/scripts/installmontables", undef, 1) ) {
   while (my $query = $sql_batch->next ) {
      $message .= $query;
      $message .= "go\\n\\n";
   }
} else {
   warn("unable to open the SQL file\\n");
}


if ( dbS::EMail::simple_send(
        'jason@froebe.net.nospam',
        $PROC . ': test HTML email',
        any_to_html('SQL', $message),
        undef,
        { type => 'html', 'files' => "/tmp/AppBug.jpg" }
   )) {
   print "SUCCESS!  Email sent\\n";
} else {
   print "ERROR:  Email send failed\\n";
   printf "ERROR:  Email Transport: %s\\n", $dbS::EMail::error if $dbS::EMail::error;
   printf "ERROR:  Email Transport Log: %s\\n", $dbS::EMail::log if $dbS::EMail::log;
}

How to colorize your code using Perl and Syntax::Highlight::Engine::Kate

Yesterday I was scratching my head on how to send an email to myself when an application failed or a certain error occurred. You see, I wanted to be able to read an email on my crackberry (Blackberry) that showed that a problem occurred and more importantly, I wanted the error messages and the snippet of code where the problem occurred.

The first part of sending the email was very simple by using Mail::Sendmail, but I wanted to extend it a bit by sending highlighted code. The problem is that the documentation of Syntax::Highlight::Engine::Kate isn’t too clear on how to use it.

The Syntax::Highlight::Engine::Kate is surprisingly easy to use once you understand that Kate just parses whatever string you give it for tokens and lets you handle how you want each type of token highlighted. In truth, you could easily forsake HTML and have it spit out XML or something else if you so desired.

package dbS::misc;
use strict;
use warnings;

our $PROC = basename($0);

BEGIN {
    use Exporter ();

    our ($VERSION, @ISA, @EXPORT, @EXPORT_OK, %EXPORT_TAGS);
    $VERSION = 1.0.0;

    @ISA = qw(Exporter);
    @EXPORT = qw(&any_to_html);
}

sub any_to_html {
    my ($type, $content) = @_;

    require Syntax::Highlight::Engine::Kate;

    my $hl = new Syntax::Highlight::Engine::Kate(
        language => $type,
        substitutions => {
            "< " => "< ",
            ">" => ">",
            "&" => "&",
            " " => " ",
            "\\t" => "   ",
            "\\n" => "\\n",
        },
        format_table => {
            Alert => ["<span style="color: #0000ff;">", "</span>"],
            BaseN => ["<span style="color: #000000;">", "</span>"],
            BString => ["<span style="color: #00c9ff;">", "</span>"],
            Char => ["<span style="color: #00ffff;">", "</span>"],
            Comment => ["<span style="color: #007f7f;"><em>", "</em></span>"],
            DataType => ["<span style="color: #0000ff;">", "</span>"],
            DecVal => ["<span style="color: #00007f;">", "</span>"],
            Error => ["<span style="color: #00ff00;"><strong><em>", "</em></strong></span>"],
            Float => ["<span style="color: #00007f;">", "</span>"],
            Function => ["<span style="color: #000000;">", "</span>"],
            IString => ["<span style="color: #00ff00;">", ""],
            Keyword => ["</span><span style="color: #000e00;"><strong>", "</strong></span>"],
            Normal => ["", ""],
            Operator => ["<span style="color: #00ff00;">", "</span>"],
            Others => ["<span style="color: #00b060;">", "</span>"],
            RegionMarker => ["<span style="color: #0096ff;"><em>", "</em></span>"],
            Reserved => ["<span style="color: #009bff;"><strong>", "</strong></span>"],
            String => ["<span style="color: #00ff00;">", "</span>"],
            Variable => ["<span style="color: #0000ff;"><strong>", "</strong></span>"],
            Warning => ["<span style="color: #0000ff;"><strong><em>", "</em></strong></span>"],
        },
    );

    return $hl->highlightText($content);
}

1;

I wrote a wrapper subroutine for Syntax::Highlight::Engine::Kate so I can just call any_to_html(‘parsing template’, $string) and it will return HTML code.

#!/usr/bin/perl

use strict;
use warnings;

use File::Basename;
use Syntax::Highlight::Engine::Kate;

use dbS::misc;
use dbS::Sybase::Parse::SQL_File;

our $PROC = basename($0);

$|++;

my $message = "";

if ( my $sql_batch = dbS::Sybase::Parse::SQL_File::get_batch("/dbms/sybase/ASE-15_0/scripts/installmontables", undef, 1) ) {
    while (my $query = $sql_batch->next ) {
        $message .= $query;
        $message .= "go\\n\\n";
    }
} else {
    warn("unable to open the SQL file\\n");
}

print $message

The language option (a parsing template) and format_table option (token into html code) are required but the substitutions option is optional but I strongly recommend using it else the output will look odd.

The following is the output of $SYBASE/$SYBASE_ASE/scripts/installmontables using our little application. Note that we are reading the batches one by one (just because we can) even though it isn’t necessary for this example.

Continue reading “How to colorize your code using Perl and Syntax::Highlight::Engine::Kate”

How to read batches of SQL from a file in Perl

Update: I fixed the code to actually use the iterator as an iterator 🙂

Scenario: You have a Perl application that performs lots of stuff but you are handed a SQL text file that you need to run on a regular basis from within your application.

Solution #1: Call isql from within your Perl application to run the SQL text file. This makes your code dependent on an external program.

`$SYBASE/$SYBASE_OCS/bin/isql -U $login -P $pass -S $DBMS -i $SQL_FILE`;

Solution #2: Read the SQL text file and send each batch to the database using Perl. In this case, we aren’t performing any real parsing of the SQL itself, we are simply retrieving the individual SQL batches. I’m using Rintaro Ishizaki’s Iterator::Simple Perl module so we can very easily get the next SQL batch.

Our Perl module:

package dbS::Sybase::Parse::SQL_File;

use warnings;
use strict;

use Iterator::Simple qw(iterator);

BEGIN {
    use Exporter ();

    our ($VERSION, @ISA, @EXPORT, @EXPORT_OK, %EXPORT_TAGS);
    $VERSION = 1.0.0;

    @ISA = qw(Exporter);
    @EXPORT_OK = qw(&get_batch);
}

our $FH;

#############################
sub _open_file {
    my $file = shift;

    my $FH;

    open ($FH, "< ", $file)
       or warn("unable to open $file file\\n");

    return $FH;
}

#############################
sub get_batch {
   my ($file, $delimiter) = @_;

   # The delimiter is a single or multiple characters
   #  that are on a line by itself.
   $delimiter = 'go' unless $delimiter;

   if ( my $FH = _open_file($file) ) {
     # Why use an iterator?  The SQL file may be huge
     #  so we want to retrieve only a single batch at
     #  a time.
      iterator {
         if (fileno $FH) {
           # Since this is an iterator, we need to
           #  verify that the $FH filehandle is valid.
           # fileno() will return a file descriptor
           #  if the $FH file handle is valid else
           #  it will return undef.
           my $query = "";

           while (my $line = <$FH>) {
                chomp $line;
                last if ($line =~ m/^$delimiter\\s*$/i);
                $query .= $line . " ";
           }

           return $query;
        }
    }
  }
}

1;

Obtaining the individual batches are now very easy. Note, that we are making several assumptions:

  1. SQL batches end with a go (case insensitive)
  2. SQL code is valid
  3. security of the SQL text file is handled by the operating system (we’re not going to worry about SQL injection attacks at this level)
use dbS::Sybase::Parse::SQL_File qw(get_batch);

....

if ( my $batch = dbS::Sybase::Parse::SQL_File::get_batch("SQL/SNAP.sql") ) {
        print "-"x40 . "\\n";
        print " Performing IGOR\\n";
        print "-"x40 . "\\n";

        while ( my $sql_batch = $batch->next ) {
            dbh_do($local_dbh, $sql_batch);
        }
}

Granted, we could have performed this without the iterator, but this is just the first revision. I expect to be adding a lot more to it (e.g. T-SQL verifier) so that I can hide the complexity behind the iterator.

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?

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