Proactive Sybase DB space

Monitoring the total space occupied by sybase database only,doesn't answer below question.

• 1 year of DB growth is available for analysis. 
• Is data continuously growing and is it predictable that database runs out of space?

* The below practical steps only applicable for the Sybase Ase version 16 SP03 and above.

1. Login to ASE DB and execute the below command.

Hostname:/sybase/<sid> >dataserver - v

Result of the above command:

DB version. 

2. ASE Database system should be integrated in DBA Cockpit.

 

Practical steps:

1. Enter hostname in the DBA Cockpit search.

2. Click on the system search.

3.click on new database opened with system name

4.click on space tab

5.click on databases in the space drop-down.

6.select the database name(Sid).

7.click on show Growth option.

8.select time frame.

9.select Granularity

10.click on Apply selection.

11.Click on Chart tab.

12.Click on summary tab.

13.Click on Details tab.

14. Download report in Excel.

Supporting sap note: 2564157

Sybase Replication step by step process

Replication Concept & Methods

Replication methods

Regular replication: – table level replication from a primary to a replicate database

Multi-site availability (MSA):- database level replication from a primary to replicate database

Warm Standby: – database level replication to maintain a standby database

Hot Standby: – transactions are run simultaneously in the primary and replicate database using distributed transaction management

Warm Standby: – transactions are captured in the primary database and distributed to the replicate database

Cold Standby: – the primary data is copied to the replicate database on a scheduled basis

Concept in replication

Primary and replicate data ( prim db to repl db )

Peer–to–Peer replication ( multiple prim db, each db is both prim & repl, update anywhere)

Heterogeneous replication ( any db to any db e.g oracle,mssql,db2 )

Replicated operations:- can not replication min log operation and manually DML on sys tbl

DML – data modification Lang (ins, upd, del, slow bcp, trun)

DDL – data definition Lang (create, alter, drop, sp_* )

DCL – grant & revoke, sp_adduser, sp_addalias, sp_changegroup

Min log operation: – select into, fast bcp, reorg rebuild, write text, update stat

Internal of sybase replication server

RepAgent reads the record from Transation log of the PDB for the tables which are marked for replication.

Logs into the PRS and write transactions in inbound queue of PDB in stable device.

Holds Data in inbound queue , untill it recieves commit.

Uses subscription information in its RSSD to decide what to do with the each transaction, after the commit:

i Discards the trans if there is no subscription.

ii Writes the transaction to the out bound queue if there are subscription.

Writes commited trans only in outbound queue according to subscription.

Sends transactions to their destination, it depends upone two things

i) if Replicated Database is managed by PRS

Apply changes to RDB using the DSI thread our the connection.

ii) If their are two server, RRS is managing RDB

Send commited trans to RRS over route.RRS apply that changes in RDB

If appropriate, uses function string information in RSSD to compose command to submit to replicate database.

As you people are seeing, there are lot of movement of trans/record , and for these trans movements Replication Server uses lot of threads(DSI,RSI,SQT,SQM etc).

If any one of thread stops , replication ceases , even it can hamper PDB performance.

Thats why it is little bit difficult to manage, not difficult, we can say, its need better monitoring.

Architecture

SD:- Stable Device

The nonvolatile store area on which replication server stores the transactions it receives from repagents or from other replication servers

Each replication server requires its own stable device

Managed directly by a replication server, but is not a database

Contains multiple stable queues

The disk device on which replication server stores data during processing

SQ:- Stable queues

Data structures within each stable device that hold data rows during the replication process

A stable device contains many stable queues, including one queue for eachRepagent connected to the replication server

Replication server to which the replication server has a route

Dataserver to which the replication server has a connection

For warm standby, a single inbound queue exists for the logical connection

For other replication relationships, there are four types of queues

Inbound Queue (IBQ):- There is one inbound queue per primary database which contains data rows and transactions that have begun at the primary database but have no yest been committed

Outbound Queue(OBQ):- There is one outbound queue for each RDB to which replication server has a connection, replication server to which it has a route , contains data for transactions that have been committed, but are still in the process of begin ( applied at the RDB, written to the stable device of another replication server )

Materialization Queue(MatQ):- holds data and commands during automatic materialization ( select the entire subscription set from the PDB, writes the subscription set to the materialization queue, insert the data into the RDB in one large transaction )

DeMaterialization Queue(DeMatQ):-

Rs_lastcommit:-

Replication server adds a table called rs_lascommit to each primary/active database in the replication system

Rs_lastcommit stores the last committed transaction that is in the stable device of the database

Replication server uses the origin_qid to ensure that no duplicate transactions are processed

Route:-

A route is a uni-directional connection between two replication servers

A replication server thread called the replication server interface(RSI) uses routes to send data and messages to other replication servers

Setting up a route automatically creates subscriptions in the destination replication server’s RSSD for the table in the source replications server’s RSSD

ID Server:-

is one replication server in the RSS that registers all the replication servers and databases with in the system

Provide each RS, DS, DB with a unique identifier

Does not have to participate in replication

Doses not to be running unless you are adding or removing RS, DB or routes to RSS

Threads

REP AGENT: – Replication agent Thread

Reads the primary database transaction lo to find transactions (SQL statements or sp exec ) that have occurred against tables that are marked for replication

Forwards transactions to the replication server using a proprietary language called Log Transfer Language (LTL)

Function as a connection manager for the repagents and passes the changes to SQM

Maintains a secondary truncation point in transaction log, which prevents transactions from being truncated until they are safely stored in the replication server stable device.

Coordinate recovery between the transaction log and replication server

Each database may only have one Repagent thread

Repagent is enabled on the standby, but is not turned on

DSI EXEC: – data server interface execution thread

Translates the replication transactions functions into destination command language (TSQL) and applies the transaction to replicate database.

DSI: – data server interface thread

Connection:-

Connection exists between replication server and the database they manage

A Replication Server has a connection to reach replicate database it manages.

A Replication thread DSI uses this connections to send updates to the replicate database

The DSI logs into the RDS as a regular client connection using the maintenance user login.

A maintenance user login is a special userid used by replication servers to make changes in replicate database and RSSD.

SQM: – Stable Queue Manager thread

is the only thread that interacts with the stable queue it performs all logical I/O to the stable queue (physical i/o actually performed by the dAIO daemon)

writes the logged changes to disk via os i/o routine, notify that async i/o deamon (dAIO)

The SQM is responsible for the following:

“Queue I/O”. All reads, writes, deletes and queue dumps from the stable queue “Duplicate Detection”. Compares OQID’s from LTL to determine if LTL log row is a duplicate of one already received.

dAIO:- async i/o deamon thread

Polls the o/s for completion and notify the SQM that i/o completed

SQT: – The Stable Queue Transaction thread

Responsible for sorting the transactions into commit order.

Request the next disk block from the SQM and sort the transaction into commit order again read request is done via SQM->dAIO once the commit record for transaction has been seen he SQT alert distribution thread (DIST) that transaction is available.

DIST: – Distribution thread

Read transaction ad determine who is subscribing to it whether subscription migration is necessary once all of the subscriber is identified the DIST thread forward the transaction to the SQM for the outbound queue for destination connection.

RSI :- Replication server interface

dSUB

dCM

dREC

dSTATS

dALARAM

USER

MSA Replication Configuration

PDS : Primary Data Server

PDB : Primary Data Base

RDS : Replicated Data Server

RDB : Replicated Data Base

PRS : Primary Replication Server

RRS : Replicated Replication Server

RSSD : Replication System Database

isql -Usa -SPDS -Psapasswd << EOF

use master

go

EXEC sp_dboption DBNAME,'abort tran on log full',true

go

EXEC sp_dboption DBNAME,'select into/bulkcopy/pllsort',true

go

EXEC sp_dboption DBNAME,'trunc log on chkpt',true

go

use DBNAME

go

sp_dropuser mnt_usr

go

sp_addalias mnt_usr,'dbo'

go

use master

go

sp_adduser mnt_usr,mnt_usr,'public'

go

use DBNAME

go

sp_reptostandby DBNAME,'all'

( ***** sp_reptostandy will pass transactions that have been performed by the maintenance user )

go

use master

go

grant set session authorization to mnt_usr

( ***** With the proxy authorization capability of Adaptive Server, System Security Officers can grant selected logins the ability to assume the security context of another user, and an application can perform tasks in a controlled manner on behalf of different users. If a login has permission to use proxy authorization, the login can impersonate any other login in Adaptive Server.

***** )

go

use DBNAME

go

( *****

scan batch size 'no_of_qualifying_records'

Specifies the maximum number of log records to send to Replication Server in each batch. When the maximum number of records is met, RepAgent asks Replication Server for a new secondary truncation point. The default is 1000 records.

***** )

sp_config_rep_agent DBNAME , "send warm standby xacts", "true"

( ***** Replication Server automatically puts a begin tran and commit around all transactions. This will cause this behavior on the replicate database. There is a paramtater in the Replciation Agent "send warm stanby xacts" that needs to be set to true when setting up MSA replication. This paramater allows the DDL to be replicated properly without recieving the above server message.

Error :-

MSA Replication with DDL – Some Store Procedure's don't replicate. Creation message "Message from server: Message: 2762, State 3, Severity 16 —

'The 'CREATE TABLE' command is not allowed within a multi-statement transaction in the 'tempdb' database.

***** )

go

sp_config_rep_agent DBNAME , "send buffer size","8k"

( *****    Controls the size of the send buffer that RepAgent uses to communicate with Replication Server. increasing the size of the send buffer reduces the number of times RepAgent communicates with Replication Server, but increases the amount of memory used.

The default value is 2K.

***** )

go

sp_config_rep_agent DBNAME , "short ltl keywords","TRUE"

( ***** Specifies whether RepAgent sends an abbreviated form of LTL to Replication Server, requiring less space and reducing the amount of data sent. The default value is “false.”

***** )

go

sp_config_rep_agent DBNAME , "priority","4"

( *****    Sets relative priority values for individual RepAgents.

4 – high priority

5 – medium priority

6 – low priority

The default value of priority is 5.

***** )

go

exec sp_config_rep_agent DBNAME , "send structured oqids","TRUE"

( ***** Specifies whether RepAgent sends origin queue IDs (OQIDs) as structured tokens, which saves space in the LTL and thus improves throughput, or as binary strings. The default value is “false.”

***** )

go

sp_stop_rep_agent DBNAME

go

sp_start_rep_agent DBNAME

go

EOF

isql -Usa -SPRS -Psapasswd_rs << EOF

alter connection to PDS.DBNAME set dsi_replication_ddl 'on'

go

( ***** Bi-directional MSA setup, DDL is replicating back to PDB.

***** )

alter connection to PDS.DBNAME set dsi_keep_triggers 'off'

go

alter connection to PDS.DBNAME set dynamic_sql 'on'

go

suspend connection to PDS.DBNAME

go

resume connection to PDS.DBNAME

go

EOF

isql -Usa -SPDS -Psapasswd << EOF

use DBNAME

go

sp_start_rep_agent DBNAME

go

EOF

isql -Usa -SRDS -Psapasswd << EOF

use master

go

EXEC sp_dboption DBNAME,'abort tran on log full',true

go

EXEC sp_dboption DBNAME,'select into/bulkcopy/pllsort',true

go

EXEC sp_dboption DBNAME,'trunc log on chkpt',true

go

use DBNAME

go

sp_dropuser mnt_usr

go

sp_addalias mnt_usr,'dbo'

go

use master

go

sp_adduser mnt_usr,mnt_usr,'public'

go

use DBNAME

go

sp_reptostandby DBNAME,'all'

go

use master

go

grant set session authorization to mnt_usr

go

use DBNAME

go

sp_config_rep_agent DBNAME , "send warm standby xacts", "true"

go

sp_config_rep_agent DBNAME , "send buffer size","8k"

go

sp_config_rep_agent DBNAME , "short ltl keywords","TRUE"

go

sp_config_rep_agent DBNAME , "priority","4"

go

exec sp_config_rep_agent DBNAME , "send structured oqids","TRUE"

go

sp_stop_rep_agent DBNAME

go

sp_start_rep_agent DBNAME

go

EOF

isql -Usa -SRRS -Psapasswd_rs << EOF

alter connection to RDS.DBNAME set dsi_replication_ddl 'on'

go

alter connection to RDS.DBNAME set dsi_keep_triggers 'off'

go

alter connection to RDS.DBNAME set dynamic_sql 'on'

go

suspend connection to RDS.DBNAME

go

resume connection to RDS.DBNAME

go

EOF

isql -Usa -SRDS -Psapasswd << EOF

use DBNAME

go

sp_start_rep_agent DBNAME

go

EOF

Tips  :-

drop subscription manually from rs_subscriptions, if it has no other subscription for database than delete it from rs_repdbs, and if ct has filter on the database than rs_dbsubsets. Than recycle the rep server.

***********************************************************************

To reset the locater, update the rs_locater table in the RSSD for the Replication

Server controlling the database using the Adaptive Server stored procedure

rs_zeroltm:

rs_zeroltm data_server, database

***********************************************************************

Use admin who, sqm – check if First.Seg.Bock and Last.Seg.Block are the same indicating the queue is empty

***********************************************************************

Use admin who, sqt – verify that there are no transactions in the ‘Closed’ column. ‘

Closed’ shows the number of committed transactions in the SQT cache.

The transactions have been read from the stable queue and await processing.

***********************************************************************

Once a stored procedure is marked as replicate, it is no more possible to replicate data updated by this procedure.

***********************************************************************

Create proxy table and drop proxy table are not supported in warm standby and MSA replication.

CR 324773 has been filed. There is no workaround but resume connection and skip transaction

***********************************************************************

Dump marker can only activate one db repdef at a time. The correct way to do this is:

Create db sub for r1. Dump p1, wait and load r1.

Create db sub for r2. Dump p1, wait and load r2.

***********************************************************************

*MSA When there are two database (db) replication definitions (repdefs) –

One primary db with two db repdefs each with filters and each replicates to a seperate replicate database.

If the 2nd db repdef’s filter is modified by RM or isql, the 1st db repdef’s filter stops working.

***********************************************************************

Error :- RS1501ESD#3. Bi_directional MSA w/table repdef. Can not drop connection after dropping all table replication definition,The error is:Database ‘DBCI2.ci’ is the primary for some replication definitions. but all repdef have been dropped.

Solution :- When a repdef was ever being used MSA or standby connection (with or without the send standby option), when it is dropped, the repdef is renamed to rs_drp0x0s. However, it is a bug that the user have to manually delete rs_drp0x0s before they can drop a connection.Workaround:update rs_objects set dbid = 0 where objname like “rs_drp%” and dbid =

before dropping the connection.

Repserver Bug 500356 will be fixed in 15.0.1 ESD #4 and 15.1 ESD #1.In this case, customer manually deleted rs_drp0x0s from rs_objects before drop the connection. Or you update rs_objects set dbid 0 where objname like ‘rs_drp%’. Then.drop the connection.

***********************************************************************

sp_setrepproc stored_procedure_name

go

exec stored_procedure_name

go

This will force the stored procedure to execute on the target instead.

***********************************************************************

To set traces, add the following lines into the .cfg file of the Replication Server

you want to trace.

1 This line forwards the output to the standard output or to the errorlog:

trace=GEN,TRC_STDERR or trace=GEN,TRC_ERRLOG

2 This line gives the output of commands that are sent to the replicate site.

trace=DSI,DSI_CMD_DUMP

***********************************************************************

Dumping stable queues

sysadmin dump_queue, q_number, q_type, seg, blk, cnt [,RSSD | Client ]

q_number, q_type :- check with admin who or admin who,sqm

seg :-  -1 1st active segement , -2 1st segement ( act or inactive )

Blk :- start from 1 to 64

if seg -1 and blk -1 : start with 1st undeleted blk of queue

if seq -1 and blk -2 :- start with 1st unread blk of queue

cnt :- number of blk to dump

-1 end of current seg is last

-2 end of queue

RSSD :- o/p to rssd table (rs_queuemsg, rs_queuemsgtxt )

Client :- o/p to client isql

If no option then written into rep server log file or dump_file

eg :- sysadmin dump_queue,103,1,0,15,65

sysadmin dump_file, qdump

sysadmin dump_queue,103,1,-1,1,-2

sysadmin dump_file

*********************************************************************

• 
  

select the sort order in sybase

Selecting the Sort Order

Different languages sort the same characters differently. SAP ASE uses sort orders to create indexes, store date into indexed tables, and specify an order by clause.

For example, in English, Cho is sorted before Co, whereas in Spanish, the opposite is true. In German, β is a single character, however in dictionaries it is treated as the double character ss and sorted accordingly. Accented characters are sorted in a particular order so that aménité comes before amène, whereas if you ignored the accents, the reverse would be true. Therefore, language-specific sort orders are required so that characters are sorted correctly.

Each character set comes with one or more sort orders that SAP ASE uses to collate data. A sort order is tied to a particular language or set of languages and to a specific character set. The same sort orders can be used for English, French, and German because they sort the same characters identically, for example, A, a, B, b, and so on. Or the characters are specific to one of the languages—for example, the accented characters, é , à, and á, are used in French but not in English or German—and therefore, there is no conflict in how those characters are sorted. The same is not true for Spanish however, where the double letters ch and ll are sorted differently. Therefore, although the same character sets support all four languages, there is one set of sort orders for English, French and German, and a different set of sort orders for Spanish.

In addition, a sort order is tied to a particular character set. Therefore, there is one set of sort orders for English, French, and German in the ISO 8859-1 character set, another set in the CP 850 character set, and so on. The sort orders available for a particular character set are located in sort order definition files (*.srt files) in the character set directory.

• Different Types of Sort Orders
  All character sets are offered with a binary sort order at a minimum, which blindly sorts all data based only on the arithmetic value of the code assigned to represent each letter (the “binary” code) in the character set.
• Selecting the Default Sort Order
  SAP servers can support only one default sort order at a time. If your users all use the same language, or their languages all use the same sort order, selecting the default sort order is straightforward.

1860413 - How to change character set or sort order of SAP ASE

Symptom

• How to change the default character set and sort order of ASE?
• Steps to change character set or sort order in ASE.

Environment

SAP Adaptive Server Enterprise (ASE)

Resolution

Below 2 examples of how to change character set and sort order.

EXAMPLE 1 Change from iso_1 character set to UTF8 character set, keeping binary sort order=============================================================================

1. Run sp_helpsort to check current character set & sort order (output truncated for clarity) :

sp_helpsort
go

Sort Order Description

Character Set = 1, iso_1ISO 8859-1 (Latin-1) - Western European 8-bit character set.
Sort Order = 50, bin_iso_1Binary ordering, for the ISO 8859/1 or Latin-1 character set (iso_1).

2. Check which character sets and sort orders are installed:

select type, id, csid, name from syscharsets
go

Note: For 'type' columns, numbers from 1001 to 1999 represent character sets. Numbers from 2000 to 2999 represent sort orders.

3. Add a UTF8 charset with binary sort order to ASE, using 'charset' utility which can be found in $SYBASE/ASE-1*_0/bin:

charset -Usa -SASE binary.srt utf8

.....
Finished loading file 'binary.srt'.
1 sort order loaded successfully

4. Change default character set:

sp_configure 'default character set id', 190   --  character set # 190 is utf8
go

5. Restart ASE twice.

On first restart, ASE will detect character set change and will shutdown automatically after these log messages :

Default Sort Order successfully changed.
ASE shutdown after verifying System Indexes.

6. Run sp_helpsort to check changed character set:

sp_helpsort
go

Sort Order Description

Character Set = 190, utf8
Unicode 3.1 UTF-8 Character Set
Class 2 Character Set
Sort Order = 50, bin_utf8
Binary sort order for the ISO 10646-1, UTF-8 multibyte encoding character set (utf8).

         

EXAMPLE 2 Change from iso_1 with binary sort order to cp1250 with noaccents sort order==========================================================================

The second example is similar to the first. Please refer to Example 1 for explanations / more detailed outputs.

1. Run sp_helpsort to check current character set & sort order (output truncated for clarity) :

sp_helpsort
go

2. Check which character sets and sort orders are installed:

select type, id, csid, name from syscharsets
go

3. Add the cp1250 charset with binary AND noaccent sort orders to ASE, using 'charset' utility:
   

charset -Usa -SASE binary.srt cp1250
charset -Usa -SASE noaccents.srt cp1250

4. Change default character set:

sp_configure "default character set id", 22  --  character set # 22 is cp1250
go
sp_configure "default sortorder id", 54       --  sort order id # 54 is noaccents
go

5. Restart ASE twice.

6. Run sp_helpsort to check that the new character set has been changed successfully.

sp_helpsort
go

Sort Order Description

Character Set = 22, cp1250
Microsoft Windows Code Page 1250, Eastern Europe
Sort Order = 54, noaccents_cp1250
Polish Windows dictionary sort order. Uses the Polish and EE Windows code page 1250 character set and is case and accent insensitive.

7. Run dbcc reindex when needed, see KBA 2496376 - Index is marked as suspect after sort order has been changed. SAP ASE

How to extent sap data unit in sybase

In any database data units and log units are core places where the data of database resides. in this post  i am sharing the process of extending disk space of one data unit in Sybase.

In the below screenshots,  sapdata_1 to sap data _4 & saplog_1 to saplog_2 are available.

To extended the disk space of sapdata_4,execute the below commands:

% isql -Usapsa -SSOL -X

Password:

1> use master

2> go

1> disk resize name = "SOL_data_004",size="5G"

2> go

1> use master

2> go

1> alter database SOL on SOL_data_004 = '5G'

2> go

Note: the disk space from root is assigned to sapdata_4. 

How to start sybase database and Backup server

1. login with root or user with sudo -i authorization.
2. su - syb<sid>

the screen looks like below:

hostname> su - syb<sid>

syb<sid>@hostname: 

     3. cd /sybase/sybase_ase/install/RUN_<SID>                        

             startserver -f  RUN_<SID>

Note: the above command is for starting the data server.which enable to start sybase Database.

    4.cd /sybase/sybase_ase_/install/RUN_<SID>_BS

             startserver -f  RUN_<SID>_BS

Note: the above command is for starting the Backup server.which needs while taking backup of server.

• by performing step 3 and 4, makes the sybase database turn on:

can ensure through the command: ps -ef | grep -i syb<sid>

1. dataserver
2. backupserver.
3. jsagent(job schedule agent).

on linux, a running sybase ASE should have at least the following process.

SYBASE: How to update sybase database patches

How to apply patches to sybase

Tags:

• sybase_ase
• migration_to_ase_15
• sybase
• sap_sybase_ase
• sap_sybase

Procedure - apply patches to sybase

1. Download the software from the SAP Service Marketplace

2. Prepare the upgrade

3. Update the ASE software

4. Perform post-upgrade tasks

5. Update the ODBC and JDBC drivers

6. Restart the SAP system

Recommendation: Perform a file system backup of the ASE software directory (<drive>:\sybase\<SID>) before you update the software.

1. Download the software from the SAP Service Marketplace

              Download the latest software patch from the SAP Service Marketplace.

a) Open the SAP Software Download Center at http://service.sap.com/swdc.

b) Choose "Support Packages and Patches" -> "Browse our Download Catalog".

c) Choose "Sybase Products" -> "Sybase ASE FOR BUSINESS SUITE" -> "Sybase ASE 15.7 FOR BUS. SUITE".
Choose your platform and "Downloads".

d) Unpack the software package to a local temporay directory.

                       File ASE157SPxx_x.SAR contains the ASE server software (xx is a two or three-digit number and refers to the current ASE patch level).

                       File DBCLNT157SPxx_x.SAR contains the ODBC and JDBC drivers.

                       Since the ASE installation has to be performed as the ASE software owner syb<sid>, it is recommended that you unpack the file as user syb<sid>.

                       Copy the downloaded files to a temporary directory. Unpack the file ASE157SPxx_x.SAR using the command:

                       SAPCAR -xvf ASE157SPxx_x.SAR

                       Do not unpack the file DBCLNT157SPxx_x.SAR.

2. Preparation for the upgrade

a) Perform a file system backup fo the software installation directory %SYBASE% (<drive>:\sybase\<SID>).

b) Check the database version before the update.

                       Log on to the operating system with user syb<sid>. Open an administrator cmd shell and type:

                       sqlsrvr.exe --sbssav

                       sqlsrvr.exe --version

                       Make a note of the current version for later comparison.

                       Recommendation: Unlock the 'sa' account in ASE before you start the ASE upgrade. Log in to ASE using the account 'sapsso' with isql and execute the command:

                       sp_locklogin 'sa','unlock'

c) Stop the SAP system and the ASE server; use user <sid>adm.

                       Shut down the SAP system and the ASE server using the SAP Management Console. Alternatively, shut down the ASE server by stopping the services Sybase BCKServer _<SID>_BS and Sybase SQLServer _<SID>.

                       Use the Task Manager to check if the processes sqlsrvr.exe and bcksrvr.exe owned by user syb<sid> have stopped running.

                       The ASE server must be offline during the update. Also none of the utilities (for example 'isql') must be in use. Otherwise files would be locked by the running database processes.

3. Update the ASE software

              You can use either the command line installer 'setupConsole.exe' or the graphical ASE installer.

a) Using the command line installer

                       Download the file dbsrvresp_update.txt which is attached to this note. Put it into the temporary directory where you unpacked the ASE Software. As user syb<sid> open an administrator command shell, switch to the temporary directory. Call the executable setupConsole.exe with the following parameters (the command must be entered on one line at the command prompt):

                       .\setupConsole.exe -f <TMPDRIVE>:\<PATH_TO_TMPDIR>\dbsrvresp_update.txt -i silent -DUSER_INSTALL_DIR=<SYBDRIVE>:\sybase\<DBSID> -DAGREE_TO_SYBASE_LICENSE=true

                       Replace <DBSID> with your SAP System ID, replace <SYBDRIVE> with the drive where your ASE software is installed.

                       Replace <TMPDRIVE> and <PATH_TO_TEMPDIR> as it applies to your system (the temporary folder with installable ASE software is located).

                       When the install process has finshed, check the installer log file ASESuite.log which is located in directory %SYBASE%\log.

b) Using the GUI installer

                       As user syb<sid> open an administrator command shell, switch to the temporary directory. Call the executable setupConsole.exe with the following parameters (the command must be entered on one line at the command prompt):

                       .\setup.exe -DINSTALL_USER_PROFILE=USER -DDO_NOT_CREATE_SHORTCUT=TRUE -DREGISTER_UNINSTALLER_WINDOWS=FALSE

                       In the installer GUI window, select the Sybase home directory <drive>:\sybase\<SID> for the update and click "Next". Confirm that you want to perform the installation in an existing directory. Select "Yes" when you are asked if you want to update the product.

                       If installing ASE 15.7 SP100 and higher the GUI install displays a dialog box 'Choose Update Adaptive Server'. Do not choose this option. Click "Next" until the GUI procedure finishes.

                       Depending on the upgrade history of the installation, the ASE installer might not display the dialog box asking whether you want to update the product.
Instead a dialog box is displayed asking whether you want to install 'Typical', 'Full' or 'Custom'. Choose custom mode and mark the check boxes as follows:

• In the Adaptive Server Enterprise feature group uncheck 'Additional ASE language modules' (unless you do need any additional language support).

• In the 'Open Client' feature group choose DB client (leave all other checkboxes unchecked).

• Set the checkbox for the jConnect feature.

• In the feature group 'sysam License Utilities' uncheck 'SySam License Server'.

• In the feature group 'Sybase Control Center' set the checkbox for 'Remote Command and Control Agent for Adaptive Server'. Leave all other checkboxes in the group unchecked.

                       In the 'License Type' screen choose 'Install Licensed Copy'. Agree to the license terms in the following screen. Choose product edition 'Enterprise Edition' and License Type 'Unknown'. Do not configure email alerts and click 'Next'. In the 'Configure new servers' dialog, leave everything unchecked and select 'Next'.                       If a dialog box "Remember passwords after connecting to servers" is displayed, select the option suitable to your business needs. We recommend you to choose "Disable".                       When the install process has finished, check the installer log file ASESuite.log which is located in directory %SYBASE%\log.                      

4. Perform post upgrade tasks

a) Restart the ASE server.

                       Start the services Sybase BCKServer _<DBSID>_BS and Sybase SQLServer _<DBSID>.                       Review the ASE error log file for correct execution (open the file <drive>:\sybase\<SID>\ASE-15_0\install\<SID>.log).                       If the ASE Job Scheduler was not disabled before you performed the upgrade, you may see an error message similar to the one below in the ASE error log:                       kernel  Initializing Job Scheduler Task
kernel  Installed Job Scheduler sequencer code version 0.29 - 29 tokens
server  Error: 11031, Severity: 16, State: 1
server  Execution of rule check_jsc_state failed because of errors parsing the source text in syscomments during upgrade. Please drop and recreate dbo.check_jsc_state.
kernel  JS : Exception 3602 raised.
kernel  JS : js__excute got the Exception                       You can ignore this error at this point.

b) Log on to the operating system with user syb<sid>, connect to ASE with isql and temporarily unlock user "sa".

                       sp_locklogin 'sa', 'unlock'

c) Execute the Sybase installation scripts.

                       After the installation of the software, execute the following scripts that adapt ASE system catalog tables. Log in as user 'syb<sid>' and switch to directory                       <drive>:\sybase\<SID>\ASE-15_0\<scripts>                       Run the script 'instmstr':                       isql -X -Usa -S<SID> -iinstmstr -o<output file>                       Check <output file> for errors.                       Check <drive>:\sybase\<SID>\ASE-15_0\install\<SID>.log for errors.                       Repeat these steps for the following scripts:

• instcomm

• instmsgs.ebf

• installjsdb

• installdbextend

• installdbccdb (optional, necessary only if you configured the dbcc database)

d) Execute the script 'syb_update_db.TXT' which is attached to this note.

                       Copy the script to a temporary directory (e.g. the temporary directory to which you copied the Sybase software package). Change to that directory and execute the script:

                       $>isql -X -Usa -S<SID> -e -i syb_update_db.TXT -o sybupd_out.txt

                       Review the generated output file for correct execution.

                       For more information on the script, see SAP Note 1602547.

e) Restart the job scheduler. Log in to ASE with isql with the Sybase account 'sapsa' and execute the command:

                       sp_configure "enable job scheduler", 1

                       exec sybmgmtdb..sp_sjobcontrol '' , 'start_js'

f) Lock user "sa" again. Log in to ASE with isql with the Sybase account 'sapsso' and execute the command:

                       sp_locklogin 'sa', 'lock'

g) Remove the newly installed sample client library applications according to SAP Note 1598817 (optional).

5. Update of the ODBC and JDBC drivers

              The SAP system does not have to be stopped when you copy the new ODBC and JDBC software to the global directory.

a) Log in as user <sid>adm.

b) Perform a file system backup of the directory

                       <drive>:\usr\sap\<SID>\SYS\global\syb\NTAMD64\

c) Copy the file DBCLNT157SPxx_x.SAR to the directory

                       <drive>:\usr\sap\<SID>\SYS\global\syb\NTAMD64\

d) Unpack the DBCLNT157SPxx_x.SAR file using the command:

                       <DRIVE>:\usr\sap\<SID>\SYS\global\syb\NTAMD64\$>SAPCAR -xvf DBCLNT157SPxx_x.SAR

              With DBCLNT157SP05_0. SAR, the driver name was changed from jconn3d.jar to jconn3.jar, and jconn4d.jar to jconn4.jar, respectively. Update the JDBC driver and reconfigure your Java stack to use this JDBC driver with the changed name, see SAP Note 867976.

6. Restart the SAP system

              Log in as user <sid>adm and restart the SAP system through the SAP Management Console.