Tip:
Highlight text to annotate it
X
Hello this is John Campbell a distinguished engineer in DB2 for z/OS development.
Welcome to another lecture in the web lecture series on DB2 for z/OS best practices. Today’s web lecture is going to be about
application best practices specific to DB2 for z/OS data sharing.
++ If I now turn to the second slide here. This is a disclaimer here about the use of trademarks in this presentation. Now let’s
turn to slide 3.
On slide 3 we have the agenda for this web lecture. To begin with I’m going to start simply and start talking about the value of
DB2 data sharing and talk about the high- level implications in terms of application design and also database design.
Then the next part of this web lecture I’m going to drop-down and talk in more detail about the
locking consideration, about high-volume concurrent inserts, how to handle sequentially signed
dumb number keys,
the immediate write option on BIND. Then I’ll switch over to another detail topic about batch processing.
And finally I’ll complete this web lecture by talking about how to roll application changes.
Providing a step-wise deployment of new applications or new versions of application. Now let’s turn to slide 4. ++
What I’d like to do on slide 4 is talk about the technical and business value of DB2 for z/OS data sharing.
On the right-hand side of this chart is a picture of a parallel Sysplex and data sharing group.
And what you can see in this chart here is that all the DASD is connected to the two IBM systems.
And on those IBM systems are multiple z/OS LPARs that contain DB2 members.
You also see that we have two coupling facilities here, again redundancy.
We have an internal coupling facility CF01 running on an IBM system on which there are other z/OS LPARs running.
And we also have a external LPAR, CF02, which runs on a separate box.
Also you can see on this picture, we have two Sysplex timers also for redundancy. And Sysplex timer provides a
consistent time reference around the data sharing group.
So what is the business and technical value of DB2 for z/OS data sharing?
Firstly, it is nearly continuous availability. The idea of Sysplex and data sharing is to mask planned and unplanned
outages so that it appears that the business applications are continuously available.
The second benefit is flexible growth.
That is have the flexibility of growing the size of your system horizontally by adding more systems, more LPARs, and
more DB2 members. Or growing the existing systems systems vertically.
The third benefit is scalability. We can have up to 32 systems, 32 members in a data sharing group.
And these can all scale significantly vertically as well as being able to grow out to 32 systems.
There is also the opportunity here to reduce cost. Once you accept the idea you are going to have multiple systems for availability.
One of the advantages of data sharing and associated technologies is that you can drive the utilization of the overall
configuration to a much higher utilization provided you have good fine-grained workload balancing.
And finally, most importantly it gives you the ability to leverage your IBM System z investment in your existing applications.
And a really big thing about the data sharing proposition, is that you don’t have to rewrite existing applications
or design specifically for data sharing. Of course there will be some exceptions and some optimizations in place,
but the premise here is being able to leverage existing applications that DB2 for z/OS data sharing is a
turbo charger for those existing applications. Now let’s turn to slide 5. ++
Now one of the myths of data sharing is that implementing a parallel sysplex and DB2 data sharing alone will immediately
deliver continuous availability. This really is a myth.
In itself, these are enabling technologies. But what you also need is other requirements in order to achieve your goals.
And one of those goals is it must have application redundancy. In other words, the application must run in at
least two places. Now the picture on the right- hand side of this chart here, shows three IBM systems.
You can think of them as 3 DB2 members. And what it shows here is that application 1 actually runs in 3 places.
On image 1, 2, and 3. On the other hand, application 2, only runs on images 1 and 2.
But now what is significant is when you look at application 6, application 5, and application 3 in this picture.
These only run on one image. And unfortunately from an application perspective, that is a single point of failure.
So for example, in image 3 if the CEC, the LPAR, or the DB2 subsystem were to fail, you would lose application 6 and 3 completely.
Similarly if you look at image number 1, again the CEC, the LPAR, or the DB2 member was to fail.
You would lose application 5 completely. But obviously the best case here is in fact application 1.
Which runs on all 3 images. And therefore you could even lose 1 or even 2 images and this application could
potentially still be available. We have application 7, which runs on 2 images, image 1 and image 2,
so we can lose one of those images and still application 7 is available.
So a key message about this chart, is the need for application cloning or replication.
In other words, the application runs in at least 2 places. So although sysplex and data sharing are enabling technologies,
there are other requirements to achieve your goal of continuous availability. So yes you need active inter-system read-write sharing,
you need to foster and encourage that sharing so that we have fine-grained page and row level locks in the coupling facility.
We need replicated cloned applications In other words multiple instances of application workload need to run across these systems
so we avoid a single point of failure. We need fine grained workload balancing.
Obviously we want to use the aggregate capacity of all the IBM systems in the Sysplex.
But what we want to be able to do is route that workload at the transaction level as opposed for example at a region level.
And basically route that workload, that fine- grained level across the group. And if we can do that, we can improve application
availability, throughput and scalability. And the last thing we need here is automatic route around failure.
So when failures occur, what we want to achieve here is fault tolerant application processing.
And therefore we won’t need the software stack to understand when there is a failure and route work available to other images in
the parallel Sysplex and other members in the data sharing group. Now let’s turn to slide 6. ++
Now a common question here is from the customer is that I want to explore and exploit DB2 data sharing.
And the hanging question is that: Do I need to rewrite or do major surgery to my existing applications?
And the answer is no. The application SQL interface to DB2 is exactly the same in data sharing as it was not data sharing
application. An application now can attach to any member of the data sharing group.
There's no affinity to any particular member or there shouldn’t be.
The data sharing technology as I mentioned during my introduction, is a turbo charger for your
existing applications. Most applications can be cloned with little or no changed and behave exactly the same way.
When you do perform data definition and data manipulation operations this affects the entire group.
But there are a few things that application needs to be aware of.
First of all, the application needs to be aware of some unique SQL codes and reason codes.
There are some new data sharing unique abend codes and messages.
And I need to stress in this web lecture about the increased importance of managing locks, commits, and partitions.
And also with introduction of data sharing their new commands and options on those commands that need to be understood.
Now let's turn to slide 7. ++
There are many good application and database design techniques that have existed for a long time in DB2.
The good news is that as good applications design techniques are usually are also very good DB2 data sharing
application design techniques. And the most significant ones are mentioned on this chart.
So the first one is to minimize locking. First of all, lock contention can badly affect scalability
and so you want to try minimize lock contention for that reason.
And also lock contention particularly in a data sharing
environment, is a critical performance factor and needs to be minimized.
So by minimizing the locking, you have the opportunity there to reduce the possibility of lock contention.
Also by minimizing locking, one can minimize the performance overhead of data sharing.
By reducing the number of locks that have to be propagated beyond the local IRLM to the coupling facility.
Another aspect of minimizing locking, is to minimize the scope of the data being locked on the surviving
DB2 members when the individual DB2 member crashes out.
So generally what you want to do here is take fine-grained page and row level locks and avoid the use of
gross level table locks or partition locks. Second most important technique is to commit frequently.
And the value of this is to reduce the level of logs scanned to limit application rollback and to allow for faster
DB2 crash restart times. Committing frequently is also an important ingredient in reducing lock contention
and also in improving the effectiveness of global lock
avoidance. I will discuss more about global lock avoidance later.
It's also good practice to adopt a consistent policy for acquiring data across multiple tables in the same table or row order.
And this is what helps reduce the possibility of deadlocks.
And actually if you are having deadlocks then by changing the application programs
so that all acquire the data in the same table or row order this can convert a deadlock into a serialization.
One important message here is about not hardcoded values for a particular DB2 member.
So for example in the batch JCL of the batch job specifying a specific subsystem ID.
It's much better it's to specify the group ID there and that way that same batch JCL can be used to run that batch
program on any member of the data sharing group. Now let’s turn to slide 8. ++
Continuing the theme of application design. There is a need to rethink a few application design choices.
The first one is an in memory counter. Some applications today use in memory counters which is specific to that application.
But more importantly, specific to that application instance.
So when you are running a Sysplex with the application being cloned this meant you don't have a
single in -memory counter. Each instance of the application has its own in memory counter.
And therefore solution is required. Some applications, may be using a single row table to
provide a sequential counter.
There's two problems with this. First of all it’s a scalability constraint.
It will probably cause a locking hotspot or chokepoints as the amount of workload increases.
Aand secondly, it becomes a single point of failure covered by retained lock when the DB2 member fails.
So when one DB2 member fails, but because we talked the whole table the whole row page with a retained lock the
real problem with the surviving DB2 members cannot work because they are blocked by that retained lock.
And the last point here is about the processing of local timestamp. That is it will not be unique across a Sysplex.
So you need to use alternatives for such unique key. So DB2 provides a scaler function called generate unique
which provides a Sysplex unique timestamp to be used by an application. Now let's turn to slide 9. ++
Continuing this theme on how to rethink design choices. One thing that needs to be discussed here is about avoiding affinities.
Affinities limit the ability to download work load units around the parallel Sysplex and the data sharing group.
And there's different types of affinities. First of all there's inter-transaction affinity.
The application must not be designed on the assumption that subsequent executions of the transaction will run on the same IBM
system, the same image, DB2 member as the first transaction.
The application must not be designed on the assumption that the order of the arrival of the transaction will be the order of
execution of those transactions and their associated work units. Next thing is system and image affinity.
This may be logical by LPAR or DB2 member. Sometimes customers will practice system or image affinity
because for example you want to reduce MLC costs or maybe it's because they have a particular
cryptography device or a special printer on a particular system.
But basically, by having system or image level affinities again you compromise the ability of the system to
route work away from failures. Another thing you want to do here as well,
is avoid limiting access to a DB2 page set or partition from a single-member.
This effectively makes continuous availability unworkable. When you do this, when the failing DB2 member fails,
this will result in ‘X ‘ mode page set P-lock being held on that page set partition and this will block other
members of the data sharing group from accessing any data in that page set partition until the failed DB2 member
completes the forward phase of DB2 crash restart. Another thing that we start thinking about is about online rolling of application
changes and new versions. This is an environment in application development and what I shall talk about later in this
presentation is try to provide some thought leadership and some encouragement as to how this might be achieved.
Now let's turn to slide 10. ++
So here on slide 10 I like to talk about database design considerations related to data sharing. I will talk about the use of a
particular database functions and features that can be used to support parallel Sysplex processing.
So the first one is clustering an then I'll talk about partitioning and then also some specific DB2 unique capabilities.
So clearly when it comes to parallel processing. Clustering is pretty important here.
In other words the row sequence in the tables. Because you can identify the clustering index to DB2.
And the new rows are actually inserted into the table DB2 tries to insert those rows in the right position so you maintain clustering for
later query processing. The advantage of designing your applications with parallel processing
is to take advantage of clustering to basically minimize random I/O, making batch processing as efficient as possible.
Clearly you get the gain of application parallelism here providing multiple copies of the same application at the same time.
Then what you want to try to do is leverage table partitioning. In other words, the physical partitioning of a logical table.
This is transparent to the application and has the ability to reduce logical and physical contention.
So what would happen here is you would run one copy of application against each partition are the table and they run in parallel to reduce
the overall elapsed time. Now there are some DB2 product unique capabilities which are listed here.
Things like MEMBER CLUSTER and TRACKMOD etc.
And what I will do on the following charts here is to delve into more details of these as we go through.
But a key thing and an important application that's going to be deployed in a parallel Sysplex and data shareing
environment is to is to involve the application DBA as soon as possible in the development cycle. Now let’s turn to slide 11 ++
So on slide 11 here I like to drill down provide some more information in the area of locking considerations.
One of the most important things is to use a lightweight locking protocol. DB2 provides different locking protocols
such as repeatable read, read stability, cursor stability, and updated uncommitted read.
And for high-performance and high concurrency you want to use a lightweight locking protocol such as
cursor stability or possibly even uncommitted read if the application can accept those semantics.
DB2 also has a concept called lock avoidance which is available for the isolation level called cursor stability.
Now lock avoidance only applies to read-only SQL. And what it means by lock avoidance is that a given
SQL statement becomes eligible for lock avoidance. And notice the emphasis on the word eligible.
Just because an SQL statement is eligible for lock avoidance doesn't mean that lock avoidance is actually achieved.
Lock avoidance can be effectively achieved. This will increase concurrency by reducing the number of lock requests and the lock
contention.
By decreasing the lock and unlock activity this can reduce CPU's resource
consumption. In data sharing this increases the number CF requests and the associated CPU overhead on the coupling facility.
And it also has the effect of minimizing the impact of retained locks.
So the strong recommendation here as a design point for data sharing is to either use isolation cursor stability
with current data no or to use isolation UR.
Another strong recommendation is to define cursors for their intended use.
So if a cursor is only going to use for read- only processing then specify it as read-
only. If you're going to read data through a cursor and then subsequently update it than specify the for update of clause on the cursor.
Next use a timestamp column on each table. This timestamp column can be used to recall the
timestamp of the insert and the last update to those rows. And then what you can do in your application is use the
value returned from a select as a predicate on a search update and delete to enforce data currency.
Now the concept of using a timestamp here on the table and using the predicate on a search update or delete is
often referred to as optimistic update or delete.
And this can be very efficient and also deliver very high concurrency. DB2 today has a row change
timestamp that provides the function of this timestamp column.
Where DB2 will automatically update that column value on both the insert and the subsequent update.
So committing frequently is very important in a data sharing environment and it's important that all applications
and I stress the word ‘all’ applications commit frequently.
The value of the commit is that this enables the value of the global commit LSN value to move forward and
therefore improve the effectiveness of global lock avoidance.
Alternatively, up all it takes is one bad application, rogue application, that does not commit
frequently and this will prevent the global commit LSN value moving forward and
therefore the effectiveness of global lock avoidance for all applications running inside the data sharing
group will fall off and become less effective. A key thing that often gets asked this how do I monitor the
effectiveness of lock avoidance. The general rule of thumb is to monitor unlock requests per commit.
And generally speaking, you want the number of unlock requests per commit to be less than five. Now let's turn to slide 12. ++
Now on the same theme as locking now, I like to know drill down and talk about the meaning of current data.
Current data has a value of either yes or no. And it helps determine the currency of the data returned to an
application cursor. And it only applies to those applications are bound with isolation cursor stability.
That now begs the question what is the isolation cursor stability.
When you bind a plan or package with the option isolation CS.
This indicates that the data fetched by a cursor is actually committed. And if application process
returns to the same page another application might in the meantime have since updated,
deleted, or inserted the qualifying rows. If the cursor is actually defined with the for update of clause,
the data returned by the cursor to the application is stable and it may not be updated by another
transaction while the updatable cursor is positioned on it.
If the cursor is actually defined as read only, or is left be ambiguous, then isolation level cursor stability
ensures the data returned to the application is committed and the stability of the cursor is determined by the current data option
described above.
Now let's turn to slide 13. ++
What we generally encourage customers to do is to switch from using current data yes to current data no.
And in general that can be done for most applications without any adverse affect.
Now what I like to do is introduce and discuss the following scenario. In this scenario we have two programs
program A and program B. Program A is bound with isolation cursor stability and current data yes.
And it fetches a row from a SQL select cursor. The row was read from the base table and the
result set was not materialized into a work file. So a key point is the row is read from the base table.
Now program B tries to update the row just read by program A.
In this case program B the access is not allowed because program A specified current data yes.
And this has caused a lock to be acquired on the page and row when program A read that data.
So basically that page row lock, locks program B. Now in this example current data yes a significant.
It protects the data intended to be read by program A and between the timeframe from the fetch until the searched update.
Now if current data yes was specified but the result set was now materialized into a work file
then current data yes provides no protection at all. So some people think that by specifying current data yes,
they are protected. But if the result set is not read from the base table space but materialized into a work file,
then current data yes provides no protection between the time of the fetch and in the time of the searched update.
Now in the above example if program A was rebound current data no, then program B would be able to access the row just
fetched by program A and be able to update it. And therefore we would end up with the missing update.
So this example here shows you where current data yes will or will not protect you.
And shows you the implication of using current data no.
And what I'll talk about soon is about recommended coding practices for this area. Now let's turn to slide 14. ++
On slide 14 here, I have a list of things here for recommendations which are about reducing lock contention.
First of all, use lock size page as your general design default.
Only use lock size row when it's appropriate and beneficial.
In some cases you might want to on a small size table try to simulate lock size row by specifying lock size
page with max rows of one. That way you only get one row per page and effectively you're getting row level locking.
Commiting frequently to improve global lock avoidance and reduce lock contention.
Avoid the use of the SQL lock table statement. This means you are locking the whole table.
In which case you’ve given up on fine-grained page and row level locks.
The danger of course is that if the DB2 member crashes out you’ll end up with a retained lock over the whole table.
Avoid any dependence on lock escalation. When lock escalation occurs, then a row or page level lock will
escalate to tablespace lock and that will result in the same sort of problem you get with SQL lock table when an individual DB2
member crashes out. When you actually finish within SQL cursor close that cursor.
As that may result in freeing up any possible read locks that are still being held.
If you have any open cursors defined with hold, close those cursors ahead of commit.
Otherwise those cursors will be held open across the commit and the claim count will be elevated at +1.
Use of table and index partitioning has a lot of value and can potentially reduce inter-DB2 lock contention provided
insert and read activity is spread over the different partitions of the index and table space.
And as already mentioned, accessing data rows and tables in a consistent order can help reduce the possibility of deadlocks
across different application processes. Now let's turn to slide 15. ++
Now another common question that comes up in the area of locking. Is how do I know what the chokepoints are?
What are the hot points where I get lots of lock contention.
Well, you obviously can check in the DB2 statistics and accounting traces and reports to see whether lock
contention either at a system level or application level is a problem.
But given that it is a problem you need to do some detailed analysis.
So what you see on this chart here, chart 15, is that for detailed analysis you need to start a more detailed
DB2 performance trace. And in this example here where we are starting performance class trace 30
with a long list of IFCIDs which will give you detailed lock and latch tracing.
Now this trace is very expensive. You probably only be able to run it for a small number of minutes because of the volume of data
involved and the CPU overhead. And what you then want to do is post process this data.
So you get it out into a spreadsheet and do some spreadsheet analysis on the data.
So DB2PM and PE enables, gives you the capability to generate a .CSV file.
And what you see in the example here is a locking report request to DB2PM level
suspension requesting a spreadsheet DD output and you want the output ordered by page set within database.
Now let's turn to slide 16. ++
Now the next major topic here is about high volume concurrent inserts. And I want to talk about a number of features that are available
for use that can help the situation where you have the situation high volume concurrent inserts and you want to could design for
maximum throughput and low contention. So the first thing is about the selective use of member cluster.
Member cluster is not a general recommendation. It's a niche recommendation
and should only be used when you have excessive page page P-lock or page latch contention when you insert into a table which
is in append mode or when you are inserting at the end of a page set partition.
When you are inserting in append mode or at the end of a page set partition,
then the space map pages or the last space map page can become very hot with lots of contention.
And furthermore if you are using row level locking, then the data pages at the end of the page set partition can become hot.
So what member cluster does is it significantly increases the number of space map pages.
So normally a space map page governs up to 10,000 data pages.
When you use member cluster, each space map page only governs 199 pages.
So by switching on member cluster you get many more space map pages.
The other thing that happens is that at runtime you end up with a fairly loose affinity whereby each member
owns its own set of pages associated with the space map page.
So at runtime each member effectively owns a space map and the associated data pages.
Now one of the downsides of member cluster is that you sacrificed the clustering of data rows and this could adversely
affect query performance. Now clearly if clustering is important one could perform a more frequent REORG of the table space
object in order to get the rows back into the clustering sequence and resume good query performance.
But for many applications clustering is not important it is not significant. So one of the things we did in DB2 version 10 is we added
a new statistic into real-time statistics which in this chart hears
RTS SYSINDEXSPACESTATS.REORGCLUSTERS ENS.
And this statistic tells you or indicates to you the clustering sensitivity of the queries.
So obviously, if the clustering sensitivity is very low, then therefore the value of clustering is low and therefore
there's no disadvantage on switching to member clustering and giving up data row clustering.
The other good news in version 10 is that member cluster is now available for the first time for the universal table space.
It's available for both UTS PBG and PBR in version 10.
Member cluster is still not available for segmented and there's no plan to make member cluster available for segmented
table spaces. Use of member cluster is often augmented by the use of TRACKMOD NO.
TRACKMOD is a facility inside DB2 which is on by default which actually tracks modified pages in the space map.
And the information about modified pages in the space map is then used by incremental copy to quickly
identify which pages are changed since the last copy and now have to be copied out.
So the default is that TRACKMOD is TRACKMOD YES and that means that we are tracking change
pages in the space map. When you're in the situation where a custom installation makes no use incremental image copy
or the performance of incremental image copy is not significant then using TRACKMOD NO in conjunction with member
cluster is a way of reducing space map contention and in reducing page P-lock and page latch contention.
Now let's turn to slide 17. ++
Reducing index overhead. When it comes to high volume concurrent insert, one way of improving performance and especially in data sharing is to reduce the number of indexes that have to be modified.
So keeping the number of index on a table to a minimum will reduce the data sharing overhead.
So if you have indexes that are unused, it makes sense to delete them. So again in DB2 version 9 we provided a new statistic in r
eal-time statistics called last used in the table called SYSINDEXSPACESTATS.
And this is updated once a day, once every 24 hours after mid-day.
And if that last used timestamp is not being updated this means that this index is not being used.
So by dropping it you reduce the number of indexes that have to be maintained by both insert and delete.
The more difficult situation is when an index is actually used when it low value and not used very often.
Or it may only help a small number of queries. So again consider dropping low value our low use indexes.
Given that you have a certain number of indexes, other important thing to do when it comes to high bond concurrent insert
in data sharing is to try and reduce as much as possible painful index leaf page splits.
Now one way of helping this is first of implementing data and index partitioning
because each index partition has its own index B-tree and by having multiple index B- tree's
and having the insert spread around the index partitions we can have more concurrency in terms of those painful leaf page splits.
But also there other enhancements they came in DB2 version 9 which can help. First of all we allow a larger index page size.
This can be used with or without index compression. Similar here I'm thinking about using larger index page size without index
compression where you have a sequential key insert pattern. So that leading to a larger index page size one can have fewer leaf page splits.
And I would encourage some moderation here to go from 8 K to 16, or 32 K.
The danger of going to a very large index page size is it can adversely affect the buffer pool hit ratio for read when you have random
reference to data in the buffer pool. The next thing here in version 9 is asymmetric index leaf page splits.
This is where DB2 is actually tracking the insert pattern of the new keys being inserted into index pages.
And rather than during the previous 50-50 split with 50% of the entries goes to the old leaf pages and 50% go to new pages,
DB2 will now adjust that split maybe it's 100 – 0. It might be a 70-30, it could even be a 40-60 split
based on the prevailing index key insert pattern. Now let's turn to slide 18. ++
Continuing on the topic of high volume concurrent insert. I now want to switch onto different subtopic which is called logging
considerations. In some extreme cases logging bandwidth can be a constraint.
What you should do to make sure is consult the DB2 statistics trace.
Looking at the number physical writes and also the write of latch class 19 latch contention.
Latch class 19, LC19, is related to the log output latch.
And is one latch for the log output buffer which serializes the insertion of new log records into the logging stream.
The good news is that version 9 NFM and version 10 NFM provided significant relief in most cases for latch class 19 contention.
And significantly reduce what was called LRSN spinning. LRSN stands for log record sequence number.
And prior to the advent of version 9 each log record on the member of the members log had to have a unique LRSN.
In this LRSN was a high order six bytes of the store clock. And it move forward every 16 µs.
So as the processes processors got faster there's chance of getting duplicate LRSN's.
And therefore what DB2 used to do was loop in its code for up to 16 µs waiting for the LRSN value to move forward.
So the most welcome changes in version 9 and version 10 provided relief in some cases.
The last topic here inside this is about I/O bandwidth bottlenecks. So assuming you look at the statistics
rate and you see significant physical writes. Then what you should be looking foris to see whether or not there's a large volume of data
to be written and see if there is a bottleneck.
Now things that can be done to help if there is an I/O bandwidth
bottleneck. Is basically to take those tables with wide rows that compress well and to use table compression.
Consider possibly using data set striping. Or moving the DB2 active logs to faster devices. Now let's turn to slide 19. ++
Now let's talk about affinity routing. I really am talking about it yet again.
Now earlier in this presentation I made some very strong statements that affinity routing is bad.
And it was bad because it could compromise availability and result in single points of failure.
But now I want to re-examine affinity routing based on the context of parallel batch jobs.
Here about what you might want to do is you a very significant long-running batch process.
And what you want to do is run parallel copies of the same batch program in parallel in order to reduce the overall elapsed time.
And what you may decide to try and do to improve effectiveness and efficiency is basically to partition that table and
run one copy of that batch program against each partition.
So effectively you create an affinity between an instance of the batch program and a given partition on the table.
So clearly this is only reserved for special circumstances. This is not a recommendation for OLTP.
This can have great potential to improve batch performance and reduce global lock contention.
But if it's taken to extremes again it could compromise the benefits of data sharing in terms of operational simplicity,
continuous availability, and scale-out . Another thing that may be worth considering is data partitioned secondary indexes, DPSIs.
These are a useful technique as well to minimize physical and logical interference.
But note carefully, DPSIs can only provide unique constraint within the partition.
And good query performance may be compromised if there are no WHERE predicates on the partitioning key column in
the subsequent queries. So many cases DPSIs cannot be used today because of those restrictions. Let's move to slide 20. ++
Now I would like to talk about sequentially assigned dumb number keys. Now this is a common application requirement
where an application wants to have a unique identifier sequential in nature.
And applications are seriously challenged in this space. Because often if you do a user- defined solution there's
often a compromise here between performance scalability and continuous availability.
So for example, I mentioned earlier about the problems with a single row table used as a counter.
In this case, what the application does is it has a single table it reads that row every time, updates the counter
and updates the row, and then that value is used by the application. So if you have enough concurrency
this will become a scalability bottleneck and in the event of the DB2 member crashing out, this will mean a retained
lock on that row or that page. And that will block new work arriving on the surviving members.
So I now want to talk about some other solutions. One solution is to use an identity column or to pull a
value from sequence object. This is far superior in performance and availability compared to that
user-defined table space solution I talked about above. It also provides caching as well.
So first of all, why is a good from availability viewpoint. Well DB2 uses a special non- modified P-lock to
serialize the update to the SYSSQUENCES page inside the DB2 catalog.
DB2 forces the updated page to group buffer pool prior to releasing the P-lock. If the DB2 member fails in the
middle the P-lock is not retained. There are particular as SQL DDL changes for identity columns for SEQUENCE objects you can say
GENERATED ALWAYS or GENEERATED BY DEFAULT.
You can say START WITH or increment the value by one or typically five. And one important option here is about whether you
can specify NO CACHE or CACHE. CACHE can cause nonsequential value assignment
but is the best option here in terms scalability. So the risk of using the CACHE option is that you can end up with cache values that are not
being used after DB2 termination. And the last option here, is about using the generate unique scalar function.
And what this function does is generate a 13 character identifier which is a unique Sysplex wide unique key. Now let's turn to slide 21. ++
On slide 21 here, I want to introduce and discuss the IMMEDWRITE bind option.
Now this immediate right option should only be used in small numbers cases and it really is a niche solution.
But it is solution or possible solution for the following problem.
So what we have here is transaction Tx1 making an update on DB2 member DB2 A.
Now before committing, transaction 1 actually spawns another transaction 2.
And what happens here is that transaction 2 is actually running dispatched on the second member DB2 B.
And it wants to actually to read the update has been made by transaction 1.
The problem is that transaction 1 may not have committed yet. Right.
And therefore transaction 2 may not see the update. So unless transaction 2 is isolated isolation level repeatable read
it may not see the update. So let’s repeat this example again.
We have two transactions and two members. Transaction 1 runs on member A and spawns a second transaction transaction 2.
That is dispatched and running on member 2. And the problem is that the transaction 2 was spawned before transaction 1 committed
And the problem is transaction 2 cannot yet see the update performed by transaction 1.
And the only way around this would be to run with transaction 2 with an isolation level of repeatable read.
And very few customers in the world would run with isolation level RR because it restricts concurrency and scalability.
Now let's turn to slide 22.
So what are the possible solutions. While we could run transaction 1 and transaction 2 in this OLTP application on the same member.
And clearly for what I said earlier this is something you really do not want to do in an OLTP environment.
You have actually created a member affinity. And if that member fails then you've lost your application.
The second one is, let transaction 1 commit first and then spawn transaction 2. And that would be a good practice.
The third one as I discussed honest previous slide would be to run the transaction 2
with the package statement running with isolation level repeatable read.
This is not desirable because it will kill concurrency and scalability.
Or the other option to be considered carefully, is to use the immediate right option on the bind for the transaction 1.
So what you could do is bind the package used by transaction 1 with IMMEDWRITE YES.
And what then happens is that transaction 1 will immediately write any uncommitted buffer update to the group buffer pool yet
immediately. And not wait until the commit point. Now what happens is the updated pages are not pushed out to the group buffer
pool until we get to commit point. So key point about this immediate write is that it does incur a performance
penalty, it is most certainly not a general recommendation, and it's much better to defer spawning the
transaction until the transaction 1 is committed.
The immediate write has no effect to updates to non-group buffer pool dependent objects. It has no effect in the non-data sharing
environment. And I repeat again, use of immediate write YES should be considered very carefully because of the downside
and used very sparingly. Now let's turn to slide 23. ++
I now want to return to the subject of batch processing. Batch processing is still a significant component of major applications.
And there are many batch processes that run for a very long time and which also have technical and business deadlines to be met.
So one way of dealing with a challenge like this is to run parallel copies of the same application in parallel.
And for each parallel copy of the batch program to run against a subset of the overall table and maybe run it gets a partition.
So when I talk about designing for parallel batch here what I'm about doing here is avoid serial processing either in full or in part.
What you are trying to do is get as many parallel copies in parallel to the saturate the available processor and Sysplex capacity.
The first thing you have to do is determine how many parallel streams are needed to meet the last time requirement.
And that will then determine how many data partitions and index partitions are required.
And the next thing is when you have data- driven programs is to take the input records sort them into clustering sequence.
And therefore that has the effect driving dynamic sequential prefetch. And that has the benefit of reducing random I/O
and exploiting data row clustering. One thing that is very important in non-data sharing but more important in data sharing
is that when you have long-running processes that you have immediate commit and restart points.
And so for long-running batch programs having this intermediate commit and restart function should be should be considered
mandatory. And you need to evaluate what tools and middleware are available in the
marketplace to help you do that which applications can use. Some requirements that need to be addressed are.
You need flexible criteria based on CPU consumption.
That may be represented in terms of the number of SQL calls and/or based on elapsed time.
The criteria to be externalized into at a DB2 table and easy to change.
The new criteria should picked up on the fly by the application periodically for example after so many commit points.
And you also want intelligent commit point processing based on prevailing operating conditions.
So for example, if the system is very busy in the online day you want to commit more frequently.
But overnight when there's less interference from online transactions you can afford to take commit points less frequently.
And certainly if you want to design your batch workload so it can run in to and during the online day then it's very important to have
frequent intermediate commit points. So overall what you need to do here is evaluate and acquire a robust reliable product.
Now let’s turn to slide 24. ++
This is the last part of this web lecture. And this is like the nirvana for applications.
The ability, what everybody would like to do is have the ability to roll in a new application or new version of an application across
the images of the data sharing group without having to take an application outage.
Today in major releases of enterprise applications the application has to be quiesced .
And therefore the application is unavailable while a new version of the application programs is rolled in and the application
packages are rebound. So first of all, on chart 24 here,
I'm not talking about the applications here, I’m talking about the general principle of the parallel Sysplex.
So what we have here is a three-way parallel Sysplex, three IBM systems and the same application running on each system.
And what you see here on the top left-hand side is the existing application running existing images all on the same
level of z/OS the same level of DB2. What you see on the top right-hand side at time 1
is that you decide to take image 1 out because you are upgrading DB2 or are upgrading z/OS.
The application continues to run on images 2 and 3. Then at time 2 you basically then update image 2.
The application is quiesced on image 2 only, it continues to run on image 1 and 3 and we roll in the new level of
z/OS or DB2 on image 2 and then we start that image. And then as we see here in time 3
we then actually queisce the application image 3. And we roll in the new z/OS or DB2 on image 3
and then bring it back to life. So because of the application redundancy here we are only able to take one
image out of the system to upgrade to z/OS or DB2 release and application availability was maintained
throughout. So let's do what system software changes rolling in, rolling in changes and avoiding planned outages.
Now let's turn to slide 25. ++
Now we come to the real challenge here the golden nugget about all of this.
What you like to be able to do here is run different versions, different levels of the same application across the data sharing group.
So on this chart 25 here I'm just talking about the principles.
Here at time 4 here we have same version, single version of the application running across the whole images.
Now we have at time 4a is we introduce a new version version N+1 of the application.
At time 4b we introduce N+1 on the second image.
And at time 4c we roll in version N+1 on the third image.
So the same principles except now we are doing it for application software as opposed to system software.
Now let's turn to slide 26. ++
So let's talk about some principles. If you are going to roll application changes, in other words step wise deployment and versioning,
you have to design for certain principles. First of all, backward compatibility.
It's important that the new version N+1 must coexist with the old version.
So this backward compatibility must be built into the new version of the application N+1 as it is being developed.
Forward compatibility. The old version needs to tolerate the new version so both version N and N+1 can coexist at the same time
across the images. And that means required toleration changes may need to be applied to version N before deploying the new version.
Now this is very similar to what DB2 does today. But there are some changes in DB2 are what are called two part fixes.
We have to roll a toleration fix to the existing members first before you put on the new fix.
But ultimately what we're talking about here is coexistence. Coexistence of the old and new application level.
So we need to allow different versions to exist. But actually function differently.
Any common services need to be version aware and function accordingly. Depending on which version is running.
You may end up in situations where in fact there are changes to the data model. So in fact, the new version of the application
may actually be populating or updating data in a different way.
But generally, if you are going to design for this you need to design for at least two versions N and N+1.
And this sort of rolling change in terms of backward and forward compatibility and the principles of coexistence
need to be built into the applications. Now let’s turn to slide 27. ++
So continuing this theme. The consequences are that this may require version awareness code in each module.
In other words in other words dual path code. Basically taken a different path based on version and release information.
The application would need to check the version and release compatibility of the application module at
initialization time and must include logic for non-tolerant situations.
Now in order to implement this solution here this may necessitate the creation of temporary application affinity
in order to be able to do this. And it also has some challenges here for change management.
Because of the classic way of doing this we have to take an application outage you tend to go for purer larger versions or
releases of your applications. If you can choose this rolling change methodology.
Then you may decide to go for frequent smaller versions and implement more frequent changes.
So what you end up is a trade-off between if you make large changes, a small number of large enterprise application releases,
you end up with less changes but high-risk. On the other hand with this with this rolling change approach
is you can actually achieve frequent smaller versions you end up with a faster time-to- market. Now let's turn to slide 28. ++
On slide 28 here I like to summarize this web lecture and highlight some key points.
First of all applications have an important role to play in continuous availability.
All applications requiring continuous availability need to have all transactions or system affinities removed.
Applications need to be cloned or replicated to provide redundancy and must run in at least two places.
If a particular single point of control or serialization between two points that could cause locking retained locks,
these need to be designed away. For most applications, the majority of applications, data sharing is a
turbocharger for existing applications. The SQL interfaces is exactly the same and only relatively minor changes
made are required when you want to avoid a particular locking hotspot. You want to try and use a lightweight locking protocol.
Isolation level like cursor stability and designed to exploit lock avoidance by binding your packages with current
data no and coding accordingly. You need to commit frequently
so that failed DB2 subsystems can restart quickly and also to ensure the effectiveness of the global lock avoidance algorithms.
When you have very large batch processes which run for a long time and have very strict deadlines, business and technical deadlines.
Then I provided some guidelines here about how to design for parallel batch. Specifically in terms of data and index partitioning.
And running multiple parallel copies against distinct partitions.
And also trying to leverage data row clustering. And at the end of this web lecture I also talked about the next great challenge in
application development which is about providing a solution for the online rolling of application changes and new versions.
So I would like to thank you for attending today’s web lecture and I hope the information was useful.