ca.portal.admin

Re:SQL question

Discussion created by ca.portal.admin on Oct 10, 2008
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
Re: Multitasking - is it worth it?
"And sometimes I find I=C2=A0have to recant; at least partially.=20



Latest on our little science experiment here seems to indicate the MVS disp=
atching overhead may really be a big deal, maybe bigger than the MPMODE che=
cking overhead.=20



After tossing out the comparisons based on=C2=A0DB calls (turns out we were=
overflowing the counter multiple times during the run of CV, rendering it =
useless as a metric), we find that raising queue depth from 2 to 4 appears =
to cut average CPU/task by 30% (or more) in our environment.=C2=A0=20



The observations about the impact of this change on secondary TCBs and thei=
r work has remained constant; they wake up less but do more when they do.=
=20



We continue to poke, prod and measure whatever twitches when we do.=20



Anybody else out there have info to share on your experiences?=20



Don Casey=20

Run Right, LLC=20

Downwind from the Angel Island fire=20




----- Original Message -----=20
From: donjcasey@comcast.net=20
To: ""IDMS Public Discussion Forum"" <IDMS-L@LISTSERV.IUASSN.COM>, IDMS-L@LIS=
TSERV.IUASSN.COM=20
Sent: Friday, October 3, 2008 12:34:30 PM GMT -08:00 US/Canada Pacific=20
Subject: Re: Multitasking - is it worth it?=20


Terry:=20

Thanks for the info on R16; but I'm going to have to poke back on the overh=
ead discussion.=20

My understanding is there are two levels of overhead, you describe the seco=
nd (more on that later).=C2=A0 The first bit of overhead has to do with MPM=
ODE conflict checking.=C2=A0 In the past (and now, as far as I know), this =
was done by calling into a routine to check for conflicts every time a modu=
le was entered.=C2=A0 In a non-MT system, the branch into this check was ef=
fectively no-opped (by means of=C2=A0a branch instruction in the CSA that g=
ets set to a NOP or a BR as part of startup).=C2=A0 The checking entailed s=
ome amount of overhead.=C2=A0 Not sure how much.=20

The second bit of overhead, waking up and putting back to sleep MVS TCBs as=
the workload waxes and wanes, is somewhat controlled by queue depth.=C2=A0=
This is why the shop I'm at diddled that parameter recently.=C2=A0 What we=
saw was a decrease in the amount of times the alternate TCBs were woken up=
(expected), a decrease in the total amount of work performed by them (expe=
cted),=C2=A0AND an increase in the amount of work they managed to do each t=
ime they were woken up (as measured by dispatch count in the D SUBTASKS com=
mand).=C2=A0 In theory, by waking up less frequently but handling more work=
once woken up, the relative amount of overhead associated with the occasio=
nal arousal should decrease.=20

Unfortunately, we have yet been unable to discern a measureable difference =
in overhead.=C2=A0We've been looking at two ratios over a week of work (mil=
lions and millions of tasks): average CPU/task and average CPU/DBMS call.=
=C2=A0 One went up, the other stayed pretty constant.=C2=A0 Evidently the d=
ifference is small, or our workload has changed in composition over the per=
iod of the experiment.=20

We're still tracking.=20

Don Casey=20
Run Right, LLC=20
<working at an undisclosed global shipping and logistics firm somewhere in =
the Bay Area>=20

P.S. ADS is perhaps the poster child for MT; quite a bit of its workload is=
""ANY"", and thus will not suffer MPMODE conflicts.=20

-------------- Original message --------------=20
From: ""Schwartz, Terry"" <Terry.Schwartz@PS.NET>=20
Greetings all,=20
=20
Multitasking will help even if you are in a CICS environment. Why you ask=
?=20
In version 16 more of the IDMS system functions were converted over to=20
run in MP mode ANY. That includes dispatching, storage management, resour=
ce=20
management and some DBMS functions. All are used by a task coming over fr=
om=20
CICS.=20
=20
MT only kicks in if you exceed the queue depth of task waiting on dispatc=
h.=20
Queue depth=20
defaults to 2 but you can set it to any number.=20
=20
The overhead incurred is not in turning on MT but when MT swaps the task =
over=20
to the additional TCB. So if none of your workload is eligible for MT=20
(although I do not see that happening) you will not incur sig nificant ov=
erhead.=20
=20
I have been running MT on my heavily ADS system for over 10 years and cou=
ld not=20
achieve stellar response times without multitasking. I am getting ready t=
o turn=20
it on in=20
a CICS environment which is very spikey and has issues with lock contenti=
on.=20
=20
The bottom line is to turn it on, take some measurements, and execute the=
DCMT D=20
SUBTASK=20
at the end of the day to see how effective MT is in your system. You can =
always=20
turn it off=20
since it is a simple parm in the startup proc or JCL.=20
=20
(Note: John Siraco will be covering Multitasking in his CA World pre-conf=
erence=20
education CA IDMS tuning course.)=20
=20
Regards,=20
Terry Schwartz=20
Perot Systems=20
"
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: Multitasking - is it worth it?
"And sometimes I find I have to recant; at least partially.



Latest on our little science experiment here seems to indicate the MVS dispatching overhead may really be a big deal, maybe bigger than the MPMODE checking overhead.



After tossing out the comparisons based on DB calls (turns out we were overflowing the counter multiple times during the run of CV, rendering it useless as a metric), we find that raising queue depth from 2 to 4 appears to cut average CPU/task by 30% (or more) in our environment.



The observations about the impact of this change on secondary TCBs and their work has remained constant; they wake up less but do more when they do.



We continue to poke, prod and measure whatever twitches when we do.



Anybody else out there have info to share on your experiences?



Don Casey

Run Right, LLC

Downwind from the Angel Island fire




----- Original Message -----
From: donjcasey@comcast.net
To: ""IDMS Public Discussion Forum"" <IDMS-L@LISTSERV.IUASSN.COM>, IDMS-L@LISTSERV.IUASSN.COM
Sent: Friday, October 3, 2008 12:34:30 PM GMT -08:00 US/Canada Pacific
Subject: Re: Multitasking - is it worth it?


Terry:

Thanks for the info on R16; but I'm going to have to poke back on the overhead discussion.

My understanding is there are two levels of overhead, you describe the second (more on that later). The first bit of overhead has to do with MPMODE conflict checking. In the past (and now, as far as I know), this was done by calling into a routine to check for conflicts every time a module was entered. In a non-MT system, the branch into this check was effectively no-opped (by means of a branch instruction in the CSA that gets set to a NOP or a BR as part of startup). The checking entailed some amount of overhead. Not sure how much.

The second bit of overhead, waking up and putting back to sleep MVS TCBs as the workload waxes and wanes, is somewhat controlled by queue depth. This is why the shop I'm at diddled that parameter recently. What we saw was a decrease in the amount of times the alternate TCBs were woken up (expected), a decrease in the total amount of work performed by them (expected), AND an increase in the amount of work they managed to do each time they were woken up (as measured by dispatch count in the D SUBTASKS command). In theory, by waking up less frequently but handling more work once woken up, the relative amount of overhead associated with the occasional arousal should decrease.

Unfortunately, we have yet been unable to discern a measureable difference in overhead. We've been looking at two ratios over a week of work (millions and millions of tasks): average CPU/task and average CPU/DBMS call. One went up, the other stayed pretty constant. Evidently the difference is small, or our workload has changed in composition over the period of the experiment.

We're still tracking.

Don Casey
Run Right, LLC
<working at an undisclosed global shipping and logistics firm somewhere in the Bay Area>

P.S. ADS is perhaps the poster child for MT; quite a bit of its workload is ""ANY"", and thus will not suffer MPMODE conflicts.

-------------- Original message --------------
From: ""Schwartz, Terry"" <Terry.Schwartz@PS.NET>
Greetings all,

Multitasking will help even if you are in a CICS environment. Why you ask?
In version 16 more of the IDMS system functions were converted over to
run in MP mode ANY. That includes dispatching, storage management, resource
management and some DBMS functions. All are used by a task coming over from
CICS.

MT only kicks in if you exceed the queue depth of task waiting on dispatch.
Queue depth
defaults to 2 but you can set it to any number.

The overhead incurred is not in turning on MT but when MT swaps the task over
to the additional TCB. So if none of your workload is eligible for MT
(although I do not see that happening) you will not incur sig nificant overhead.

I have been running MT on my heavily ADS system for over 10 years and could not
achieve stellar response times without multitasking. I am getting ready to turn
it on in
a CICS environment which is very spikey and has issues with lock contention.

The bottom line is to turn it on, take some measurements, and execute the DCMT D
SUBTASK
at the end of the day to see how effective MT is in your system. You can always
turn it off
since it is a simple parm in the startup proc or JCL.

(Note: John Siraco will be covering Multitasking in his CA World pre-conference
education CA IDMS tuning course.)

Regards,
Terry Schwartz
Perot Systems
"
IDMS Public Discussion Forum
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Re: Multitasking - is it worth it?
"My sense is that an IDMS Mutlitasking environment is heavily dependent
on what other high priority jobs are running in the box. Usually, MT is
tried in shops where CPU utilization is maxed out, as a last ditch
effort. In those environments, any TCB that puts itself in a wait
state, gives other jobs a chance to run until they put themselves in a
wait state. So, the key to IDMS success is to hog the box as long as
there's work, and to stuff all the work it has in one dispatchable
cycle. That is the inherent fault with MT, that each database request
must be serviced by two TCB's (DB and DC) serially. That inherently
gives other jobs/TCB's a chance to get dispatched and hog the box, like
CICS and VTAM. CA has made some changes where a lot of traditional DC
work, like storage acquisition can be made in DB mode, but it's a
stopgap, not a design strength.

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: Multitasking - is it worth it?
"My sense is that an IDMS Mutlitasking environment is heavily dependent=0D=
=0Aon what other high priority jobs are running in the box=2E Usually, MT =
is=0D=0Atried in shops where CPU utilization is maxed out, as a last ditch=
=0D=0Aeffort=2E In those environments, any TCB that puts itself in a wait=
=0D=0Astate, gives other jobs a chance to run until they put themselves in =
a=0D=0Await state=2E So, the key to IDMS success is to hog the box as long=
as=0D=0Athere's work, and to stuff all the work it has in one dispatchable=
=0D=0Acycle=2E That is the inherent fault with MT, that each database requ=
est=0D=0Amust be serviced by two TCB's (DB and DC) serially=2E That inher=
ently=0D=0Agives other jobs/TCB's a chance to get dispatched and hog the bo=
x, like=0D=0ACICS and VTAM=2E CA has made some changes where a lot of trad=
itional DC=0D=0Awork, like storage acquisition can be made in DB mode, but =
it's a=0D=0Astopgap, not a design strength=2E=0D=0A=0D=0ALutz Petzold=0D=0A=
TDM UDB/IDMS Support=0D=0A401-782-2265=0D=0APage 860 366 0865 or Telalert=
=0D=0A =0D=0A =0D=0A=0D=0AThis e-mail may contain confidential or privilege=
d information=2E If=0Ayou think you have received this e-mail in error, ple=
ase advise the=0Asender by reply e-mail and then delete this e-mail immedia=
tely=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: Multitasking - is it worth it?
"Don, what is your operating environment? And have you heard any rumors
whether CA plans to multithread the DB TCB and DCE TCB?

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: Multitasking - is it worth it?
"Don, what is your operating environment? And have you heard any rumors=0D=
=0Awhether CA plans to multithread the DB TCB and DCE TCB?=0D=0A=0D=0ALutz =
Petzold=0D=0ATDM UDB/IDMS Support=0D=0A401-782-2265=0D=0APage 860 366 0865 =
or Telalert=0D=0A =0D=0A =0D=0A=0D=0AThis e-mail may contain confidential o=
r privileged 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-m=
ail immediately=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: Multitasking - is it worth it?
"My understanding of the underlying mechanics of MT differs somewhat.=20



IDMS does not reserve a specific TCB for a particular mode, and does not sw=
ap from one TCB to another whenever a mode change happens.=20



In a particular task execution, as internal modules are entered, the mpmode=
requirement for that module is checked against other active runners (on ot=
her TCBs).=C2=A0 If no conflict, the existing task is assigned the new mpmo=
de and continues to operate on the existing TCB.=C2=A0=C2=A0If there is a c=
onflict, it is put into a mpmode wait (#WAIT, internal to DC), and the disp=
atcher looks for another candidate to dispatch on that TCB.=C2=A0 Only if n=
o other ready and otherwise eligible work is found will IDMS relinquish tha=
t TCB (via an OS wait).=20



MT support was turned on here in one of three DB/DC systems over a year ago=
, due to peak load concerns.=C2=A0 During certain times of the day (around =
midnight; the volume peak coincides with both Asia and Europe sites being s=
imultaneously active) and during business peaks (e.g. pre-Xmas rush) this s=
ystem needs more cycles than a single engine can supply, even if the CPU as=
a whole is not stressed.=C2=A0 MT support has provided sufficient boost to=
keep the system from reaching max task conditions, and at the same time ho=
lding lock contention down by ensuring tasks can zip through without having=
to wait for CPU resources.=20



The environment=C2=A0where I'm currently stationed=C2=A0is z/OS; and I have=
no breaking news on CA changes or plans in this area (haven't talked to an=
ybody there on this topic recently).=20



Don Casey=20

Run Right, LLC=20

Somewhere North of Oracle Arena=20


----- Original Message -----=20
From: ""Lutz Petzold"" <PetzoldL@AETNA.COM>=20
To: IDMS-L@LISTSERV.IUASSN.COM=20
Sent: Tuesday, October 14, 2008 5:36:22 AM GMT -08:00 US/Canada Pacific=20
Subject: Re: Multitasking - is it worth it?=20

My sense is that an IDMS Mutlitasking environment is heavily dependent=20
on what other high priority jobs are running in the box. =C2=A0Usually, MT =
is=20
tried in shops where CPU utilization is maxed out, as a last ditch=20
effort. =C2=A0In those environments, any TCB that puts itself in a wait=20
state, gives other jobs a chance to run until they put themselves in a=20
wait state. =C2=A0So, the key to IDMS success is to hog the box as long as=
=20
there's work, and to stuff all the work it has in one dispatchable=20
cycle. =C2=A0That is the inherent fault with MT, that each database request=
=20
must be serviced by two TCB's (DB and DC) =C2=A0serially. =C2=A0That inhere=
ntly=20
gives other jobs/TCB's a chance to get dispatched and hog the box, like=20
CICS and VTAM. =C2=A0CA has made some changes where a lot of traditional DC=
=20
work, like storage acquisition can be made in DB mode, but it's a=20
stopgap, not a design strength.=20

Lutz Petzold=20
TDM UDB/IDMS Support=20
401-782-2265=20
Page 860 366 0865 or Telalert=20
=C2=A0=20
=C2=A0=20

This e-mail may contain confidential or privileged information. If=20
you think you have received this e-mail in error, please advise the=20
sender by reply e-mail and then delete this e-mail immediately.=20
Thank you. Aetna=20
"
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: Multitasking - is it worth it?
"My understanding of the underlying mechanics of MT differs somewhat.



IDMS does not reserve a specific TCB for a particular mode, and does not swap from one TCB to another whenever a mode change happens.



In a particular task execution, as internal modules are entered, the mpmode requirement for that module is checked against other active runners (on other TCBs). If no conflict, the existing task is assigned the new mpmode and continues to operate on the existing TCB. If there is a conflict, it is put into a mpmode wait (#WAIT, internal to DC), and the dispatcher looks for another candidate to dispatch on that TCB. Only if no other ready and otherwise eligible work is found will IDMS relinquish that TCB (via an OS wait).



MT support was turned on here in one of three DB/DC systems over a year ago, due to peak load concerns. During certain times of the day (around midnight; the volume peak coincides with both Asia and Europe sites being simultaneously active) and during business peaks (e.g. pre-Xmas rush) this system needs more cycles than a single engine can supply, even if the CPU as a whole is not stressed. MT support has provided sufficient boost to keep the system from reaching max task conditions, and at the same time holding lock contention down by ensuring tasks can zip through without having to wait for CPU resources.



The environment where I'm currently stationed is z/OS; and I have no breaking news on CA changes or plans in this area (haven't talked to anybody there on this topic recently).



Don Casey

Run Right, LLC

Somewhere North of Oracle Arena


----- Original Message -----
From: ""Lutz Petzold"" <PetzoldL@AETNA.COM>
To: IDMS-L@LISTSERV.IUASSN.COM
Sent: Tuesday, October 14, 2008 5:36:22 AM GMT -08:00 US/Canada Pacific
Subject: Re: Multitasking - is it worth it?

My sense is that an IDMS Mutlitasking environment is heavily dependent
on what other high priority jobs are running in the box. Usually, MT is
tried in shops where CPU utilization is maxed out, as a last ditch
effort. In those environments, any TCB that puts itself in a wait
state, gives other jobs a chance to run until they put themselves in a
wait state. So, the key to IDMS success is to hog the box as long as
there's work, and to stuff all the work it has in one dispatchable
cycle. That is the inherent fault with MT, that each database request
must be serviced by two TCB's (DB and DC) serially. That inherently
gives other jobs/TCB's a chance to get dispatched and hog the box, like
CICS and VTAM. CA has made some changes where a lot of traditional DC
work, like storage acquisition can be made in DB mode, but it's a
stopgap, not a design strength.

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
FW: IUA Board of Directors - Election Notice
"Hi everyone,

=20

It's that time of year again, when we must fill one position on the IUA =
Board of Directors. If you're a member of the IUA, please visit =
www.iuassn.org, and sign in. Choose the ""Board of Directors Ballot"" =
under the Members Only section, and please vote to fill the available =
position. You have until Thursday, November 13th to cast your vote =
online.

=20

Also for more information on CA-World, please see the article ""CA-World =
News for IDMS Users"" located on www.iuassn.org <http://www.iuassn.org/> =
home page.

=20

Thank you,

Diane Montstream

IUA Nominations Chair

=20
"
IDMS 3rd-party providers forum
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP
IDMSVENDOR-L@LISTSERV.IUASSN.COM
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Daylight-savings to Standard time change
"Our software support team is suggesting we perform the following 8 steps =
in order to ensure we have no problems with IDMS following the DST to =
Standard time change. This all seems pretty extreme. We are currently on =
IDMS 16.0 SP4. Are other businesses performing all these tasks to =
support the DST to Standard time change?=20

1. SHUTDOWN the CV/DC system(s)
2. Archive any full journals and the active journal=20
3. Reinitialize the journal files=20
4. Offload the DCLOG file=20
5. Reformat the DCLOG file=20
6. Re-IPL the machine with the correct time and date=20
7. Bring up the CV/DC system(s)=20
8. Disable the MAPC task and RHDCMPUT for one hour
=20

Ron Lamb
GE - GIS - DBA COE
8700 Governors Hill Drive
Cincinnati, OH 45249
=20
"
IDMS 3rd-party providers forum
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP
IDMSVENDOR-L@LISTSERV.IUASSN.COM
IDMSVENDOR-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Daylight-savings to Standard time change
"Our software support team is suggesting we perform the following 8 steps in order to ensure we have no problems with IDMS following the DST to Standard time change. This all seems pretty extreme. We are currently on IDMS 16.0 SP4. Are other businesses performing all these tasks to support the DST to Standard time change?

1. SHUTDOWN the CV/DC system(s)
2. Archive any full journals and the active journal
3. Reinitialize the journal files
4. Offload the DCLOG file
5. Reformat the DCLOG file
6. Re-IPL the machine with the correct time and date
7. Bring up the CV/DC system(s)
8. Disable the MAPC task and RHDCMPUT for one hour


Ron Lamb
GE - GIS - DBA COE
8700 Governors Hill Drive
Cincinnati, OH 45249

"
IDMS Public Discussion Forum
IDMS-L@LISTSERV.IUASSN.COM
SMTP
IDMS-L@LISTSERV.IUASSN.COM
IDMS-L@LISTSERV.IUASSN.COM
SMTP








Normal

Normal
Re: Daylight-savings to Standard time change
"You can shut down your systems and leave them down for one hour if you
don't want to perform the other steps. Some shops can't do this due to
workload, though. =20

Outcomes