T Shirt Printing Plymouth, full-time security expert and part-time DB2 T-shirt model Rebecca Bond, along with members of the Lab Services and TFG migration teams are working together to complete an Oracle to DB2 migration…here’s to a successful migration weekend!!!

On June 9, 2011 IBM released Fixpack 1 of the Q Replication Dashboard v10.1.  Cool new features include an Alert Manager that sends e-mail messages when replication thresholds are breached (e.g. end-to-end latency exceeds 30 seconds).  After some tire-kicking, I will let you know if this is a viable substitute for the asnmon monitoring feature of Q Replication.  The dashboard has also been integrated with Tivoli Enterprise Portal.

Full details and the free download are at https://www-304.ibm.com/support/docview.wss?rs=3557&uid=swg24023065&wv=1

Here is another technique to use only when standard practices won’t work.  Let’s say you are performing a manual load in Q Replication (either InfoSphere Replication Server or DB2 Homogeneous Replication Feature).  Ordinarily you would INSERT a LOADDONE into the IBMQREP_SIGNAL table.  The INSERT into the Q Replication Control Table is logged and Q Capture will see it.

BUT, what if Q Capture is way behind?  Various operational problems could cause this.  The manual load has actually completed, but the WebSphere MQ spill queues are growing because Q Apply doesn’t know that.  It might take hours for Q Capture to get to the point in the log where the LOADDONE is posted.

Here are the steps to bypass LOADDONE and initiate the draining of the spill queues by Q Apply:

1. Stop Q Capture.
2. Wait for the Receive queue to empty.  The reason for this is that Q Capture sets an indicator in the message placed on the Send queue specifying that a subscription is still being manually loaded.
3. Stop Q Apply.
4. For each subscription where the LOAD has completed: change the STATE value in the IBMQREP_TARGETS table from ‘E’ to ‘F’ and change the STATE value in the IBMQREP_SUBS table from ‘L’ to ‘A’.
5. Start Q Apply.
6. Start Q Capture.

Thanks to Christian Zentgraf and Anupama Mahajan of IBM and my TFG colleague, Jim Herrmann, for developing this procedure.

It’s rare I begin a blog post which will explain how to do something with “DON’T DO IT!”, but I’ll make an exception this time.  Read what follows, but don’t do it unless extreme circumstances warrant.

A colleague, Jim Herrmann, and I have been working on a Q Replication implementation at a large financial institution.  Through a procedural error, we cold started a Q Replication environment, then deleted the schema messages from the WebSphere MQ receive queue that Q Replication had sent from source to target to activate subscriptions.  Ooops…  We had begun a performance test so there were almost 700k messages added to the receive queue.  When we started Q Apply, it complained about missing messages (the schema messages we had deleted).  We could have deleted all of the messages and executed another cold start, but that would have required that we re-run the performance test.  Due to scheduling constraints, rerunning the test wasn’t an option.  We could have activated the subscriptions by re-issuing the CAPSTART command into the IBMQREP_SIGNAL table, but Q Apply wouldn’t see those messages until after the 700k messages were processed.  A classic chicken-and-egg problem.  What to do?

With Q Capture and Q Apply down we manually updated the IBMQREP_TARGETS table.  First, we UPDATEd the STATE column to ‘A’ for each subscription.  Then, for each subscription we matched the SUB_ID in the target to the source.  For example, for SUBNAME MY_TAB_0001, we updated the SUB_ID in IBMQREP_TARGETS to the value found in IBMQREP_SUBS.

Next we had to modify the IBMQREP_TRG_COLS table.  This update relied on the fact that we had a QA environment that almost matched the performance environment.  Jim drafted some nifty SQL to create the necessary DML code.

SELECT ‘UPDATE ASN.IBMQREP_TRG_COLS SET MSG_COL_TYPE=’|| MSG_COL_TYPE ||’, MSG_COL_LENGTH=’|| MSG_COL_LENGTH ||’, SRC_COL_MAP =”’|| SRC_COL_MAP ||”’ where RECVQ=”’|| RECVQ ||”’ AND SUBNAME=”’|| SUBNAME||”’ AND TARGET_COLNAME=”’|| TARGET_COLNAME||”’;’ FROM ASN.IBMQREP_TRG_COLS;

Then we ran the resulting UPDATE statements against the target Q Replication Control Table database.

Finally, Q Apply relies on WebSphere MQ for assured delivery of messages – including the schema messages we had inadvertently obliterated.  The following message was posted in both the Q Replication log and the IBMQREP_APPLYTRACE table.

ASN7551E  “Q Apply” : “ASN” : “BR00000” : The Q Apply program detected a gap in message numbers on receive queue “SERVER1.CUSTDB.RECEIVE.DATAQ” (replication queue map “DB2S_TO_DB2T”).  It read message ID “515245504D875D2C00000000000000000000000000000019”, but expected to find message ID “515245504D875D2C00000000000000000000000000000001”.  The Q Apply program cannot process any messages until it finds the expected message.

We had to tell Q Apply to ignore the missing messages.  To do this, we added a message to the IBMQREP_DONEMSG table.

INSERT INTO ASN.IBMQREP_DONEMSG (RECVQ, MQMSGID) VALUES(‘SERVER1.CUSTDB.RECEIVE.DATAQ’, x’515245504D875D2C00000000000000000000000000000018′)

Then we started Q Apply.

All of the usual caveats are relevant:

• Don’t try this at home.
• Professional driver, closed course.
• Your mileage may vary.

This was a work-around in a tight spot.  Not business as usual.

By now you’ve probably heard that IBM has acquired Netezza.  So how do I get my data into the darn thing?

We’ve worked a lot with IBM change data capture technologies: SQL Replication, Q Replication, and InfoSphere Change Data Capture (ICDC).  We also work with InfoSphere Warehouse, IBM’s internally developed data warehousing platform.  Usually we use one of the former to feed the latter.  As changes occur in a transactional database, the deltas are shipped near-real-time to the target data warehouse.  There might be some transformation, cleansing, or other manipulation (that’s the “T” in “ETL”) for which we would use DataStage, but a (surprisingly large) number of customers just copy data to the reporting and analysis platform with little massaging.

Enter Netezza.  The usual “drip-feed” (i.e. as soon as it changes in the source, send it to and update the target) methods listed above don’t play as well.  Netezza is optimized for bulk data loading.  One-row-at-a-time INSERT/UPDATE/DELETE propagation by a replication technology isn’t efficient.  The solution is to create intermediate mini-bulk delimited files that can be ingested, say, every five minutes.

There are a couple of ways to create these files.  One is to use InfoSphere Data Event Publisher (EP).  Think of EP as Q Replication without the Apply component that posts the deltas to a target database.  EP can publish XML or delimited files.  Viola!  Our Netezza problem is solved.  If you need complex business rules applied to transform the data, use ICDC or Q Replication to feed DataStage and have it created the delimited files.

Led Zeppelin had a concert film, song, and album with the title “The Song Remains the Same”.  Even though it comes in different packaging, Q Replication functionality remains consistent. 

Data interoperability – what IBM calls Information Integration – is a core competency of The Fillmore Group.  We deliver consulting services and IBM authorized training for software products like Information Server (think DataStage), InfoSphere Change Data Capture (ne’e DataMirror), InfoSphere Federation Server, and more.

Right now we’re working a lot with DB2 Homogeneous Replication Feature and it’s sibling InfoSphere Replication Server.  BTW – a moment’s digression – what is the difference between these two?  Both feature what is commonly known as Q Replication: IBM’s high volume, low-latency, assured-delivery data replication solution.  The differences come down to licensing and capabilities.  When you install DB2 Enterprise Server Edition (DB2 ESE), all of the code for the DB2 Homogeneous Replication Feature is there.  To activate it, you must purchase and apply the appropriate license key.  This gives you the ability to create DB2 for LUW to DB2 for LUW replication topologies.  Of course DB2 for z/OS can be both a source and target for Q Replication, but the licensing to include DB2 for z/OS is beyond the scope of this post.  DB2 Advanced Enterprise Server Edition (DB2 AESE)  includes a limited-use license for the DB2 Homogeneous Replication Feature.  To learn more about the feature-rich DB2 AESE bundle, join us for a free webinar on Wednesday, March 16.  By contrast, InfoSphere Replication Server can replicate from and to heterogeneous sources and targets.  At one very large integrated financial services firm, Q Replication is being used to:

• migrate Oracle databases to DB2 for AIX without taking an outage
• populate a warm standby subsystem for DB2 for z/OS
• integrate DB2 for z/OS data into an Oracle application
• create a DB2 for AIX reporting database with near-real-time DB2 transactional data

Clear?  Good.  If not, drop me a note (frank.fillmore@thefillmoregroup.com) to talk about your specific replication use case.

Valentine’s Day!  What could be more fitting if you need to learn to LOVE InfoSphere Q Replication?  In honor of St. Valentine’s Day students attending class will receive a box of Godiva Valentine chocolates.  If you use Q Replication and have not attended any formal training this class will give you an understanding of how Q Rep works, how to make changes, and how to ensure your monitoring is 100% functional.  The outline and registration are on the IBM Learning Services website.  I hope you will join us!

The Fillmore Group had the pleasure of working with the IBM New York Metro team on a DB2 Advanced Enterprise Server Edition upgrade and purchase for an existing DB2 ESE customer and I am working on the IBM “Winwire” document that gets circulated internally at IBM about our successful teaming.  The challenge for me is this:  how do I make it sound difficult?  Buying DB2 AESE made perfect sense for the customer from a strategic/technical perspective, and the IBM pricing was amazing.  Read More…

Frank Fillmore will deliver a one hour presentation to explain the new DB2 for LUW Advanced Enterprise Server Edition (AESE) packaging – what’s included, how AESE differs from DB2 Enterprise Server Edition and most importantly, why anyone running DB2 for LUW should give AESE serious consideration.  

Date:  Thursday, November 18, 2010, Time:  12pm-1pm Eastern

With AESE, IBM is bundling Optim and InfoSphere tools that have been available as for-fee add-on features in the past.  Come learn how to upgrade DB2 and get: 

• DB2 Storage Optimization Feature
• DB2 Performance Optimization Feature
• Optim Performance Manager
• InfoSphere Federation Server
• InfoSphere Replication Server (Q Replication)
• Optim Development Studio
• DB2 Advanced Access Control Feature

To register, click here.  If you have a problem registering or have questions, email Blaire Crowley (blaire.crowley@thefillmoregroup.com).

For those planning to attend the IBM Information On Demand Conference in Las Vegas beginning October 24 my colleague, Kim May, will be presenting a survey of IBM replication technologies.  Session TOD-2708 will be delivered on Wednesday, October 27 at 3:15 p.m. (local time) in the Breakers L room.  Check the conference schedule to confirm.

“IBM has three distinct replication technologies: SQL Replication, InfoSphere Replication Server (Q Replication) and InfoSphere Change Data Capture (ICDC) What are the strengths and weaknesses of each tool? What data sources and targets are supported by each product? Learn about customer use cases for each technology, from data warehousing to eCommerce to high-availability. Understand how to choose the right tool for your business.”