Informix Celebration

Agenda:

10:00 – 10:15am                Welcome

                                                Speaker Introductions

                                                Agenda review and session expectations

                                                Administrative items (lunch, restrooms, etc.)

10:15 – 11:00                      Informix Packaging and Licensing Update

Presenter:  Warren Heising, IBM Certified Consulting IT Specialist

Abstract:  Warren will provide an overview of the latest Informix licensing options and present licensing examples and pricing.  Warren will include licensing reinstatement examples, new customer options and considerations for current Informix customers.

11:00 – 12:00                      Reasons to Upgrade to Informix V11 

                                                Presenter:  Carlton Doe, Informix Executive IT Specialist

Abstract:  The Informix product development team has steadily increased the functionality of each new version. For organizations currently using an old version of Informix, Carlton will provide an overview of the new technical features of Informix V11 and why you should begin your migration to Version 11 today.

12:00 – 1:00                        Lunch

1:00 – 1:45                           Upgrade Migration Considerations

                                                Presenter:  Carlton Doe, Informix Executive IT Specialist

Abstract:  Remember when planning an upgrade meant not seeing your family on the weekends for months at a time?  Not anymore.  Carlton’s presentation will provide an overview of what to expect when upgrading to Informix v11.  There ARE changes and the process must be planned, but you may not miss dinner this time around!

1:45 – 2:30                           Getting the Best out of Informix Support

                                                Presenter:  Charles Gonsalves, Senior Manager, Worldwide Informix Support

This session will focus on how to effectively engage the Informix support organization and take advantage of all the resources available. This includes our client self-assist options, how to use the web including My Notifications, dial in options via AOS, using our Must Gather documents and IBM Support Assistant. The presentation will also include unique support offerings such as Upgrade Bridge, Continuing Support, and the Accelerated Value Program.

2:30 – 3:30                           Crystal Ball Report:  What’s Next for Informix?

                                                Presenter:  Carlton Doe, Informix Executive IT Specialist               

From a technical perspective Informix users have never been disappointed with the new features and improved functionality of Informix releases.  Each new Informix version has introduced exciting new functionality due to an excellent Informix engineering team working at IBM.   Carlton’s presentation will show you why “Panther” will be no exception to the rule. 

Bio: Charles Gonsalves, Sr.Manager - WW Informix Support, IBM
Charles has 14 years of Informix support experience in various capacities. He started as an Informix trainer in India in 1996. He is an Informix certified Database Specialist, Informix Certified System Admin and Informix Certified 4GL Developer. He also achieved Informix Down System certification in 1998. After serving as Customer Support Manager (South Asia) for 2 years, he relocated to the US in 2000 to manage a team focused on Informix deep diagnostics, bug fixing and special builds. He has also managed the Informix tools and Informix premium support teams in the subsequent years and took over the responsibility for WW Informix Support in 2007. Charles operates out of San Jose, CA office.

Bio:  Warren Heising, Certified Consulting IT Specialist IBM

Warren is a Certified Consulting IT Specialist for IBM working in the Mid-Atlantic region.  He has over 25 years experience in the database field including 9 years at Informix prior to the IBM acquisition. He has a strong background in both OLTP and DSS Informix products and has worked with numerous clients in both Commercial and Federal accounts. Currently, he supports the full family of IBM Informix products along with DB2 and Optim products for the Mid-Atlantic region and is actively involved in scheduling and delivering Proof of Technologies and Proof of Concepts.

Bio:  Carlton Doe, Executive IT Specialist, IBM
Carlton’s official title is Executive IT Specialist for IBM but he’s really just a technical sales wonk. He used Informix as a DBA and application developer for many years before joining Informix/IBM. He’s a cofounder of the International Informix Users Group (iiug.org) and the author of a number of Informix books and other publications. All that is supposed to make you think he knows something. His latest Informix 11.50 book, Administering Informix Dynamic Server, Building the Foundation is available at e-tailers everywhere
(http://www.amazon.com/Administering-Informix-Dynamic-Server-Foundation/dp/158347076X/ref=sr_1_1?ie=UTF8&s=books&qid=1276291949&sr=8-1).

IBM Silicon Valley Lab’s Vijay Bommireddipalli is presenting at the users group meeting the 15th on .NET and Java tuning, then leading the pureQuery PoT the following day.  Whether your organization is considering pureQuery or not, Vijay’s presentations will help you better understand best practices – and how pureQuery evolved to make them easier to implement and monitor.  Joining Vijay at the users group is Toronto Lab’s Bob Harbus, presenting on what you need to know about upgrading to DB2  v9.7 and enabling Oracle applications to run on DB2, and a panel of four DB2 Gold Consultants, ready to answer questions on performance and tuning. 

For more information on the Baltimore/Washington DB2 Users Group event: BWDB2UG Agenda.   For more information on the pureQuery PoT event:  pureQuery PoT.  There is a nominal fee to attend the Baltimore/Washington DB2 Users Group to cover the cost of the facility and lunch, and the PoT at The Fillmore Group is free.  These are two terrific, low-cost opportunities to learn more about DB2 for LUW and to network with the local DB2 community.  I hope to see you at one or the other – or both!

For more in-depth training, consider the following IBM authorized training classes.

• CF602 “DB2 Connect 9 to DB2 for z/OS DRDA Implementation with TCP/IP” December 6, 2010
• CF632 “DB2 Connect 9 for DB2 for z/OS Problem Determination and Performance” September 20, 2010

DB2 Connect for DBAs: A Primer and Look Ahead

Whether you are a DB2 for LUW DBA who would like to access enterprise information or a z/OS DBA wondering how all those Java and .NET programmers are getting to your data, DB2 Connect is the solution.   Frank Fillmore, A DB2 Gold Consultant with an extensive history in training and consulting, will step you through a DB2 Connect primer from end-to-end, including platform architecture, DB2 Connect configuration parameters, and more. Case studies from large scale DB2 Connect health checks will be included.   IBM’s Kevin Foster, who manages the development of the product, will be on hand to discuss licensing, new packaging options such as the DB2 Connect Advanced Edition, which provides pureQuery acceleration in the box, and upcoming changes to the product.

Date: 19 August 2010
Time:  10:00-11:00AM Pacific, 1:00-2:00PM Eastern
Register here: http://ow.ly/2iAlr

InfoSphere Change Data Delivery combines legacy products from two different IBM acquisitions: Data Mirror Transformation Server (now InfoSphere Change Data Capture) and Ascential DataStage (now InfoSphere DataStage).  A recent customer engagement highlights the benefits - and a few challenges - of this hybrid replication/ETL (extract, transform, load) combination.

The B2B eCommerce business problem: a large, privately-held regional retailer wanted to gather data from each of their retail outlets into a single web presence.  This “bricks and clicks” approach would enable just-in-time delivery of their products from either a warehouse or the nearest store to the businesses that install the products for end-customers.  Each store had a small System i to handle local transactions.  But there are hundreds of stores.  Standard replication from each of the stores to a single website repository would be unwieldy at best.  Coupled with the astronomical licensing charges for replication software at each of the stores, direct replication was a technical and financial non-starter.

The IBM Change Data Delivery (CDD) offering makes local replication “free”.  InfoSphere Change Data Capture (ICDC) software can be installed on each of the (in this case) System i store transaction servers at no cost.  Data is replicated from journaled application tables/physical files to Consistent Change Data (CCD) tables on the same server.  Then DataStage reaches up periodically from a mid-tier server to gather the data from the CCD tables and populate the web application database - in this case MySQL.  Once the data is loaded into MySQL from a CCD table, the CCD table is pruned.

We employ ICDC Live Audit replication so that an application table INSERT, followed immediately by an UPDATE to the same row, followed by a DELETE of that row would record three distinct rows in the CCD tables.  Each would be identified by a timestamp and an “entity type” (e.g. INSERT, UPDATE) of the activity.  DataStage reads the Live Audit records and makes the appropriate change to the target MySQL database tables.

There are 17 application tables of interest: 10 on the warehouse System i and 7 on each of the store System i servers.  So there are 17 DataStage jobs.  Each DataStage job has four Stages or nodes:

• the first ODBC stage that uses an SQL SELECT to read from a particular System i CCD table
• a Transform stage to place each of the Live Audit row types (i.e. INSERT, UPDATE, DELETE) on one of three links to a second ODBC stage
• the second ODBC stage with the appropriate MySQL Data Manipulation Language (DML) statement (i.e. INSERT, UPDATE, DELETE) for each link
• a third and final ODBC stage branching from the Transform stage to prune the CCD table

All of the DataStage jobs have exactly the same structure.  They had to be exceedingly efficient because we are executing them so frequently (~ one per second).

DataStage Job

The entity types in the ICDC Live Audit CCD tables are as follows:

• RR - an INSERT record caused by the refresh of a table in a subscription
• PT - a regular application INSERT
• PX - an extra INSERT (found only on the System i)
• UB - before image of an UPDATE
• UP - after image of an UPDATE
• DL - a DELETE

There are also two Live Audit control records: RS (which signals a full refresh) and CR (which is equivalent to a TRUNCATE) which I was able to ignore.

The “secret sauce” is the SQL SELECT statement used to gather the data from each CCD table on the System i servers.  There are several reasons for this.

1. As much work as possible needed to be done on the System i servers which are being polled individually by DataStage every five minutes.  DataStage on the other hand had to run approximately one job per second.
2. The System i transaction processing application allowed for the updates of Primary Key fields.  As such, I needed to know both the before and after key values for an UPDATE.  Yet I didn’t want to code logic to compare consecutive UB and UP records in a Transform stage.
3. I needed to ensure that all of the rows were gathered and sent in sequence so that the INSERT followed immediately by an UPDATE of the same row arrived in MySQL in the proper order.
4. Finally, I needed a way to to ensure the pruning process did not purge rows from the CCD tables that arrived after the data was gathered by DataStage in the initial SQL SELECT.

So here’s an example:

SELECT CDECFXREF.MFRCODE AS MFRCODE_A,
 CDECFXREF.LINECODE AS LINECODE_A,
 CAST(NULL AS CHAR(3)) AS MFRCODE_B, 
 CAST(NULL AS CHAR(3)) AS LINECODE_B,
 CDECFXREF.AUD_TS AS AUD_TS,
 CAST(NULL AS CHAR(26)) AS MAX_TS,
 CDECFXREF.AUD_ENTTYP AS AUD_ENTTYP
FROM
 CCDLIB.CDECFXREF AS CDECFXREF
WHERE
 CDECFXREF.AUD_ENTTYP IN ( ‘RR’, ‘PT’, ‘PX’, ‘DL’ )

UNION ALL

SELECT A.MFRCODE AS MFRCODE_A,
 A.LINECODE AS LINECODE_A,
 B.MFRCODE AS MFRCODE_B,
 B.LINECODE AS LINECODE_B,
 A.AUD_TS AS AUD_TS,
 CAST(NULL AS CHAR(26)) AS MAX_TS,
 A.AUD_ENTTYP AS AUD_ENTTYP
FROM
 CCDLIB.CDECFXREF AS B,
 CCDLIB.CDECFXREF AS A
WHERE
 B.AUD_TS = A.AUD_TS
AND B.AUD_ENTTYP = ‘UB’
AND A.AUD_ENTTYP = ‘UP’

UNION ALL

SELECT CAST(NULL AS CHAR(3)) AS MFRCODE_A,
 CAST(NULL AS CHAR(3)) AS LINECODE_A,
 CAST(NULL AS CHAR(3)) AS MFRCODE_B,
 CAST(NULL AS CHAR(3)) AS LINECODE_B,
 TIMESTAMP(’9999-12-31-23.59.59.999999′) AS AUD_TS,
 T.MAX_TS AS MAX_TS,
 CHAR(’TS’) AS AUD_ENTTYP
FROM
 ( select char(max(aud_ts)) as max_ts from CCDLIB.CDECFXREF ) as T
ORDER BY 5 ASC

There are three major parts here:

1. The first section SELECTs the rows from the CCD tables that are either INSERTs or DELETEs.  The before image key values are NULLs.  There follows - as in all subsequent sections - the Live Audit timestamp, another NULL (we’ll talk about that later) and the Live Audit entity type.
2. The second section uses a recursive join to get both the after image for an UPDATE and the before image Primary Key values in the same row.   Note the same table is named twice in the FROM clause, but given different aliases: B for before and A for after.  The table can be joined to itself in this way because the Live Audit timestamps for both the before and after images are identical to the micro-second.
3. The final section delivers the maximum timestamp found in the CCD table at the time of the SELECT.  I use a nested table expression (the parenthetical SELECT statement in the FROM clause).  I create a dummy Live Audit timestamp of the maximum timestamp allowed by DB2 (December 31, 9999) so this will collate last.

The last piece was basically a nod to my earliest days as a Cobol programmer.  This serves as a “control record”.  The answer set is ordered by the Live Audit timestamp (including my 9999-12-31 dummy timestamp) so that INSERTs will arrive at the target MySQL database before an UPDATE of the same row.  Once all of the Live Audit records are successfully passed to MySQL, the last DataStage node (the third ODBC stage) is invoked to prune the System i CCD table of all rows less than or equal to the maximum Live Audit timestamp gathered at the time of the SELECT.

Many thanks to Pete Stroback and Ernie Ostic of IBM and the folks who monitor and respond on the www.dsxchange.com website for their help in putting this solution together.

1. Oskar Schindler said you must have a “clever accountant”.  Mills made it clear that organizations that can accurately allocate their IT expenses will see the most benefit from zEnterprise.  zEnterprise delivers System z quality of services (QOS) across heterogeneous architectures: the aforementioned System z as well as Power 7 blade servers and (eventually) System x blades.  The problem for most organizations is that System z “mainframe” costs have been capitalized from central IT budgets for over four decades.  As the PC revolution unfolded since the early 1980’s, most of the costs for networking, systems administrator salaries, PCs themselves and the software they run have been expensed out of departmental budgets.  Organizations with the discipline to accurately accumulate these costs certainly will be able to see the benefit of deploying the zEnterprise platform.  Interestingly, the table talk at lunch indicated that some IBMers see the sweet-spot for zEnterprise in the rapidly growing economies of China and Russia.  The reason?  Tight budgetary control and hierarchical, centralized decision-making in state and quasi-state enterprises (think Gazprom) will help them “get it” immediately.  I would not be surprised to see zEnterprise adoption in emerging and growing economies exceed that of North America in the next two years.
2. IBM has been able to run Linux on System z hardware using Virtual Servers and z/VM for a decade.  And the System z has been able to dispatch workloads to specialty engines within System z such as the Integrated Facility for Linux, zIIP, and zAAP for years.  Think of zEnterprise as extending that dispatching capability out of the physical System z box to discrete blade servers.  IBM’s goal is to move away from the “you can do everything on System z” posture - which in reality was a losing, rear-guard action - to embracing disparate architectures and acknowledging that maybe a print server really should run under Linux on an x86 platform.  Yet you can benefit from the centralized management and security of the System z.  This is workload integration at the chip, firmware, hypervisor, and middleware levels.  A pretty neat trick.
3. So what can I do with the zEnterprise?  Here are two relatively simple scenarios.

The Online Travel Portal  One well-known travel reservation site front-ends their expensive Oracle transaction servers with MySQL running on cheap x86 hardware.  While you’re noodling around trying to figure out the best intinerary, all you’re seeing is data replicated from Oracle to MySQL on a near-real-time basis.  When you enter your credit card number and hit “Purchase”, you’re routed to the Oracle OLTP server.  This is called “database tiering”.  I can now architect the same topology on System z with DB2 for z/OS on the back-end and x86 blades running the DB2 Express-C freeware database.  On the zEnterprise platform, these databases will communicate over a 10Gb private, secure network with extraordinarily low latency.  Ever get the “That seat is no longer available” message?  It might be a thing of the past with zEnterprise.

The Hospitality Company  This organization runs their centralized reservation systems on DB2 for z/OS already.  In order to support their frequent-guest affinity program portal, they have WebSphere Application Server running on a separate System p AIX server.  The only problem is that sometimes transactions hang to the point that the JVMs have to be recycled.  The Java programmers say their code is tightly written and the DB2 for z/OS database administrations say that the incoming SQL requests are satisfied sub-second.  While zEnterprise alone would not resolve this problem - see pureQuery and the lyrically named Optim Performance Manager Extended Edition - the application and the database servers will be as tightly coupled as possible while each runs on the optimal platform.  Since the transfer points and the servers themselves are under unified management, an entire layer of complexity (and potential breakage) will be eliminated.

The real buzz in the announcement for me is the IBM Smart Analytics Optimizer (ISAO).  For a generation as a DB2 database administrator, I’ve told my clients that OLTP and ad hoc query workloads should not be intermingled.  The solution has been to make copies of the data using replication technologies - InfoSphere Change Data Capture and Q Replication among them.  This approach has been a boon to DBAs, and software, storage, and server salesmen everywhere.  When it achieves its full promise, ISAO will evaluate incoming database requests and dispatch them along with the data needed to satisfy the request to the appropriate platform server.  DB2 for z/OS will serve as a centralized front-end for all workloads: OLTP, OLAP, ad hoc query, etc.  ISAO will transparently run the workload on the optimal platform and return the result set to the requesting application.  Organizations will be able to dismantle the miasma of extracts, FTPs, and other artifices now necessary to keep analytic workloads from bogging down OLTP.  And they’ll reduce complexity.  And save a ton of cash.

So on whose door will IBM knock first?  Clearly the System z installed base will be getting lots of attention.  But could Facebook or some other enterprise with orders-of-magnitude scaling issues (500 million Facebook users and counting) benefit from zEnterprise?  Surprisingly, the answer is Yes!  Facebook needs to manage lots of unstructured data (pictures, videos, et al) , but they also have the need for complex analytics.  First, to target online advertising ever more precisely, but also to serve larger societal needs.  Let’s say a man declares he needs a reduction in child support because he’s nearly broke.  The local social services agency unleases a smart agent to run against social networking sites and comes up with pictures on Facebook from the man’s recent two-week vacation in Hawaii.  Too big brother-ish?  A topic for another day.

From now through Labor Day, September 6, 2010, DB2 customers are invited to send us competing vendor quotes for Information Management project services.   In return, The Fillmore Group will provide a proposal to complete the same tasks with qualified consultants at savings of up to 50%.
 
The Fillmore Group has been delivering DB2 services to IBM customers since 1987.  TFG’s consultants are IBM certified DB2 experts, recognized for their ability to help DB2 users make the most of their information assets.  We want to help DB2 customers obtain services at reasonable prices.
 
The fine print:  This offer does not include discounts on travel and lodging costs, but as always, when we can work remotely and save customers money, we will.  The minimum engagement length for this special is 40 hours.  Engagements of less than 120 hours require prepayment.  The success of consulting engagements is largely dependent on the availability of the appropriately skilled resource at the time the project resources are ready.  The Fillmore Group will make every effort to have the right consultant ready when you are, but if dates or requirements change The Fillmore Group reserves the right to modify their proposal to reflect the additional costs resulting from the changes.

IDA Tips&Tricks by Joe Cullen

pureQuery Deep Dive Part 3:  Client Optimization Administration Enhancements for DBA’s

June 24th, at 1pm Eastern, 10am Pacific

Our dynamic duo of client optimization experts is back again!  Patrick Titzler and Chris Farrar, who some of you may remember from pureQuery Deep Dive Part 1, will discuss the enhancements released in Fix Pack 3 in Optim Development Studio and pureQuery Runtime that make the maintenance, security, and management of the client optimization processes better for DBA’s.  These enhancements are driven by real customer experiences and are centered around a relational repository that enhances security, fosters collaboration and streamlines the process of administering a pureQuery-enabled application.  Tooling support for these capabilities in Optim Development Studio will be demonstrated. 

Register today and don’t miss out on your opportunity to hear from the experts!

DB2 Connect for DBAs:  A Primer and a Look to the Future

Whether you are a DB2 for LUW DBA who would like to access enterprise information or a z/OS DBA wondering how all those Java and .NET programmers are getting to your data, DB2 Connect is the solution.  Frank Fillmore, A DB2 Gold Consultant with an extensive history in training and consulting, will step you through a DB2 Connect primer from end-to-end, including platform architecture, DB2 Connect configuration parameters, and more.  Case studies from large scale DB2 Connect health checks will be included. Kevin Foster, who manages the development of the product will be on hand to discuss packaging including licensing, new packaging options such as the DB2 Connect Advanced Edition, which provides pureQuery acceleration in the box, and upcoming changes to the product.
What you will learn:
• Why you need DB2 Connect
• How DB2 Connect is packaged and licensed
• Platform architecture
• Configuration and tuning options

As soon as I have registration information I will post it.