Sybase Blog -Anything About Sybase ASE,REP,IQ.

Wednesday, December 29, 2010

Vi Editor – Part 2

Problem 6
———
How to copy range of lines from a file to another?

Solution 6
———-
Suppose i want to copy 50 lines from line number 15 to 64, then open file in vi editor in command mode then follow below command
:15,64w file2.txt

Additionally,
For single line –> :10w file2.txt
For current line where cursor is positioned –> : .w file2.txt
For last line –> :$w file2.txt
For current line to end –> : .,$w file2.txt

Problem 7
———-
What are repeat factor and how to use in navigation?

Solution 7
———
k — move cursor up
j — move cursor down
h — move cursor left
l — move cursor right

if want to move 5 lines up then use “5k” this is called repeat factor.

Problem 8
———
how to navigate among words in a line?

Solution 8
———-


Problem 9
———
How to do “copy and paste” in vi editor?

Solution 9
———-
Open a file in vi editor in command mode,
y — copy single character
yy — copy current line
15yy — copy current line and 14 lines below

p – paste text on right
P – paste text on left

NOTE: p and P paste text on right and left when delete single character of line. However, same keys paste “below” and “above” when delete complete line.

Problem 10
————
How to join lines?

Solution 10
————
Use “J” in command mode. Additionally, if want to join current line and 5 lines below current line then use “6J” (called repeat factor)

Problem 11
———–
How to discard all changes in a line before moving away from the line.

Solution 11
———–
Use “U” in command mode

Problem 12
———–
How to repeat last command?

Solution 12
———–
Use “.” in command mode. For e.g. if you have deleted 3 lines using “3dd” then you can delete 3 lines again and again by pressing “.”

Vi Editor – Part 1

Basic Vi Editor commands
————————
Problem 1
———
How to do the undo in Vi editor?

Solution 1
———-
Go to command mode and press “u” to undo the last action.

Problem 2
———
How to convert lines into comments?

Solution 2
———
Use “I” and “A”
“I” inserts at the beginning of line.
“A” appends at the end of line.
So press “I” and type /* then [Escape] and then press “A” and type */ then [Escape]. Due to this each line can be converted to comment.

Problem 3
———
How to insert lines above and below the current line without placing the cursor at the start and end of line?

Solution 3
———-
Use “o” and “O”
open a file in command mode and press “o” which inserts a new line below the current line.
Open a file in command mode and press “O” which inserts a new line above the current line.

Problem 4
———
What is the difference between s,S,r,R in command mode?

Solution 4
———
r –> Replaces a single character with one character.
R –> Replaces all text on the right of the cursor position.
s –> Replaces a single character with many.
S –> Replaces the entire line irrespective of cursor position.

Problem 5
———
How to save and quit in one character?

Solution 5
———
Use “:x” in command mode.

Performance Tuning – Good For Developers – Part 2

Scene 1 — If you are suppossed to search range of values then use clustered index because in range search values will be at consecutive location in sorted order after clustered index creation. So effort for engine would be reduced.

Scene 2 — Use stored procedure instead of individual queries because
- It reduces the network traffic
- Query plan got created once for same query which could be re-used instead of creating again and again.
- Stored procedure can be invoked by passing different parameters instead of sending newly created query again and again.

Scene 3 – Make sure there are no table scan on large tables.

Performance Tuning – Good For Developers – Part 1

Performance Tuning – Part 1

1. Indexes improve select performance.

2. While writing queries few things must be considered – Use operators =,>,<,>=,<=,like,between. 3. Replace the BETWEEN with >= and <= operators because BETWEEN in turn converted to mentioned oprators. So with we can reduce one step.

4. Don’t use functions in the WHERE clause. For e.g. select col1 from Table1 where upper(col1) = col2 — If we are having index created on columns col1 and col2 then because of function query engine will not use the index in select operation.

5. Similarly don’t use the mathematical expression in WHERE clause. For e.g. select col1 from Table1 where (col1 * 3) = col2

6. One of the most common pitfall is mismatch in datatype on both side of join. for e.g. select col1 from table1 where col1=col2 — if datatype of col1 and col2 are different then it reduces the performance of query.

sybsyntax database – Very Helpful to DBAs & Developers

Installation steps:

sybsyntax database require 3 to 5 mb space.

1. Create the device as below :
1> disk init name=”sybsyntaxdev”,physname=”c:\sybase\sybsyntaxdev.dat”,size=”200M”
2> go
1> sp_helpdevice sybsyntaxdev
2> go
device_name physical_name
description
status cntrltype vdevno vpn_low vpn_high
———— ————————–
—————————————————————————————–
—— ——— —— ——- ——–
sybsyntaxdev c:\sybase\sybsyntaxdev.dat
file system device, special, dsync off, directio on, physical disk, 200
.00 MB, Free: 200.00 MB
2 0 6 0 102399

(1 row affected)
dbname size allocated vstart lstart
—— —- ——— —— ——

(1 row affected)
(return status = 0)
2. Create the database sybsyntax on above device.

1> create database sybsyntax on sybsyntaxdev=”200M”
2> go
CREATE DATABASE: allocating 102400 logical pages (200.0 megabytes) on disk
‘sybsyntaxdev’ (102400 logical pages requested).
Database ‘sybsyntax’ is now online.

3. Install the sybsyntax database with the script as below
C:\Sybase\ASE-15_0\scripts>isql -Usa -iins_syn_sql -w9999 -oins_syn_sql.out
Password:
4. Check the syntax help, using sp_syntax for any sql keyword

1> sp_syntax
2> go
Msg 17970, Level 16, State 1:
Server ‘TESTPC’, Procedure ‘sp_syntax’, Line 74:
sp_syntax provides syntax help for Sybase products.
Msg 17971, Level 16, State 1:
Server ‘TESTPC’, Procedure ‘sp_syntax’, Line 78:
These modules are installed on this Server:

Module
——————–
ESP
System Procedure
Transact-SQL
UNIX Utility
Windows NT Utility
dbcc Procedure

Msg 17972, Level 16, State 1:
Server ‘TESTPC’, Procedure ‘sp_syntax’, Line 85:
Usage: sp_syntax command [, module [, language]]
(return status = 0)
1> sp_syntax “dbcc”
2> go
Syntax Help

——————————————————————————
System Procedure
sp_plan_dbccdb – Recommends suitable sizes for new dbccdb and dbccalt
databases, lists suitable devices for dbccdb and dbccalt,
and suggests a cache size and a suitable number of
worker processes for the target database.
sp_plan_dbccdb [dbname]

Transact-SQL
dbcc checkalloc [(database_name [, fix | nofix])]
dbcc checkcatalog [(database_name)]
dbcc checkdb [(database_name [, skip_ncindex])]
dbcc checkstorage [(database_name)]
dbcc checktable({table_name|table_id}[, skip_ncindex])
dbcc checkverify [(database_name)]
dbcc complete_xact (xid, {“commit” | “rollback”})
dbcc forget_xact (xid)
dbcc dbrepair (database_name, dropdb)
dbcc engine( {offline , [enginenum] | “online” })
dbcc fix_text ({table_name | table_id})
dbcc indexalloc ({table_name | table_id}, index_id
[, {full | optimized | fast | null}
[, fix | nofix]])
dbcc rebuild_text (table [, column
[, text_page_number]])
dbcc reindex ({table_name | table_id})
dbcc tablealloc ({table_name | table_id}
[, {full | optimized | fast | null}
[, fix | nofix]])|
dbcc { traceon | traceoff } (flag [, flag ... ])
dbcc tune ( { ascinserts, {0 | 1 } , tablename |
cleanup, {0 | 1 } |
cpuaffinity, start_cpu {, on| off } |
des_greedyalloc, dbid, object_name,
” { on|off }” |
deviochar vdevno, “batch_size” |
doneinproc { 0 | 1 } |
maxwritedes, writes_per_batch } )

grant dbcc – Assigns permissions for dbcc commands
grant dbcc {dbcc_command [on] {all | database_name}]
[, dbcc_command [on {all | database}],…]}
to {user_list | role_list}

revoke dbcc – revokes permissions for dbcc commands
revoke dbcc {dbcc_command [on] {all | database_name}]
[, dbcc_command [on {all | database}],…]}
from {user_list | role_list}

dbcc Procedure
sp_dbcc_alterws – Changes the size of the specified workspace to a specified
value, and initializes the workspace.
sp_dbcc_alterws dbname, wsname, “wssize[K|M]“

sp_dbcc_configreport – Generates a report that describes the configuration
information used by the dbcc checkstorage operation for the
specified database.
sp_dbcc_configreport [dbname]

sp_dbcc_createws – Creates a workspace of the specified type and size on the
specified segment and database.
sp_dbcc_createws dbname, segname, [wsname], wstype,
“wssize[K|M]“

sp_dbcc_deletedb – Deletes from dbccdb all the information related to the
specified target database.
sp_dbcc_deletedb [dbname]

sp_dbcc_deletehistory – Deletes the results of dbcc checkstorage operations
performed on the target database before the specified date and time.
sp_dbcc_deletehistory [cutoffdate [, dbname]]

sp_dbcc_differentialreport – Generates a report that highlights the changes
in I/O statistics and faults that took place between two dbcc
operations.
sp_dbcc_differentialreport [dbname [, objectname]],
[db_op] [, "date1" [, "date2"]]

sp_dbcc_evaluatedb – Recomputes configuration information for the target
database and compares it to the current configuration information.
sp_dbcc_evaluatedb [dbname]

sp_dbcc_faultreport – Generates a report covering fault statistics for the
dbcc checkstorage operations performed for the specified object
in the target database on the specified date.
sp_dbcc_faultreport [report_type [, dbname
[, objectname [, date ]]]]

sp_dbcc_fullreport – Runs sp_dbcc_summaryreport, sp_dbcc_configreport,
sp_dbcc_statisticsreport, and sp_dbcc_faultreport short for
database..object_name on or before the specified date.
sp_dbcc_fullreport [dbname [, objectname [, date]]]

sp_dbcc_runcheck – Runs dbcc checkstorage on the specified database, then
runs sp_dbcc_summaryreport or a report you specify.
sp_dbcc_runcheck dbname [, user_proc]

sp_dbcc_statisticsreport – Generates an allocation statistics report on the
specified object in the target database.
sp_dbcc_statisticsreport [dbname [, objectname
[, date]]]

sp_dbcc_summaryreport – Generates a summary report on the specified database.
sp_dbcc_summaryreport [dbname [, op_name]]

sp_dbcc_updateconfig – Updates the dbcc_config table in dbccdb with the
configuration information of the target database.
sp_dbcc_updateconfig dbname, type, “str1″ [, "str2"]

(return status = 0)
1> sp_syntax “select”
2> go
Syntax Help

——————————————————————————
Transact-SQL
select – Retrieves rows from database objects.
select ::=
select [ all | distinct ] select_list
[into_clause]
[from_clause]
[where_clause]
[group_by_clause]
[having_clause]
[order_by_clause]
[compute_clause]
[read_only_clause]
[isolation_clause]
[browse_clause]
[plan_clause]
select_list ::=
For details, see “Keywords and options” in the SAG
into_clause ::=
into [[database.]owner.]table_name
[ lock {datarows | datapages | allpages } ]
[ with into_option [, into_option] …]
into_option ::=
| max_rows_per_page = num_rows
| exp_row_size = num_bytes
| reservepagegap = num_pages
| identity_gap = gap
from_clause ::=
from table_reference [,table_reference]…
table_reference ::=
table_view_name | ANSI_join
table_view_name ::=
[[database.]owner.] {table_name | view_name}
[as] [correlation_name]
[index {index_name | table_name }]
[parallel [degree_of_parallelism]]
[prefetch size ][lru | mru]}
[holdlock | noholdlock]
[readpast]
[shared]
ANSI_join ::=
table_reference join_type join table_reference join_condition
join_type ::= inner | left [outer] | right [outer]
join_conditions ::= on search_conditions
where_clause ::=
where search_conditions
group_by_clause ::=
group by [all] aggregate_free_expression
[, aggregate_free_expression]…
having_clause ::=
having search_conditions
order_by_clause ::=
order by sort_clause [, sort_clause]…
sort_clause ::=
{ [[[database.]owner.]{table_name.|view_name.}]column_name
| select_list_number | expression }
[asc | desc]
compute_clause ::=
compute row_aggregate(column_name)
[, row_aggregate(column_name)]…
[by column_name [, column_name]…]
read_only_clause ::=
for {read only | update [of column_name_list]}
isolation_clause ::=
at isolation
{ read uncommitted | 0 }
| { read committed | 1 }
| { repeatable read | 2 }
| { serializable | 3 }
browse_clause ::=
for browse
plan_clause ::=
plan “abstract plan”

(return status = 0)
1>