sp__optdiag for ASE 15+ / 16

[Former Member]

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

[kevin_sherlock]

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.

[simon_ogden]

Agreed, I attempted one use of this new setup yesterday and have come to the same conclusion. The old SCN with its layout, presentation and notification setup was probably the most usable part of the entire sap.com website. The one single problem with it was the paragraph formatting on long discussions and this could have been easily corrected.

This completely unnecessary replacement is of no use and is pretty much unusable as a technical forum or a knowledge base.

[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

[Former Member]

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.

[Former Member]

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.

[Former Member]

I've tweaked the "Large I/O efficiency" calculation and although it is still different than the results from optdiag, it's hopefully different less often.

The version of optdiag I tested against can't handle big[date]time and [unsigned] bigint, where the procedure can. optdiag is also much slower than the procedure - but that might be to do with my laptop's connectivity.

[Former Member]

After a bit more tweaking, here is (hopefully) the final version for ASE 15.5