PowerBuilder Tips, Tricks, and Techniques

Berndt Hamboeck

Subscribe to Berndt Hamboeck: eMailAlertsEmail Alerts
Get Berndt Hamboeck: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn

PowerBuilder: Article

Tips, Tricks & Recipes for Sybase ASE

Tips, Tricks & Recipes for Sybase ASE

Tips, Tricks & Recipes for Sybase ASE
Author: Rob Verschoor
Publisher: Sypron Publications, The Netherlands

In contrast with Rob Verschoor's previous book, The Complete Sybase ASE Quick Reference Guide, his new book, Tips, Tricks & Recipes for Sybase ASE, is a full-size book filled with a lot information that ASE administrators and ASE developers will find useful. It provides dos and don'ts, and tips on how to keep your data intact, performant, and valid, and your ASE users happy.

The author, Rob Verschoor, is a certified DBA for ASE 12.5/12.0, and a freelance consultant with his own company (Sypron B.V.). He has more than 16 years of experience working with a variety of clients in the international software industry. Rob has written another book, The Complete Sybase ASE Quick Reference Guide, that covers ASE versions 11.9, 12.0, and 12.5.

Tips, Tricks & Recipes for Sybase ASE starts off with an historical overview of Sybase SQL Server, including the former cooperation with Microsoft and the name change to Adaptive Server Enterprise (ASE), and ends with a look at the future version of ASE 15. In Chapter 1 you'll learn different ways to figure out which ASE (EBF) version your procedures are currently running on.

The book then describes all ASE-related files, like the RUN_server file, interfaces file, or the errorlog file, and how to write and interpret error messages (you'll learn what the SPID is). You'll also find a description of the ASE console (yes, I was one of those readers who did not know what this was). Other valuable tips include how to figure out the master device pathname or where your server started from.

Chapter 3 should have come a bit later in the book as it describes the right way to terminate (kill) other users' processes (in fact, SPIDs or FIDs) or how to bring down the whole server. I like Rob's writing style; he's not afraid to discuss real-life situations, even if he's made a mistake (several years ago). It's amusing to read about how someone had tried to use the kill command several times and why this wouldn't accelerate the ending of the process. It's also very interesting to learn why a process might not go down or what happens when a client terminates itself during a long-running query.

Chapter 4 covers the handling of duplicate data. This shouldn't happen in a correct and consistent data model, but sometimes it's exactly what your business logic needs. Rob describes how to find duplicate rows and move or copy them into another table and how to merge tables. This leads into the next chapter, in which you are shown some tricks with the "ignore_dup_key" index option. There is an example that shows you how to toggle this option (very useful if you'd like to recover a corrupt table to save the time for index rebuilds, as you might see in the real-life example scenario described by the author). Another interesting part of this chapter is when you learn how the index order can affect insert performance.

If you don't have to handle duplicate data, you might have to find missing data. Rob presents different solutions, starting with the simplest way to query the difference between two tables, "not in" and "not exist" subqueries with and without null values. He describes a solution using T-SQL outer joins and ANSI outer joins (supported since 12.0) and a combined solution of these two, with a detailed description of which of the solutions is faster and why. Also interesting is the part where we want to query for missing sequential data, again with different solutions - starting with the slowest: the classic self-join - with an update with variables or by finding the data by using checksums, again with a ready-to-use full sample.

Chapters 7 and 8 deal with primary keys. First you're introduced to an overview of sequential primary keys, including the popular version of "max+1", that describes different solutions to getting it to work within a system under high load for those who don't want or can't use the fastest algorithm - an identity column. Also discussed in detail in this chapter are identity gaps and how to fix them. An interesting part of this chapter is when you learn how to include a check digit in your primary key, and how to calculate it and validate against it. Also a must read is the generation of unique keys in a replication system where the same table with identical schemas exists in different ASE servers.

If you don't need sequential keys, Chapter 8 is what you need. You'll learn how to create (pseudo) random keys and a random number, or how to create a key by using the system clock time, a globally unique identifier (GUID), or a timestamp. The chapter also deals with keys generated by client applications, for example, by using the IP address of the client.

Chapter 9 is one of the most exciting chapters for ASE developers. It includes a lot of tips and tricks using the T-SQL update statement. I liked the one where Rob displays how to concatenate all employee names (all rows from the column firstname in the employee table) into one string (update employees set @str = @str + firstname + ' '). This chapter is indeed a must read for every serious database developer. I had a lot of fun playing around with some of the provided pages; maybe Rob will add an additional chapter to this book in the future with a quiz in which readers have to figure out this stuff on their own. This would fit perfectly into this book; anyway you'll have a lot of fun and "ahas" as you work your way through this chapter.

Chapter 10 is something we, as PowerBuilder developers, know very well. It's about executing dynamic SQL statements immediately. This new 12.0 feature works in a similar fashion to the way we use it in PowerBuilder; use the execute (or exec) keyword followed by a variable or a constant string expression. You'll see that there are also limitations, for example, you can't access the inserted or deleted tables inside a trigger, but there's a lot you can do with it: update a union view, optionally select to the client or to a table, or drop and re-create an object in the same batch/procedure.

Chapter 11 tells you everything you (n)ever wanted to know about identity columns. It covers almost all topics related to identity columns, including an explanation of how other database vendors have implemented it. Every ASE administrator or developer has recognized the identity gaps (usually a side effect of a shutdown with no wait) and you might have already configured the "identity burning set factor" or since 12.0 the table-specific identity_gap setting, but did you ever use the "identity grab size" or have to reset the identity counter upward or downward, or did you need to update identity columns, maybe by setting the status bits in syscolumns.status and sysobjects.sysstat2? If this is something you want to learn, this chapter is of great value to you (and there's more).

The next chapter is all about transactions and processes. It starts by giving you hints about transactions in general (short, close them, transaction names) and moves on to a full transaction log and full tempdb and provides hints on how to find the reason for it and what to do against it (no, not necessarily increasing the database). You'll learn a lot about the sysprocesses table and how to deal with the information stored there and the dbcc command.

It's always good to know some internals of the product you use (this is why you'll always find me in the EAServer internals session at TechWave), so what would be a tips and tricks book without such a chapter? Well, here it is. It immediately dives into internals about the database timestamp and database recovery; it then describes why (the undocumented and unsupported command) dbcc rebuild_log is dangerous. It stops briefly by timestamp columns and again gives the reader an inside view on SPIDs, index IDs, or the kernel process ID. Also good to know is the (undocumented and unsupported) "set background" command that executes the client process in the background with all its features and side effects. If you need to know on which database page a particular row or index is located, don't stop reading the chapter. At the end you'll see what the global variable @@nodeid is doing; again a very interesting chapter.

Chapter 14 is the last chapter and explains things that did not fit into the other chapters, like a stored procedure sp_repeat that executes a SQL command a specified number of times or a detailed explanation on how to store or copy binary files in ASE. Another nice built-in function is syb_sendmsg in which you're able to communicate with the outside world of ASE by passing a client application. Another must read for developers is the section in which the author implements application locks with all its dangers, such as deadlocks

How to Order
Tips, Tricks & Recipes for Sybase ASE can only be ordered through the author's Web site - www.sypron.nl - using a secure credit card. Orders from all countries are accepted and information on price, discounts, shipping, etc., is there. Note that it's not possible to buy it at your local bookstore (or via Amazon).

I can fully recommend the book and for a consultant (like I am) it is indeed a must have. How often have I seen a "Select Max(keycol) + 1", and every time I've had to demonstrate why it wouldn't work in a production environment; now I have a sort of ASE bible that I can show to people who don't believe it. This kind of information is not available everywhere and that's why the book is so interesting (and this is just one of the simpler examples; you'll find a lot more in the book, believe me). Don't forget to download the source code to the book from the author's Web site after you buy the book. I wish you happy ASEing!

More Stories By Berndt Hamboeck

Berndt Hamboeck is a senior consultant for BHITCON (www.bhitcon.net). He's a CSI, SCAPC8, EASAC, SCJP2, and started his Sybase development using PB5. You can reach him under [email protected]

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.