OLTP

USE in_memory_OLTP

GO

CREATE TABLE [dbo]. [PeopleWithSchema]

(

[PeopleID] [int] NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT= 2000000),

[Fname] [nvarchar] (50) NULL, 

[Lname] [nvarchar] (50) NULL,

[Email] [nvarchar] (50) NULL,

[Social Security Number] [nvarchar] (50) NULL,

[Phone] [nvarchar] (50) NULL

)

with (MEMORY_OPTIMIZED= ON, DURABILITY=SCHEMA_ONLY)

go

USE in_memory_OLTP

GO

CREATE TABLE [dbo].[People]

(

[PeopleID] [int] NOT NULL,

[Fname] [nvarchar] (50) NULL,

[Lname] [nvarchar] (50) NULL,

[Email] [nvarchar] (50) NULL,

[Social Security Number] [nvarchar] (50) NULL,

[Phone] [nvarchar] (50) NULL,

CONSTRAINT [PK_People] PRIMARY KEY CLUSTERED

(

[PeopleID] ASC

))

go

USE in_memory_OLTP

GO

CREATE TABLE [dbo].[PeopleWithSchemaData]

(

[PeopleID] [int] NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT= 2000000),

[Fname] [nvarchar] (50) NULL,

[Lname] [nvarchar] (50) NULL,

[Email] [nvarchar] (50) NULL,

[Social Security Number] [nvarchar] (50) NULL,

[Phone] [nvarchar] (50) NULL

)

WITH (MEMORY_OPTIMIZED =ON, DURABILITY =SCHEMA_AND_DATA)

go

multipath replication support

PRIMSERVER_RS ---> REPSERV ---> PRIMSERVER  

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

create replication definition repdef_db1_test1

with primary at PRIMSERVER.db1

with primary table named 'test1'

with replicate table named 'test1'

(id INTEGER , Fullname varchar(20))

primary key (id)

go

create subscription sub_db1_test1

for repdef_db1_test1

with replicate at REPSERV.db1

without materialization

check subscription sub_db1_test1

for repdef_db1_test1

with replicate at REPSERV.db1

go

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

create replication definition repdef_db1test2

with primary at PRIMSERVER.db1

with primary table named 'test2'

with replicate table named 'test2'

(id INTEGER , detail varchar(20))

primary key (id)

go

create subscription sub_db1test2

for repdef_db1test2

with replicate at REPSERV.db1

without materialization

check subscription sub_db1test2

for repdef_db1test2

with replicate at REPSERV.db1

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

drop subscription sub_db1test2

for database replication definition  repdef_db1test2

with primary at PRIMSERVER.db1

with replicate at REPSERV.db1

without purge

create table test1(id INTEGER , fullname varchar(10))

create table test2(id INTEGER , fullname varchar(10))

insert into test1 values(1,"SM")

insert into test1 values(2,"AM")

insert into test1 values(3,"DM")

insert into test1 values(4,"UM")

insert into test1 values(5,"PM")

1.  RS user:  PRIMSERVER_RS_ra                                                

2.  RS password:  PRIMSERVER_RS_ra_ps

sp_replication_path db1, 'config', "alternate_Rep_path","rs_username", userid

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

default(single path) replication heartbeat

@D30

exec sp_setreplicate 'rs_marker' , true

exec rs_ticket 'heartbeat'

@D28

1> use db1

2> go

1> select * from rs_ticket_history

2> go

 cnt       pdb_t                         rdb_t

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

  1       Jan 19 2010  2:17AM           Jan 19 2010  2:17AM

ticket

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

V=2;H1=profile1;H2=start;PDB(pdb)=01/19/10 02:17:19.406;

EXEC(40)=01/19/10 02:17:19.423;B(40)=1332;

DIST(26)=01/19/10 02:17:19.669;

DSI(35)=01/19/10 02:17:19.916;

DSI_T=1;DSI_C=3;RRS=SAMPLE_RS_XIEL

./rs_init

@primase

1> sp_config_rep_agent db1,'multithread rep agent' , true

2> go

 Parameter_Name        Default_Value Config_Value Run_Value

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

 multithread rep agent false         true         false    

(1 row affected)

1> sp_stop_rep_agent db1

2> go

1> sp_start_rep_agent  db1

2> go

4) Set the multiple replication paths (4) for RepAgent--->

1> sp_config_rep_agent db1,'max number replication paths','4'

2> go

 Parameter_Name               Default_Value Config_Value Run_Value

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

 max number replication paths 1             4            1        

At the primary Adaptive Server, enter:

1> sp_replication_path "db1", 'add',"alternate_Rep_path", "PRIMSERVER_RS","PRIMSERVER_RS_maint", "PRIMSERVER_RS_Maint_ps"

2> go

@status1:

            

 -----------

           0

Create an alternate replication path from the primary database to Replication Server---->

create alternate connection to PRIMSERVER.db1

named PRIMSERVER.alternate_Rep_path

set error class to rs_sqlserver_error_class

set function string class to rs_sqlserver_function_class

set username to db1_maint

set password to db1_Maint_ps

with primary only

go

create alternate connection to REPSERV.db1

named REPSERV.alternate_Rep_path

go

Alternate connection RepSer.alternate_Rep_path' to database

RepSer.db1' is created.

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

 sp_replication_path db1 ,'bind',"table","test2","alternate_Rep_path"

 go

@status1:

            

sp_config_rep_agent db1, 'enable', 'PRIMSERVER_RS',  'PRIMSERVER_RS_maint', 'PRIMSERVER_RS_maint_ps

How to clear data from cache memory ?

0)     

data cache
as of 15.0.3 - dbcc cachedataremove (dbid | dbname , objid | objname , partitionid | partitionname , indid | indexname)
you need sa_role for that as wellpre-15.0.2 ESD6 - try sp_unbindcache_all 'default data cache'
for example to clear "default data cache".  For named data caches, you should do this, followed by a sp_bindcache again for each object bound to a named cache

statement cache
set statement_cache off    -- session level
<your sql statements here>
or
sp_configure 'statement cache size',0  -- server level

procedure cache
In 12.5.4 ESD5 and 15.0.2 :   dbcc proc_cache (free_unused)pre 12.5.4 ESD5     :    dbcc proc_cacherm(type, dbname, objname)

where: type is V,P,T,R,D,C,F, or S (must be uppercase) corresponds to View, Proc, Trigger, Rule, Default, Cursor, SQLJ Function, SQL function.

Top Ten ASE MDA Tables

Some mon table  which help in finding the performance of server or query currently running.

1)   monProcessObject

Description

Provides statistical information regarding objects currently being accessed by processes.

2) monProcessStatement

Description

Provides information about the statement currently executing.

3) monProcess

Description

Provides detailed statistics about processes that are currently executing or waiting.

4) monProcessWaits

Description

Provides a list of all wait events for which current processes on the server are waiting. Returns only wait events whose Waits value is greater than zero.

5) monTableColumns

Description

Describes all the columns for each monitoring table. monTableColumns helps determine what columns are in the monitoring tables. You can join monTableColumns with monTables to report columns and column attributes for the monitoring tables.

6) monTables

Description

Provides a description of all monitoring tables. You can join monTables with monTableColumns for a description of each monitoring table and the columns it contains

Top Ten ASE MDA Tables

• monEngine

•monDataCache & monCachePool

•monCachedObject

•monProcedureCacheMemoryUsage & monProcedureCacheModuleUsage

•MonCachedProcedures

•monDeviceIO & monIOQueue

•monProcessActivity

•monSysWaits

•monOpenObjectActivity

•monSysStatement

For example monErrorLog is likely polled frequently

monCachedObject…which admittedly is fairly handy

monDeviceIO/monIOQueue frequently used for diagnosing device waits….used more frequently than…

How can we check for the long running trans?

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.

Steps upgrading (ASE's) from 12.5.x to 15.0.2 ESD3

1)  Install ASE 15.0.2 into it's own directory (ie, $SYBASE will be different for the 12.5.x and 15.0.x software) run the preupgrade binary to find any easy-to-fix issues (eg, missing/bad SQL text for compiled objects,

 configuration settings that are too low, system db's that are too small, etc.); fix the issues and re-run the

preupgrade binary again;continue this process until the preupgrade binary reports no errors; this

preupgrade work can be performed prior to the actual day that you perform the real upgrade

2)  ASE 15.x optimizer seems to have problems if it doesn't have up-to-date histogram stats for all

columns in an index; running 'update index statistics' may require a good bit of tempdb space (depends

on number of records and datatype of non-leading columns); for this client we've setup a separate user-

created tempdb and bound the 'sa' login to said user-created tempdb (objective being to make sure the

login which will run the update stats command will use the new tempdb, while not causing any problems

for users of other tempdb's)            

3) lock all non-sa_role logins and bounce the server; objective being to make sure we can take a clean

set of backups without having to worry about any ongoing DML activity; also makes life easier if we don't

have no schtinkin' users in the dataserver during the upgrade; obviously we also shutdown any DBA/sa

monitoring/maintenance scripts .

4) make 12.5.x backups of the master database and any important user databases and  update the resource file with environment variable OLD SYBASE_OCS & SYBASE_ASE

5)  perform the upgrade in place; since most of our steps are done at the command line and/or via shell

scripts, we use sqlupgraderes and a resource file to perform the actual update; I usually have a second

window open where I can tail the various output log files .

6)  once the upgrade has been performed we run a set of 'update index statistics'; for some of the really

large systems where 'update index statistics' takes too long we're using a process that runs equivalent

'update statistics' in parallel - note: this requires much more tempdb space and cpu cycles, but can

greatly reduce the overall time to update the stats on all indexed columns (eg, a 4.8 billion row table

would have taken several days to run 'update indexstats' while the comparable 'update stats' running in

parallel took about 20 hours); note - client is using 20 percent sampling .

7)  make 15.0.2 backups of the master database and any important user databases

8)  unlock all non-sa_role logins; obviously re-enable DBA/sa monitoring/maintenance scripts

Additional steps are taken for any db's that participate in replication (ie, as a primary or replicate

database). Additional steps are taken if the ASE happens to contain the RSSD for a repserver (eg, the

repserver is shutdown during the actual upgrade of the ASE).

9) command use :-

The utility is located in $SYBASE/$SYBASE_OCS 

sqlupgraderes [-r resource file]

Gotcha /Issues with the actual upgrade process:

1 - one time we had a problem with the preupgrade program generating a stack trace and dying due to a

problem with sysprocedures; work around was to run the preupgrade process with the -X flag to perform

upgrades of all but procedures; then we used a separate script to perform the necessary dbcc upgrade

commands for individual procedures; we only had this problem once and then only during the run of the

preupgrade program ... couldn't get the problem to occur again during the run of the associated

upgraderes step ...

2 - occassionally a database will fail the upgrade process due to a duplicate key error in sysroles; when

this happens the upgrade of said database is skipped; the fix consists of basically rebuilding sysroles;

afterwards the database can be upgraded by a) 'online database' or b) during recovery at the next ASE

reboot or c) by running the upgrade(res) program again; it's also a good idea to run the upgrade(res)

program again because this 'error' may actually cause some key upgrade steps to be missed;

 [I don't have the steps in front of me but could locate/post them if you happen to run into this situation]

For the most part we've found the upgrade-in-place to work pretty well ... given that we also have a few

documented work arounds for common (pre)upgrade issues that we've run into.

 3- The following workaround has shown to be effective (so far) and should be
 used in place of the previous invocation of the 'preupgrade' binary.

 run the preupgrade process for everthing but compiled objects

$SYBASE/ASE-15_0/upgrade/preupgrade -S$S -U$U -P$P \
-X cache,config,data_mods,datatype,db_size,free_space,object_id,required_dbs,srvclass,statistics

 to process the preupgrade checks for compiled objects it is necessary to run
 the following for every object in sysprocedures:

 dbcc upgrade_object(<db_name>,<object_name>,'check')

 If you know all objects are owned by 'dbo' then pulling a list of
 distinct object_name(id)'s from sysprocedures should suffice.  Alternatively,
 perform the necessary join to sysobjects to grab the owner (ie, user_name(uid)).

 If any error messages are generated then the typical solution is to drop
 and recreate the stored proc in question.

================================

4- Not enough space in the sybsecurity database, ie, preupgrade suggests adding
space to the sybsecurity database, but every time you add space to
sybsecurity the preupgrade program says you have to add more space.

Depending on which auditing options you have enabled, and the size of
your dataserver (eg, total number of objects), the preupgrade program
could be generating a large volume of audited activities.

Run your 'normal' process to clear the sysaudits tables (eg, a process
that dumps the contents of sysaudits to disk and then truncs the table),
and then try running the preupgrade program again.

The objective here is to make sure the preupgrade program is not filling
up your sybsecurity database thus forcing you to add more space to the
sybsecurity database.

Alternatives would include disabling all or some of the auditing (eg,
disable auditing for login performing the preupgrade tests) during the
preupgrade.

================================

5- If the upgrade process complains about not being able to upgrade a tempdb
database because it is in use (eg, the login performing the upgrade may
be sitting in said tempdb database), you can typically ignore this particular
warning.  The tempdb database will automagically get upgraded at the
next reboot when it is copied from the model database (which should
have already gone through a successful upgrade).

================================

6- During the upgrade process we've been disabling Auditing (and re-enabling after
the upgrade).  When executing 'sp_configure auditing,0' we've (rarely) received
the following:

Msg 7729, Level 16, State 1:
Invalid role string 'mon_role' entered.

This usually occurs in the login's default database.  Our fix has been to
run the following:

sp_configure 'allow up', 1
go
use <dbname>
go
delete sysroles
go
insert sysroles select ssr.srid, su.uid,0,0 from master..syssrvroles ssr ,
sysusers su where su.name = ssr.name
go
sp_configure 'allow up', 0
go

================================

7- If the upgrade fails with a message that the system is already at version 15
(eg, you have to re-run the upgrade process), you can trick the system into
thinking it is a pre-15 version with:

sp_configure 'upgrade version', 12500

================================

8- Messages are generated an invalid format in the *.cfg file at line 97,
or problems with the string 'enable enterprise'.

The issue here is that two pre-15 *.cfg file entries ([Parallel Query],
enable enterprise java beans) no longer exist in ASE 15.  The upgrade
process should be removing these from the *.cfg file, but if for
some reason the upgrade process hiccups it may miss this.

The 'solution' is to remove the entire [Parallel Query] section from the
*.cfg file and/or remove the single line for 'enable enterprise java beans'.

Ideally a re-running of the upgrade process should correct this problem
assuming all other errors have been addressed prior to re-running
the upgrade process.

================================

9- During the upgrade process an error is generated during step 245 relating
to a duplicate key error with the sysusers.ncsysusers1 index:

          Error: 2601, Severity: 14, State: 6
                  Attempt to insert duplicate key row in object 'sysusers'
                with unique index 'ncsysusers1'
          Error: 3461, Severity: 20, State: 1 Database 'cider':
                upgrade could not install required upgrade item '245'.
          Error: 3454, Severity: 20, State: 1 Database 'cider':
                ASE could not completely upgrade this database;
                upgrade item 1417 could not be installed.

This will cause the upgrade for the particular database to fail.

The following steps have shown to be useful in correcting this issue:

use <dbname>
go
select uid, name from sysusers where name in ('ha_role', 'dtm_tm_role')
go
sp_configure 'allow updates', 1   -- if not already set
go
delete sysusers where name in ('ha_role', 'dtm_tm_role')
delete sysroles
insert sysroles select ssr.srid, su.uid,0,0 from master..syssrvroles ssr ,
sysusers su where su.name = ssr.name
go
sp_configure 'allow updates', 0  -- if it was 0 to start with
go

At this point the database can be brought online ('online database') and the
upgrade should continue as desired.  Alternatively you could wait for the
next dataserver reboot at which point the database will automatically go
through the upgrade process as part of the recovery process.

I usually catch this issue while the dataserver is being run through the upgrade
process.  If I can get in and make the necessary changes quickly I can usually
avoid any follow-on issues.  (NOTE:  If the upgrade process is currently running
then you shouldn't need to make any changes to 'allow updates'.)

If I can't get the changes done before the upgrade process reboots the dataserver
then I typically go ahead and run the through the entire upgrade process again.
I've found on a couple occasions that this error may cause some upgrade steps
to be skipped thus leading to system reliability issues after the initial
upgrade has completed.  (NOTE:  It may be necessary to 'upgrade version' to 12500
prior to being able to run the upgrade process again.)

================================

10- Other issues we've run into include run-of-the-mill stuff which is pretty
easy to understand/fix, eg,

- preupgrade step complains about some configuration settings being too
small for ASE 15; error messages should be addressed; warning messages
can be ignored or addressed, your call

- problems with source code for compiled objects (eg, source not available,
source corrupted, etc) requires said object to be dropped and recreated

- ASE 15 configuration options may eat up more of the available memory
in the dataserver so it may be necessary to either a) add more memory
to the dataserver and/or b) shift memory from other areas (eg, data caches)
to make soom for new configuration settings

================================

11- The new optimizer in ASE 15 can be somewhat temperamental when it comes to
the availability of statistics, eg, it likes to see statistics on non-leading
columns of the various indexes on a table.

Running 'update index statistics' on a table requires tempdb space for the
sorting of columns that show up in indexes as anything other than a leading column.
So if you run 'update index statistics' you'll want to keep an eye on tempdb
usage.  tempdb space usage tends to go up with the more rows that are in
the table as well as the size of the datatype for the column in question (eg,
1 million rows of tinyint's won't take as much room as 50 million rows of
char(50) not null's).

'update index statistics' actually runs (serially) the equivalent 'update
statistics <table> <index_name>' and 'update statistics <table> (<column>)'.
This can lead to much longer run times if you're used to running just
'update statistics <table>'.  You can implement the same logic by running
several of the subordinate commands in parallel; just realize that you'll
typically be trading (less) time for (more) cpu cycles and (more) disk space
requirements in tempdb.

================================