Sybase: Cannot convert after checking with isnumeric

If you have numeric and not numeric values and what to the isnumeric function to find out which values you can convert, pay attention. Isnumeric is not always reliable. Here an example:

1> create table benohead(almostnum varchar(10))
2> go
1> insert into benohead values('e')
2> go
(1 row affected)
1> select isnumeric(almostnum) from benohead
2> go

 -----------
           1

(1 row affected)
1> select convert(int, almostnum) from benohead where isnumeric(almostnum) = 1
2> go
Msg 249, Level 16, State 1:
Server 'SYBASE', Line 1:
Syntax error during explicit conversion of VARCHAR value 'e' to a INT field.

So in this case isnumeric returns 1 i.e. true. But you still can’t convert it to an int. Converting to a float, money or real will also fail:

1> select convert(float, almostnum) from benohead where isnumeric(almostnum) = 1
2> go
Msg 249, Level 16, State 1:
Server 'SYBASE', Line 1:
Syntax error during explicit conversion of VARCHAR value 'e' to a FLOAT field.
1> select convert(money, almostnum) from benohead where isnumeric(almostnum) = 1
2> go
Msg 249, Level 16, State 1:
Server 'SYBASE', Line 1:
Syntax error during explicit conversion of VARCHAR value 'e' to a MONEY field.
1> select convert(real, almostnum) from benohead where isnumeric(almostnum) = 1
2> go
Msg 249, Level 16, State 1:
Server 'SYBASE', Line 1:
Syntax error during explicit conversion of VARCHAR value 'e' to a REAL field.

But converting to a decimal value will work (well it won’t throw an error):

1> select convert(decimal, almostnum) from benohead where isnumeric(almostnum) = 1
2> go

 ---------------------
                     0

(1 row affected)

Of course, it depends whether you actually mean 0 when writing e in the column… It will not throw an error but might deliver wrong results…

Sybase: Find the name of the current database

If you need to switch to a given database, you can use the following statement:

1> use master
2> go

Replacing master by the database name you want to switch to.

If you want to figure out what’s the current database, there is unfortunately no @@db, @@dbid or @@dbname global variable. But you can check it over the current process ID:

1> select d.name from master..sysdatabases d, master..sysprocesses p where d.dbid=p.dbid and p.spid=@@spid
2> go
 name
 ------------------------------
 master

(1 row affected)

Update: As Peter correctly noticed, there are built-in functions you can use for this too:

1> select db_id()
2> go

 ------
      1

(1 row affected)
1> select db_name()
2> go

 ------------------------------
 master

(1 row affected)
1> select user_id()
2> go

 -----------
           1

(1 row affected)
1> select user_name()
2> go

 ------------------------------
 dbo

(1 row affected)
1> select suser_id()
2> go

 -----------
           1

(1 row affected)
1> select suser_name()
2> go

 ------------------------------
 sa

(1 row affected)

Sybase: space used by the indexes on a table

In order to find the space used by a table (data and indexes) you can execute the following:

1> sp_spaceused mytable
2> go
 name    rowtotal reserved  data    index_size unused
 ------- -------- --------- ------- ---------- ------
 mytable 20612    103320 KB 7624 KB 95216 KB   480 KB

(1 row affected)
(return status = 0)

If you see that the indexes take much more space than expected, you can also use 1 as a second parameter to the sp_spaceused stored procedure:

1> sp_spaceused mytable,1
2> go
 index_name             size reserved unused
 ------------------ -------- -------- ------
 my_index_1           612 KB   736 KB 124 KB
 my_index_2          2840 KB  2864 KB  24 KB
 my_index_3           504 KB   536 KB  32 KB
 my_index_4          3016 KB  3032 KB  16 KB
 my_index_5          1144 KB  1152 KB   8 KB
 tmytable           87064 KB 87288 KB 224 KB
 mytable_key_index     36 KB  7712 KB  52 KB

(1 row affected)
 name    rowtotal reserved  data    index_size unused
 ------- -------- --------- ------- ---------- ------
 mytable 20612    103320 KB 7624 KB 95216 KB   480 KB
(return status = 0)

The tmytable entry are the text and image fields.

SQL: UNION vs. UNION ALL

Both the UNION and UNION ALL operators are used to combine the result of two or more SELECT statements.

Each of the combined SELECT statement must have the same number of columns, compatible data types and have columns in the same order. The returned column names are the column names in the first SELECT statement in the UNION.

This means that:

SELECT 1 AS col1, 2 AS col2 UNION SELECT 3 AS col2, 4 AS col1

will return

col1	col2
1	2
3	4

and not:

col1	col2
1	2
4	3

Here are few example or not valid UNION operations:

SELECT 1 AS col1, 2 AS col2 UNION SELECT 3 AS col2, 4 AS col1, 5 AS col3

The two combined SELECT statements do not have the same number of arguments.

SELECT 1 AS col1, 2 AS col2 UNION SELECT 3 AS col2, 'a' AS col1

The types of 2 and ‘a’ are not compatible.

The difference between UNION and UNION ALL is that UNION ALL will return ALL results of ALL statement without filtering out duplicates. Or you could say UNION is an UNION ALL followed by a DISTINCT operation .e. sorting the results and removing duplicates (lines where all columns in the results of two select statements are the same).

One consequence is that UNION is always slower than UNION ALL. So if you know you won’t get duplicates or you don’t mind duplicates, you should consider using UNION ALL instead of UNION. If you don’t which one you need, then you probably need UNION ALL.

This performance difference can be noticed on all database management systems. But the technical reason behind it might be different: either there is an extra sorting operation performed or an extra index on the temporary work table is needed.

The type of the data returned by both operations depends on the types of the columns returned by the individual statements. The returned type basically needs to be able to store the data of all combined columns.

Note that UNION cannot be used on BLOBs and CLOBs (aka. Image and Text columns).

Also note that UNION and UNION ALL do not guarantee the order of rows. If you need the rows ordered, you need to use ORDER BY. Also keep in mind that you need to provide aliases for all computed columns in the first SELECT when using an ORDER BY.

UNION DISTINCT was defined in SQL 99. It is the same as UNION. It’s purpose is only to make it explicit where we want to get distinct results or all. But it is not supported by all DBMS yet.

Sybase devices: dsync and directio

dsync

First, dsync has no effect on raw devices (i.e., a device on a raw partition) and on devices on Windows operating system (i.e., it only affects Unix/Linux operating systems).

ASE opens a database device file of a device with the dsync setting on, using the operating system dsync flag.With this flag, when ASE writes to the device file, both the written data must be physically stored on disk before the system call returns.

This allows for a better recoverability of the written data in case of crash: If the writes are buffered by the OS and the system crashes, these writes are lost. Of course, this only handles OS level buffering. The data could still be in the disk write cache and get lost…

The drawback of dsync is that it costs performance (because the writes, even if buffered by the OS, are guaranteed to go to the disk before the operation finishes).

It should be noted that dsync doesn’t mean that there is not asynchronous I/O. It just means that when you write synchronously or check for whether the asynchronous I/O was performed, you’ll only get the response that the write is completed once the data are effectively on the physical disk.

Please also note that a cache and a buffer are different.

dsync is always on for the master device: the performance of writes there is not critical and it’s important that it can be fully recovered.

On the other hand, it is common to turn off dsync on devices of databases which do not need to be recovered like the tempdb.

directio

directio is basically a way to get a way to perform I/O on file system devices in a similar way to raw devices i.e. the OS buffer caches are bypassed and data are written directly to disk.

But directio does not guarantee that the writes will only return after all data have been stored on disk (just that data will not go into caches). But it since the OS buffer caches is bypassed, it does provide a pretty good recoverability.

directio provides better write performance than sync (especially if the device is stored on a SAN). On the other hand, dsync is faster on devices for read operations. So transaction log devices are very good candidates for directio (or for raw devices).
Also on newer Linux kernels dsync provides awful performance and you should then rather use directio than dsync.

Note that directio is not supported on HP-UX.

For the tempdb devices you should use neither dsync nor directio (as you do not need the recoverability at all).

Sybase: Change the IP address of a remote server

In order to access a remote database, you need to add a remote server and create a proxy database as shown here.

If your remote server gets a new IP address, you can of course drop the proxy database and remote server and recreate them. But I didn’t want to do that, so just update the sysservers table in the master database:

1> sp_configure 'allow updates to system tables', 1
2> go
 Parameter Name                 Default              Memory Used Config Value         Run Value            Unit                 Type
 ------------------------------ -------------------- ----------- -------------------- -------------------- -------------------- --------------------
 allow updates to system tables           0                    0            1                    1         switch               dynamic

(1 row affected)
Configuration option changed. ASE need not be rebooted since the option is dynamic.
Changing the value of 'allow updates to system tables' does not increase the amount of memory Adaptive Server uses.
(return status = 0)
1> update master..sysservers set srvnetname='192.168.230.236:2055' where srvid=4
2> go
(1 row affected)
1> sp_configure 'allow updates to system tables', 0
2> go
 Parameter Name                 Default              Memory Used Config Value         Run Value            Unit                 Type
 ------------------------------ -------------------- ----------- -------------------- -------------------- -------------------- --------------------
 allow updates to system tables           0                    0            0                    0         switch               dynamic

(1 row affected)
Configuration option changed. ASE need not be rebooted since the option is dynamic.
Changing the value of 'allow updates to system tables' does not increase the amount of memory Adaptive Server uses.
(return status = 0)

In order to update a system table like sysservers you first need to allow such changes. After modifying sysservers, you can disable such updates again as shown above.

Now testing whether I can still refresh my proxy:

1> alter database myproxydb for proxy_update
2> go
Msg 11216, Level 16, State 13:
Server 'SYBASE', Line 1:
Internal Ct-Lib/Cs-Lib error 84083972: 'ct_connect(): network packet layer: internal net library error: Net-Lib protocol driver call to connect two endpoints failed'.
Msg 11206, Level 16, State 1:
Server 'SYBASE', Line 1:
Unable to connect to server 'SOURCE'.

So I thought it was some kind of network connectivity issue, but:

# telnet 192.168.230.236 2055
Trying 192.168.230.236...
Connected to 192.168.230.236.
Escape character is '^]'.

So I can connect to the remote ASE with telnet, so must have missed something else. In the end, I never figured out why it didn’t but did find an alternative way to do this:

1> sp_addserver SOURCE, ASEnterprise, "192.168.230.236:2055"
2> go
Msg 17290, Level 16, State 1:
Server 'SYBASE', Procedure 'sp_addserver', Line 328:
There is already a server named 'SOURCE', physical name '192.168.230.236:2055'.

Ok, it doesn’t work because I’ve already updated sysservers manually, so setting it back to the way it was before the update:

1> sp_configure 'allow updates to system tables', 1
2> go
 Parameter Name                 Default              Memory Used Config Value         Run Value            Unit                 Type
 ------------------------------ -------------------- ----------- -------------------- -------------------- -------------------- --------------------
 allow updates to system tables           0                    0            1                    1         switch               dynamic

(1 row affected)
Configuration option changed. ASE need not be rebooted since the option is dynamic.
Changing the value of 'allow updates to system tables' does not increase the amount of memory Adaptive Server uses.
(return status = 0)
1> update master..sysservers set srvnetname='192.168.230.225:2055' where srvid=4
2> go
(1 row affected)
1> sp_configure 'allow updates to system tables', 0
2> go
 Parameter Name                 Default              Memory Used Config Value         Run Value            Unit                 Type
 ------------------------------ -------------------- ----------- -------------------- -------------------- -------------------- --------------------
 allow updates to system tables           0                    0            0                    0         switch               dynamic

(1 row affected)
Configuration option changed. ASE need not be rebooted since the option is dynamic.
Changing the value of 'allow updates to system tables' does not increase the amount of memory Adaptive Server uses.
(return status = 0)

And changing the IP address again using sp_addserver:

1> sp_addserver SOURCE, ASEnterprise, "192.168.230.236:2055"
2> go
Changing physical name of server 'SOURCE' from '192.168.230.225:2055' to '192.168.230.236:2055'

That’s right! sp_addserver doesn’t only add a server but can also change the physical name of a server. Now trying to refresh the proxy again:

1> alter database pdir_old for proxy_update
2> go

No error message this time ! That was easy (well, once you figure out that sp_addserver is actually sp_add_or_update_server…).

Sybase: Check for which objects you have transactions in the log

If you see that your transaction log is growing at an unexpected speed or are just curious which transactions are currently contained in the log, you can use the dbcc log operation:

dbcc traceon(3604)
go
dbcc log(mydbname, 0)
go

Replacing mydbname by the name of the database which transaction log you want to inspect. This will return quite some data. If you only want to see which objects (e.g. tables) are affected, you can use the following shell command:

cat <<EOT | isql -Usa -Pmysapassword | grep "objid=" | awk ' { print $1; }' | awk -F "=" '{ print $2; }' | sort | uniq -c | sort -n -r
dbcc traceon(3604)
go
dbcc log(mydbname, 0)
go
EOT

If isql is not in your PATH, write the full path instead (probably $SYBASE/OCS/bin/isql on a Linux box).

This will get the log data, extract the lines containing the object ID, then extract the object IDs, counting the number of time each one appears and sorting the list by decreasing number of occurences.

On my machine it returns something like this:

     10 2062679415
      6 20
      1 8

2062679415 being a test table I’ve filled for the purpose of this test.
8 is syslogs.
20 is sysanchors.

You can check what those returned object IDs are with the following statement run in your database:

1> select id, name from sysobjects where id in (8,20,2062679415)
2> go
 id          name

 ----------- --------
           8 syslogs
  2062679415 henri

Note that sysanchors (object ID 20) is not shown here because it is not a normal database table. It’s a per-database pseudo-catalog where row anchors used by system catalogs are stored.

Also keep in mind that checking the contents of the transaction log takes longer when it’s already full. So if you see that the log is filling at a constant rate, it might be a good idea to read from the log not too long after the last dump database or dump of the transaction log, so that it is faster (it will have an engine use 100% CPU for quite some time if it’s already full).

Sybase: Retrieve the text of a long trigger or stored procedure

A few months ago, I wrote a post about getting the text of a stored procedure or trigger. There was one limitation: It could only retrieve up to 16384 characters which is the limit for a varchar variable. Defining a text variable is not possible. Now I have a few triggers longer than 16384 and their text is being truncated.

So what are the options ?

  1. Storing the text in a variable is not an option.
  2. Creating a dummy table with one text row and appending the text is not option either since you’d then get an error saying you cannot add to a text column (you can only read it and store it, but not append to it).
  3. Just selecting all corresponding rows in syscomments is not an option either, since it would split lines

I ended up have a table and storing one row per line in the trigger text. Identifying the lines basically means appending the text to a varchar(16384) variable, searching for a line feed, storing the text before the line feed in the table, doing this recursively until you got all lines currently in the buffer and resuming with the rest.

Here a stored procedure doing this:

create procedure print_trigger(@trigger_name varchar(255))
as
begin
	create table tempdb..triggertext (triggertext text)

	declare trigger_text_cursor cursor
	for select c.text 
	from pdir..syscomments c, pdir..sysobjects o 
	where o.id=c.id and o.name=@trigger_name order by c.colid
	for read only

	declare @triggertext varchar(16384)
	declare @triggertext2 varchar(16384)
	declare @text varchar(255)
	declare @index int

	open trigger_text_cursor
	fetch trigger_text_cursor into @text

	while @@sqlstatus = 0
	begin
		-- Appends each line of the trigger text in syscomments to the text already gathered.
		set @triggertext=coalesce(@triggertext, '')+@text 		
		-- Loop through the lines (delimited by a line feed
		select @index=charindex(Char(10),@triggertext)
		while (@index >0)
		begin
			select @triggertext2=substring(@triggertext,1,@index-1)			
			-- Add each line to the table
			if (@triggertext2 is not null)
				insert into tempdb..triggertext (triggertext) values(@triggertext2)
			-- Continue with the rest of the string
			select @triggertext=substring(@triggertext,@index+1,16384)
			select @index=charindex(Char(10),@triggertext)
		end
		fetch trigger_text_cursor into @text
	end

	close trigger_text_cursor
	deallocate cursor trigger_text_cursor

	if (@triggertext is not null)
		insert into tempdb..triggertext (triggertext) values(@triggertext)

	select triggertext from tempdb..triggertext
	drop table tempdb..triggertext
end
go

If you want to execute this with isql and write the output to a file, you additionally do the following:

  • call isql with the -b option which disables the display of the table headers output.
  • set nocount on to suppress X rows affected messages.
  • set proc_return_status off to suppress the display of return statuses when stored procedures are called.
  • pipe the result to the following sed command to remove all those annoying trailing spaces (basically removing all spaces before an end of line):
    sed 's/[[:space:]]*$//'

Sybase: ISNULL vs. COALESCE

In Transact-SQL (as well as on T-SQL on Microsoft SQL Server), you can use both ISNULL and COALESCE to use a default in case you have NULL values:

1> select ISNULL(NULL, 123)
2> go

 -----------
         123

1> select COALESCE(NULL, 123)
2> go

 -----------
         123

So what are the differences between these two functions:

  1. COALESCE is ANSI standard. So if you think you might have to port the code to another DBMS, it’s a safer bet.
  2. ISNULL means something totally different in other DBMS e.g. MySQL. There it returns a boolean value meaning whether the expression is NULL or not. So this might confuse colleagues coming from different DBMS.
  3. COALESCE is harder to spell… After you’ve mispelled it 20 times you might consider using ISNULL instead !
  4. COALESCE can do more than ISNULL. You can provide a list of X expressions and it will return the first one which is not NULL. You can of course write something like ISNULL(expression1, ISNULL(expression2, expression3)) but it’s then much more complex to read.
  5. From a performance point of view COALESCE is converted to a case statement which seems to be a little bit slower than ISNULL which is a system function. But I guess the difference in performance is so small that it shouldn’t be a criterion to go for ISNULL instead of COALESCE.
  6. The datatype, scale and precision of the return expression is the one of the first expression for ISNULL. With COALESCE it’s more difficult to figure it out since it is determined by datatype hierarchy. You can get the hierarchy with the following query:
    select name, hierarchy from systypes order by hierarchy
  7. If you come from Microsoft SQL Server, you should also note that the following does work in SQL Server (recursively concatenating the strings) but will only return the last row in ASE:

    declare @triggertext varchar(16384)
    select @triggertext=coalesce(@triggertext, '')+text
    from syscomments c, sysobjects o 
    where o.id=c.id and o.name=@trigger_name order by c.colid

Sybase: Enabling and disabling triggers

If you want to disable a trigger, you can of course just drop it. But when you need it again, you’ll have to recreate it. If you just want to disable the trigger because you’re copying data to the table and this data has already been preprocessed and the triggered is just getting in the way, you can disable it with the alter table command:

1> alter table mytable disable trigger
2> go

This will disable all triggers on the table. If you only want to disable a single trigger, you can use the following:

1> alter table mytable disable trigger mytriggername
2> go

To enable all triggers:

1> alter table mytable enable trigger
2> go

To enable a specific trigger:

1> alter table mytable enable trigger mytriggername
2> go

If you want to check whether the triggers are enabled or disabled, you’ll have to check the sysobjects entry for this table. The column sysstat2 contains the information you’re looking for:

select 
object_name(instrig) as InsertTriggerName,
case sysstat2 & 1048576 when 0 then 'enabled' else 'disabled' end as InsertTriggerStatus,
object_name(updtrig) as UpdateTriggerName,
case sysstat2 & 4194304  when 0 then 'enabled' else 'disabled' end as UpdateTriggerStatus,
object_name(deltrig) as DeleteTriggerName,
case sysstat2 & 2097152  when 0 then 'enabled' else 'disabled' end as DeleteTriggerStatus
from sysobjects where name='mytable'

Keep in mind that the three flags used above are not documented and could by changed by SAP in the future.

To check this for all tables:

select convert(varchar(30), name) as TableName,
convert(varchar(30), isnull(object_name(instrig), '')) as InsertTriggerName,
case sysstat2 & 1048576 when 0 then 'enabled' else 'disabled' end as InsertTriggerStatus,
convert(varchar(30), isnull(object_name(updtrig), '')) as UpdateTriggerName,
case sysstat2 & 4194304  when 0 then 'enabled' else 'disabled' end as UpdateTriggerStatus,
convert(varchar(30), isnull(object_name(deltrig), '')) as DeleteTriggerName,
case sysstat2 & 2097152  when 0 then 'enabled' else 'disabled' end as DeleteTriggerStatus
from sysobjects where type='U' and (instrig<>0 or updtrig<>0 or deltrig<>0)
order by name

If you use identifiers longer than 30 bytes (which is only possible in ASE 15 and above), you can remove the convert varchar(30).