http://goo.gl/EmKxy0

Home > ASE > sybsyntax database – Very Helpful to DBAs & Developers

sybsyntax database – Very Helpful to DBAs & Developers

December 22nd, 2010 Leave a comment Go to comments

Installation steps:

sybsyntax database require 3 to 5 mb space.

1. Create the device as below :
1> disk init name=”sybsyntaxdev”,physname=”c:sybasesybsyntaxdev.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:sybasesybsyntaxdev.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:SybaseASE-15_0scripts>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>

Check the Sybase Wiki @ sybasewiki.com
Categories: ASE Tags: ,
  1. No comments yet.
  1. No trackbacks yet.