I am looking forward to the Baltimore/Washington Db2 Users Group March education meeting – in our new meeting location, the BWI Hilton!!  After months of discussion the board decided this location offers our members convenience, better traffic routes and an improved lunch facility (the Flight Deck; even sounds fun!)

The meeting is scheduled for Wednesday, March 14th, from 9:00am – 4:00pm.  The cost is $250 in advance and $350 at the door.  Register HERE.

March is of course the group’s annual education session.  This year the group has invited Tony Andrews of Themis to present.  Tony’s full-day session consists of topics selected specifically to assist developers with Db2 SQL programming performance and best practices.

Attendees will gain knowledge of SQL programming, performance and tuning, and the SQL-PL native Stored Procedure language.

The material provided to each attendee will include:
– SQL Standards and Guidelines
– SQL coding best practices
– SQL-PL coding best practices
– Many great SQL examples on rewrites
– SQL and program tuning tips
– SQL and program tuning step by step processes

Session Topics:
? Learn the top reasons for poor performance
? Learn our top SQL rewrites every developer should know
? Learn our top SQL Tuning Tips for developers
? Learn our top reasons why you have to watch out for null columns
? Learn the top reasons why developers need to know their data
? Learn what filter factors are and why they are so important
? Learn our top 10 steps to tuning a query or program
? Learn our top SQL-PL stored procedures best practices

Presenter Tony Andrews, Themis Inc., Consultant and Trainer
Tony Andrews has more than 25 years’ experience in the development of relational database applications. For most of this time he has provided development and consulting services to Fortune 500 companies and government agencies. Tony has written literally thousands of queries and programs and has also served as a database analyst. For the last 10 years Tony has been splitting his time between performance and tuning consulting engagements and training.  His main focus is to teach today’s developers the ways of RDMS application design, development and SQL programming — always with a special emphasis on improving performance.  He is a current IBM Champion, and regular speaker at many regional user groups, IDUG NA, and IDUG EMEA.  He is also the author of a book for developers titled ‘DB2 SQL Tuning Tips for z/OS Developers’.

I was on a call this morning with an IBM technical team to discuss our progress filling the seats in one of our upcoming free IDAA classes.  Not surprising, given the number of people on the call (and who they were!) we went a bit off topic and ended up chatting about services and what appears to be – to IBM customers and sellers at least – a shortage of skilled consultants available to help with the “tions” – replication, implementations, migrations and federation.

The Fillmore Group has skilled consultants available to support these efforts, at rates that are typically about half of those being charged by “the competition”.

If your team has a project pending, or one in process, where a skilled consultant, at a reasonable hourly rate, can get things back on track, please contact me.  We are ready to work!  A list of our IBM Information Management capabilities is attached here.

Performance tuning for both DB2 and Q Replication are an ongoing process.  However, there are three tools which provide an initial set of recommendations for bufferpools, Database Manager (DBM or instance) and database configuration parameters, indexes, and other application table objects.

DB2 Configuration Advisor

The DB2 Configuration Advisor recommends the initial values of the bufferpool size, database configuration parameters, and database manager configuration parameters.

To use the Configuration Advisor, specify the AUTOCONFIGURE command for an existing database, or specify AUTOCONFIGURE as an option of the CREATE DATABASE command.

Example:

db2 autoconfigure using mem_percent 50 num_stmts 100000 tpm 6000 admin_priority performance num_local_apps 144 num_remote_apps 0 isolation UR apply none

DB2 Design Advisor

The DB2 Design Advisor is used to improve application SQL performance by creating (or dropping) indexes, Materialized Query Tables (MQTs), and MultiDimensional Clustering (MDCs).  The Design Advisor analyzes an SQL workload against existing table structures and recommends changes to improve performance.  The workload can be derived from Dynamic Statement Cache (DSC).  The DB2 Design Advisor command can be invoked by the db2advis command.

A prerequisite is creating the DB2 EXPLAIN tables for the schema conducting the analysis.

cd /<path>/sqllib/misc

db2 –vtf EXPLAIN.DDL

Then run the db2advis command.

db2advis -d mydb -g -a myuser -x -c USERSPACE1 -f -m IMCP -o db2advis_output

Q Replication Dashboard Performance Advisor

The Q Replication Performance Advisor helps you identify latency issues with your replication environment and suggests ways to lower end-to-end latency.  The Performance Advisor tab is located within each Monitoring Group.  The Q Replication Dashboard Performance Advisor can recommend updating statistics or reorganizing tables.

As promised, the replay of the “Introducing [InfoSphere Optim] Query Capture and Replay” (QCR) webinar delivered on October 16, 2012 is on ChannelDB2.  The presentation materials for the webinar are here: Query Capture and Replay Webinar 2012-10-16.

For assistance implementing QCR: Infosphere Optim Query Capture and Replay

As much as I dislike the relentless repetition that’s part of the nature of the world of blogs and twitter and listserves and email blasts, here I go with a shameless pitch for a TFG special services offering I emailed to several DB2 users earlier today.  I am doing this because, at the end of the day, the rate disparity in today’s DB2 services market baffles me.  Are the ridiculously high rates being charged eroding product adoption?  I am afraid so, which is why we are offering a summer services special. 

Read More…

As you know, I work with IBM’s Q Replication technology – a lot.  Q Replication functionality is delivered in InfoSphere Replication Server.  The challenges are amplified when working on DB2 for z/OS with *really* large tables.  One financial institution at which I am working has tables with over 1 billion rows and hundreds of partitions.  Of course, DB2 for z/OS can manage tables of that size, but what about the tooling?

Q Replication comes with a utility called ASNTDIFF.  ASNTDIFF compares a checksum of rows in the source and target tables being replicated to validate that there are no discrepancies.  Challenge #1 is that when replicating between DB2 for z/OS subsystems, ASNTDIFF runs under Unix Systems Services (USS) that provides the Unix APIs enabled in z/OS.  There are considerations for USS applications that will form the basis for another post.

Challenge #2 is that ASNTDIFF retrieves the rows from the remote system (Application Server or AS) using a three-part-name query across a Distributed Relational Database Architecture (DRDA) connection.  For example:

SELECT * FROM <location>.<schema>.<tablename>

where location is found in the DRDA Communications Database (CDB) portion of the DB2 for z/OS Application Requestor (AR) catalog tables.  You typically run the ASNTDIFF utility on the replication target DB2 for z/OS server.  That’s because the CDB has probably already been configured to support cursor-based loading of the target tables.  Why is this a challenge?  Well, a three-part-name query across a DRDA pipe against a 1 billion row table ran for about 18 hours.  Ouch!

So the basic problem is: how can I get three-part-name queries running across a DRDA connection between DB2 for z/OS subsystems to run faster?  I asked a couple of IBMers and Jim Pickel pointed me in the direction of exploiting OPTIMZE FOR n ROWS.  There’s a good explanation of this in “Limiting the number of DRDA network transmissions”.  Right now the ASNTDIFF utility adds OPTIMIZE for 1000 ROWS and FOR READ ONLY to every ASNTDIFF query.  We’re experiementing with the recommendations in the “Limiting…” document to see if we need to override this hardcoded parameter.  I’ll keep you posted as to our findings.

The second LUW track meeting is scheduled for this Friday, March 6, 2009, at the IBM TEC in McLean, Virginia.  Petrus Chan from the Toronto Lab will be presenting. 

I’ve spent a good portion of the past couple weeks getting individual invitations out to local DB2 for LUW users but if you are out there and I have not personally invited you, please consider yourself welcome – and try to join us.  Details on the presentations are below.   There is no charge for the sessions (they are *free* thanks to Tak Lee and the local IBM team) and pizza will be provided by The Fillmore Group. Read More…