DBA Rules of Thumb - Part 9 (Call on Others for Help When Needed)

Use All of the Resources at Your Disposal

Remember that you do not have to do everything yourself. Use the resources at your disposal. We have talked about some of those resources, such as articles and books, Web sites and scripts, user groups and conferences. But there are others.

Do not continue to struggle with problems when you are completely stumped. Some DBAs harbor the notion that they have to resolve every issue themselves in order to be successful. Sometimes you just need to know where to go to get help to solve the problem. Use the DBMS vendor’s technical support, as well as the technical support line of your DBA tool vendors. Consult internal resources for areas where you have limited experience, such as network specialists for network and connectivity problems, system administrators for operating system and system software problems, and security administrators for authorization and protection issues.

As a DBA you are sometimes thought of as "knowing everything" (or worse a know-it-all), but it is far more important to know where to go to get help to solve problems than it is to try to know everything there is to know. Let's face it... it is just not possible to know everything about database systems and making them work with all types of applications and users these days.

When you go to user groups, build a network of DBA colleagues whom you can contact for assistance. Many times others have already encountered and solved the problem that vexes you. A network of DBAs to call on can be an invaluable resource (and no one at your company even needs to know that you called for outside help).

Finally, be sure to understand the resources available from your DBMS vendors. DBMS vendors offer their customers access to a tremendous amount of useful information. All of the DBMS vendors offer software support on their Web sites. Many of them provide a database that users can search to find answers to database problems. IBM customers can use IBMLink,^[1] and both Oracle and Microsoft offer a searchable database in the support section of their Web sites. Some DBAs claim to be able to solve 95 percent or more of their problems by researching online databases. These resources can shrink the amount of time required to fix problems, especially if your DBMS vendor has a reputation of “taking forever” to respond to issues.

Of course, every DBA should also be equipped with the DBMS vendor’s technical support phone number for those tough-to-solve problems. Some support is offered on a pay-per-call basis, whereas other times there is a prepaid support contract. Be sure you know how your company pays for support before calling the DBMS vendor. Failure to know this can result in your incurring significant support charges.

---

[1]. IBMLink is a proprietary network that IBM opens up only to its customers.

Read More

DBA Rules of Thumb - Part 8 (Being Business Savvy)

Understand the Business, Not Just the Technology

Remember that being technologically adept is just a part of being a good DBA. Although technology is important, understanding your business needs is more important. If you do not understand the impact on the business of the databases you manage, you will simply be throwing technology around with no clear purpose.

Business needs must dictate what technology is applied to what database—and to which applications. Using the latest and greatest (and most expensive) technology and software might be fun and technologically challenging, but it most likely will not be required for every database you implement. The DBA’s tools and utilities need to be tied to business strategies and initiatives. In this way, the DBA’s work becomes integrated with the goals and operations of the organization.

The first step in achieving this needed synergy is the integration of DBA services with the other core components of the IT infrastructure. Of course, DBAs should be able to monitor and control the databases under their purview, but they should also be able to monitor them within the context of the broader spectrum of the IT infrastructure—including systems, applications, storage, and networks. Only then can companies begin to tie service-level agreements to business needs, rather than technology metrics.

DBAs should be able to gain insight into the natural cycles of the business just by performing their job. Developers and administrators of other parts of the IT infrastructure will not have the vision into the busiest times of the day, week, quarter, or year because they are not privy to the actual flow of data from corporate business functions. But the DBA has access to that information as a component of performing the job. It is empowering to be able to understand business cycle information and apply it on the job.

DBAs need to expand further to take advantage of their special position in the infrastructure. Talk to the end users — not just the application developers. Get a sound understanding of how the databases will be used before implementing any database design. Gain an understanding of the database’s impact on the company’s bottom line, so that when the inevitable problems occur in production you will remember the actual business impact of not having that data available. This also allows you to create procedures that minimize the potential for such problems.

To fulfill the promise of business/IT integration, it will be necessary to link business services to the underlying technology. For example, a technician should be able to immediately comprehend that a service outage to transaction X7R2 in the PRD2 environment means that regional demand deposit customers cannot access their accounts. See the difference?

Focusing on transactions, TP monitors, and databases is the core of the DBA’s job. But servicing customers is the reason the DBA builds those databases and manages those transactions. Technicians with an understanding of the business impact of technology decisions will do a better job of servicing the business strategy. This is doubly true for the DBA’s manager. Technology managers who speak in business terms are more valuable to their company.

Of course, the devil is in the details. A key component of realizing effective business/IT integration for DBAs is the ability to link specific pieces of technology to specific business services. This requires a service impact management capability—that is, analyzing the technology required to power each critical business service and documenting the link. Technologies exist to automate some of this through event automation and service modeling. Such capabilities help to transform availability and performance data into detailed knowledge about the status of business services and service-level agreements.

Today’s modern corporations need technicians who are cognizant of the business impact of their management decisions. As such, DBAs need to get busy transforming themselves to become more business savvy — that is, to keep an eye on the business impact of the technology under their span of control. 

Read More

DBA Rules of Thumb - Part 7 (Don't Become a Hermit!)

Part 7 of our ongoing series on DBA Rules of Thumb is a short one on being accessible and approachable... in other words, Don't Be a Hermit!

Sometimes DBAs are viewed as the "curmudgeon in the corner" -- you know the  type, don't bother "Neil," he'll just yell at you and call you stupid. Don't be like Neil!

Instead, develop a good working relationship with the application developers. Don’t isolate yourself in your own little DBA corner of the world. The more you learn about what the applications do and the application requirements, the better you can adjust and tune the databases to support those applications.

A DBA should be accessible. Don’t be one of those DBAs whom everyone is afraid to approach. The more you are valued for your expertise and availability, the more valuable you are to your company.

Read More

DBA Rules of Thumb - Part 6 (Preparation)

Measure Twice, Cut Once

Being prepared means analyzing, documenting, and testing your DBA policies and procedures. Creating procedures in a vacuum without testing will do little to help you run an efficient database environment. Moreover, it will not prepare you to react rapidly and effectively to problem situations.

The old maxim applies: Measure twice, cut once. In the case of DBA procedures, this means analyze, test, and then apply. Analyze your environment and the business needs of the databases to create procedures and policies that match those needs. Test those procedures. Finally, apply them to the production databases.

DBAs must be calm amid stress. DBAs must prepare for every situation that can be reasonably thought to have the potential to occur... ...and when the unthinkable occurs, the DBA remains logical and thorough in collecting details, ferreting out the root cause of the problem, and taking only the necessary actions to remediate the problem. This Rule of Thumb ties in nicely with the last one (Don't Panic!)... Every action you take should be planned and implemented with a calm disposition. Analysis and preparation are the friend of the DBA. The last thing you want to do is rage into a problem scenario making changes like gunslinger who acts first and worries about the consequences later.

Read More

DBA Rules of Thumb - Part 5 (Don’t Panic!)

Way back in the early 1990s when I was working as a DBA I had a button pinned up in my cubicle that read in large letters “DON’T PANIC!” If I recall correctly, I got it for free inside a game from back in those days based on “The Hitchhiker’s Guide to the Galaxy.” When I left my job as a DBA to go to work for a software company I bequeathed that button to a friend of mine (Hello, Chris!) who was taking over my duties… for all I know, he still has that button pinned up in his office.

But the ability to forgo panicking is a very important quality in a DBA.

A calm disposition and the ability to remain cool under strenuous conditions are essential to the makeup of a good DBA. Problems will occur—nothing you can do can eliminate every possible problem or error. Part of your job as a DBA is to be able to react to problems with a calm demeanor and analytical disposition.

When a database is down and applications are unavailable, your environment will become hectic and frazzled. The best things you can do when problems occur are to remain calm and draw on your extensive knowledge and training. As the DBA, you will be the focus of the company (or at least the business units affected) until the database and applications are brought back online. It can be a harrowing experience to recover a database with your boss and your users hovering behind your computer terminal and looking over your shoulder. Be prepared for such events, because eventually they will happen. Panic can cause manual errors—the last thing you want to happen when you are trying to recover from an error.

The more comprehensive your planning and the better your procedures, the faster you will be able to resolve problems. Furthermore, if you are sure of your procedures, you will remain much calmer.

So Don’t Panic!

Read More

DBA Rules of Thumb - Part 4 (Analyze, Simplify, and Focus)

The job of a DBA is complex and spans many diverse technological and functional areas. It is easy for a DBA to get overwhelmed with certain tasks—especially those that are not performed regularly. In a complex, heterogeneous, distributed world it can be hard to keep your eye on the right ball, at the right time. The best advice I can give you is to remain focused and keep a clear head.

Understand the purpose for each task and focus on performing the steps that will help you to achieve that end. Do not be persuaded to broaden the scope of work for individual tasks unless it cannot be avoided. In other words, don’t try to boil the ocean. If non-related goals get grouped together into a task, it can become easy to work long hours with no clear end in sight.

I am not saying that a DBA should (necessarily) specialize in one particular area (e.g., performance). What I am suggesting is that each task should be given the appropriate level of focus and attention to details. Of course, I am not suggesting that you should not multitask either. The successful DBA will be able to multitask while giving full attention to each task as it is being worked on.

What is the enemy of focus? There are many: distraction, lack of knowledge, “management,” and always worrying about the next thing to try or do. Such distractions can wreak havoc on tasks that require forethought and attention to detail.

Analyze, simplify, and focus. Only then will tasks become measurable and easier to achieve.

Read More

DBA Rules of Thumb - Part 2 (Automate)

Why should you do it by hand if you can automate DBA processes? Anything you can do probably can be done better by the computer – if it is programmed to do it properly. And once it is automated you save yourself valuable time. And that time can be spent tackling other problems, learning about new features and functionality, or training others.

Furthermore, don’t reinvent the wheel. Someone, somewhere, at some time many have already solved the problem you currently are attempting to solve. Look to the web for sites that allow you to download and share scripts. Or if you have budget money look to purchase DBA tools from ISVs. There are a lot of good tools out there, available from multiple vendors, that can greatly simplify the task of database administration. Automating performance management, change management, backup and recovery, and other tasks can help to reduce the amount of time, effort, and human error involved in managing database systems.

Of course, you can take the automation idea too far. There has been a lot of talk and vendor hype lately about self-managing database systems. For years now, pundits and polls have been asking when automation will make the DBA job obsolete. The correct answer is "never" - or, at least, not any time soon.

There are many reasons why DBAs are not on the fast path to extinction. Self-managing databases systems are indeed a laudable goal, but we are very far away from a “lights-out” DBMS environment. Yes, little-by-little and step-by-step, database maintenance and performance management is being improved, simplified, and automated. But you know what? DBAs will not be automated out of existence in my lifetime – and probably not in your children’s lifetime either.
Many of the self-managing features require using the built-in tools from the DBMS vendor, but many organizations prefer to use heterogeneous solutions that can administer databases from multiple vendors (Oracle, DB2, SQL Server, MySQL, etc.) all from a single console. Most of these tools have had self-managing features for years and yet they did not make the DBA obsolete.

And let’s face it, a lot of the DBMS vendors claims are more hyperbole than fact. Some self-managing features are announced years before they will become generally available in the DBMS. All vendors claims to the contrary, no database today is truly 100% self-contained. Every database needs some amount of DBA management – even when today’s best self-management features are being used.

What about the future? Well, things will get better – and probably more costly. You don’t think the DBMS vendors are building this self-management technology for free, do you? But let’s remove cost from the equation for a moment. What can a self-managing database actually manage?

Most performance management solutions allow you to set performance thresholds. A threshold allows you to set up a trigger that says something like “When x% of a table’s pages contain chained rows or fragmentation, schedule a reorganization.” But these thresholds are only as good as the variables you set and the actions you define to be taken upon tripping the threshold. Some software is bordering on intelligent; that is, it “knows” what to do and when to do it. Furthermore, it may be able to learn from past actions and results. The more intelligence that can be built into a self-managing system, the better the results typically will be. But who among us currently trusts software to work like a grizzled veteran DBA? The management software should be configurable such that it alerts the DBA as to what action it wants to take. The DBA can review the action and give a “thumbs up” or “thumbs down” before the corrective measure is applied. In this way, the software can earn the DBA’s respect and trust. When the DBA trusts the software, he can turn it on so that it self-manages “on the fly” without DBA intervention. But today, in most cases, a DBA is required to set up the thresholds, as well as to ensure their on-going viability.

Of course, not all DBA duties can be self-managed by software. Most self-management claims are made for performance management, but what about change management? The DBMS cannot somehow read the mind of its user and add a new column or index, or change a data type or length. This non-trivial activity requires a skilled DBA to analyze the database structures, develop the modifications, and deploy the proper scripts or tools to implement the change. Of course, software can help simplify the process, but software cannot replace the DBA.

Furthermore, database backup and recovery will need to be guided by the trained eye of a DBA. Perhaps the DBMS can become savvy enough to schedule a backup when a system process occurs that requires it. Maybe the DBMS of the future will automatically schedule a backup when enough data changes. But sometimes backups are made for other reasons: to propagate changes from one system to another, to build test beds, as part of program testing, and so on. A skilled professional is needed to build the proper backup scripts, run them appropriately, and test the backup files for accuracy. And what about recovery? How can a damaged database know it needs to be recovered? Because the database is damaged any self-managed recovery it might attempt is automatically rendered suspect. Here again, we need the wisdom and knowledge of the DBA.

And there are many other DBA duties that cannot be completely automated. Because each company is different, the DBMS must be customized using configuration parameters. Of course, you can opt to use the DBMS “as is,” right out-of-the-box. But a knowledgeable DBA can configure the DBMS so that it runs appropriately for their organization. Problem diagnosis is another tricky subject. Not every problem is readily solvable by developers using just the Messages and Codes manual and a help desk. What happens with particularly thorny problems if the DBA is not around to help?

Of course, the pure, heads-down systems DBA may (no, let's say should) become a thing of the past. Instead, the modern DBA will need to understand multiple DBMS products, not just one. DBAs furthermore must have knowledge of the business impact of each database under their care (more details here). And DBAs will need better knowledge of logical database design and data modeling – because it will advance their understanding of the meaning of the data in their databases.

Finally, keep in mind that we didn't automate people out of existence when we automated HR or finance. Finance and HR professionals are doing their jobs more efficiently and effectively, and they have the ability to deliver a better product in their field. That's the goal of automation. So, as we automate portions of the DBA’s job, we'll have more efficient data professionals managing data more proficiently.


This blog entry started out as a call to automate, but I guess it kinda veered off into an extended dialogue on what can, and cannot, be accomplished with automation. I guess the bottom line is this... Automation is key to successful, smooth-running databases and applications... but don't get too carried away by the concept.


I hope you found the ideas here to be useful... and feel free to add your own thoughts and comments below! 

Read More

DBA Rules of Thumb - Part 1

Over the years I have gathered, written, and assimilated multiple collections of general rules of the road that apply to the management discipline of Database Administration (DBA). With that in mind, I thought it would be a good idea to share some of these Rules of Thumb (or ROTs) with you in a series of entries to my blog.

Now even though this is a DB2-focused blog, the ROTs that I will be covering here are generally applicable to all DBMSs and database professionals.

The theme for this series of posts is that database administration is a very technical discipline. There is a lot to know and a lot to learn. But just as important as technical acumen is the ability to carry oneself properly and to embrace the job appropriately. DBAs are, or at least should be, very visible politically within the organization. As such, DBAs should be armed with the proper attitude and knowledge before attempting to practice the discipline of database administration.

Today's blog entry offers up an introduction, to let you know what is coming. But I also will share with you the first Rule of Thumb... which is

#1 -- Write Down Everything

During the course of performing your job as a DBA, you are likely to encounter many challenging tasks and time consuming problems. Be sure to document the processes you use to resolve problems and overcome challenges. Such documentation can be very valuable should you encounter the same, or a similar, problem in the future. It is better to read your notes than to try to recreate a scenario from memory.

Think about it like this... aren't we always encouraging developers to document their code? Well, you should be documenting your DBA procedures and practices, too!

And in Future Posts...

In subsequent posts over the course of the next few weeks I post some basic guidelines to help you become a well-rounded, respected, and professional DBA.

I encourage your feedback along the way. Feel free to share your thoughts and Rules of Thumb -- and to agree or disagree with those I share.

Read More

Say "Hello" to DB2 11 for z/OS

DB2 11 for z/OS Generally Available Today, October 25, 2013

As was announced earlier this month (see press release) Version 11 of DB2 for z/OS is officially available as of today. Even if your company won’t be migrating right away, the sooner you start learning about DB2 11, the better equipped you will be to embrace it when you inevitably must use and support it at your company.

So let’s take a quick look at some of the highlights of this latest and greatest version of our favorite DBMS. As usual, a new version of DB2 delivers a large number of new features, functions, and enhancements, so of course, not every new DB2 11 “thing” will be addressed in today’s blog entry.

Performance Claims

Similar to most recent DB2 versions, IBM boasts of performance improvements that can be achieved by migrating to DB2 11. The claims for DB2 11 from IBM are out-of-the-box savings ranging from 10 percent to 40 percent for different types of query workloads: up to 10 percent for complex OLTP and update intensive batch – up to 40 percent for queries.

As usual, your actual mileage may vary. It all depends upon things like the query itself, number of columns requests, number of partitions that must be accessed, indexing, and on and on. So even though it looks like performance gets better in DB2 11, take these estimates with a grain of salt.

The standard operating procedure of rebinding to achieve the best results still applies. And, of course, if you use the new features of DB2 11 IBM claims that you can achieve additional performance improvements.

DB2 11 also offers improved synergy with the latest mainframe hardware, the zEC12. For example, FLASH Express and pageable 1MB frames are used for buffer pool control blocks and DB2 executable code. So keep in mind that getting to the latest hardware can help out your DB2 performance and operation!

Programmer Features

Let’s move along and take a look at some of the great new features for building applications offered up by DB2 11. There are a slew of new SQL and analytical capabilities in the new release, including: 

• Global variables – which can be used to pass data from program to program without the need to put data into a DB2 table
• Improved SQLPL functionality, including an array data type which makes SQLPL more computationally complete and simplifies coding SQL stored procedures.
• Alias support for sequenceobjects.
• Improvements to Declared Global Temporary Tables (DGTTs) including the ability to create NOT LOGGED DBTTs and the ability to use RELEASE DEALLOCATE for SQL statements written against DGTTs.
• SQL Compatibility feature which can be used to minimize the impact of new version changes on existing applications.Support for views on temporal data. 
• SQL Grouping Sets, including Rollup, Cube
• XML enhancements including XQuery support, XMLMODIFY for improved updating of XML nodes, and improved validation of XML documents.

The BIND and REBIND enhancements made in DB2 11 are important to note here, too. Since BIND and REBIND spans application programming and database administration, I’ll talk about here at the end of the Programming Features section and right before we move on to talk about DBA features.

The first new capability is the addition of the APREUSE(WARN) parameter. Before we learn about the new feature, let’s backtrack for a moment to talk about the current (DB2 10) capabilities of the APREUSE parameter. There are currently two options:

• APREUSE(NONE): DB2 will not try to reuse previous access paths for statements in the package. (default value)
• APREUSE(ERROR): DB2 tries to reuse previous access paths for SQL statements in the package. If the access paths cannot be reused, the operation fails and no new package is created.

So you can either not try to reuse or try to reuse, and if you can’t reuse when you try to, you fail. Obviously, a third, more palatable choice was needed. And DB2 11 adds this third option.

• APREUSE(WARN): DB2 tries to reuse previous access paths for SQL statements in the package, but the bind or rebind is not prevented when they cannot be reused. Instead, DB2 generates a new access path for that SQL statement.

So you can think of APREUSE(ERROR) as functioning on a package boundary, whereas APREUSE(WARN) functions on a statement boundary.

DBA and Other Technical Features

There are also a slew of new in-depth technical and DBA-related features in DB2 11. Probably the most important, and one that impacts developers too, is transparent archiving using DB2’s temporal capabilities first introduced in DB2 10.

Basically, if you know how to set up SYSTEM time temporal tables, setting up transparent archiving will be a breeze. You create both the table and the archive table and then associate the two. This is done by means of the ENABLE ARCHIVE USE clause. DB2 is aware of the connection between the operational table and the archive table, so any data that is deleted will be moved to the archive table.

Unlike SYSTEM time, only deleted data is moved to the archive table. There is a new system defined global variable MOVE_TO_ARCHIVE to control the ability to DELETE data without archiving it, should you need to do so.

Of course, there are more details to learn about this capability, but remember, we are just touching on the highlights today!

Another notable feature that will interest many DBAs is the ability to use SQL to query more DB2 Directory tables. The list of DB2 Directory tables which now can be accessed via SQL includes:

• SYSIBM.DBDR
• SYSIBM.SCTR
• SYSIBM.SPTR
• SYSIBM.SYSLGRNX
• SYSIBM.SYSUTIL

Another regular area of improvement for new DB2 version is enhanced IBM DB2 Utilities, and DB2 11 is no exception to the rule. DB2 11 brings the following improvements:

• REORG – automated mapping tables (where DB2 takes care of the allocation and removal of the mapping table during a SHRLEVEL CHANGE reorganization), online support for REORG REBALANCE, automatic cleanup of empty partitions for PBG table spaces, LISTPARTS for controlling parallelism, and improved switch phase processing.
• RUNSTATS – additional zIIP processing, RESET ACCESSPATH capability to reset existing statistics, and improved inline statistics gathering in other utilities.
• LOAD – additional zIIP processing, multiple partitions can be loaded in parallel using a single SYSREC and support for extended RBA LRSN.
• REPAIR – new REPAIR CATALOG capability to find and correct for discrepancies between the DB2 Catalog and database objects.
• DSNACCOX – performance improvements

Additionally, there is a new command to externalize Real Time Statistics. You can use ACCESS DATABASE … MODE(STATS) instead of stopping and starting a database object or forcing a system checkpoint to externalize RTS.

DB2 11 also delivers a bevy of new security-related enhancements, including:

• Better coordination between DB2 and RACF, including new installation parameters (AUTHEXIT_CHECK and AUTHECIT_CACHEREFRESH) and the ability for DB2 to capture event notifications from RACF
• New PROGAUTH bind plan option to ensure the program is authorized to use the plan.
• The ability to create MASKs and PERMISSIONs on archive tables and archive-enabled tables
• Column masking restrictions are removed for GROUP BY and DISTINCT processing

Online schema changes are still being introduced to new version of DB2 amd DB2 11 offers up some nice functionality in this realm. Perhaps the most interesting new capability is DROP COLUMN. Dropping a column from an existing table has always been a difficult task requiring dropping and recreating the table (and all related objects and security), so most DBAs just left unused and unneeded columns in the table. This can cause confusion and data integrity issues if the columns are used by programs and end users. Now, DROP COLUMN can be used (as long as the table is in a UTS). Of course, there are some other restrictions on its use, but this capability may help many DBAs clean up unused columns in DB2 tables.

An additional online schema change capability in DB2 11 is support for online altering of limit keys, which enables DBAs to change the limit keys for a partitioned table space without impacting data availability.

Finally, in terms of online schema change, we have an improvement to operational administration for deferred schema changes. DB2 11 provides improved recovery for deferred schema changes. With DB2 10, when the REORG begins to materialize pending change it is no longer possible to perform a recovery to a prior point in time. DB2 11 removes this restriction, allowing recovery to any valid prior point.

In terms of Buffer Pool enhancements, DB2 11 offers up the new 2GB frame size for very large BP requirements.

In terms of Data Sharing enhancements, DB2 11 offers faster CASTOUT, improved RESTART LIGHT capability, and automatic recovery of all pages in LPL during a DB2 restart.

Analytics and Big Data Features

There are also a lot of features added to DB2 11 to support Big Data and analytical processing. Probably the biggest is the ability to support Hadoop access. If you don’t know what Hadoop is, this is not the place to learn about that. Instead, check out this link.

Anyway, DB2 11 can be used to enable applications to easily and efficiently access Hadoop data sources. This is done via the generic table UDF capability in DB2 11. Using this feature you can create a variable shape of UDF output table.

This capability allows access to BigInsights, which is IBM’s Hadoop-based platform for Big Data. As such, you can use JSON to access Hadoop data via DB2 using the UDF supplied by IBM BigInsights.

DB2 11 also adds new SQL analytical extensions, including:

• GROUPING SETS can be used for GROUP BY operations to enable multiple grouping clauses to be specified in a single statement.
• ROLLUP can be used to aggregate values along a dimension hierarchy. In addition to aggregation along the dimensions a grand total is produced. Multiple ROLLUPs can be coded in a single query to produce multidimensional hierarchies in a result set.
• CUBE can be used to aggregate data based on columns from multiple dimensions. You can think of it like a cross tabulation.

And finally, new version (V3) of IBM DB2 Analytics Accelerator (IDAA) is part of the mix, too. IDAA V3 brings about improvements such as:

• The ability to store 1.3 PB of data
• Change Data Capture support to capture changes to DB2 data and propagate them to IDAA as they happen
• Additional SQL function support for IDAA queries (including SUBSTRING, among others, and additional OLAP functions).
• Work Load Manager integration

Other "Stuff"

Of course, there are additional features and functionality being introduced with DB2 11 for z/OS. A blog entry of this nature on the day of GA cannot exhaustively cover everything. That being said, two additional areas are worth noting.

• Extended log record addressing – increases the size of the RBA and LRSN from 6 bytes to 10 bytes. This avoids the outage that is required if the amount of log records accumulated exhausts the capability of DB2 to create new RBAs or LRSNs. To move to the new extended log record addressing requires converting your BSDSs.
• DRDA enhancements – including improved client info properties, new FORCE option to cancel distributed threads, and multiple performance related improvements.

Summary

DB2 11 for z/OS brings with it a bevy of interesting and useful new features. They range the gamut from development to administration to performance to integration with Big Data. Now that DB2 11 is out in the field and available for organizations to start using it, the time has come for all DB2 users to take some time to learn what DB2 11 can do. 

Read More

View Naming Conventions

Naming conventions sometimes instigate conflict within the world of DB2, especially when it comes to views. But, really, it should be very easy. Just always remember, that a view is a logical table. It consists of rows and columns, exactly the same as a DB2 table. A DB2 view can (syntactically) be used in SQL SELECT, UPDATE, DELETE, and INSERT statements in the same way that a DB2 table can. Furthermore, a view can be used functionally the same as a DB2 table (with certain limitations on updating as outlined in my article).

Therefore, shouldn't it stand to reason that views should be held to the same naming conventions as are used for tables? (As an aside, the same can be said for DB2 aliases and synonyms).

End users querying views don't need to know whether they are accessing a view or a table. That is the whole purpose of views. Why then, should we enforce an arbitrary naming standard, such as putting a V in the first or last position of a view name, on views?

DBAs and technical analysts, those individuals who have a need to differentiate between tables and views, can utilize the DB2 Catalog to determine which objects are views and which objects are tables.

Most users don't care whether they are using a table, view, synonym, or alias. They simply want to access the data. And, in a relational database, tables, views, synonyms, and aliases all logically appear to be identical to the end user: collections of rows and columns.

There are certain operations that cannot be performed on certain types of views, but the end users who need to know this will generally be sophisticated users. For example, very few shops allow end users to update any table they want using a report writer or query tool (e.g. QMF, SPUFI, etc.). Updates, deletions, and insertions (the operations which are not available to some views) are generally coded into application programs and executed in batch or via online transactions. Most end users need to query tables dynamically.

Now you tell me, which name will your typical end user remember more readily when he needs to access his marketing contacts: MKT_CONTACT or VMKTCT01?

Read More

Happy SYSADMIN Day

To all of the system administrators out there (and I include DBAs and network admins in that group), HAPPY SYSADMIN DAY.

For those who are unaware if this very important holiday, every year on the last Friday of July responsible people everywhere celebrate Sysadmin Day. The idea is to show some appreciation for the folks who keep your systems up and running every day of the week. There is even a page with some gift ideas if you are so inclined to get something for your favorite sysadmin. (Personally, I prefer cash... .)

At the very least you can wish him/her a Happy Sysadmin Day today... and hoist a beer or two in his/her honor at the pub this evening...

Read More

On Becoming a DBA

Perhaps the most frequent question I am asked is: How can I become a DBA?

The answer, of course, depends a lot on what you are currently doing. Programmers who have developed applications using a database system are usually best-suited to becoming a DBA. They already know some of the trials and tribulations that can occur when accessing a database.

If you are a programmer and you want to become a DBA, you should ask yourself some hard questions before you pursue that path. First of all, are you willing to work additional, sometimes crazy, hours? Yes, I know that many programmers work more than 40 hours already, but the requirements of the DBA job can push people to their limits. It is not uncommon for DBAs to work late into the evening and on weekends; and you better be ready to handle technical calls at 2:00 a.m. when database applications fail.

Additionally, you need to ask yourself if you are insatiably curious. A good DBA must become a jack-of-all-trades. DBAs are expected to know everything about everything -- at least in terms of how it works with databases. From technical and business jargon to the latest management and technology fads, the DBA is expected to be "in the know." And do not expect any private time: A DBA must be prepared for interruptions at any time to answer any type of question -- and not just about databases, either.

And how are your people skills? The DBA, often respected as a database guru, is just as frequently criticized as a curmudgeon with vast technical knowledge but limited people skills. Just about every database programmer has his or her favorite DBA story. You know, those anecdotes that begin with "I had a problem..." and end with "and then he told me to stop bothering him and read the manual." DBAs simply do not have a "warm and fuzzy" image. However, this perception probably has more to do with the nature and scope of the job than with anything else. The DBMS spans the enterprise, effectively placing the DBA on call for the applications of the entire organization. As such, you will interact with many different people and take on many different roles. To be successful, you will need an easy-going and somewhat amiable manner.

Finally, you should ask yourself how adaptable you are. A day in the life of a DBA is usually quite hectic. The DBA maintains production and test environments, monitors active application development projects, attends strategy and design meetings, selects and evaluates new products and connects legacy systems to the Web. And, of course: Joe in Accounting just resubmitted that query from hell that's bringing the system to a halt. Can you do something about that? All of this can occur within a single workday. You must be able to embrace the chaos to succeed as a DBA.

Of course, you need to be organized and capable of succinct planning, too. Being able to plan for changes and implement new functionality is a key component of database administration. And although this may seem to clash with the need to be flexible and adaptable, it doesn't really. Not once you get used to it.

So, if you want to become a DBA you should already have some experience with the DBMS, be willing to work long and crazy hours, have excellent communication and people skills, be adaptable and excel at organization. If that sounds like fun, you'll probably make a good DBA.

Read More

The Ever-Changing Role of the DBA

Defining the job of DBA is getting to be increasingly difficult. Oh, most people know the rudimentary aspects of the job, namely keeping your organization's databases and applications running up to par. The DBA has to be the resident DBMS expert (whether that is DB2, Oracle or SQL Server, or most likely a combination of those). He or she has to be able to solve thorny performance problems, ensure backups are taken, recover and restore data when problems occur, make operational changes to database structures and, really, be able to tackle any issue that arises that is data-related.

The technical duties of the DBA are numerous. These duties span the realm of IT disciplines from database design to physical implementation and consistent, on-going monitoring of the database environment.

DBAs must possess the abilities to create, interpret, and communicate a logical data model and to create an efficient physical database design from a logical data model and application specifications. There are many subtle nuances involved that make these tasks more difficult than they sound. And this is only the very beginning. DBAs also need to be able to collect, store, manage, and query data about the data (metadata) in the database and disseminate it to developers that need the information to create effective application systems. This may involve repository management and administration duties, too.

After a physical database has been created from the data model, the DBA must be able to manage that database once it has been implemented. One major aspect of this management involves performance management. A proactive database monitoring approach is essential to ensure efficient database access. The DBA must be able to utilize the monitoring environment, interpret its statistics, and make changes to data structures, SQL, application logic, and the DBMS subsystem to optimize performance. And systems are not static, they can change quite dramatically over time. So the DBA must be able to predict growth based on application and data usage patterns and implement the necessary database changes to accommodate the growth.

And performance management is not just managing the DBMS and the system. The DBA must understand SQL used to access relational databases. Furthermore, the DBA must be able to review SQL and host language programs and to recommend changes for optimization. As databases are implemented with triggers, stored procedures, and user-defined functions, the DBA must be able to design, debug, implement, and maintain these code-based database objects as well.

Additionally, data in the database must be protected from hardware, software, system, and human failures. The ability to implement an appropriate database backup and recovery strategy based on data volatility and application availability requirements is required of DBAs. Backup and recovery is only a portion of the data protection story, though. DBAs must be able to design a database so that only accurate and appropriate data is entered and maintained - this involves creating and managing database constraints in the form of check constraints, rules, triggers, unique constraints, and referential integrity.

DBAs also are required to implement rigorous security schemes for production and test databases to ensure that only authorized users have access to data. As industry and governmental regulations multiply, the need to audit who did what to which data when is also a requirement for sensitive data in production systems – and the DBA must be involved in ensuring data auditability without impacting availability or performance.

And there is more! The DBA must possess knowledge of the rules of relational database management and the implementation of many different DBMS products. Also important is the ability to accurately communicate them to others. This is not a trivial task since each DBMS is different than the other and many organizations have multiple DBMS products (e.g., DB2, Oracle, SQL Server).

Remember, too, that the database does not exist in a vacuum. It must interact with other components of the IT infrastructure. As such, the DBA must be able to integrate database administration requirements and tasks with general systems management requirements and tasks such as network management, production control and scheduling, and problem resolution, to name just a few systems management disciplines. The capabilities of the DBA must extend to the applications that use databases, too. This is particularly important for complex ERP systems that interface differently with the DBMS. The DBA must be able to understand the requirements of the application users and to administer their databases to avoid interruption of business. This includes understanding how any ERP packages impact the business and how the databases used by those packages differ from traditional relational databases.

But Things Are Changing

So at a high level, DBAs are tasked with managing and assuring the integrity and efficiency of database systems. But keep in mind, too, that there are actually many different DBAs. Some focus on logical design; others focus on physical design; some DBAs specialize in building systems and others specialize in maintaining and tuning systems; and there are specialty DBAs and general-purpose DBAs. Truly, the job of DBA encompasses many roles.

Some organizations choose to split DBA responsibilities into separate jobs. Of course, this occurs most frequently in larger organizations, because smaller organizations often cannot afford the luxury of having multiple, specialty DBAs.

Still other companies simply hire DBAs to perform all of the tasks required to design, create, document, tune, and maintain the organization’s data, databases, and database management systems.

But no matter what "type" of DBA you happen to be, chances are that your role is changing and adapting to new types of computing and data requirements. Indeed, one of the biggest challenges for DBAs these days is the ongoing redefinition of the job roles and responsibilities.

The primary role of database "custodian," of course, continues to be the main emphasis of the job. But that is no longer sufficient for most organizations. The DBA is expected to take on numerous additional -- mostly technical -- roles. These can include writing application code, managing the application server, enterprise application integration, managing Web services, network administration and more.

If you compare the job description of DBAs across several organizations, it is likely that no two of them would match exactly. This is both good and bad. It is good because it continually challenges the technically-minded employees who tend to become DBAs. But it can be bad, too; because the job differs so much from company to company, it becomes more difficult to replace a DBA who leaves or retires. And no one can deny that database administration is a full-time, stressful job all on its own. But the stress level just keeps increasing as additional duties get tacked onto the DBA's "to do" list.

Summary

There are many jobs that DBAs perform and it can be confusing when you try to match job title up against the responsibilities of the job. Don't let your job title keep you from expanding into other, related disciplines. The more you know and the more you can do, the more employable you become... and that is important in this day and age!

Read More

Some New Year's Resolutions for DBAs

This is sort of a re-blogging (to coin a term). I first published this last month in the Data Management Today blog I wrote for NEON. Well, I no longer work for NEON and I'm not sure how long that blog will remain active, so I thought it might make sense to re-blog some of the pertinent entries here... so here goes with my New Year's Resolutions for DBAs blog entry...

At the beginning over every year many of us take the time to cobble together some resolutions for the coming year. We plan to lose weight, save money, stop smoking, and so on. Usually, it doesn’t take long before we’ve abandoned these resolutions. Perhaps we’d be wiser to make some business related resolutions. With that in mind, here are some thoughts on the New Year’s resolutions you might be wise to make as a DBA in 2010.

Are you insatiably curious? A good DBA must become a jack-of-all-trades. DBAs are expected to know everything about everything -- at least in terms of how it works with databases. From technical and business jargon to the latest management and technology fads, the DBA is expected to be "in the know." So perhaps “be more curious” would be a useful DBA resolution.

Most DBAs know that private time is a luxury we cannot afford. A DBA must be prepared for interruptions at any time to answer any type of question -- and not just about databases, either. With that in mind, how are your people skills? DBA are usually respected as a database guru, but also frequently criticized as a curmudgeon with limited people skills. Just about every database programmer has his or her favorite DBA story. You know, those anecdotes that begin with "I had a problem..." and end with "and then he told me to stop bothering him and read the manual." DBAs simply do not have a "warm and fuzzy" image. However, this perception probably has more to do with the nature and scope of the job than with anything else. The DBMS spans the enterprise, effectively placing the DBA on call for the applications of the entire organization. As such, you will interact with many different people and take on many different roles. To be successful, you will need an easy-going and somewhat amiable manner. So another good New Year’s resolution might be to “improve your people skills.” Take a Dale Carnegie course or start by reading Carnegie’s seminal book, How to Win Friends and Influence People.

How adaptable you are? A day in the life of a DBA is usually quite hectic. The DBA maintains production and test environments, monitors active application development projects, attends strategy and design meetings, selects and evaluates new products and connects legacy systems to the Web. And, of course: Joe in Accounting just resubmitted that query from hell that's bringing the system to a halt. Can you do something about that? All of this can occur within a single workday. You must be able to embrace the chaos to succeed as a DBA. So a third resolution might be to “roll with the punches” better – and without complaining!

Of course, you need to be organized and capable of succinct planning, too. Being able to plan for changes and implement new functionality is a key component of database administration. And although this may seem to clash with the need to be flexible and adaptable, it doesn't really. Not once you get used to it. You just need to prepare yourself to be adapatable and organize to incorporate change more rapidly than others. So my final suggestion for a 2010 New Year’s resolution is to adopt a planning methodology and stick to it. Buy a planner – either electronic or not – and use it this year. You might even consider taking a time management class.

If you keep all of these resolutions, just imagine how productive you will be in 2010. And then you can use 2011 to lose weight and save money and…

Read More

Evaluating a DBA Job Offer

Today's blog entry is for DBAs. Although this blog is focused on mainframe DB2 the advice here applies to any DBA job... so pass the linik on to your Oracle and SQL Server friends...


As a DBA, it is almost inevitable that you will change jobs several times during your career. When making a job change, you will obviously consider requirements such as salary, bonus, benefits, frequency of reviews, and amount of vacation time. However, you also should consider how the company treats their DBAs. Different organizations place different value on the DBA job. It is imperative to your career development that you scout for progressive organizations that understand the complexity and ongoing learning requirements for the position.

Here are some useful questions to ask:

Does the company offer regular training for its DBAs to learn new DBMS features and functionality?

As a DBA you need to be well-versed on the latest and greatest features of the DBMSs you manage. And, on average, there will be a new version to contend with every 18 to 24 months. It is possible to learn the basics by reading the WHAT'S NEW manual and skimming through the voluminous, additional manuals, but some formal training is warranted to get the most out of a new version of the DBMS.


What about training for related technologies such as programming, networking, e-business, transaction management, message queueing, and the like?

DBAs are also called upon to administer more than just databases these days. A top notch employer will allow its DBAs to be trained in new technologies... as well as time for independent learning through reading books and articles.


Does the company allow DBAs to regularly attend local user groups? What about annual user groups at remote locations?

User groups are essential for networking with others who perform the same, or similar, job duties. By attending local user group meetings you can not only get inexpensive training through watching the presentations, but you can also learn exchange ideas with your peers.


Are there backup DBAs, or will you be the only one on call 24/7?

Nobody wants to be the only DBA on call, every night, all the time, on weekends, holidays, etc. And if there is no backup what happens if you take a vacation? Is it really a vacation if you have to carry a company cell phone everywhere you go?


Are there data administration and system administration organizations, or are the DBAs expected to perform all of these duties, too?

DBA is a full-time job but some organizations expect the DBA staff to handle data administration and system administration duties, too. Depending on the volume of work this might not be a deal breaker, but be cautious.


Does the DBA group view its relationship with application development groups as a partnership? Or is the relationship more antagonistic?

A partnership is essential in order to produce optimally performing database applications. And if you do not have applications that perform well, then the DBA job will be burdensome.


Are DBAs included in design reviews, budgeting discussions, and other high-level IT committees and functions?

The more involved the DBA team is in the overall IT strategy the better prepared the company's databases will be to support the required work... and the easier your job will be as a DBA.


The more YES answers you get to these questions, the more progressive the DBA environment is. Be sure to ask these questions during your interview. It will show that you have experience and that you care about your career. Be sure to research the answers later, too. Ask around about the company to those who used to work there and anyone you know (remember those user groups) that currently works there. Sometimes the answers given by the workers will not exactly match those given by the interviewer.

Keep in mind, too, that these are not the ONLY questions you need to ask.

And good luck with you DBA career!

Read More

Bad Standards

Just started a new series on bad standards over on my Data Management Today blog.

Check it out when you get a chance and share your favorite "bad standards" either here or there... or by e-mailing me.

Read More

DBA Rules of Thumb

Database administration is a very technical discipline, but it is also a discipline in which the practitioner is very visible politically within the organization. As such, DBAs should be armed with the proper attitude and knowledge before attempting to practice the discipline of database administration.

Just as important as technical acumen, though, is the ability to carry oneself properly and to embrace the job appropriately. With this in mind, I wrote a series of blog entries on DBA Rules of Thumb over at my Data Management Today blog... and I thought the information I wrote there may be helpful to my DB2 and mainframe readership here, so I'm sharing the eight rules of thumb (with links) here on my DB2 Portal blog:

1. Document Everything!
2. Automate Ingelligently
3. Share
4. Don't Panic!
5. Focus Your Efforts
6. Invest In Yourself
7. Diversify
8. Develop Business Acumen

What do you think? Did I miss anything important?

P.S. Just a reminder that I will be presenting a webinar on assuring DB2 recoverability with my colleague, Michael Figaro, this Thursday, October 23, 2008 at 10:30 Central time. If you are at all interested in the topic, be sure to register today - and attend this Thursday!

Read More

A Perfect Storm?

There is something of a perfect storm brewing in the world of data today. The world is becoming more automated, more connected, more wireless, and more complex. The next wave of database administration is intelligent automation. I refer to this as implementing software scrubbing bubbles that “work hard, so you don’t have to.” (Remember that commercial!)

As more of the tasks required of DBAs become more automated, the DBA will be freed to expand into other areas. So one front on this storm is the autonomic computing initiatives that automate DBA tasks. At the same time, IT professionals are being asked to know more about the business instead of just knowing the technology. So DBAs need to understand the business purpose and definition of the data they manage, as well as the technological underpinnings of the DBMS. The driving force here is predominantly regulatory compliance. This second front of the perfect storm will cause DBAs to work more closely with metadata to drive database archiving, data auditing, and security to ensure their organization complies with regulations like Sarbanes-Oxley, HIPAA, and others.

Regarding the wireless aspect of things, pervasive devices (PDA, handhelds, cell phones, etc.) will increasingly interact with database systems. DBAs will need to get involved there to ensure successful data synchronization. And database systems will work with disconnected data seamlessly, such as data generated by RFID tags.

Yet another big database trend is technology "suck." By that I mean the DBMS is as it sucks up technologies and functions that previously required you to purchase separate software. Remember when the DBMS had no ETL or OLAP functionality? Those days are gone. This will continue as the DBMS adds capabilities to tackle more and more IT tasks.

Another trend impacting DBAs will be a change in some of their roles as more and more of the recent DBMS features actually start being used in more production systems.

The net result of this perfect storm of changes is that data professionals are absolutely being required to do more... sometimes with less (less time, less money, less staff, etc.)

If you know the technology but are then required to know the business, this is doing more – much more. But the technology, in many cases, is also expanding. For example, DB2 9 incorporates native XML. Most DBAs are not XML savvy, but increasingly they will have to learn more about XML as the DBMS technology expands. And this is just one example.

Additionally, data is growing at an ever-increasing rate. Every year the amount of data under management increases (some analysts peg the compound annual rate of data growth at 125%) and in many cases the number of DBAs to manage that growing data is not increasing, and indeed, could be decreasing.

And, budgetary limitations can cause DBAs to have to do more work, to more data, with less resources. When a company reduces budget but demands more work, automation is an absolute necessity. Turning work over to the computer can help (although it is unlikely to solve every administrative issue). Sometimes IT professionals fight against the very thing they excel in – that is, automating work. If you think about it, every computer program is written to automate someone’s work – the write (word processing), the accountant (financials, payroll, spreadsheets), and so on. This automation did not put the executives whose work was automated out of a job; instead it made them more efficient. Yet, for some reason, there is a notion in the IT industry that automating IT tasks will eliminate jobs. You cannot automate a DBA out of existence – but you can make that DBA’s job more effective and efficient with DBA tools and autonomic computing.

And the sad truth of the matter is that there is still a LOT more than can, and should, be done in most companies. We can start with better automation of DBA tasks, but we shouldn't stop there!

Corporate governance is hot – that is, technologies to help companies comply with governmental regulations. Software to enable archiving for long-term data retention, auditing to determine who did what to which piece of data, and security to better protect data are all hot data technologies right now. But database security need to be improve and technologies for securing and auditing data need to be more pervasively implemented.

Metadata is increasing in importance. As data professionals really begin to meld together technology and business, they find that metadata is imperative. But most organizations do not have a metadata repository fully-populated and up-to-date that acts as a lexicon for business data.

And finally, something that isn’t nearly hot enough is data quality and integrity. Tools, processes, and database options that can be used to make data more accurate and reliable are not implemented appropriately with any regularity. So the data stored in our corporate databases is suspect. According to Thomas C. Redman, data quality guru, poor data quality costs the typical company at least ten percent (10%) of revenue. That is a significant cost! Data quality is generally bad in most organizations – and more needs to be done to address that problem.

With all of these thoughts in mind, are you prepared to weather this perfect storm?

Read More