slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Wayne Slomiany January 29, 2008 PowerPoint Presentation
Download Presentation
Wayne Slomiany January 29, 2008

Loading in 2 Seconds...

play fullscreen
1 / 33

Wayne Slomiany January 29, 2008 - PowerPoint PPT Presentation


  • 73 Views
  • Uploaded on

Application Performance for DB2. Wayne Slomiany January 29, 2008. Agenda. Targeted DB2 Data for Testing Efficient Coding Practices Efficient Application Relationships Using DB2 Stored Procedures Resolving DB2 Abends External Influences. Targeted DB2 Data for Testing.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Wayne Slomiany January 29, 2008' - greg


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

Application Performance for DB2

Wayne Slomiany

January 29, 2008

agenda
Agenda
  • Targeted DB2 Data for Testing
  • Efficient Coding Practices
  • Efficient Application Relationships
  • Using DB2 Stored Procedures
  • Resolving DB2 Abends
  • External Influences
targeted db2 data for testing
Targeted DB2 Data for Testing

Testing Audit Reports

z/OS

z/OS

Apply Privacy Rules

Load

Maintain Integrity

Subset

Extract

Distributed

Distributed

Privacy

Audit Reports

less data less resources
Less Data, Less Resources
  • Large TABLE loads can cause excessive resource usage and long LOAD times.
  • Optimization of data used will decrease this.
  • Usage of tools that support SORTKEYS in the load process which bypasses INDEX Key validation on each KEY written.
  • Specifying LOG NO will prevent logging.
relationships of data intact
Relationships of Data Intact
  • Referential Integrity
  • Application Relationships
data relationships
Data Relationships

Production

Subset

Extract

Distributed

z os relationships
z/OS Relationships

Production

AR/RI

z/OS

efficient coding practices
“The later performance problems are caught in the life cycle, the more costly they are to fix. Inefficiencies introduced in design can cost twice as much to fix during programming, four times more during system testing, and eight times more when the application enters production.”

Accenture

Efficient Coding Practices
pro active application relationship management
Pro-Active Application Relationship Management
  • Normalize Database Including Application Relationships during Logical Design Phase
    • Those used in Programs
    • Those used in Driving Files
  • Perform Physical Design (Table and Index) de-Normalizing for Performance
pro active application relationship management1
Pro-Active Application Relationship Management
  • Table joins tend to cause de-Normalization to improve performance.
  • Focus on proper creation of INDEXs.
  • Make Only Acceptable Relationships Available to Applications.
create a baseline of the application
Create a Baseline of the Application
  • Identify what is acceptable (SLAs) for a particular application.
    • End User response times.
    • Overall system performance.
    • Overall resource usage.
  • Use this to compare future releases and Production.
    • Identify problem areas.
    • Identify any changes made.
benefits of db2 stored procedures
Benefits of DB2 Stored Procedures

CICS/ IMS

Batch

Browser

Middleware

Data Servers

S390Servers

  • Reduce Network traffic
  • Improve Security
  • Called from many programs/platforms
  • Easier to maintain data & logic
db2 stored procedures
DB2 Stored Procedures
  • They reduce the SQL statements needed in the calling program.
  • SQL is executed by the stored procedure and results are returned to the calling program.
  • They have separate authority from the caller, allowing them to access and update tables that the caller can’t.
db2 stored procedures1
DB2 Stored Procedures
  • The caller only needs authorization to execute the stored procedure.
  • This provides an additional level of security by preventing corruption of the SQL.
  • This also eliminates outdated Client processes, the current stored procedure is always executed.
db2 stored procedures2
DB2 Stored Procedures
  • They must run using Language Environment.
  • If a stored procedure does abend, there is only the LE dump to work with.
  • The programmer could add his own trace code to try to narrow down the cause of the abend.
db2 stored procedures challenges
DB2 Stored Procedures Challenges

Added Complexity

Analysis

Programmer Familiarity

Debugging

Project Deadlines

Testing

Prevent Problems

ProductionSupport

Customer Satisfaction

Realize Benefits

- PLUS -

xpediter for db2 stored procedures
XPEDITER for DB2 Stored Procedures

-------------------- XPEDITER/TSO - Process DB2 Store Procedures --------------

COMMAND ===>

Primary Commands: SEtup (display setup menu)

Stored Procedure Name ===> DOE.SPTEST1

Load Module name ===> PGM12345 (optional)

Client End User Name ===> JOHNDOE

DB2 AuthID ===> (DB2 Version 5 only)

Luname ===> (DB2 Version 5 only)

DB2 Sub System Name ===> DB61TEST

Maximum number of Tests ===> (1 - 9999)

Specify Execute Jcl ===> N(Y or N)

Jobcard Information:

===> //PFHRAS0 JOB (#ACCONT),CLASS=A,MSGCLASS=X,

===> // MSGLEVEL=(1,1)

PressENTERto process or enterENDcommand to terminate

xpediter for db2 stored procedures1
XPEDITER for DB2 Stored Procedures

----------------------- XPEDITER/TSO - SELECT JOB STEP ----- Row 1 to 1 of 1

COMMAND ===> SCROLL ===>PAGE

Line Commands: Primary Commands:

I - Interactive testing Edit - Display converted selected steps

U - Unattended testing END - Exit without processing

IC - Interactive Code Coverage RUN - Submit and connect

UC - Unattended Code Coverage SEtup - Setup work datasets

SUBmit - Convert selected steps and submit

blank - Reset I/U/C STatus - Display status of submitted job(s)

Dataset:'SYS1.PROCLIB(D61WLM2)'

PROGRAM INITSCR STEPNAME PROCNAME PROCSTEP EXEC PGM

-------------------------- --------------------------------------------------

__PGM12345 ________XPTS0 XPAE001 RUNPROG XPTSO

******************************* Bottom of data ********************************

xpediter for db2 stored procedures2
XPEDITER for DB2 Stored Procedures

------------------------- XPEDITER/TSO - SOURCE ------------------------------

COMMAND ===> SCROLL ===>CSR

BEFORE BREAKPOINT ENCOUNTERED

** END **

------ ---------------------------------------------------Before PGM12345<>

=====> BFUNCTION = 'COMMAND'; /* SET FUNCTION FOR IFI CALL */

000265 IFCA.LNGTH = STORAGE(IFCA); /* BYTES USED IN MEMORY */

000266 IFCA.EYE_CATCHER = 'IFCA'; /* EYE CATCHER */

000267 IFCA.OWNER_ID = 'LOC2'; /* DB2 LOCATION 1=LOCAL, 2=REMOTE*/

000268 FREE RETURN_AREA; /* FREE STORAGE AND THEN */

000269 /* ALLOCATE STORAGE FOR THE */

000270 ALLOCATE 1 RETURN_AREA, /* RETURN AREA */

000271 2 LNGTH,

000272 2 RTRN_BUFF CHAR(4096);

000273

000274 RTRN_BUFF = ' '; /* CLEAR THE RETURN BUFFER */

000275 RETURN_AREA.LNGTH = 4096; /* LENGTH OF RETURN BUFFER */

000276 TEXT_OR_COMMAND=BLANK; /* CLEAR THE DB2 COMMAND AREA*/

000277 OUTPUT_AREA.UNUSED = '00000000'B; /* CLEAR THE UNUSED AREA */

000278 OUTPUT_AREA.LNGTH = LENGTH(INPUTCMD)+4; /* GET REAL LENGTH OF */

000279 OUTPUT_AREA.TEXT_OR_COMMAND = INPUTCMD; /* ACTUAL DB2 COMMAND */

resolving db2 application abends
Resolving DB2 Application Abends
  • Abends, including DB2, can use a large portion of the processor.
  • Resolving them in a quick and complete fashion is important.
  • Correlation of Application and DB2 information is important in resolution.
cics application abend
CICS Application Abend
  • Locate the task interface element (TIE) for “DSNCSQL”.
  • Locate the CICS Life of Task (CLOT) in the TIE for DSNCSQL. Locate the SQL-PLIST using the pointer (CLOTPARM) from the CLOT.
  • Validate the SQL-PLIST by locating the DBRM name once the SQL-PLIST is verified, the statement number, type, APARM (INPUT Host Variables) address, VPARM (OUTPUT Host variables) address, timestamp and pointer to the SQLCA have been located.
  • To locate the SQLCODE, use the SQLCA pointer from the SQL-PLIST.
  • Once located, the SQLCODE must be converted from HEX to DECIMAL before it can be used. For example, A -302 displays as “FFFFFED2” in the SQLCA.
  • Look up the SQLCODE in the DB2 message and codes manual. The message text does not offer much more than an explanation.
  • There are seldom any suggestions as to the cause of the problem.
  • The statement number is used to locate the SQL statement in the precompile listing.
application management solution
Application Management Solution

Reduce excessive resource consumption at the root cause through a proactive and systemic approach.

Application Resource Management provides empirical data to help you make educated decisions.

external influences
External Influences

Database

Utilization

Delivery Stats

Server Performance

Database

Servers

Application

Servers

Web Servers

End user monitoring

Top App by Traffic

Service Dashboard

Top Server Traffic

WAN Utilization

Response Times

Performance Overview

J2EE and .NET Analyzer

distributed mq application
Distributed MQ Application

Client

N-Tier

Server

Back end

Server

Query

Request 1

Reply 1

1. Web application was occasionally failing.

3. Traced the MQ message flow and saw that the 2033 was due to sluggish response from the mainframe application.

Request 2

4. Analyzed the performance of the mainframe application – and saw a significant issue with MQ system-level tracing during bursts of activity.

Reply 2

2. Became aware of problem in real time (without any user interaction). Also got background information: unexpected 2033.

Request3

Reply 3

Request 4

Pdarep2

Pdaaggr.exe

Reply 4

Answer

mq performance impact
MQ Performance Impact
  • Excessive wait time can impact DB2 performance.
  • Difficult to identify the culprit.
environment tuning
Environment Tuning
  • Evolving applications introduce more potential points of failure
  • Environmental complexity contributes to resource growth
  • Gradual MIPS consumption can go undetected
  • Reactive, fire-fighting mindset
  • Limited expertise and skills
  • Mainframe costs (hardware and software) are driven by MIPS
dynamic sql queries
Dynamic SQL Queries
  • Usually rarely used however has great impact.
  • Can involve large table scans with long wait times at the user end.
  • Minimize and control as best possible.
db2 environment considerations
DB2 Environment Considerations
  • Manage BUFFER POOLS based on recommendations per Version of DB2.
  • Proper BUFFER POOL sizes: a good indicator is ratio of GETPAGES to synchronous reads of no greater than 10:1.
  • There is a difference between read-only and read/write tables (maybe a separate BUFFER POOL here).