Knowledge in Sybase
Sybase ASE -Introduction to Sybase ASE(Part 1)
Sybase ASE -Introduction to Sybase ASE(Part 1) The Client-Server Architecture As every other Database platform this is also a Client Server Architecture. Client – Program that receives requests from a user and sends them to the server; also receives responses from the server and displays them for a user Server – Program that processes requests from the client and returns results to the client ASE – History This is commonly known as Sybase DB or Sybase ASE, is a Relational Database Model product by Sybase Corporation which became part of SAP in July 2010. ASE is a legacy software predominantly used on the Unix platform, but is also available for Microsoft Windows. Versions so far, 1992 : SQL Server 4.2 1993: Sybase SQL Server 10.0 1995: Sybase released SQL Server 11.0 1996: Sybase with version 11.5 1998: ASE 11.9.2 1999: ASE 12.0 2001: ASE 12.5 2005: Sybase released ASE 15.0 2010: Sybase release ASE 15.5 2011: Sybase released ASE 15.7 at Techwave 2014: SAP released ASE 16. Adaptive Server It is a Sybase server that manages multiple databases Databases are divided as follows.Required Databases Master Model Sybsystemprocs Sybsystemdb tempdb Utility databases sybsecurity dbccdb sybsyntax pubs2 Application Databases. Connection For connecting to the server, you need any of the ASE client Valid Login name and password The connection so created, is called as the session When your login name is authenticated, you are connected to your default database Default database is the database, where you are placed after a successful connection It is specific to a login The SA (System Administrator) assigns it to individual logins. Isql – Connection to server isql is a command line utility which acts as a client to Adaptive Server isql can be invoked with the following options (most commonly used) -U login name -S server name -P password -w width of display -c line terminator e.g: $isql –Usa –Sserver1 –Ppassword If –P parameter is omitted, it prompts for the password The prompt defaults to the line number, where the commands can be typed e.g : 1>select db_name() 2>go ——————— master (1 row affected) go, on a line by itself executes the command (There cannot be any blank spaces in front of go) Batches in isql A batch is a set of Transact-SQL statements that are submitted as a group by the client and executed as a group by the server Example: select * from titles select * from authors go isql batches are terminated by the word go Database Navigation in isql A session is always in one of the server databases To display the current database, use the command: select db_name( ) go To move to a different database, use the command: use go System Procedures System procedures are built-in utilities of Sybase to view, modify information inside Sybase ASE They generally reside in sybsystemprocs(System database) They are preceded with sp_ Examples of procedures sp_help Displays all the objects in the current database sp_helpdb Displays all the databases in the current server sp_helpdb databasename Displays detailed information about a particular database Summary In a client/server relationship: The client is the program that receives requests from the user, sends them to the server, and returns the results to the user. The server receives and executes client requests. Adaptive Server is the ASE server. ASE 12.5 includes two client applications: isql, a command-line client jisql, a java-based graphical interface client System procedures can provide information on: The databases managed by the server The objects within a given database
Nupur Sharma
Basic Sybase Introduction -Part 1
Very Basic introduction for Sybase ASE database technology where you can learn our old database technology now owned by SAP Company. Its just a little overview of Sybase and its related terms. There are other vedios/Documents are coming up to support this Database Knowledge.
How to Refresh or Resync a replicated database using Sybase ASE and Replication Server
How to Refresh or Resync a replicated database using Sybase ASE and Replication Server This is just one way to resync the replicated database in Sybase Stop Replication log into primary server and change database sp_stop_rep_agent <DBNAME> dbcc settrunc(ltm, ignore) log into repserver suspend connection to <secondary_dbserver>.<dbname> Flush queue Check disk space on the RepServerlog into RepServer admin disk_space log into RSSD database (typically on the standby Sybase ASE) run rs_helppartition “partition name” to determine which queues this partition contains If we are going to resync, we need to flush the queue (in the RepServer) sysadmin hibernate_on, “Reason for flushing queue” sysadmin sqm_purge_queue, <connection #>, 0 sysadmin sqm_purge_queue, <connection #>, 1 sysadmin sqm_purge_queue, 140, 0 (“0” is the outbound queue. “1” is the inbound queue) – do for both queues sysadmin hibernate_off, “Reason for flushing queue” Sync databases log into RSSD database rs_zeroltm <primary_server>.<dbname> turn secondary truncation marker in primary server.dbname dbcc settrunc(ltm, valid) dump database on primary server sp_start_rep_agent <dbname> this will transfer the transactions from the primary database’s log segment into the queues. load database on secondary server (do not bring on line) dbcc dbrepair(<dbname>, ‘ltmignore’) online database <dbname> Enable Replication log into rep server resume connection to <secondary_dbserver>.<dbname> if the replication definitions are for individual tables and not the entire database set autocorrection on for <table_repdef> with replicate at <secondary_dbserver>.<dbname> when the tables are in sync, turn off autocorrection (replace ‘on’ with ‘off’) if the replication definition is for the entire database be prepared to run “resume connection to <secondary_dbserver>.<dbname> skip transaction” until the databases are in sync – can be quite a few (this can be scripted if necessary).
SYBASE IQ : Add disk space
SYBASE IQ : Add disk space To Add Space in iq Database. Find free space in each file 1>sp_iqfile 2>go 1>alter DBSPACE iq_main add file XXXXX_iq_main019_16405 ‘/p_kalm_iq_devs/XXXXX_iq_XXXXXXXXXX_main019.dbf’ size 100MB/GB 2>go
Sybase Tempdb 100% Full
Sybase Tempdb 100% Full tempdb is 100% full ? Ideally tempdb capacity should be sized enough to handle largest and parallel transactions. However this can’t be achieved all the time. So one of the unlikely situation occurs when tempdb becomes 100% full. If users and administrators are using the same tempdb, commands such as sp_who don’t work for the sa leaving people wondering what to do next. Errors when tempdb is full As the sa: Failed to allocate disk space for a work table in database 'tempdb'. You may be able to free up space by using the DUMP TRANsaction command, or you may want to extend the size of the database by using the ALTER DATABASE command. sp_who shows: The transaction log in database tempdb is almost full. Your transaction is being suspended until space is made available in the log. select * from syslogshold then shows: Failed to allocate disk space for a work table in database 'tempdb'. You may be able to free up space by using the DUMP TRANsaction command, or you may want to extend the size of the database by using the ALTER DATABASE command. Use a query that works when tempdb is full select “spid=” + convert( varchar(3), SPID) + ” login=” + suser_name(ServerUserID) + ” SQLText=” + SQLText from master..monProcessSQLText 1> select "spid=" + convert( varchar(3), SPID) + " login=" + suser_name(ServerUserID) + " SQLText=" + SQLText from master..monProcessSQLText 2> go --------------------------------------------------------------------------------------------------------- spid=16 login=sa SQLText=sp_who spid=17 login=fsmith SQLText=insert junk select * from raw_test..junk spid=20 login=sa SQLText=select "spid=" + convert( varchar(3), SPID) + " login=" + suser_name(ServerUserID) + " SQLText=" + SQLText from master..monProcessSQLText (3 rows affected) 1> Option 1 kill offending spid In the test example above we can identify that spid 17 has the offending query. kill 17 go During a simple test it took over 4 minutes for the tempdb to become free so a degree of patience is needed. Option 2 kill off all spids using tempdb If it was impossible to determine what the offending spid was then to kill off all spids using tempdb (dbid=2): select lct_admin(“abort”,0,2) 1> select lct_admin("abort",0,2) 2> go lct_admin(abort): Process 25 waiting on log-suspend state on database 'tempdb is being aborted. lct_admin(abort): Process 16 waiting on log-suspend state on database 'tempdb is being aborted. Option 3 Increasing tempdb size If the process is important and needs to finish then increasing the size of the tempdb database is the way to go. It might be that tempdb is undersized for the application. use master go 1> disk init name="tempdb_tmp12", size="400M", 2> physname="/home/sybase/SYB157/data/tempdb_tmp12.dat" , dsync="false", directio="false" 1> alter database tempdb on tempdb_tmp12=400 2> go Extending database by 102400 pages (400.0 megabytes) on disk tempdb_tmp12 Choices of options Hastily increasing tempdb might mean that all of the corresponding test, development and disaster recovery environments need to be changed to match, possibly due to one stray user query which shouldn’t have been there in the first place. It is for this reason that sites are sometimes reluctant to increase database sizes without good planning. Checking progress of a rollback The following query doesn’t always give meaningful values ( it can show negative values when tempdb full) but does give an indication of progress: select (lct_admin(“logsegment_freepages”,2) – 1.0 * lct_admin(“reserved_for_rollbacks”,2)) /1048576.*@@maxpagesize For the above query “,2” signifies tempdb dbid = 2 This query shows 6.32 Mb free on database 16 which is in log suspend: 1> select (lct_admin("logsegment_freepages",16) - 1.0 * lct_admin("reserved_for_rollbacks",16)) /1048576.*@@maxpagesize 2> go ------------------------------------ 6.320312320 (1 row affected) 1> Prevention: Using abort tran on log full Depending on the site and the application setting tempdb to “abort tran on log full” prevents getting into a position where tempdb is unavailable to all users. 1> use master 2> go 1> sp_dboption tempdb, "abort", true 2> go Warning: Attempting to change database options for a temporary database. Database options must be kept consistent across all temporary databases. Database option 'abort tran on log full' turned ON for database 'tempdb'. Running CHECKPOINT on database 'tempdb' for option 'abort tran on log full' to take effect. (return status = 0) 1> use tempdb 2> go 1> checkpoint 2> go abort tran on log full tested ok for Sybase ASE 15.7 with a mixed data and log tempdb. Creating another tempdb and binding the users to it If users and administrators use different a tempdb from each other, then system stored procedures can still be used by “sa” logins if a user or batch process fills up tempdb. 1> create temporary database tempdb_raw on RAW_DATA002=350 2> go 1> sp_tempdb 'bind','lg','fsmith','db','tempdb_raw' 2> go (return status = 0) tempdb is full – what to do now ? One of the worst things about tempdb being full is that it can prevent both users and administrators from using the system. If users and administrators are using the same tempdb, commands such as sp_who don’t work for the sa leaving people wondering what to do next. Errors when tempdb is full As the sa: Failed to allocate disk space for a work table in database 'tempdb'. You may be able to free up space by using the DUMP TRANsaction command, or you may want to extend the size of the database by using the ALTER DATABASE command. sp_who shows: The transaction log in database tempdb is almost full. Your transaction is being suspended until space is made available in the log. select * from syslogshold then shows: Failed to allocate disk space for a work table in database 'tempdb'. You may be able to free up space by using the DUMP TRANsaction command, or you may want to extend the size of the database by using the ALTER DATABASE command. Use a query that works when tempdb is full select “spid=” + convert( varchar(3), SPID) + ” login=” + suser_name(ServerUserID) + ” SQLText=” + SQLText from master..monProcessSQLText 1> select "spid=" + convert( varchar(3), SPID) + " login=" + suser_name(ServerUserID) + " SQLText=" + SQLText from master..monProcessSQLText 2> go --------------------------------------------------------------------------------------------------------- spid=16 login=sa SQLText=sp_who spid=17 login=fsmith SQLText=insert junk select * from raw_test..junk spid=20 login=sa SQLText=select "spid=" + convert( varchar(3), SPID) + " login=" + suser_name(ServerUserID) + " SQLText=" + SQLText from master..monProcessSQLText (3 rows affected) 1> Option 1 kill offending spid In the test example above we can identify that spid 17 has the offending query. kill 17 go During a simple test it took over 4 minutes for the tempdb to become free so a degree of patience is needed. Option 2 kill off all spids using tempdb If it was impossible to determine what the offending spid was then to kill off all spids using tempdb (dbid=2): select lct_admin(“abort”,0,2) 1> select lct_admin("abort",0,2) 2> go lct_admin(abort): Process 25 waiting on log-suspend state on database 'tempdb is being aborted. lct_admin(abort): Process 16 waiting on log-suspend state on database 'tempdb is being aborted. Option 3 Increasing tempdb size If the process is important and needs to finish then increasing the size of the tempdb database is the way to go. It might be that tempdb is undersized for the application. use master go 1> disk init name="tempdb_tmp12", size="400M", 2> physname="/home/sybase/SYB157/data/tempdb_tmp12.dat" , dsync="false", directio="false" 1> alter database tempdb on tempdb_tmp12=400 2> go Extending database by 102400 pages (400.0 megabytes) on disk tempdb_tmp12 Choices of options Hastily increasing tempdb might mean that all of the corresponding test, development and disaster recovery environments need to be changed to match, possibly due to one stray user query which shouldn’t have been there in the first place. It is for this reason that sites are sometimes reluctant to increase database sizes without good planning. Checking progress of a rollback The following query doesn’t always give meaningful values ( it can show negative values when tempdb full) but does give an indication of progress: select (lct_admin(“logsegment_freepages”,2) – 1.0 * lct_admin(“reserved_for_rollbacks”,2)) /1048576.*@@maxpagesize For the above query “,2” signifies tempdb dbid = 2 This query shows 6.32 Mb free on database 16 which is in log suspend: 1> select (lct_admin("logsegment_freepages",16) - 1.0 * lct_admin("reserved_for_rollbacks",16)) /1048576.*@@maxpagesize 2> go ------------------------------------ 6.320312320 (1 row affected) 1> Prevention: Using abort tran on log full Depending on the site and the application setting tempdb to “abort tran on log full” prevents getting into a position where tempdb is unavailable to all users. 1> use master 2> go 1> sp_dboption tempdb, "abort", true 2> go Warning: Attempting to change database options for a temporary database. Database options must be kept consistent across all temporary databases. Database option 'abort tran on log full' turned ON for database 'tempdb'. Running CHECKPOINT on database 'tempdb' for option 'abort tran on log full' to take effect. (return status = 0) 1> use tempdb 2> go 1> checkpoint 2> go abort tran on log full tested ok for Sybase ASE 15.7 with a mixed data and log tempdb. Creating another tempdb and binding the users to it If users and administrators use different a tempdb from each other, then system stored procedures can still be used by “sa” logins if a user or batch process fills up tempdb. 1> create temporary database tempdb_raw on RAW_DATA002=350 2> go 1> sp_tempdb 'bind','lg','fsmith','db','tempdb_raw' 2> go (return status = 0)