Transcript Title Slide: Session Title Always Title Case, Up to 100

My DB2 process is
broken
By Jeff Gross
WW DB2/IMS Technical
Support Manager
Abstract
> DB2 application or vendor product processing can fail for
a variety of reasons due to no changes in the original
application. Learn what time and environmental changes
can do to a process that used to run just fine.
2
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Agenda
> Learn some of the DB2 product changes that can happen and
how to manage this change.
> Learn what changes Vendor can make.
> What happens to data objects over time?
> How can an application process design be less sensitive to
change?
> What to do before an application fails.
> What to do when an application or vendor code fails.
3
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Changes to the product DB2 9
> New features
> Parameter options change
> Command output or actions can be different
> SQL Reserved words change
> SQL Codes change!
> Old features deprecated
> Recommendations
4
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
New features affect implementation
> XML changed dramatically from DB2 8 to 9

XML Extender is deprecated

Drop any user-defined data types with the name XML to prevent problems
with the new Version 9.1 built-in XML data type. You can recreate the existing
user-defined data types with new names.
> Long names in DB2 7
5
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Parameter options change in DB2 9
> Changes in BIND PACKAGE and BIND PLAN defaults
 The default value for bind option CURRENTDATA is
changed from YES to NO.
 The default value for bind option ISOLATION is
changed from RR to CS.
 DBPROTOCOL(PRIVATE) to DBPROTOCOL(DRDA)
6
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Command output or actions are
different in DB2 9
> DB2 drops certain indexes when a unique constraint is
dropped.
 If a table is in an implicitly-created table space, and a
unique constraint on that table is dropped, DB2 drops the
index that enforces uniqueness.
> Changes to -DISPLAY command location name length
 The length of the -DISPLAY command location name can
be longer than 16 characters so that the location name
can have a value of an IPV6 IP address.
 Do you have any operations scripts dependent on this
command?
> Automatic rebind of plans and packages created before
DB2Version 4
7
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
SQL Reserved words change
> New words and term can become non-usable.
 AT
 CLONE
 DOCUMENT
 KEEP
 ROLE
 ROW
> This would mostly affect an application.
8
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
SQL Codes change!
> Adjust applications that depend on error information
that is returned from DB2-supplied stored procedures
 These stored procedures return more meaningful
SQLCODEs and SQLSTATEs than they returned in
previous releases of DB2.
–
SQLJ.INSTALL_JAR
–
SQLJ.REMOVE_JAR
–
SQLJ.REPLACE_JAR
–
SQLJ.DB2_INSTALL_JAR
–
SQLJ.DB2_REPLACE_JAR
–
SQLJ.DB2_REMOVE_JAR
–
SQLJ.DB2_UPDATEJARINFO
> Adju
9
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Old features that are deprecated in DB2 9
> Simple Tablespaces
> XML Extender
> Applications that are compiled on unsupported
compilers that do not support the COBOL COMP-5
attribute
> Warning, these features going away in the future
 DDF Private protocol
 Plans that contain DBRMs
10
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Recommendations for changing DB2
releases (part 1 of 2)
> Be careful in using brand new featured that may
change in the next release.
> Watch for older applications that might have features
eliminated(deprecated)
> Review IBM Install Guide
 DB2 8 has 5 pages of info
 DB2 9 has 23 pages of info
 DB2 X could have ??
11
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Recommendations for changing DB2
releases (part 2 of 2)
> Test thoroughly
 Make sure test subsystem has same attributes as
production subsystem
– Data sharing
– Data size
– If possible, stress test with high transaction rate
12
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Vendor changes
> z/OS changes affect our environments.
 New z/OS every September
> Application Vendors
 How are applications integrated
 SAP, PeopleSoft, Siebel and others
> Tool Vendors
 What should be done
 How has changes affected CA
13
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
z/OS 1.9 Changes
> change to the way DFSMS handles recordsize
> ALLOWUSERKEYCSA
14
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
z/OS 1.10 Changes
> Unable to read the active DB2 log in a non-data
sharing
> This ABEND is caused by a zero value in the
RDISECTN field of the DB2 RDI control block.
> IBM CSECT CUNMCNV
> What will z/OS 1.11 reveal?
15
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Application Vendors
> Who is planning for upgrades to stay in sync with
z/OS and DB2?
> Does anyone have any real world stories they would
like to share?
16
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Tool Vendors (what should be done)
> Should probably be treated in the same manner as
Application Vendors.
> Who is planning for upgrades to stay in sync with
z/OS and DB2?
> Does anyone have any real world stories they would
like to share?
17
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
DB2 9 changes that affected CA Tools
> DB2 9 compressed indexes
> Multiple versions of tablespace in a mixed release (V8
NFM & 9 CM) Data Sharing group
> Partition by Growth option
> Routine name > 16 characters
> Real Time Stats
> Data Sharing group control block chaining
18
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Updating software in correct and
efficient order is difficult to coordinate
> Here is a calendar for z/OS, DB2 and CA DB Mgmt
Tools for DB2 for z/OS
> Need to have a plan on when to upgrade.
z/OS
DB2
CA
App ??
19
Spring Summer Fall
2007 2007
2007
z/OS
1.9
DB2 9
r11.5
SP1
Winter Spring Summer Fall Winter Spring Summer Fall
2007 2008 2008 2008 2009
2009 2009
2009
z/OS
z/OS
1.10
1.11
r11.5
SP2
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
r12
Winter Spring Summer Fall
2009 2010 2010
2010
z/OS
1.12?
DB2 X?
r14?
Recommendation for Vendor changes
> Know the schedule
 Not only Release date, but End of Service date
> Ask the vendor how comfortable they are with the
release
 Check on vendors latest maintenance package
20
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Data Changes over time
> Objects become larger over time
> Objects become disorganized
> Transaction rate grows
21
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Objects become larger over time
> When objects get larger, backups and reorgs need
more resources
> More objects means the DB2 Catalog must work
harder.
 Typical Application from a vendor has 10,000 Tables
and 20,000 Views
> Downstream processes may not be handle the greater
volume.
> Errors such as
 B37 (disk space)
 S878 (Memory)
 322 (CPU Time)
22
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Objects become disorganized
> DB2 objects can be organized in a particular order if a
Clustering Index is used. However, the APPEND
option on CREATE TABLE or ALTER TABLE statement
will allow rows to be added at the end of object or
partition.
> This should affect the majority of the queries since
the original purpose of clustering is to optimize data
retrieval.
> This disorganization can affect SQL CPU time and
Buffer Pool size as more pages need be read.
23
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Transaction rate grows
> Real time activities are affected.
> Batch windows might be at the limit or asked to be
reduced.
> More real storage needed by processes.
 SQL processes
 Application programs
 Vendor tools
24
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Recommendation for what can be done
to predict
> Of course, run appropriate Reorg schedule
 Use tool to determine when reorg should be
performed.
> Have a Resource Manager monitor the processes
 Capture metrics on CPU and I/O
 If a limit or threshold is passed, then investigation can
begin.
> Product to assist are
 CA MICS Resource Management
 CA JARS Resource Accounting
25
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
How can the process or application be less
sensitive to time.
> At design time, code in processes to sense volume
 If a transaction count goes over a certain limit, print a
message.
 If storage is sensed to be low, reduce activity
– Detector has new processes to sense memory
constraints, shut down activity and automatically
restart when constraint is eliminated.
> If an object is a data store, plan an archive feature.
 Use a periodic cycle to categorize data. Could be
monthly, quarterly or yearly.
> Recommendations?
26
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
What do before a problem is found.
> Ask the own of the process what are their
recommendations.
 Type of error settings
 Type of documentation to capture.
 Best Practices for running process
27
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Type of error settings
> Are there Parmlib options?
> Initialization options
> Run time options to gather trace or debug messages.
28
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Type of documentation to capture.
> Job or started task output
> DB2MSTR
> Dump (SYSU or SYSM?)
 Make sure DD is (Mod,Catlg) for multiple dumps
> Is data needed?
 Image copy and Log
> Consistency in question? SYSLGRNX
29
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Best Practices for running process
> Ask the owner of the process what are their
recommendations.
30
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
What to do when a problem is found.
> Have a plan for failure
> Get correct people involved
> Capture information
> Contact process owner to get advice
> Give expectation as to resolution need.
31
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Have a plan for failure
> Have a simple plan that new people can use.
> Have this plan/resource in a fail safe mode.
 Online is best with a local electronic copy
 Doubtful that a printed copy is needed.
> Have this plan be reviewed and signed off by involved
parties.
> This is basic operation stuff..
32
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Get correct people involved
> As before, the first person involved can make all the
difference in resolving an issue correctly.
 Experience
 Training
 Education
 Communication Skills
> Have an escalation process.
 Who is called
 For what expertise
33
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Capture information
> Follow the plan
> If loop or hang, capture dump before cancel.
34
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Contact process owner to get advice
> Has anything changed to affect this process?
 OS
 DB2 maintenance
 Process Maintenance
> Confirm actions taken
> Ask what else they want done.
35
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Give expectation as to resolution need.
> What is the business impact to your company?
 Will this process not running affect bringing up on-line
systems?
 Will a billing or accounting cycle be affected.
 What is that cost to the down stream processes.
> How often are updates needed?
 How do you want updates? Email or Teleconference
36
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Summary
> I hope you found this presentation to get you thinking
of what is happening at your site.
> Are there any experiences you would like to share?
> I thank you for time and knowledge.
37
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Q&A
> Any other questions?
38
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
Terms of This Presentation
This presentation was based on current information and resource allocations as of November
16, 2008 and is subject to change or withdrawal by CA at any time without notice.
Notwithstanding anything in this presentation to the contrary, this presentation shall not serve
to (i) affect the rights and/or obligations of CA or its licensees under any existing or future
written license agreement or services agreement relating to any CA software product; or (ii)
amend any product documentation or specifications for any CA software product. The
development, release and timing of any features or functionality described in this presentation
remain at CA’s sole discretion. Notwithstanding anything in this presentation to the contrary,
upon the general availability of any future CA product release referenced in this presentation,
CA will make such release available (i) for sale to new licensees of such product; and (ii) to
existing licensees of such product on a when and if-available basis as part of CA maintenance
and support, and in the form of a regularly scheduled major product release. Such releases
may be made available to current licensees of such product who are current subscribers to CA
maintenance and support on a when and if-available basis. In the event of a conflict between
the terms of this paragraph and any other information contained in this presentation, the terms
of this paragraph shall govern.
39
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.
For Informational Purposes Only
Certain information in this presentation may outline CA’s general product direction. All
information in this presentation is for your informational purposes only and may not be
incorporated into any contract. CA assumes no responsibility for the accuracy or completeness
of the information. To the extent permitted by applicable law, CA provides this document “as
is” without warranty of any kind, including without limitation, any implied warranties or
merchantability, fitness for a particular purpose, or non-infringement. In no event will CA be
liable for any loss or damage, direct or indirect, from the use of this document, including,
without limitation, lost profits, lost investment, business interruption, goodwill, or lost data,
even if CA is expressly advised of the possibility of such damages.
40
My DB2 process is broken. Fix It! Copyright © 2009 CA. All rights reserved.