http://goo.gl/EmKxy0

Archive

Archive for the ‘Developement’ Category

Database load error

November 24th, 2011 No comments

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

November 24th, 2011 No comments

Use below command

 

dbcc dbreboot(‘report’,’dbname’)

go

It will give you all spids which are using given database.

How to remove duplicate rows from a file in unix

November 24th, 2011 No comments

use

cat <filename> | sort | uniq -c

 

Note: “sort” command is required before “uniq” because uniq command matches the next line with previous one. If file is not sorted then duplicate rows unlikely be filtered.

“Pushd, popd and cd -” unix commands

November 21st, 2011 No comments

pushd:- This command acts similar to “cd” command.

popd:- This command is used to return to previous directory.

cd -:- This command is also used to return to previous directory.

NOTE Both “popd” and “cd -” works similar but they differ primarily that “cd -” acts like a loop i.e.

                                                     cd -                  cd -       cd -      cd -

Directory X ——> Directory A ——–> Directory B ——-> Directory A ——–> B ——->A —–> B

However,

                                                popd       popd

Directory X ——> Directory A —> Directory B ——-> A———> X

Categories: ASE, Developement, Start Sybase, UNIX Tags: ,

getopts unix command

November 21st, 2011 No comments

getopts is an unix command to fetch argument and argument values. OPTARG and OPTIND are two variables to hold argument values and index.

usage:

suppose we have define command usage as below

test.ksh [-s <server name>] [-d <database name>] [-u <user name>] [-p <password>]

so usage is

while getopts s:S:d:D:u:U:p:P i

do case “$i” in

        s|S) SERVER="$OPTARG";;
        d|D) DATABASE="$OPTARG";;
        u|U) USER="$OPTARG";;
        p|P) PASS="$OPTARG";;
        ?) usage;;
   esac

done

Categories: ASE, Developement, Start Sybase, UNIX Tags: ,

Basic awk concepts

November 21st, 2011 No comments

Awk is basically a tool for text processing and report generation which is having features of programming language like C. Let see below how awk happens to be useful tool while writing shell scripts.

$ awk ‘{ print }’ /etc/passwd

When we executed awk, it evaluated the print command for each line in /etc/passwd, in order. All output is sent to stdout, and we get a result identical to catting /etc/passwd. Now, for an explanation of the { print } code block. In awk, curly braces are used to group blocks of code together, similar to C. Inside our block of code, we have a single print command. In awk, when a print command appears by itself, the full contents of the current line are printed.

$ awk ‘{ print $0 }’ /etc/passwd

In awk, the $0 variable represents the entire current line, so print and print $0 do exactly the same thing.

$ awk ‘{ print “” }’ /etc/passwd

Whenever you pass the “” string to the print command, it prints a blank line. If you test this script, you’ll find that awk outputs one blank line for every line in your /etc/passwd file. Again, this is because awk executes your script for every line in the input file. Here’s another example:

$ awk ‘{ print “hiya” }’ /etc/passwd

Print different fields – Fields are separated by colon “:”

  • ======================*

$ awk -F”:” ‘{ print $1 }’ /etc/passwd

$ awk -F”:” ‘{ print $1 ” ” $3 }’ /etc/passwd –> It will print first and third fields with a space between them.

 

[edit]========================

Reference: http://www.ibm.com/developerworks/linux/library/l-awk1/index.html

Categories: ASE, Developement, Start Sybase, UNIX Tags:

Isolation Level – Summarized.

October 10th, 2011 No comments

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 . Transaction T2
then creates data items that satisfy T1’s and commits. If T1 then repeats its read with the
same , it gets a set of data items different from the first read.

—————————————————————————–
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
——————————————————————————

Why Software is Soft?

September 22nd, 2011 No comments

why software is soft ,it’s very basis question, worth to know it..

Our computer is designed in layered in architecture, In the core (inner layer) we have all physical component(h/w).

In starting , we were directly interacting with hardware and mainly in 1 & 0 , even you can say in on/off state of a transistor. It was really very difficult process for the user’s interaction.

To reduce the hardness of physical component for interaction, we started layering h/w with piece of code.

Simply saying to remove the hardness of hardware , for making it soft for users, we used layers, as we calling now as Software.

Thanks Prof Raman for clearing my thoughts!

Basic tools for performance tuning

September 18th, 2011 No comments

I have mentioned few basic performance tuning tools used to check the the health of query and stored procedure. These tools are basically sybase commands only.

 

Sybase optimizer is basically a cost based optimizer which calculates the cost for each query ,in terms of how much I/O performs, and then decide the appropriate plan.

For calculating the cost, optimizer uses the statistic of table columns and index data which happens to be stored in the form of histogram. It then chooses the access method that the optimizer determines has the least cost.

Sybase has a command called “update statistics” to store the statistics in “systatics” table.

Syntax

—-

update statistic table_name {(column list) | (index name)]

Tools

—-

1. Set statistics io

2. set statistic time

3. set showplan

4. set noexec

5. set fmtonly

 

set statistics io

————

syntax

set statistic io on

go

Description:It will show the number of logical reads and physical reads  and total I/O reads

Table: Author scan count 1,

logical reads: (regular=465 apf=0 total=465),

physical reads: (regular=324 apf=234 total=558), apf IOs used=234

Total actual I/O cost for this command: 1023.

Total writes for this command: 0

 

set statistic time

————

syntax

set statistic time on

go

Description: It will show the time taken to parse and compile the query, execution time etc.

Parse and Compile Time 35.   (CPU ticks)

SQL Server cpu time: 35ms.

Execution Time 140.

SQL Server cpu time:14000 ms. SQL Server elapsed time: 60123ms.

 

set showplan

———–

syntax

set showplan on

go

Description: It will show the query paln of query.

 

set noexec on

———–

syntax

set noexec on

go

Description: If you want to see the query plan without executing the query then use both commands together as below

set showplan on

go

set noexec on

go

select * from Author

go

set fmtonly on

———–

syntax

set fmtonly on

go

Describe: It is used to show the query plan of a stored procedure without executing the stored procedure. Use both commands together as below.

set showplan on

go

set fmtonly on

go

exec getAuthorsData

go

Installing the jconnect for Sybase ASE.

March 24th, 2011 No comments

1. Install the Java Runtime environment or Install the JDK from www.java.com

2. Set the PATH environment variable for Java compiler and interpreter as

PATH=.:$PATH:”/bin”
export PATH

3. Download the jconnect(Sybase Driver) from Sybase and untar/unzip the package from http://www.sybase.com/products/middleware/jconnectforjdbc

4. You need to define two env variable CLASSPATH & JDBC_HOME

i)Set the JDBC_HOME as
[sybase@localhost jConnect-6_0]$ JDBC_HOME=”/home/sybase/15.5/jConnect-6_0″
[sybase@localhost jConnect-6_0]$ export JDBC_HOME
[sybase@localhost jConnect-6_0]$ echo $JDBC_HOME
/home/sybase/15.5/jConnect-6_0

ii) Set the CLASSPATH as
[sybase@localhost jConnect-6_0]$CLASSPATH=”$JDBC_HOME/classes”:”/home/sybase/15.5/jConnect-6_0/classes/jconn3.jar “:.
[sybase@localhost jConnect-6_0]$ export CLASSPATH
[sybase@localhost jConnect-6_0]$ echo $CLASSPATH
.:/home/sybase/15.5/jConnect-6_0/classes/jconn3.jar:/home/sybase/15.5/jConnect-6_0/classes

5. Install the Jconnect stored procs and tables :

For jConnect to function properly, you must install stored procedures and tables on the Adaptive Server Enterprise or ASA.

Go the $JDBC_HOME/classes
java IsqlApp -U sa -P -S jdbc:sybase:Tds:localhost.localdomain:5000 -I $JDBC_HOME/sp/sql_server15.0.sql -c go

6. If installation is successful, you need to test the installation

For testing you can check using following Applet : run from $JDBC_HOME
java sample2.SybSample Version
If everything is fine, you can get the jconnect version in output window.

7. Testing Java connectivity with Sybase ASE
[sybase@localhost ~]$ cat JdbcTest.java
import java.sql.* ;
public class JdbcTest
{
public static void main( String args[] )
{
try
{
Class.forName( “com.sybase.jdbc3.jdbc.SybDriver” ) ;
Connection conn = DriverManager.getConnection( “jdbc:sybase:Tds:localhost.localdomain:5000”, “sa”, “”) ;
Statement stmt = conn.createStatement() ;
ResultSet rs = stmt.executeQuery( “SELECT * FROM master..sysdatabases” ) ;
while( rs.next() )
System.out.println( rs.getString(1) ) ;
rs.close() ;
stmt.close() ;
conn.close() ;
}
catch( Exception e )
{ System.out.println( e ) ; }
}
}

i)Compile the code
[sybase@localhost ~]$ javac JdbcTest.java

ii)Test your code
[sybase@localhost ~]$ java JdbcTest
master
model
tempdb
sybsystemdb
sybsystemprocs
pubs2
sybsecurity