Sybase Prod commands..

Check Blocking session..

select spid," login " = convert(varchar(12),suser_name(suid)),cmd,
" database " = convert(varchar(12),db_name (dbid)),cpu,physical_io,memusage,blocked,hostname,time_blocked from
master..sysprocesses where blocked>0

select spid,uid,blocked,cmd,dbid,'dbname'=db_name(dbid) from master..sysprocesses where blocked > 0

check db is in logsuspend or not

select spid,uid,blocked,cmd,dbid,'dbname'=db_name(dbid) from master..sysprocesses where cmd = 'LOG SUSPEND'

Check opentrans on db..

select spid,loggedindatetime from master..sysprocesses where spid
in (select spid from master..syslogshold)

select * from master..syslogshold
sp_whodo "spid"
sp_whodo "n_cycle"

Check DB startup time

select "dbname" = convert(varchar(12),db_name(dbid)),crdate,"currentdate" =getdate()
from sysdatabases where dbid=2


Check Number of user connections on db

select suser_name(suid),count(*) from sysprocesses group by suid

Check what all sessions on db

select spid,blocked,cmd,memusage,physical_io from sysprocesses order by memusage desc
select spid,blocked,cmd,memusage,physical_io from sysprocesses order by physical_io desc
select spid,suser_name(suid),cpu,cmd,status from sysprocesses where status
like '%run%'
select spid,status,hostname,program_name,hostprocess,cmd, cpu,physical_io from sysprocesses where status like 'runn%'

Check ServerDetails

select * from sysservers

select * from syslisteners -- check port details

To get sqlplan

dbcc traceon(3604)
dbcc sqltext(379)
sp_showplan 379,null,null,null

Checkin User process details

select spid,cmd,db_name(dbid),ipaddr,uid from sysprocesses where suser_name(suid) ='drdapp'

select count(suser_name(suid)),suser_name(suid) login from sysprocesses group by suser_name(suid) order by count(suser_name(suid)) DESC

select spid,cmd,status,suser_name(suid),loggedindatetime from sysprocesses where suser_name(suid)='drdapp' and loggedindatetime <'Oct 29 2009'

select spid,suser_name(suid) from sysprocesses where suser_name(suid)='drdapp' and loggedindatetime <'Oct 28 2009'

how to kill user sessions

kill spid

select distinct 'kill ' + convert(char,blocked) from master..sysprocesses where blocked > 0

select 'kill ', spid from sysprocesses where suser_name(suid)='drdapp' and loggedindatetime <'Oct 28 2009'

select 'kill ', spid from sysprocesses where suser_name(suid)='unxadmin';

kill 325 with statusonly

Useful Commands

sp_monitor
sp_monitorconfig "all" -- all config parameter values
sp_monitorconfig 'locks'
sp_monitorconfig 'procedure cache size

sp_configure "user connections"
sp_configure 'number of user connections',600 - to increase no.of connectins
sp_configure "number of locks"
sp_configure "max_memory"
sp_configure 'auditing'

select @@errorlog
select @@boottime
select @@version
select @@servername
select getdate()


check Parameters on db

select paramname,paramvalue from d_dbdba002..task_params
select paramname,paramvalue from d_dbdba002..task_params where paramname='DUMP_GENERATIONS'
select * from d_dbdba002..task_params where paramname like '%DUMP%'

All jobs/Tasks listed on db
select * from d_dbdba002..tasks

--job configured or not
nc_task 'job_name'

To get backup details
select * from d_dbdba002..backup_files where backup_time like '%Nov%';

checking database status on sysbase cluster db

/opt/VRTSvcs/bin/hastatus -sum | grep 'dbName'

--Login details:

sp_displaylogin 'Username'
use db
sp_helprotect 'Username'
sp_helprotect Bond -- object name
sp_helpgroup 'groupname'
sp_helpuser 'Username'

how to password reset

sp_password "mypwd",newpwd,username


how to dump database or chk bkpserver working or not

dump database master to "/tmp/sud.d"

chk Stable queue Usage

admin disk_space

DB and segment details

select * from sysobjects where name='out'
sp_list U
sp_spaceused out -- 'out' is an object name
sp_help out

sp_helpdb
sp_helpdb "DBNAME"
sp_helpsegment "default"
"DBNAME"..sp_helpsegment "default"

- -To check wheteher table contains any indexes or not :

"DBNAME"..sp_helpindex "OBJECT_NAME"

---StackTrace:
select * from syslocks where spid not in (select spid from sysprocesses)

-- DBSPACE VIOLATION:

login to server isql -Uusername -Sserver_name -w300

use dbname

sp_dbspace

sp_helpdb "DBNAME"

"DBNAME"..sp_helpsegment logsegment

sp_dev_alloc

If client wants to alter or violation reaches -- do alter db on any data device

alter database on = 2000

- -THRESHOLD LOG DUMP ERROR:

use db
sp_helpsegment logsegment

select * from master..syslogshold

select db_id("dbnAME") -- to get the db_id of specifed database


dbcc checkverify(DB_NAME)

use DB_NAME

select object_name(11111)

dbcc indexalloc(tablename,indexnumber)

RepAgent chks

sp_help_rep_agent
admin who_is_down

DBCC CHECKSTORAGE ALERTS..

if it is dba database pls run below..if it is user db, Hv to investigage
dbcc checkstorage(d_dbdba002)
dbcc checkverify(d_dbdba002)

Crontab – Quick Reference

Crontab – Quick Reference
Setting up cron jobs in Unix and Solaris

cron is a unix, solaris utility that allows tasks to be automatically run in the background at regular intervals by the cron daemon. These tasks are often termed as cron jobs in unix , solaris. Crontab (CRON TABle) is a file which contains the schedule of cron entries to be run and at specified times.

This document covers following aspects of Unix cron jobs
1. Crontab Restrictions
2. Crontab Commands
3. Crontab file – syntax
4. Crontab Example
5. Crontab Environment
6. Disable Email
7. Generate log file for crontab activity

1. Crontab Restrictions
You can execute crontab if your name appears in the file /usr/lib/cron/cron.allow. If that file does not exist, you can use
crontab if your name does not appear in the file /usr/lib/cron/cron.deny.
If only cron.deny exists and is empty, all users can use crontab. If neither file exists, only the root user can use crontab. The allow/deny files consist of one user name per line.

2. Crontab Commands

export EDITOR=vi ;to specify a editor to open crontab file.

crontab -e Edit your crontab file, or create one if it doesn’t already exist.
crontab -l Display your crontab file.
crontab -r Remove your crontab file.
crontab -v Display the last time you edited your crontab file. (This option is only available on a few systems.)

3. Crontab file
Crontab syntax :
A crontab file has five fields for specifying day , date and time followed by the command to be run at that interval.

* * * * * command to be executed
- - - - -
| | | | |
| | | | +----- day of week (0 - 6) (Sunday=0)
| | | +------- month (1 - 12)
| | +--------- day of month (1 - 31)
| +----------- hour (0 - 23)
+------------- min (0 - 59)


* in the value field above means all legal values as in braces for that column.
The value column can have a * or a list of elements separated by commas. An element is either a number in the ranges shown above or two numbers in the range separated by a hyphen (meaning an inclusive range).
Notes
A. ) Repeat pattern like /2 for every 2 minutes or /10 for every 10 minutes is not supported by all operating systems. If you try to use it and crontab complains it is probably not supported.

B.) The specification of days can be made in two fields: month day and weekday. If both are specified in an entry, they are cumulative meaning both of the entries will get executed .

4. Crontab Example
A line in crontab file like below removes the tmp files from /home/someuser/tmp each day at 6:30 PM.

30 18 * * * rm /home/someuser/tmp/*

Changing the parameter values as below will cause this command to run at different time schedule below :

min hour day/month month day/week Execution time
30 0 1 1,6,12 * – 00:30 Hrs on 1st of Jan, June & Dec.

0 20 * 10 1-5 –8.00 PM every weekday (Mon-Fri) only in Oct.

0 0 1,10,15 * * – midnight on 1st ,10th & 15th of month

5,10 0 10 * 1 – At 12.05,12.10 every Monday & on 10th of every month
:

Note : If you inadvertently enter the crontab command with no argument(s), do not attempt to get out with Control-d. This removes all entries in your crontab file. Instead, exit with Control-c.

5. Crontab Environment
cron invokes the command from the user’s HOME directory with the shell, (/usr/bin/sh).
cron supplies a default environment for every shell, defining:
HOME=user’s-home-directory
LOGNAME=user’s-login-id
PATH=/usr/bin:/usr/sbin:.
SHELL=/usr/bin/sh

Users who desire to have their .profile executed must explicitly do so in the crontab entry or in a script called by the entry.

6. Disable Email
By default cron jobs sends a email to the user account executing the cronjob. If this is not needed put the following command At the end of the cron job line .

>/dev/null 2>&1

7. Generate log file
To collect the cron execution execution log in a file :

30 18 * * * rm /home/someuser/tmp/* > /home/someuser/cronlogs/clean_tmp_dir.log