Dynamic SQL from a CICS Program

[login to unmask email]

Dynamic SQL from a CICS Program
Hi. Here's a question that I'd like to throw out to the list for discussion,
and to get some opinions on how we should proceed.

We have an CICS/DB2 application currently under development which includes an
on-line query screen. I know few details about their requirements other than
the fact that they will be selecting a fixed-list of columns from a particular
table, but could have a varying set of where conditions (up to three columns
predicates in any combination, based on what the user chooses), and the sort
order of the result set can also be chosen by the user.

The developers are suggesting that because of the different possible
combinations of where predicates and sort orders, that they would like to use
dynamic SQL to build and execute the query from the CICS transaction. While I'm
not particularly opposed to dynamic SQL in general, this would be the first time
(to my knowledge) that we would be issuing dynamic SQL from within the confines
of a CICS transaction.

Does anybody have any warnings, tips, gotcha's, etc. about this scenario?

Any information and comments would be greatly appreciated.

Thanks,

Bill Gallagher, DBA
Phoenix Home Life
Enfield, CT



Jim Knisley

Re: Dynamic SQL from a CICS Program
(in response to BILL_GALLAGHER@PHL.COM)
Bill,

We use a ton of dynamic SQL from CICS programs (DB2 V5.1, CICS V4). With
current releases of DB2, dynamic SQL operates very efficiently. There are
options for caching statements, however there are some DB2 considerations about
whether or not the caching would buy you anything in terms of performance. The
biggest consideration we have run across is the access path determination.
Since the queries are not statically bound, a query that runs great today, may
run poorly tomorrow if you run RUNSTATS and there are significant changes that
cause DB2 to pick a different access path. Therefore, daily monitoring is
important. Although you can create one package that multiple transactions can
use to issue dynamic SQL, I would strongly suggest creating a separate package
for each transaction, just as you would for static SQL. Otherwise, it works
well and we maintain sub-second response time on most transactions that use
dynamic SQL.

Jim





[login to unmask email] on 12/23/99 09:11:00 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Jim Knisley/TFG/HII)
Subject: Dynamic SQL from a CICS Program



Hi. Here's a question that I'd like to throw out to the list for discussion,
and to get some opinions on how we should proceed.

We have an CICS/DB2 application currently under development which includes an
on-line query screen. I know few details about their requirements other than
the fact that they will be selecting a fixed-list of columns from a particular
table, but could have a varying set of where conditions (up to three columns
predicates in any combination, based on what the user chooses), and the sort
order of the result set can also be chosen by the user.

The developers are suggesting that because of the different possible
combinations of where predicates and sort orders, that they would like to use
dynamic SQL to build and execute the query from the CICS transaction. While I'm
not particularly opposed to dynamic SQL in general, this would be the first time
(to my knowledge) that we would be issuing dynamic SQL from within the confines
of a CICS transaction.

Does anybody have any warnings, tips, gotcha's, etc. about this scenario?

Any information and comments would be greatly appreciated.

Thanks,

Bill Gallagher, DBA
Phoenix Home Life
Enfield, CT








craig patton

Re: Dynamic SQL from a CICS Program
(in response to Jim Knisley)
Bill,
One thing to be careful of is LONG running dynamic SQL. You might want to
incorporate RLF to prevent a run away query. From your description this
might NOT be an issue, it depends on how tightly you are controlling that
SQL.

HTH,
Craig Patton
DB2 DBA



>From: [login to unmask email]
>Reply-To: DB2 Data Base Discussion List <[login to unmask email]>
>To: [login to unmask email]
>Subject: Dynamic SQL from a CICS Program
>Date: Thu, 23 Dec 1999 09:11:00 -0500
>
>Hi. Here's a question that I'd like to throw out to the list for
>discussion,
>and to get some opinions on how we should proceed.
>
>We have an CICS/DB2 application currently under development which includes
>an
>on-line query screen. I know few details about their requirements other
>than
>the fact that they will be selecting a fixed-list of columns from a
>particular
>table, but could have a varying set of where conditions (up to three
>columns
>predicates in any combination, based on what the user chooses), and the
>sort
>order of the result set can also be chosen by the user.
>
>The developers are suggesting that because of the different possible
>combinations of where predicates and sort orders, that they would like to
>use
>dynamic SQL to build and execute the query from the CICS transaction.
>While I'm
>not particularly opposed to dynamic SQL in general, this would be the first
>time
>(to my knowledge) that we would be issuing dynamic SQL from within the
>confines
>of a CICS transaction.
>
>Does anybody have any warnings, tips, gotcha's, etc. about this scenario?
>
>Any information and comments would be greatly appreciated.
>
>Thanks,
>
>Bill Gallagher, DBA
>Phoenix Home Life
>Enfield, CT
>
>
>
>
>

______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com



Leslie Pendlebury-Bowe

Re: Dynamic SQL from a CICS Program
(in response to craig patton)
Bill
coming from a 99% dynamic DB2 environment I would suggest that
utilising the Local and Global cache(edmpool) will aid
performance .. this is important as you DO NOT want the system having
to contunally re-preparing the statements everytime (i.e no cache) if
the queries are the same .. for example say 100 people all use the
same query exactly and they use it 100 times a day .. then with the
right caching you can save yoursefl (100 * 100) - 1 number of prepares
.. and the CPU saving is immense .. it may not matter to you as you
may have CPU out your ears ... :-) (we have a ZZ7 - 1600 mips and run
at an average of 30% CPU ... peak 70/80% - but we still avoid burning
CPU at all costs).

.....do you have thread reuse? whatever the answer to that question is
make sure you have the storage creep PTFs and APARS on ..(if you need
them then shout and I will dig out the bit) - from memory you need to
set CONSTOR=YES in one of the DB2 modules and also set sprmsth to
1048576 ... this will kick in the storage contraction when the DBM1
address space gets to a certain MB free .. But this is all explained
the apar docs.

Biggest thing to watch for with dynamic sql is access path selection
.. and we frig a lot of the stats on some of our key
tablespace/indexes so as to make the access path the same each time.

regards

Leslie Pendlebury-Bowe
DB2 DBA
OS390 / SAP etc






______________________________ Reply Separator _________________________________
Subject: Dynamic SQL from a CICS Program
Author: [login to unmask email] at Internet
Date: 12/23/99 9:11 AM


Hi. Here's a question that I'd like to throw out to the list for discussion,
and to get some opinions on how we should proceed.

We have an CICS/DB2 application currently under development which includes an
on-line query screen. I know few details about their requirements other than
the fact that they will be selecting a fixed-list of columns from a particular
table, but could have a varying set of where conditions (up to three columns
predicates in any combination, based on what the user chooses), and the sort
order of the result set can also be chosen by the user.

The developers are suggesting that because of the different possible
combinations of where predicates and sort orders, that they would like to use
dynamic SQL to build and execute the query from the CICS transaction. While I'm
not particularly opposed to dynamic SQL in general, this would be the first time
(to my knowledge) that we would be issuing dynamic SQL from within the confines
of a CICS transaction.

Does anybody have any warnings, tips, gotcha's, etc. about this scenario?

Any information and comments would be greatly appreciated.

Thanks,

Bill Gallagher, DBA
Phoenix Home Life
Enfield, CT








Roger Miller

Re: Dynamic SQL from a CICS Program
(in response to Leslie Pendlebury-Bowe)
One of the most common techniques for this kind of situation is to code
one or two combinations in static SQL, so that common cases do not have
the overhead of dynamic SQL. So you find a case or two that you expect
to be common and has a good index, then use dynamic SQL for the
exceptions.

Roger



Isaac Yassin

Re: Dynamic SQL from a CICS Program
(in response to Roger Miller)
Hi,

We do it a lot (about 70% of our activity is dynamic sql using cics) and it
works fine. We have about 200+ plans to separate monitoring and catch the
problematic ones. At the beginning there were some attempts at misuse (cpu hogs)
but that disappeared fast enough with education and internal program limitations
(like adding restrictive clauses to some "bad" queries). We started it on DB2
V2.3 !!! and never had complaints even then.

Good Luck,

--
Isaac Yassin

DBMS & IT Consultant

Tel: +972 9 9505172
Cel: +972 54 452793
Fax: +972 9 9560803


[login to unmask email] wrote:
>
> Hi. Here's a question that I'd like to throw out to the list for discussion,
> and to get some opinions on how we should proceed.
>
> We have an CICS/DB2 application currently under development which includes an
> on-line query screen. I know few details about their requirements other than
> the fact that they will be selecting a fixed-list of columns from a particular
> table, but could have a varying set of where conditions (up to three columns
> predicates in any combination, based on what the user chooses), and the sort
> order of the result set can also be chosen by the user.
>
> The developers are suggesting that because of the different possible
> combinations of where predicates and sort orders, that they would like to use
> dynamic SQL to build and execute the query from the CICS transaction. While I'm
> not particularly opposed to dynamic SQL in general, this would be the first time
> (to my knowledge) that we would be issuing dynamic SQL from within the confines
> of a CICS transaction.
>
> Does anybody have any warnings, tips, gotcha's, etc. about this scenario?
>
> Any information and comments would be greatly appreciated.
>
> Thanks,
>
> Bill Gallagher, DBA
> Phoenix Home Life
> Enfield, CT
>
>
>



Venkat (PCA) Pillay

Re: Dynamic SQL from a CICS Program
(in response to Isaac Yassin)
Bill

At the previous place I worked we used dynamic query from CICS a
lot. The main reason was REOPT(VAR) was not available at earlier versions
and the online screens had many optional fields. If user does not specify
value for them, they used to be > LOW VALUE or < HIGH VALUE. We couldn't
achieve good performance with static SQLs because of so many choices user
had. We switched over to Dynamic SQL along with RLF and had tremendous
improvement. User community was especially happy.

RLF has another problem sometimes. The query abends with -905 if CPU limit
exceeds and if application is very visible, the user community cries foul.
If you are on DATA SHARING and each machine is of different capacity then
you have to set your RLST tables with appropriate value otherwise the same
query which runs on one m/c could abend on different m/c (Good news is CICS
can not attach to group but is fixed to one physical DB2 subsystem in
data-sharing.)

As there is always overhead with dynamic SQLs so IT DEPENDS !!

> -----Original Message-----
> From: [login to unmask email] [SMTP:[login to unmask email]
> Sent: Thursday, December 23, 1999 9:11 AM
> To: [login to unmask email]
> Subject: Dynamic SQL from a CICS Program
>
> Hi. Here's a question that I'd like to throw out to the list for
> discussion,
> and to get some opinions on how we should proceed.
>
> We have an CICS/DB2 application currently under development which includes
> an
> on-line query screen. I know few details about their requirements other
> than
> the fact that they will be selecting a fixed-list of columns from a
> particular
> table, but could have a varying set of where conditions (up to three
> columns
> predicates in any combination, based on what the user chooses), and the
> sort
> order of the result set can also be chosen by the user.
>
> The developers are suggesting that because of the different possible
> combinations of where predicates and sort orders, that they would like to
> use
> dynamic SQL to build and execute the query from the CICS transaction.
> While I'm
> not particularly opposed to dynamic SQL in general, this would be the
> first time
> (to my knowledge) that we would be issuing dynamic SQL from within the
> confines
> of a CICS transaction.
>
> Does anybody have any warnings, tips, gotcha's, etc. about this scenario?
>
> Any information and comments would be greatly appreciated.
>
> Thanks,
>
> Bill Gallagher, DBA
> Phoenix Home Life
> Enfield, CT
>
>
>
>
>



Mike Vaughan

Re: Dynamic SQL from a CICS Program
(in response to Venkat (PCA) Pillay)
I worked with an application a few years ago that did this same thing for
the same reasons. One thing I found was that keeping response times
acceptable was a little challenging, mainly because of the end-user being able
to choose the sort-order on the fly. In my case we had the potential for huge
result sets, but of course only the first 'n'-rows could be displayed on a
CICS screen. In this case it obviously made sense to try to build supporting
indexes to eliminate the db2-sorts. With over a dozen different selection
criteria available (including any combination of these criteria) along with 5
different sort-orders, this could have easily led to an excessive number of
indexes being needed to support each combination. We ended up trying to tune
for the most-used options, but would still get a runaway query once in awhile.
RLF was eventually used to identify the run-aways (tried being more pro-active
by checking SQLERRD(4) after the prepare and not allow the query to run if
it's too large, but this turned out to be much less reliable than I had
initially hoped).
Looking back, I still think using dynamic SQL was the right choice in our
case, but if I had to do it over again I would try limit some of the options
allowed from the front-end. Bottom line is using dynamic SQL from CICS really
doesn't present an issue, but you need to be careful about how much
flexibility is allowed or you basically end up with adhoc-type access and
CICS-type response-time expectations.

Mike.
(my opinions, etc, etc)
-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Thursday, December 23, 1999 8:11 AM
To: [login to unmask email]
Subject: Dynamic SQL from a CICS Program


Hi. Here's a question that I'd like to throw out to the list for discussion,
and to get some opinions on how we should proceed.

We have an CICS/DB2 application currently under development which includes an
on-line query screen. I know few details about their requirements other than
the fact that they will be selecting a fixed-list of columns from a particular
table, but could have a varying set of where conditions (up to three columns
predicates in any combination, based on what the user chooses), and the sort
order of the result set can also be chosen by the user.

The developers are suggesting that because of the different possible
combinations of where predicates and sort orders, that they would like to use
dynamic SQL to build and execute the query from the CICS transaction. While
I'm
not particularly opposed to dynamic SQL in general, this would be the first
time
(to my knowledge) that we would be issuing dynamic SQL from within the
confines
of a CICS transaction.

Does anybody have any warnings, tips, gotcha's, etc. about this scenario?

Any information and comments would be greatly appreciated.

Thanks,

Bill Gallagher, DBA
Phoenix Home Life
Enfield, CT








Craig Theisen

Re: Dynamic SQL from a CICS Program
(in response to Mike Vaughan)
Recently we built our first CICS/Dynamic SQL application for similar
reasons and it appears to be working quite well. It was a worth-while
learning experience.

One thing we encountered and would like a better way to handle is "How to
add table owner Qualifiers" so the source does not need to be changed when
going between test and production environments.(the compile/bind will not
qualify dynamic SQL tablenames) What we did was used the CICS assign
APPLID instruction to identify what CICS region we are in, then based upon
the result issued a set current sqlid statement to qualify the table name.
DOES ANYONE HAVE A BETTER PROCESS? We tried modifying the AUTHID parameter
in the RCT definition for our TRAN-id, but that did not work. (The authid
wanted a RACF userid, but we had a RACF group - secondary authid)

Also while developing our application, the users dwelt on the unusual
scenarios which truely required the dynamic SQL. However through carefully
worded interviews we determined that 95% of the querries could be handled by
a few static routines. We still used the dynamic SQL to provide total
functionality, but it gets hit less often.


-----Original Message-----
From: [login to unmask email]
To: [login to unmask email]
Sent: 12/23/99 8:11 AM
Subject: Dynamic SQL from a CICS Program

Hi. Here's a question that I'd like to throw out to the list for
discussion,
and to get some opinions on how we should proceed.

We have an CICS/DB2 application currently under development which
includes an
on-line query screen. I know few details about their requirements other
than
the fact that they will be selecting a fixed-list of columns from a
particular
table, but could have a varying set of where conditions (up to three
columns
predicates in any combination, based on what the user chooses), and the
sort
order of the result set can also be chosen by the user.

The developers are suggesting that because of the different possible
combinations of where predicates and sort orders, that they would like
to use
dynamic SQL to build and execute the query from the CICS transaction.
While I'm
not particularly opposed to dynamic SQL in general, this would be the
first time
(to my knowledge) that we would be issuing dynamic SQL from within the
confines
of a CICS transaction.

Does anybody have any warnings, tips, gotcha's, etc. about this
scenario?

Any information and comments would be greatly appreciated.

Thanks,

Bill Gallagher, DBA
Phoenix Home Life
Enfield, CT



the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
can



Sibimon Philip

Re: Dynamic SQL from a CICS Program
(in response to Craig Theisen)
You may be able to use DYNAMICRULE=B in package bind in order to use
qualifier of the package as the authid of the table in the dynamic SQL. I am
doing this while executing a dynamic query using DRDA through ISQL. I do not
specify authid in the dynamic SQL. Same thing may work for CICS also.

Authid can be a character string in the RCT, but you may need to modify the
RACF security exit routine to skip this string from RACF checkup. This is
mentioned in DB2 installation guide.

thanks
Sibimon Philip
972-702-2515 - Office
972-417-3597 - Residence
E-mail - [login to unmask email]



-----Original Message-----
From: Theisen, Craig [mailto:[login to unmask email]
Sent: Thursday, December 23, 1999 4:03 PM
To: [login to unmask email]
Subject: Re: Dynamic SQL from a CICS Program


Recently we built our first CICS/Dynamic SQL application for similar
reasons and it appears to be working quite well. It was a worth-while
learning experience.

One thing we encountered and would like a better way to handle is "How to
add table owner Qualifiers" so the source does not need to be changed when
going between test and production environments.(the compile/bind will not
qualify dynamic SQL tablenames) What we did was used the CICS assign
APPLID instruction to identify what CICS region we are in, then based upon
the result issued a set current sqlid statement to qualify the table name.
DOES ANYONE HAVE A BETTER PROCESS? We tried modifying the AUTHID parameter
in the RCT definition for our TRAN-id, but that did not work. (The authid
wanted a RACF userid, but we had a RACF group - secondary authid)

Also while developing our application, the users dwelt on the unusual
scenarios which truely required the dynamic SQL. However through carefully
worded interviews we determined that 95% of the querries could be handled by
a few static routines. We still used the dynamic SQL to provide total
functionality, but it gets hit less often.


-----Original Message-----
From: [login to unmask email]
To: [login to unmask email]
Sent: 12/23/99 8:11 AM
Subject: Dynamic SQL from a CICS Program

Hi. Here's a question that I'd like to throw out to the list for
discussion,
and to get some opinions on how we should proceed.

We have an CICS/DB2 application currently under development which
includes an
on-line query screen. I know few details about their requirements other
than
the fact that they will be selecting a fixed-list of columns from a
particular
table, but could have a varying set of where conditions (up to three
columns
predicates in any combination, based on what the user chooses), and the
sort
order of the result set can also be chosen by the user.

The developers are suggesting that because of the different possible
combinations of where predicates and sort orders, that they would like
to use
dynamic SQL to build and execute the query from the CICS transaction.
While I'm
not particularly opposed to dynamic SQL in general, this would be the
first time
(to my knowledge) that we would be issuing dynamic SQL from within the
confines
of a CICS transaction.

Does anybody have any warnings, tips, gotcha's, etc. about this
scenario?

Any information and comments would be greatly appreciated.

Thanks,

Bill Gallagher, DBA
Phoenix Home Life
Enfield, CT



the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
can








Viswanathan N

Re: Dynamic SQL from a CICS Program
(in response to Sibimon Philip)
hi Bill

There is nothing wrong in giving a dynamic sql option from CICS. We
too have it in our shops and use it for small administration work . It
depends on what sort of query and how and when u fire it. See always we
have to remember that CICS regions run at a higher priority and if you fire
a dynamic sql we have to compromise on the response as this will take CPU.
So u can build constraints within the program which does dynamic sql and
then do it. We fire and save the output in TSQ and do a cebr to show it to
user .

regards
Vishy





[login to unmask email] on 12/23/99 07:41:00 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>



To: [login to unmask email]

cc: (bcc: Viswanathan N/LTITL)



Subject: Dynamic SQL from a CICS Program









Note: Some recipients have been dropped due to syntax errors.
Please refer to the "$AdditionalHeaders" item for the complete headers.



Hi. Here's a question that I'd like to throw out to the list for
discussion,
and to get some opinions on how we should proceed.

We have an CICS/DB2 application currently under development which includes
an
on-line query screen. I know few details about their requirements other
than
the fact that they will be selecting a fixed-list of columns from a
particular
table, but could have a varying set of where conditions (up to three
columns
predicates in any combination, based on what the user chooses), and the
sort
order of the result set can also be chosen by the user.

The developers are suggesting that because of the different possible
combinations of where predicates and sort orders, that they would like to
use
dynamic SQL to build and execute the query from the CICS transaction.
While I'm
not particularly opposed to dynamic SQL in general, this would be the first
time
(to my knowledge) that we would be issuing dynamic SQL from within the
confines
of a CICS transaction.

Does anybody have any warnings, tips, gotcha's, etc. about this scenario?

Any information and comments would be greatly appreciated.

Thanks,

Bill Gallagher, DBA
Phoenix Home Life
Enfield, CT








Lynne Flatley

Re: Dynamic SQL from a CICS Program
(in response to Viswanathan N)
"How to add table owner qualifiers"

We created a table, kind of a "system" table, i.e. it didn't belong to any
specific application. Its key was a 3-character applid and another column
contained the value of the table creator ID. Any program that needed the
table creator ID would do a look-up (SELECT) based on their applid. This
had the added benefit of 'forcing' the program to contain static SQL so that
we could bind it and therefore use the dynamic bind rule for granting
execute authority.

> -----Original Message-----
> From: Theisen, Craig [SMTP:[login to unmask email]
> Sent: Thursday, December 23, 1999 5:03 PM
> To: [login to unmask email]
> Subject: Re: Dynamic SQL from a CICS Program
>
> Recently we built our first CICS/Dynamic SQL application for similar
> reasons and it appears to be working quite well. It was a worth-while
> learning experience.
>
> One thing we encountered and would like a better way to handle is "How to
> add table owner Qualifiers" so the source does not need to be changed when
> going between test and production environments.(the compile/bind will not
> qualify dynamic SQL tablenames) What we did was used the CICS assign
> APPLID instruction to identify what CICS region we are in, then based
> upon
> the result issued a set current sqlid statement to qualify the table name.
> DOES ANYONE HAVE A BETTER PROCESS? We tried modifying the AUTHID
> parameter
> in the RCT definition for our TRAN-id, but that did not work. (The authid
> wanted a RACF userid, but we had a RACF group - secondary authid)
>
> Also while developing our application, the users dwelt on the unusual
> scenarios which truely required the dynamic SQL. However through
> carefully
> worded interviews we determined that 95% of the querries could be handled
> by
> a few static routines. We still used the dynamic SQL to provide total
> functionality, but it gets hit less often.
>
>
> -----Original Message-----
> From: [login to unmask email]
> To: [login to unmask email]
> Sent: 12/23/99 8:11 AM
> Subject: Dynamic SQL from a CICS Program
>
> Hi. Here's a question that I'd like to throw out to the list for
> discussion,
> and to get some opinions on how we should proceed.
>
> We have an CICS/DB2 application currently under development which
> includes an
> on-line query screen. I know few details about their requirements other
> than
> the fact that they will be selecting a fixed-list of columns from a
> particular
> table, but could have a varying set of where conditions (up to three
> columns
> predicates in any combination, based on what the user chooses), and the
> sort
> order of the result set can also be chosen by the user.
>
> The developers are suggesting that because of the different possible
> combinations of where predicates and sort orders, that they would like
> to use
> dynamic SQL to build and execute the query from the CICS transaction.
> While I'm
> not particularly opposed to dynamic SQL in general, this would be the
> first time
> (to my knowledge) that we would be issuing dynamic SQL from within the
> confines
> of a CICS transaction.
>
> Does anybody have any warnings, tips, gotcha's, etc. about this
> scenario?
>
> Any information and comments would be greatly appreciated.
>
> Thanks,
>
> Bill Gallagher, DBA
> Phoenix Home Life
> Enfield, CT
>
>
>
> the DB2-L webpage at http://www.ryci.com/db2-l. The owners of the list
> can
>
>
>
>
>