HOME

Transact SQL: Finding duplicate rows in a table.

SOLUTION

1> select cargo_id, dest_id
2> from routing t1
3> where
4> ( select count(*)
5> from routing t2
6> where t2.dest_id = t1.dest_id ) > 1
7>
8> go

Forcing an index in a query

Sometimes it is necessary to force the index when the optimizer has incorrect statistics.

-- Example: Force the idx_emp index in this query

select t1.emp_id, t1.emp_name, t2.status
from employee t1 (index idx_emp),
audit_flags t2
where t2.emp_id = t2.emp_id
go

Renaming a database
Renaming a database requires that it be put in single-user mode first.

-- Renaming a database

use master
go

sp_dboption 'warehouse003','single user',true
go
use warehouse003
go
checkpoint
go
use master
go

sp_renamedb 'warehouse003','warehouse009'
go

sp_dboption 'warehouse009','single user',false
go
use warehouse009
go
checkpoint
go

use master
go

sp_helpdb warehouse009
go

Setting the thresholds
Threshold settings allow customized procedures to be run when database segments approach a defined capacity.

The "last chance threshold" is set by default, to execute sp_thresholdaction within thecurrent database, when a segment reaches 95% of capacity. The procedure sp_thresholdaction needs to be created by the DBA. Here is a sample:

create proc sp_thresholdaction (
@dbname varchar(30),
@segmentname varchar(30),
@space_left int,
@status int ) as

declare @msg varchar(80),
@date1 datetime,
@fname varchar(80),
@fdate varchar(20),

@fpath varchar(40)

select @fpath = '/usr/dumps/logs/'

select @date1 = getdate()

select @fdate =
convert(varchar(2),datepart(MM,@date1)) +
convert(varchar(2),datepart(DD,@date1)) +
convert(varchar(2),datepart(HH,@date1)) +
convert(varchar(2),datepart(MI,@date1))

select @fname = @fpath + 'log_' + @dbname + @fdate + '.dmp'

select @msg = '***!! Last Chance Threshold reached, for ' + @dbname + '(' + @segmentname + ')'

print @msg

if @segmentname = 'logsegment'
dump tran @dbname to @fname

return

1> sp_addthreshold dbname,logsegment,400,'proc_log_threshold'
2> go
Adding threshold for segment 'logsegment' at '400' pages.
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
(return status = 0)

SYBASE CHECKPOINT
A checkpoint process is an integral part of database logging and recovery in Sybase and indeed other database engines. The operation itself is known as "checkpoint." To elaborate on this we need to understand the logging and recovery process in Sybase.
In Sybase -- or to be more precise, in Adaptive Server Enterprise (ASE) -- the recovery process for every database is accomplished by means of the so-called transaction log. This transaction log is a database-specific system table called "syslogs." The syslogs table contains a sequential (serialized) list of all modifications to every object in that database, as well as any information required to maintain data integrity.

The database transaction log is:
Recommended to be created on a device separate from data devices
Shared by all users of that database
written first (i.e. a write-ahead log)
A crucial component in database recovery and integrity

Note that for every user, ASE allocates a "user log cache" (ULC) of 1 page size. Let us assume that our page is 2K. So ASE allocates 2048 bytes of memory for every connection. ASE uses this cache (area of memory) to buffer the user transaction log records, which reduces the contention at the end of the transaction log.

A commit by a user causes ULC to be flushed to the cache version of the log and the cache version of the log is force written to syslogs table. A checkpoint writes both the log records for all users of that database (flushes the ULC for each user of that database to the syslogs table), plus writing dirty (modified) data buffers to disk. It is essential that a checkpoint forces the flushing of log records from cache to syslogs table. This makes sense in that if we flushed the modified data buffers to disk and do not flush the log records to disk and the system crashed, we would have modified records on disk with no log information associated with them. We need to flush the log buffers before writing modified data buffers so that the database integrity from a transactional point of view is maintained.

At recovery time (after server goes down and restarted), the last checkpoint time for each database is examined. Committed transactions that have not been written to the data area after the last checkpoint are rolled forward into the data. This is called roll forward. On the other hand, uncommitted transactions are rolled back. This is called rollback.

Now, moving to checkpoint itself. As mentioned above, a checkpoint writes both the log records for all users of that database to the syslogs table, plus all the modified data buffers for that database to disk. Strictly speaking, for the server to guarantee data integrity, it needs only to make certain that the log is written to syslogs. However, if enough transactions pile up in syslogs without being recorded on the data disk as well, after server goes down, it will take ages to recover all the committed transactions. So we need the checkpoint process to get data updated while the server is running and ensure that the committed data is available to the users of the database.

A checkpoint occurs under the following conditions:

The owner of database (dbo) issues the checkpoint command
The server runs out of data buffer space and requires additional space
The server recovery interval has been exceeded
A shutdown process is initiated by the DBA which checkpoints every database
A recovery process is completed for a database
A database is loaded from a backup
There are configuration parameters in ASE which affect the recovery interval for each database and also fast recovery, concurrent recovery, etc.

The automatic checkpoint process itself is handled by the ASE's background process (as seen in sp_who) with the column heading and value: cmd, 'CHECKPOINT SLEEP' respectively. This process wakes up every minute and examines each database in turn. Based upon the amount of data accumulated in the transaction log for each database since the last checkpoint, this process determines whether it will take longer than the recovery interval (default is set to 5 minutes in ASE 12.5.3) to restore the database. If so, it issues an automatic checkpoint. Also it is worthwhile noting that if the database option "truncate log on checkpoint" is set, this process prunes the transaction log for those transactions that have been committed and are now reflected in the data.

Some user commands
To find users with sa permission

select b.name from sysloginroles a, syslogins b where a.suid=b.suid and a.srid=0

sp_addlogin , ,
sp_locklogin , unlock
sp_role "grant", sa_role,

sp_helprotect

With Sybase 12.1.X and higher, a column type can be altered. You need to be dbo and have select into turned on, in the database defaults.

create table employee (
emp_id integer not null,
salary money default 0,
hire_dt datetime default getdate(),
last_name varchar(5) null
)
go

/* make the name column longer */
alter table employee modify last_name varchar(80) not null
go

/* rename the column (works with Sybase 11 and 12) */
sp_rename 'employee.last_name',last_nm
go

OPTDIAG
---------------
To take out statistics of a table
optdiag statistics tcsdev1..SAO_SA_ORDR -o SAO.aftr -Uncsdev2 -Pxxx -SNCSDEV2

To load statistics from file to DB
optdiag statistics tcsdev1..SAO_SA_ORDR -i SAO.aftr -Uncsdev2 -Pxxx -SNCSDEV2

*For this, severs should have same value for configuration parameter 'histogram tuning factor', if stats is loaded on diff server.

For huge tables, use binary mode ie
optdiag binary statistics tcsdev1..SAO_SA_ORDR -Uncsdev2 -Pxxx -SNCSDEV2 ......

*For binary mode, the OS should be same at both sides, if stats is loaded on diff server.
=========================================================================
dbcc traceon(3604), dbcc(traceflags), dbcc traceoff(3604).
=========================================================================
1> set parallel_degree 1
2> go
1> set scan_parallel_degree 1
2> go
1> set sort_merge off
2> go
=========================================================================
We have downloaded ASE 15.0.3 binaries for Solaris and created ASE
15.0.3. The download license is un-served EE license. However, we notice
that when we do

1> sp_lmconfig "license type"
2> go

Parameter Name Config Value
----------------- ------------
license type DT

which says it is a Development license! As a result it resets the number
of CPUs to one at start-up. In our other ASE servers (15.0.2), we get
Config Value = 'SR' which is the correct value

As far as we know the binaries are correct binaries. Any idea how we can
resolve this or what has caused this issue?

1> select @@version
2> go
Solution
-------------------
If you have the EE license, then reconfigure ASE.

sp_lmconfig "edition","EE"
go
sp_lmconfig "license type","xx"
go
---- xx is whatever your license says, such as SR, CP, etc.
=========================================================================
nohup RUN_file 2>&1 &

Some Sybase Q&As
I. Query Management
Q1. How does Join Transitive Closure (JTC) differ from Search Argument (SARG) Transitive Closure?
A1. To enable systemwide merge joins, use sp_configure as follows:
sp_configure "enable sort-merge join and JTC", 1
Additionally, you can turn JTC on during a session with the set jtc on command.

Q2. Why the new 50-table limit/query?
A2. The new limit improves your ability to run queries with multiple subqueries because you do not have to break up queries as recommended for pre-12.0 servers.
However, if you increase the number of tables referenced in a query, you may need to reset the configuration parameter number of aux scan descriptors.

II. Distributed Transaction Management
Q1. What are external transactions?
A1. External transactions, introduced in Adaptive Server 12.0, are transactions that are not tightly bound to a server connection.

Q2. What is a detached transaction?
A2. A "detached transaction" is a transaction which retains any transactional locks acquired in a previous activity. You can determine that a transaction is detached by examining the output of sp_transactions. A spid of 0 indicates a detached transaction.

Q3. How does a detached transaction impact kills?
A3. As of Adaptive Server 12.0, "kill" applies only to threads. However, because detached transactions have no thread attached, a "kill" command will not abort a detached transaction. For the "kill" to take effect, you must either wait for the detached transaction to complete, or clear the detached transaction.

Q4. How do I kill a detached transaction?
A4. To abort a detached transaction, clear the block with heuristic completions. The "kill" command does not work on detached transactions because "kill" applies only to threads, and detached transactions are not attached to threads.

Q5. What are "heuristic completions?
A5. "Heuristic completions" complete or abort a detached transaction through DBA or SA intervention. You can commit, rollback, or delete the transaction through a heuristic completion in systransactions. Heuristic completions are performed using dbcc commands.
dbcc complete_xact("transactionid, "rollback|commit")

Last updated on: 15 Jul 2009

HOME