I am a developer on the IBM DB2 for z/OS product, where I have worked for the last 12 years. I have spent a lot of my time working on stored procedures in DB2 for z/OS, and have most recently been involved in text search support as well as XML. I frequently give presentations on all of these topics. I am very interested in social computing and its implications on business. I'm also on facebook as well as mySpace. I'm an active bicyclist, swimmer, runner, and golfer, too.

Wouldn't you be tempted to peek at this data?

It seems that an employee accessed some data that he or she shouldn't have. And they are still dealing with the extent of the breach. Now... I admit that it sure would be tempting to peek at celebrity health records. I don't know why, I guess somehow every aspect of a famous person's life is interesting. However, this particular employee went on and disclosed information to the National Enquirer! I can't imagine that the money received could possibly have been worth enough to lose one's job over. But sure, it would be tempting to just look, or at least test my authority to peek if I knew I might find something to give me a chuckle or a smug feeling that I knew something others didn't. ("What? Cher had cosmetic surgery??") Now - the thought that I might be tracked or caught would absolutely shut down that temptation for me.

Clearly the press on this data access breach is horrible and condemning. But those of us in the information technology industry aren't shocked by this -- in general, I've seen that IT people can all access more than we should be able to, just to be able to do our job. And we're trustworthy, even if it's tempting!! But the systems we manage still need to change to prevent this access.

This latest blow-up is one more indication that scrutiny has to tighten around who can access what data and how to track it, and it's becoming a very public concern. Of course I see that IBM has lots of solutions around this - a search of ibm.com for 'data privacy' came up with over 200,000 hits.

Be careful out there - you don't want to end up on the news!

Categories : [   data_governance  |  information_management  ]

Apr 11 2008, 07:11:40 PM EDT Permalink

Interesting technology to replay a system crash

Reading the article, I was struck by a couple of points. They quote Lindo as saying that the inspiration came to them as "Wouldn't it be great if we could just TiVo this and replay it?" And then it says this:

Innovation by analogy is a powerful concept, says Giovanni Gavetti, an associate professor at the Harvard Business School who, with his colleague Jan W. Rivkin, has published research on how businesses can use analogic reasoning as a strategic tool. Human beings are analogy machines, he notes, dealing with new information by comparing it to things they already know something about.

That's true, I often try out analogies when I'm trying to understand or explain something. And I can really see how that could lead to innovations, as well as to some odd product evolutions. For a consumer example, I love how the iPhone lets me listen to my voicemail messages in any order, instead of sequentially, which must have been a leftover paradigm from when messages were stored on an analog tape. I can picture someone saying - "why can't I access my messages like I read my email?" - and voila - innovation.

Then I started wondering just how much you could tinker with the crash replay. Could you start eliminating concurrently-running applications, for example, to see if any of them contributed to the crash? And could you test a fix with the replay to see if it fixes the crash?

I also wonder whether IBM's customers would voluntarily seek out software like this to help them narrow down problems. It's not from IBM, and I really don't know any more about it than is in the article above. It's from a company called Replay Solutions, and it runs on several versions of the Microsoft Windows operating system. So, no mainframe support yet (grin). But you could ask them about it!

Categories : [   bug  |  recreate  |  software  ]

Mar 31 2008, 04:26:37 PM EDT Permalink

What grade would your organization get?

I found the article online here and found some interesting things, although nothing specific about California.

The article says:
When all is said and done, a state’s skill with information is found at the intersection of three distinct operations: the willingness to share data, the capacity to generate good information, and the ability to get those who should use the data to do so.

Well, that sounds a lot like stuff that I have talked about when describing IBM's Information on Demand strategy. Is your organization good at doing this? I particularly noted the last point in the article, because some of the states complain that their legislators just aren't interested in using the data! Maybe we information professionals have to make that easy (and fun?) to do.

What about the highest-graded states? The article had this to say about one of them:
In Washington State, Governor Christine Gregoire held a series of town hall meetings on the budget to communicate results to citizens and follow up on the budgetary priorities she had previously established with much citizen input. “We want to give concrete information about whether a difference has been made or hasn’t"

Yep... this is what everyone wants to know. What did we say we'd do? Did it make a difference? In fact, I've been trying to get this type of information from my financial analyst for some time!

What about states that were graded worse than California?
Some state employees in Rhode Island are still operating with typewriters—electric, of course, but still a far cry from the ability to share information in a database. New Hampshire has such weak data-sharing systems that it doesn’t know how much it spends each month—kind of like an average Joe who’s lost his checkbook.

And finally:
At the opposite end of the spectrum, there’s Wyoming. Its transportation department has linked geographic information systems to financial systems and now knows with exact specificity how money is being spent, down to the cost of the salt used between each mile marker on the state’s snowy roads.

OK, well, perhaps that is an example of too much information! :-)

Categories : [   information_on_demand  ]

Mar 11 2008, 05:20:11 PM EDT Permalink

Wow - one-time charge for DB2 for z/OS!

Announcing today, and already found here is this gem of a news item tidbit:

IBM is also announcing the immediate availability of DB2 for z/OS Value Unit Edition, which provides a new one-time-charge offering that enables the deployment of new application workloads. This offering strengthens the role of System z as a cornerstone for key business initiatives such as SOA, Data Warehousing, Business Intelligence and packaged applications such as SAP. DB2 for z/OS Value Unit Edition and IBM Information Server enable System z clients to further deliver trusted information for their dynamic warehousing requirements.

Just updated: Here is where you can find the gory details.

Is this cool or what? Doesn't this just remove the last and final objection that the application architects have for leaving DB2 for z/OS out of the running for those new applications?

Now, lest you think I am somehow reflecting a non-developer perspective, look, I have spent most of my efforts in DB2 for z/OS developing the kinds of new technologies designed to attract new workloads, and since even I have heard the pricing objection, isn't it perfectly fair for me to mention this in my DW space? And heck, since I am a developer, not a pricing person by any stretch of the imagination, if this has gotten my attention, you know it's big news!

Bring on those new workloads! And then come to us in development and tell us what you need to bring more work onto z, OK?

Categories : [   db2_z/OS  |  db2_z/OS_VUE  ]

Feb 26 2008, 02:36:35 PM EST Permalink

Native SQL stored procedures in DBM1 - What, me worry?

1. Doesn't the external WLM-managed infrastructure provide some throttling of stored procedures? What's going to happen when this is gone?
2. Can DBM1 handle the same amount of concurrent stored procedures as multiple WLM-SPAS?
3. User routines only use below the bar storage, so how much below the bar storage is available in DBM1 for these native SQL procedures?

In order to answer this, I have to explain a little bit about how DB2 handles native SQL procedures. They are simply packages, with "runtime structures" for the SQL statements to be executed. So, when you invoke a native SQL procedure, DB2 finds and loads the package and executes the statements.

In contrast, an external stored procedure with SQL needs a complete language environment for the user program, and then that external program comes back to DBM1 to get its package loaded and SQL statements executed. That's what needs to be "throttled" - the external program execution environments and their associated TCBs. When an incoming stored procedure request is queued for WLM, the DB2 thread is suspended in DBM1. Many customers have experienced delays and DBM1 storage problems when their WLM goals weren't adjusted properly and the queued requests built up. The solution is to either adjust the WLM goals, or else adjust the limit on DB2 threads (local and/or distributed).

With native SQL procedures, the thread will just switch packages when the call statement is processed and run the procedure - no queuing. The storage used for the local variables is above the bar and managed with efficient algorithms. The maximum concurrent first-level native SQL procedures is effectively the same as your setting for maximum DB2 threads. (What I mean by first-level is that a native SQL procedure may have a nested call to another native SQL procedure, so the actual number of concurrent native SQL procedures may be even higher).

So, I guess the way I'd answer the questions is:

1. Yep. When it's gone, SPs will run much more efficiently
2. Yep - in fact likely more
3. n/a - SQL procedures aren't "really" user routines - they are a pre-defined set of SQL statements, and they don't use below the bar storage

Of course I recommend that you test your native SQL procedures in your environment and measure for yourself, and do capacity planning based on the results of your testing. Native SQL procedures will use some DBM1 storage, after all, and how much depends on what statements and what variables are used in the program.

Oh, and if you didn't recognize it, the "What, me worry?" is a reference to the signature quote from Alfred E. Neuman. It's more than a little tongue-in-cheek.


Feb 11 2008, 03:37:53 PM EST Permalink

SQLPROCEDURECOLS - a metadata stored procedure

• Don't call the metadata stored procedures

Many invocations of DB2 for z/OS stored procedures come from a Java(TM) or a CLI application. The software stack for these programs accessing DB2 for z/OS is through a "driver" program. These driver programs have SQL packages bound to DB2 for z/OS, and in the case of the application invoking a stored procedure, there is a fair amount of code executed in the driver program.

For a CLI program (the term CLI is often used interchangeably with ODBC) -- this is usually something running from a Microsoft(TM) application accessing DB2 for z/OS. The DB2 connect software that includes the driver for DB2 for z/OS has some smarts in it so that if the application is coded using incorrect data types for the stored procedure being invoked, the driver recovers and invokes the SQLPROCEDURECOLS metadata stored procedure on DB2 for z/OS to find out what the data types are and then re-sends the stored procedure call to DB2 for z/OS. Yes, you got it right, this means that a poorly coded application can invoke 3 stored procedure calls for every SQL CALL it's trying to do -- one to the original SP, one to SYSIBM.SQLPROCEDURECOLS, and then again to the original SP with the correct parm types! How do you recognize this? Well, you could run a client-side DRDA trace and it will show up there. Or you can look at statistics at the server. Or you can set the value DESCRIBEPARAM=0 in the db2cli.ini file on the client, and let the applications get the error SQLCODE -301 because now the driver won't do the metadata PS call and instead will let the application fail due to using the wrong datatype. Same result if you issue a -STOP PROCEDURE (SYSIBM.SQLPROCEDURECOLS) ACTION(REJECT) command on the DB2 for z/OS server.

For a Java(TM) program, the current driver is the DB2 Universal Java Driver, and it will not invoke the metadata stored procedure. So this is an excellent reason to switch to the current driver, because the older version of the driver went through the CLI code path and had the same problem as described above.

Note that if you invoke a stored procedure from the command line (the CLP), that code will always invoke the SQLPROCDURECOLS stored procedure since the command line doesn't provide anything for what data type the arguments are.

Now, if you are stuck with a CLI program that you can't modify, what can you do to improve the performance of SQLPROCEDURECOLS? Well, APAR PK57017 just shipped which reduces the size of the package for this stored procedure, so you can free up some EDM pool usage and get a small CPU usage improvement. You can also be sure you run RUNSTATS so that the data access for this SP is the most efficient it can be. I have also heard rumors of some customers creating additional indexes on the tables used by SQLPROCEDURECOLS, but I don't have any specifics on that, sorry.

Categories : [   db2_z/OS  |  metadata_stored_procedures  |  stored_procedures  ]

Jan 21 2008, 12:58:14 PM EST Permalink

Max of 512 stored procedures in a WLMENV?

• No more than 512 SP's in a WLM

Let me explain why I recommend this. It's actually at the bottom of the list, and that's because it doesn't come up that often. But it has, and when it does, it can cost in I/O. DB2 has a Language Environment table of load modules in each stored procedures address space. For stored procedures defined STAY RESIDENT YES, we only have room for 512 load modules in that table. A load module has to be in the table in order for DB2 to invoke it. So, starting with the 512th, we'll delete it from the table after we call it, even if it's STAY RESIDENT YES. And come to think of it, we have separate tables for TYPE MAIN and TYPE SUB.

So to be completely accurate, the recommendation could actually say something like this:

• No more than 512 different load modules for STAY RESIDENT YES SP's in a WLM application environment, that are all either PROGRAM TYPE MAIN or PROGRAM TYPE SUB and invoked during the lifetime of a single instance of a WLM-SPAS.

For that last bit, remember that different invokers of a stored procedure that end up classified in different WLM enclaves will not have their SPs run in the same instance of a WLM-SPAS.

What's a WLM-SPAS? It's what I use to abbreviate a "WLM-established stored procedures address space".

And this post has motivated me to get a more recent copy of my stored procedures recommended practices presentation out online!

Categories : [   DB2_limits  |  WLM_application_environment  |  db2_z/OS  |  stored_procedures  ]

Jan 17 2008, 07:15:19 PM EST Permalink

Pointer to article on enterprise search

Some excerpts:

Company networks contain mountains of structured and unstructured data archived in numerous formats, some of them decades old and stored in secure servers.

IBM also is building a portfolio of enterprise search tools and services, under the OmniFind brand.

Of course you know that DB2 for z/OS data contains mountains of information! This is what our just-released text search support addresses for DB2 for z/OS data - character, binary, and XML. And it's built on OmniFind technology. With this support, you can do text search queries using the built-in CONTAINS() function. It's provided with DB2 9 for z/OS and the no-charge accessories suite.

Now, I know that this is just one piece of enterprise search. In fact, I joke with my colleagues that all of the work that we've put into this is "just an SQL statement". :-) But hey, it's an important piece - it can keep the DB2 for z/OS data where it is and "let the searches come to us".


Jan 14 2008, 05:28:20 PM EST Permalink

Setting up java(TM) stored procedures in DB2 9 for z/OS

If your installation lets SMP/E default to that directory, then the same set up you use for Java stored procedures in DB2 for z/OS V8 will continue to work. But if you change that, then you need to set a new ENVAR in your JAVAENV dataset such as "DB2_BASE=/usr/lpp/db9a/db2910_base" so we can find our code. Otherwise, you'll see this error when the WLM-SPAS tries to start up: java.lang.NoClassDefFoundError: com.ibm.db2.dsnx9.JARLoader

I know, there are an awful lot of "moving parts" to setting up for running java stored routines. You need the DB2 universal java driver, the z/OS JVM, and JCL and a JAVAENV dataset. The stored procedures redbook has a good chapter on setup. It's a complex environment, but a very powerful one, too!

Categories : [   java_stored_procedures  |  stored_procedures  ]

Jan 04 2008, 12:43:39 PM EST Permalink

Text Search is now available in DB2 9 for z/OS

You mean you missed the announcement?

And you just followed that link and still couldn't find it? It's under "utilities", no, it's not that kind of utility, but still, that's where it is.

What is added is built-in functions for contains() and score(), and also shipping a text search server which runs on a separate, non-z/OS server. For more details, see the announcements!

One prerequisite for this is to have a WLM application environment set up to run a java user-defined function. The early customers I've been working with have had the most stumbling with this part of it. So, this is something you can set up even if you are not quite to DB2 9 for z/OS yet. I'll post some more on the setup steps for that.

So, what kind of data are you going to search, and what kinds of searches are you going to do?

Categories : [   contains  |  text_search  ]

Dec 18 2007, 07:53:50 PM EST Permalink

A single SQL statement in a stored procedure?

A new twist on this is with our DB2 9 for z/OS support for native SQL procedures. Folks ask me, well now is it OK to have a single SQL statement in a native SQL procedure?

The thing is, the native SQL procedure is still a package for DB2 to load or at least switch to. So there really still is overhead, not to mention the network time to get over to DB2, as most apps will have to invoke several stored procedures to accomplish their logic. And guess what? Those applications typically invoke the same stored procedures in the same order with the same application logic in between. So... why not make that whole set a single stored procedure?

Bottom line: even with native SQL procedures executing in the DB2 engine rather than the WLM-managed address space, I still recommend an architecture with multiple SQL statements and some business logic rather than a single SQL statement per procedure.

If you do have an architecture with single SQL statements in stored procedures, that's not to say it won't work - it will work, just not as efficiently as it would if you follow the above advice. And even if the single-threaded app performs OK, it won't scale as well as having more SQL statements and logic in the stored procedure, due to longer-held locks over network transmissions as well as some database engine serialization required for external SQL procedures (that part at least is gone in DB2 9).

SQL procedures are absolutely strategic and I do recommend an architecture based on them. The advice I've given other customers is to determine the ones that are invoked most often, analyze the patterns of when they are invoked together from the same applications(s), and work to at least combine those into a single stored procedure with several SQL statements and some logic. If you implement SQL procedures on V8, the switch to DB2 9 native SQL procedures is a snap - drop and create and go, with no change to the code.

So, go, create, reuse, and be happy. :-)

Categories : [   db2_zos_architecture  |  sql_procedures  |  stored_procedures  ]

Nov 27 2007, 12:32:13 PM EST Permalink

Spatial support in DB2 9 as presented at IDUG Athens

Hello from IDUG Europe in Athens

All this week, I am busy at the International DB2 User's Group conference here in Athens, Greece. The two topics I'm presenting are "Native SQL stored procedures in DB2 9 for z/OS" and "Java stored procedures".

I was very glad to get the chance to get across this key point in yesterday's presentation on native SQL stored procedures -- yes, when they execute they are eligible for redirect to the zIIP processor, but only when they are invoked from a remote client, and then at the same percentage as other DDF work. Lots of presentations lately have stated this a bit more broadly, leaving people with the wrong impression. So, let's be clear - when a remote thread comes into DB2, it executes on an enclave SRB, and DB2 dials the zIIP redirect to a certain percentage. A DB2 9 native SQL procedure executes on the invoking execution block and not on a WLM-SPAS TCB - that's one of their big advantages. Thus, when a native SQL stored procedure is invoked from a remote client over a TCP/IP connection, it runs on the enclave SRB and thus picks up that same DDF zIIP redirect percentage. On the other hand, when a native SQL stored procedure is invoked locally on z/OS, it is executed on the TCB that PC'd in from CICS or batch, and that is not eligible for zIIP redirect.

Lots of other good stuff going on here - tomorrow is a DB2 for z/OS Special Interest Group as well as our IBM query panel.

Categories : [   db2_z/os  |  idug  ]

Nov 07 2007, 08:42:33 AM EST Permalink