SybaseBlog

How to Solve Problems

2007-02-02T16:21:00.000-07:00

I received the following question from (presumably) a student."I had a doubt can you kindly clarify me, and if you can send me details it will really help me to understand better. Thanks for your time.Delete operation will be faster ina) All page lockingb) Data page lockingc) Data row locking"How would I find an answer to this question? First of all, I would consult Sybase documentation. It

Accepting Contributions

2006-05-30T13:58:00.000-06:00

Please send any contributions to me, and I will post them here on your behalf. Check my profile for contact information. There are limited options for Sybase professionals looking for information, and I'd be pleased to help in any way I can. Sadly I have changed jobs recently and since I don't work with Sybase as much anymore, I will be contributing very little myself.In the mean time, visit

Join Optimization

2006-01-27T13:44:00.000-07:00

Tuning queries is a required skill for a Sybase specialist. One of the many ways to tune a query is to make sure that the very best query plan is being used. One clear advantage one plan may have over another is the join order that it uses when querying several tables. This advantage can be based on such factors as the where clauses, the number of rows in the tables, how they are stored, and

Sybase and Perl

2005-12-14T14:20:00.000-07:00

Question: I've got a Perl script that collects information every night that I'd like to store in the database. What is the simplest way to do that?Answer: Well that depends which is easiest for you, there are many ways.The most pedestrian way using Perl:1. Output your SQL to a file.2. Use "system" (or back-ticks) to run a command on the shell, and within:3. Run "isql" to connect to the database

MS SQL Server vs Sybase ASE

2005-10-27T12:18:00.000-06:00

What is the difference between MS SQL Server and Sybase ASE? Those that have seen both are probably struck by how similar they are. Why are they so similar?Well, MS SQL Server gets it roots from Sybase. In fact, the first version, MS SQL 4.2, was Sybase SQL Server 4.0, and was designed for OS/2. The NT port came out a few years later. The first version of MS SQL Server that was not based on

Where 1=2

2005-09-28T17:35:00.000-06:00

Here is an SQL trick for beginners.Say you want to know the columns in a table. You can run sp_help on that table, but here is another trick:1> select * from tablename where 1=22> goThis will come back with all the columns and no matching rows (because 1=2 is always false). It will also come back very quickly because the optimizer knows there shouldn't be any matching rows.This can be

String Truncation

2005-09-20T15:45:00.000-06:00

The other day I was asked what happens when you provide a string that is too long to a table or stored procedure parameter. Rather than guess, that sounds like an easy thing to test.create table atable (stringvar varchar(5))goinsert into atable values ("too long")goselect * from atablegostringvar --------- too l So it would appear as if the string is silently truncated. Step two is to

DDL in Stored Procedures

2005-09-02T11:37:00.000-06:00

SQL is kind of a misnomer. "Structured Query Language" is used for more than just queries.Often, SQL is broken up in DDL, DCL and DML. DML, Data Manipulation Language, includes queries (SELECT) but also includes manipulation, like inserts and updates.DDL is Data Definition Language, and includes modifying the structures of tables and so on.Which brings us to the question I received. Is it

Constraints vs Rules

2005-08-25T11:11:00.000-06:00

Most organisations take data integrity very seriously. Indeed, it is Codd's 10th rule of relational data management. So the question isn't whether or not to manage the integrity of your data, but rather "how".In most situations, I advocate managing the integrity of your data at the server level, as opposed to the application or procedure level. Using the server's data integrity is generally

Compute

2005-08-24T11:07:00.000-06:00

You have a table of client contracts and you want to know the sum of the value of those contracts by customer. First let's set up our sample data.create table ClientContracts (client_name varchar(32), work_detail varchar(32), amount money)insert into ClientContracts values ("Johnson Autobody", "04AB Engine", 4000.00)insert into ClientContracts values ("Johnson Autobody", "TL7 Axel", 325.00)

Getting Help with Sybase ASE

2005-08-09T16:25:00.000-06:00

In a previous blog, I mentioned some of the key documents and tools that many Sybase ASE professionals would have available.http://sybasease.blogspot.com/2005/07/sybase-survival-pack.htmlIn this post, I am going to expand on this somewhat and talk about where you can go to find the solutions to your Sybase ASE problems.Understandably, the very first place you should look is with Sybase's

NULL is not nothing

2005-07-14T14:53:00.000-06:00

There is a big difference between NULL and nothing. I will demonstrate one notable difference with a simple example.First, create a table that allows NULLs and has a default valuecreate table test_table (col_a int default 0 null, col_b int null)goNow let's try inserting these rows:1. A row where we assign neither value:insert into test_table values ()goselect * from test_tablegocol_a col_b

Sybase Survival Pack

2005-07-07T17:07:00.000-06:00

There are a lot of great resources on Sybase, and you can see some of them among my links. But there are some utilities/documents that are so valuable that I burn them on a CD and carry a copy with me. 1. SQSHI have nothing against isql. But SQSH (SQL-Shell, developed by Scott Gray) allows you to edit previous statements (even far back in your history), use variables, among many other things.

Copying a database

2005-06-17T14:49:00.000-06:00

Warning: this is a VERY simplistic overview.Today I had to grab a database on one system and put it up on another: structure, data and all. Here are the basic steps (refer to Sybooks for more detailed information):1. On the base machine, dump the database1> use master2> go1> dump database fk6_5_latest_release to "/tmp/db_name.dmp"2> goThis will create some messages.2. Move the dump file to the

Select 8 from [Table]

2005-06-16T16:19:00.000-06:00

QUESTION:I think there's a database corruption. The table is there, and I can insert rows without error, but when I select everything from the table all I get is this! ----------- 8 8 8 8 8 8 8 8(8 rows affected)What is going on, do we have a virus?ANSWER:Sorry it took me so long to answer, but it took me awhile to

Deadlocks in Sybase ASE

2005-06-14T10:57:00.000-06:00

Normally deadlock information is written to the Sybase error log. However, that may not include enough information for your analysis. You can enable more information to be written to the error log using the "print deadlock information" parameter.1> sp_configure "print deadlock information" 12> goThis parameter should be disabled after the detailed information has been collected because this

Exclusive OR (XOR)

2005-06-13T11:27:00.000-06:00

QUESTION:Hello Techies,Can you let me know on which situation the below type of queries are used.SELECT f1.parent_id , f2.child_id FROM PortfolioTreeFlat f1 , PortfolioTreeFlat f2 WHERE f1.child_id = Pparent_id and f2.parent_id = Pchild_id ;Is this an "exclusive or"?ANSWER:- Given a child and a parent,- Give me all parents of that child,- Crossed with all children of that parentBasically,

On Vacation

2005-05-26T09:26:00.000-06:00

I start my vacation tomorrow. The next update will be June 13th. Please enjoy the 8 posts from the last 2 weeks, and comments are welcome.

Optimizing Sybase Queries and Stored Procedures

2005-05-26T09:21:00.000-06:00

This is meant for technical specialists, not DBAs, although some of the information obtained can be passed onto DBAs for server-level tuning.This is also not a step-by-step or a "how to," is it instead a collection of tools that can be used to find and resolve common performance issues.Basics:How to log your resultsisql -P password -e -i input_file -o outputfileisql -P password < input_file >

Investigating Locks

2005-05-25T16:57:00.000-06:00

I had a colleague come across a situation where an investigation of a performance issue revealed blocking in the database, but wasn't sure how to investigate further.The first step is usually to execute sp_who.1> sp_who2> goThis will list all the tasks and what they are doing. Some of them may be waiting on locks being use by another task. This task will also be in the list, and will helpfully

Sybase ASE Glossary

2005-05-24T17:23:00.000-06:00

Recently I had a client with a tricky database performance issue. So I read the Sybase Performance and Tuning Guide. It is an excellent document, but very long (1000 pages). I think it explains basically everything you need to know about performance and tuning:http://sybooks.sybase.com/onlinebooks/group-as/asg1250e/ptallbkWhile I read this guide, I made note of a number of confusing terms I

Dynamic SQL

2005-05-20T11:12:00.000-06:00

I got the following question this past week from a colleague.QUESTION:Is there a way to query a field by having it as a variable?ExampleSybase isql prompt:declare @var varchar(20)@var="param_0"select @var from MyTable where @var <> NULLANSWER:Yes, it is possible. Incidentally, this is called "Dynamic SQL." That basically means any SQL code that is generated on the fly (ie. dynamically), as

Propagating Session-Level Parameters

2005-05-19T12:27:00.000-06:00

I received a question on parallelism, and even after reviewing sections 24 and 25 of the Sybase Performance Tuning Guide, it still called for a testhttp://sybooks.sybase.com/onlinebooks/group-as/asg1250e/ptallbkI have a stored procedure that calls 20 others. I want to set the parallel degree to 1 for this stored procedure, and everything it calls. This works fine if I set it in isql before

Variables in Cursors

2005-05-18T10:10:00.000-06:00

You can use variables in cursor declarations, along these lines:1. declare a cursor, using a variable2. open that cursor, and use it3. close that cursor4. change the variable used in the declaration5. open the cursor, and use it6. close that cursor(repeat 4-6 as you wish)Here is an example.dump tran sybsystemprocs with truncate_onlygouse sybsystemprocsgo-- prepare the testsif exists (select *

Scoping in T-SQL

2005-05-17T11:34:00.000-06:00

Is there scoping in T-SQL? Actually ... no, not really.The term "scope" refers to the time or place that the value of a declared variable is accessible. Normally variables declared in a loop, for instance, are not available outside that loop.In T-SQL, a local variable is available basically anywhere in the stored procedure. There are also global variables, which endure beyond the session and can

NULLs in Sybase ASE

2005-05-16T15:37:00.000-06:00

Here is where my story begins. For potential performance reasons, I was trying to change a "where" clause that looked like this:ta.b & isnull (tb.b, ta.b) = isnull (tb.b, ta.b)to one that looked like this:ta.b & tb.b = tb.b(ta and tb are different tables)But I wasn't sure if this would work if tb.b was null. Would ta.b & null = null? So I did this test:declare @a intdeclare @b intselect @a =