Sybase : How to check programmatically if index contains particular column?

So you’d like to check if a certain column is included in an index created on a table, that’s easy:

1> sp_helpindex authors
2> go
Object has the following indexes
 
 index_name index_keys          index_description index_max_rows_per_page index_fillfactor index_reservepagegap index_created       index_local 
 ---------- ------------------- ----------------- ----------------------- ---------------- -------------------- ------------------- ------------
 auidind     au_id              clustered, unique                       0                0                    0 Feb  3 2013  2:27PM Global Index
 aunmind     au_lname, au_fname nonclustered                            0                0                    0 Feb  3 2013  2:27PM Global Index

unless you want to do that in a programmatic way, because parsing sp_helpindex output is out of question and you’d like to perform some actions depending on the result (eg. drop & recreate the index with the missing column).

The first and most obvious idea was to check if this information is in the sysindexes table, so, let’s check:

1> sp_help_params sysindexes
2> go
 Parameter_name Type        Length Prec Scale Param_order Mode   
 -------------- ----------- ------ ---- ----- ----------- -------
 name           longsysname    255 NULL  NULL           1 unknown
 id             int              4 NULL  NULL           2 unknown
 indid          smallint         2 NULL  NULL           3 unknown
 doampg         int              4 NULL  NULL           4 unknown
 ioampg         int              4 NULL  NULL           5 unknown
 oampgtrips     int              4 NULL  NULL           6 unknown
 status3        smallint         2 NULL  NULL           7 unknown
 status2        smallint         2 NULL  NULL           8 unknown
 ipgtrips       int              4 NULL  NULL           9 unknown
 first          int              4 NULL  NULL          10 unknown
 root           int              4 NULL  NULL          11 unknown
 distribution   int              4 NULL  NULL          12 unknown
 usagecnt       smallint         2 NULL  NULL          13 unknown
 segment        smallint         2 NULL  NULL          14 unknown
 status         smallint         2 NULL  NULL          15 unknown
 maxrowsperpage smallint         2 NULL  NULL          16 unknown
 minlen         smallint         2 NULL  NULL          17 unknown
 maxlen         smallint         2 NULL  NULL          18 unknown
 maxirow        smallint         2 NULL  NULL          19 unknown
 keycnt         smallint         2 NULL  NULL          20 unknown
 keys1          varbinary      255 NULL  NULL          21 unknown
 keys2          varbinary      255 NULL  NULL          22 unknown
 soid           tinyint          1 NULL  NULL          23 unknown
 csid           tinyint          1 NULL  NULL          24 unknown
 base_partition int              4 NULL  NULL          25 unknown
 fill_factor    smallint         2 NULL  NULL          26 unknown
 res_page_gap   smallint         2 NULL  NULL          27 unknown
 exp_rowsize    smallint         2 NULL  NULL          28 unknown
 keys3          varbinary      255 NULL  NULL          29 unknown
 identitygap    int              4 NULL  NULL          30 unknown
 crdate         datetime         8 NULL  NULL          31 unknown
 partitiontype  smallint         2 NULL  NULL          32 unknown
 conditionid    int              4 NULL  NULL          33 unknown

unfortunately, there is no name of the column included in this table:

1> select name, id, object_name=object_name(id), indid, keycnt from sysindexes where id=object_id('authors')
2> go
 name     id          object_name indid  keycnt
 -------- ----------- ----------- ------ ------
 auidind    576002052 authors          1      1
 aunmind    576002052 authors          2      3

Okay, so if the information is not there, how does sp_helpindex know what to display? Let’s check by looking at the sp_helpindex source code:

1> sybsystemprocs..sp_helptext sp_helpindex
2> go
...

	/*
	**  First we'll figure out what the keys are.
	*/
	declare @i int
	declare @thiskey varchar(255)
	declare @sorder char(4)	
	declare @lastindid int

	select @keys = "", @i = 1

	set nocount on

	while @i <= 31
	begin
		select @thiskey = index_col(@objname, @indid, @i)

		if (@thiskey is NULL) 
		begin
			goto keysdone
		end

		if @i > 1
		begin
			select @keys = @keys + ", " 
		end

		select @keys = @keys + @thiskey

		/*
		** Get the sort order of the column using index_colorder()
		** This support is added for handling descending keys.
		*/
		select @sorder = index_colorder(@objname, @indid, @i)
		if (@sorder = "DESC")
			select @keys = @keys + " " + @sorder

		/*
		**  Increment @i so it will check for the next key.
		*/
		select @i = @i + 1

	end

Bingo! The answer is hidden in this line:

select @thiskey = index_col(@objname, @indid, @i)

Let’s check what the documentation says about index_col:

index_col

Description

Returns the name of the indexed column in the specified table or view.

Syntax

index_col (object_name, index_id, key_# [, user_id])
Parameters

object_name
is the name of a table or view. The name can be fully qualified (that is, it can include the database and owner name). It must be enclosed in quotes.

index_id
is the number of object_name’s index. This number is the same as the value of sysindexes.indid.

key_#
is a key in the index. This value is between 1 and sysindexes.keycnt for a clustered index and between 1 and sysindexes.keycnt+1 for a nonclustered index.

user_id
is the owner of object_name. If you do not specify user_id, it defaults to the caller’s user ID.

Examples

Example 1

Finds the names of the keys in the clustered index on table t4:

declare @keycnt integer
select @keycnt = keycnt from sysindexes 
     where id = object_id("t4")
     and indid = 1
while @keycnt > 0
begin
     select index_col("t4", 1, @keycnt)
     select @keycnt = @keycnt - 1
end

Pretty neat! But for my use case, not good enough, I don’t want to run a while loop every time I’d like to check that, so how to get rid of the loop?

Let’s make use of set based approach. I’m going to project a list of 31 integers from 1 to 31 (31 is the maximum amount of columns that a composite index can contain) that are available and waiting for you in master..spt_values table:

IF EXISTS (SELECT 1
           FROM master..spt_values
           WHERE type = 'P'
           AND   number BETWEEN 1 AND 31
           AND   index_col('authors'
                           , (SELECT indid
                              FROM sysindexes
                              WHERE id    = object_id('authors')
                              AND   name  = 'aunmind')
                           , number) = 'au_fname')
BEGIN
   PRINT 'INFO: aunmind index on authors table contains au_fname column.'
END
ELSE
BEGIN
   PRINT 'WARNING: au_fname column is not contained within aunmind on authors table.'
   EXECUTE ("DROP INDEX authors.aunmind")
   EXECUTE ("CREATE NONCLUSTERED INDEX aunmind ON authors (au_lname, au_fname)")
END

That’s it for today.

Sybase : How to disable return status when executing stored procedure?

Let’s say I want to run a stored procedure in an isql session and then send the result set via mail:

isql -S -U -P
1> set nocount on
2> exec PSTEST 'F', 7
3> go
 x y
 - -----------
 A           1
 F           7
 Z           9
(return status = 0)

but then you get this “(return status = 0) which is not that nice. So for some time I’ve been using grep -v “return status” or sed on the result file to remove the unwanted line until I’ve found out that you can actually disable printing of the return status with a nice command:

set proc_return_status off

Here’s the result set with this parameter turned off:

isql -S -U -P
1> set nocount on
2> set proc_return_status off
3> go
1> exec PSTEST 'F', 7
2> go
 x y
 - -----------
 A           1
 F           7
 Z           9

Quoting the documentation:

proc_return_status
controls sending of a return status TDS token back to the client. set proc_return_status off suppresses sending the return status token to the client, and isql client does not display the (return status = 0) message. The default for this parameter is on.

Sybase : How to count number of rows in a table – demystified

Today I’ve encountered a great forum post explaining how to count the number of rows in a table “the right way” by Derek Asirvadem.

So, it’s not:

SELECT COUNT(*) FROM mytable

it is not even:

SELECT COUNT(1) FROM mytable

can be slightly better by hinting the usage of an index:

SELECT COUNT(*) FROM mytable (INDEX column_1_index)

the right way is to do:

SELECT ROW_COUNT(DB_ID(), OBJECT_ID("mytable") [, partition_id ])

because it uses sybstats, no I/O.

I knew about it, but the documentation discouraged me from using this approach. Take a look:

sp_spaceuseddisplays estimates of the number of data pages, space used by a specified table or by all tables in the current database, and the number of rows in the tables. sp_spaceused computes the rowtotal value using the rowcnt built-in function. This function uses a value for the average number of rows per data page based on a value in the allocation pages for the object. This method is very fast, but the results are estimates, and update and insert activity change actual values. The update statistics command, dbcc checktable, and dbcc checkdb update the rows-per-page estimate, so rowtotal is most accurate after one of these commands executes. Always use select count(*) if you need exact row counts.

What I’ve found out reading the forum post, is, that if you really, really, need an exact value in current moment, you should perform these just before ROW_COUNT():

EXEC sp_flushstats [Table]
-- or
UPDATE TABLE STATISTICS [Table]

Thanks Derek!

How to trace user activity to a file in SAP Sybase ASE

Today, I’ve been checking if there are any videos on YouTube on Sybase ASE’s dbcc traceon usage and I’ve found this interesting video by Lester Leonares about a nice functionality to log user activity to a file:


(This video is a part of the SAP D&T Academy, definitely worth checking out.)

Googling further on the “set tracefile”, I’ve found a White Paper by Sybase entitled “Application Tracing in Adaptive Server Enterprise (ASE) 15” (opens pdf file in new window).

Below are some notes from the video and the white paper.

To enable tracing:

set tracefile "<file-path>" [for <spid>]

To disable tracing:

set tracefile off [for <spid>]

The “set” commands in ASE whose output can be traced under application tracing context are:

set show_sqltext <on/off>
set showplan <on/off>
set statistics io <on/off>
set statistics time <on/off>
set statistics plancost <on/off>
-- and all ‘set option’ subcommands

Obviously, as you could imagine, there are some documents in Sybase Infocenter about Finding Slow Running Queries where you can read about tracefile and see some examples. This link is an excerpt from a Sybase book on Performance and Tuning Series: Query Processing and Abstract Plans (opens a 3.9MB pdf file in new window).

Sybase APT Workbench – Text GUI RAD

I’m just curious if any of you had/have pleasure to work with Sybase’s text GUI rapid application development environment called APT Workbench.

Below are some screenshots to illustrate what I’m writing about.

First, the login window:
Sybase APT Workbench - Login screen

Creating new form:
Sybase APT Workbench - New Form

Forms menu:
Sybase APT Workbench - Forms Menu

Form attributes:
Sybase APT Workbench - Form Attributes

Fields menu:
Sybase APT Workbench - Fields Menu

Field attributes:
Sybase APT Workbench - Field Attributes

Field special attributes:
Sybase APT Workbench - Field Special Attributes

Does any of the keywords ring the bell?:

CALLFORM
CANCELFORM
POSITIONFORM
USEFORM

SUBMIT LOCAL
SQLBEGIN
   SELECT ... FROM ... WHERE ...
SQLEND

After Googling for a while I’ve found some archive of a group: sybase.public.apt-dwb-sqr and also here on Sybase Community Forums.

Since APT isn’t, for quite a long time, any more supported by Sybase, some applications needed to be converted/rewritten to other platforms. I’ve seen some code automatically converted from APT to PowerBuilder by AnyLEX (site unfortunately under construction).

If you happen to be stuck with some of your legacy code written in the APT, contact me, I can try to help.

Sybase : How to remove leading zeros from a column?

This is how I do it:

UPDATE <tablename>
SET <column> = SUBSTRING(<column>, PATINDEX('%[^0]%', <column>), CHAR_LENGTH(<column>))
WHERE <column> LIKE '0%'

Obviously, the most interesting part for me is the:

'%[^0]%'

construct that finds the position of the first non-ZERO character in the <column> to start substring from.

For detailed info about used functions, please visit Sybase’s documentation:

patindex

char_length