DB2 for System i (iSeries, AS/400)

Posted by Frank Fillmore on June 9, 2011 under DB2 Connect, DB2 for i. Tags: , , , .

Jim Tonchick had this recent post on DB2-L in response to a query from Dan Luksetich regarding DB2 for iSeries:

>> Yep, we’re out here.  But this forum is mostly for z/OS and LUW.  There are some iSeries forums, but DB2 is not the main focus. 

>> DB2 for iSeries is IBM’s best kept secret.  Every iSeries lpar has DB2 by default.  It comes with the operating system as the default file manager.  Even though it is on every instance of the i5 operating system, it is not covered by IBM at the OnDemand Conference or at IDUG’s conferences.  Even the IBM’s DB2 Family web site does not mention it (http://www-01.ibm.com/software/data/db2/).  You have to do to the IBM System i software path to find it (http://www-03.ibm.com/systems/i/software/db2/).  That’s a shame because DB2 for iSeries fits into the the DB2 family very well and can be a part of a distributed DRDA based application.

>> I attended IDUG NA 2011 in May and asked around why DB2 for iSeries was not even mentioned let alone represented.  The answers I received ranged from “not our target market” to “DB2 runs on the iSeries?”.  I’m glad to see someone in the industry finally take notice.

I agree that this member of the DB2 “family” is neglected.  For those of you who have been attending the DB2 Connect Virtual Users Group, DB2 Connect enables Distributed Relational Database Architecture (DRDA) between System i and DB2 for Linux, Unix, Windows because System i is EBCDIC-based (as is z/OS).

There’s a great manual for explaining the duality (split personality?) of DB2 for System i: “DB2 Universal Database for iSeries Database Performance and Query Optimization”.  It discusses the legacy Classic Query Engine (CQE) as well as the more-DB2-like SQL Query Engine (SQE).

There are also Redbooks

  • “OnDemand SQL Performance Analysis Simplified on DB2 for i5/OS in V5R4” (SG24-7326)
  • “SQL Performance Diagnosis on IBM DB2 Universal Database for iSeries” (SG24-6654)
  • “Preparing for and Tuning the SQL Query Engine on DB2 for i5/OS” (SG24-6598)

Finally there’s “SQL Reference for Cross-Platform Development” which takes the place of FREDS (Formal Register of Existing Differences of SQL – believe it or not!).  This manual compares the SQL dialects for

  1. Version 9 of DB2 for z/OS
  2. Version 6.1 of DB2 for IBM i
  3. Version 9.5 of DB2 for the Linux, UNIX and Windows Platforms

Free Your Data…Without Breaking the Bank!

Posted by Kim May on February 21, 2011 under Attunity, DB2 for i, DB2 for Linux Unix Windows, DB2 for z/OS, TFG Blog. Tags: , , , .

Affordable IBM Mainframe & DB2 Data Integration

Live Webinar Thursday, February 24th from 12:30pm – 1:30pm EST

Register today to learn how you can accomplish efficient and affordable IBM mainframe or DB2 data integration in a heterogeneous environment for BI, reporting, MDM, DW, and more.  The session will conclude with a live product demo and Q&A session.

Read More…

DB2 101 Sessions Announced

Posted by Kim May on February 1, 2011 under Baltimore Washington DB2 Users Group, DB2 Education, DB2 for i, DB2 for Linux Unix Windows, DB2 for VSE&VM, DB2 for z/OS, DB2 Migrations, IBM DB2 Services, Optim, Oracle, Q-Replication. Tags: , , , , , .

The Fillmore Group will be hosting three sessions of what we are calling, “DB2 101” – sessions that provide an executive overview of the IBM Information Management product portfolio, one each month, in February, March and April.  We have selected three restaurants close to our Baltimore headquarters – 2 in Baltimore and 1 in Columbia, Maryland (about halfway between Baltimore and Washington, DC).  The purpose of each session is to bring attendees up to date on IBM’s Information Management product portfolio (an expanding target), and strategy. Read More…

Virtual Users Group “DB2 Connect: History, Today’s Functionality, and What’s Next”

Posted by Frank Fillmore on December 14, 2010 under DB2 Connect, DB2 Education, DB2 for i, DB2 for VSE&VM, DB2 for z/OS, International DB2 Users Group (IDUG). Tags: , .

Curt Cotner presents DB2 Connect from a technical, historical perspective.  In his presentation Curt explains how DB2 Connect evolved as a technical solution, describes the changes to DB2 Connect and why organizations should consider eliminating the gateway.  Curt looks at what the future holds for DB2 Connect.  Curt covers best practices for moving from three-tier DB2 Connect topology using a gateway to the recommended two-tier architecture so attendees can begin to figure out how to get their organization from here to there.

db2-connect-history-dec-2010

Also, we would like to thank everyone for volunteering.  We are hoping to have three meetings a year and will be posting them through the IDUG website.  A special thanks to Michael McBride for all of this assistance with the website.

Please feel free to invite your friends and colleagues to join the Virtual Users Group!  Contact Blaire Crowley at blaire.crowley@thefillmoregroup.com for details.

DB2 Connect Virtual Users Group Inaugural Meeting on December 14

Posted by Frank Fillmore on November 24, 2010 under DB2 Connect, DB2 for i, DB2 for Linux Unix Windows, DB2 for VSE&VM, DB2 for z/OS, International DB2 Users Group (IDUG). Tags: , .

“DB2 Connect, History, Today’s Functionality, and What’s Next”

Tuesday, December 14, 2010 from 11:30 a.m. to 1:00 p.m. (US) Eastern Standard Time

IBM Fellow Curt Cotner will present DB2 Connect from a technical, historical perspective.  In his presentation Curt will explain how DB2 Connect evolved as a technical solution, describe the changes to DB2 Connect and why organizations should consider eliminating the gateway.  Curt will finish the presentation with a look at what the future holds for DB2 Connect.  Curt will cover best practices for moving from three-tier DB2 Connect topology using a gateway to the recommended two-tier architecture so attendees can begin to figure out how to get their organization from here to there.

The presentation will be delivered online via GoToWebinar.  Click here to register.

DB2 Connect Virtual Users Group

Posted by Kim May on September 26, 2010 under DB2 Education, DB2 for i, DB2 for Linux Unix Windows, DB2 for z/OS, International DB2 Users Group (IDUG), Uncategorized. Tags: , , .

The Fillmore Group is coordinating the startup of a virtual users group of DB2 Connect users.  We are actively seeking DB2 Connect users to participate – as members, as board members, and as presenters.  We hope to have a volunteer board meet periodically and have one to two hour sessions delivered via Webex to all members quarterly.  All of this will be handled virtually, with an opt-in email contact list providing event notification to members, and an affiliated IDUG group to provide interim communication between members. 

I am planning to attend the IOD Conference and will host a meeting on Monday, October 25th, for anyone volunteering to participate as a board member.  My colleague Blaire Crowley at The Fillmore Group is working with Michael McBride at IDUG to establish the IDUG group and to collect member, board member and speaker volunteer names.  On the 25th we will have board member volunteers attending IOD get together and share a conference call with remote volunteers to work through a schedule of events for late 2010 and 2011.

The virtual users group will initially operate independent of IBM and IDUG.  Kevin Foster and Kimberly Madia on the IBM DB2 Connect team have offered assistance with speakers. 

If you are interested in joining please email Blaire Crowley at blaire.crowley@thefillmoregroup.com.  Blaire will send you instructions on joining the IDUG group and add you to the list of members.  Please (please!) let Blaire know if you would like to volunteer.

IBM/Optim Team Virtual Tech Briefing – DB2 Connect

Posted by Kim May on August 16, 2010 under DB2 Connect, DB2 for i, DB2 for Linux Unix Windows, DB2 for z/OS, Optim, Uncategorized. Tags: , , , , .

Kimberly Madia’s IBM/Optim team is hosting a Virtual Tech Briefing this Thursday:

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

Change Data Delivery Combines InfoSphere Change Data Capture (ICDC) and DataStage

Posted by Frank Fillmore on August 9, 2010 under DB2 for i, InfoSphere. Tags: , , , , , , , , .

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

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.  Read More…

Summer Services Special

Posted by Kim May on July 21, 2010 under DB2 Connect, DB2 for i, DB2 for Linux Unix Windows, DB2 for VSE&VM, DB2 for z/OS, IBM DB2 Services, InfoSphere, Optim, Q-Replication, SQL Tuning. Tags: , , , , , , , , , , , .

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…

DB2 Connect Virtual Briefing with the IBM Team

Posted by Kim May on June 7, 2010 under DB2 for i, DB2 for Linux Unix Windows, DB2 for z/OS, Optim. Tags: , , , , .

IBM has moved DB2 Connect support into the Optim group, as it is in some ways the original Optim solution.  Kathy Zeidenstein, who manages community outreach efforts for the Optim team (a weekly e-newsletter, content on the developerworks site, Twitter, etc.) is coordinating a DB2 Connect Virtual Briefing scheduled for August 19th.  The primary purpose of the presentation is to introduce new features in the advanced edition, however, as DB2 Connect can be used in so many ways, and is so often under-utilized, she’s invited Frank to devote some of the time allocated to delivering an overview of what DB2 Connect can do.  More information is on the way…for the moment we have the date and time reserved (mark your calendars – August 19th at 1pm Eastern), and a tentative agenda:

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.