Sybase-Replication-iq

#### correcting mismatch user id with login while migrating /refreshing database from one env. to other or

sp_configure 'allow update",1

use user_db

begin tran

update sysusers

set suid=suser_id(user_name(uid))

from sysusers

where uid between 2 and 16000

and suser_id(user_name(uid)) is not null

sp_helpuser

commit tran

use master

sp_configure 'allow update",0

Reporting: SQL Performance and Tuning

This is a list of some techniques used successfully at several different sites.
Getting Maximum Index Usage

1) Verify tables have had "update statistics" on them ;
Verify tables have had "sp_recompile" on them.

2) Verify any declared variables have the same data
type as their corresponding columns - this is a common
pitfall.

3) Force index usage as follows, with a hint:
from customer (index idx_customer2)

4) Use SET TABLE COUNT
Example: set table count 6
Then, compile the procedure, in the same session.

5) If temp tables are being used, put the temp table
creation statements in one procedure, and the
processing SQL in another procedure. This allows
the optimizer to form a query plan on the already
established tables.
Example:
proc_driver calls proc_create_temp_tables
then,
proc_driver calls proc_generate_report

General SQL Programming

- Plan for growth. Assume the driver table doubled or tripled in size; would
the report still function ?

- Avoid dumb comparisons in the where clause, like
where @emp_id > 0

- use "WHERE EXISTS ( )" rather than "WHERE NOT EXISTS"

- use "!=" rather than "<>"

- use "IS NOT NULL" rather than "<>NULL"

- use "IS NULL" rather than "=NULL"
- avoid distinct if possible ; see cursor loop option below
- use meaningful names for temp tables ... don't use #temp (lame)

Report Structure Approaches

1) Single query

Single query reports are rare - usually they involve getting a simple list
together.

- Don't try to 'shoehorn' SQL into one statement. Shorter programs are
great for C or Perl applications, but this is not the case in SQL.
Think "Bigger is Better" (and more maintainable).

- Keep queries from using more than four tables if possible.

2) Cursor on driver table(s), with IF..THEN processing in loop

Using a cursor for complex reports almost always increases performance
when large tables and a lot of joins are involved.

- Keep cursor queries from using more than two tables if possible,
make sure this query performs well on its own.

- Try to have a unique key of some sort available within the tables involved.
Strange results have been known to occur when a cursor is scanning
rows that are exactly alike.

- Don't use cursors for updating.

- Use IF statements for filtering results even further. In most cases:

A code construct like the one below is better than cramming the
logic in a where clause.

IF <cond-a>

BEGIN

IF <cond-b> and <cond-c>
.....
ELSE
....

END

3) Set processing without cursors

This technique should be attempted when even a cursor construct fails to
achieve the desired performance.

Basically, the driver query is re-run with each iteration of the loop.

Sample, with cursor:

declare cursor1 cursor for
select emp_id, last_name, salary
from employee

open cursor1

fetch cursor1 into @emp_id, @last_name, @salary

while (@@sqlstatus = 0)
begin

< processing >

fetch cursor1 into @emp_id, @last_name, @salary
end
close cursor1
Sample, with set processing:
select @emp_id = 0, @loop = 1
while (@loop > 0)
begin
set rowcount 1
select
@emp_id = emp_id,
@last_name = last_name,
@salary = salary
from employee
where emp_id > @emp_id
order by 1
select @loop = @@rowcount
set rowcount 0

if @loop > 0
begin

< processing >

end

end

Transaction Log Filling Up ?

If the transaction log is filling up, for tempdb or the main database, there
is likely something wrong with the report logic.

Things to check:

- Instead of repetitively updating each row, can the values be obtained
ahead of time, and then inserted with a single transaction ?

- Are the "joined" updates occuring on each row once ? When updating
using a join statement, make sure that the tables in question
are joined in a way that avoids duplicate rows. Try running the
SQL statement as a SELECT - check it out.

- Are you cramming 500,000 rows from a temp table into a db table ? bTry elminating the temp table.

- Create indexes on updated/inserted tables after the fact.

- Use "set rowcount" along with "waitfor delay" if log problems persist

*** A proper DBA will never extend the log segment, based on the needs of a single process.

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

Isolation Levels

Setting the isolation levels can eliminate contention problems, when reports are run on the same database as the online application.
Three flavors to choose from, for troublesome reports, queries, and updates.

1)
select cus_id
from customer_location
where cus_id< 1000000
at isolation read uncommitted

-- > Allows table to be read (ala Oracle) even when update page locks are pending.

2)
select cus_id from
customer_location noholdlock
where cus_id< 1000000

-- > Allows big queries to run without locking pages / tables.

3)
/* For updates: */

-- Session #1
begin transaction
update invoice_item
set discount_amt = 0
where invoice_id < 2000000
commit transaction

-- Session #2

set transaction isolation level 0

Queries against invoice_item in session #2 will NOT be blocked, even before the commit occurs.
Updates against invoice_item in session #2 where pages included in the Session #1 transaction WILL be blocked, before the commit occurs.

*** Mixing 1 & 2 from above is not recommended

Transact SQL: Finding duplicate rows in a table

This example finds cargo records with have duplicate destination ids.

select cargo_id, dest_id
from routing t1
where
( select count(*)
from routing t2
where t2.dest_id = t1.dest_id ) > 1
go

Using Temporary Tables

Temp tables allow developers to create and scan tables within a stored procedure - and
have the tables totally isolated from all other database connections. This is very
valuable when results need to be processed several times within a loop, or when
a complex result set is expected (like a crosstab). Note that temp table transactions
are logged within tempdb (exception: select into create statements).
create proc proc_gen_report (@region_id integer) as
declare @total money
/* standard create */

create table #rpt (
store_id integer not null,
store_cd char(5) not null,
inv_count integer not null,
total_sales money not null
)

/* create using select into - make sure 'select into' is turned on */

select t1.cus_id, t1.cus_name, sum(t2.inv_amount) 'inv_summary'
into #cus_invoices
from customer t1, invoice t2
where t2.cus_id = t1.cus_id

/* Processing occurs, using temp table(s) where needed. */
/* Temp tables can be used in joins, aggregates, updates, etc. */

drop table #rpt
drop table #cus_invoices
return
go

Safely delete a large number of rows without blowing the t-log

It is key to monitor the transaction log during large deletes.

Controlled delete, for large data sets, allows transaction log to clear.

The syntax for this operation is different for ASE 15 ... be sure to comment
out the 12.X code if you are using ASE 15.X

--This example: 9000 rows in a batch, transaction log
--needs to clear after 200mb is allocated between batches

select @@servername, db_name()
go

set nocount on
go

set rowcount 9000
go
declare @rows integer,
@trows integer,
@mb money
select @rows=1,@trows=0
while @rows > 0
begin
delete from invoice_history
select @rows = @@rowcount
select @trows = @trows + @rows
select @trows, 'tLog=', @mb
waitfor delay '00:00:02'

-- ASE 12.X
select @mb1 = ( data_pgs (8, doampg) ) * (@@maxpagesize/1000.0) / 1000
from sysindexes where id = 8

-- ASE 15.X
select @mb1 = ( ( data_pages (db_id(), 8, doampg) ) * (@@maxpagesize/1000.0) / 1000.0)
from sysindexes where id = 8

while @mb > 200
begin
waitfor delay "00:02:00"

-- ASE 12.X
select @mb1 = ( data_pgs (8, doampg) ) * (@@maxpagesize/1000.0) / 1000
from sysindexes where id = 8

-- ASE 15.X
select @mb1 = ( ( data_pages (db_id(), 8, doampg) ) * (@@maxpagesize/1000.0) / 1000.0)
from sysindexes where id = 8
select @mb "trans log MB"
end
end
go
select 'Table is now empty:', 'invoice_history', count(1) from invoice_history
go

Running SQL within a script

This script accepts a sybase command as a parameter, and executes it.

#!/usr/bin/ksh
#------------------------------------------------------
# File: sybexec
# Process Sybase command, output goes to std output
# Parameter: SQL command, in quotes
#
# Sample call: sybexec "sp_helpdb billing_db"
#------------------------------------------------------
intfile=/apps/sybase/interfaces

eval /apps/sybase/bin/isql -Sserver -I$intfile -Ujsmith -Pyankees << finis

$1
go

finis
Shared memory setting

Below are the examples for setting shared memory in Linux for Sybase.
For Solaris, you need to modify /etc/system

echo 134217728 > /proc/sys/kernel/shmmax

echo 999999999 > /proc/sys/kernel/shmmax

Clustered vs non-clustered indexes

Typically, a clustered index will be created on the primary key of a table, and non-clustered indexes are used where needed.

Non-clustered indexes

Leaves are stored in b-tree
Lower overhead on inserts, vs clustered
Best for single key queries
Last page of index can become a 'hot spot'

Clustered indexes

Records in table are sorted physically by key values
Only one clustered index per table
Higher overhead on inserts, if re-org on table is required
Best for queries requesting a range of records
Index must exist on same segment as table

Note! With "lock datapages" or "lock datarows" ... clustered indexes are sorted physically only upon creation. After that, the indexes behave like non-clustered indexes.

Adding a segment to a database

1> use dbname
2> go
1> sp_addsegment 'idx_seg1','dbname','device18'
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
Segment created.
1> use dbname
2> go
1> sp_dropsegment 'system','dbname','device18'
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
Segment reference to device dropped.
(return status = 0)
1> sp_dropsegment 'default','dbname','device18'
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
Segment reference to device dropped.
(return status = 0)

Adding a segment to a database

Hardware failures can result in databases that are corrupt and will not open
upon restart of the server. In some cases the database is marked suspect, and
then cannot be opened.
The best way to deal with a database in this state is to
nuke it and reload it from a backup. Here's a code snippet which will
force the drop to occur, when drop database fails.

/* note: X=the dbid of the database (from sysdatabases) */

use master
go
sp_configure "allow updates",1
go
begin tran
go
update sysdatabases set status = 320 where dbid = X
go

/* always make sure the status has been changed to 320 */
select dbid, status from sysdatabases where dbid = X
go
commit tran
go
sp_configure 'allow updates', 0
go
checkpoint
go

/* recycle the server */

dbcc dbrepair (database_name, dropdb)
go

/* now, recycle the server and rebuild the database */

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

Dealing with a Server Failure

There are rare instances when the server crashes down so hard that it cannot
be started again. In the synopsis that follows, the crash was due to extremely
high database activity after the transaction log filled up - making it impossbile
to clear. The server was brought down, and could not be restarted. The trick here
was to bring up the server in "non-recovery" mode, and then clear the transaction
log using some tricks from the Sybase support team.

/* Note: dbname = the database name, X = the dbid */

/* In the runserver file, add the following flags: */

-m
-T3608 (recover master and nothing else)

-or-

-T3607 (no recovery)

/* Now, recycle the server */

Then, in isql:

sp_configure 'allow updates',1
go

update sysdatabases set status=-32768
where name = 'dbname'
go

select config_admin(1,102,1,0,null,null)
go

update sysdatabases set status=0 where dbid=X
go

/* recycle again, things should be OK */

Database creation

/* create a 2 gig device */
1> disk init name = 'Data01',
2> physname = '/dev/md/rdsk/d19',
3> vdevno = 6,
4> size = 1500M
5> go
1> disk init name = 'log01',
2> physname = '/dev/md/rdsk/d20',
3> size = 500M
5> go

/* create a 1 gig database, with a 50 mb transaction log */
/* for load clause allows quick creation when dump is available */
1> create database dbname
3> on Data01 = 1500M
4> log on Log01 = 500M
6> go
CREATE DATABASE: allocating 512000 pages on disk 'device18'
CREATE DATABASE: allocating 25600 pages on disk 'device8'
/* change the database owner */
use dbname
go
1> sp_changedbowner 'jmith'
2> go
/* set up automatic log truncate, for development mode */
use master
go
sp_dboption 'dbname','trunc log on chkpt',true
go

Deferred Name Resolution

Symptom

Deferred Name Resolution turned on

Stored procedure created with dynamic sql which includes a reference to a #temp table

Error 208 is observed

Msg 208, Level 16, State 1:

Server 'myserver', Procedure 'testproc', Line 13:

#tempsysobjects not found. Specify owner.objectname or use sp_help to check

whether the object exists (sp_help may produce lots of output).

Environment

Sybase Adaptive Server Enterprise(ASE) 15.5

SAP Adaptive Server Enterprise(ASE) 15.7

SAP Adaptive Server Enterprise(ASE) 16.0

Reproducing the Issue

Configure deferred name resolution

sp_configure 'deferred name resolution',1

set deferred_name_resolution on

Create procedure with dynamic sql that creates a #temptable

create proc testproc as

declare @SQLStatement varchar(6000)

select @SQLStatement =

'select * ' +

'into ' +

'#tempsysobjects ' +

'from ' +

'master..sysobjects'

exec (@SQLStatement)

select top 1 * from #tempsysobjects

Execute Procedure

exec testproc

Turn off deferred name resolutions:

sp_configure 'deferred name resolution',0

set deferred_name_resolution off

Cause

Symptom

From source server, use ddlgen to generate DDL for the database

On target server, load the DDL that was generated from source server, run sp_depends on object and find missing stored procedure / trigger

Msg 208, Level 16, State 1:

Environment

SAP Adaptive Server Enterprise (ASE)

SAP Adaptive Server Enterprise For Business Suite

SAP Adaptive Server Enterprise Cluster Edition

Sybase Adaptive Server Enterprise (ASE)

Sybase Adaptive Server Enterprise Cluster Edition

Reproducing the Issue

Here are two different scenarios on how this may happen:

Using ddlgen:

From source server, run sp_depends on table "titles", it shows 3 stored procedures, 2 triggers, and 1 view:

isql -Usa -Ppassword -Sase_server

use pubs2

sp_depends titles

Things inside the current database that reference the object.

object type

-------------------------------------- --------------------------------

dbo.deltitle trigger

dbo.history_proc stored procedure

dbo.title_proc stored procedure

dbo.titleid_proc stored procedure

dbo.titleview view

dbo.totalsales_trig trigger

....

Run ddlgen from source server to generate DDL for this database:

ddlgen -Usa -Ppassword -Sase_server -TDB -Npubs2 -opubs2.ddl

Load the DDL into target server to generate the same in target server:

isql -Usa -Ppassword -Sase_server2 -ipubs2.ddl

On target server, run sp_depends on table "titles", it shows 3 stored procedures, 1 trigger, and 1 view; a trigger named "totalsales_trig" is missing in target server:

isql -Usa -Ppassword -Sase_server2

use pubs2

sp_depends titles

Things inside the current database that reference the object.

object type

-------------------------------- --------------------------------

dbo.deltitle trigger

dbo.history_proc stored procedure

dbo.title_proc stored procedure

dbo.titleid_proc stored procedure

dbo.titleview view

...

sysdepends loses dependency information when objects are dropped:

Create a table:

create table t1 (x int)

create procedure p1 as select * from t1

select * from sysdepends where depid = object_id("t1")

id number depid depnumber status selall resultobj readobj columns

----------- --------- ------------ --------- ------ ------ --------- ------- -------------------------------------------------------------------------------------------------

431691137 1 415691080 0 1 1 0 0 0x02

(1 row affected)

Drop the table t1:

drop table t1

create table t1 (x int)

select * from sysdepends where depid = object_id("t1")

id number depid depnumber status selall resultobj readobj columns

----------- --------- ------------ --------- ------ ------ --------- ------- -------------------------------------------------------------------------------------------------

(0 rows affected)

Cause

There is an assumption that the dependence is created in right order.

If the objects were created out of order (for example, if a trigger that uses a table is created before the table is created, or if a procedure that uses a view is created before the view is created.), no rows exist in sysdepends for dependencies.

Looking into the output when loading the DDL script to target server, the error is reported that when creating trigger "totalsales_trig", table "titles" is not found (table "titles" is not created yet):

<<<<< CREATING Trigger - "pubs2.dbo.totalsales_trig" >>>>>

Msg 208, Level 16, State 1:

Server 'sp110', Procedure 'totalsales_trig', Line 16:

titles not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).

Msg 208, Level 16, State 1:

Server 'sp110', Procedure 'totalsales_trig', Line 23:

titles not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).

Resolution

The solution is to:

Recreate the missing object(s) on target server for the ddlgen issue.

Drop and recreate the stored procedure for dropped objects.

Keywords

sp_depends sysdepends sybmigrate ddlgen

## Auto Start/Stop/Sybase server @ OS Level #######

refer this also ...

https://sap-database.blogspot.com/2017/05/startup-script-to-run-sybase-ase.html

-----------------------------------------------------working code----with root previledge-----

cd /etc/systemd/system/

vi sybase.service

[Unit]

Description=Sybase ASE Server 16.0

After=network.target

[Service]

Environment=PW="Password" DS=sybase_ASE

WorkingDirectory=/apps/sybase/server/16.04/ASE-16_0/install

Type=forking

ExecStart=/sbin/runuser sybase -c ". /apps/sybase/server/16.04/SYBASE.sh; /apps/sybase/server/16.04/ASE-16_0/bin/startserver -f RUN_${DS} -f RUN_${DS}_BS"

ExecStop= /sbin/runuser sybase -c ". /apps/sybase/server/16.04/SYBASE.sh; printf 'shutdown\ngo' | isql -Usa -P$PW -S$DS"

[Install]

WantedBy=multi-user.target

###########SQL FRAME COMMAND ################# ###################

select 'use',name, char(10) ,'go' , char(10) , 'sp_changedbowner', name ,'true' , char(10) ,'go' from sysdatabases where name not in ('sybsystemdb','sybsystemprocs','master','model','tempdb','sybsecurity') order by suid
go

################# OS level COMMAND ###############

sybase> lscpu | grep "cpu(s):"

sybase> cat /proc/cpuinfo | grep "processor" | wc -l

isybase> cat /proc/meminfo | grep "MemTotal"

MemTotal: 7878772 kB

sybase>

###############user permission issue ###################

use user_db

alter all userID.* modify owner dbo

sp_dropuser userID

sp_addalias userID,dbo

sp_helpuser userID

#### Script for DB CREATE LOAD ##### can make edit and use as needed #########

#!/bin/bash

# Sybase server details

SERVER_NAME="sybase_ASE"

#USERNAME="sa_id"

#PASSWORD="sa_pwd"

# Get parameters from user

DB_NAME=$1

#DATA_SIZE=$2

#LOG_SIZE=$3

USERNAME=$2

PASSWORD=$3

PATH_NAME=$4

# Create device names

#DEVICE_NAME="${DB_NAME}D1"

#LOG_DEVICE_NAME="${DB_NAME}L1"

# Create physical device names

#PHYSICAL_DATA_DEVICE_NAME="/apps/sybase/data05/userdata/${DEVICE_NAME}.dat"

#PHYSICAL_LOG_DEVICE_NAME="/apps/sybase/data04/userlog/${LOG_DEVICE_NAME}.dat"

# Create device command

#CREATE_DEVICE_CMD="disk init name = '${DEVICE_NAME}', physname = '${PHYSICAL_DATA_DEVICE_NAME}', size = '${DATA_SIZE}'"

#CREATE_LOG_DEVICE_CMD="disk init name = '${LOG_DEVICE_NAME}', physname = '${PHYSICAL_LOG_DEVICE_NAME}', size = '${LOG_SIZE}'"

# Create database command

#CREATE_DB_CMD="create database ${DB_NAME} on ${DEVICE_NAME} = '${DATA_SIZE}' log on ${LOG_DEVICE_NAME} = '${LOG_SIZE}'"

# load database command

LOAD_DB_CMD="load database ${DB_NAME} from '$PATH_NAME/${DB_NAME}.dat'"

# online database command

online_database=" online database ${DB_NAME}"

# Execute commands using isql

isql -U $USERNAME -P $PASSWORD -S $SERVER_NAME -w 1000 << EOF

#$CREATE_DEVICE_CMD

#go

#$CREATE_LOG_DEVICE_CMD

#go

#$CREATE_DB_CMD

#go

$LOAD_DB_CMD

$online_database

EOF

#############################################################

Display Locks on Tables

This procedure will display the names of the locked tables
(shared, update, etc)
across databases.

create procedure sp_locks
@spid1 int = NULL, /* server process id to check for locks */
@spid2 int = NULL /* other process id to c heck for locks */
as

declare @length int
declare @msg varchar(250)

if @@trancount = 0
begin
set transaction isolation level 1
set chained off
end

/* Print warning message about cursor lock info:
** 18052, "The class column will display the cursor
name for locks associated
** with a cursor for the current user and the cursor id for other
** users."
*/
exec sp_getmessage 18052, @msg out
print @msg

/*
** Show the locks for both parameters.
*/
if @spid1 is not NULL
begin
select @length = max(datalength(db_name(dbid)))
from master..syslocks
where spid in (@spid1, @spid2)

if (@length > 15)

select spid, locktype = name, table_name = object_name(id, dbid), page,
dbname = db_name(dbid), class
from master..syslocks l, master..spt_values v
where l.type = v.number
and v.type = "L"
and spid in (@spid1, @spid2)
else
select spid, locktype = name, table_name = object_name(id, dbid), page,
dbname = convert(char(15), db_name(dbid)), class
from master..syslocks l,
master..spt_values v
where l.type = v.number
and v.type = "L"
and spid in (@spid1, @spid2)
end

/*
** No paramete
rs, so show all the locks.
*/
else
begin
select @length = max(datalength(db_name(dbid)))
from master..syslocks

if (@length
> 15)
select spid, locktype = name, table_name = object_name(id, dbid), page,
dbname = db_name(dbid), class
from master..syslocks l, master..spt_values v
where l.type = v.number
and v.type = "L"
order by spid, dbname, table_name, locktype, page

else
select spid, locktype = name, table_name = object_name(id, dbid), page,
dbname = convert(char(15), db_name(dbid)), class
from master..syslocks l, master..spt_values v
where l.type = v.number
and v.type = "L"
order by spid, dbname, table_name, locktype, page
end
return (0)

Sybase ASE: Setting up user assigned custom tempdbs

Sybase ASE allows users to be assigned specific tempdb databases - this allows for better process segregation and contention reduction.

-- Create a custom temp db, and assign user jsmith to it, also application cognos

create temporary database tempdb_app
on tmp004 = "4G",
tmp005 = "4G"
log on tmp006 = "4G"
go

sp_tempdb 'bind','lg','jsmith','db','tempdb_app'
go
sp_tempdb 'bind','app','cognos','db','tempdb_app'
go

-- Show the assigments

sp_tempdb 'show'
go

How to check long running transaction?

Step 1:- Log in the dataserever using isql

isql -U<user_name> -S<server_name> -w999

Step 2:- Check the long running trans as below
If there is any row in syslogshold, see the starttime column and current time, If there is big diff, it means you have log runningg trans in dataserver and you need to esclate to APP team.

cmd:

use master goselect * from syslogsholdgoselect getdate()go

Example:

1> select * from syslogshold

2> go

dbid reserved spid page xactid masterxactid starttime name xloid
------ ----------- ------ ----------- -------------- -------------- -------------------------- ------------------------------------------------------------------- ----------- 4 0 627 43859290 0x029d3d5a0005 0x000000000000 Jun 12 2011 9:34AM $REORG REBUILD dmASSET_HIST ID=621764747 1254 2 0 314 332908 0x0005146c000a 0x000000000000 Jun 12 2011 3:02PM $ins 628
(2 rows affected)
1> select getdate()
2> go
--------------------------
Jun 12 2011 4:00PM(1 row affected)

Step 3:- Now we need to find the detail of blocking spid as below
cmd:

use master

go
select * from sysprocesses where spid=<blocking spid>
go
sp_who "<blocking spid>"
traceon(3604)
go
dbcc sqltext(<blocking spid>)
go
sp_showplan <blocing spid>,null,null,null
go
sp_lock <blocking spid>
go
example : (I have colllected the detail for 843 , you need to find out detail for all the blocking spids)

1> select * from sysprocesses where spid=843

2> go
spid kpid enginenum status suid hostname program_name hostprocess cmd cpu physical_io memusage blocked dbid uid gid tran_name time_blocked network_pktsz fid execlass priority affinity id stmtnum linenum origsuid block_xloid clientname clienthostname clientapplname sys_id ses_id loggedindatetime ipaddr ------ ----------- ----------- ------------ ----------- ---------- ---------------- ----------- ---------------- ----------- ----------- ----------- ------- ------ ----------- ----------- ---------------------------------------------------------------- ------------ ------------- ------ ------------------------------ ---------- ------------------------------ ----------- ----------- ----------- ----------- ----------- ------------------------------ ------------------------------ ------------------------------ ------ ----------- -------------------------- --------------- 843 283443444 4 lock sleep 546 nyggmgrotc RESETREP 23822 INSERT 3 0 22 627 4 405 16390 $user_transaction 15143 512 0 EC2 HIGH ANYENGINE 637764804 5 439 NULL 0 0 0 Jun 12 2011 11:24AM 10.152.115.100 (1 row affected)
1> sp_who "843"
2> go
fid spid status loginame origname hostname blk_spid dbname cmd block_xloid ------ ------ ------------ ------------------------------ ------------------------------ ---------- -------- ------------------------------ ---------------- ----------- 0 843 lock sleep lcprusr lcprusr nyggmgrotc 627 summitdb INSERT 0 (1 row affected)(return status = 0)
1> dbcc traceon(3604)
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
1> dbcc sqltext(843)
2> go
SQL Text: 320110714CONV_AVGAUTO CARRY
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.
1> sp_showplan 843,null,null,null
2> go
QUERY PLAN FOR STATEMENT 5 (at line 439).
STEP 1 The type of query is INSERT.
The update mode is direct. TO TABLE dmASSET_HIST Using I/O Size 2 Kbytes for data pages.(return status = 0)
1> sp_lock 843
2> go
The class column will display the cursor name for locks associated with a cursor for the current user and the cursor id for other
users.
fid    spid   loid        locktype                     table_id    page        row    dbname
        class                          context
------ ------ ----------- ---------------------------- ----------- ----------- ------ ---------------
        ------------------------------ ----------------------------
     0    843        1686 Ex_intent                      589764633           0      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row                         589764633      199169      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_intent                      635720842           0      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row                         635720842    22607248      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_intent                      667720956           0      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row                         667720956    46143296      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_intent                     1076770418           0      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row-blk                    1076770418    13586806      2 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row                        1076770418    13807873      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_intent                     1108770532           0      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row                        1108770532    18465042      2 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row                        1108770532    33477240      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_intent-blk                 1732772755           0      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row                        1732772755    13475329      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_intent                     1764772869           0      0 summitdb
        Non Cursor Lock
     0    843        1686 Ex_row                        1764772869    19673089      0 summitdb
        Non Cursor Lock
(16 rows affected)
(return status = 0)

Step 4:- With above detail you need to send the mail to APP Team.

Sybase-Replication-iq

Thursday, 20 March 2025

VERITAS Cluster Server (VCS) for database

Wednesday, 19 March 2025

Data cache , Procedure Cache management

Thursday, 6 March 2025

ASE Notes

Deferred Name Resolution

## Auto Start/Stop/Sybase server @ OS Level #######

About Me

Blog Archive