Runaway Reorgs

I often run into issues with a customer who's DB2 reorgs get out of control.

I found a great Reference in DBA to DBA, which outlines how to view the status of the reorg.

Killing a reorg is a fairly simple matter, on the face of it, inasmuch as that you can just kill the reorg process and you're "done".

The problem is that you may have to rebuild indexes after this, for reasons that should be obvious.

Per Alexander Ashkenazi, If you cancel it during SORT or BUILD phase, your REORG will roll back. It shouldn't take long, because nothing has changed in your table at this point. 

If your REORG fails for whatever reason during "RECREATE ALL INDEXES" phase, all or some of your indexes will be gone, but DB2 will start automatically rebuilding them on the first usage of the table. 

Link to Alexander's response to a question on IT TOOLBOX

My advice would be to NOT mess with a reorg that you find in the "REPLACE" phase. That is where the database either copies the shadow copy back to the original, or drops the original and points the db at the shadow copy. For obvious reasons, all effort should be made to avoid interrupting this process.

Admin_CMD from within Stored Procedure - Error handling

I spent a lot of time on this one, and it wasn't clear from examples precisely how the various items should be used together.

The format of the ADMIN_CMD query is pretty straight-forward, and in our case, it took on roughly this form (edited to remove any identifying data):

______________________
CALL SYSPROC.ADMIN_CMD('EXPORT TO /datadestination/subfolder1/subfolder2/FileName.IXF OF IXF MESSAGES ON SERVER SELECT ');
______________________

Using ADMIN_CMD from CLP

If you're using ADMIN_CMD from a normal query, then it's pretty straight-forward.  The command will return a result set that has a query that gets you more detailed information about what you just did.

For example:

NOTE: Result set is edited to remove whitespace.

  Result set 1
  --------------
  ROWS_EXPORTED        MSG_RETRIEVAL                                                                       MSG_REMOVAL                                        
  -------------------- ----------------------------------------------------------------------------------- ----------------------------------------------------
                     0 SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('28138_DB2ADMIN')) AS MSG     CALL SYSPROC.ADMIN_REMOVE_MSGS('28138_DB2ADMIN')   
  1 record(s) selected.
  Return Status = 0
To break that out a bit, the MSG_RETREIVAL query is:
MSG_RETRIEVAL                                                                     
-----------------------------------------------------------------------------------
SELECT SQLCODE, MSG FROM TABLE(SYSPROC.ADMIN_GET_MSGS('28138_DB2ADMIN')) AS MSG   

... The MSG_REMOVAL query is:
MSG_REMOVAL                                     
-------------------------------------------------
CALL SYSPROC.ADMIN_REMOVE_MSGS('28138_DB2ADMIN')

You can then just copy and paste those, enter them into a query window and get results.

Using ADMIN_CMD from a stored procedure

This gets a bit more dodgy.
The problem, of course, on DB2 LUW 9.5, there's just not a great, straight-forward way to retrieve these results.
Fortunately, I was able to find an excellent example on dbforums, submitted by db2girl.
Example can be found here
DB2Girl's profile can be found here.

Turns out, it is necessary to create a RESULT_SET_LOCATOR that we associate with the call to ADMIN_CMD(Export).  I'm a bit embarrassed to admit that I had not previously heard of a RESULT_SET_LOCATOR.

Even now, I'm finding it hard to find documentation on this. Prior to DB2 10, it seems to mostly appear in 3rd party documentation, such as that from Micro focus.
I guess I'm not surprised by this, IBM. But, really? Not one little example of this?  Maybe it's in the COBOL documentation.  I dunno.

So, what I place here is "by rote", meaning I don't fully understand it.
I can deduce from the example below the following:

1. RESTULT_SET_LOCATOR is a DB2 Data Type
2. It can be "associated" with a command using the ASSOCIATE RESULT SET LOCATORS command.
3. A cursor can then be allocated against the result set, and used to retrieve data.

What I don't know (and what bothers me):

1. VARYING is obviously a modifier. What, precisely, does it mean, and what alternatives are available?

The approach I took was to simply create a table for logging and inserted the results of the call to SYSPROC.ADMIN_CMD into that table. If all is not well after the job runs, this can be queried.

A better approach would be to get these results and log them, then clean up the results using ADMIN_REMOVE_MSGS().

CREATE PROCEDURE RATESTUDYSR.SP_SQL_EXP_PSH_RS_SEG_LOAD (IN INT_P_BEGIN_DATE INT, IN INT_P_END_DATE INT)
 DYNAMIC RESULT SETS 1
P1: BEGIN
 DECLARE V_MSG_RETREIVAL VARCHAR(512);
 DECLARE V_ROWS_EXP BIGINT;
 DECLARE V_MSG_REMOVAL VARCHAR(512);
 DECLARE RESULT1 RESULT_SET_LOCATOR VARYING;

 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION,SQLWARNING,NOT FOUND BEGIN END;

  CALL SYSPROC.ADMIN_CMD('EXPORT TO /export.ixf OF IXF MESSAGES ON SERVER SELECT Manyfields FROM mytables JOIN othertables' );

 ASSOCIATE RESULT SET LOCATORS(RESULT1) WITH PROCEDURE SYSPROC.ADMIN_CMD;
 ALLOCATE RSCUR CURSOR FOR RESULT SET RESULT1;
 FETCH RSCUR INTO V_ROWS_EXP, V_MSG_RETREIVAL, V_MSG_REMOVAL;

 INSERT INTO RATESTUDYSR.LOG_SP_SQL_EXP_PSH_RS_SEG_LOAD VALUES (CURRENT_TIMESTAMP, 'TO RETREIVE ADMIN_CMD INFO: ' || V_MSG_RETREIVAL);
 
END P1

So, that's where I'm at as of now.  It's not hard to imagine retuning the result set from the stored procedure rather than logging it, but that's work for another day ;-)

Regular Expressions in SQL Server, DB2 UDB, and Oracle

I was a bit surprised that not all RDBMS platforms offer robust, native support for regular expressions.

Oracle:

Oracle introduced this in 10g. It is implemented in roughly the way I would expect and looks like it's pretty useful.
Reference

DB2:

DB2 seems to have this capability in 9.7. NOTE that I have not checked to see if it was available in earlier releases.  However, it appears you have to go through the XQuery interface. I'm not surprised by this, but a bit disappointed.  I had hoped for an implementation more like Oracle's. I think that the DB2 way is going to be cumbersome for the average user.

Example:

db2 "with val as (
 select t.text
 from texts t
 where xmlcast(xmlquery('fn:matches(\$TEXT,''^[A-Za-z 0-9]*$'')') as integer) = 0
)
select * from val"

Reference1, IBM DB2 for LUW 9.7 information center reference

MS SQL Server: 

SQL Server, it seems, requires you to roll your own solution or look to some sort of 3rd party.  I'm very surprised by this.

There are 3rd party libraries that appear to be pretty robust.  One example can be found here: SQL Server Central article on RegEx in MSSQL Server

I suppose that they want us to use the full text search, which appears to have robust features, and address the same purpose.  Still, just a bit surprised ...

Crazy access plan

Well, I've never seen one like that, before :-)

Createing a remote UDF for a Federated DB2 LUW 9.5 database

There are a couple good references for federated DB's that I've found in my research, but none really laid out the high level process for connecting a DB2 federated DB to a UDF on a data source.

Here are the steps.

1. Create Function on Datasource DB
2. Create Function Template on Federated DB
3. Create Function Mapping on federated db

For details on how to perform each of these steps, consult

• The DB2 9.5 Info Center. There's an entire section on Federation that's OK.
• The Federated Systems Guide. Google it. It's a PDF.

Good luck!

Christianity and Evolution

https://www.youtube.com/watch?v=FxBASNmg4AY

I don't understand.
I really, really don't understand. Not even a little bit.
OK, several things.

1) An atheist who says "there is no God because science" is just as wrong as a Christian who says "Evolution can't be true because bible." 

2) EVOLUTION does not equal ATHEISM. Lots of people believe in a deity and still buy into evolution.

3) Evolution does not have to mean there is no God. In point of fact, it doesn't have to mean much of anything to a religious or faith perspective.

Well, I guess it does in a very specific situation...
IF you REALLY believe that God LITERALLY spoke things into existence in 7 days, creating a vault in the sky between two waters and hanging in that vault the sun, moon, and stars
.. AND IF you believe that he sort of drew a person in the dirt and blew on him to make him live
... AND IF you believe that an ACTUAL TALKING SNAKE (because, hey, who HASN'T run across one of those ... ) talked an actual woman into eating an ACTUAL piece of fruit that, when eaten, somehow LITERALLY gave here the knowledge of good and evil 
... AND IF you believe that this Omnipotent God that was so powerful he spoke things into existence was just sort of strolling in the garden one day and was, in spite of being all-knowing, completely taken by surprised by this unacceptable state of affairs
... AND IF you believe that, in his goodness and mercy and utter, perfect justness he could think of no other course of action than to curse not only the "guilty" party, but all of their offspring for ever and ever and the entire earth and everything on it, up to and including an eternity in HELL for nothing more egregious than being born human  ...

*deep breath*
Yeah. Maybe it went down EXACTLY that way. Why not ...

Look - if you want all of that to be LITERALLY TRUE, then I guess you can't be OK with evolution.

If you want it to be a beautiful, important, symbolic allegory for something far more profound and important, then there's not a problem, here.

This is all just one more way to practice that basal human impulse to divide into us and them, love us, and hate them, and nobody does it better than Christians...

How's that new nature coming along?

HOBBY LOBBY, why I care...

Sometimes I find myself very puzzling.

Why, for instance, am I so terribly, terribly upset about the Hobby Lobby thing?

A bunch of very nice folks who, by most accounts, give their employees a great place to work relative to the other outfits in that space, are asking, in their minds, to not pay for murder.
That doesn't seem unreasonable to me.

So why am I so upset?

This probably says it better than I ever could.
http://aattp.org/when-corporations-become-indistinguishable-from-churches-government-isnt-far-behind/

It's a little long winded, and I have a few things to add, so here's a summary followed by some embellishments.

Summary: 
It's one more step towards what the author so rightly points out is a uniquely American form of Oligarchy / Theocracy. Is the Hobby Lobby thing, in itself, a terribly big deal? Probably not. But, as the author points out, the supreme court has instructed lower courts to reexamine several cases related to birth control in light of this ruling. It's part of a larger pattern.  Corporations are people.  Money is speech. If my feudal Lord believes a thing, then by God it impacts me. And, the obvious answer of a single payer system is right out of the question, despite all the problems it would solve.

My first Embellishment -

It's that maddening abortion question that seems to be the end all and be all of every consideration in the conservative religion.

You believe life begins at conception.

OK - Why? There are several key points between conception and birth, actually including birth, that I could make an argument for. Why conception?
And, can you prove it sufficiently to design law around it?

And while I'm asking questions, why do you insist on calling me a murderer if I don't agree that this is where life starts? What great evidence can you site that gives you the right to poke your finger into the chest of my wife or daughter who believes differently than you and say "You have to surrender your body to the rigors and dangers of pregnancy for 9 months AGAINST YOUR WILL because I, THE GOOD AND PERFECT CHRISTIAN, BEELIEEEEVE that the thing in your belly is a person."  I've come to see this whole issue as a power-grab, with you the Moral Majority imposing your will, once again, on people who do not believe as you do, against their will. Until you can prove that the thing in question is, in fact, a person, then MY RELIGIOUS FREEDOMS must be protected. Specifically, my freedom to disagree with your religious assertions.

My second embellishment - Sorry, but just the hypocrisy ...

The inconceivable levels of hypocrisy of the conservative religion.
I've heard them all.  "Come to church, there's always room for one more." "I'm just a sinner saved by grace".  on and on.  You know what, that GREAT, until you start making LAWS.
Two of the most outspokenly anti-gay-marriage people I know are divorced and just got remarried in defiance of he word of CHRIST!!  But YOU, you dirty Ho-Mo-SEXual, with your SATANIC a-GEN-da ... GOD WILL JUDGE 'MERICA if we allow you to jointly file your income taxes.

1/2 of my Pastor's family will be at work on Sunday in DEFIANCE of the very 10 commandments they want posted in every court house.

I could go on and on.  It's like the "biblical" rules are for everyone BUT YOU, the CHRISTIAN who's hitting people over the head with them.

I have more, but this is getting long, so I'll cut to the chase.

My last embellishment - ultimately, there's the Obama factor.

There's a link that the author shared, which I'll not take the time to investigate, because I've done that too many times and no one cares. If you'd like to, feel free.

At any rate, it states that Hobby Lobby was ALREADY paying for these "abortifacients" before it became an Obama issue,  It was paying for them, one would assume, because of a federal mandate that employers provide health insurance to workers that, if I'm not mistaken, dates back to the 70's.

Why, then, the current challenge? That demonic Obama character, that's why.

The Obama presidency has shown me a few things.
1 - The "liberal bias" of the main stream media is nothing at all when compared to the cold as steel effectiveness of the right wing machine.  They've managed to convince their constituency that Obama was born in Kenya, that he's a Muslim, that he is doing horrible things by issuing executive orders, that the vast majority of the solar companies have failed, and on and on and on. Democracy is and will remain completely broken until something is done about that.
2 - I'm not at all sure that we, as a country, would be nearly so susceptible to this if Obama was white.  I know, I know.  You're not a racist.  All I know is that white people have, quite collectively, gone completely off their nut. They will believe almost anything ... no.  They will believe ANYTHING you say about that guy.
3 - NO ONE is better at dividing into us and them, loving us, and hating them, than members of the Conservative Religion, who normally call themselves Christian.  Yes, Christian, I'm talking to you right now.  What makes you so utterly brilliant at it is that you can't possibly imagine that you're doing it.

It's amazing to watch. It really is.

Quit Calling Me Stupid...

I recently saw this one going around Facebook:

Here is my response:

No, I don't think criminals obey laws. Only the fictional liberal bogey man that you create so that you can win arguments believes that. No actual human being believes that.

However, given what I think I know about supply and demand, I DO think it's entirely probable that cutting the overall supply of weapons will reduce the number of criminals who have access to said weapons. Will some criminals still have them? Yes. But, they will have to be smarter and better funded. Will some brainless brat who's going to take your wallet to buy weed have one? Potentially - but it's far less likely, if we sufficiently reduce the supply, thus increasing the value even on a black market. How about the suburban kid who's been rejected by the cute girls one too many times? I dunno. Maybe. But, again, I have to believe a smaller supply is going to create higher barriers to ownership and thus reduce the chances.

This would, of course, require both laws and law enforcement, which I know you hate. I'm not saying it's something conservatives wanna do, just that it could be done.

But, that's not the point. The reality is that NO ONE wants to take your guns away.

Here's what I have a problem with:
In Iowa, Conservatives have gone so stark-raving-mad with hatred for the liberal bogey - man, who I can attest does not exist, that they've passed laws removing Sheriff's discretion from concealed carry permits. The result is convicted sex offenders walking around with concealed carry permits. THAT's the sort of thing that I want to get rid of.
http://archive.desmoinesregister.com/article/20130505/NEWS01/305050072/1001/news/Register-Exclusive-50-sex-offenders-gun-permits

That and gormless twits carrying loaded AR's into Target amongst the sugar-mad kids and terrified soccer moms.
http://www.motherjones.com/politics/2014/06/guns-target-open-carry-texas


The words "well regulated" are right there in the 2nd amendment. Maybe you need to read it again.

And, quit calling me stupid.

Well, THIS saved me a lot of hastle

Oracle SQL Developer, rather inexplicably in my view, doesn't come out-of-the-box with a good facility to set the schema.  Even the set active schema command gets ignored.

This extension provides a handy drop-down box that allows you to set the active schema.  Brilliant :-)

http://javaforge.com/project/schemasel

Opening files in SSMS as query files when they don't have .sql as extension

SSMS (SQL Server Management Studio) will happily load a *.sql file and execute it as a SQL Script.
However, we store our DDL files as .DDL.

I was able to convince SSMS to treat these as sql scripts in the following way:

Tools -> Options
Expand Text Editor
Select File Extension
Add an extension.  NOTE: Make sure to select "SQL Query Editor" in the "Editor" drop-down.

And, voila!