Sybase

Sybase Technical Support states that this is extremely dangerous as it "jacks up the value of the timestamp" which is used for recovery purposes. This may cause potential database corruption if the system fails while the timestamp rolls over.

In 4.9.2, you could only run the dbcc rebuild_log command once and after that you would have to use bcp to rebuild the database

In System 10, you can run this command about 10 times.

In System 11 I (Pablo, previous editor) tried it about 20 times and no problem.

1. use master

2. go

1. select count(*) from your_database..syslogs

2. go

-----------

some number

1. sp_configure "allow updates",1

2. go

1. reconfigure with override /* for system 10 and below only*/

2. go

1. begin tran

2. go

/* Save the following status to be used later... */

1. select saved_status=status from sysdatabases where name = "your_database"

2. go

1. update sysdatabases set status = -32768 where name = "your_database"

2. go

1. commit tran

2. go

1. shutdown

2. go

Clustered Index

A phone book lists everyone by last name. We have an A section, we have a B section and so forth. Within each section my phone book is clever enough to list the starting and ending names for the given page.

The phone book is clustered by last name.

create clustered index on phone_book (last_name)

It's fast to perform the following queries on the phone book:

* Find the address of those whose last name is Cisar.

* Find the address of those whose last name is between Even and Fa

Searches that don't work well:

* Find the address of those whose phone number is 440-1300. * Find the address of those whose prefix is 440 In order to determine the answer to the two above we'd have to search the entire phone book. We can call that a table scan.

Non-Clustered Index

To help us solve the problem above we can build a non-clustered index.

create nonclustered index on phone_book (phone_number)

Our non-clustered index will be built and maintained by our Mythical ASE as follows:

1. Create a data structure that will house a phone_number and information where the phone_number exists in the phone book: page number and the row within the page. The phone numbers will be kept in ascending order.

2. Scan the entire phone book and add an entry to our data structure above for each phone number found.

3. For each phone number found, note along side it the page number that it was located and which row it was in.

The following was written by the prior maintainer:

Sybase' first forray into 64-bit was with release 11.9.3. I do not know much about that release, but I seem to remember that it was always lagging behind its sister release of 11.9.2.

With ASE 12, Sybase have both 32-bit and 64-bit versions at the same release level. This is a big improvement, since it cuts out some concern that was prevelant with 11.9.3 as to why they were on different numbers. The releases are supposed to be identical in terms of functionality, save the fact that the 64-bit version can address more memory.

So, why not just be done with it and have just the one version? Firstly, I suppose that not everyone who can run Solaris has the capability to run the 64-bit version. There are still a lot of 32-bit Sparc chips around and a lot of people use them. It is also possible to run 32-bit Solaris on a 64-bit machine. In order to be able to run 64-bit Sybase you will have to be running 64-bit Solaris.

If you have a 64-bit environment, you still need to choose between which Sybase version to run. If you have more than 4G bytes of memory on your machine and you would like Sybase to take advantage of it, then the 64-bit version is for you. If not, then the word on the street, and from Sybase themselves, is that in identical environments, the 32-bit version runs slightly faster. I have heard a couple of explanations as to why this is so, but nothing that I find 100% convincing.

System 11

In System 11, the binding of the internal ordinal value is alleviated so that instead of using the ordinal index value, the index name can be used instead:

select ... from my_table (index my_first_index)

Sybase 4.x and Sybase System 10

All indexes have an ordinal value assigned to them. For example, the following query will return the ordinal value of all the indexes on my_table:

select name, indid

from sysindexes

where id = object_id("my_table")

Assuming that we wanted to force the usuage of index numbered three:

select ... from my_table(3)

Note: using a value of zero is equivalent to forcing a table scan. Whilst this sounds like a daft thing to do, sometimes a table scan is a better solution than heavy index scanning.

It is essential that all index hints be well documented. This is good DBA practice. It is especially true for Sybase System 10 and below.

One scheme that I have used that works quite well is to implement a table similar to sysdepends in the database that contains the index hints.

create table idxdepends

(

tblname varchar(32) not null -- Table being hinted

,depname varchar(50) not null -- Proc, trigger or app that

-- contains hint.

,idxname varchar(32) not null -- Index being hinted at

--,hintcount int null -- You may want to count the

-- number of hints per proc.

)

There are a number of different ways to achieve this, depending on what you are trying to achieve. Usually, you are trying to remove duplication of a certain key due to changes in business rules or recognition of a business rule that was not applied when the database was originally built.

Probably the quickest method is to build a copy of the original table:

select *

into temp_table

from base_table

where 1=0

Create a unique index on the columns that covers the duplicating rows with the ignore_dup_key attribute. This may be more columns that the key for the table.

create unique index temp_idx

on temp_table(col1, col2, ..., colN)

with ignore_dup_key

Now, insert base_table into temp_table.

insert temp_table

select * from base_table

First off, just to get it out of the way, Sybase does now support row level locking! (See Q6.1.11 for a description of the new features.) OK, that said and sone, if you think you need row level locking, you probably aren't thinking set based -- see Q6.1.1 for set processing.

The SQL Server uses locking in order to ensure that sanity of your queries. Without locking there is no way to ensure the integrity of your operation. Imagine a transaction that debited one account and credited another. If the transaction didn't lock out readers/writers then someone can potentially see erroneous data.

Essentially, the SQL Server attempts to use the least intrusive lock possible, page lock, to satisfy a request. If it reaches around 200 page locks, then it escalates the lock to a table lock and releases all page locks thus performing the task more efficiently.

There are three types of locks:

* page locks

* table locks

* demand locks

Any of the following will result in a stored procedure to grow when it is recompiled:

1. One of the tables used in the procedure is dropped and re-created.

2. A new rule or default is bound to one of the tables or the user runs sp_recompile on one of the tables.

3. The database containing the stored procedure is re-loaded.

Other things causing a stored procedure to be re-compiled will not cause it to grow. For example, dropping an index on one of the tables used in the procedure or doing EXEC WITH RECOMPILE.

The difference is between simple recompilation and re-resolution. Re-resolution happens when one of the tables changes in such a way that the query trees stored in sysprocedures may be invalid. The datatypes, column offsets, object ids or other parts of the tree may change. In this case, the server must re-allocate some of the query tree nodes. The old nodes are not de-allocated (there is no way to do this within a single procedure header), so the procedure grows. In time, trying to execute the stored procedure will result in a 703 error about exceeding the 64 page limit for a query.

When a stored procedure references an object that is modified after the creation of the stored procedure, the stored procedure must be re-resolved. Re-resolution is the process of verifying the location of referenced objects, including the object id number. Re-resolution will occur under the following circumstances:

1. One of the tables used by the stored procedure is dropped and re-created.

2. A rule or default is bound to one of the tables (or unbound).

3. The user runs sp_recompile on one of the tables.

4. The database the stored procedure belongs to is re-loaded.

5. The database that one of the stored procedure's tables is located in is re-loaded.

6. The database that one of the stored procedure's tables is located in is dropped and re-created.

Stored procedures are in a database as rows in sysprocedures, in the form of parse trees. They are later compiled into execution plans.

A stored procedures is compiled:

1. with the first EXECute, when the parse tree is read into cache

2. with every EXECute, if CREATE PROCEDURE included WITH RECOMPILE

3. with each EXECute specifying WITH RECOMPILE

4. if the plans in cache for the procedure are all in use by other processes

5. after a LOAD DATABASE, when all procedures in the database are recompiled

6. if a table referenced by the procedure can not be opened (using object id), when recompilation is done using the table's name 7. after a schema change in any referenced table, including:

1. CREATE INDEX or DROP INDEX to add/delete an index

2. ALTER TABLE to add a new column

3. sp_bindefault or sp_unbindefault to add/delete a default

4. sp_bindrule or sp_unbindrule to add/delete a rule

8. after EXECute sp_recompile on a referenced table, which increments sysobjects.schema and thus forces re-compilation

A netter responded stating that it was faster to ditch their algorithm and to apply a set based strategy:

The way to take your approach is to convert the row at a time processing (which is more traditional type of thinking) into a batch at a time (which is more relational type of thinking). Now I'm not trying to insult you to say that you suck or anything like that, we just need to dial you in to think in relational terms.

The idea is to do batches (or bundles) of rows rather than processing a single one at a time.

So let's take your example (since you didn't give exact values [probably out of kindness to save my eyeballs] I'll use your generic example to extend what I'm talking about):

Before:

if exists (select record) then

update record

else

insert record

New way:

1. Load all your rows into a table named new_stuff in a separate work database (call it work_db) and load it using bcp -- no third GL needed.

1. truncate new_stuff and drop all indexes

2. sort your data using UNIX sort and sort it by the clustered columns

3. load it using bcp

4. create clustered index using with sorted_data and any ancillary non-clustered index.

Open/Client 11.1.1

Create a view based on the table that you want to exclude a column from and then bcp out from the view. Open/Client Versions Older Than 11.1.1

The documentation Utility programs for Unix describes the use of format files, including the field Server Column Order. Server Column Order must equal the colid of the column, or 0 if the host file field will not be loaded into any table column.

I don't know if anyone has got this feature to work. So, here is another way of removing the column. In your example, you want to remove the last column. I am going to include another example to remove the second column and include a fourth column. Why? Because it is harder. First example will deal with removing the last column.