Skip to Content
avatar image
Former Member

sp__optdiag for ASE 15+ / 16

I'm currently working on updating sp__optdiag. I've started with Kevin Sherlock's version for ASE 15 (sp__optdiag/1.15.0/0/B/KJS/AnyPlat/AnyOS/15.7.x/Tue Jun 20 11:28:10 2006) and I'm reapplying the changes that I made to it when I was working at my last company. (Those involve fixing some bugs that cause problems with the looping, and extending it for new data types.) I'm testing it in ASE 16 SP02 and I've got the histogram output sorted out. I need to add the new lines output by ASE 16's optdiag, which I'll work on next.

I'd like to make it flexible by including handling for ASE 15.7, ASE 15.5, and maybe ASE 15.0. I have access to ASE 15.5 but not ASE 15.7 (which I was using in my last job when I made the changes the first time) or ASE 15.0.

I'm not sure if this request is going to be possible because there does not appear to be a way to attach a file in the forums. Could someone that has sa_role access to ASE 15.7 and / or ASE 15.0 please run the SQL below (to create and populate a table and update its statistics), run optdiag to extract its statistics, and post the results? The commands will need to be changed for ASE 15.0 to remove the types that it doesn't support.

Thanks in advance

Raymond

P.S. I got errors from optdiag in ASE 15.5/EBF 18158 SMP ESD#2/P/X64/Windows Server for the two bigdatetime columns, and bigtime and [unsigned] bigint returned incorrect values.

drop table all_types
go

create table all_types (
v1 varchar (5),
v2 varchar (5) NULL,
nv1 nvarchar (5),
nv2 nvarchar (5) NULL,
uv1 univarchar (5),
uv2 univarchar (5) NULL,
c1 char (5),
c2 char (5) NULL,
nc1 nchar (5),
nc2 nchar (5) NULL,
uc1 unichar (5),
uc2 unichar (5) NULL,
i1 int,
i2 int NULL,
i3 integer,
i4 integer NULL,
ui1 unsigned int,
ui2 unsigned int NULL,
ui3 unsigned integer,
ui4 unsigned integer NULL,
si1 smallint,
si2 smallint NULL,
usi1 unsigned smallint,
usi2 unsigned smallint NULL,
ti1 tinyint,
ti2 tinyint NULL,
uti1 unsigned tinyint,
uti2 unsigned tinyint NULL,
bi1 bigint,
bi2 bigint NULL,
ubi1 unsigned bigint,
ubi2 unsigned bigint NULL,
n1 numeric (10, 3),
n2 numeric (10, 3) NULL,
n3 numeric (10),
n4 numeric (10) NULL,
d1 decimal (10, 3),
d2 decimal (10, 3) NULL,
d3 decimal (10),
d4 decimal (10) NULL,
f1 float,
f2 float NULL,
r1 real,
r2 real NULL,
f3 float (8),
f4 float (8) NULL,
dp1 double precision,
dp2 double precision NULL,
f5 float (18),
f6 float (18) NULL,
m1 money,
m2 money NULL,
sm1 smallmoney,
sm2 smallmoney NULL,
bdt1 bigdatetime,
bdt2 bigdatetime NULL,
dt1 datetime,
dt2 datetime NULL,
sdt1 smalldatetime,
sdt2 smalldatetime NULL,
da1 date,
da2 date NULL,
bt1 bigtime,
bt2 bigtime NULL,
t1 time,
t2 time NULL,
sn1 sysname,
sn2 sysname NULL,
lsn1 longsysname,
lsn2 longsysname NULL,
vb1 varbinary (5),
vb2 varbinary (5) NULL,
b1 binary (5),
b2 binary (5) NULL,
bit1 bit,
te1 text,
te2 text NULL,
ute1 unitext,
ute2 unitext NULL,
im1 image,
im2 image NULL,
tmst timestamp)
go

create index all_types_NC_1 on all_types (v1, v2, nv1, nv2, uv1, uv2, c1, c2, nc1, nc2, uc1, uc2)
go
create index all_types_NC_2 on all_types (i1, i2, i3, i4, ui1, ui2, ui3, ui4, si1, si2, usi1, usi2, ti1, ti2, uti1, uti2, bi1, bi2, ubi1, ubi2)
go
create index all_types_NC_3 on all_types (n1, n2, n3, n4, d1, d2, d3, d4, f1, f2, r1, r2, f3, f4, dp1, dp2, f5, f6, m1, m2, sm1, sm2)
go
create index all_types_NC_4 on all_types (bdt1, bdt2, dt1, dt2, sdt1, sdt2, da1, da2, bt1, bt2, t1, t2)
go
create index all_types_NC_5 on all_types (sn1, sn2, lsn1, lsn2, vb1, vb2, b1, b2, tmst)
go

truncate table all_types
go

insert all_types values ("v1a", NULL, "nv1a", NULL, "uv1a", NULL, "c1a", NULL, "nc1a", NULL, "uc1a", NULL,
-1, NULL, -2147483648, NULL, 1, NULL, 4294967295, NULL, -32768, NULL, 65535, NULL, 1, NULL, 255, NULL, -9223372036854775808, NULL, 18446744073709551615, NULL,
-1.1, NULL, -3, NULL, -1.1, NULL, -3, NULL, 0.1, NULL, 0.1, NULL, 0.3, NULL, 0.1, NULL, 0.5, NULL, -922337203685477.5808, NULL, -214748.3648, NULL,
current_bigdatetime (), NULL, getdate (), NULL, getdate (), NULL, current_date (), NULL, current_bigtime (), NULL, current_time (), NULL,
"sn1a", NULL, "lsn1a", NULL, 0x1, NULL, 0x1, NULL, 0, "te1a", NULL, "ute1a", NULL, 0x65, NULL, NULL)

insert all_types values ("v1b", "v2b", "nv1b", "nv2b", "uv1b", "uv2b", "c1b", "c2b", "nc1b", "nc2b", "uc1b", "uc2b",
-1, 2, -3, 4, 1, 2, 3, 4, -1, 2, 1, 2, 1, 2, 1, 2, -1, 2, 1, 2,
-1.2, 2.2, -3, 4, -1.2, 2.2, -3, 4, 0.1, -0.2, 0.1, -0.2, 0.3, -0.4, 0.1, -0.2, -0.5, 0.6, 9.99, -99.9999, 9.99, -99.9999,
current_bigdatetime (), current_bigdatetime (), getdate (), getdate (), getdate (), getdate (), current_date (), current_date (), current_bigtime (), current_bigtime (), current_time (), current_time (),
"sn1b", "sn2b", "lsn1b", "ls2b", 0x11, 0x22, 0x11, 0x22, 1, "te1b", "te2b", "ute1b", "ute2b", 0x65, 0x66, NULL)

insert all_types values ("v1c", NULL, "nv1c", NULL, "uvc1c", NULL, "c1c", NULL, "nc1c", NULL, "uch1c", NULL,
-1, NULL, -2147483648, NULL, 1, NULL, 4294967295, NULL, -32768, NULL, 65535, NULL, 1, NULL, 255, NULL, -9223372036854775808, NULL, 18446744073709551615, NULL,
-1.1, NULL, -3, NULL, -1.1, NULL, -3, NULL, 0.1, NULL, 0.1, NULL, 0.3, NULL, 0.1, NULL, 0.5, NULL, -922337203685477.5808, NULL, -214748.3648, NULL,
"1 Jan 0001 00:00:00.000000", NULL, "1 Jan 1773 00:00:00.000", NULL, "1 Jan 1900 00:00:00.000", NULL, "1 Jan 0001", NULL, "00:00:00.000000", NULL, "00:00:00.000", NULL,
"sn1c", NULL, "lsn1c", NULL, 0x111, NULL, 0x111, NULL, 0, "te1c", NULL, "ute1c", NULL, 0x65, NULL, NULL)

insert all_types values ("v1d", "v2d", "nv1d", "nv2d", "uvc1d", "uvc2d", "c1d", "c2d", "nc1d", "nc2d", "uch1d", "uch2d",
1, 2147483647, -2147483648, -1, 1, 4294967295, 1, 4294967295, -32768, 32767, 1, 65535, 1, 255, 255, 1, -9223372036854775808, 9223372036854775807, 18446744073709551615, 1,
-1.1, NULL, -3, NULL, -1.1, NULL, -3, NULL, 0.1, NULL, 0.1, NULL, 0.3, NULL, 0.1, NULL, 0.5, -0.6, -922337203685477.5808, 922337203685477.5807, -214748.3648, 214748.3647,
"31 Dec 9999 23:59:59.999999", "30 Jun 0999 11:59:59.999999", "31 Dec 9999 23:59:59.999", "30 Jun 1899 11:59:59.999", "6 Jun 2079 23:59", "30 Jun 1989 11:59:59.999", "31 Dec 9999", "30 Jun 1899", "23:58:59.999999", "11:59:59.999999", "23:58:59.999999", "11:59:59.999999",
"sn1d", "sn2d", "lsn1d", "ls2d", 0x1111, 0x2222, 0x1111, 0x2222, 1, "te1d", "te2d", "ute1d", "ute2d", 0x65, 0x66, NULL)
go

update all statistics all_types
go

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Oct 31, 2016 at 09:52 PM

    I don't have access to a 15.0 dataserver.

    I do have access to a 15.7 dataserver.

    For 15.7, optdiag generates 3100+ lines of output ... way too much for this POS Q&A medium. Send me an email (see my profile) and I can forward you the 15.7 optdiag output.

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 04, 2016 at 01:39 AM

    Raymond,

    Thanks for your work on sp__optdiag.

    As Mark points out, this forum is no longer useful for exchange of sharing experience with ASE. I"m no longer contributing to SCN after the really pointless and awful changes made to this platform (this post excepted).

    I have access to an instance of ASE 15.0 if you are interested in the results of your script.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Kevin,

      I'm honoured to stand on the shoulders of a giant.

      I'm still interested in what the output from ASE 15.0's optdiag looks like for the table.

      By looking at another track for sp__optdiag I've spotted that there is an option to insert up to ten attachments in a post and also to insert links (either to an attachment or to something else). So that cuts down on needing to include an email address for passing large bits of text.

      Cheers

      Raymond


      test.txt (4 B)
  • avatar image
    Former Member
    Nov 10, 2016 at 07:24 PM

    Here is the ASE 15.5 version of sp__optdiag

    Although it is aimed at ASE 15.5 it will also work in ASE 15.7+ but it won't show the extra output that optdiag for those versions outputs. I'll be working on versions for ASE 15.7 and ASE 16.0.

    The only issues I know about are some problems with "Large I/O Efficiency" that I can't isolate.

    I've done as much testing as I can given the limitations of trying to invent data, so there still may be something that needs to be tweaked. Please report any issues in this sub-track of this topic.

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Nov 30, 2016 at 05:17 PM

    Here is the ASE 15.7 version of sp__optdiag. If you have any issues, please post a comment in this sub-track of this topic.

    Add comment
    10|10000 characters needed characters exceeded