Need to upload a file to multiple Windows boxes?

If you don’t want to mess around with windows scripting and you just want to get the job done, well, the easiest method is sometimes the not so obvious…  use smbclient!  It’s part of Samba (no, not the samba style of music).

I’ve created a very simple script to automate smbclient.  It assumes that the login name, password and the directory on each of the windows boxes are the same.

Note that the directory is relative to the windows share.  For example the following two windows machines share the sybase directory as “sybase”.  Whether actual location of sybase is located on the C drive or somewhere else, doesn’t really matter as it is simply “\\<server>\sybase” to the rest of the network.

c:\sybase shared as \\mywin2k\sybase

d:\sybase shared as \\mywin2k3\sybase

Put either the ip address or the name of each of the windows machines you want to upload your file to into the smb_ips file.

192.168.0.70
192.168.0.71
192.168.0.17
192.168.0.101
192.168.0.23
192.168.0.24
192.168.0.25

Change the smb_user and smb_pass to your windows login/password.  Next, change upload_file to point to the file you need to upload to the windows box.  Finally, change the upload_dir to the directory on the windows box you want to upload the file to.

#!/bin/bash

smb_user=”login”
smb_pass=”password”

# EBF 13464.zip is Sybase ASE 12.5.1 esd 13 for windows
upload_file=”EBF13464.zip”
upload_dir=”sybase”

while read ip; do
echo Uploading ${upload_file} to ${ip}
smbclient –user $smb_user \\\\${ip}\\${upload_dir} $smb_pass < Uploading EBF13464.zip to 192.168.0.70
Domain=[DONUT_COOKIE] OS=[Windows 5.0] Server=[Windows 2000 LAN Manager]
putting file EBF13464.zip as \EBF13464.zip (330.5 kb/s) (average 330.5 kb/s)

Uploading EBF13464.zip to 192.168.0.71
Domain=[DONUT_COOKIE] OS=[Windows 5.0] Server=[Windows 2000 LAN Manager]
putting file EBF13464.zip as \EBF13464.zip (331.5 kb/s) (average 331.5 kb/s)

Uploading EBF13464.zip to 192.168.0.17
Domain=[DONUT_COOKIE] OS=[Windows 5.0] Server=[Windows 2000 LAN Manager]
putting file EBF13464.zip as \EBF13464.zip (330.4 kb/s) (average 330.4 kb/s)

Uploading EBF13464.zip to 192.168.0.101
Domain=[DONUT_COOKIE] OS=[Windows 5.0] Server=[Windows 2000 LAN Manager]
putting file EBF13464.zip as \EBF13464.zip (335.4 kb/s) (average 335.4 kb/s)

Uploading EBF13464.zip to 192.168.0.23
Domain=[DONUT_COOKIE] OS=[Windows 5.0] Server=[Windows 2000 LAN Manager]
putting file EBF13464.zip as \EBF13464.zip (327.5 kb/s) (average 327.5 kb/s)

Uploading EBF13464.zip to 192.168.0.24
Domain=[DONUT_COOKIE] OS=[Windows 5.0] Server=[Windows 2000 LAN Manager]
putting file EBF13464.zip as \EBF13464.zip (318.4 kb/s) (average 318.4 kb/s)

Uploading EBF13464.zip to 192.168.0.25
Domain=[DONUT_COOKIE] OS=[Windows 5.0] Server=[Windows 2000 LAN Manager]
putting file EBF13464.zip as \EBF13464.zip (329.5 kb/s) (average 329.5 kb/s)

Share Button

FW Ken Rearick: repserver exception list/deleter

My good friend Ken Rearick wrote a rather clever little stored procedure for managing all the exceptions that are raised in the life of a Sybase Replication Server.  He posted this on sybase.public.rep-server a few days ago:

This is a rewriten rs_delexception script that will list out all exceptions and then step through deleting all of them. As this uses the same procedure for deleting the entries it does not cause any problems with integerity in the RSSD — Ken Rearick

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

create proc rs_del_all_exception
@xact 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, 0×0000100) = 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_delexception {Xact ID}’" */
  exec rs_get_msg 20502, @msg output
  print @msg
  print " "
end

select @row_cnt = 1

while (@row_cnt > 0)
begin

set rowcount 1

  select @systran = sys_trans_id from #tab1

  select @row_cnt = @@rowcount

  delete #tab1 where @systran = sys_trans_id

set rowcount 0

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

  if (convert(int, 0×0000100) = 65536)
  begin
    select @cxact = convert(char(8), convert(int,
reverse(substring(@systran, 5, 8))))
  end
  else
  begin
    select @cxact =  convert(char(8), convert(int, substring(@systran, 5, 8)))
  end

  /* 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
  print " "
  print " "

  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_all_exception’,'unchained’
go

IF OBJECT_ID(‘dbo.rs_del_all_exception’) IS NOT NULL
    PRINT ‘< << CREATED PROCEDURE dbo.rs_del_all_exception >>>’
ELSE
    PRINT ‘< << FAILED CREATING PROCEDURE dbo.rs_del_all_exception >>>’
go

Share Button

How to create a graphical tutorial (would be wonderful for cross-training)

A fellow TeamSybase member pointed me to the freeware program Wink. I think it will do very nicely for a few projects I have….

Wink is a Tutorial and Presentation creation software, primarily aimed at creating tutorials on how to use software (like a tutor for MS-Word/Excel etc). Using Wink you can capture screenshots, add explanations boxes, buttons, titles etc and generate a highly effective tutorial for your users.

Here is a sample Flash tutorial created by Wink.

Continue reading “How to create a graphical tutorial (would be wonderful for cross-training)”

Share Button

How to create a ramdisk on AIX for Sybase

As root:

mkramdisk 2700M
chown sybase /dev/rramdisk0

Granted, the memory would be better off used as a larger cache for tempdb, but some individuals believe that tempdb is faster on a ramdisk (it isn’t).

Share Button

RIP: Sybase’s ADO.NET component

According to http://www.sybase.com/detail?id=1038292, Sybase management in their infinite wisdom has killed the ADO.NET 2.0 component so they can push their DataWindow .NET 2.0 product. 

Does anyone honestly care about DataWindow .NET except for a few PowerBuilder developers?  Honestly?  Sybase has been known for making mistakes (i.e. practically giving Microsoft SQL Server.. what 10 years ago?) and this is no small one.  Sybase can be quite the idiot.

UPDATE:

Ajit Sabnis wrote:
> Hi Jason
>
> I have no idea how this conclusion was reached. Let me assure that the
> conclusion is not correct. Two things:
>
> – Please refer to the end of the press release that talks about futures and
> the first bullet is:
>         Supporting ADO.NET providers through ASE 15.0 in order to allow .NET
>         Framework-based applications to access ASE 15.0 databases through
> ADO.NET
> – The ADO.NET 2.0 Data Provider is being developed and tentatively targeted
> for 1H 2007.
>
> HTH
>

Hi Ajit,

thank you for the clarification! 🙂   The press release was written in a way that implied that the ADO.NET 2.0 provider would be only in the DataWindow .NET component.  I am greatly relieved 🙂 

Jason 

Share Button

DOL Table: What exactly is a clustered index?

There seems to be a great deal of confusion as to what a clustered index on a Data Only Locked table (Data page locked or row level locking) actually is.

A clustered index on a DOL table is really a NONCLUSTERED INDEX.  The index key of this pseudo-clustered index is used for sorting the data when you perform a reorg rebuild.  That’s about it. 

Share Button

MDA monEngine and 11213 error

When attempting to get the current status of the ASE 12.5.3 esd 2 server mydb1, I ran:

select * from master..monEngine

I received the error 11213:

Msg 11213, Level 16, State 2
Server ‘mydb1’, Line 1
Column count mismatch in remote object ‘monEngine’; (defined: 19 found 15).
Command has been aborted. 

I’ve opened Sybase case 11300469 for an explanation.  I suspect that the 11213 error has been fixed (Sybase’s Solved Cases is down at the moment).

Continue reading “MDA monEngine and 11213 error”

Share Button

How to reconnect a dead DBD::Sybase Connection

It is important to know that this is just one method of reconnecting a dead connection.  Note that we are handling the errors manually for the individual query.  It wouldn’t take much to create a db_exec subroutine so we just call the db_exec() subroutine and just worry about the reconnect in one place.

Notice that because of DBD::Sybase bug # 616, the error 151 will be printed to STDERR.

./test_sybase
OpenClient message: LAYER = (1) ORIGIN = (1) SEVERITY = (1) NUMBER = (151)
Message String: ct_cancel(): user api layer: external error: A connection to the server must exist on the connection structure before this routine can be called.
ERROR: Connection to DBMS died
syb_db_disconnect(): ct_close() failed
MSG: ERROR: Reconnected

#!/usr/bin/perl

use strict;
use warnings;

use lib “/home/jfroebe/lib”;

use DBI;
use File::Basename;

our $SYBDBA1_login = ‘login’;
our $SYBDBA1_password = ‘password’;

our $dbh = connect_dbms();

sub report_err {
my $msg = shift;
my $type = shift;

if ($type eq ‘error’) {
print “ERROR: $msgn”;
} else {
print “MSG: $msgn”;
}
}

sub exit_on_error {
my $msg = shift;

report_err($msg, “error”);
return -1;
}

sub connect_dbms {
my $loc_dbh;
my $script = basename($0);

if ($loc_dbh = DBI->connect(“dbi:Sybase:server=SISDBA1;loginTimeout=10;timeout=30;scriptName=$script;encryptPassword=1;tdsLevel=CS_TDS_50;charset=iso_1”, $SYBDBA1_login, $SYBDBA1_password, { PrintError => 0, RaiseError => 1 } )) {
return $loc_dbh;
}

report_err(“unable to connect to SISDBA1”, “error”);
return;
}

sub syb_loop {
my $query = “exec sp_helpdb”;

RETRY: for (my $i = 0; $i < 10000; $i++) { my $array_ref; eval { $array_ref = $dbh->selectall_arrayref($query);
};

if ($@) {
if ($dbh->err == 151 || $dbh->err == 60) {
report_err(“Connection to DBMS died”, “error”);
undef($dbh);

for (my $i = 0; $i < 10; $i++) { if( $dbh = connect_dbms() ) { report_err("Reconnected", "msg"); redo RETRY; } sleep 3; } exit_on_error("Unable to reconnect after $i attempts!"); } } } } syb_loop(); [/perl]

Share Button

The Chicago Hackathon 2006

I’m planning on going this Saturday…  are you?

The Chicago Perl Mongers and The Perl Foundation are proud to announce the Fall 2006 Chicago Hackathon, the weekend of November 10-12, 2006 in suburban Crystal Lake, IL. It will be a round-the-clock weekend of programming on Perl-related projects with your colleagues in the open source community. Dozens of programmers from the open source community in the midwest, as well as others from around the US, will be getting together to share ideas, work on code, and move their Perl-related projects forward.

Country Inn and Suites
600 Tracy Trail
Crystal Lake, IL 60014
815-477-3500
815-477-0189 fax
http://www.countryinns.com/crystallakeil
Google Maps | Yahoo! Maps | MapQuest

Fall 2006 Chicago Hackathon is sponsored by:

Share Button

DBD::Sybase – reconnect through err_handler (feature request)

I received an email from Michael Peppler, the creator/maintainer of DBD::Sybase. In it, he says that this is a limitation of Sybase’s OpenClient CT-Library not DBD::Sybase.

The error handler is called by Sybase’s ct_callback() function. Sybase has restricted what CT-Library calls that can be made. Apparently while ct-connect() can be called, memory allocation of the connection will be disallowed.

I’m opening a number of feature requests with Sybase to make reconnecting, when the connection dies, easier to do.

I’m writing an application with a business requirement of reconnecting a dead db connection to Sybase through the syb_err_handler() error handler routine.

In the past, I would just run $dbh->DBDEAD() just prior to running the queries. If the connection was dead, I would just reconnect and run the query (plus any initialization db routines).

The syb_err_hander() can easily catch a dead connection but calling $dbh->connect() in the error handler fails

./test_sybase 
ERROR: Connection dropped ct_config(CS_SET, CS_LOGIN_TIMEOUT) failed at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/Sybase.pm line 94. 
ct_config(CS_SET, CS_TIMEOUT) failed at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/Sybase.pm line 94. 
ct_con_alloc failed at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/Sybase.pm line 94. 
ERROR: unable to connect to SISDBA 

Has anyone done this? I’m starting to think that this can’t be done through the error handler of DBD::Sybase

#!/usr/bin/perl
use strict;
use warnings;
use lib "/home/jfroebe/lib";
use DBI;
use File::Basename;
our $SYBDBA_login = ‘login’;
our $SYBDBA_password = ‘password’;
our $dbh = connect_dbms();
sub report_err {
        my $msg = shift;
        my $type = shift;
        if ($type eq ‘error’) {
          print "ERROR: $msgn";
        } else {
          print "MSG: $msgn";
        }
}
sub syb_err_handler {
    my($err, $sev, $state, $line, $server, $proc, $msg, $sql, $err_type) = @_;
    my %ignore_errors = map { $_, 1 } (6, 183, 208, 2056, 2057, 14024, 17001);
    if ($err == 151 || $err == 50 || $err == 60) {
       report_err("Connection dropped", "error");
       for (my $attempt = 0; $attempt < 10; $attempt++) {
          if ($dbh = connect_dbms()) {
             report_err("Reconnected", "error");
             return 0;
          }
          sleep 2;
       }
       return 1;
    } elsif ($err == 63 || ($err == 4 && $sev == 5) ) {
        report_err("Connection timed out", "error");
        return 1;
    } elsif ($ignore_errors{$err}) {
        # ignore
        return 0;
    }
    print "error: $err, $sev, $state : $msgn";
    return 1;
}
sub connect_dbms {
    my $script = basename($0);
    if ($dbh = DBI->connect("dbi:Sybase:server=SISDBA;loginTimeout=10;timeout=30;scriptName=$script;encryptPassword=1;tdsLevel=CS_TDS_50;charset=iso_1", $SYBDBA_login, $SYBDBA_password, { PrintError => 0, RaiseError => 0, syb_err_handler => &syb_err_handler } )) {        return $dbh;    }
    report_err("unable to connect to SISDBA", "error");
    return;
}
sub syb_loop {
        my $query = "exec sp_helpdb";
        for (my $i = 0; $i < 10000; $i++) {
                my $array_ref = $dbh->selectall_arrayref($query);
        }
}
syb_loop();
Share Button