Microsoft SQL Server Express - automating backups

MS SQL Tips article on automating SQL Server Express backups and the purging of old backups

The very brief, yet excellent article by Ahmad Yaseen, linked above, contains a PowerShell script that would allow one to:

1. Automate backups on SQL Server Express
2. Automatically clean up backups that are aged > x days.

I always advocate that a DBA should test their backups in SQL Server. I would therefore recommend adding a step to Ahmad's script that performs a restore of the backup.

There are some assumptions, here:

• These are relatively small databases, and are using simple recovery model
• Along those same lines, there is room on the server's storage media to hold a "throw-away" database.

In sitting down to actually code this up, it occurred to me that this is WAY more trouble than it's worth ;-)

I would probably follow an approach that generates the necessary backup and restore scripts from SSMS, then executes them from either a normal command shell, or the powershell, as the case may be.

For those who use SQL Server Express, if you have not already encountered it, I recommend all of MSSQL Tips articles on Express Edition.

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?