LevSelector.com New York
home > Sybase

Sybase
 
On this page: Other pages:
* links
* tools
* books
* hints
* bcp
* results into excel		 * sql_sybase
* isql
* sample_dblib.cgi
* sample_dblib.pl
* sample using dbi
Links home - top of the page -

* directory.google.com/Top/Computers/Software/Databases/Sybase/ - on Google
* www.sybase.com - Sybase, Inc. - Main site for Sybase SQL Server
* www.sybase.com/support/manuals/ - Sybase online documentation (sybooks).  (ASE, AS-IQ-M)
(also http://sybooks.sybase.com/- manuals ) -
* http://manuals.sybase.com/onlinebooks/group-as/srv10024/quickref - quick reference
* http://www.ncst.ernet.in/dbois/quickref/- quick reference

* www.sybase.com/products/databaseservers/ase/ - Adaptive Server Enterprise - main standard database server for OLTP and DSS
* www.sybase.com/products/databaseservers/asiq/ - Adaptive Server IQ Multiplex - a relational database designed to do very fast queries (ad hoc query x100 times faster than traditional RDBMSs ). This is achieved mostly because queries are indexed and processed "by column" instead of "by row".  Very good database for OLAP (DSS), very good scalability to support 1000s of users and terabytes of data IQ for the Web.
* www.sybase.com/products/anywhere/ - SQL Anywhere Studio (client/development studio)

* www.isug.com/ - Sybase User Group - International (ISUG) - International Sybase User's Group home page.
* www.edbarlow.com/ - Ed Barlow's Sybase Stuff - A non-commercial repository of material useful to developers and DBA's. Includes freeware such as a widely used extended stored procedure library.
* www.tiac.net/users/sqltech/links/links.pl - Ed Barlow's Sybase Links - Categorized links of Sybase sites.

* www.mbay.net/~mpeppler/ - Michael Peppler's Home Page - he is the author of sybperl, the Sybase OpenClient extensions to Perl.

* www-es1.sybase.com/ - Sybase Support - Main - Main page for support of Sybase products.
* techinfo.sybase.com - Sybase Documentation - Technical Information Library - Sybase Techincal Information Library @sybase.com

* www.voicenet.com/~gray/sqsh.html - SQSH 1.7 - Home page of SQshelL, the alternative for the isql program supplied by Sybase. Includes downloadable software.

* www2.dgsys.com/~dcasug/ - Sybase User Group - Washington DC - DC Area Sybase User's Group home page.
* sybernet.sri.com/ - Sybernet - Stand alone CGI daemon in Unix which holds connections to the Sybase SQL server on behalf of a stateless web client.
* sybernet.sri.com/instructions.html - A Guide for Writing Sybase Stored Procedures for the World Wide Web by Denis D. Workman.

* www.sypron.nl/ - Sybase Tools and Documentation by Rob Verschoor. You can order there ( www.sypron.nl/qr/ ) a very good book - "The Complete Sybase ASE Quick Reference Guide".

* www2.dgsys.com/~dcasug/sybintro/intro.html - Sybase Architecture - Introduction - Introduction to the Sybase architecture written by Ben Slade. Ben is a software consultant specializing in Sybase and configuration
management.

* www.sqlworkshop.com/ - The SQL Workshop - A Sybase Open Solutions CODE partner that provides consultation to Sybase DBA's and developers.
* www.mag-sol.com/Sybase/ - Sybase Contractor's Resource Page - Resources for the Sybase contractor, including employment information.
* www.whirlycott.com/phil/dbdsybase/cms.html - DBD Sybase on Linux Discussion Group - Online forum for DBD::Sybase on Linux.
* www.alaska.net/~pacrim/sybase.html - Sybase Links - Pacific Rim - List of Sybase resources links maintained by alaska.net.

* www.primenet.com/~thain/sybase/SybaseLinux.html - Connecting to Sybase or Microsoft SQL Server databases from Linux.
* www.acs.ncsu.edu:80/Sybase - Sybase Archive - North Carolina State University (NCSU) - Sybase information including an F.A.Q., documentation, free downloadable tools, links.

 
Tools home - top of the page -

* use  DBArtisan , RapidSQL from embarcadero (also ER Studio).

* isql , wisql, SQL Advantage - from Sybase.

* www.voicenet.com/~gray/sqsh.html - " Sqsh (pronounced skwish) is short for SQshelL (pronounced s-q-shell), it is intended as a replacement for the venerable 'isql' program supplied by Sybase. It came about due to years of frustration of trying to do real work with a program that was never meant to perform real work. Sqsh is much more than a nice prompt, it is intended to provide much of the functionality provided by a good shell, such as variables, redirection, pipes, back-grounding, job control, history, command completion, and dynamic configuration. Also, as a by-product of the design, it is remarkably easy to extend and add functionality.

 
Books home - top of the page -

Books:
* Sybase Sql Server 11 Unleashed (1996) by Ray Rankins(Editor), et al
* Sybase Dba Survival Guide (1996) by Jeffrey R. Garbus

* The Complete Sybase ASE Quick Reference Guide (2001) - order at www.sypron.nl/qr/ - very useful reference in small format.  When I received 1st copy - I immediately ordered several more. You can also download a "Sybase Replication Server Quick Reference Guide" from http://www.sypron.nl/rs_qref.html .

* The Sybase Sql Server Survival Guide (1996) by Jim Panttaja et al
* Sybase Dba Companion (1997) by Brian Hitchcock
* Transact-SQL Programming (1999) by Kevin E. Kline et al - O'Reilly (1st chapter)

 
Hints home - top of the page -

Hints

Play with those commands:
========================================
set rowcount 10
select * from table -- show 10 rows of a table

select * from table where 1=2 -- show column names (and widths separators)
========================================
correct way to check if something is (not) null:
 is null       is not null

remember:
   - you can not insert a null value into a column defined NOT NULL unless a column has a default value bound to it
   - null values are not equal to each other (don't expect them to match in where clause)
   - a row containing a null value is ignored in the calculation of aggregate values (AVG, SUM, MAX)

comparing fields one of which may be NULL:
   - change to "where exists" or "where not exists", for example:
  select t1.col11
  from mytab t1
  where
   not exists (select * from mytab t2 where t2.parent_id = t1.id)
   and t1.id not in (select id from some_other_table)
========================================
sp_helpdb -- show short help on all databases
sp_helpdb  some_database -- show help on all databases

use somedatabase
select db_name()
select @@servername
select user_name()

select name from sysobjects where type='U'  -- show list of user tables ('V' - list of views, 'S' - system tables)
sp_help some_table -- show definitions for a table
select name from sysobjects where type='P'  -- show list of stored procedures
sp_helptext some_stored_procedure --show text of a stored procedure
select * from sysusers -- shows id, group and name
sp_helplogin -- shows list of logins and related databases

on a server level there are some useful tables, for example syslogins table in the master database:

use master
go
sp_help syslogins
select name, fullname, dbname from syslogins

get a list of all user tables and stored procedures in a database:

select 'mydb', type, name from mydb..sysobjects where type='U' order by name
select 'mydb', type, name from mydb..sysobjects where type='P' order by name

Use showplan to find out why the query is slow - and force indexes when necessary:
 
showplan options - - session-level

set showplan on
set noexec on  -- only command it will execute after that is set noexec off

real session:
set showplan on
go
set noexec on
go
select ...
go
exec some_store_proc
go
set noexec off
go
set showplan off
go

forcing a select to use an index on the table.
Let's say the table has 3 indexes listed in sp_help in order 1,2,3.
If you want to use index #2, then in the 'from' part of the select statement
put the index number in the parenthesis:
      select * from mytab t1 (2) where ...

Alternatively you can refer the index by name (starting v.11.5):
      select * from mytab (index mytab_idx) where ...
 

 
Temp-tables with hashes in reality get names tagged with session:

select name from tempdb..sysobjects where type = 'U' and name like '#temp3%'
name
1 #temp3_______00000810011065957

 
Catching new line characters:
  select myname from mytable where charindex(char(10), myname) !=0

 
Conversion between binary(16) and varchar(32):
============================================
drop table #test

create table #test (cc varchar(32) null, bb binary(16) null)

-----------------------------
-- string to binary
-----------------------------
insert #test values ("873b7706de0011d59be0f05e055bbe70", null)

update #test
set bb = convert(binary(4), hextoint(substring(cc,  1, 8)))
       + convert(binary(4), hextoint(substring(cc,  9, 8)))
       + convert(binary(4), hextoint(substring(cc, 17, 8)))
       + convert(binary(4), hextoint(substring(cc, 25, 8)))
from #test
where cc="873b7706de0011d59be0f05e055bbe70"

-----------------------------
-- binary to string
-----------------------------
insert #test values (null,0x1234567890abcdef1234567890abcdef)

update #test
set cc = lower(
          inttohex(convert(binary, substring(bb,  1, 4)))
        + inttohex(convert(binary, substring(bb,  5, 4)))
        + inttohex(convert(binary, substring(bb,  9, 4)))
        + inttohex(convert(binary, substring(bb, 13, 4)))
        )
from #test
where bb=0x1234567890abcdef1234567890abcdef
============================================

select * from #test

============================================


 
bcp utility home - top of the page -

bcp - bulk copy utility

From unix prompt:
output a table in tab-separated text file:
   bcp mydb..mytable out somefile.txt -Ssomeserver -Usomeuser -Psomepassword -c
read tab-separated text file into a table:
  Note1: you may need to have an index on destination table.
  Note2: you may need to update statistics on destination table afterwards:
    bcp mydb..mytable in somefile -Ssomeserver -Usomeuser -Psomepassword -c

To change field or row terminators use -t or -r options, for example if you want to separate the columns with something like this:
  '<&+f>'
Then use the following command:
  bcp tempdb..lev_rep1  out  resultfile.txt -Ssomeserver -Usomeuser -Psomepassword -c -t '<&+f>'

Note: bcp does not fire any trigger that exists on the target table.

Note: If the table has indexes and/or triggers - then Sybase uses "slow" bcp which logs data inserts in the transaction log. These logged inserts can cause the transaction log to become very large.

Example of a script copying tables from db1 to db2:
#!/bin/sh -v
bcp dbase..tab1  out  tab1.dat -S DBSERV1 -U someuser -P somepasswd -c > /dev/null
bcp dbase..tab2  out  tab2.dat -S DBSERV1 -U someuser -P somepasswd -c > /dev/null

isql -S DBSERV2 -U someuser -P somepasswd -w250 <<EOF
use dbase
go
delete tab1
go
delete tab2
go
EOF

bcp dbase..tab1  in  tab1.dat -S DBSERV2 -U someuser -P somepasswd -c > /dev/null
bcp dbase..tab2  in  tab2.dat -S DBSERV2 -U someuser -P somepasswd -c > /dev/null

isql -S DBSERV2 -U someuser -P somepasswd -w250 <<EOF
use dbase
go
update statistics tab1
go
update statistics tab2
go
EOF

Even better example: script p2d. Note that it takes care of updating statistics
and of recompiling stored procedures related to those tables:
#!/usr/local/bin/perl
#
# p2d - simple script to copy table data 
# (from production to development)

use strict;

my $DEV  = '-S DEV  -U someuser -P somepass';
my $PROD = '-S PROD -U someuser -P somepass';
my $db = 'somedatabase';

my @tabs;
if(@ARGV){
  @tabs = @ARGV;
} else {
  print "p2d - simple script to copy table data from production to development\n";
  print "USAGE: p2d tab1 tab2 ...\n";
  exit(0);
} 

my $counter=0;

for my $tab (@tabs) {

  chomp($tab);
  $tab =~ s/(^\s+|\s+$)//g;
  next if ! $tab;
  $counter++;
  print"\nTABLE No $counter - $tab\n";

  my ($command,$out);
  $command = "bcp $db..$tab out $tab.dat $PROD -c";
  print "$command\n"; $out = qx($command);

  $command = "isql $DEV -w250 <<EOF\nuse $db\ngo\n";
  $command .= "truncate table $tab\ngo\n";
  $command .= "\nEOF\n";
  print "truncate table $tab on DEV\n"; $out = qx($command);

  $command = "bcp $db..$tab in $tab.dat $DEV -c";
  print "$command\n"; $out = qx($command);

  $command = "rm -f $tab.dat";
  print "$command\n"; $out = qx($command);

  $command = "isql $DEV -w250 <<EOF\nuse $db\ngo\n";
  $command .= "update statistics $tab\ngo\nexec sp_recompile $tab\ngo";
  $command .= "\nEOF\n";
  print "update statistics and sp_recompile $tab on DEV\n"; $out = qx($command);

}

print "FINISHED\n";

How to get tab-separated data into the database from perl script:
First, create a text file $inputFileName with tab-separated data: rows of data correspond to rows of the table
Just data - no headers, no comments
----- delete previous table
$dbh->dbcmd("truncate table $tableName");
----- bcp in new data
my $bcpcmd = "bcp oe..$tableName in $inputFileName";
$bcpcmd .= " -Sserver_name -Uuser_name -Ppasswd -c";
if ($diagMsg == 0) { $bcpcmd .= " > /dev/null"; }
$rc = system($bcpcmd);
 if ($rc != 0) {
         $rc /= 256;
         $errMsg = "bcp operation for table \'$tableName\' failed (bcp retcod = $rc).";
         return $False;
 }
----- update indexes
$dbh->dbcmd("update statistics $tableName");

 
getting large result set into excel home - top of the page - email

The idea is to select the results into a named table in a tempdb database.
Then bcp it in a text file (extension .txt)
Then open it by Excel - it will automaticall ask you if you want to treat is as delimited
Then save it as excel file

drop table tempdb..mytable
select something
into tempdb..mytable
from some_tables
where some_joins_and_restrictions

---------------------------------------------------