SybaseBlog

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 database

1> use master
2> go

1> dump database fk6_5_latest_release to "/tmp/db_name.dmp"
2> go

This will create some messages.

2. Move the dump file to the target machine

In this case, the target machine had an FTP server, so I FTP'd the dump file over. Due to its size, it took awhile. After confirming it was there, I deleted it from the base machine.

3. On the target machine, make sure the backupserver is running

You can not do dump or load without the backupserver running.

4. Create or check the devices on which you will be created this database

disk init name="data1",physname="/usr/local2/data1",size="2500M"
go

disk init name="log1",physname="/usr/local2/log1",size="1250M"
go

sp_helpdevice
go

5. Create the database on the target machine

create database db_name on data1=2500 log on log1=1250
go

6. Load the dump file into this new database

load database db_name from "/usr/local2/db_name.dmp"
go

7. Bring the new database online

online database db_name
go

use db_name
go

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 stop laughing.

No, there is no problem with the database. You fell victim to the most common typo in database history.

Observe.

1> create table TableA (a int)
2> go
1> insert into TableA values (1)
2> go
(1 row affected)
(repeat 8 times)
1> select 8 from TableA
2> go

-----------
8
8
8
8
8
8
8
8

(8 rows affected)

You probably meant "select * from TableA" but you didn't press the shift key, so you got "select 8 from TableA".

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" 1
2> go

This parameter should be disabled after the detailed information has been collected because this setting degrades Sybase ASE performance.

Please check the Sybase manual for more information on this parameter, and deadlocks in general.

http://manuals.sybase.com/onlinebooks/group-as/asg1250e/ptallbk/@Generic__BookTextView/17268

On older versions of Sybase ASE you would get this deadlock information by using certain Trace Flags. Look at 1204 and 3605.

Actually handling deadlocks is a topic for another day. For a potentially quick fix, consider increasing the number of locks.

Or, reduce the locking on index pages by altering affected tables from allpages lock to datapage lock. This rebuilds all the indexes, so it may take awhile.

1> alter table [name] lock [locktype]
2> go

I will leave you with the two best links I have on deadlocks:

Peter Sap's Sybase ASE tip: Investigation of deadlocks reported in the errorlog
http://www.petersap.nl/documents/deadlock.html

Todd Boss's technical notes on deadlocks (Edited)
http://www.bossconsulting.com/sybase_dba/sublevels/
deadlock.info

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 parent

Basically, get all the children of this parent, and all the parents of this child, and then present it to me in every possible combination of the two.

create table atable (parent_id int, child_id int)
go

insert into atable (parent_id, child_id) values (1,4)
go
insert into atable (parent_id, child_id) values (1,5)
go
insert into atable (parent_id, child_id) values (2,4)
go
insert into atable (parent_id, child_id) values (3,5)
go

select a.parent_id, b.child_id
from atable a, atable b
where a.child_id = 4 and b.parent_id = 1
go

1 4 "this is us"
1 5 "this is parent's other child"
2 4 "this is child's other parent"
2 5 "this is child's parent with the parent's child"

This is not an "EXCLUSIVE OR". In an exclusive OR each row would have either a parent of the child or the child of the parent, but not both. So the first row would not show up, neither would the 3rd.

This is just an "OR". That is, give me any combination where there is a parent of the given child, OR there is a child of the given parent.

I can not really think of a business reason why this is needed.