Useful selects:

Get user defined tables: select * from DOMAIN.TABLES where TYPE='TABLE'

Useful links

sapdb tips: Database Instances, comparison, data types, and memory sizes in plain english. I wish I had found this first.

Compile PHP with sapdb

To compile php with sapdb, include the switch "--with-sapdb=/opt/sapdb/indep_prog"

Example php script for sapdb odbc

odbc_connect("localhost:TST","DBA","DBA")

#!/usr/local/bin/php
<?php
$db_type = 'odbc';
$user = 'dba';
$pwd = 'dba';
$db_host = 'saphost';
$db_name = 'db_instance';

$dsn = "$db_host:$db_instance"

$conn = odbc_connect($dsn, "DBA", "DBA");

$sql="select setting_name, setting_value from AppSettings";
$results = odbc_exec($conn,$sql);
if ($results) {
    while (odbc_fetch_into($results,$row)) {
        echo ">>".$row[0].">br<\n";
    }
}

?>

ODBC in PHP Pear has an bug

If you are experience the folloing error with PHP when connection with Pear's DB package

[nativecode=IM002 [SAP AG][LIBSQLOD SO]Data source name not found and no default driver specified.]

Apply this patch:

--- odbc.php 2003-12-23 14:36:57.000000000 -0700
+++ odbc.php.new 2003-12-23 14:37:05.000000000 -0700
@@ -122,12 +122,13 @@
         $dbhost = $dsninfo['hostspec'] ? $dsninfo['hostspec'] : 'localhost';
         $user = $dsninfo['username'];
         $pw = $dsninfo['password'];
+ $database = $dsninfo['database'];
         if ($this->provides('pconnect')) {
             $connect_function = $persistent ? 'odbc_pconnect' : 'odbc_connect';
         } else {
             $connect_function = 'odbc_connect';
         }
-        $conn = @$connect_function($dbhost, $user, $pw);
+        $conn = @$connect_function("$dbhost:$database", $user, $pw);
         if (!is_resource($conn)) {
             return $this->raiseError(DB_ERROR_CONNECT_FAILED, null, null,
                                          null, $this->errorNative());

LIMIT 10,20 for SAPDB

I've been very disappointed by SAP's help regarding this issue. It seems that the entire mailing list is filled up with requests for a MySQL like LIMIT function and an equal number of incoherent and useless responses that say little more than look in the archive. In one message, the SAP representative expressed his surprise that anyone would even want to be able to do this. It's amazing that this company acts like it's a development mistake to want to do this. Jeeze!

Further complicating this problem is the fact that the ROWNO paramter doesn't play well with ORDER BY or DESC. SAP thought there database could respond faster if they randomly picked X results (specified by ROWNO) and then sorted the table. They are absolutely right! The results ARE returned faters... They are useless for the most part, but they ARE returned faster. Real fast, useless results. That's what I want from a database (Extreme Sarcasm Here).

Someone I work with and I sat down to attempt to write a stored procedure that would fix this problem. We had to learn about cursors and stored procedures to do it, (we still don't have the math down), but this is what we ended up with:

DROP DBPROC MH_RANGE2
//
CREATE DBPROC MH_RANGE2(IN startpos Integer, IN endpos Integer, IN numfields Integer) returns cursor AS
   begin
       /* Sort the table */
       DECLARE c3 CURSOR FOR select * from dba.TableName ORDER BY MainIndex FOR REUSE;
       /* Grab all the results up to the last required result */
       DECLARE c4 CURSOR FOR select * from dba.c3 where rowno<= :endpos FOR REUSE;
       /* Turn the table upside down - ROWNO does not do a >= compare */
       DECLARE c5 CURSOR FOR select * from dba.c4 order by INTERNALDATE DESC FOR REUSE;
       /* Now return the results */
       DECLARE :$cursor CURSOR for select * from dba.c5 where ROWNO<=:numfields;
    end;

Coherent SAPDB Docs

Answer

Query Table and Column Names

To Query a the column names from a table:

    select tablename from DOMAIN.TABLES where type='TABLE'

    select columnname from DOMAIN.COLUMNS where tablename='[insert table]'

autoincrement for sap

AutoIncrement for sap can be specified with serial(1):

create table myTable { /
    unique_number integer default serial(1) NOT NULL, /
    a_useless_number int /
    mytext varchar(255) /
}

Notice the 'integer' field in the autoincrement field. Don't know why, but it doesn't work with an int though it does work with no type specified at all.

sapdb-web issues (rc.config, permission denied)

rc.config is called from the standard sapdb-web init.d script. This is a SuSe file and is not available under RedHat Linux. Basically, it's primary purpose is to tell the script whether to start or not. In RedHat, this is done by including an entry in /etc/rcX.d (see init Notes for more information). To fix the sapdb-web script, either add the /etc/rc.config with one line (START_SAPDB=YES) or replace the test with a set.

There may be some permission problems with the sapdb-web script too. In the case of the version I downloaded, there was a direct call to the /opt/sapdb/indep_prog/web/pgm/wahttp.conf file. I'm not sure why it was set up like that, so I removed it and used it to set the default parameter. The final sapdb-web script is shown below:

#!/bin/bash
echo SapDB Web Control Database

CONFIG_FILE=/usr/spool/sql/ini/WebAgent74.ini
START_SAPDB=YES

return=$rc_done

export DBROOT=/opt/sapdb
case "$1" in
        start)
        echo "Starte SapDB Web Interface/Server";
        cd $DBROOT/indep_prog/web/pgm &&=/usr/sapdb/web/lib:/usr/sapdb/depend/lib ./wahttp -f CONFIG_FILE 2>&1>dev/tty9 &
        echo -e "$return"
        ;;

        stop)
        echo "Beende SapDB Webinterface";
        killall -q -6 wahttp 2>&1>dev/tty9
        echo -e "$return"
        ;;

        *)
        echo "Usage $0 {start|stop}";
esac

test "$return" = "$rc_done" || exit 1
exit 0

Example /etc/rc.d/init.d/sapdb script. I think you can set up one of these for every instance. In the end, someone will probably want to write a small script to load a list of the instances from a config file and start them all individually. I might do this myself if I decide to use sap.

#!/bin/bash
# /etc/sapdb.rc
echo SapDB Database
if [ ! -f /etc/sapdb.rc ] ; then
    echo "Error: No SapDB resource file Refer to: /etc/sapdb.rc"
    exit 1
fi

source /etc/sapdb.rc
if [ -z $INSTANCE ] ; then
        echo None INSTANCE name is given in /etc/sapdb.rc
        exit 1
fi

if [ -z $SAPDBUSER ] ; then
        echo None System-Runtime-User \$SAPDBUSER Parameter is defined in /etc/sapdb.rc
        exit 1
fi



DBMCLI="$SAPDB/depend/bin/dbmcli"
X_SERVER="$SAPDB/indep_prog/bin/x_server"

case "$1" in
        start)
        echo "Starting SapDB remote SQL";
        su $SAPDBUSER -c "$X_SERVER start" 2>&1 >/dev/null

        echo -n "Starting SapDB Database $INSTANCE: cold ... ";
        su $SAPDBUSER -c "$DBMCLI -d $INSTANCE -u $DBM db_start"
        if [ $? -gt 0 ] ;  then
                echo -n "Starting SapDB Database: Problem Clearup: ";
                su $SAPDBUSER -c "$DBMCLI -d $INSTANCE -u $DBM db_clear"  2>&1 >/dev/null
                su $SAPDBUSER -c "$DBMCLI -d $INSTANCE -u $DBM db_start"  2>&1 >/dev/null
        fi

        if [ $? -gt 0 ] ;  then
                echo -n "SapDB Database Problem, Cold-Start not Possible";
                exit 1
        fi

        echo -n "Starting SapDB Database $INSTANCE: warm ... ";
        su $SAPDBUSER -c "$DBMCLI -d $INSTANCE -u $DBM db_warm"
        if [ $? -gt 0 ] ;  then
                echo -n "Starting SapDB Database: Problem Clearup: ";
                su $SAPDBUSER -c "$DBMCLI -d $INSTANCE -u $DBM db_clear"  2>&1 >/dev/null
                su $SAPDBUSER -c "$DBMCLI -d $INSTANCE -u $DBM db_start"  2>&1 >/dev/null
                su $SAPDBUSER -c "$DBMCLI -d $INSTANCE -u $DBM db_warm"   2>&1 >/dev/null

        fi

        if [ $? -gt 0 ] ;  then
                echo -n "SapDB Database Problem, Warm-Start not Possible";
                exit 1
        fi

        ;;

        stop)
        echo "Shutdown SapDB $INSTANCE"
        su $SAPDBUSER -c "$DBMCLI -d $INSTANCE -u $DBM db_cold"  2>&1 >/dev/null
        su $SAPDBUSER -c "$DBMCLI -d $INSTANCE -u $DBM db_offline"  2>&1 >/dev/null
        echo "Beende SapDB remote SQL.";
        su $SAPDBUSER -c "$X_SERVER stop"  2>&1 >/dev/null
        killall -q vserver
        ;;

        *)
        echo "Usage $0 {start|stop}";
esac

exit 0

Example sapdb.rc

# SapDB Installationsort:
SAPDB=/opt/sapdb
#
# To define The Database basics
#
#
# System-Runtime-User of the SapDB Instance
#
SAPDBUSER=sapdb
#
# Instance Name, Name of the running Database
#
INSTANCE=wbedb
# Define here the Master Users and Passwords
# Database Manager for the Instance itself: create, backup, restore,
# this user creates initially the Database Content Adminstrator (dba)
# Format: user,password
DBM=dbm,dbm
#
# The Database Content Administrator DBA this is the SQL-Level Masteruser
# for creating further DB-Users, distributes Roles etc.
DBA=dba,dba

#
# USER for ODBC Environment/Systemuser, no User level
#
DOMAIN=domain,domain


#
# SAPDBBACKUPDIR
# specify directory, where to put file-based autobackups
# of the whole Database.
# if this dir does not exist, or is not writeable to the
# sapdb user, there will be used a default dir:
# $HOME/backup/$INSANCE
# $HOME marks the user-home of the SAPDB Runtimeuser
# SAPDBBACKUPDIR=/home/fileserver/backup/CAP-pc1
#

# To create all nessesary tables from scratch
TABLES=/opt/sapdb/script/sql/all-tables.sql
CONSTRAINTS=/opt/sapdb/script/sql/all-constraints.sql
USERS=/opt/sapdb/script/sql/all-users.sql

# To define the Database Parameters
# Standard:            OLTP
# LiveCache:           LVC {only for SAP/R3 Installations}
# Contentserver:       CS
# Buisiness Warehouse: BW
# one PAGE is 8192 Bytes
# Example:
# INITIAL=BW
# DATAPAGES=8192
# LOGPAGES=4096

# Parameter
INITIAL=OLTP
DATAPAGES=8192
LOGPAGES=4096

Connecting to the web interface

http://localhost:9999/webdbm Default user password is dbm/dbm

http://localhost:9999/webdbm Default user/password is dba/dba

Timestamps on SAPDB

Timestamps cannot be zero '0000-00-00 00:00:00'. SAP will throw the 3050, Invalid timestamp format:INTERNAL error. Use "timestamp default time" instead.

From sap maillist:

   As I said, 0 years and something is not valid.
   SAP DB is only able to store date values from 1. of  January 0001 to 31. of December 9999, 
   no BC, no 'during live of the  5. king of the romantic period'
   or something like this.

   Elke
   SAP Labs Berlin

Typically you want to set the timestamp to the current time. For example:

sql_execute DROP TABLE Sessions
sql_execute CREATE TABLE Sessions ( /
    session_id varchar(32) NOT NULL default '', /
    last_access_time timestamp NOT NULL default TIMESTAMP, /
    data long varchar NOT NULL, /
    PRIMARY KEY  (session_id) /
)

Adding an index (key in MySQL)

MySQL provides a KEY mechanism to specify when indexes are generated for column names.

CREATE TABLE TheSmallTable ( /
    myname varchar(255) NOT NULL default '', /
    yourname varchar(32) NOT NULL default '', /
    testTime timestamp NOT NULL default, /
    PRIMARY KEY  (myname,yourname), /
    KEY testTime (testTime)
)

Most other databases SAP would use the following convention for the above:

sql_execute CREATE TABLE TheSmallTable ( /
    myname varchar(255) NOT NULL default '', /
    yourname varchar(32) NOT NULL default '', /
    testTime timestamp NOT NULL default, /
    PRIMARY KEY  (myname,yourname)
)
sql_execute CREATE INDEX testTime on TheSmallTable(testTime)

Quick Useful notes

Well, a couple of things I stole from some other site somwhere:
0) Environment setup (su - sapdb)
1) Starting the database (xserver start)
2) Starting the instance (dbmcli -d sid -u dbm,dbm db_warm)
3) Shutting down the instance (dbmcli -d sid -u dbm,dbm db_offline)
4) Recovering from un-clean shutodown.
5) Backing up online (and restoring).
6) Running queries to create tables as part of creating a DB.
7) The possible meanings of: "Message not available".

After installation, add these:

# ld.so.conf
/opt/sapdb/indep_prog/web/lib
/opt/sapdb/indep_prog/lib
/opt/sapdb/web/lib

bash# ldconfig -v

Startup Error on RedHat 9

This one is esoteric. The startup error tells you absolutely nothing about the error. Redhat 9 will throw an error: "Kernel died before reaching ADMIN state" and tell you to check the knldiag file. The knldiag file is pretty useless for this. What you need to do is set the following environment variable; I don't know why (still new at sap).

export LD_ASSUME_KERNEL=2.2.5

SAP Enviornment Variables

Create the /etc/profile/profile.d/sapdb.sh file

export SAPDB=/opt/sapdb
export DBROOT=/opt/sapdb/depend
if [ -d $SAPDB/indep_prog/bin ] ; then
    export PATH=$PATH:$SAPDB/indep_prog/bin
fi

if [ -d $DBROOT/bin ] ; then
    export PATH=$PATH:$DBROOT/bin
fi

if [ -z "$CLASSPATH" ] ; then
    CLASSPATH=/usr/lib/java/lib:/usr/lib/java/jre/lib:.
fi

if [ -f /usr/lib/java/lib/sapdbc.jar ] ; then
    export CLASSPATH=$CLASSPATH:sapdbc.jar:
fi

if [ -f /usr/lib/java/jre/lib/sapdbc.jar ] ; then
    export CLASSPATH=/usr/lib/java/jre/lib:sapdbc.jar:$CLASSPATH
fi

sapdb sysV startup scripts

init.d/sapdb

#!/bin/bash
# /etc/sapdb.rc
echo SapDB Database
if [ ! -f /etc/sapdb.rc ] ; then
    echo "Error: No SapDB resource file Refer to: /etc/sapdb.rc"
    exit 1
fi

source /etc/sapdb.rc
if [ -z $INSTANCE ] ; then
        echo None INSTANCE name is given in /etc/sapdb.rc
        exit 1
fi

if [ -z $SAPDBUSER ] ; then
        echo None System-Runtime-User \$SAPDBUSER Parameter is defined in /etc/sapdb.rc
        exit 1
fi



DBMCLI="$SAPDB/depend/bin/dbmcli"
X_SERVER="$SAPDB/indep_prog/bin/x_server"

case "$1" in
        start)
        echo "Starting SapDB remote SQL";
        su $SAPDBUSER -c "$X_SERVER start" 2>&1 >/dev/null

        echo -n "Starting SapDB Database $INSTANCE: cold ... ";
        su $SAPDBUSER -c "$DBMCLI -d $INSTANCE -u $DBM db_start"
        if [ $? -gt 0 ] ;  then
                echo -n "Starting SapDB Database: Problem Clearup: ";
                su $SAPDBUSER -c "$DBMCLI -d $INSTANCE -u $DBM db_clear"  2>&1 >/dev/null
                su $SAPDBUSER -c "$DBMCLI -d $INSTANCE -u $DBM db_start"  2>&1 >/dev/null
        fi

        if [ $? -gt 0 ] ;  then
                echo -n "SapDB Database Problem, Cold-Start not Possible";
                exit 1
        fi

        echo -n "Starting SapDB Database $INSTANCE: warm ... ";
        su $SAPDBUSER -c "$DBMCLI -d $INSTANCE -u $DBM db_warm"
        if [ $? -gt 0 ] ;  then
                echo -n "Starting SapDB Database: Problem Clearup: ";
                su $SAPDBUSER -c "$DBMCLI -d $INSTANCE -u $DBM db_clear"  2>&1 >/dev/null
                su $SAPDBUSER -c "$DBMCLI -d $INSTANCE -u $DBM db_start"  2>&1 >/dev/null
                su $SAPDBUSER -c "$DBMCLI -d $INSTANCE -u $DBM db_warm"   2>&1 >/dev/null
                # Start the WEBDB server
                su $SAPDBUSER -c "$DBMCLI -d WBEDB -u $DBM db_clear"  2>&1 >/dev/null
                su $SAPDBUSER -c "$DBMCLI -d WBEDB -u $DBM db_start"  2>&1 >/dev/null
                su $SAPDBUSER -c "$DBMCLI -d WBEDB -u $DBM db_warm"  2>&1 >/dev/null

        fi

        if [ $? -gt 0 ] ;  then
                echo -n "SapDB Database Problem, Warm-Start not Possible";
                exit 1
        fi

        ;;

        stop)
        echo "Shutdown SapDB $INSTANCE"
        su $SAPDBUSER -c "$DBMCLI -d WBEDB -u $DBM db_cold"  2>&1 >/dev/null
        su $SAPDBUSER -c "$DBMCLI -d WBEDB -u $DBM db_offline"  2>&1 >/dev/null

        su $SAPDBUSER -c "$DBMCLI -d $INSTANCE -u $DBM db_cold"  2>&1 >/dev/null
        su $SAPDBUSER -c "$DBMCLI -d $INSTANCE -u $DBM db_offline"  2>&1 >/dev/null
        echo "Beende SapDB remote SQL.";
        su $SAPDBUSER -c "$X_SERVER stop"  2>&1 >/dev/null
        killall -q vserver
        ;;

        *)
        echo "Usage $0 {start|stop}";
esac

exit 0

Follow this up with a set of symbolic links:

ln -s /etc/rc.d/init.d/sapdb /etc/rc1.d/K45sapdb
ln -s /etc/rc.d/init.d/sapdb /etc/rc2.d/K45sapdb
ln -s /etc/rc.d/init.d/sapdb /etc/rc3.d/S45sapdb
ln -s /etc/rc.d/init.d/sapdb /etc/rc4.d/K45sapdb
ln -s /etc/rc.d/init.d/sapdb /etc/rc5.d/S45sapdb
ln -s /etc/rc.d/init.d/sapdb /etc/rc6.d/K45sapdb

S45 says that the file should be started and K45 means it should be stopped.

####################sql2sap script
#!/usr/bin/perl
#
# SQL2SAP Filter to read SQL files into SAPDB
# written by Fabian Morchen <fabian at the mybytes dot de domaina>
#
# Usage example:
#   cat oracle-demo.sql | ./sql2sap | dbmcli -d TST -u dba,dba -uSQL /so.conf
#test,test
#
# So far only tested with the Oracle sample database
#
# Use as you like, but please post improvements on sapdb-general@sap.com

while (<>) {
  if (length($_) gt 1) {
    # put the sql_execute command before every line not starting with space/tab
    $_ =~ s/^(\w)/sql_execute\ \1/;
    # change dates
    $_ =~ s/-JAN-/.01./;
    $_ =~ s/-FEB-/.02./;
    $_ =~ s/-MAR-/.03./;
    $_ =~ s/-APR-/.04./;
    $_ =~ s/-MAY-/.05./;
    $_ =~ s/-JUN-/.06./;
    $_ =~ s/-JUL-/.07./;
    $_ =~ s/-AUG-/.08./;
    $_ =~ s/-SEP-/.09./;
    $_ =~ s/-OCT-/.10./;
    $_ =~ s/-NOV-/.11./;
    $_ =~ s/-DEC-/.12./;
    $_ =~ s/\'(\d)\.(\d\d)\.(\d\d)/\'0\1\.\2\.\3/;
    $_ =~ s/(\d\d)\.(\d\d)\.(\d\d)/19\3\2\1/;
    # data type conversions
    $_ =~ s/NUMBER/FIXED/g;
    $_ =~ s/VARCHAR2/VARCHAR/g;
    # append / to every line not ending with ; and remove all ;
    if (!($_ =~ s/;$//)) { $_ =~ s/$/\ \//; }
    print;
  }
}