Commands

set ins_by_bulk

The ins_by_bulk parameter is improved for the set command, providing faster data loading by using parallel index updates for insert statements into a datarows-locked table with non-clustered indexes. The session-level syntax is:

set ins_by_bulk {on | off}

load ... with headeronly

Dumps that are created in a version of Adaptive Server using newer features may be incompatible for loading into earlier versions that do not support these newer features. The load database and load transaction...with headeronly commands report the database features that are contained in the dump, and the target version that allows the load of such dumps.

This is an example of the output from the load transaction command for a transaction log that contains activity from an optimized dump load with parallel index updates.

1> load tran pubs2 from '/linuxstore6_eng4/DBS/2k/UpgdDowngd/pubs2.tran1.dmp' with headeronly
2> go

Backup Server session id is: 18. Use this value when executing the 'sp_volchanged' system stored procedure after fulfilling any volume change request from the Backup Server.
Backup Server: 6.28.1.1: Dumpfile name 'pubs21323503E6C  ' section number 1 mounted on disk file '/linuxstore6_eng4/DBS/2k/UpgdDowngd/pubs2.tran1.dmp'

This is a log dump of database ID 4, name 'pubs2', from Aug 23 2013  4:26AM. ASE version: lite_744746-2/Adaptive Server Enterprise/15.7/EBF XXXXX SMP SP110 /744746-2/x86_64/Enterprise Linux/. Backup
Server version: Backup Server/15.7 SP110/EBF XXXXX/P/Linux AMD Opteron/Enterprise Linux/asecarina/4347/64-bit/OPT/T. Database page size is 16384.

Log begins on page 262147; checkpoint RID=Rid pageid = 0x42950; row num = 0x8a; previous BEGIN XACT RID=(Rid pageid = 0x40003; row num = 0x1); sequence dates: (old=Aug 23 2013  4:24:28:740AM, new=Aug
23 2013  4:26:20:613AM); truncation page=272720; 9 pages deallocated; requires database with 524288 pages.

Database log version=7; database upgrade version=35; database durability=UNDEFINED.
segmap: 0x00000003 lstart=0 vstart=[vpgdevno=3 vpvpn=0] lsize=262144 unrsvd=259433
segmap: 0x00000004 lstart=262144 vstart=[vpgdevno=4 vpvpn=0] lsize=262144 unrsvd=260714

The database contains 524288 logical pages (8192 MB) and 524288 physical pages (8192 MB).
dbdevinfo: devtype=0 vdevno=3 devname=pubs2_data physname=/linuxstore6_eng3/DBS/asecarina/16k/pubs2_data.dat.16k devsize=2097152 dbdevsize=262144 vstart=0 devstat=2 devstat2=1
dbdevinfo: devtype=0 vdevno=4 devname=pubs2_log physname=/linuxstore6_eng2/DBS/asecarina/16k/pubs2_log.dat.16k devsize=2097152 dbdevsize=262144 vstart=0 devstat=2 devstat2=1

This is the SYSDATABASES row of database ID 4, name 'pubs2' from the dump header. Status:0x0205. Crdate:Aug 18 2013 11:32PM. Dumptrdate:Aug 23 2013  4:24AM. Status2:0x0000. Audflags:0x0000.
Deftabaud:0. Defvwaud:0. Defpraud:0. Def_remote_type:0. Status3:0x20000. Status4:0x4000. Durablility:1. Lobcomp_lvl:0. Inrowlen:0. Dcompdefault:1.
This is the SYSATTRIBUTES row for the  database 'pubs2' from the dump header. Flmode:0x0000.

Features found in the database or transaction dump header:

ID= 4:15.7.0.000:Database has system catalog changes made in 15.7 GA
ID= 7:15.7.0.020:Database has system catalog changes made in 15.7 ESD#02
ID=11:15.7.0.100:Database has the Sysdams catalog
ID=13:15.7.0.100:Database has indexes sorted using RID value comparison
ID=14:15.7.0.110:Log has transactions generating parallel index operations

----

The last row indicates that this dump contains activity from the optimized data load feature with parallel index updates. Such an activity can be safely recovered only in Adaptive Server Enterprise 15.7 SP110, or later. Loading this dump into an earlier version, such as Adaptive Server 15.7 SP100, is prohibited, and the load command fails.

Before creating a database or transaction dump, use sp_downgrade and sp_downgrade_esd and their associated recommendations to prepare your databases to be loadable in an earlier Adaptive Server version. Use the information from with headeronly to identify the features in a particular dump that may be incompatible with an earlier Adaptive Server version.