Freeware

The best place to search for Sybase freeware is Ed Barlow (sqltech@tiac.net)'s site (http://www.edbarlow.com).   He is likely to spend more time maintaining his list than I will spend on this.   I will do my best!

9.0      Where is all the code and why does Section 9 suddenly load in a reasonable amount of time?

Stored Procedures

9.1.1      sp_freedevice - lists device, size, used and free.
9.1.2      sp_dos - This procedure graphically displays the scope of a object
9.1.3      sp_whodo - augments sp_who by including additional columns: cpu, I/O...
9.1.4      sp__revroles - creates DDL to sp_role a mirror of your SQL Server
9.1.5      sp__rev_configure - creates DDL to sp_configure a mirror of your SQL Server
9.1.6      sp_servermap - overview of your SQL Server
9.1.7      sp__create_crosstab - simplify crosstable queries
9.1.8      sp_ddl_create_table - creates DDL for all user tables in the current database
9.1.9      sp_spaceused_table
9.1.10    SQL to determine the space used for an index.
9.1.11    sp_helpoptions - Shows what options are set for a database.
9.1.12    sp_days - returns days in current month.
9.1.13    sp__optdiag - optdiag from within isql
9.1.14    sp_desc - a simple list of a tables' columns
9.1.15    sp_lockconfig - Displays locking schemes for tables.

Shell Scripts

9.2.1      SQL and sh(1)to dynamically generate a dump/load database command.
9.2.2      update statistics script

Perl/Sybperl

9.3.1      SybPerl - Perl interface to Sybase.
9.3.2      dbschema.pl - Sybperl script to reverse engineer a database.
9.3.3      ddl_insert.pl - creates insert DDL for a table.
9.3.4      int.pl - converts interfaces file to tli
9.3.5      Sybase::Xfer.pm - Module to transfer data between two servers.
9.3.6      sybmon.pl - realtime process and lock monitor
9.3.7      showserver.pl - shows the servers on a particular machine in a nice format.
9.3.8      Collection of Perl Scripts

Sybtcl

9.4.1      Sybtcl - TCL interface to Sybase.
9.4.2      sybdump - a Tcl script for dumping a database schema to disk
9.4.3      wisql - graphical sql editor and more

Python

9.5.1      Sybase Module for Python.

Tools, Utilities and Packages

9.6.1      sqsh - a superset of dsql with local variables, redirection, pipes and all sorts of goodies.
9.6.2      lightweight Sybase Access via Win95/NT
9.6.3      BCPTool - a utility for trasferring data from ASE to another (inc. native port to Linux).

'Free' Versions of ASE

The next couple of questions will move to the OS section (real) soon.

9.7.1      How to access a SQL Server using Linux see also Q11.4.6
9.7.2      Sybase on Linux
9.7.3      How to configure shared-memory for Linux
9.7.4      Sybase now available on Free BSD

Other Sites of Interest

9.8.1      Ed Barlow's collection of Stored Procedures.

9.8.2      Examples of Open Client and Open Server programs -- see Q11.4.14.
9.8.3      xsybmon - an X interface to sp_monitor

9.0: Where is all the code and why does Section 9 suddenly load in a reasonable amount of time?

This section was in need of a spring clean, and it has now had it. I have tested all of the stored procs included here against all versions of Sybase that I have to hand. (11.0.3.3, 11.9.2 and 12.5 on Linux, 11.9.2 and 12 on Solaris and 11.9.2 and 12 on NT.) If Pablo or the supplier documented that he had tested it on other versions, then I have included those comments. Just remember that I did not test them on anything pre-11.0.3.3. If you are still using them on a pre-11.0.3.3 release (I know of at least one place that is still running 4.9.2!) then let me know and I will add a suitable comment.

I have actually taken the code away and built a set of packages. First and foremost is the stored proc package, then there is a shell script package, a perl package and finally there is the archive package, which contains any stuff specific to non-current releases of ASE.

In addition to wrenching out the code I have added some samples of the output generated by the scripts. It occurred to me that people will be better able to see if the stored proc does what they want if they can see what it produces.

Finally, part of the reason that this is here is so that people can examine the code and see how other people write stored procs etc. Each stored proc is in a file of its own so that you can choose which ones you wish to browse on-line and then cut and paste them without having to go through the hassle of un-htmling them.

Back to top

9.1.1: sp_freedevice

This script displays the size of the devices configured for a server, together with the free and used allocations.

Get it as part of the bundle (zip or tarball) or individually from here.

Output:

[30] BISCAY.master.1> sp_freedevice
[30] BISCAY.master.2>> go
 total                 used                  free
 --------------------- --------------------- ---------------------
             950.00 MB             750.00 MB             200.00 MB

(1 row affected)
 devname                        size                  used                  free
 ------------------------------ --------------------- --------------------- ---------------------
 db01                                       100.00 MB              72.00 MB              28.00 MB
 db02                                       100.00 MB               0.00 MB             100.00 MB
 log01                                      100.00 MB              51.00 MB              49.00 MB
 master                                      50.00 MB              27.00 MB              23.00 MB
 sysprocsdev                                200.00 MB             200.00 MB               0.00 MB
 tlg01                                      200.00 MB             200.00 MB               0.00 MB
 tmp01                                      200.00 MB             200.00 MB               0.00 MB

(7 rows affected, return status = 0)
[31] BISCAY.master.1>

Back to top

9.1.2: sp_dos

sp_dos displays the scope of an object within a database. What tables it references, what other procedures it calls etc. Very useful for trying to understand an application that you have just inherited.

Get it as part of the bundle (zip or tarball) or individually from here.

The output looks like this:

1> sp_dos sp_helpkey
2> go

** Utility by David Pledger, Strategic Data Systems, Inc.  **
**         PO Box 498, Springboro, OH  45066               **

         SCOPE OF EFFECT FOR OBJECT:  sp_helpkey
+------------------------------------------------------------------+
(P) sp_helpkey
|
+--(S) sysobjects
|
+--(S) syskeys
|
+--(P) sp_getmessage
   |
   +--(S) sysusermessages
   |
   +--(P) sp_validlang

(return status = 0)
1>

Back to top

9.1.3: sp_whodo

Sybase System 10.x and above

sp_whodo is an enhanced version of sp_who, with cpu and io usage for each user. Note that this proc is now a little out of date since Sybase introduced the fid column, so subordinate threads are unlikely to be grouped with their parent.

Get it as part of the bundle (zip or tarball) or individually from here.

Output:

1> sp_whodo
2> go
 spid   status       loginame     hostname   blk blk_sec program
         dbname  cmd              cpu    io      tran_name
 ------ ------------ ------------ ---------- --- ------- ----------------
        ------- ---------------- ------ ------- ----------------
      2 sleeping     NULL                    0   0
         master  NETWORK HANDLER  0      0
      4 sleeping     NULL                    0   0
         master  DEADLOCK TUNE    0      0
      5 sleeping     NULL                    0   0
         master  MIRROR HANDLER   0      0
      6 sleeping     NULL                    0   0       <astc>
         master  ASTC HANDLER     0      0
      7 sleeping     NULL                    0   0
         master  CHECKPOINT SLEEP 0      128
      8 sleeping     NULL                    0   0
         master  HOUSEKEEPER      0      33
     17 running      sa           n-utsire.m 0   0       ctisql
         master  SELECT           0      1

(7 rows affected)

Back to top

9.1.4: sp__revroles

Well, I cannot get this one to do what it is supposed to, I am not sure if it is just that it was written for a different release of Sybase and 11.9.2 and above has changed the way that roles are built, or what. Anyway, I may work on it some more.

Get it as part of the bundle (zip or tarball) or individually from here.

Back to top

9.1.5: sp__rev_configure

This proc reverse engineers the configure settings. It produces a set of calls to sp_configure for those values that appear in syscurconfigs. I am not sure how relevant this is with the ability to save and load the config file.

Get it as part of the bundle (zip or tarball) or individually from here.

The output is as follows, however, I have edited away some of the values since my list was considerably longer than this.

 -- sp_configure settings
 -------------------------------------------------------------
 sp_configure 'recovery interval', 5
go
 sp_configure 'allow updates', 0
go
 sp_configure 'user connections', 25
go
 sp_configure 'memory', 14336
go
 sp_configure 'default character set id', 2
go
 sp_configure 'stack size', 65536
go
 sp_configure 'password expiration interval', 0
go
 sp_configure 'audit queue size', 100
go
 sp_configure 'additional netmem', 0
go
 sp_configure 'default network packet size', 512
go
 sp_configure 'maximum network packet size', 512
go
 sp_configure 'extent i/o buffers',
go
 sp_configure 'identity burning set factor', 5000
go
 sp_configure 'size of auto identity', 10
go
 sp_configure 'identity grab size', 1
go
 sp_configure 'lock promotion threshold', 200
go

(41 rows affected)
(return status = 0)

Back to top

9.1.6: sp_servermap

A one stop shop for a quick peek at everything on the server.

Get it as part of the bundle (zip or tarball) or individually from here.

The output for a brand new 11.0.3.3 ASE on Linux server is as follows:

                                Current Date/Time
 ------------------------------ --------------------------
 TRAFALGAR                             Jan 14 2001  1:48PM

 Version

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

 SQL Server/11.0.3.3 ESD#6/P-FREE/Linux Intel/Linux 2.2.14 i686/1/OPT/Fri Mar 17 15:45:30 CET 2000

A - DATABASE SEGMENT MAP
************************
 db              dbid   segmap      segs device fragment start (pg)  size (MB)
 --------------- ------ ----------- ---- --------------- ----------- ---------
 master               1           7  LDS master                    4    3.00
 master               1           7  LDS master                 3588    2.00
 tempdb               2           7  LDS master                 2564    2.00
 model                3           7  LDS master                 1540    2.00
 sybsystemprocs       4           7  LDS sysprocsdev        16777216  150.00
 sybsecurity          5          15 ULDS sybsecurity        33554432  300.00

Segment Codes:
U=User-defined segment on this device fragment
L=Database Log may be placed on this device fragment
D=Database objects may be placed on this device fragment by DEFAULT
S=SYSTEM objects may be placed on this device fragment


B - DATABASE INFORMATION
************************
 db              dbid   size (MB) db status codes    created
         dump tran
 --------------- ------ --------- ------------------ ---------------
        ---------------
 master               1    5.00                      01 Jan 00 00:00
         07 Jan 01 04:01
 tempdb               2    2.00   A                  14 Jan 01 13:46
         14 Jan 01 13:47
 model                3    2.00                      01 Jan 00 00:00
         07 Jan 01 03:38
 sybsystemprocs       4  150.00    B                 07 Jan 01 03:32
         14 Jan 01 13:43
 sybsecurity          5  300.00    B                 07 Jan 01 04:01
         07 Jan 01 04:55

Status Code Key

Code       Status
----       ----------------------------------
 A         select into/bulk copy allowed
 B         truncate log on checkpoint
 C         no checkpoint on recovery
 D         db in load-from-dump mode
 E         db is suspect
 F         ddl in tran
 G         db is read-only
 H         db is for dbo use only
 I         db in single-user mode
 J         db name has been changed
 K         db is in recovery
 L         db has bypass recovery set
 M         abort tran on log full
 N         no free space accounting
 O         auto identity
 P         identity in nonunique index
 Q         db is offline
 R         db is offline until recovery completes


C - DEVICE ALLOCATION MAP
*************************
 device fragment start (pg)  size (MB) db              lstart      segs
 --------------- ----------- --------- --------------- ----------- ----
 master                    4    3.00   master                    0  LDS
 master                 1540    2.00   model                     0  LDS
 master                 2564    2.00   tempdb                    0  LDS
 master                 3588    2.00   master                 1536  LDS
 sybsecurity        33554432  300.00   sybsecurity               0 ULDS
 sysprocsdev        16777216  150.00   sybsystemprocs            0  LDS

Segment Codes:
U=USER-definedsegment on this device fragment
L=Database LOG may be placed on this device fragment
D=Database objects may be placed on this device fragment by DEFAULT
S=SYSTEM objects may be placed on this device fragment


D - DEVICE NUMBER, DEFAULT & SPACE USAGE
****************************************
 device          vdevno default disk? total (MB) used    free
 --------------- ------ ------------- ---------- ------- -------
 master               0     Y          100.00       9.00   91.00
 sysprocsdev          1     N          150.00     150.00    0.00
 sybsecurity          2     N          300.00     300.00    0.00

E - DEVICE LOCATION
*******************
 device          location
 --------------- ------------------------------------------------------------
 master          d_master
 sybsecurity     /d/TRAFALGAR/3/sybsecur.dat
 sysprocsdev     /d/TRAFALGAR/2/sybprocs.dat

NO DEVICES ARE MIRRORED
(return status = 0)

Back to top

9.1.7: sp__create_crosstab

Hmmm... not quite sure about this one. Was not 100% sure about how to set it up. From the description it builds a cross tab query. If someone knows how to use this, then let me know how to set it up and I will improve the description here and provide some output.

Get it as part of the bundle (zip or tarball) or individually from here.

Back to top

9.1.8: sp_ddl_create_table

Well, you all know what a create table statement looks like... This produces the table definitions in their barest form (lacking in constraints etc) and the resulting DDL is perhaps not as elegant as some other utilities, but far be it from me to blow dbschema's trumpet :-), but it is worth a look just for the query. The layout of the carriage returns being embedded within strings is deliberate!

Get it as part of the bundle (zip or tarball) or individually from here.

Back to top

9.1.9: sp_spaceused_table

Brief

In environment where there are a lot of temporary tables #x being created, how do you tell who is using how much space ? The answer is sp_spaceused_table, which basically lists the tables in a database with rowcount and space usage statistics. I have replaced the original proc with K-shell script for a single proc. I think that it is easier to compare if it is all in one listing. However, if you disagree I will add the original code to the archive package, just let me know.

Get it as part of the bundle (zip or tarball) or individually from here.

The output of the proc is as follows: (I used sqsh, hence the prompt, since it auto-resizes its width as you resize the xterm.)

[25] N_UTSIRE.tempdb.1> sp_spaceused_table
[25] N_UTSIRE.tempdb.2> go
 name                                          rowtotal    reserved        data            index_size      unused
 --------------------------------------------- ----------- --------------- --------------- --------------- ---------------
 #matter______00000010014294376                12039       3920 KB         3910 KB         0 KB            10 KB
 #synopsis____00000010014294376                6572        15766 KB        274 KB          15472 KB        20 KB
 #hearing_____00000010014294376                5856        572 KB          568 KB          0 KB            4 KB
 #hearing2____00000010014294376                5856        574 KB          568 KB          0 KB            6 KB
 #hearing3____00000010014294376                5856        574 KB          568 KB          0 KB            6 KB
 #synopsis2___00000010014294376                6572        15820 KB        274 KB          15472 KB        74 KB

(return status = 0)

Back to top

9.1.10: SQL to determine space used for an index

This one is not strictly a stored proc, but it has its uses.

Fundamentally, it is sp_spaceused reduced to bare essentials:

  set nocount on
  declare @objname varchar(30)
  select  @objname = "your table"

  select  index_name = i.name,
          i.segment,
          rowtotal   = rowcnt(i.doampg),
          reserved   = reserved_pgs(i.id, i.doampg) +
                       reserved_pgs(i.id, i.ioampg),
          data       = data_pgs(i.id, i.doampg),
          index_size = data_pgs(i.id, i.ioampg),
          unused     = (reserved_pgs(i.id, i.doampg) +
                        reserved_pgs(i.id, i.ioampg) -
                          (data_pgs(i.id, i.doampg) +
                           data_pgs(i.id, i.ioampg)))
  into    #space
  from    sysindexes i
  where   i.id = object_id(@objname)

You can analyse this in a number of ways:

1. This query should tally with sp_spaceused @objname:

     select 'reserved KB' = sum(reserved)   * 2,
            'Data KB'     = sum(data)       * 2,
            'Index KB'    = sum(index_size) * 2,
            'Unused KB'   = sum(unused)     * 2
       from #space

2. This one reports space allocation by segment:

     select 'segment name' = s.name,
            'reserved KB'  = sum(reserved)   * 2,
            'Data KB'      = sum(data)       * 2,
            'Index KB'     = sum(index_size) * 2,
            'Unused KB'    = sum(unused)     * 2
       from #space t,
            syssegments s
      where t.segment = s.segment
      group by s.name

3. This one reports allocations by index:

     select  t.index_name,
                 s.name,
                 'reserved KB' = reserved * 2,
                 'Data KB' = data * 2,
                 'Index KB' = index_size * 2,
                 'Unused KB' = unused * 2
         from    #space t,
                 syssegments s
         where   t.segment = s.segment

If you leave out the where clause in the initial select into, you can analyse across the whole database.

Hope this points you in the right direction.

Back to top

9.1.11: sp_helpoptions - Shows what options are set for a database.

Thanks again go to Bret Halford for some more sterling work. The following proc will let you know some of options that are set within a database. The release included is here has been tested to work on Solaris (11.9.2 and 12.0), but it is likely that other platforms use and set @@options differently (endian issues etc). As such, it is more of a sort of template for platforms other than Solaris. Please feel free to expand it and send the modified proc back to me and Bret.

Get it as part of the bundle (zip or tarball) or individually from here.

The output is as follows:

1> sp_helpoptions
2> go
showplan is off
ansinull is off
ansi_permissions is off
arithabort is on
arithignore is off
arithignore arith_overflow off
close on endtran is off
nocount is on
noexec is off
parseonly is off.
(return status = 0)

Back to top

9.1.12: sp_days - returns days in a given month.

Returns the number of days in a month. Modify to fit your needs, either returning a result set (of 1 row) or set a variable, or both as this version does.

Get it as part of the bundle (zip or tarball) or individually from here.

The output is as follows:

1> declare @days int
2> -- For November 1999
3> exec sp_days @days,11,99
4> go

 ---
  30

(1 row affected)
(return status = 0)

Back to top

9.1.13: sp__optdiag - optdiag from within isql.

Versions of ASE: minimum of 11.5. I cannot test it on 11.5, so I do not know if it works on that version. However, the procedure uses a 'case' statement, so will certainly not work before 11.5. If anyone still has 11.5 running and can let me know that it works, I would be grateful.

It seems little point in showing you what optdiag looks like, since it takes a fair amount of space. This proc produces pretty much identical output.

Get it as part of the bundle (zip or tarball) or individually from here.

Back to top

9.1.14: sp_desc - a simple list of a tables' columns

Stored proc to return a much simpler picture of a table that sp_help.  sp_help produces all of the information, too much in fact, and it always takes me a couple of minutes to work out the various flags etc.  I think that this is a little easier to read and understand quickly.

1> sp_desc spt_values
2> go
spt_values
No.   Column Name                    Datatype
----- ------------------------------ -------------------- --------
(1)   name                           varchar(28)
(2)   number                         int                  NOT NULL
(3)   type                           char(2)              NOT NULL
(4)   low                            int
(5)   high                           int
(6)   msgnum                         int

(6 rows affected)
(return status = 0)
1>

Get it as part of the bundle (zip or tarball) or individually from here.

Back to top

9.1.15: sp_lockconfig - displays locking schemes for tables

sp_lockconfig [sys_flag]

1. All table-specific lock configurations for the current database. 
2. The database-wide lock configurations, if they exist. 
3. The server-wide lock configurations. 

A list of all tables will then be listed by locking scheme. For data-only tables a suffix of "*" indicates that the table was originally created with a clustered allpages configuration and was then altered to a data-only configuration. (The reverse cannot be detected.) If sys_flag is non-null then system tables will be included. Note that many system tables do not have a defined locking scheme. (The implicit usage is allpages.)

1> sp_lockconfig
2> go

 TYPE     OBJECT                       LEVEL LOCK DATA
 -------- ---------------------------- ----- ---------------------------------
 Server   -                            page  PCT = 100, LWM = 200, HWM = 200
 Server   -                            row   PCT = 100, LWM = 200, HWM = 200
 Server   default lock scheme          -     allpages

    THERE ARE 4 USER TABLES WITH ALLPAGES LOCKING.

 TABLE                          OWNER
 ------------------------------ ------------------------------
 appkey8                        dbo
 appkey8_hist                   dbo
 text_table7                    TESTUSER2
 with_types_table12             TESTUSER3

    THERE ARE 2 USER TABLES WITH DATAPAGES LOCKING.

 TABLE                          OWNER
 ------------------------------ ------------------------------
 dol_test1                      dbo
 dol_test2                      dbo

    THERE ARE 2 USER TABLES WITH DATAROWS LOCKING.

 TABLE                          OWNER
 ------------------------------ ------------------------------
 dol_test10                     dbo
 dol_test11                     dbo

(return status = 0)
1>

Get it as part of the bundle (zip or tarball) or individually from here.

Back to top

9.2.1: Generating dump/load database command.

This shell script generates dump/load database commands from dump devices. I cannot show the output because it seems to be broken, it is certainly a little convoluted and is really pertinent to the pre-11 days, possibly even pre-10. It is available as part of the archive code package.

What is really needed here is some automatic backup scripts. How is it going Barb?

Get it as part of the bundle (zip or tarball) or individually from here.

Back to top

9.2.2: upd_stats.csh

This is a script from Frank Lundy (mailto:flundy@verio.net) and does not generate output, but does the updates directly. As such there is no output to show you. It requires a program called sqlsa which you will need to modify to suit your own server.  You probably want to make the file unreadable by regular users who have no need for any passwords contained within.

Get it as part of the bundle (zip or tarball) or individually from here.

Back to top

9.3.1: SybPerl FAQ

Sybperl is a fantastic utility for DBAs and system administrators needing to put together scripts to monitor and manage their installations as well as the main way that web developers can gain access to data held in ASEs.

Sybperl now comes in a number of flavours, including a DBD version that is part of the DBI/DBD suite. Michael has also written a package called Sybase::Simple that sits on top of Sybperl that makes building such scripts a breeze.

Find out more and grab a copy from Michael Peppler's mpeppler@peppler.org own FAQ:

http://www.mbay.net/~mpeppler/Sybperl/sybperl-faq.html

Back to top

9.3.2: dbschema.pl

dbschema.pl is a script that will extract the schema (everything from the server definition down to table permissions etc) from ASE/SQL Server.  It was initially developed by Michael Peppler but currently maintained by me (David Owen dowen@midsomer.org)  The script is written using Sybperl and was originally distributed solely as part of that package.  The latest copy can be got from ftp://ftp.midsomer.org/pub/dbschema.tgz.

Back to top

9.3.3: ddl_insert.pl

In order to use this script you must have Sybperl installed -- see Q9.3.1 for more information.

This utility produces the insert statements to rebuild a table. Note that it depends on the environment variable DSQUERY for the server selection. Also be warned that the generated script truncates the destination table, which might not be what you want. Other than that, it looks like an excellent addition to the testing toolkit.

Get it as part of the bundle (zip or tarball) or individually from here.

[dowen@n-utsire code]$ ./ddl_insert.pl alrbprod sa myPassword h%
-- This script is created by ./ddl_insert.pl.
-- It would generate INSERT statements for tables whose names match the
-- following pattern:
/*      (  1 = 0
     or name like 'h%'
        )

*/

set nocount on
go


/*.............. hearing ...............*/
-- Sat Feb 17 13:24:09 MST 2001

declare @d datetime
select @d = getdate()
print        '       %1!    hearing', @d
go

truncate table hearing  -- Lookout !!!!!!
go

insert hearing values('Dec 11 1985 12:00:00:000AM', 1, '1030', 2, '0930', 'Calgary, Alberta', NULL, NULL, '3408 Board Room', 3, NULL, '35', NULL)
...

Back to top

9.3.4: int.pl

Background

Please find included a copy of int.pl, the interfaces file conversion tool. It should work with perl 4 and 5, but some perl distributions don't seem to support gethostbyname which you need for the solaris, ncr, and vms file format.

You may need to adjust the first line to the path of perl on your system, and may need to set the PERLLIB environment variable so that it finds the getopts.pl module.

While it may not be 100% complete (e.g. it ignores the timeout field) you're free to add any functionality you may need at your site.

int.pl -h will print the usage, typical invocation is
	int.pl -f sun4-interfaces -o sol > interfaces.sol

Usage:  int.pl  -f 
                -o { sol|ncr|vms|nw386|os2|nt386|win3|dos|ntdoswin3 }
                [-V] [-v] [-h]
where
        -f  input file to process
        -o  specify output mode
                  (e.g. sol, ncr, vms, nw386, os2, nt386, win3, dos, ntdoswin3)
        -V        turn on verbose mode
        -v        print version string
        -h        print this message

[The following are a couple of output examples, is any other utility ever needed? Ed]

Get it as part of the bundle (zip or tarball) or individually from here.

The following interface file:

N_UTSIRE
        master tcp ether n-utsire 4100
        query tcp ether n-utsire 4100

N_UTSIRE_XP
        master tcp ether n-utsire 4400
        query tcp ether n-utsire 4400

N_UTSIRE_BS
        master tcp ether n-utsire 4010
        query tcp ether n-utsire 4010

becomes

[dowen@n-utsire code]$ ./int.pl -f $SYBASE/interfaces -o vms
N_UTSIRE
        master tcp ether 192.168.1.1 4100
        query tcp ether 192.168.1.1 4100
N_UTSIRE_XP
        master tcp ether 192.168.1.1 4400
        query tcp ether 192.168.1.1 4400
N_UTSIRE_BS
        master tcp ether 192.168.1.1 4010
        query tcp ether 192.168.1.1 4010
[dowen@n-utsire code]$
[dowen@n-utsire code]$ ./int.pl -f $SYBASE/interfaces -o sol
N_UTSIRE
        master tli tcp /dev/tcp \x00021004c0a801010000000000000000
        query tli tcp /dev/tcp \x00021004c0a801010000000000000000
N_UTSIRE_XP
        master tli tcp /dev/tcp \x00021130c0a801010000000000000000
        query tli tcp /dev/tcp \x00021130c0a801010000000000000000
N_UTSIRE_BS
        master tli tcp /dev/tcp \x00020faac0a801010000000000000000
        query tli tcp /dev/tcp \x00020faac0a801010000000000000000
[dowen@n-utsire code]$
[dowen@n-utsire code]$ ./int.pl -f $SYBASE/interfaces -o ncr
N_UTSIRE
        master tli tcp /dev/tcp \x00021004c0a80101
        query tli tcp /dev/tcp \x00021004c0a80101
N_UTSIRE_XP
        master tli tcp /dev/tcp \x00021130c0a80101
        query tli tcp /dev/tcp \x00021130c0a80101
N_UTSIRE_BS
        master tli tcp /dev/tcp \x00020faac0a80101
        query tli tcp /dev/tcp \x00020faac0a80101
[dowen@n-utsire code]$

Back to top

9.3.5: Sybase::Xfer.pm

The following is taken directly from the authors own documentation.

QUICK DESCRIPTION
 Sybase::Xfer transfers data between two Sybase  servers  with  multiple
 options like specifying a where_clause, a smart auto_delete option  and
 can pump data from a perl subroutine or take a  plain  flat  file.  Has
 option, similiar to default behaviour in Sybase::BCP, to capture failed
 rows in a batch.

 Also comes with a command line wrapper, sybxfer.

 Also comes with a sister module Sybase::ObjectInfo.pm


DEPENDENCIES
   Requires Perl Version 5.005 or beyond

   Requires packages:
      Sybase::DBlib
      Getopt::Long
      Tie::IxHash


SYNOPSIS
   #from perl
      #!/usr/bin/perl5.005
      use Sybase::Xfer;
      $h = new Sybase::Xfer( %options );
      $h->xfer();
      $h->done();

   #from shell
      #!/usr/ksh
      sybxfer <options>


DESCRIPTION (a little bit from the pod)

  If you're in an environment with multiple servers and you  don't  want
  to use cross-server joins then this module may be worth a  gander.  It
  transfers data from one server to another server row-by-row in memory
  w/o using an intermediate file.

  To juice things up it can take data from any set of  sql  commands  as
  long as the output of the sql matches the  definition  of  the  target
  table. And it can take data from a  perl  subroutine  if  you're  into
  that.

  It also has some smarts to delete rows in the target table before  the
  data is  transferred  by  several  methods.  See  the  -truncate_flag,
  -delete_flag and -auto_delete switches.

  Everything is controlled by switch settings sent has  a  hash  to  the
  module. In essence one describes the from source and the to source and
  the module takes it from there.

  Error handling:

    An attempt was made to build in hooks for robust error reporting via
    perl callbacks. By default, it will print to stderr  the  data,  the
    column names, and their datatypes upon  error.  This  is  especially
    useful when sybase reports attempt to load an oversized row  warning
    message.


  Auto delete:

    More recently the code has been  tweaked  to  handle  the  condition
    where data is bcp'ed into a table but the row already exists and the
    desired result to replace  the  row.  Originally,  the  -delete_flag
    option was meant for this condition. ie. clean out the table via the
    -where_clause before the bcp in was to occur. If this is  action  is
    too drastic, however, by using the -auto_delete option  one  can  be
    more precise and force only those rows about to be  inserted  to  be
    deleted before the bcp in begins. It will bcp the 'key'  information
    to a temp table, run a delete (in a loop so as not to blow  any  log
    space) via a join between the temp table and target table  and  then
    begin the bcp in. It's weird but in the right situation  it  may  be
    exactly what you want. Typically used to manually replicate a table.


CONTACTS
   my e-mail: stephen.sprague@msdw.com

Back to top

9.3.6: Sybmon.pl

Sybmon is a utility for interactive, realtime, monitoring of processes and locks. It is a sort of "top" for Sybase. It requires both Sybperl and Perl/Tk to be installed, both are available for most platforms, including Linux, NT, Solaris.

Grab the tarball from ftp://ftp.midsomer.org/pub/sybmon.tar.gz or a zip'd one from ftp://ftp.midsomer.org/pub/sybmon.zip.

There is also an NT binary for those people that are unable or just don't want to install Perl.  You can get that from ftp://ftp.midsomer.org/pub/sybmon-i386.zip.

You can view a screenshot of the main process monitor from here (just to prove that it runs on NT fine!!!!). A not very exciting server, doing not a lot!

A note of interest! To get the screenshot I used the latest copy of Activestate Perl for NT and their Perl Package Manager (just type PPM from a DOS prompt once Perl is installed) and had the 3 required packages (Tk, Sybperl, Sybase::Login) installed in under 2 minutes!!!!

Back to top

9.3.7: showserver.pl

This small Perl script shows a list of what servers are running on the current machine.  Does a similar job to the showserver that comes with ASE, but looks much nicer.

Get it as part of the bundle (zip or tarball) or individually from here.

bash-2.03$ ./showserver.pl

monserver's
-----------
  CONCRETE Owner: sybase, Started: 14:25:51
      Engine:  0 (PID: 520)
  PORTLAND Owner: sybase, Started: 14:29:33
      Engine:  0 (PID: 545)

dataserver's
------------
  CONCRETE Owner: sybase, Started: 14:10:38
      Engine:  1 (PID: 494)
      Engine:  0 (PID: 493)
  PORTLAND Owner: sybase, Started: 14:26:56
      Engine:  0 (PID: 529)

backupserver's
--------------
  CONCRETE_back Owner: sybase, Started: 14:25:25
      Engine:  0 (PID: 515)
  PORTLAND_back Owner: sybase, Started: 14:29:07
      Engine:  0 (PID: 538)

Back to top

9.3.8: Collection of Perl Scripts

David Whitmarsh has put together a collection of scripts to help manage and monitor ASEs. They can be grabbed individually or en masse from http://sparkle-consultancy.co.uk/sybase/ .

Back to top

9.4.1: Sybtcl FAQ

This is Tom Poindexter http://www.nyx.net/~tpoindex/ FAQ.

Index of Sections

• Overview
• The enabling language platform
• Design and commands
• Applications
• Information Sources
• Download
• About the Author

Sybtcl is an extension to Tcl (Tool Command Language) that allows Tcl programs to access Sybase databases. Sybtcl adds additional Tcl commands to login to a Sybase server, send SQL statements, retrieve result sets, execute stored procedures, etc. Sybtcl simplifies Sybase programming by creating a high level interface on top of DB-Library. Sybtcl can be used to program a wide variety of applications, from system administration procedures to end-user applications.

Sybtcl runs on Unix, Windows NT and 95, and Macintosh platforms.

Tool Command Language, often abbreviated "Tcl" and pronounced as "tickle", was created by Dr. John Ousterhout at the University of California-Berkeley. Tcl is an interpreted script language, similar to Unix shell, Awk, Perl, and others. Tcl was designed to be easily extended, where new commands are added to the base interpreter to provide additional functionality. Core Tcl commands contain all of the usual constructs provided by most programming languages: setting and accessing variables, file read/write, if-then-else, do-while, function calls. Tcl also contains many productivity enhancing commands: list manipulation, associative arrays, and regular expression processing.

Tcl has several features that make it a highly productive language. First, the language is interpreted. Interpreters allow execution without a compile and link step. Code can be developed with immediate feedback. Second, Tcl has a single data type: string. While this might at first glance seem to a deficiency, it avoids problems of data conversion and memory management. (This feature doesn't preclude Tcl from performing arithmetic operations.) Last, Tcl has a consistent and simple syntax, much the same as the Unix shell. Every Tcl statement is a command name, followed by arguments.

Dr. Ousterhout also developed a companion Tcl extension, called Tk. Tk provides simplified programming of X11 applications with a Motif look and feel. X11 applications can be programmed with 60%-80% less code than equivalent Xt, Motif, or Xview programs using C or C++.

Dr. Ousterhout now leads Tcl/Tk development at Sun Microsystems.

Sybtcl was designed to fill the gap between pure applications development tools (e.g. Apt, Powerbuilder, et.al.) and database administration tools, often Unix shell scripts consisting of 'isql' and Awk pipelines. Sybtcl extends the Tcl language with specialized commands for Sybase access. Sybtcl consists of a set of C language functions that interface DB-Library calls to the Tcl language.

Instead of a simple one-to-one interface to DB-Library, Sybtcl provides a high-level Sybase programming interface of its own. The following example is a complete Sybtcl program that illustrates the simplified interface. It relies on the Tcl interpreter, "tclsh", that has been extended with Sybtcl.

  #!/usr/local/bin/tclsh
  set hand [sybconnect "mysybid" "mysybpasswd"]
  sybuse $hand pubs2
  sybsql $hand "select au_lname, au_fname from authors order by au_lname"
  sybnext $hand {
    puts [format "%s, %s" @1 @2]
  }
  sybclose $hand
  exit

In this example, a Sybase server connection is established ("sybconnect"), and the "pubs" sample database is accessed ("sybuse"). An SQL statement is sent to the server ("sybsql"), and all rows returned are fetched and printed ("sybnext"). Finally, the connection is closed ("sybclose").

The same program can be made to display its output in an X11 window, with a few changes. The Tcl/Tk windowing shell, "wish", also extended with Sybtcl is used.

  #!/usr/local/bin/wish
  listbox .sql_output
  button  .exit -text exit -command exit
  pack .sql_output .exit
  set hand [sybconnect "mysybid" "mysybpasswd"]
  sybuse $hand pubs2
  sybsql $hand "select au_lname, au_fname from authors order by au_lname"
  sybnext $hand {
    .sql_output insert end  [format "%s, %s" @1 @2]
  }
  sybclose $hand

In addition to these commands, Sybtcl includes commands to access return column names and datatypes ("sybcols"), return values from stored procedures ("sybretval"), reading and writing of "text" or "image" columns ("sybreadtext", "sybwritetext"), canceling pending results ("sybcancel"), and polling asynchronous SQL execution ("sybpoll").

Full access to Sybase server messages is also provided. Sybtcl maintains a Tcl array variable which contains server messages, output from stored procedures ("print"), DB-Library and OS error message.

The Sybtcl distribution includes "Wisqlite", an X11 SQL command processor. Wisqlite provides a typical windowing style environment to enter and edit SQL statements, list results of the SQL execution in a scrollable listbox, save or print output. In addition, menu access to the Sybase data dictionary is provided, listing tables in a database, the column names and datatypes of a table, text of stored procedures and triggers.

For a snapshot of Wisqlite in action, look here.

Other applications included in the Sybtcl distribution include:

• a simple graphical performance monitor
• a version of "sp_who", with periodic refresh

Sybtcl users have reported a wide variety of applications written in Sybtcl, ranging from end user applications to database administration utilities.

Sybtcl is extensively documented in "Tcl/Tk Tools", edited by Mark Harrison, published by O'Reilly and Associates, 1997, ISBN: 1-56592-218-2.

Tcl/Tk is described in detail in "Tcl and the Tk Toolkit" by Dr. John Ousterhout, Addison-Wesley Publishing 1994 ISBN: 0-201-63337-X . Another recent publication is "Practical Programming in Tcl and Tk" by Brent Welch, Prentice Hall 1995 ISBN 0-13-182007-9.

A wealth of information on Tcl/Tk is available via Internet sources:

news:comp.lang.tcl
http://www.neosoft.com/tcl/
http://www.sco.com/Technology/tcl/Tcl.html
ftp://ftp.neosoft.com/pub/tcl/

Download Sybtcl in tar.gz format for Unix.
Download Sybtcl in zip format for Windows NT and 95.

Tcl/Tk and Sybtcl are both released in source code form under a "BSD" style license. Tcl/Tk and Sybtcl may be freely used for any purpose, as long as copyright credit is given to the respective owners. Tcl/Tk can be obtained from either anonymous FTP site listed above.

Tcl/Tk and Sybtcl can be easily configured under most modern Unix systems including SunOS, Solaris, HP-UX, Irix, OSF/1, AIX, SCO, et.al. Sybtcl also runs under Windows NT and 95; pre-compiled DLL's are include in the distribution. Sybtcl requires Sybase's DB-Library, from Sybase's Open Client bundle.

Current versions are:

• Sybtcl 2.5: released January 8, 1998
• Tcl 8.0: released August 13, 1997
• Tk 8.0: released August 13, 1997

The Internet newsgroup comp.lang.tcl is the focal point for support. The group is regularly read by developers and users alike. Authors may also be reached via email. Sun has committed to keeping Tcl/Tk as freely available software.

Tom Poindexter is a consultant with expertise in Unix, relational databases, systems and application programming. He holds a B.S. degree from the University of Missouri, and an M.B.A. degree from Illinois State University. He can be reached at tpoindex@nyx.net.

Back to top

9.4.2: sybdump

Sybdump is a Tcl script written by De Clarke (de@ucolick.org) for extracting a database schema.  Look in

ftp://ftp.ucolick.org/pub/src/UCODB

for sybdump.tar or sybdump.tar.gz.

Back to top

9.4.3: wisql

Another Sybtcl package maintained by De Clarke (de@ucolick.org) this one is a graphical replacement for isql. Correct me if I am wrong, but I think that this started life as wisqlite and was included as part of the Sybtcl package and was then updated by De and became wisql.

You can grab a copy of wisql from ftp://ftp.ucolick.org:/pub/UCODB/wisql5B.tar.gz

Back to top

9.5.1: Sybase Module for Python

Dave Cole has a module for Python that allows connectivity to Sybase in an analagous way to Sybperl or Sybtcl. You can find details from http://www.object-craft.com.au/projects/sybase/.

Back to top

9.6.1: SQSH, SQshelL

SQSH is a direct replacement for isql with a million more bells and whistles. In fact, the title gives it away, since SQSH is a pretty seemless marriage of

sh(1)

There has been a webified copy of the SQSH FAQ based on the 1.4 release contained within these pages for a while, but it is considerably behind the times. As such, I have moved the 1.4 release to a separate file readable from here.

The current SQSH FAQ can be seen on Scott's own site, http://www.voicenet.com/~gray/FAQ.html.

Back to top

9.6.2: NTQuery.exe

Brief

ntquery.exe is a 32-bit application allowing a lightweight, but robust sybase access environment for win95/NT. It has a split window - the top for queries, the bottom for results and error/message handler responses, which are processed in-line. Think of it as isql for windows - a better (reliable) version of wisql (with sensible error handling). Because its simple it can be used against rep-server (I've also used it against Navigation Server(R.I.P.))

Requirements: open client/dblib (Tested with 10.x up to 11.1.1)

It picks up the server list from %SYBASE%\ini\sql.ini and you can add DSQUERY,SYBUSER and SYBPASS variables in your user variables to set default server,username and password values.

Instructions

To connect: SQL->CONNECT (only one connection at a time, but you can run multiple ntquery copies) Enter query in top window and hit F3 (or SQL->Execute Query if you must use the mouse) Results/Messages/Errors appear in bottom window

A script can be loaded into the top window via File->Open Either sql or results can be saved with File->Save - it depends which window your focus is on.

Theres a buffer limit of 2mb

Get it here

ntquery.zip [22K]

Back to top

9.6.3: BCPTool - A utility for Transferring Data from one ASE to Another.

BCPTool is a GUI utility written by Anthony Mandic that moves data from one ASE to another. It runs on Solaris and Linux and is very straightforward to use.

Go to http://www.mbay.net/~mpeppler/bcptool to grab a copy, read the documentation and see a couple of screen shots.

Hot news! Michael Peppler is porting BCPtool to use the GTK+ libraries, which is basically the standard gnome toolkit for Linux. Go to Michael's site for more details (http://www.mbay.net/~mpeppler).

Back to top

9.7.1: How to access a SQL Server using Linux

I am planning to remove/reduce/rewrite this section when the ASE on Linux FAQ moves to section 2. Most of it is out of date, and I think that most of its links are broken.

Some time back, Sybase released a binary distribution of ctlib for Linux. This is just the header and libraries files for ctlib only, not dblib, not isql, not bcp, not the dataserver and not the OpenServer. This was done as a skunk works internal project at Sybase, for the good of the Linux community, and not supported by Sybase in any official capacity. This version of ctlib identifies itself as 10.0.3.

At the time, the binary format for Linux libraries was a format called a.out. Since then, the format has changed to the newer, ELF format. ELF libraries and .o files cannot be linked with a.out libraries and .o files. Fortunately, a.out libraries and .o files can easily be converted to ELF via the objdump(1) program.

Getting a useable ctlib for Linux isn't that easy, though. Another compatibility problem has arisen since these old libraries were compiled. The byte-order for the ctype macros has changed. One can link to the (converted-to-ELF) ctlib, but running the resulting executable will result in an error message having to do with missing localization files. The problem is that the ctype macros in the compiled ctlib libraries are accessing a structure in the shared C library which has changed its byte order.

I've converted the a.out library, as distributed by Sybase to ELF, and added the old tables directly to the library, so that it won't find the wrong ones in libc.

Using this library, I can link and run programs on my Linux machines against Sybase databases (It also can run some programs against Microsoft SQL server, but that's another FAQ). However, you must be running Linux 2.0 or later, or else the link phase will core dump.

This library is available for ftp at:

• ftp://mudshark.sunquest.com/pub/ctlib-linux-elf/sybperl.tar.gz
• ftp://mudshark.sunquest.com/pub/ctlib-linux-elf/ctlib-linux-elf.tgz

is a compiled version of sybperl 2.0, which is built with the above library. Obviously, only the ctlib module is in this distribution.

In order to use this code, you will need a Sybase dataserver, a Sybase interfaces file (in the non-TLI format -- see Q9.3.4), a user named sybase in your /etc/passwd file, whose home directory is the root of the distribution, and some application code to link to.

As far as an isql replacement goes, use sqsh - Q9.5.1.

One of the libraries in the usual Sybase distribution is a libtcl.a This conflicts with the library on Linux which implements the TCL scripting language, so this distribution names that library libsybtcl.a, which might cause some porting confusion.

The above conflict problem is addressed by SybPerl - Q9.3.1 and sqsh - Q9.5.1

More information

See Q11.4.6 for more information on setting up DBI/DBD:Sybase

Back to top

9.7.2: Sybase on Linux FAQ

I am planning to move this section out of here next release.

Sybase have released two versions of Sybase on Linux, 11.0.3.3 and 11.9.2, and a third, 12.5, is in beta testing at this moment, slated for GA sometime in the first half of 2001.

11.9.2

This is officially supported and sanctioned.  The supported version can be purchased from Sybase at similar, if not exactly the same, conditions as 11.9.2 on NT, with one small exception: you can download a developer's version for free!  There is a 11.9.2.2 EBF, although I am not 100% sure if the current developer's release is 11.9.2 or 11.9.2.2. Certainly for a while, you could only get the EBF if you had a paid for version.

11.0.3.3

Please remember that Sybase Inc does not provide any official support for SQL Server on Linux (ie the 11.0.3.3 release). The folks on the 'net provide the support.

Index

• Minimum Requirements
• How to report a bug
• Bug list

Minimum Requirements

• Linux release: 2.0.36 or 2.1.122 or greater.

How to report a bug

I hope you understand that the Sybase employee who did the port is a very busy person so it's best not to send him mail regarding trivial issues. If you have tried posting to comp.databases.sybase and ase-linux-list@isug.com and have checked the bugs list, send him an e-mail note with the following data - you will not get an acknowledgement to your e-mail and it will go directly into the bug tracking database; true bugs will be fixed in the next release; any message without the above Subject will be deleted, unseen, by a filter.

Administrator: I know that the above sounds harsh but Wim ten has been launched to world-wide exposure. In order for him to continue to provide Sybase ASE outside of his normal workload we all have to support him. Thanks!

With the above out of the way, if you find a bug or an issue please report it as follows:

To: wtenhave@sybase.com
Subject: SYBASE ASE LINUX PR
uname: the result of typing 'uname -a' in a shell
$SYBASE/scripts/hw_info.sh: As 'sybase' run this shell script and enclose its output
short description: a one to two line description of the problem
repeatable: yes, you can repeat it, no you cannot
version of dataserver: the result of: as the 'sybase' user, 'cd $SYBASE/bin' and type './dataserver -v|head -1'
test case: test case to reproduce the problem

Back to top

9.7.3: Linux Shared Memory for ASE (x86 Processors)

2.2.x Series Kernels and Above

To set the maximum shared memory to 128M use the following:

# echo 134217728 > /proc/sys/kernel/shmmax

This comes from the following calculation: 128Mb = 128 x 1024 x 1024 bytes = 134217728 bytes

2.0.x and 2.1.x Kernels

To increase the total memory for ASE (SQL Server) beyond 32mb, several kernel parameters must be changed.

1. Determine Memory/System Requirements
   • a: Total Memory < 128mb specific instructions
   • b: Total Memory > 128MB - specific instructions
2. Modify the linux/include/asm/shmparam.h to setup shared memory
3. Increase the size of the swap
4. Recompile your kernel & start using the new kernel
5. Verify the changes have taken effect
6. Increase the total memory to the desired size

Comments

1a - Total Memory < 128mb specific instructions

Requirements:

Linux 2.0.36 or higher

Total memory is currently limited to 128mb. A request to the Linux kernel developers has been made to enable large swap support which will allow the same size as 2.2.x kernels.

1b - Total Memory > 128mb - specific instructions

Requirements:

• Linux Kernel 2.2.x or higher *
• util-linux package 2.9 or higher *
• Swap space atleast as large as the SQL Server

* - both are available from ftp://ftp.us.kernel.org

You need to make the following changes in linux/include/asm-i386/page.h:

- #define __PAGE_OFFSET (0xC0000000)
+ #define __PAGE_OFFSET (0x80000000)

This allows accessing up to 2gb of memory. Default is 960mb.

Step 2: Modify the linux/include/asm/shmparam.h to setup shared memory

[max seg size]
- #define SHMMAX 0x2000000 /* defaults to 32 MByte */
+ #define SHMMAX 0x7FFFE000 /* 2048mb - 8k */

[max number of segments]
- #define _SHM_ID_BITS 7 /* maximum of 128 segments */
+ #define _SHM_ID_BITS 5 /* maximum of 32 segments */

[number of bits to count how many pages in the shm segment]
- #define _SHM_IDX_BITS 15 /* maximum 32768 pages/segment */
+ #define _SHM_IDX_BITS 19 /* maximum 524288 pages/segment */

Alter _SHM_IDX_BITS only if you like to go beyond the default 128MByte where you also need the swap space available.

_SHM_ID_BITS + _SHM_IDX_BITS must be equal to or less then 24.

Linux kernel PAGE size for Intel x86 machines = 4k

Step 3: To increase the size of swap

$ mkswap -c <device> [size] <- use for pre 2.2 kernels
- limited to 128mb - 8k

$ mkswap -c -v1 <device> [size] <- limited to 2gb 8k

$ swapon <device>


• Add the following to your /etc/fstab to enable this swap on boot
  

  <device> swap swap defaults 0 0

Step 4: Recompile your kernel & restart using the new kernel

Follow the instructions provided with the Linux Kernel

Step 5: Verify the changes have taken effect

Step 6: Increase the total memory to the desired size

Because of current limitations in the GNU C Library (glibc), ASE is limited to 893mb. A workaround to increase this to 1400mb has been submitted.

Increase the total memory to desired size. Remember the above limitation as well as the 128mb limitation on Linux kernel 2.0.36.

For example, to increase the total memory to 500mb:

1> sp_configure "total memory", 256000
2> go
1> shutdown
2> go

* Note that it is possible to increase the total memory far above the physical RAM

Back to top

9.7.4: Sybase now available on Free BSD

Amazing, the Sybase folks have got ASE running on FreeBSD!  The following post is from Reinoud van Leeuwen (reinoud.v@n.leeuwen.net). His web site is http://www.xs4all.nl/~reinoud and contains lots of other useful stuff.

Sybase has made an update of their free 11.0.3.3 SQL server available.  This updated version includes some bug fixes and *FreeBSD support*.

The 11.0.3.3 version is unsupported, but Free for development *and production*!

The server still runs under the Linux emulation, but there is a native SDK (libraries).

download on

http://www.sybase.com/linux/ase/

some extra info on:

http://my.sybase.com/detail?id=1009270

Here are the notes I made to get everything working (still working on things like sybperl, dbd::sybase and PHP :-)

notes on getting Sybase to work on FreeBSD 4.0 RELEASE
======================================================

(log in as root)

1: create a user sybase. give it /usr/local/sybase as home  directory. I gave him bash as shell and put him in the group sybase 

2: put the following files in /usr/local (they contain the path sybase): 

• sybase-ase-11.0.3.3-FreeBSD-6.i386.tgz 
• sybase-doc-11.0.3.3-FreeBSD-6.i386.tgz 
• sybase-ocsd-10.0.4-FreeBSD-6.i386.tgz 

3: untar them: 

 tar xvzf sybase-ase-11.0.3.3-FreeBSD-6.i386.tgz
 tar xvzf sybase-doc-11.0.3.3-FreeBSD-6.i386.tgz
 tar xvzf sybase-ocsd-10.0.4-FreeBSD-6.i386.tgz
 rm sybase*.tgz

4: change the ownership of the tree to sybase:

 chown -R sybase:sybase /usr/local/sybase

5: install the FreeBSD linux emulation:

• add the following line to /etc/rc.conf
  

  linux_enable="YES"

• build the following ports:
  

  /usr/ports/emulators/linux_base

(TIP: move the nluug site up in the makefile, this speeds up things considerably from the Netherlands!)

6: build a kernel that supports System V shared memory blocks make sure that the following lines are in the kernel config file (/sys/i386/conf/YOUR_KERNEL)

# the next 3 are now standard in the kernel
 options SYSVSHM
 options SYSVMSG
 options SYSVSEM

 options SHMMAXPGS="8192"
 options SHMMAX="(SHMMAXPGS*PAGE_SIZE+1)"

(this might be a good time to also enable your kernel for Multi processor) It is also possible to set the last two entries during runtime:

sysctl -w kern.ipc.shmmax=32000000
sysctl -w kern.ipc.shmall=8192

(log in as sybase or su to it; make sure that the SYBASE environment variable is set to /usr/local/sybase ; the .cshrc file should set it.)

7: brand some executables to make sure FreeBSD knows that they are Linux ones 

 brandelf -t Linux /usr/local/sybase/install/sybinit
 brandelf -t Linux /usr/local/sybase/install/startserver
 brandelf -t Linux /usr/local/sybase/bin/*

8: run ./install/sybinit

With this program you should be able to install a sybase server and a backup server. (see the included docs or the online manuals on http://sybooks.sybase.com)

9: To make Sybase start during system boot copy the following script to /usr/local/etc/rc.d and make it executable by root

 #!/bin/sh
 # start all sybase servers on this system
 # assume that sybase is installed in the home dir of user
 # sybase
 export SYBASE=`grep -e "^sybase" /etc/passwd | cut -d: -f 6`
 export PATH="${SYBASE}/bin:${SYBASE}/install:${PATH}"

 unset LANG
 unset LC_ALL

 cd ${SYBASE}/install

 for RUN_SERVER in RUN_*
 do
      su sybase -c "startserver -f ${RUN_SERVER}" > /dev/null 2>&1
      echo -n "${RUN_SERVER} "
 done
 echo

# end of script

Getting 2 CPU's working
=======================

Two get Sybase running on 2 CPU's involves two steps:

• getting Unix working on 2 CPU's and
• configuring Sybase to use them.

1: Getting FreeBSD to work on 2 CPU's.

Build a new kernel that supports 2 CPU's. Run the command mptable (as root). note the last few lines of output, they will tell you what you should include in your kernel file.

Edit the Kernel file and build it. Note the messages during the next reboot. It should say somewhere that it uses the second CPU now.

2: insert the following line in the sybase.sh startup script in /usr/local/etc/rc.d

 export SRV_CPUCOUNT=2 

Also insert this line in the files where environment variables are set for the user sybase. Edit the config file for the sybase server(s) on your system (/usr/local/sybase/<SERVERNAME>.cfg). Change the values in the line "max online engines" from "Default" to "2". (Another option is to give the SQL command sp_configure "max online engines",2) During the next Sybase reboot, the last line in the errorlog should say something like:

engine 1, os pid xxx online

there should be two processes with the name dataserver now.

9.8.1: Other Extended Stored Procedures

The following stored procedures were written by Ed Barlow sqltech@tiac.net and can be fetched from the following site:

http://www.edbarlow.com

Here's a pseudo-man page of what you get: