Greetings,
I've just started using sqlite to (ironically) report on z/OS DB2 stats.
I've written a query where the results look a bit strange. I group by
the DB2 plan name and order by the maximum
CPU time. The sort order looks incorrect as you can see in the report
below. I'm certainly not discounting the fact I've done something dumb.
Here's the query.
.headers on
.mode column
.width 0 -10 -10 -10
select plan,
printf("%.6f", max(cpu1)) as 'Max CPU',
printf("%.6f", avg(cpu1)) as 'Avg CPU',
printf("%.6f", min(cpu1)) as 'Min CPU'
from db2
group by plan
order by 2 desc;
.width 0 0 -10 -10 -10 -10 -10 -10 -10 -10 -10 -10
-- list all Proteus plan performance
select time, plan,
printf("%.6f",cpu1) as "CPU Time",
"select", "insert", "update", "delete", "fetch", "open", "close",
"prepare", "commits"
from db2 -- where plan = 'PTS46'
order by cpu1 desc;
davcra01@cervidae:~$ sqlite3 --version
3.14.2 2016-09-12 18:50:49 29dbef4b8585f753861a36d6dd102ca634197bd6
Plan Max CPU Avg CPU Min CPU
---------- ---------- ---------- ----------
DSNUTIL 9.733366 0.251608 0.000503
PTS46 595.509361 2.278950 0.000892
DSNREXX 29.117646 0.015989 0.001210
?RRSAF 28.607606 8.248447 0.131628
KO2PLAN 14.684294 0.004192 0.000167
ADB 1.351044 0.174945 0.002422
1.270712 0.130687 0.000216
DISTSERV 0.230132 0.003751 0.000343
IPIUTILB 0.206471 0.022027 0.006389
ADB2GEN 0.163992 0.092727 0.021461
DSNTIAD 0.085937 0.048863 0.001895
CSQ5L800 0.066622 0.001047 0.000391
DSNESPCS 0.061223 0.022873 0.003060
DSNBIND 0.031947 0.009363 0.002422
CSQ5S800 0.020556 0.000914 0.000383
CSQ5W800 0.007023 0.002816 0.000673
ADB27SPC 0.004710 0.004484 0.004258
CSQ5R800 0.003698 0.000695 0.000263
CSQ5T800 0.003539 0.002574 0.000695
FBODCP05 0.002416 0.000428 0.000354
CSQ5U800 0.002293 0.001106 0.000438
TIME Plan CPU Time SELECT
INSERT UPDATE DELETE FETCH OPEN CLOSE PREPARE
-------------------------- ---------- ---------- ----------
---------- ---------- ---------- ---------- ---------- ----------
----------
2016-09-12 09:33:54.761413 PTS46 595.509361 0 15968
4793 16769 233954 55343 55343 1223
2016-09-07 12:41:53.949671 PTS46 95.516983 0 9501
1156 3334 101360 12034 12034 1378
2016-09-07 10:54:46.404475 PTS46 95.059669 0 12598
780 2898 120602 12199 12199 751
2016-09-07 11:21:42.332159 PTS46 58.592052 0 6007
508 1445 48193 6114 6114 287
2016-09-07 12:50:56.839200 PTS46 58.303021 0 6007
508 1445 51395 6274 6274 287
2016-09-09 17:46:06.732869 DSNREXX 29.117646 0
0 0 286 287 1 1 1
2016-09-11 17:01:02.454043 ?RRSAF 28.607606 0 0
9722 612400 9722 9722 9722 0
2016-09-08 17:46:05.062529 DSNREXX 28.011428 0
0 0 0 1 1 1 1
2016-09-12 17:46:14.052496 DSNREXX 27.694071 0
0 0 0 1 1 1 1
2016-09-13 17:45:56.749485 DSNREXX 26.836652 0
0 0 0 1 1 1 1
2016-09-07 17:45:57.286365 DSNREXX 26.765156 0
0 0 0 1 1 1 1
2016-09-11 17:01:02.562077 ?RRSAF 16.733743 0 0
9718 0 9718 9718 9718 0
2016-09-08 12:11:25.626596 DSNREXX 11.265809 0
0 0 0 9876 1 0 1
2016-09-08 12:10:55.707563 DSNREXX 11.196782 0
0 0 0 9876 1 0 1
2016-09-08 12:12:37.436988 DSNREXX 11.190524 0
0 0 0 9876 1 0 1
2016-09-08 17:18:03.434308 DSNUTIL 9.733366 0
0 0 0 0 0 0 0
2016-09-09 17:18:03.481755 DSNUTIL 9.719033 0
0 0 0 0 0 0 0
2016-09-12 17:18:23.088672 DSNUTIL 9.682909 0
0 0 0 0 0 0 0
2016-09-13 17:17:58.083050 DSNUTIL 9.650545 0
0 0 0 0 0 0 0
2016-09-12 09:49:23.324659 PTS46 9.645624 0
150 24 84 23500 321 321 31
2016-09-08 17:46:17.707124 DSNREXX 9.593526 0
0 0 4 5 1 1 1
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
