GUI changes to create database to allow user to encrypt database on creation.

GUI changes to create index to added compression storage option.

GUI changes to create table to allow for IndexCompression and EraseResidualData options

GUI changes to Alter Trigger to allow for modification of source

GUI changes to Alter View to allow for modification of source

GUI changes to Alter Function to allow for modification of source

GUI changes to Alter Procedure to allow for modification of source

 

You can now

  "Create/Edit/ Function In Editor" & "Debug Function In Debugger"

extract / scripting changes to extract database and index for new option information.

extract indexes: had to use educated guess that status3 bit information [1024:compressed or 512:Compressed]

 

new ExtractEncryption class.  Extraction of database level encryption key names.  had to use educated guesses to decipher status3 bit information.  concluded [4096: database level encryption key]

 

CREATE OR REPLACE RULE

CREATE OR REPLACE DEFAULT

CREATE OR REPLACE TRIGGER

CREATE OR REPLACE VIEW

CREATE OR REPLACE FUNCTION

CREATE OR REPLACE PROCEDURE

 

Added new Keywords

 

Multiple Triggers --  The catalog tables  have not been enhanced to handle multiple trigger information.  Due to this, you can create multiple triggers on a table but the disable/enable property is not extractable.  The sysobjects table has columns deltrig, instrig & updtrig holding the first of its trigger type "ID" created on the table, but further triggers "ID" lost.  sysstat2 on the table object is used to determine enable/disable but unable to discover the status of all the triggers.  This causes the tree node to incorrectly show the triggers state.

 

Archive Database --  Archive Databases have been moved to there own folder only basic menu options given to its folder and all objects listed under its tree node.  No extraction/scripting done for archive database and it will not show up under the server objects to script.

// exec sp_configure 'enable compression', 1

// CREATE DATABASE ivan ON master = '20480K'

// USE ivan

//create table aieg (a int, b int, c int, d varchar(25), e date) 

//create table aieg1 (a int, b int, c int, d varchar(25), e date) with index_compression = PAGE

//create table aieg2 (a int, b int, c int, d varchar(25), e date) with index_compression = NONE

//create table aieg3 (a int, b int, c int, d varchar(25), e date) with erase residual data off

//create table aieg4 (a int, b int, c int, d varchar(25), e date) with erase residual data ON

//create table aieg5 (a int, b int, c int, d varchar(25), e date) with erase residual data off, index_compression = PAGE

//create table aieg6 (a int, b int, c int, d varchar(25), e date) with erase residual data ON, index_compression = PAGE

//create table aieg7 (a int, b int, c int, d varchar(25), e date) with erase residual data off, index_compression = NONE

//create table aieg8 (a int, b int, c int, d varchar(25), e date) with erase residual data ON, index_compression = NONE

//CREATE OR REPLACE TRIGGER trig1 ON dbo.aieg1 FOR DELETE AS SELECT * FROM dbo.aieg

//CREATE OR REPLACE TRIGGER trig2 ON dbo.aieg1 FOR DELETE AS SELECT * FROM dbo.aieg

//CREATE OR REPLACE TRIGGER trig3 ON dbo.aieg1 FOR DELETE AS SELECT * FROM dbo.aieg

//CREATE OR REPLACE TRIGGER trig4 ON dbo.aieg1 FOR INSERT AS SELECT * FROM dbo.aieg

//CREATE OR REPLACE TRIGGER trig5 ON dbo.aieg1 FOR UPDATE AS SELECT * FROM dbo.aieg

//ALTER TABLE dbo.aieg1 enable trigger trig1

//ALTER TABLE dbo.aieg1 disable trigger trig2

//ALTER TABLE dbo.aieg1 enable trigger trig3

//ALTER TABLE dbo.aieg1 disable trigger trig4

//ALTER TABLE dbo.aieg1 enable trigger trig5

//USE master

// disk init name = 'scratchdev', physname = 'C:\Sybase\data\scratchdev', vdevno = 5, size = 20480, dsync = false, vstart = 0

// disk init name = 'archievedev', physname = 'C:\Sybase\data\archievedev', vdevno = 6, size = 20480, dsync = false, vstart = 0

// disk init name = 'logdev', physname = 'C:\Sybase\data\logdev', vdevno = 7, size = 20480, dsync = false, vstart = 0

// CREATE DATABASE scratchdb ON scratchdev = 20 log on logdev = 20 

// sp_dboption scratchdb, 'scratch database', true

//CREATE ARCHIVE DATABASE archivedb ON archievedev = 20 WITH SCRATCH_DATABASE = scratchdb

// dump database pubs2 to 'C:\Sybase\data\pubs2.dmp'

// LOAD DATABASE archivedb FROM 'C:\Sybase\data\pubs2.dmp' 

// ONLINE DATABASE archivedb

/*

CREATE OR REPLACE TRIGGER trig2 

    ON dbo.aieg1 

    FOR DELETE 

 

    -- Adaptive Server has expanded all '*' elements in the following statement

 

        AS SELECT dbo.aieg.a, dbo.aieg.b, dbo.aieg.c, dbo.aieg.d, dbo.aieg.e FROM dbo.aieg

*/

/*

CREATE OR REPLACE VIEW dbo.sysquerymetrics1 

        (   uid, gid, hashkey, id, sequence, 

            exec_min, exec_max, exec_avg, elap_min, elap_max, 

            elap_avg, lio_min, lio_max, lio_avg, pio_min, 

            pio_max, pio_avg, cnt, abort_cnt, qtext) 

    AS SELECT 

        a.uid, -a.gid, a.hashkey, a.id, a.sequence, 

        convert(int, substring(b.text, charindex('e1', b.text) + 3, charindex('e2', b.text) - charindex('e1', b.text) - 4)), 

        convert(int, substring(b.text, charindex('e2', b.text) + 3, charindex('e3', b.text) - charindex('e2', b.text) - 4)), 

        convert(int, substring(b.text, charindex('e3', b.text) + 3, charindex('t1', b.text) - charindex('e3', b.text) - 4)), 

        convert(int, substring(b.text, charindex('t1', b.text) + 3, charindex('t2', b.text) - charindex('t1', b.text) - 4)), 

        convert(int, substring(b.text, charindex('t2', b.text) + 3, charindex('t3', b.text) - charindex('t2', b.text) - 4)), 

        convert(int, substring(b.text, charindex('t3', b.text) + 3, charindex('l1', b.text) - charindex('t3', b.text) - 4)), 

        convert(int, substring(b.text, charindex('l1', b.text) + 3, charindex('l2', b.text) - charindex('l1', b.text) - 4)), 

        convert(int, substring(b.text, charindex('l2', b.text) + 3, charindex('l3', b.text) - charindex('l2', b.text) - 4)), 

        convert(int, substring(b.text, charindex('l3', b.text) + 3, charindex('p1', b.text) - charindex('l3', b.text) - 4)), 

        convert(int, substring(b.text, charindex('p1', b.text) + 3, charindex('p2', b.text) - charindex('p1', b.text) - 4)), 

        convert(int, substring(b.text, charindex('p2', b.text) + 3, charindex('p3', b.text) - charindex('p2', b.text) - 4)), 

        convert(int, substring(b.text, charindex('p3', b.text) + 3, charindex('c', b.text) - charindex('p3', b.text) - 4)), 

        convert(int, substring(b.text, charindex('c', b.text) + 2, charindex('ac', b.text) - charindex('c', b.text) - 3)), 

        convert(int, substring(b.text, charindex('ac', b.text) + 3, char_length(b.text) - charindex('ac', b.text) - 2)), 

        a.text 

        FROM    sysqueryplans a, sysqueryplans b 

        WHERE (a.type = 10) and (b.type =1000) and (a.id = b.id) and a.uid = b.uid and a.gid = b.gid

*/

/*

CREATE OR REPLACE FUNCTION fullname(

        @firstname varchar(30),           

        @lastname varchar(30))

    RETURNS varchar(61)   

    AS 

        BEGIN

            declare @name varchar(61)   

            set @name = @firstname|| ' ' ||@lastname   

            RETURN @name   

        END

 

CREATE OR REPLACE PROCEDURE showind @tabname varchar (30) 

    AS

        SELECT  sysobjects.name, sysindexes.name, indid 

        FROM    sysindexes, sysobjects 

        WHERE   sysobjects.name = @tabname 

                AND sysobjects.id = sysindexes.id

*/

//sp_dboption ivan, 'select into', true

//sp_dboption ivan, 'full logging for all', true

// create table mytable (f1 int,f2 bigint not null,f3 varchar (255) null) with transfer table on

// CREATE TABLE dbo.mytable1 (f1 int NOT NULL, f2 bigint NOT NULL, f3 varchar(255) NULL)

// CREATE TABLE dbo.mytableROW (f1 int NOT NULL, f2 bigint NOT NULL, f3 varchar(255) NULL) WITH COMPRESSION = ROW

// CREATE TABLE dbo.mytablePAGE (f1 int NOT NULL, f2 bigint NOT NULL, f3 varchar(255) NULL) WITH COMPRESSION = PAGE

// CREATE TABLE dbo.mytableNONE (f1 int NOT NULL, f2 bigint NOT NULL, f3 varchar(255) NULL) WITH COMPRESSION = NONE

// CREATE TABLE dbo.mytabledmlMin (f1 int NOT NULL, f2 bigint NOT NULL, f3 varchar(255) NULL) WITH dml_logging = minimal

// CREATE TABLE dbo.mytabledmlFull (f1 int NOT NULL, f2 bigint NOT NULL, f3 varchar(255) NULL) WITH dml_logging = full 

// CREATE TABLE dbo.mytabledmlDefault (f1 int NOT NULL, f2 bigint NOT NULL, f3 varchar(255) NULL) WITH dml_logging = Default

// CREATE TABLE dbo.mytablelobcompOFF (f1 int NOT NULL, f2 bigint NOT NULL, f3 varchar(255) NULL) WITH lob_compression = off 

// CREATE TABLE dbo.mytablelobcomplevel4 (f1 int NOT NULL, f2 bigint NOT NULL, f3 varchar(255) NULL) WITH lob_compression = 4

// CREATE TABLE dbo.mytablelobcomplevel100 (f1 int NOT NULL, f2 bigint NOT NULL, f3 varchar(255) NULL) WITH lob_compression = 100

// CREATE TABLE dbo.mytablelobcomplevel101 (f1 int NOT NULL, f2 bigint NOT NULL, f3 varchar(255) NULL) WITH lob_compression = 101

//CREATE TABLE dbo.aa (a VARCHAR(25) NULL) WITH index_compression = PAGE, erase residual DATA ON, TRANSFER TABLE ON, COMPRESSION = PAGE, DML_LOGGING = FULL, LOB_COMPRESSION = 100

 

//SELECT      so.name, so.sysstat3, so.lobcomp_lvl

//FROM        dbo.sysobjects so LEFT JOIN dbo.sysindexes si ON(si.id=so.id) 

//            LEFT JOIN dbo.syssegments seg ON(si.segment = seg.segment) 

//            LEFT JOIN dbo.sysattributes attr ON(attr.object_type = 'OD' AND  so.name = attr.object_cinfo) 

//WHERE       (so.type = N'U' OR so.type = N'S') AND si.indid = 0 

//            AND USER_NAME(so.uid) = 'dbo' AND so.name like 'mytable%' 

 

//     0 – the row is not compressed.

// 1-9 – the SAP ASE server uses ZLib compression. Generally, the higher the compression number, the more the SAP ASE server compresses the LOB data, and the greater the ratio between compressed and uncompressed data (that is the greater the amount of space savings, in bytes, for the compressed data versus the size of the uncompressed data).  However, the amount of compression depends on the LOB content, and the higher the compression level , the more CPU-intensive the process. That is, level 9 provides the highest compression ratio but also the heaviest CPU usage.

// 100 – the SAP ASE server uses FastLZ compression. The compression ratio that uses the least CPU usage; generally used for shorter data.

// 101 – the SAP ASE server uses FastLZ compression. A value of 101 uses slightly more CPU than a value of 100, but uses a better compression ratio than a value of 100.

// deferred_allocation | immediate_allocation

//ivan..sp_addsegment seg1, ivan, master

//ivan..sp_addsegment seg2, ivan, master

//ivan..sp_addsegment seg3, ivan, master

//CREATE TABLE dbo.a1 (

// a varchar(25) NULL,

// b varchar(25) NULL,

// c varchar(25) NULL,

// d varchar(25) NULL,

// e varchar(25) NULL)

//    LOCK ALLPAGES

//    WITH    max_rows_per_page = 11, 

//            reservepagegap = 3, 

//            identity_gap = 3, 

//            index_compression = PAGE, 

//            erase residual data ON, 

//            TRANSFER TABLE ON, 

//            COMPRESSION = PAGE, 

//            DML_LOGGING = MINIMAL, 

//            LOB_COMPRESSION = 7

//    partition by hash (a) ( p1 on seg1, p2 on seg2, p3 on seg3)

// CREATE NONCLUSTERED INDEX idxwithpart ON dbo.a1(a, b, c) WITH index_compression = NONE ON [default] LOCAL INDEX ip1 WITH index_compression = PAGE, ip2 , ip3 WITH index_compression = NONE

// CREATE NONCLUSTERED INDEX idxwithpart1 ON dbo.a1(a, b, c) WITH index_compression = PAGE local index  ip1 with index_compression = PAGE, ip2 with index_compression = NONE, ip3 with index_compression = PAGE

// CREATE NONCLUSTERED INDEX idxwithpart2 ON dbo.a1(a, b, c) local index ip1, ip2, ip3