This is Leonid Gvirtz's Typepad Profile.
Join Typepad and start following Leonid Gvirtz's activity
Join Now!
Already a member? Sign In
Leonid Gvirtz
Israel
Freelance consultant in database-related areas
Interests: All aspects of databases, application design
Recent Activity
Sampling for update statistics in ASE has been introduced a long time ago, in ASE version 12.5.0.3 which was released in 2003. For some reason, this very useful feature has not been advertised nor documented by Sybase very well. Actually, the whitepaper by Eric Miner still remains the most valuable... Continue reading
Posted Jan 27, 2014 at Database Diver's Diary
Hi Your comment seems to be not related to my post, but anyway: I suppose that the number of delimited values in the row is not known in advance. I don't see any easy way to solve this problem in ASE. If you want to do it generic, you may want to implement something similar to sp_lg_print_table procedure, that can be found in Downloads section on my website. The procedure allows to unpivot any result set. Since your input is not really a result set (no real columns), you will need to do some extra work to discover at least the number of delimited values ("columns") in your input. I think that the better way to solve this problem is not to try to solve it directly, any solution will be poorly scalable. Sometimes, changing of the design is the best way to go. Another possible option is to parse your delimited string on the client.
In this post I’d like to focus on precomputed result sets in SAP Sybase ASE, which are also called materialized views (MVs). Basically, an MV is a calculated result set of a query based on one or more tables. Unlike regular views, the result of the materialized view is stored... Continue reading
Posted Nov 3, 2013 at Database Diver's Diary
Since its introduction in 2011, SAP tries to push HANA very heavily and there is a lot of marketing buzz over this new product. For a freelance consultant focused on SAP Sybase database products, like me, it is next to impossible to ignore HANA in year 2013. So, I decided... Continue reading
Posted Sep 24, 2013 at Database Diver's Diary
These days, I see more and more organizations evaluating the use of Hadoop as a part of their data warehouse. Using of Hadoop in addition or a "normal" SQL-based RDBMS has a number of advantages, just to list some of them: 1. Today, for various reasons, many organizations have to... Continue reading
Posted Jan 27, 2013 at Database Diver's Diary
This is actually a continuation of the previous post. My customer has tons of stored procedure code that involve temporary tables. The temporary tables are mostly created using CREATE TABLE statement and then filled using series of INSERTs, UPDATEs and DELETEs, which cause some considerable performance issues with ASE 12.5.*... Continue reading
Posted Dec 26, 2012 at Database Diver's Diary
Unattended startup with column encryption is a relatively new ASE feature. Recently, one of my customers decided to evaluate the column encryption in ASE 15.7 and the issue of unattended startup popped up almost immediately. Normally, the password for the database master key should be provided (or "set" in Sybase... Continue reading
Posted Dec 22, 2012 at Database Diver's Diary
These days I have started learn Hadoop at last. I planed to learn this promising technology for a while and finally find a time to do it. Currently, I'm learning the Hadoop architecture and what functionality it can provide that an RDBMS can't do. As a part of learning the... Continue reading
Posted Aug 26, 2012 at Database Diver's Diary
Sybase has released ASE 15.7 ESD#2 a number of weeks ago. Not so important event at the first look. Now, I'm in the process of upgrading one of my OEM customers to ASE 15.7 and decided to check what kind of bugs have been fixed in the last EBF. To... Continue reading
Posted Aug 19, 2012 at Database Diver's Diary
One of my customers, that uses DML replication for years, recently asked me about DDL replication in Sybase Replication Server. I explained him that in order to replicate DDL, RS connects to the replicated dataserver with the login that actually performed the DDL on the primary dataserver. For this reason,... Continue reading
Posted May 2, 2012 at Database Diver's Diary
Yesterday, I decided that my CentOS VM has become just too tight for my needs and I urgently need to expand it to be able to perform some benchmarks with Sybase IQ and Oracle. Using these simple steps I have added 40 GB to the root filesystem in less than... Continue reading
Posted May 2, 2012 at Database Diver's Diary
I have just released a bug fix for the last version of my IQ DDL scripts. sp_lg_get_ddl_tables procedure for IQ 15 calls sp_iqobjectinfo to retrieve some information relevant for partitioned tables. In version 1.5 of the scripts, I tried to solve the problem with slowness of a DDL generation for... Continue reading
Posted Apr 28, 2012 at Database Diver's Diary
I have released the new version (1.5) of my IQ DDL scripts, it can be downloaded from my website: http://www.gvirtz-consulting.com/downloads. The interface of a number of stored procedures has been changed, but backward compatibility is maintained. See README file inside the zip for more details. The primary new feature is... Continue reading
Posted Mar 31, 2012 at Database Diver's Diary
In this version of my stored procedure for un-pivoting result sets in ASE I have fixed a number of issues: I tried the procedure on ASE 12.5.4 and discovered that "set nocount" is not supported inside dynamic SQL in this ASE version. So, I moved "set nocount" to static SQL.... Continue reading
Posted Jan 30, 2012 at Database Diver's Diary
In my opinion, reading result sets that contain tens of columns in isql is not so fun. The problem is, of course, that a row from the result set is presented by a number of rows on the screen. In my experience, the number of rows displayed per actual row... Continue reading
Posted Jan 21, 2012 at Database Diver's Diary
Recently, I was asked by a customer to solve somewhat unusual problem. He had a big table with records of some events related to different system users collected over time (a kind of events log). In addition, there was another, smaller table with keys from the big table, mostly related... Continue reading
Posted Jan 4, 2012 at Database Diver's Diary
Recently, I was needed to load to IQ a number of csv files extracted from Oracle. This is for a customer that is currently evaluating migration to IQ. The format of all timestamp (Oracle datatype) fields in imput files was like this: 26/04/2011 12:26:59.123 Unfortunately, IQ doesn't recognize such a... Continue reading
Posted Jul 3, 2011 at Database Diver's Diary
Recent versions of ASE and IQ has introduced some new neat features for high-view monitoring. I find these features very convenient and decided to share my experience. IQ 15.2 introduces sp_iqstatistics system stored procedure. It provides a fixed list of 31 system-wide performance statistics. Most of these statistics were previously... Continue reading
Posted Jun 2, 2011 at Database Diver's Diary
Locking mechanism is one of the most unique parts of a RDBMS implementation. Actually, all RDBMS I ever worked with implement different approach when it comes locking. Some databases have ro- level locks only, some databases employ both page-level and row-level locking, some implement snapshot isolation (writers don't block readers... Continue reading
Posted Jun 2, 2011 at Database Diver's Diary
Anyone who knows how IQ manages transactions knows how important avoid unnecessary piling up of old versions. When I started to work with IQ 15.2, I quickly discovered and nasty problem with Sybase Central - it just leaves an active transaction behind! This transaction is closed automatically after any action... Continue reading
Posted May 31, 2011 at Database Diver's Diary
I have not blogged for a while, but it is not because I had nothing to write about, actually the opposite is true. The delay was caused by a combination of personal circumstances (I was on my first ski holiday and then moved to a new flat with my girlfriend... Continue reading
Posted May 30, 2011 at Database Diver's Diary
IQ Message Log management was a bit annoying in previous IQ versions. Particularly, it was not easy to control the size of the log. Basically, all we have was IQMSG_LENGTH_MB option. When IQMSG log was reaching the specified size, IQ just started to write the file from the beginning, overwriting... Continue reading
Posted Jan 27, 2011 at Database Diver's Diary
It is generally a good idea to use the set-based approach when working with databases and not process the data row-by-row. This is especially true with a dedicated DW database like Sybase IQ. But, I have discovered that IQ teaches us not to use cursors in somewhat strange way. It... Continue reading
Posted Jan 5, 2011 at Database Diver's Diary
One of my customers has finally decided to migrate from ASE 12.5.4 to ASE 15 (currently to 15.0.3, migration to 15.5 will probably follow). I was asked to provide the list of new reserved words in ASE 15.0. To my surprise, obtaining such a list appeared to be not simple... Continue reading
Posted Dec 20, 2010 at Database Diver's Diary
It appears that recent improvements related to IQ proxy tables don't affect INSERT-SELECT and SELECT-INTO statements too much. Actually, I have found these statements hardly useful when a proxy table appears in the FROM clause of the query. Results of the benchmark talk for itself: 1> truncate table test_insert_location_IQ 2>... Continue reading
Posted Dec 14, 2010 at Database Diver's Diary