I connected to the new database via the web SQL interface by pointing my browser to http://localhost:9999/websql (this is the URL for the SQL tool - /webdbm gives you the database administration tool). Using the database name of TST, username TEST, and password TEST, I logged into the SQL front end for the database. The interface is fairly clean and easy to use. One complaint I have is that the web tools uses JavaScript and CSS, which can cause some issues in older browsers.

With the browser running the SQL interface, I used the 'show table' SQL statement to see the tables in the TST database. SAPdb returned a listing of all the tables associated with the database, including the system and configuration tables. There's quite a few of them and if you look under the owner column you can see who they belong to. DBA and DOMAIN own the tables for database administration and configuration, and SYS owns the tables for system settings.

Since none of these tables belong to user TEST, we shouldn't be mucking around with them. Creating a new table will give us something to play with. Using the standard 'create table {…}' SQL syntax, I was able to create a new table called alumni. I won't go into detail about each of the SQL commands that I used. If you do need help with them, you can refer to the "SQL statement: overview" chapter in the SAPdb reference manual. Next, I put in a small amount of test data using 'INSERT'. This also went smoothly, so I decided to try loading bulk data from a comma-delimited file.

Here things became a little tricky… I had previously used the 'load data' statement in mySQL and the 'copy' command in PostgreSQL, but I wasn't sure where to look for the SAPdb implementation. The difficulty was in finding the proper section of the documentation for importing data from external file sources. I finally found a tool named repmcli, which is included with SAPdb in the /depend/bin folder. The documentation states that it can load and unload data from and into external files.

With a little patience I managed to load the test data into the table, although I had to edit my comma delimited file to put double quotes around each data field. Double quotes are a SAPdb default, and can be changed to a different character if necessary. If you are using Robelle's STExport tool to export the data, use the 'quote double' command.

The repmcli command includes a rich set of features; it can accomplish some fairly complicated things by executing a batch file. The batch file includes specific commands and SQL statements which tell SAPdb how to load the data. In my case I used a simple batch file, which I created in VI and named command.dat with the following code:

	FASTLOAD TABLE alumni
		Id 1
		Birthdate 2
		Title 3
	INFILE 'testdata.txt'

In this batch file, alumni is the test table, and testdata.txt is our comma delimited data file with double quotes surrounding each field entry.

SAPdb Evaluation/Comparison with Other Databases

I find it difficult to compare SAPdb to mySQL. The two offer separate solutions to different problems. The learning curve with mySQL is far smaller than that of SAPdb. However mySQL does not have the features of SAPdb. Ease of use and availability is the reason mySQL is such a popular choice for backend database operations. For most web applications it does its job perfectly and with mySQL max and mySQL v4.x on the way and support for transactions and innodb tables, mySQL will offer even more bang for the buck. Performance-wise mySQL is very fast with small sites, in fact one of the fastest databases available. However as soon as you increase the number of concurrent users, mySQL starts to slow down exponentially and gets blown away by both SAPdb and PostgreSQL. If your site is small and the need for advanced SQL functions is not needed, mySQL will more than fit the bill. When you weigh in the fact that the average programmer could easily pick it up in a day or two, mySQL begins to look especially attractive.

Comparing SAPdb with PostgreSQL is a little bit easier. Both databases have many of the advanced features found in enterprise level database solutions, and focus on features/performance. PostgreSQL has a slightly higher learning curve than mySQL, but is nowhere near as hard to learn as SAPdb. It also comes with online documentation and many third-party publishers have books relating directly to PostgreSQL. For my needs I found that I could do everything I had to in PostgreSQL, and in half the time or less than it took me in SAPdb. For a user discussion of SAPdb vs PostgreSQL, read this Slashdot thread.

The real target audience for SAPdb is in the enterprise market, where behemoths like Oracle have dominated the market for years. SAPdb strives to offer all of the features available in Oracle with high performance, but for free. This sounds a bit too good to be true, and at this current moment I think SAPdb still has to make some progress if they want to convince Oracle administrators and users that their product is not only superior, but also free.

While SAPdb supports many of the features found in Oracle, I would be a little hesitant before replacing any existing Oracle backends with SAPdb. The reason I come to this conclusion is that Oracle is built on mature code, which has been used in large scale production sites for many moons. Oracle has a larger group of users who have been demanding top notch stability and performance from their databases since day one. SAPdb on the other hand is a bit of a newcomer. Although it has been around for some time (originally Adabas D), SAPdb really hasn't been used for much other than the SAP R/3 framework. Many of the bugs still haven't been found and are only being exposed now as SAPdb grows in popularity and maturity. Your best bet by far is to try out SAPdb on a test server, with test data and see if it offers what you really need.

SAPdb Datatypes

Character Datatypes:

Char[n]: Data is stored with a fixed length of n. n can take on any value between 0 and 8000, and if not supplied it is assumed to be 1.

If you are migrating a TurboIMAGE X or U field, you select either Varchar or Char. When you return the SQL data into your COBOL program, you must specify a fixed-size field, so either datatype will work. However, many people use Varchar by preference because it allows the database the option of optimizing the space for the values.

Numeric Datatypes:

Int[]: This data type is the same as FIXED(10.0). Its permitted values are between -2147483648 and 2147483647.

SmallInt[]:This data type is the same as FIXED(5.0). Its permitted values are between -32768 and 32767.

Float[p]: A column with a floating point number with precision p (0 Fixed [p,s]: A column is created with a fixed point number with precision p and with s number of decimal places. If s is not supplied it is assumed there are 0 decimal places. P can take values from 0 to 38 and s must be less than or equal to p.

When converting a TurboIMAGE I1 field, select Smallint. For I2 select Int. Notice that there is no 8-byte integer type, so I4 (PIC S9(10) COMP) must be converted to Fixed in the database and COMP-3 (packed decimal) in COBOL. For J and K fields, use the same types as for I.

TurboIMAGE E types convert to Float, while R types are deprecated (this is the old floating point of the Classis HP 3000). N and P convert to Fixed.

Date and Time Datatypes:

Date: Allows you to store special character strings which describe a date value.

Time: Allows you to store special character strings which describe a time value.

Timestamp: A timestamp consists of a date and time value and a microsecond specification.

Boolean Datatype:

Other Datatypes:

Data Type	Is Mapped To
DEC[IMAL](p,s)	FIXED(p,s)
DEC[IMAL](p)	FIXED(p)
DEC[IMAL]	FIXED(5)
BINARY(p)	FIXED(p)
FLOAT	FLOAT(16)
FLOAT(39..64)	FLOAT(38)
DOUBLE PRECISION	FLOAT(38)
REAL(p)	FLOAT(p)
REAL	FLOAT(16)
LONG VARCHAR	LONG
SERIAL	FIXED(10) DEFAULT SERIAL
SERIAL(p)	FIXED(10) DEFAULT SERIAL(p)

Adding UNICODE to the column declaration:

Adding LONG to the column declaration:

Memory Requirements of Columns based on Datatypes:

Date and Time Formats

Users can change the date and time format for the current session by setting the relevant parameters in the database tools or by specifying the corresponding parameters when using programs.

There are five formats:

   <datetimeformat> ::= EUR | INTERNAL | ISO | JIS | USA

Date value

'YYYY'	Four-digit year format
'MM'	Two-digit month format (01-12)
'DD'	Two-digit day format (01-31)

Date Value Examples:

Format	General Format	Example
EUR	'DD.MM.YYYY'	'23.01.1999'
INTERNAL	'YYYYMMDD'	'19990123'
ISO/JIS	'YYYY-MM-DD'	'1999-01-23'
USA	'MM/DD/YYYY'	'01/23/1999'

In all formats, with the exception of INTERNAL, leading zeros may be omitted in the identifiers for the month and day.

If you are transforming dates from a TurboIMAGE database to load a SAPdb table, you may have to adjust the date format to make it match the format you selected for SAPdb. For example, if your dates are stored in TurboIMAGE as J2 (binary) with a format of YYYYMMDD and your SAPdb format is USA, then you could do the conversion with these Suprtool and STExport commands:

Suprtool Commands:

Get salesdetail
Item salesdate,date,yyyymmdd
Extract customer,salesdate,item,quantity
Output tempsales,temp,link
Xeq

STExport Commands:

input tempsales
quote double
date  mmddyyyy "/"
output sapdbsales,temp
xeq

Time value

Time Value Examples:

Format	General Format	Example
EUR	'HH.MM.SS'	'14.30.08'
INTERNAL	'HHHHMMSS'	'00143008'
ISO/JIS	'HH:MM:SS'	'14:30:08'
USA	'HH:MM AM (PM)'	'2:30 PM'

In all time formats, the identifier of the hour must consist of at least one digit. In the USA time format, the minute identifier can be omitted completely. In all the other formats, with the exception of INTERNAL, the minute and second identifiers must comprise at least one digit.

Timestamp value

Timestamp value Examples:

Format	General Format	Example
EUR/JIS/USA	'YYYY-MM-DD-HH.MM.SS.MMMMMM'	'1999-01-23-14.30.08.456234'
ISO	'YYYY-MM-DD HH:MM:SS.MMMMMM'	'1999-01-23 14:30:08.456234'
INTERNAL	'YYYYMMDDHHMMSSMMMMMM'	'19990123143008456234'

The microsecond identifier can be omitted in all timestamp formats. In all formats, with the exception of INTERNAL, the month and day identifiers must consist of at least one digit. For INTERNAL the month and day identifiers must have exactly two digits.

SAPdb Resources:

Additional reading on the Robelle web site:

• About mySQL
• About PostgreSQL
• About Oracle
• Robelle's Migration Web Center