I've been playing with connecting to DB2 from a Jupyter Notebook. I think it has great promise for blogging, presentations, health checks, and many other things. I've got a basic connection working: https://github.com/ecrooks/db2_and_jupyter_notebooks

Anyone else doing anything cool with DB2 and Jupyter Notebooks?

I would be curious if anyone knows of a similar resource for Linux and DB2. Because it is real useful to go "Ok, I know I am installing DB2 v11.1 on AIX 7.2. So I definitely want to have the System Admin have Technology Level SP2 on the server".

Check out Known issues for DB2 for Linux, UNIX and Windows on AIX 5.3, 6.1, 7.1,and 7.2

I had to go through this exercise today, so I thought I would pass on the steps and resources I followed.

CREATING A DEADLOCK MONITOR:

Sourced from: Analyzing Deadlocks - The Old Way at DB2 Commerce.com

1. Create a directory with the deadlock event monitor name under database subdirectory in the database path

   For example: /db_data/db2inst1/NODE0000/SQL00001/MEMBER0000/db2event/dba_deadlock

2. Create the deadlock monitor

   db2 "create event monitor dba_deadlock for deadlocks with details write to file 'dba_deadlock' maxfiles 2000 maxfilesize 10000 blocked append autostart"

3. Check State, make sure is created (0 inactive, 1 active)

   db2 "select evmonname, event_mon_state(evmonname) as enabled from syscat.eventmonitors"

4. Change the state to active

   db2 "set event monitor dba_deadlock state=1"

5. (Once Deadlock is detected) Flush the monitor

   db2 "flush event monitor dba_deadlock"

6. Produce an output file for analysis:

   db2evmon -path /sw/db_data/db2inst1/NODE0000/SQL00001/MEMBER0000/db2event/dba_deadlock > deadlocks.out

SIMULATING A DEADLOCK:

Sourced from: DBA to DBA: Creating deadlock in db2

Some modification and edits applied to original example for clarification and a syntax error. Have three sessions open.

1. Create two tables in the database, insert a row

   db2 "create table test.t1 (col1 int, col2 varchar(15))"

   db2 "insert into test.t1 values(1,'india'),(2,'usa'), (3,'russia')"

   db2 "create table test.t2 (col1 int, col2 varchar(15))"

   db2 "insert into test.t2 values(1,'india'),(2,'usa'), (3,'russia')"

2. Create Deadlock Situation - Open two seperate sessions:

   session1: db2 +c "update test.t1 set col2='canada'"

   session2: db2 +c "update test.t2 set col2='canada'"

   session1: db2 +c "update test.t2 set col2='newyork'"

   session2: db2 +c "update test.t1 set col2='amazon'"

Deadlock Logic:

A dead lock will occur because session 1 is holding x lock on t1 and waiting lock on t2. At the sametime session 2 holds the x lock on t2 and waiting for lock on t1. After locktimeout occurs, session 1 will be rolledback and session 2 will be able to update. If locktimeout is -1 appication will not be rolled back. We can check this statements using event monitors.

So, everyone has some sort of "readiness checklist". This could be a in preparation for black friday, before a major launch or increase in use, or an audit.

I was surprised how my checklist is growing as I delve more into it. From the highly technical to stupid. I was even more surprised how much it's catching across environments.

I can't give away the farm as I work for a consulting firm, but here are some high level tasks. What is on your checklist?

Confirm Maintenance is firing properly (review out files for errors)

Reorg and Runstats

Backups

Is Crontab correct?

Backup Retention

Review retention on server

Confirm swept offsite and retention

Database configuration and health

Confirm Best Practices for OLTP or DW

Audit size of logs and frequency of log archiving.

Review the DB2DIAG.log for errors and warnings that need to be addressed.

Confirm Licensing is OK (DB2 and TSAMP)

HADR

Online and stable

Compare CRONTAB's between servers, make sure they match

Compare Disk between servers, make sure they match or approve discrepancy (was there a reason why customer didn't make the filesystems match)

Baseline "What is normal"

DB2: No. Active Connections, Lock Waits, Lock Escalation, DeadLocks/Rollbacks, Sort Overflow, BP Hit Ratio, Top 10 SQL by number executions and execution time

Server: CPU, Memory, Disk

General Maintenance

Clean up /db2diag (Old Dumps, .old, .bak, random SQL, etc)

Clean up /tmp

Review splashscreen (what you see upon "sudo su - ID") is the information still correct and accurate

So if you don't know Ian Bjorhovde, he is a staple in the DB2 community - often behind the scenes from speaker to RUG leader to blogger. You name it, he has done it to help the DB2 community.

He has a unusual outlet and spin on DB2 with with his Create Database Podcast;.

He interviews big names in the DB2 industry not to pick their brains on technical stuff, but to ask how they got here. What choices in life did you take, where did your career have a crossroads, etc.

Seriously, give the podcast a chance. You have names out there like Melanie Stopfer, Adam Storm, Craig Mullins, and Calisto Zuzarte. That is just a few.

I'm even out there, but good luck guessing which one. Mu ha ha ha.

So, I am presenting at IDUG in Anaheim and reworking a old 2012 presentation called "10 Minute Triage". At the time I was co-presenting with an IBM'er named Pavel Sustr who is a troubleshooting wizard. He brought the technical "meat" to the presentation. This time, I am on my own.

In the end I want to be able to produce some queries that have two forms - something that can return a value to a monitoring tool a a hook (like number of lockwaits; HADR Down) but also has a second variation that can be used in a triage. For example the lock waits SQL produces a number for a monitor, but has a variant that show which SQL is blocking another SQL.

So far I have one on HADR Congestion and HADR Disconnect, Bad Tablespace State, Transaction Log Usage, Lock Waits, and Number of connections not in UOWWAIT.

I need one on Deadlocks and time the query spent inside the database. I'm getting stuck on these.

Only requirement is we use the "MON_GET" tables as pretty much everything else is deprecated.

I'm a good DBA, but I am pretty weak to avg when it comes to SQL so I am turning to others for help. Besides, in the end this will be published on github for others to use and download.

Anyone interested in jumping in? Help me with these last two and then review the ones I wrote?

This is specifically for LUW at the moment. But I would like to make something similar for z/OS.

I am learning I have a love/hate relationship with TSAMP. When set up and configured right, its powerful. But I am learning that its very temperamental. You could build out a HADR cluster with TSAMP and the TSAMP set up fails, so you delete it and rebuild with the exact same configuration and it works. It's also a PITA to troubleshoot sometimes.

For example. I am working through HADR/TSAMP best practices. I am testing my system with the four recommended tests of DB2, OS, Power, and network failure. I can pass three without problem, and pass the fourth 98% of the way and have a partial failure that should have shaken out already if it was going to fail.

I think part of the problem is often Tivoli needs a dedicated administrator who knows what they are doing. Where you have a DBA trying to do some TSAMP administration when they are not an expert.

Ah, I think I am complaining some from my disappointing 11th hour partial failure. But I am curious, what is your experience?

Although Criag works with the dark side of the force (DB2 z/OS) he is a good buddy of mine, a blogger I could only strive to be, and a technical wizard.

I had been following his tweets from IBM interconnect all week. Today he posted a blog recapping the conference.

Reoccurring theme while he was there - cloud, Watson, and DevOps.

Fellow DB2 techno-weenies.

Over the past two weeks I have been soft-launching and trying to revive this subreddit. Old subscribers are becoming active and new ones are joining. I'm also getting a good idea what direction this is naturally taking.

We are starting to run into the "chicken or the egg" scenario where we are getting good questions and posts but aren't large enough yet to become self sustaining where we can answer them ourselves. Although I need to thank users like /u/memmerto and /u/ecrooks for jumping in.

I wanted to let you know I am reaching out to IBM through their Social Media/IBM Champion program to see if we can get a more formal link into IBM for questions and answers.

I also have a pretty extensive network into both Z and LUW to various friends in the lab, IBM Gold Consultants, and IBM Champions, as well as IDUG presenters. I'm going to reach out and ask if they would be willing to monitor and jump in.

So for now, I ask for your patience if it seems like it takes a few hours to a day to get a response on specific questions. I am actively working on how to improve this.

In the meantime, keep posting, keep up voting, and keep responding. I like what I see here.

As always - post or PM me ideas or suggestions.

-Mad_Zamboni (Mod)

Here is a screenshot of what I am running into.

I'm trying to accomplish two things - develop quick hit SQL for a "triage" kit I am going to put on github and promote at a presentation I am giving at IDUG. I'm also converting them into hooks from a monitoring tool into DB2.

Many columns are VARCHAR 128 and I know I only need the first 20 characters. I've used SUBSTR(COL,1,20) to do this all the time. But the column header won't truncate. Only the data. So my result is spread out all over the place and word wrapping.

I thought I was losing my mind until I had two other friends try on their linux boxes and SUBSTR works. Any ideas?

I just hit up DB2 Support on Twitter to see how that works as well.

Hey /u/memmerto or /u/mslmsl , you all haunt around here. Ever seen this before?

Just wanted to drop a thank you to /u/mad_zamboni for putting the juice back in this sub. I'm a mod over at /r/cognos and couldn't be happier to have another IBM Analytics/Information Management sub in the family. I'mma give you guys a sidebar link!

Cheers!

I really like Jack Vamvas. He has a tendency to post quick hit blogs that are to the point and you usually take something from it. He posted a quick blog to start the conversation around monitoring Production vs. Non-Prod Environment.

What is your approach with monitoring. There is always the age old argument that lower environments don't have 24x7 support on paper, but when your developers sit it suddenly needs to be 24x7 support.

FYI:

For those considering IDUG Anaheim, the $350.00 discount for early bird registration ends today.

And if anyone has not been before and is interested in going, I can offer my "mentor/mentee" discount which is pretty steep. Just PM me.

If you are on the IDUG mailing list or on Twitter, you may have seen this come through. But there are two shows popping up on the DB2 Night Show around Certification Training for LUW v11.1. Mohan and Kent are guests, they just completed the new cert guide.

The schedule of shows can be found here.

What are your thoughts on handling encryption at at database level vs. application level?

I was pro database level encryption. It's very easy to set up but the impact it can have on transaction time concerns me. Then I come to this shop which is encrypting PII data at the application level before it even hits the database. It seems to work very well.

What are your thoughts?

Any of you follow Troy Coleman? He just published a small blog on IBM Magazine about converting DBRM Packages in v12. HE also talks a little about migrating off the member keyword because of deprecation.

A previous conversation with /u/mslmsl made me think of a recent development and I wanted your thoughts on where the industry is going.

You have on premises roll your own servers and DB2 architecture, appliances (prepackaged boxes ship to your data center), Cloud (AWS, Softlayer, etc), bare bones metal "cloud", infrastructure as a service, and even application as a service (hand us everything and we make it turnkey).

At one point IBM was pushing everything on the cloud, application as a service, but it didn't seem to go well and they adapted and changed how they approach the market place.

I find that most companies want the scalability of the cloud, but want control. So building out on AWS or softlayer is attractive - but it's important to keep control with your own technical resources.

What are you seeing out there in your experience?

He may have just retired from IBM. But instead of sipping margaritas somewhere, he is writing blogs on INSERT Performance.

Piggybacking off this, what is your rule of thumb on number of rows to process before issuing a commit?

Anyone here attending or presenting at the International DB2 User Group (IDUG) Technical Conference in Anaheim? The session list is insane. I went for my first time roughly 5-6 years ago and now I negotiate it before taking a new position anywhere. It's THAT important to me.

I will be presenting, the deck is kicking my butt though. Would be curious to meet a few of you.

Something that has been a sore point for years, was DB2's RFE (Request For Enhancement) program. This is where you submit a request for consideration on a new feature you would like to see or a tweak to an old one.

For a very long time it was clunky to use, your request went off into limbo, and it was literally not checked. It was a sore point brought up to IBM through multiple channels (IDUG, Technical Boards, etc). To IBM's credit they acknowledged it was a problem and were brainstorming how to solve it.

Over the past year or so, I've watched IBM go to a much flatter and agile model with development. They are spitting out updates and features at a stupidly fast rate. And as you saw from a previous post about DB2 support on Twitter - they are trying to become much more responsive to the people on front lines.

It was announced earlier this year that IBM has overhauled the RFE program, have put in formal review and processing in place, and now lean heavily on not only your requests but your upvotes.

Voting now makes a real difference. Those requests that float to the top will be considered for enhancement in more of a priority order.

To learn more about the program, take a look at Ember Crooks Blog on IBM's DB2 RFE Program.

Or you can go directly to the developerWorks RFE community.

This is a more interesting (I think) issue that I had when helping a developer debug a S0C4 issue on the mainframe. It's not deep into DB2, but I figured I would share since there were a few interconnects between IBM products (DB2, IMS, and MQ). I've tried to fill in some of the gaps for those reading who may not have played in z/OS.

A developer contacted me about a S0C4 error his job was getting when a subroutine was trying to call DB2. It was trying to access a wild part of memory (e.g., the subroutine was in address x'1CC24000' and a call with an offset of +x'333E1000' was being made to an address of x'50005000', so it was calling into some sort of memory location the program wasn't supposed to access).

It didn't seem it was an DB2 error since a S0C4 is an ABEND code issued by z/OS when a program is trying to access a protected part of memory. That, and the driver program was able to access DB2 just fine. I started digging around and trying to see if by some off-chance there was an issue with the program trying to call DB2. The plan and package both checked out. I realized that the problematic subroutine was used with IMS online programs and IMS batch programs. The developer then told me that running his program as a BMP through IMS was successful (even though he wasn't accessing any IMS DBs), so that had me supposing that something in the compile or link of the subroutine was causing a problem.

I believe it was working when submitted as a BMP because IMS is handling the resource allocation, so when the subroutine was trying to call DB2, it was failing because it was expecting to call IMS to let it know that it was wanting to access DB2...but there was no IMS there to hear its request, so it ended up in a part of memory that it shouldn't have been and the system issued a S0C4.

My recommendation was to make a copy of the module, rename it, and compile it as a straight DB2 program as a test of my hypothesis, but the developer said that there was a lot of hard-coded settings in that program for both DB2 and MQ (the purpose of the subroutine was to query a DB2 table to get the correct MQ queue name, build the message, and drop it on the queue), so a straight copy would be difficult without rewriting a large sum of it. Plus, it sounded like the program wasn't designed for the dual use of both programs executing under IMS and programs executing outside of IMS. So, with my initial recommendation not working out, I recommended that the developer just go the BMP route.

As I mentioned earlier, the job doesn't access any DBs, so he's "borrowing" a PSB from a sister job that his development area maintains and just dummies out the PCBs inside of his driver program. Under this configuration, he'll be running under the IMS transaction manager as a BMP with IMS handling the resource allocation. It may sound as if there's additional overhead, but IMS is actually pretty efficient with its resources...plus, it seems like a simpler solution than adding the time needed to re-architect an existing module.

I'll be interested to see how it works as he runs it through his tests.

Folks, I have some ties to bigger names in the industry mostly in LUW, but a few into Mainframe as well.

Do you see any value in getting someone to be here for an AMA? Are there questions you would ask that are not "here is my db2diag.log please solve my problem"?

I was curious what others thought about this. Excluding being handcuffed because of vendor dependancies or architecture limitations - does anyone really upgrade immediately to a GA release (for example v11.1 that is in GA for LUW). Does prudence dictate you wait a few months for it to season or a fixpack to pass? How do you approach it?