http://goo.gl/EmKxy0

Archive

Archive for the ‘Developement’ Category

SAP D&T Academy Video:How to create login in Sybase ASE

November 22nd, 2012 No comments

Please follow the following video for creating login in Sybase ASE

 

SAP D&T Academy Video :How to set database options in sybase ASE

November 22nd, 2012 No comments

Please follow the following video for setting sybase database options.

 

Nested Transaction

August 12th, 2012 No comments

NOTE: There must be at least one commit tran executed for each tran statement executed. But, regardless of how many begin tran/commit tran pairs appear to be nested in the code, there is only one transaction.

 

Example:

begin tran

change 1

begin  tran

change 2

commit tran

commit tran

In above pseudocode, there are two nested begin tran statement. Despite, the fact that it appears that there are two transactions (one inside another), there is actually only one open transaction. Thus, the transaction is active until the second commit tran statement is executed.

 

Example:

begin tran

change 1

begin tran

change 2

begin tran

change 3

commit tran

change 4

commit tran

change 5

rollback tran

In this example, none of the five changes would remain in the data after the rollback; again, although it looks like there are three levels of nesting, and it looks like changes 2,3,4, and 5 are fully bounded by begin/commit pairs, there is really only one transaction, and all work must complete in full or not at all. In fact, the transaction shown above is functionally identical to:

begin tran

change 1

change 2

change 3

change 4

change 5

rollback tran

Useful Sybase ASE Tools available for free download…

May 24th, 2012 No comments

I have started some time ago to distribute some tools I have created to assist me in my professional life as full-time production dba/sybase migration analyst/sybase database architect.

Some are years old, others are very fresh (the last developed two days ago).  There are additional tools that I will publish in the near future.  The aim of all of the tools is to make the life of a DBA/Analyst/Architect a bit easier.  Although I do by no means claim the position of the ASE tool guru – there are other tools available out there, some pretty costly.  The tools may be of some use for those having a particular need in view and lacking an alternative.

Not to repeat myself, I provide you the link to the description of each particular tool here.

There are so far 4 tools:  1st is aimed to use/learn the MDA tables better.  2nd to create pin-pointed monitoring of an ASE server a thing for novices, 3d aimed to make parsing of Sybase Sysmon reports a bit more convenient, and the last to be able to reuse all those handy queries DBAs accumulate over years with more ease.

Below are the tools, with screen-shots  & links.

1.  ASE Monitoring (MDA) Tables Assistant

2.  A Simple Graphical Monitor for Sybase ASE

3.  ASE System Report (Sysmon) Parser

4.  ASE Frequent QueryAssistant

You are welcome to use them and post comments.

ps.  I wish to thank the hosts of the present blog for the opportunity to use it to make the tools available to a greater public.

It was my pleasure to share.

Andrew T.M.

Inserting data into a data-only-locked heap table

January 5th, 2012 No comments

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

January 5th, 2012 No comments

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

Guidelines for improving I/O performance

December 8th, 2011 No comments

The major guidelines for improving I/O performance in Adaptive Server are as follows:

• Spreading data across disks to avoid I/O contention.

• Isolating server-wide I/O from database I/O.

• Separating data storage and log storage for frequently updated databases.

• Keeping random disk I/O away from sequential disk I/O.

• Mirroring devices on separate physical disks.

• Partitioning tables to match the number of physical devices in a segment.

Logical or Database Devices and Physical Devices

December 7th, 2011 No comments

Distinctions between logical or database devices and physical devices:
———————————————————————-
• The physical disk or physical device is the actual hardware that stores the data.

• A database device or logical device is a piece of a physical disk that has been initialized (with the disk init command) for use by Adaptive Server. A database device can be an operating system file, an entire disk, or a disk partition.

• A segment is a named collection of database devices used by a database. The database devices that make up a segment can be located on separate physical devices.

• A partition is block of storage for a table. Partitioning a table splits it so that multiple tasks can access it simultaneously. When partitioned tables are placed on segments with a matching number of devices, each partition starts on a separate database device.

sp_helpdevice –> To get information about devices
sp_helpsegment –> To get information about segments
sp_helpartition –> To get information about partitions.

What’s in YOUR Architecture?

December 4th, 2011 No comments

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

How to run sybase query in different databases in one run

November 24th, 2011 No comments

I would suggest to run SQL via shell script as below.

database.txt

————

database1

database2

database3

 

query.txt

———

select col1, col2 from table

go

write shell script

multiple_database_query.sh

————————–

while read LINE

do

database=$LINE

isql -S “” -D “${database}” -i “query.txt”

done < database.txt