I found the results quite interesting. Page requests were down about
52.5K, reads down, writes down, calc on target just a tad better, via on
target 2.5K better, less fragments stored, index activity just a wee bit
behind the only down side.
Rollforward of day one following load with fix:
29 SEP 08 12:24 EZ-REORG CATCH UP PHASE 3
DMCL: DMPCV006 SUBSCHEMA: SSPPCSLD
TOTAL DML PROCESSED : 8,182,863
TOTAL COMMITS ISSUED : 0
DBEXCUP3 DATABASE STATISTICS REPORT
PAGES READ 1,370,414
PAGES WRITTEN 1,268,135
PAGES REQUESTED 13,532,582
TIMES CALC RECORD FITS 24,463
TIMES CALC REC OVERFLOW 5,874
TIMES VIA RECORD FITS 1,018,663
TIMES VIA RECORD OVERFLOW 131,390
RECORDS REQUESTED 20,291,107
RECORDS CURRENT OF R/U 5,606,919
CALLS TO IDMS 9,362,171
FRAGMENTS STORED 1,651
SR8 SPLITS 947
SR8 SPAWNS 15
SR8S STORED 1,327
B-TREE LEVELS SEARCHED 2,485,259
Rollforward of day one following load without fix:
5 OCT 08 22:36 EZ-REORG CATCH UP PHASE 3
DMCL: DMPCV006 SUBSCHEMA: SSPPCSLD
TOTAL DML PROCESSED : 8,182,863
TOTAL COMMITS ISSUED : 0
DBEXCUP3 DATABASE STATISTICS REPORT
PAGES READ 1,376,679
PAGES WRITTEN 1,271,893
PAGES REQUESTED 13,585,095
TIMES CALC RECORD FITS 24,460
TIMES CALC REC OVERFLOW 5,877
TIMES VIA RECORD FITS 1,016,179
TIMES VIA RECORD OVERFLOW 133,872
RECORDS REQUESTED 20,291,630
RECORDS CURRENT OF R/U 5,606,919
CALLS TO IDMS 9,362,171
FRAGMENTS STORED 1,742
SR8 SPLITS 946
SR8 SPAWNS 14
SR8S STORED 1,325
B-TREE LEVELS SEARCHED 2,485,258
Day 2 saw similar results in most categories but did store 10 more
fragments in the fix loaded image, still staying ahead in fragmentation.
Again about 50K less page requests. Day 3 page requests were down 80K,
fragments down again. Day 4 I saw writes up 2K for the first time in
the fix-loaded image, but again page requests were down, the time about
76K. Day 5 was a light weekend day with little differences, 26K less
page requests in the fix-loaded image. Day 6 a large batch purge job
was run and the results are quite interesting.
Rollforward of day six following load with fix:
30 SEP 08 10:09 EZ-REORG CATCH UP PHASE 3
DMCL: DMPCV006 SUBSCHEMA: SSPPCSLD
TOTAL DML PROCESSED : 14,878,412
TOTAL COMMITS ISSUED : 0
DBEXCUP3 DATABASE STATISTICS REPORT
PAGES READ 3,600,991
PAGES WRITTEN 3,606,391
PAGES REQUESTED 25,332,483
TIMES CALC RECORD FITS 1,560
TIMES CALC REC OVERFLOW 1,359
TIMES VIA RECORD FITS 55,894
TIMES VIA RECORD OVERFLOW 6,571
RECORDS REQUESTED 40,738,622
RECORDS CURRENT OF R/U 7,513,148
CALLS TO IDMS 14,943,739
FRAGMENTS STORED 224
Rollforward of day six following load without fix:
6 OCT 08 17:01 EZ-REORG CATCH UP PHASE 3
DMCL: DMPCV006 SUBSCHEMA: SSPPCSLD
TOTAL DML PROCESSED : 14,878,412
TOTAL COMMITS ISSUED : 0
DBEXCUP3 DATABASE STATISTICS REPORT
PAGES READ 3,838,959
PAGES WRITTEN 3,844,843
PAGES REQUESTED 29,662,932
TIMES CALC RECORD FITS 1,566
TIMES CALC REC OVERFLOW 1,353
TIMES VIA RECORD FITS 56,374
TIMES VIA RECORD OVERFLOW 6,091
RECORDS REQUESTED 40,738,664
RECORDS CURRENT OF R/U 7,513,148
CALLS TO IDMS 14,943,739
FRAGMENTS STORED 233
I pulled all the index stats because they were identical, but I'd say
4.3 Million less page requests says it all. 200K+ less reads and writes
also. I guess this fix may be a keeper. Day 7 was the last day rolled
on both images. Again I'm seeing 80K less page requests and better via
on target, with other differences relatively insignificant.
John
P Please consider the environment before printing this e-mail
Cleveland Clinic is ranked one of the top hospitals in America by U.S.
News & World Report (2008).
Visit us online at
http://www.clevelandclinic.org for a complete listing
of our services, staff and locations.
Confidentiality Note: This message is intended for use only by the
individual or entity to which it is addressed and may contain
information that is privileged, confidential, and exempt from disclosure
under applicable law. If the reader of this message is not the intended
recipient or the employee or agent responsible for delivering the
message to the intended recipient, you are hereby notified that any
dissemination, distribution or copying of this communication is strictly
prohibited. If you have received this communication in error, please
contact the sender immediately and destroy the material in its entirety,
whether electronic or hard copy. Thank you.
-------------------------------------------------------------------------
My e-mail address has been harmonized into @dexia.com !
Please remember to update your contact list.
Veuillez noter que mon adresse e-mail est harmonisée en @dexia.com !
N'oubliez pas de mettre à jour votre liste de contacts.
Meine E-Mailadresse wurde harmonisiert zu @dexia.com
Vergessen Sie nicht Ihre Kontaktadresse anzupassen.
Mijn e-mailadres is geharmoniseerd naar @dexia.com.
Vergeet niet uw contactenlijst aan te passen.
-------------------------------------------------------------------------
Dexia disclaimer:
http://www.dexia.com/maildisclaimer.htm
-------------------------------------------------------------------------
"
IDMS Public Discussion Forum
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-L@LISTSERV.IUASSN.COM
SMTP
Normal
Normal
SQL question
"Hi everyone,
I have a SQL question from a developper that I can't answer. He wants
to get all B records from a set A-B, but only the B that are not an
active member of the set A-B. He tried with NOT A-B, and it doesn't
work. Is there a way to do this in SQL?
Thanks,
Laura Rochon
Ajilon
"
IDMS Public Discussion Forum
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-L@LISTSERV.IUASSN.COM
SMTP
Normal
Normal
SQL question
"Hi everyone,
I have a SQL question from a developper that I can't answer. He wants
to get all B records from a set A-B, but only the B that are not an
active member of the set A-B. He tried with NOT A-B, and it doesn't
work. Is there a way to do this in SQL?
Thanks,
Laura Rochon
Ajilon
"
IDMS 3rd-party providers forum
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP
IDMSVENDOR-L@LISTSERV.IUASSN.COM
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP
Normal
Normal
Re: SQL question
"I have an idea, but it isn't pretty=2E SQL doesn't support sets=2E So to=
=0D=0Amake IDMS-SQL work, I believe they use a foreign key=2E Since each r=
ecord=0D=0Atype is the equivalent of a table, could you do a table scan and=
select=0D=0Athe rows where the foreign key is null?=0D=0A=0D=0ALutz Petzol=
d=0D=0ATDM UDB/IDMS Support=0D=0A401-782-2265=0D=0APage 860 366 0865 or Tel=
alert=0D=0A =0D=0A =0D=0A=0D=0AThis e-mail may contain confidential or priv=
ileged information=2E If=0Ayou think you have received this e-mail in error=
, please advise the=0Asender by reply e-mail and then delete this e-mail im=
mediately=2E=0AThank you=2E Aetna
"
IDMS 3rd-party providers forum
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP
IDMSVENDOR-L@LISTSERV.IUASSN.COM
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP
Normal
Normal
Re: SQL question
"I have an idea, but it isn't pretty. SQL doesn't support sets. So to
make IDMS-SQL work, I believe they use a foreign key. Since each record
type is the equivalent of a table, could you do a table scan and select
the rows where the foreign key is null?
Lutz Petzold
TDM UDB/IDMS Support
401-782-2265
Page 860 366 0865 or Telalert
This e-mail may contain confidential or privileged information. If
you think you have received this e-mail in error, please advise the
sender by reply e-mail and then delete this e-mail immediately.
Thank you. Aetna
"
IDMS Public Discussion Forum
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-L@LISTSERV.IUASSN.COM
SMTP
Normal
Normal
Re: SQL question
"Good idea, but in this case, there is no foreign key.
Thanks anyway,
Laura
Petzold, Lutz wrote:
I have an idea, but it isn't pretty=2E SQL doesn't support sets=2E So to=
=0D=0Amake IDMS-SQL work, I believe they use a foreign key=2E Since each r=
ecord=0D=0Atype is the equivalent of a table, could you do a table scan and=
select=0D=0Athe rows where the foreign key is null?=0D=0A=0D=0ALutz Petzol=
d=0D=0ATDM UDB/IDMS Support=0D=0A401-782-2265=0D=0APage 860 366 0865 or Tel=
alert=0D=0A =0D=0A =0D=0A=0D=0AThis e-mail may contain confidential or priv=
ileged information=2E If=0Ayou think you have received this e-mail in error=
, please advise the=0Asender by reply e-mail and then delete this e-mail im=
mediately=2E=0AThank you=2E Aetna
"
IDMS 3rd-party providers forum
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP
IDMSVENDOR-L@LISTSERV.IUASSN.COM
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP
Normal
Normal
Re: SQL question
"Good idea, but in this case, there is no foreign key.
Thanks anyway,
Laura
Petzold, Lutz wrote:
I have an idea, but it isn't pretty=2E SQL doesn't support sets=2E So to=
=0D=0Amake IDMS-SQL work, I believe they use a foreign key=2E Since each r=
ecord=0D=0Atype is the equivalent of a table, could you do a table scan and=
select=0D=0Athe rows where the foreign key is null?=0D=0A=0D=0ALutz Petzol=
d=0D=0ATDM UDB/IDMS Support=0D=0A401-782-2265=0D=0APage 860 366 0865 or Tel=
alert=0D=0A =0D=0A =0D=0A=0D=0AThis e-mail may contain confidential or priv=
ileged information=2E If=0Ayou think you have received this e-mail in error=
, please advise the=0Asender by reply e-mail and then delete this e-mail im=
mediately=2E=0AThank you=2E Aetna
"
IDMS Public Discussion Forum
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-L@LISTSERV.IUASSN.COM
SMTP
Normal
Normal
Re: SQL question
"Consider you have an owner (REC-A calc) and member (REC-B via SET-A-B)
and you want some columns from both. Something like this will get the
owner and all the members. SET-A-B is essentially the foreign key.
SELECT A.col1, A.col2, B.col1, B.col2, B.col3
FROM sqlschem.""REC-A"" A, sqlschem.""REC-B""
WHERE A.calckey =3D calckeyvalue
AND ""SET-A-B""
If you want REC-A data when SET-A-B is empty do this.
SELECT A.col1, A.col2, B.col1, B.col2, B.col3
FROM sqlschem.""REC-A"" A, sqlschem.""REC-B""
WHERE A.calckey =3D calckeyvalue
AND ""SET-A-B""
PRESERVE A
I hope I didn't misunderstand the original question.
=20
Joe S Cates, Database Analyst II
Systems Architecture and Operations
Database Management/Unix Administration
Montgomery County Public Schools
Rockville, MD 20850
301-279-3697
joe_cates@mcpsmd.org