SAP Sybase ASE - An introduction for a newbie

Since the day of its acquisition of Sybase, SAP has made great strides in porting its business suite on to Sybase ASE (known as Adaptive Server Enterprise - the Sybase relational database). Last week's announcement from SAP is just one of the many milestones that symbolises its committment to Sybase products and highlights the successes. The highest number in SD benchmark was achieved on a 2 core HP/Linux machine by ASE. Check out the details here.

As SAP works towards its stated goal of becoming a premier database company in the world, it is but natural that more and more SAP practitioners and enthusiasts become interested in ASE and may be looking to use it not only for SAP applications but also for other in-house applications. For a java programmer, familiarity with the JDBC API is enough to use a standard RDBMS. But once in a while one is required to connect directly to the underlying database to debug an issue. Unlike production systems where you can depend on a DBA for support, you are often left to deal with problems yourself in a development environment. Quirks or peculiarities in a new relational database system can be a source of frustration. Some features that totally make sense in one database may not work the same way in other. It's those little things, quirks, that can drive one crazy when one is dealing with tighter deadlines.

I have been working with SAP Sybase Adaptive Server Enterprise for more than a year now. I feel comfortable in sharing some bits and pieces I pieced together over that period. Much of this information is available on the web and or in the product documentation, but scouring and digging through this information can take time. Also, some of the information is available at most unlikely places.  Having worked with popular databases other than ASE before, these key features seemed interesting, a bit different or I came across them too often. This is not a comprehensive list of ASE features, but it is a list to save some time for a newcomer to quickly get off the ground. So here it goes.

First, SAP allows free downloads of developer versions of most of the Sybase products. You can download the one you want from here. Consider revisiting this post before you install ASE.

1. Concept of devices and databases in ASE - They resemble the concepts of databases and schemas in Oracle.

2. Finding the version of the database you are running - Sybase products including ASE are fast evolving to cater to SAP ecosystem. Major features are being added fairly regularly. When something isn't working as expected, first thing you may want to do before calling into tech support is to find the version of the database you are running and refer to product documentation here.

• dataserver -v 

3. Sybase environment variables are set in sybase.csh file on linux installation. The server startup/shutdown scripts could be found in following directory

• your sybase home directoy/ASE-15_0/install

4. If you believe that an installed instance is not responding to your application code, the most likely reason could be that the thread is kept waiting due to the lack of log space. To free up the space you can use following commands. Refer to sybase documentation on what each does, but if you are not concerned about losing the running transaction, then either of the following should work. You should see your transaction going through as soon as you run the following command.

• DUMP TRANSACTION your_database_name WITH TRUNCATE_ONLY
• DUMP TRANSACTION your_database_name with no_log

Before you try above commands, you may want to look at all the running connections/threads to ASE and their status by executing sp_who stored procedure.  

5. One of the most important things you need to decide while installing the ASE is the page size. All data from one row of one table is colocated in one page to improve performance. Once defined, you can not change page size for the installed ASE instance. Below is a table showing relationship between the page sizes and corresponding maximum possible database sizes.

• 2K page size - 4 TB
• 4K page size - 8 TB
• 8K page size - 16 TB
• 16K page size - 32 TB

If you are not sure what page size the installed ASE instance is using, then run the following command from isql utility, the client used to connect to ASE.

• select @@maxpagesize

6. If you use Hibernate for data access, you may want to visit hibernate-sybase integration page here. Spending a few minutes on the required ASE settings for hibernate could  save you from some frustration later on.

7. Once the installation is complete you can run Sybase Control Center application that provides a GUI interface to all your ASE instances. You can alternatively use isql, a command line sql interface to interact with ASE. Super admin user name is 'SA' and password could be left blank.

8. Use following stored procedures to add users. sp_addlogin just adds a login and not a user.

• sp_addlogin - adds only a login name 
• sp_adduser - adds a new user.

9. Make sure to assign a role with adequate privileges to the new user by executing following procedure.

• sp_role "grant", some_role, username 

10. Before you try any sql you may want to check the 'reserved words' of ASE by using following command. I recommend to go through this list if you are porting an existing application to ASE.

• select name from master..spt_values where type = "W"

11. Its a common practice to add an autoincrement column to a table and use it as a primary key. Autoincrement is achieved in ASE by denoting a column as IDENTITY. ASE does not allow more than one autoincrement column in a table. There is no  'sequence' as in Oracle. ASE allows adding user defined values to an autoincrement column after you run the following command.

• set identity_insert tablename on

12. If you have used MS SQL Server, then you will find ASE's t-sql very similar as both share the same roots. T-sql is similar to pl/sql in Oracle.

13. ASE's native JDBC driver is called jconnet. JTDS, an open source driver,  can be used also. Jconnect supports changing connection properties through connection url. Just append the connection url with 'property_name=property_value'. For example

•  'jdbc:sybase:tds:servername:portnumber/database?QUOTED_IDENTIFIER=ON'

allows quoted identifiers in a sql statement like below.

• SELECT * from  "dbo". "User_Table"

If you want to run your existing relational database application on ASE then it is really important to pay attention to this connection property. However, if you are building all sqls as 'preparedStatements' then quoted strings are handled correctly even when quoted_identifier is not explicitly turned on.

14. Also important to note is the difference in handling table aliases. Table aliases are allowed in 'SELECT' statements but not in 'UPDATE' or 'DELETE'. For example the first sql below is valid but the second returns an error.

• select * from tablename t1 
• update tablename t1 set columnname=value where column2=value2
   

 15. Sybase Jconnect driver depends on some key metadata information for its correct working. In absence of this information you may receive sql exception with similar description as 'missing metadata'. If this happens,  it means that you missed a step during ASE installation. This missed step installs the metadata  information in the master database. This error could be eliminated by running a stored procedure after the installation. Refer to the documentation here.

16. 'Select for Update' was introduced in ASE 15.7 and you would expect it to work by default in a new install. Alas, no such luck. I highly recommend reading this document before you try this feature. You would need 'datarows' locking scheme on the table on which select is performed. You can turn 'datarows locking' ON on the enitre database or on a table using following commands.

• sp_configure "lock scheme", 0, datarows
• alter table tablename lock datarows

17. ASE is case sensitive by default. To display case sensitivity, sort order and character set settings, use sp_helpsort stored procedure.

18. Following information is useful if you want to programmatically access metadata information from ASE. Sysobjects table in master database holds information on all entities like tables, indexes, views in ASE. To display all user objects use following command.

• Select * from sysobjects where type = 'U'

syscolumns table holds information of all columns from all tables.
sysreferences, sysconstraints hold information on relationships between  tables like foreign key constraints.

19. By joining above tables you can get pretty much all the information about a user table. Unfortunately there is no simple query like 'describe table' in Oracle. You can use following stored procedure to get all the details about a table, but it will include much more than just the column names and their types.

• sp_help tablename

If you want just the column names of a user table, use following sql.

• select sc.name from syscolumns sc, sysobjects so where sc.id=so.id and so.name='tablename'

20. Unlike Oracle, ASE truncates all trailing blanks while storing variable length varchar columns. So be careful if your code has comparisons on strings pulled from varchar columns.

I hope this list will help you install and navigate ASE in early stages. I mentioned these in particular because I had to use these commands  more often than any others. Once you spend a little bit of time on ASE, you will of course come across other things that seem more useful. I will try to list those in my next post.