Anything About Sybase ASE,Replication Server & Sybase IQ.
Sybase ASE/REP Interview Questions
Sybase ASE/REP Interview Questions
Implementation of Function String in Sybase Replication Server(SRS)
03 weeks ago
These experience shared by Senior DBAs as name mentioned, Hope this will help you to understand more about function string from implementation point of view in a Replication environment:
Craig Oakley , Senior DBA.
—————————--
We used function strings when we wanted to replicate all columns to some servers, and only selected columns to other (web-facing) servers. This was particularly useful before Rep Server allowed multiple RepDefs on the same table. One concern was text columns which were not being replicated to the web-facing server: we had to create a function string to get a text pointer (we used a one-row table and just update all the text columns on top of each other, as the value was not needed on that server): failure to get a text pointer cause the DSI to go down, and we could not specify that as a condition to ignore.
Beyond this, I would imagine function strings could help specify how you want the update to be done, which could be a performance improvement. It would also allow for a different implementation at the replicate than there is at the primary (such as a table at the primary being two joined tables at the replicate).
Sukhesh Nair, Senior Sybase DBA
———————————–
We used to have a setup where data was replicated from sybase to oracle as also to a warm standby sybase server. Rep Server function strings helped in filtering data that would need to be passed to Oracle. It helped immensely in streamlining the data flow to targets by manipulating the incoming data through function string. I feel it is one of the most advanced and useful yet very less used capabilities of Sybase Rep Server.
The deterrent could be because of the complexity it would introduce to the replication system. The setup we had worked wonderfully and never gave us any major problems. Without proper monitoring (which needs to be scripted by DBAs) it used to be hard to maintain. Many of the current Rep Server administrators I see do not have adequate knowledge or experience of handling function strings.
Rey Wang , Senior Sybase DBA
————————-
You can map the delete to no op with functional string.
Partha Gogoi Senior DBA
————————-
We use function strings to transform data at the replicate..We have databases being replicated from Toronto and New York to London, Sydney and Singapore and the client ids are transformed at the replicate because, as per business requirements, the client ids are different at each site.. Of course , having a Universal client id would simplify things , but the systems and databases at each site grew independently until replication was set up and it would be a lot of rework to change all the client ids at the replicate sites
Øystein Grinaker Senior DBA
—————————
A Function String could be used to change default behaviour.
Say you delete a row in a table on PDB, but you do not want to delete the row on the RDB. Then make a change in rs_delete. You may make the rs delete just to make a logical delete by updateing a deletemarker for that spesific row.
Source : Linkedin.com
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
Sybase Interview Questions
01 month ago
Same has been updated in @http://sybaseblog.com/interviewquestions/
How can we configure the dbcc database?
How can you configure sybsecurity?
Have you ever worked on terabyte size of database? How are you taking backup for the same?
Whats the diff between MSA and WS? Can we consider MSA as a Ws?
You are not able to execute any command in ASE as tempdb is full and you cant create user defined tempdb on the fly , how will you investigate ?
What are the new features fo Sybase ASE 15?
What are the different options avilable with reorg ?
Why we require reorg ?
Suppose if every thing is fine in REplication enviorment and data is not replicating , how will you troubleshoot the same?
What is gen id in rep server?
How can you check the latency in the replication enviorment?
Whats is HA in Sybase? How can we monitor the HA status?
Sybase IQ : Architecture & Benefits
01 month ago
Sybase IQ ??
================
Sybase® IQ is a high-performance decision-support server designed specifically for data warehousing.
Sybase IQ is part of the Sybase product family that includes Adaptive Server Enterprise and SQL Anywhere. Component Integration Services within Sybase IQ provide direct access to relational and nonrelational databases on mainframe, UNIX, or Windows servers.
Architecture ??
===============
Sybase IQ architecture differs from most relational databases. Sybase IQ focuses on readers, not writers, which provides a fast query response for many users.
Data is stored in columns, not rows
Placing indexes on all columns provides a performance advantage
A large page size provides a performance advantage
A large temporary cache provides a performance advantage for most operations
Access to data occurs at the table level
Most query results focus on data at the table level
Most insertions and deletions write data for an entire table, not for a single row.
Benefits ??
=========
Sybase IQ is a decision support system optimized to deliver superior performance for mission-critical business solutions.
Intelligent query processing that use index-only access plans to process any type of query.
Ad hoc query performance on uniprocessor and parallel systems.
Multiplex capability for managing large query loads in a multi-server configuration.
Fully-flexible schema support.
Efficient query execution without query-specific tuning under most circumstances.
Fast initial and incremental loading.
Fast aggregations, counts, comparisons of data.
Parallel processing optimized for multi-user environments.
Stored procedures.
Increased productivity due to reduced query time.
Entire database and indexing stored in less space than raw data.
Reduced input/output (I/O).
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