How do I move tempdb off of the Master Device

1.1.3: How do I move tempdb off of the Master Device?
There used to be a section in the FAQ describing how to drop all of tempdb'sdevices physically from the master device. This can make recovery of theserver impossible in case of a serious error and so it strongly recommendedthat you do not do this but simply drop the segments as outlined below.Sybase TS Preferred Method of Moving tempdb off the Master Device.This is the Sybase TS method of removing most activity from the master device: 1. Alter tempdb on another device:
1> alter database tempdb on ...
2> go 2. Use the tempdb:
1> use tempdb
2> go 3. Drop the segments:
1> sp_dropsegment "default", tempdb, master
2> go
1> sp_dropsegment "logsegment", tempdb, master
2> go
1> sp_dropsegment "system", tempdb, master
2> go Note that there is still some activity on the master device. On a three connection test that I ran:
while ( 1 = 1 )
begin
create table #x (col_a int)
drop table #x
end
there was one write per second. Not bad. An Alternative (I recently did some bench marks comparing this method, the previous method and a combination of both. According to sp_sysmon there was no difference
in activity at all. I leave it here just in case it proves useful to
someone.) The idea of this handy script is to simply fill the first 2MB of tempdb thuseffectively blocking anyone else from using it.
The slight gotcha with thisscript, since we're using model, is that all subsequent database creates willalso have tempdb_filler installed.
This is easily remedied by dropping thetable after creating a new database.
This script works because tempdb is rebuilt every time the ASE is rebooted.
Very nice trick!/* this isql script creates a table in the model database.
*//* Since tempdb is created from the model database when the
*//* server is started, this effectively moves the active
*//* portion of tempdb off of the master device.
*/use modelgo/* note: 2k row size */create table tempdb_filler(a char(255) not null,b char(255) not null,c char(255) not null,d char(255) not null,e char(255) not null)go/* insert 1024 rows */declare @i intselect @i = 1while (@i <= 1024)begin insert into tempdb_filler values('a','b','c','d','e')
if (@i % 100 = 0) /* dump the transaction every 100 rows */
dump tran model with truncate_only
select @i=@i+1endgo

how to get the sql query behind a spid in sybase

The command you need to use to find the text of a spid say one that is blocking is dbcc sqltext.

This can be used like this

dbcc sqltext()
you can also use this command to get see whats is happening in a block you can do this by

use master
go
select * from syslogshold this will find the longest running transaction.
go
dbcc traceon(3604)
go
dbcc sqltext (104)
go
SQL Text: INSERT INTO JMA2_HstYtdSave ( Class, A_B, SUN_DB, Ent_ID, LAcct_ID, CC_ID, PJ_ID, Period, Amount, Type, Cat, LAcct, Acct_ID, CP_ID, LM_Gr, SU_CC_ID, FU_ID, CC, PE_BL_ID, Div_ID, BL_ID, BU_ID )\t\t VALUES ( 'Hst', 'A', 'DB7', '720', '800000001', '3110', ' ', 2008012, -0.050, 'H', 'FU', 'Head count Avg', '0010E', ' ', 'HC', ' ', 'CM30_105_0005', 'Money Market', 'woPE', 'IB', 'CM30', 'CM30_105' )
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

here are a list of repserver commands

Replication server Command followed by description

abort switch
Aborts the switch active command, unless Replication Server has gone too far in the active switch process to abort it.

activate subscription
For a subscription to a replication definition or a publication, starts the distribution of updates from the primary to the replicate database and sets the subscription status to ACTIVE.

add partition
Makes a partition available to Replication Server. A partition can be a disk partition or an operating system file. See create partition.

admin disk_space
Displays use of each disk partition accessed by the Replication Server.

admin echo
Returns the string entered by the user.

admin get_generation
Retrieves the generation number for a primary database.

admin health
Displays the status of the Replication Server.

admin log_name
Displays the path to the current log file.

admin logical_status
Displays status information for logical connections.

admin pid
Displays the process ID of the Replication Server.

admin quiesce_check
Determines if the queues in the Replication Server have been quiesced.

admin quiesce_force_rsi
Determines whether a Replication Server is quiescent and forces it to deliver and obtain acknowledgments for messages in RSI queues.

admin rssd_name
Displays the names of the data server and database for the RSSD.

admin security_property
Displays information about supported network-based security mechanisms and security services.

admin security_setting
Displays network-based security parameters and values for the Replication Server.

admin set_log_name
Closes the existing Replication Server log file and opens a new log file.

admin show_connections
Displays information about all connections from the Replication Server to data servers and to other Replication Servers.

admin show_function_classes
Displays the names of existing function-string classes and their parent classes, and indicates the number of levels of inheritance.

admin show_route_versions
Displays the version number of routes that originate at the Replication Server and routes that terminate at the Replication Server.

admin show_site_version
Displays the site version of the Replication Server.

admin sqm_readers
Displays the read and delete points of the threads that are reading a stable queue.

admin stats
Displays information and statistics about Replication Server counters.

admin stats, backlog
Reports the current transaction backlog in the stable queues.

admin stats, { md | mem | mem_in_use }
Reports information about memory usage.

admin stats, reset
Resets all counters that can be reset.

admin stats, status
Displays the flushing status for all counters.

admin stats, { tps | cps | bps }
Reports the number of transactions, commands, or bytes of throughput per second.

admin time
Displays the current time of Replication Server.

admin translate
Performs a datatype translation on a value, displaying the results in delimited literal format.

admin version
Displays the version number of the Replication Server software.

admin who
Displays information about threads running in the Replication Server.

admin who_is_down
Displays information about Replication Server threads that are not running.

admin who_is_up
Displays information about Replication Server threads that are running.

allow connections
Places Replication Server in recovery mode for specified databases.

alter connection
Changes the attributes of a database connection.

alter database replication definition
Changes an existing database replication definition.

alter function
Adds parameters to a user-defined function.

alter function replication definition
Changes an existing function replication definition.

alter function string
Replaces an existing function string.

alter function string class
Alters a function-string class, specifying whether it should be a base class or a derived class.

alter logical connection
Disables or enables the Distributor thread for a logical connection, changes attributes of a logical connection, and enables or disables replication of truncate table to the standby database.

alter partition
Alters the size of a partition.

alter queue
Specifies the behavior of the stable queue that encounters a large message of greater than 16K bytes. Applicable only when the Replication Server version is 12.5 or later and the site version is 12.1 or earlier.

alter replication definition
Changes an existing replication definition.

alter route
Changes the attributes of a route from the current Replication Server to a remote Replication Server.

alter user
Changes a user’s password.

assign action
Assigns Replication Server error-handling actions to data server errors received by the DSI thread.

check publication
Finds the status of a publication and the number of articles the publication contains.

check subscription
Finds the materialization status of a subscription to a replication definition or a publication.

configure connection
Changes the attributes of a database connection.

configure logical connection
Changes attributes of a logical connection.

configure replication server
Sets characteristics of the Replication Server, including network-based security.

configure route
Changes the attributes of a route from the current Replication Server to a remote Replication Server.

create article
Creates an article for a table or function replication definition and specifies the publication that is to contain the article.

create connection
Adds a database to the replication system and sets configuration parameters for the connection. To create a connection for an Adaptive Server database, use Sybase Central or rs_init.

create database replication definition
Creates a replication definition for replicating a database or a database object.

create error class
Creates an error class.

create function
Creates a user-defined function.

create function replication definition
Creates a function replication definition and user-defined function for a stored procedure that is to be replicated.

create function string
Adds a function string to a function-string class. Replication Server uses function strings to generate instructions for data servers.

create function string class
Creates a function-string class.

create logical connection
Creates a logical connection. Replication Server uses logical connections to manage warm standby applications.

create partition
Makes a partition available to Replication Server. A partition can be a disk partition or an operating system file.

create publication
Creates a publication for tables or stored procedures that are to be replicated as a group to one or more subscribing replicate databases.

create replication definition
Creates a replication definition for a table that is to be replicated.

create route
Designates the route to use for a connection from the current Replication Server to a remote Replication Server.

create subscription
Creates and initializes a subscription and materializes subscription data. The subscription may be for a database replication definition, a table replication definition, a function replication definition, or a publication.

create user
Adds a new user login name to a Replication Server.

define subscription
Adds a subscription to the Replication Server system tables, but does not materialize or activate the subscription. The subscription may be for a database replication definition, a table replication definition, a function replication definition, or for a publication. This command begins the process of bulk subscription materialization, or the process of refreshing a publication subscription.

drop article
Drops an article and optionally drops its replication definition.

drop connection
Removes a database from the replication system.

drop database replication definition
Drops an existing database replication definition.

drop error class
Drops an error class and any actions associated with it.

drop function
Drops a user-defined function and its function strings.

drop function replication definition
Drops a function replication definition and its user-defined function.

drop function string
Drops a function string for a function-string class.

drop function string class
Drops a function-string class.

drop logical connection
Drops a logical connection. Logical connections are used to manage warm standby applications.

drop partition
Removes a disk partition from the Replication Server.

drop publication
Drops a publication and all of its articles, and optionally drops the replication definitions for the articles.

drop replication definition
Drops a replication definition and its functions.

drop route
Closes the route to another Replication Server.

drop subscription
Drops a subscription to a database replication definition, table replication definition, function replication definition, article, or publication.

drop user
Drops a Replication Server user login name.

grant
Assigns permissions to users.

ignore loss
Allows Replication Server to accept messages after it detects a loss.

move primary
Changes the primary Replication Server for an error class or a function-string class.

rebuild queues
Rebuilds Replication Server stable queues.

resume connection
Resumes a suspended connection.

resume distributor
Resumes a suspended Distributor thread for a connection to a database.s

resume log transfer
Allows the RepAgent to connect to the Replication Server.

resume queue
Restarts a stable queue stopped after being passed a message larger than 16K bytes. Applicable only when the Replication Server version is 12.5 or later and the site version has not been similarly upgraded.

resume route
Resumes a suspended route.

revoke
Revokes permissions from users.

set autocorrection
Prevents failures that would otherwise be caused by missing or duplicate rows in a replicated table.

set log recovery
Specifies databases whose logs are to be recovered from offline dumps.

set proxy
Switches to another user.

shutdown
Shuts down a Replication Server.

suspend connection
Suspends a connection to a database.

suspend distributor
Suspends the Distributor thread for a connection to a primary database.

suspend log transfer
Disconnects a RepAgent from a Replication Server and prevents a RepAgent from connecting.

suspend route
Suspends a route to another Replication Server.

switch active
Changes the active database in a warm standby application.

sysadmin apply_truncate_table
Turns on or off the “subscribe to truncate table” option for all existing subscriptions to a particular table, enabling or disabling replication of truncate table.

sysadmin dropdb
Drops a database from the ID Server.

sysadmin dropldb
Drops a logical database from the ID Server.

sysadmin drop_queue
Deletes a stable queue. Use this command to drop a failed materialization queue.

sysadmin droprs
Drops a Replication Server from the ID Server.

sysadmin dump_file
Specifies an alternative log file name for use when dumping a Replication Server stable queue.

sysadmin dump_queue
Dumps the contents of a Replication Server stable queue.

sysadmin erssd
Displays ERSSD name, schedule, backup directory, and ERSSD file locations. Used with options, this command performs unscheduled backups and moves ERSSD files.

sysadmin fast_route_upgrade
Updates the route version to the site version of the lower of the primary or replicate Replication Server.

sysadmin hibernate_off
Turns off hibernation mode for the Replication Server and returns it to an active state.

sysadmin hibernate_on
Turns on hibernation mode for (or suspends) the Replication Server.

sysadmin log_first_tran
Writes the first transaction in a DSI queue into the exceptions log.

sysadmin purge_all_open
Purges all open transactions from an inbound queue of a Replication Server.

sysadmin purge_first_open
Purges the first open transaction from the inbound queue of a Replication Server.

sysadmin purge_route_at_replicate
Removes all references to a primary Replication Server from a replicate Replication Server.

sysadmin restore_dsi_saved_segments
Restores backlogged transactions.

sysadmin set_dsi_generation
Changes a database generation number in the Replication Server to prevent the application of transactions in the DSI stable queue after a replicate database is restored.

sysadmin site_version
Sets the site version number for the Replication Server. This lets you use the software features in the corresponding release, and prevents you from downgrading to an earlier release.

sysadmin sqm_purge_queue
Purges all messages from a stable queue.

sysadmin sqm_unzap_command
Undeletes a message in a stable queue.

sysadmin sqm_zap_command
Deletes a single message in a stable queue.

sysadmin sqt_dump_queue
Dumps the transaction cache for an inbound queue or a DSI queue.

sysadmin system_version
Displays or sets the system-wide version number for the replication system, allowing you to use the software features in the corresponding release level.

validate publication
Sets the status of a publication to VALID, allowing new subscriptions to be created for the publication.

validate subscription
For a subscription to a replication definition or a publication, sets the subscription status to VALID. This command is part of the bulk materialization process, or part of the process of refreshing a publication subscription.

wait for create standby
A blocking command that allows a client session in the Replication Server to wait for the standby database creation process to complete.

wait for delay
Specifies a time interval at which this command is blocked.

wait for switch
A blocking command that allows a client session in the Replication Server to wait for the switch to the new active database to complete.

wait for time
Specifies a time of day at which to unblock this command.

a little too if you dont use dbaritsan or a tool like that

This will extract your db's ddl


create procedure sp_gendbddll @dbname varchar(32) = null
as

declare @db_name varchar(30),
@dbid smallint,
@segmap smallint,
@segs varchar(4),
@dev_frag varchar(30),
@frag_size char(10),
@sql_string varchar(255),
@alter_status tinyint,
@counter int,
@seq_no int,
@numdbs int

set nocount on

if @dbname is null select @dbname = "%"

-- Match databases if given else do them all
--
select @numdbs = count(*) from master.dbo.sysdatabases where name like @dbname

-- DB exists ?
--
if @numdbs = 0
begin
/* 17590, "The specified database does not exist." */
raiserror 17590
return (1)
end

-- Initialize dbids twmp table from sysdatabases
--
select dbid into #dbids from master.dbo.sysdatabases where name like @dbname

declare @curdbid smallint /* the one we're currently working on */
declare @dbdesc varchar(102) /* the total description for the db */
declare @bitdesc varchar(30) /* the bit description for the db */

-- Set @curdbid to the first dbid.
--
select @curdbid = min(dbid) from #dbids

create table #db_info(
seq_no int not null,
sql_string varchar(255) null
)

-- Iterate through by dbid
--
while @curdbid is not NULL
begin

/************************************************
* Temporary table to hold individual fragments *
* of SQL_STRING, numbered to preserve their *
* correct sequence *
************************************************/

delete #db_info

-- Initialize variables
select @alter_status = 0
select @counter = 0
select @seq_no = 0
select @db_name = db_name(@curdbid)

/* Declare a cursor to allow processing of each row individually */
/* Select statement extracts relevant info from the system tables */
/* ORDER BY clause is critical in order to ensure that all fragments */
/* are processed in the correct sequence */

declare get_db_info cursor for
select db.dbid, usg.segmap, "device fragment"=substring(dev.name,1,15), "size(MB)" = str(usg.size/512.,10,0)
from master.dbo.sysusages usg,
master.dbo.sysdevices dev,
master.dbo.sysdatabases db
where vstart between low and high
and cntrltype = 0
and db.dbid = @curdbid
and db.dbid =usg.dbid
order by db.dbid,usg.lstart,dev.name


-- Prepare initial SQL string
select @sql_string = 'create database ' + @db_name + ' on '

-- Store initial SQL_STRING in temporary table and
-- number it to allow later extraction in the correct
-- sequence
insert into #db_info (seq_no, sql_string) values (@seq_no, @sql_string)

-- Increment the sequence number
select @seq_no = @seq_no + 1

-- Open the cursor and fetch the first row
open get_db_info

fetch get_db_info into @dbid,@segmap,@dev_frag,@frag_size

if (@@sqlstatus=2)
begin
select "No information found for database"
close get_db_info
deallocate cursor get_db_info
return
end

while (@@sqlstatus = 0)
begin

if @alter_status = 0 /* Still part of original 'create database ...' command */
begin
if @segmap = 4 /* Must refer to log device */
begin
select @sql_string = ' log on ' + @dev_frag + ' = ' +
ltrim(convert(varchar(20),@frag_size)) + char(10) + 'go' + char(10)
select @alter_status = 1
end
else /* Must refer to data device */
begin
if @counter = 0 /* 1st data device of "create database ..." */
begin
select @sql_string = ' ' + @dev_frag + ' = ' + ltrim(convert(varchar(20),@frag_size))
select @counter = @counter + 1
end
else /* Additional data device for "create database ..." */
begin
select @sql_string = ' ,' + @dev_frag + ' = ' + ltrim(convert(varchar(20),@frag_size))
end
end
end
else /* Must be an alter database command */
begin

select @sql_string = 'alter database ' + @db_name

insert into #db_info (seq_no, sql_string) values (@seq_no, @sql_string)

select @seq_no = @seq_no + 1

-- Log = 4 or data
--
if @segmap = 4
select @sql_string = ' log on ' + @dev_frag + ' = ' +
ltrim(convert(varchar(20),@frag_size)) + char(10) + 'go' + char(10)
else
select @sql_string = ' on ' + @dev_frag + ' = ' +
ltrim(convert(varchar(20),@frag_size)) + char(10) + 'go' + char(10)
end

insert into #db_info (seq_no, sql_string) values (@seq_no, @sql_string)

select @seq_no = @seq_no + 1

-- Get next row
fetch get_db_info into @dbid,@segmap,@dev_frag,@frag_size
end

close get_db_info
deallocate cursor get_db_info

-- Select each piece of SQL from temporary table ordering by */
-- sequence number */
--
declare print_createdb_cursor cursor for
select sql_string
from #db_info
order by seq_no
for read only

open print_createdb_cursor

fetch print_createdb_cursor into @sql_string

while (@@sqlstatus = 0)
begin
print @sql_string
fetch print_createdb_cursor into @sql_string
end

close print_createdb_cursor
deallocate cursor print_createdb_cursor

-- Get next dbid.
--
select @curdbid = min(dbid) from #dbids where dbid > @curdbid

end

drop table #db_info
drop table #dbids

return(0)

Fix SUIDs

In ASE you may face problems with users not being able to login, this is probably down to logind IDS and database IDS getting mismatched this often happens when a database dump is taken from one server and is loaded to another. You can solve this by either recreating the users in the same odered as they were created in the original server or drop and recreate all the IDS in the database. However there is a script that can do this for you.

sp_configure 'allow update',1
go
use database
go
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
go
sp_helpuser
go
/* if no mismatch, commit, else rollback */
commit tran
go
use master
go
sp_configure 'allow update',0
go

or create this in a store prod like this
IF OBJECT_ID('dbo.sp_dba_fixallsuid') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_dba_fixallsuid
IF OBJECT_ID('dbo.sp_dba_fixallsuid') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_dba_fixallsuid >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_dba_fixallsuid >>>'
END
go

create proc sp_dba_fixallsuid
as

declare @invalids int
declare @dbid int
declare @dbo int
declare @dbname char(100)
declare @invalid_offset int

select @dbname=db_name()
select @dbid=db_id()

set nocount on

if (@dbname not in ("master","model","sybsystemprocs","tempdb"))
begin



/* Identify the dodgy users */

select invalid_user_names_found=t.name from
sysusers t
where t.name not in (select name from master..syslogins r
where r.suid = t.suid) and t.suid > 0 and t.uid > 1


/* Get spare suid range for invalids */
select @invalid_offset = max(suid) + 10 from master..syslogins

/* Set all suids to be invalid (this prevents duplicates) */
update sysusers
set suid=suid + @invalid_offset where suid > 0 and suid < @invalid_offset and name <> 'dbo'

update sysusers
set suid=(select m.suid from master..syslogins m where sysusers.name=m.name)
where name in (select name from master..syslogins)

/* Fix the dbo in case he has changed */

select @dbo=suid from master..sysdatabases m where m.dbid=@dbid
update sysusers set suid=@dbo where name = 'dbo'


select dbo_set_to = m.name from master..syslogins m where m.suid=@dbo
select still_invalid_after_fix=name from sysusers where suid > @invalid_offset

/* Inform of the number of alaises unprocessed */

select aliases_unprocessed = count(*) from sysalternates

end
else
print "Not allowed in a system database."

return (0)

go
EXEC sp_procxmode 'dbo.sp_dba_fixallsuid','unchained'
go
IF OBJECT_ID('dbo.sp_dba_fixallsuid') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_dba_fixallsuid >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_dba_fixallsuid >>>'
go
USE sybsystemprocs
go
GRANT EXECUTE ON dbo.sp_dba_fixallsuid TO public
go

Adding raw devices to Sybase:

First you want to link the raw device you can do this by going to the device path
Do this on the primary and then make sure it is the same on the warm standby if not ask the UNIX team to create some more devices.

Example
/opt/sybase/proj/devices/servername

How to link the files example
ln -s /dev/vx/rdsk/sybdg/servername_datadev15 datadev15

then do the same on the warm standby if it is not already done.
As they need to look the same when creating the new database.

Then do a g and then SQL on both the boxes and create the devices to do this use the disk nit command.
This looks like this

disk init name = 'datadev15',
physname = '/opt/sybase/proj/devices/servername/datadev18',
size = '6144M'
go

Adding raw devices to Sybase:

First you want to link the raw device you can do this by going to the device path
Do this on the primary and then make sure it is the same on the warm standby if not ask the UNIX team to create some more devices.

Example
/opt/sybase/proj/devices/servername

How to link the files example
ln -s /dev/vx/rdsk/sybdg/servername_datadev15 datadev15

then do the same on the warm standby if it is not already done.
As they need to look the same when creating the new database.

Then do a g and then SQL on both the boxes and create the devices to do this use the disk nit command.
This looks like this

disk init name = 'datadev15',
physname = '/opt/sybase/proj/devices/servername/datadev18',
size = '6144M'
go

sybase locks and blocks

here are some basic tips on clearing a block in a database in sybase

log in to the sybase database

isql -S sybasesever -U dbo_user -P sdsdsrere123

1> sp_who
2> go

0 211 recv sleep zbi_trade_dbo zbi_trade_dbo tk0u2314ap 0 apps_gm AWAITING COMMAND 0
0 213 recv sleep read_genfeed read_genfeed ibtkygsw32 0 Clearing AWAITING COMMAND 0
0 218 sleeping dbo_swiftcentral dbo_swiftcentral tk0d2311ap 0 SwiftCentral LOG SUSPEND 0
0 224 sleeping dbo_swiftcentral dbo_swiftcentral tk0d2311ap 0 SwiftCentral LOG SUSPEND 0

you will see a log suspend or you might not you might see a block the next best thing to do is to use the master and select * from syslogshold to see if there are any long running transactions you can also to a sp_lock in the database. the transaction below is doing an insert in this case I would just truncate the log as this is a log suspend as you can see below this has cleaned up the tranlog. If that does not work then you can kill the spid

use master
go
select * from syslogshold
1> select * from syslogshold
2> go
dbid reserved spid page xactid masterxactid starttime name xloid
------ ----------- ------ ----------- -------------- -------------- -------------------------- ------------------------------------------------------------------- -----------
31516 0 9 6514 0x721900001600 0x000000000000 Jan 20 2009 9:18AM insert 18

(1 row affected)

dump tran database with truncate_only
go

1> use database
2> go
1> Dbcc checktable(syslogs)
2> go
Checking syslogs: Logical pagesize is 2048 bytes
The total number of data pages in this table is 3420.
*** NOTICE: Space used on the log segment is 3470 pages (6.78 MB), 27.11%.
*** NOTICE: Space reserved on the log segment is 0 pages (0.00 MB), 0.00%.
*** NOTICE: Space free on the log segment is 9330 pages (18.22 MB), 72.89%.
Table has 25403 data rows.
DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

if this does not work and the tranlog is still full
kill 9 where 9 is spid
this will then put the transaction in to rollback. Remember to speak to your apps team or dev team I would not just kill the spid.

Creating a table in Sybase

Now you have your database and users setup we should look at creating tables.
remember before when I said about creating objects remember to put dbo before your table name otherwise the object will be created with your user ie jagot.table.


CREATE TABLE dbo.table
(
assetManagerCode varchar(30) NOT NULL,
assetManagerName varchar(100) NULL,
assetManagerNameJpy univarchar(100) NULL,
address1 univarchar(100) NULL,
address2 univarchar(100) NULL,
attention univarchar(50) NULL,
telephoneNo varchar(20) NULL,
faxNo varchar(20) NULL,
insertedBy varchar(30) NOT NULL,
insertTimestamp datetime NOT NULL,
updatedBy varchar(30) NULL,
updatedTimestamp datetime NULL,
CONSTRAINT PKAssetManagerDetails
PRIMARY KEY NONCLUSTERED (assetManagerCode)
)
LOCK ALLPAGES
go
CREATE NONCLUSTERED INDEX IndexAssetManagerName
ON dbo.AssetManagerDetails(assetManagerName)
go

Creating a user and aliasing the user to dbo

I would not recommend logging in and out and doing all the changes with sa, so I would recommend creating and aliasing a dbo user. I would recommend being careful when creating objects with a user always specify dbo before creating tables and so on

to create a user

USE master
go
EXEC sp_addlogin 'dbo_user','PASSWORD','database','us_english',"user Dbo",null,null,null
go

USE database
go
EXEC sp_addalias 'dbo_user','dbo'
go
drop dbo_user
go
EXEC sp_addalias 'dbo_user','dbo'
go

Dumping and loading a database

Now you know how to create a database maybe you have a DR server you wish to copy the database too.
Or maybe you just wish to back the database up as this is good practise just in case you loose data or your database become corrupt.

dump database database to '/dump/dbname.dmp
go

or if your database is compressed also you might want to strip the database over a number of volumes.

Dump database database to `compress::/fox/dump/dbname/:database.dmp
Strip /dbdump/proj/dump/dbname/:database.dmp　１,
Strip /dbdump/proj/dump/dbname/:databasedmp　２,
Strip /dbdump/proj/dump/dbname/:database.dmp　3,
Strip /dbdump/proj/dump/dbname/:database.dmp　4


This goes for loading too

if you are copying this to a dr site then
scp database hostname:/dump/proj/dbname/

load database dbname from '/dump/dbname.dump
or if you have a compressed database with strips

load database database from `compress::/fox/dump/dbname/:database.dmp
Strip /dbdump/proj/dump/dbname/:database.dmp　１,
Strip /dbdump/proj/dump/dbname/:databasedmp　２,
Strip /dbdump/proj/dump/dbname/:database.dmp　3,
Strip /dbdump/proj/dump/dbname/:database.dmp　4
go
online database database
remember to online the database

Creating a database in Sybase

Sybase is a little diffrent to mssql in that you need to create sybase on devices.

In sybase you have data devices and log devices.

to create a database use this sytax.
I useually create devices called datadev and logdev as this will distiqush the too.

I would always say to set the dbowner to sa too so you know the jobs will have all the rights to the database

USE master
go
CREATE DATABASE database
ON datadev1=1000
LOG ON logdev1=250
go
ALTER DATABASE database
ON datadev2=1000
go
ALTER DATABASE database
ON datadev2=348
go
USE database
go
EXEC sp_changedbowner 'sa'
go

Intro to me and Sybase

I have been working with databases now for around 10 years. I have about 4 years experience with Sybase and about 7 years with ms products from Access to SQL server 2008.

If you would like my CV please send me a mail to Terry.Jago@gmail.com

I wont start at installing sybase as if you are looking at my site you would have already installed it for some time now I have being to think of where to start on this site.