Anything About Sybase ASE,Replication Server & Sybase IQ.
sybanurag
(0 comments, 34 posts)
This user hasn't shared any profile information
Posts by sybanurag
XML formatting – from one line to hierarchical multilevel
0Problem: How to convert a xml file which is having data in a single time to multiline multilevel hierarchial format as below…
source_xml.xml:
———-
<header><sequence_no>12345</sequence_no><Sender_id>2341</sender_id><receipient_id><ref_no>5678</ref_no></receipient_id></header>
destination_xml.xml:
—————
<header>
<sequence_no>12345</sequence_no>
<Sender_id>2341</sender_id>
<receipient_id>
<ref_no>5678</ref_no>
</receipient_id>
</header>
Solution: Use command line tool xmllint
usage:
——
xmllint -htmlout -format source_xml.xml > destination_xml.xml
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
Adaptive Server pages
0The size of Adaptive Server‘s logical pages (2K, 4K, 8K, or 16K) determines the server’s space allocation. Each allocation page, object allocation map (OAM) page, data page, index page, text page, and so on
are built on a logical page. For example, if the logical page size of Adaptive Server is 8K, each of these page types are 8K in size. All of these pages consume the entire size specified by the size of the logical page. OAM pages have a greater number of OAM entries for larger logical pages (for example, 8K) than for smaller pages (2K).
The logical page size is a server-wide setting; you cannot have databases with varying size logical pages within the same server. All tables are appropriately sized so that the row size is no greater than the current page size of the server. That is, rows cannot span multiple pages.
Benefits of Normalization
0Normalization produces smaller tables with smaller rows:
• More rows per page (less logical I/O)
• More rows per I/O (more efficient)
• More rows fit in cache (less physical I/O)
• Searching, sorting, and creating indexes is faster, since tables are
narrower, and more rows fit on a data page.
• Index searching is often faster, since indexes tend to be narrower and
shorter.
• More tables allow better use of segments to control physical
placement of data.
• You usually have fewer indexes per table, so data modification
commands are faster.
• You usually have more tables. You can have more clustered indexes (one per table), so you get more
flexibility in tuning queries.
• Fewer null values and less redundant data, making your database
more compact.
• Triggers execute more quickly if you are not maintaining redundant
data.
Guidelines for improving I/O performance
02 months ago
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
02 months ago
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.
How to run sybase query in different databases in one run
03 months ago
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
Database load error
03 months ago
Sometime “database is in use” error encounters while loading the database, this could be due to in-memory db tables might still active for the named database. So use the dbcc commands to clear those and reload the database.
How to know what are the spids using given database
03 months ago
Use below command
dbcc dbreboot(‘report’,'dbname’)
go
It will give you all spids which are using given database.
Recent Comments