Anything About Sybase ASE,Replication Server & Sybase IQ.
Posts tagged ASE
Inserting data into a data-only-locked heap table
01 month ago
When users insert data into a data-only-locked heap table, Adaptive Server tracks page numbers where the inserts have recently occurred, and keeps the page number as a hint for future tasks that need space. Subsequent inserts to the table are directed to one of these pages. If the page is full, Adaptive Server allocates a new page and replaces the old hint with the new page number.
/***********************************************************************************************************
Blocking while many users are simultaneously inserting data is much less likely to occur during inserts to data-only-locked heap tables. When blocking occurs, Adaptive Server allocates a small number of empty pages
and directs new inserts to those pages using these newly allocated pages as hints.
**********************************************************************************************************/
For datarows-locked tables, blocking occurs only while the actual changes to the data page are being written; although row locks are held for the duration of the transaction, other rows can be inserted on the page. The row-level locks allow multiple transaction to hold locks on the page.
If conflicts occur during heap inserts
————————————–
Conflicts during inserts to heap tables are greatly reduced for data-onlylocked tables, but can still take place. If these conflicts slow inserts, some workarounds can be used, including:
• Switching to datarows locking, if the table uses datapages locking
• Using a clustered index to spread data inserts
• Partitioning the table, which provides additional hints and allows new pages to be allocated on each partition when blocking takes place
Inserting data into an allpages-locked heap table
01 month ago
When you insert data into an allpages-locked heap table, the data row is always added to the last page of the table. If there is no clustered index on a table, and the table is not partitioned, the sysindexes.root entry for the heap table stores a pointer to the last page of the heap to locate the page
where the data needs to be inserted.
If the last page is full, a new page is allocated in the current extent and linked onto the chain. If the extent is full, Adaptive Server looks for empty pages on other extents being used by the table. If no pages are available, a new extent is allocated to the table.
Conflicts during heap inserts
—————————-
If many users are trying to insert into an allpages-locked heap table at the same time, each insert must
wait for the preceding transaction to complete.
This problem of last-page conflicts on heaps is true for:
• Single row inserts using insert
• Multiple row inserts using select into or insert…select, or several insert statements in a batch
• Bulk copy into the table
Some workarounds for last-page conflicts on heaps include:
———————————————————
• Switching to datapages or datarows locking
• Creating a clustered index that directs the inserts to different pages
• Partitioning the table, which creates multiple insert points for the table, giving you multiple “last pages” in an allpages-locked table
ASE database for SAP ERP
02 months ago
Hello all,
These are copilataion for Sybase ASE on SAP from the Rob’s Blog :
Read Full Story : http://blogs.sybase.com/database/2011/12/so-what-does-an-ase-database-look-like-in-sap-erp/
- SAP has released Business Suite on ASE version 15.7.
- All SAP application data resides in a single ASE database. There is another small database for use by SAP tools.
- The ASE database uses a 16KB page size.
- For ERP only (i.e. not counting CRM and the other Business Suite modules), the database contains about 80,000 tables and 170,000 indexes. This is because SAP ERP has many features and functions, all with their own set of tables. SAP customers typically run only a subset of all those functions so in practice a large part of those 80,000 tables will always remain empty.
- All SAP tables use datarowslocking (there is an interesting historical dimension.
- All tables names are in uppercase; some table names contain special characters, like the slash character in “/BCV/C_QATTR” (I don’t have a clue what that name means, BTW)
- Apart from the tables, there are also about 10,000 views. No stored procedures or triggers are used.
- SAP makes heavy use of dynamic SQL (also known as “prepared statements”).
- Many tables have a text or image column.
- All tables are owned by one database user (and that’s not the dbouser).
- The ASE database is accessed through ODBC.
- SAP makes frequent use of the built-in ASE Job Scheduler (originally added in ASE 12.5.1).
- The ASE server uses Unicode with the utf8 character set.
What’s in YOUR Architecture?
02 months ago
What’s in your architecture?
Probably not enough if you’re using just data modeling to integrate all the components of your enterprise, especially your information. To lay the
foundation of this paper, let’s start from the same level of understanding: the need for a common approach to managing all the aspects of information to
enable rapid business performance in the 21st century economy.
Competition in the marketplace is always fierce. To stay abreast, organizations must constantly analyze their customer needs and expectations, enhance
or innovate their business processes, and deliver products and services that create exceptional customer value. Organizations also need to be customercentric to forge long-term relationships with clients and consumers. Only organizations that are agile enough to respond to volatile market conditions
with innovation, expedited time-to-market processes, and reduced costs can differentiate themselves from the competition. Such agility occurs when a
company’s IT operations are closely aligned with its business operations. IT needs to understand business to implement technologies and applications that
support the current and future business goals.
Read Full Article @ http://www.sybase.com/files/White_Papers/Sybase_Whats_in_Your_Architecture_WP.pdf
K21– ASE’S KERNEL DESIGN FOR THE 21ST CENTURY – ASE 15.7’s THREADED KERNEL K21
0Basic Difference :
Process Kernel:
Pre-15.7 kernel (except Windows)
Each engine is a separate process
Retained in 15.7 for risk mitigation
Threaded Kernel:
Default kernel for 15.7
Each engine is a thread of a single process
Additional threads for handling I/O, etc.
ASE on Windows has always been thread based
http://www.sybase.com/files/Product_Overviews/ASE-15.7-New-Threaded-Kernel.pdf
Survey among Sybase and Oracle customers – Bloor’s Research .
03 months ago
Survey among Sybase and Oracle customers.
1. License fees: 75% of respondents thought that Sybase ASE was less expensive, by an average of 28%.
2. Support costs: 78% of respondents thought that Sybase ASE was less expensive, by an average of 32%.
3. Number of database administrators: nobody thought that Oracle required fewer DBAs and 61% thought that Sybase required fewer DBAs. On average the saving was 32%.
4. Frequency of security patches: again, nobody thought that this was less frequent in the case of Oracle while 68% thought that this was the case with Sybase, with an average reduction of 22%.
5. Issue resolution: 73% thought that Sybase was faster at resolving issues, typically being 21% faster.
Reference: http://www.sybase.com/files/White_Papers/SYBASE_ASE_Bloor_Research_TCO_vs_Oracle.pdf
dataserver -X : ASE Diagnostic Interface
03 months ago
$dataserver -X <——- For running the dataserver in sybmon mode.(starts this server as sybmon, not dataserver)
Enter password: <——- Password quine
Adaptive Server Enterprise/12.5.3/EBF 12331 ESD#1/P/Sun_svr4/OS 5.8/ase1253/1900/64-bit/FBO/Tue Jan 25 08:52:58 2005
Sybase Adaptive Server Enterprise Diagnostic Interface
Confidential property of Sybase, Inc.
Copyright 1987, 2005
Sybase, Inc. All rights reserved.
Unpublished rights reserved under U.S. copyright laws.
This software contains confidential and trade secret information of Sybase,
Inc. Use, duplication or disclosure of the software and documentation by
the U.S. Government is subject to restrictions set forth in a license
agreement between the Government and Sybase, Inc. or other written
agreement specifying the Government’s rights to use the software and any
applicable FAR provisions, for example, FAR 52.227-19.
Sybase, Inc. One Sybase Drive, Dublin, CA 94568, USA
WARNING: For use by authorized personnel only.
If you are not an employee of Sybase, Inc., or
have not been authorized by a qualified employee
of Sybase, Inc., please terminate this program now.
No servers found using directory: /data/sybase/sqlserver/12.5.3
> cat /data/sybase/sqlserver/12.5.3/ASE-12_5/ <————— We need to provide the Krg file location with cat
Shared memory regions currently cataloged:
Name Key Id Status
—————————————————————–
PROD_ASE1 0x64d28ab5 5603 Available
PROD_ASE2 0x64d28adf 5604 Available
PROD_ASE3 0x64d28add 205 Available
PROD_ASE4 0x64d28ae5 206 Available
> attach PROD_ASE3 <—————-Attaching a shared memory segment for analysis
Attaching to server PROD_ASE3, using shared memory id: 205
PROD_ASE3:active> help ?
Help text for Sybmon commands
Usage: <help | ?> [<command group name> | all]
PROD_ASE3:active> detach <————-Detaching the shared memory segment
> quit <——–Exiting from sybmon mode
you have mail in /var/mail//sybase
PROD_ASE3:active> who ?
List all active server processes, process for specified spid,
or only busy, idle or blocked processes
Usage: who [ <spid> | busy | blocked | idle ]
PROD_ASE3:active> locks ?
Display all the locks held or waited for
Usage: locks
PROD_ASE3:active> traceflags ?
List all active traceflags
Usage: traceflags [( 1 | 2 )]
PROD_ASE3:active> opentables ?
Display open tables for one or all active database processes
Usage: opentables [<spid> | <kpid> | <SYB_PROC *>]
PROD_ASE3:active> memdump ?
Dump server’s shared memory region(s) to a disk file
Usage: memdump [<file name> [[nocache | cache] [halt| nohalt] [proc | noproc] [nounused | unused]] | [full]]
The first of each argument pair is the default.
PROD_ASE3:active> stacktrace ?
Display stack trace for a server process
Usage: stack <kpid> | <spid> | <syb_proc addr in hex> | all | run
PROD_ASE3:active> status ?
Show status of shared memory and sybmon program
Usage: status
PROD_ASE3:active> status
Attached to server: PROD_ASE3
Logging: off
Display: on
Timestamplog: on
Sybmon Diagnostics:
General Diagnostics: off
Print Module Diagnostics: off
Virtual Memory Manager Diagnostics: off
Virtual Machine Diagnostics: off
Dump file mapping mode: normal
PROD_ASE3:active> version ?
Display the version of this program
Isolation Level – Summarized.
04 months ago
Isolation Level ??
=============
Data concurrency: means that many users can access data at the same time.
Data consistency: means that each user sees a consistent view of the data, including visible changes made by the user’s own transactions and transactions of other users.
Isolation : is a property that defines how/when the changes made by one operation become visible to other concurrent operations. Isolation is one of the ACID property.
Lower isolation levels increase transaction concurrency at the risk of allowing transactions to observe a fuzzy or incorrect database state. These incorrect state you need to manage at application design.
4 Isolation Levels:
===================
The ANSI/ISO SQL-92 specifications define four isolation levels:
(1) READ UNCOMMITTED.
(2) READ COMMITTED.
(3) REPEATABLE READ.
(4) SERIALIZABLE.
Lower Isolation level —> Higher concurrency, Data consistancy low, Reducing the locking overhead.
Higher Isolation Level —> Lower Concurrency, High Data Consistancy, Possible More Deadlock in multi user enviorment.
Three preventable phenomena
===========================
P1 (Dirty Read): Transaction T1 modifies a data item. Another transaction T2 then reads that data item before T1 performs a COMMIT or ROLLBACK. If T1 then performs a ROLLBACK, T2 has read a data item that was never committed and so never really existed.
P2 (Non-repeatable or Fuzzy Read): Transaction T1 reads a data item. Another transaction T2 then modifies or
deletes that data item and commits. If T1 then attempts to reread the data item, it receives a modified value or discovers
that the data item has been deleted.
P3 (Phantom): Transaction T1 reads a set of data items satisfying some
then creates data items that satisfy T1’s
same
—————————————————————————–
Isolation Level Dirty Read Nonrepeatable Read Phantom Read
——————————————————————————
Read uncommitted Possible Possible Possible
Read committed Not possible Possible Possible
Repeatable read Not possible Not possible Possible
Serializable Not possible Not possible Not possible
——————————————————————————
Sybase dataserver binary output and dbcc command.
04 months ago
When we execute dbcc sqltext without putting on traceflag 3604 and 3605
where the out put of sqltext goes? In errorlog?
No,For errorlog we have traceflag 3605.
Lets explore the RUN server file again:
/opt/sybase/ASE-15_0/bin/dataserver \
-d/opt/sybase/devices/master.dat \
-e/opt/sybase/ASE-15_0/install/PROD_ASE_DS1.log \
-c/opt/sybase/ASE-15_0/PROD_ASE_DS1.cfg \
-M/opt/sybase/ASE-15_0 \
-sPROD_ASE_DS1 > /dev/null \
-e : denoting the errorlog file where all error message and informational messages resides.
AS we know, When we are running any binary file, the output of that binary displays on the screen.
What about the output of $SYBASE/$YSBASE_ASE/bin/dataserver binary,
generally we redirect it to null device (/dev/null) as above.
Now,I am redirecting the output to file like below as in /tmp/sybaselog.out file.
/opt/sybase/ASE-15_0/bin/dataserver \
-d/opt/sybase/devices/master.dat \
-e/opt/sybase/ASE-15_0/install/PROD_ASE_DS1.log \
-c/opt/sybase/ASE-15_0/PROD_ASE_DS1.cfg \
-M/opt/sybase/ASE-15_0 \
-sPROD_ASE_DS1 > /tmp/sybaselog.out \
Run the dbcc sqltext command , the result would be display in dataserver output file, without any traceflag.
It means when we require any output on user screen and errorlog, need to enable the traceflag 3604 and 3605 respectively,
otherwise it will be display in sybase dataserver binary , out put file ,if we are redirecting it to file.
sybase@localhost ~]$ isql -Usa -SPROD_ASE_DS1
Password:
1> select @@spid
2> go
------
14
(1 row affected)
1> select name from sysdatabases
2> go
name
------------------------------------------------------------
master
model
sybsecurity
sybsystemdb
sybsystemprocs
tempdb
(6 rows affected)
1> dbcc sqltext(14)
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
1> dbcc sqltext(14)
2> go
DBCC execution completed. If DBCC printed error messages, contact a user with
System Administrator (SA) role.
1>
[sybase@localhost ~]$ tail -f /tmp/sybaselog.out
00:00:00000:00001:2011/09/28 08:51:15.11 server ASE's default unicode sort order is 'binary'.
00:00:00000:00001:2011/09/28 08:51:15.11 server ASE's default sort order is:
00:00:00000:00001:2011/09/28 08:51:15.11 server 'bin_iso_1' (ID = 50)
00:00:00000:00001:2011/09/28 08:51:15.11 server on top of default character set:
00:00:00000:00001:2011/09/28 08:51:15.11 server 'iso_1' (ID = 1).
00:00:00000:00001:2011/09/28 08:51:15.11 server Master device size: 500 megabytes, or 256000 virtual pages. (A virtual page is 2048 bytes.)
00:00:00000:00001:2011/09/28 08:51:15.11 kernel Warning: Cannot set console to nonblocking mode, switching to blocking mode.
SQL Text: SELECT fid=right(space(80)+isnull(convert(varchar(80),fid),'NULL'),3), spid=right(space(80)+isnull(convert(varchar(80),spid),'NULL'),4), status=SUBSTRING(convert(varchar(80),status),1,10), loginame=SUBSTRING(convert(varchar(80),loginame),1,8), origname=SUBSTRING(convert(varchar(80),origname),1,8), hostname=SUBSTRING(convert(varchar(80),hostname),1,21), blk_spid=right(space(80)+isnull(convert(varchar(80),blk_spid),'NULL'),8), dbname=SUBSTRING(convert(varchar(80),dbname),1,6), tempdbname=SUBSTRIN
SQL Text: select @@spid
SQL Text: select name from sysdatabases
Please let me knwo if you have any more thoughts!!
Recent Comments