New Features

New Features for DBA prospective:-

• Now We can find logins details with similar pattern as given below
• shrink tran log
• For quick create database with option async_init
• We can drop more than one database at a time
• Now we can create login both sp_addlogin and create login
• on line reorg rebuild
• Faster reorg rebuild
• reorg defrag
• Faster update stat
• hash-based statistics
• deferred_allocation
• Transaction log details function 
• defrag_status
• New commands ASE 15.7 ESD #2

===============================================================

1> sp_displaylogin 'sa%'
2> go

Suid Loginname Fullname Default Database Default Language Auto Login Script Locked Date of Last Password Change Password expiration interval Password expired Minimum password length Maximum failed logins Current failed login attempts Authenticate with Configured Authorization Login Password Encryption Last login date Exempt inactive lock

------------ ---------------------- -------------------------- ------------------------------------ ------------------------------------------ -------------------------------------------- ---------------- ------------------------------------------------------------------ ------------------------------------------------------------------ ------------------------------------------ -------------------------------------------------------- ---------------------------------------------------- -------------------------------------------------------------------- -------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------ ---------------------------------------- --------------------------------------------------

1 sa NULL master NULL NULL NO Mar 5 2013 3:04AM 0 NO 6 0 NULL AUTH_DEFAULT sa_role(default ON); sso_role(default ON); oper_role(default ON); sybase_ts_role(default ON); mon_role(default ON); sa_serverprivs_role(default ON) SHA-256 Mar 11 2013 11:59PM 0

3 sap NULL master NULL NULL NO Feb 18 2013 5:46AM 0 NO 6 0 0 AUTH_DEFAULT NULL SHA-256 NULL 0

4 sap_market NULL master NULL NULL NO Feb 18 2013 5:49AM 0 NO 6 0 0 AUTH_DEFAULT NULL SHA-256 NULL 0

==============================================================
shrink tran log:-

2> alter database testdb log off uatlog01=5
3> go

Removing 1280 pages (5.0 MB) from disk 'uatlog01' in database 'testdb'.

================================================================
=
For quick create database with option async_init

1> create database asy_db on uatdata01=10 log on uatlog01=5 with async_init

2> go

CREATE DATABASE: allocating 2560 logical pages (10.0 megabytes) on disk 'uatdata01' (2560 logical pages requested).

CREATE DATABASE: allocating 1280 logical pages (5.0 megabytes) on disk 'uatlog01' (1280 logical pages requested).

Database 'asy_db' is now online.

======================================================================

We can drop more than one database at a time

1> drop database without_sy_db,asy_db
2> go
===================================================================
Now we can create login both 'sp_addlogin' and 'create login'

1> create login bloom with password '@bloom09'
2> go
1>

and

1> sp_addlogin tester,'@tester45'
2> go
Password correctly set.
Account unlocked.
New login created.
(return status = 0)

=====================================================================
.> sp_configure 'enable compression',1
2> go
Checked out License : ASE_COMPRESSION as OK
Parameter Name Default Memory Used Config Value Run Value Unit Type
------------------------------------------------------------ ---------------------- ---------------------- ------------------------ ------------------------ ---------------------------------------- ----------------------------------------
enable compression 0 0 1 1 switch dynamic
(1 row affected)

1> create database crab on uatdata01=10 log on uatlog01=5 with compression=page,lob_compression=100,inrow_lob_length=5
2> go
CREATE DATABASE: allocating 2560 logical pages (10.0 megabytes) on disk 'uatdata01' (2560 logical pages requested).
CREATE DATABASE: allocating 1280 logical pages (5.0 megabytes) on disk 'uatlog01' (1280 logical pages requested).
Database 'crab' is now online.
============================================================

on line reorg rebuild

1> reorg rebuild tot with online
2> go
Beginning REORG REBUILD of table 'tot'.
Msg 16099, Level 16, State 1:
Server 'uat_155', Line 1:
ONLINE REORG on 'tot' failed as it cannot find a unique index on the table.
1> create unique index u_id on tot(b)
2> go
1> reorg rebuild tot with online
2> go
Beginning REORG REBUILD of table 'tot'.
Non-clustered index (index id = 2) is being rebuilt.
(4 rows affected)
REORG REBUILD of table 'tot' completed.
1>

reorg defrag table_name [partition {partition_list}]

   [with {time = hh:mm| resume | skip_compact_extents [= pct_value]}]

It reorganizes each partition list or partition in the table while allowing concurrent reads or writes on the data being reorganized

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00641.157060/doc/html/car1386371283553.html

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00641.1572/html/new_features/new_features9.htm

========================================================================

Faster reorg rebuild

Faster update stat

deferred_allocation

1> create table fild_def ( id int,group_def char(120))
2> go
1> sp_spaceused fild_def
2> go
 name             rowtotal         reserved         data     index_size           unused    
 ---------------- ---------------- ---------------- -------- -------------------- ------------
 fild_def         0                32 KB            4 KB     0 KB                 24 KB      

(1 row affected)
(return status = 0)
1> create table fild_def_1 ( id int,group_def char(120))
2> with deferred_allocation
3> go
1> sp_spaceused fild_def_1
2> go
 name                 rowtotal         reserved         data     index_size           unused    
 -------------------- ---------------- ---------------- -------- -------------------- ------------
 fild_def_1           0                0 KB             0 KB     0 KB                 0 KB      

(1 row affected)
(return status = 0)
1>
2>
3> insert fild_def_1 values(1,'wew')
4> go
(1 row affected)
1> sp_spaceused fild_def_1
2> go
 name                 rowtotal         reserved         data     index_size           unused    
 -------------------- ---------------- ---------------- -------- -------------------- ------------
 fild_def_1           1                28 KB            4 KB     0 KB                 20 KB      

(1 row affected)
================================================================

hash-based statistics

update index statistics authors with hashing

Running hash-based statistics should require less time to run sort-based statistics, increasing the amount of work you can accomplish during a maintenance window.

Because hash-based statistics require less procedure cache, you may be able to run update statistics on data-only-locked tables outside the maintenance window since the tempdb buffer cache, which usually uses the default data cache, is typically much larger than the procedure cache.

Generally, hash-based statistics do not require large tempdb disk allocations. If you previously increased the size of tempdb to accommodate large sorts from update statistics, you may be able to redeploy this space

===============================================================

Transaction log details function:- loginfo

display transaction log information.

select loginfo(dbid, 'database_has_active_transaction'),

       loginfo(dbid, 'oldest_active_transaction_pct'),

       loginfo(dbid, 'oldest_active_transaction_spid'),

       loginfo(dbid, 'can_free_using_dump_tran'),

       loginfo(dbid, 'is_stp_blocking_dump'),

       loginfo(dbid, 'stp_span_pct')

has_act_tran OAtran_spid Act_log_portion_pct dump_tran_free_pct is_stp_blocking stp_span_pct log_occupied_pct

------------ ------------ ------------------- ------------------ --------------- ------------ ---------------

     1            14               17                 7                 0             25            32

The function returns the transaction log information:

1 active transaction

14 is the SPID of the oldest transaction

17 percent of the log that is occupied by an active transaction

7 percent of the transaction log that can be freed by using the dump transaction command

0 blocking secondary truncation points

25 percent of the log that is occupied by the span of the secondary truncation point

32 percent of the log that is occupied

Viewing the Oldest Active Transactions:-

select top 3 convert(numeric(3,0),

      loginfo(db_id(), ‘xactspanbyspid’, t.spid)) as XACTSPAN,

          convert(char(4), t.spid) as SPID,  

          convert(char(20), t.starttime) as STARTTIME,  

          convert(char(4), p.suid) as SUID,  

          convert(char(15), p.program_name) as PROGNAME,  

          convert(char(15), p.cmd) as COMMAND,  

          convert(char(16), p.hostname) as HOSTNAME,  

          convert(char(16), p.hostprocess) as HOSTPROCESS  

from master..systransactions t, master..sysprocesses p  

where t.spid = p.spid  

order by XACTSPAN desc      

XACTSPAN SPID     STARTTIME     SUID PROGRAM COMMMAND  HOSTNAME  HOSTPROCESS

-------- ---- ----------------- ---- ------- -------- ---------- -----------

  38      19  Aug 5 2013 12:20AM  1    ISQL   WAITFOR linuxstore4  26141         

  20      20  Aug 5 2013 12:20AM  1    ISQL   WAITFOR linuxstore2  23467         

  10      21  Aug 5 2013 12:21AM  1    ISQL   WAITFOR linuxstore6  4971          

(return status =0)   

defrag_status:-

Returns metrics of any defragmentation operation that is started or ongoing on the named object or partition.

Ex:-

defrag_status on the table mymsgs:
select defrag_status(db_id(), object_id('mymsgs'))
If defragmentation has not yet been performed, the output is:
------------------------------------------------------------------
frag index=0.20, pct defrag=0, pages defrag=0, pages gen=0, 
pages tbd=1174, last run=, executing=0, elapsed mins=0 
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00641.1572/html/new_features/new_features49.htm

=======================================================================

http://www.sybase.com/detail?id=1080354

====================================================================

affected ASE 15.7 ESD #2

Changed Commands

Command	Change
alter database	noasync_init – Indicates that you are extending a database, and that Adaptive Server initializes the extended space asynchronously
alter table	with immediate_allocation – creates regular, nondeferred tables. split partition – redistributes data to two or more partitions. merge partition – combines the data from two or more merge-compatible partitions into a single partition. move partition – moves a partition (and its index) to a specified segment. drop column – drops columns from a table without performing a data copy. noasync_init – indicates the database is initialized synchronously.
create database	noasync_init – indicates the database is initialized synchronously.
create index	Allows you to issue a parallel form of create index that uses the query execution engine to more efficiently execute the command.
create table	with deferred_allocation – creates deferred tables. with immediate_allocation – creates regular, nondeferred tables.
dump database	Dumps a database according to the settings in the dump configuration file.
grant	as pred_name – the name of the predicate grantby grantor – indicates the grantor who grants permission to the user or role.
grant role	where pred_expression – The SQL condition that must be satisfied when the named role is activated.
load database	New parameters: listonly=load_sql – generates a sequence of load database and load transaction SQL statements to restore a database to a specified point in time. until_time – the database is restored up to this date and time. listonly=create_sql – generates a sequence of create database andalter database SQL statements.
load transaction	listonly=create_sql – generates a sequence of create database and alter database SQL statements.
merge	merge target tables may include triggers.
reorg rebuild	with online – allows you to reorganize your data without taking it offline
set	materialized_view_optimization – determines which precomputed result sets are considered during query optimization mon_stateful_history – when disabled, queries to the historical monitoring tables return all rows in the table buffer. When enabled, queries to the historical monitoring tables return only rows that were added to the tables since mon_stateful_history was disabled. show_transformed_sql – displays the SQL text for statements during the Adaptive Server preprocessing phase
update statistics	no hashing – uses the sort-hashing algorithm from versions of Adaptive Server earlier than 15.7 ESD #2. partial hashing – (the default) Adaptive Server uses hashing for low unique count domains.

update index statistics, update statistics, and update all statistics include the print_progress parameter, which allows these commands to display progress messages.

========================================================================