Monitor SQL Server Service using PowerShell

Mashrur Shuvo posted a script over at SQL Server Central that will user powershell to monitor your sql server services.

If you're like me, and you have trouble getting budget approval for 3rd party monitoring tools, then this is very Useful (with a capital - U ;-)   ) !
http://www.sqlservercentral.com/scripts/Monitor+SQL+Server+and+Service/127705/

DB2 LUW Invalid Packages - detection, Investigation, and Resolution

I was not able to find what I considered a good, clear reference for this when I recently encountered it at a customer installation.

I found that there were quite a few invalid packages that needed to be dealt with.
The worst, of course, were those with a value of 'X' in the VALID indicator. (VALID = 'X').

So, here are some simple steps I followed to investigate and resolve these issues.

You, of course, may want to investigate these items further.

Get the packages that have issues:

db2 "select pkgschema, pkgname, unique_id, valid from syscat.packages where VALID <> 'Y'" > invalid_packages.txt

To get the ones that require manual intervention, use

db2 "select pkgschema, pkgname, unique_id, valid from syscat.packages where VALID = 'X'"

Figure out if those packages contain your SQL:

db2 "select pkgschema,pkgname,unique_id, TEXT from syscat.statements where UNIQUE_ID = x'414141414157465A' AND PKGNAME = 'STADMG02'"

NOTE on the above: The TEXT field will show you the query associated with your package, but it is defined as a CLOB, and is therefore quite large. If you need to see it, redirect to a file. Otherwise it might be best to omit it.

Do something about those packages :)

db2 rebind package NULLID.STADME01 RESOLVE ANY REOPT ONCE

Where NULLID is the schema used by the jdbc driver, and STADME01 is the package name returned in the PKGNAME field. The schema name and the package name are retreived from your first query at top.

EDIT: To auto-generate a script that rebinds everything:

db2 -x "select 'rebind package ' || pkgschema || '.' || pkgname || ' RESOLVE ANY REOPT ONCE ;' from syscat.packages where VALID <> 'Y'" > rebind_other_packages.sql

Here's an example of me doing this in our internal environment:

/home/db2xbox >db2 "select pkgschema, pkgname, unique_id, valid from syscat.packages where VALID = 'X'"

PKGSCHEMA                                                                                                                        PKGNAME                                                                                                                          UNIQUE_ID             VALID

-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- --------------------- -----

NULLID                                                                                                                           STADMG02                                                                                                                         x'414141414157465A'   X

NULLID                                                                                                                           STADMG01                                                                                                                         x'4141414141494157'   X

NULLID                                                                                                                           STADME01                                                                                                                         x'41414141414B4B30'   X

  3 record(s) selected.

/home/db2xbox >db2 "select pkgschema,pkgname,unique_id, TEXT from syscat.statements where UNIQUE_ID = x'414141414157465A' AND PKGNAME = 'STADMG02'"

NOTE: I did note run the above ↑↑↑↑↑↑ because the output is quite large due to he "TEXT" field which is defined as a CLOB.

/home/db2xbox >db2 rebind package NULLID.STADMG02 RESOLVE ANY REOPT ONCE

DB20000I  The REBIND PACKAGE command completed successfully.

/home/db2xbox >

/home/db2xbox >db2 describe table syscat.statements

                                Data type                     Column

Column name                     schema    Data type name      Length     Scale Nulls

------------------------------- --------- ------------------- ---------- ----- ------

PKGSCHEMA                       SYSIBM    VARCHAR                    128     0 No

PKGNAME                         SYSIBM    VARCHAR                    128     0 No

STMTNO                          SYSIBM    INTEGER                      4     0 No

SECTNO                          SYSIBM    SMALLINT                     2     0 No

SEQNO                           SYSIBM    INTEGER                      4     0 No

TEXT                            SYSIBM    CLOB                   2097152     0 No

UNIQUE_ID                       SYSIBM    CHARACTER                    8     0 No

VERSION                         SYSIBM    VARCHAR                     64     0 Yes

References:
syscat.pakcages :         IBM Information Center link to syscat.packages catalog view
syscat.statements         IBM Information Center link to syscat.statements catalog view
db2 rebind command:  IBM Information Center link to db2 rebind command

Recording General System Performance Data for AIX

Collect Topas Info

Introduction

I identified a need to record general system information for review in association with DB2TOP data.

This is the very brief procedure I developed to do so.

Generate the data

Execute TOPAS_NMON by using the following commands:

topas_nmon -s 5 -c 100 -f

This will sample every 5 seconds for 100 samples and save to a file in your current directory.

NOTE: You can also leave off the -c and just let it run. When ready to stop it, issue kill command.

                ps -ef | grep -i "topas"

                then, use the kill command, passing in the pID, to end it.

Analyze using NMON Analyzer

1. Download NMON Analyzer here. (https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/Power+Systems/page/nmon_analyser  )
2. Open NMON Analyzer.
3. Enable Macros.
4. Set Settings in Settings tab as desired. NOTE: Default values are fine.
5. Set desired settings in Analyzer.
6. Click "Analyze nmon data" button to perform analysis.

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 ...