October 2014

August 2008

August 26, 2008

DSN1COPY is used to copy VSAM, sequential or image copy data sets. As a stand-alone utility, it can function even when DB2 is down. It's most commonly used to:

Recover a table or index space using an image copy that's not registered in SYSCOPY.

Copy from one subsystem to another, usually in support of development projects.

Using DSN1COPY to restore and/or copy data can be disastrous. Because DSN1COPY output is a page-for-page copy of the source, the source table space, table and index definitions must be identical to the target table space, table and index. If it isn't, you'll probably receive an SQLCODE of -904 with a reason code of 00C90101. And then you'll likely be making a support call to IBM.

Early in my DBA career I backed up a simple table space used in production. Then I dropped the table and recreated it in a segmented table space. I don’t recall the details, but I ended up performing a fallback and copying that image copy into the table space. The -904 resource unavailable with 00C90101 reason code messages immediately followed. This was at 1 a.m. on a Sunday, and I had a team of developers waiting to conduct testing.

I quickly made a severity 1 call to IBM to get our system back. The support rep told me to never, ever, use DSN1COPY to copy a simple table space to a segmented table space. On the second try, I dropped the segmented table space and recreated it as a simple table space. Now the DSN1COPY restore worked and the developer testing continued without any further problems.

I learned the hard way. Now I make sure I practice recovery scenarios and have procedures in place before I need to implement them in a live production situation.

Replicating from DB2 V8 to DB2 9: Reorder Row Format (subhead)I know many of you have production jobs in place that are used to copy production data to your test system. You may not be aware that once you go to DB2 V9 on your test systems, the data format may change and not match your production system. If you drop and recreate a table space or run a REORG on the table space, the data may be in a reorder row format.

After running the following query on the DB2 V9 system, the format will be "R" if the data is in a reorder row format. If this is the case, you should use UNLOAD/LOAD instead of DSN1COPY. I actually recommend UNLOAD/LOAD over DSN1COPY in general because I'd rather have jobs run a little slower than have developers waiting for a restore due to changes in the data structure.

With the most recent DB2 release, the biggest DSN1COPY-related change that I can see is in the number of tables in a table space that can be copied. If you use vendor products like PeopleSoft, you may have bumped up against IBM's limits in a segmented table space. (I remember some of the delivered PeopleSoft table spaces containing 1,500-plus tables.) For instance, with DB2 V8 (or by using APAR PK05758 in DB2 V7), the table limit was 1,000. But with DB2 V9, the limit is now 10,000 tables.

For More on DSN1COPY

Remember that DSN1COPY can be useful for replicating or recovering data. However, it's sensitive to any kind of structure change. Read the DB2 9 for z/OS Utility Guide and Reference (SC18-9855) to better understand all of the parameters and restrictions.

The guide also contains some sample queries used to verify source and target definitions.

August 19, 2008

I’ve written several articles about how to design systems that can store large volumes of data and how to take advantage of new DB2 V9 features like alter-table-rotate-first-to-last (RFTL). Two of my related articles from IBM Systems Magazine, Mainframe edition are here and here.

As a DBA it's important to understand your backup and recovery options when using REORG or other features that may change your data. The same applies when cataloging meta data changes through features like RFTL. RFTL changes the meta data stored in the DB2 catalog to associate a logical partition with a physical partition. It also modifies the limit key values that determine what keys can be stored in a given partition. Because of the logical-to-physical partition and limit key changes, determining how to recover to a given point in time gets tricky.

A reader recently asked me if it's possible to recover to a point in time before the RFTL operation. Yes and no. You cannot recover to a point in time before the RFTL if you start with an index-controlled partitioned table space. However, if you start with a table-controlled partitioned table space, you can recover a partition that's not involved in the RFTL operation. But you cannot recover the partition that was rotated back to a point in time prior to the rotation.

Let's consider three scenarios. The first is a recovery when starting with an index-controlled table space. The second and third scenarios use a table-controlled partitioned table space. Scenario 2 recovers a partition to a point in time prior to the last rotation, but the partition being recovered hasn't been rotated. In scenario 3, I provide the message you'd see if you pick the rotated partition instead of the non-rotated partition from scenario 2.

Scenario 1: Index-controlled partitioned table space recovery

Before starting the rotation, take a complete set of image copies and run the QUIESCE utility to get a to point in time relative byte address (RBA) to recover to if you have problems.

Then issue the ALTER DDL to rotate the first partition to the last:

ALTER TABLE CSBPDAT.INVOICE ROTATE PARTITION FIRST TO LAST ENDING AT ('2008-03-31') RESET

Notice the message produced by the ALTER statement, since this is an index-controlled partitioned table space:

Looking at the recovery information in SYSIBM.SYSCOPY:Line 1-4 are entries for full imacopy taken before the alter RFTL.Line 5-8 are entries for a quiesce to establish a common recovery point.Line 9 is the entry generated by the ALTER RFTL.Line 10-11 are entries generated by the REORG to remove the REORGP status.

So with index-controlled partitioning, you cannot recover any partition to a point in time prior to the ALTER. The best thing to do is convert to table-controlled prior to using the ALTER RFTL statement.

Scenario 2: Recover non-rotated partition

This scenario takes a table-controlled partitioned table space and recovers a partition that isn't involved in the rotation back to a point prior to the last rotation.

Similarly to the first scenario, before starting the rotation, take a complete backup of all the partitions and run QUIESCE. Then issue the ALTER DDL command to rotate the first partition to the last:

ALTER TABLE CSBPDATD.INVOICE ROTATE PARTITION FIRST TO LAST ENDING AT ('2008-03-31') RESET

Notice there are no conversion messages. And if you display the status of the table space, you don't see objects being restricted as they are with the index-controlled scenario.

The return code of 4 indicates a warning. Look closely at the above messages -- the indexes have been put into a rebuild pending status.

It's always a good idea to display the database status after completing the alter operation. As you can see in the display output below, index space INVOICER logical part 2 is in RBDP status. This is because DB2 needs to rebuild logical partition 2 with the keys of what you just restored.

The purpose here is to show that you cannot recover a rotated partition back to a point in time prior to the rotation. The image copy taken before the rotation contains data that's no longer valid for the partition since the limit keys have changed. This message confirms that partition 1 cannot be recovered because of the ICTYPE=A row found in SYSCOPY:

August 12, 2008

In last week's blog entry, I wrote about outsourcing in IT and the future of the mainframe platform. While these topics are understandably a concern for mainframe professionals, not all of the news is dire. IBM continues to invest in mainframe technology and work with colleges and universities to provide mainframe-centric curriculum to IT students.

This week I'll cover some other trends related to application development--and personal development.

Technology Needs for z/OS

The current trend in application development is to write applications in Java and run them as Web services. However, that doesn't speak to the untold number of legacy applications that run core business processes for companies of all kinds. IBM doesn't believe that rewriting 30 years of COBOL, JCL, DB2 applications is smart or practical. A better solution is to modernize these legacy applications to run as Web services using SOA.

Thanks to modernization, companies don't have to rewrite their legacy COBOL applications in Java. The modernization trend is also a huge positive for mainframe professionals, who are needed to support of these enterprise projects that bridge the technology gap between z/OS and Linux, UNIX and Windows developers.

New AD Tools

IBM Data Studio--This is a strategic tool to deliver an integrated, modular data management environment to design, develop, operate, optimize and govern database and data-driven applications throughout the lifecycle.

The development and performance problem of accessing DB2 data with Java applications has greatly been improved with pureQuery. For more, see "Understanding pureQuery" part 1 and part 2 from IBM developerWorks.

Free development tools--Java, XML and Web development are here today on System z with Linux and z/OS. While IBM is playing catch-up in its support of this technology on z/OS, the company has become very aggressive in the marketplace by providing free DB2 and SOA development tools like DB2 Express-C and Data Studio Developer.

Note: Data Studio is available at no charge, but Data Studio Developer includes some features that must be purchased. These features are available for a 31-day trial. Once the trial ends, the product will continue to work without some of these extended features. For learning purposes, the free version offers everything you need.

What You Can Do

Even in the age of outsourcing, professionals with mainframe skills remain valuable in the IT world. Remember too that IBM continues to invest in the mainframe, and that U.S. IT workers have advocates on Capitol Hill. Based on these facts, I believe that future prospects for those who develop and support applications running on System z for both Linux and z/OS are actually very good.

That said, all jobs are temporary. And, especially in this recessionary economy, even if you have great skills, you may find yourself out of work at some point. That makes networking all the more important. Rely on your friends, coworkers, college contacts and social networking sites like Linkedin, Facebook, Plaxo and Twitter.

Another thing you can do as a mainframe professional is simply be willing to learn new things. Keeping up with trends and educating yourself on new technologies enhances your value to employers. Learning Java, XML and Linux will make you attractive to companies engaged in enterprise modernization projects on z/OS. And your years of experience working with TSO, CICS, COBOL and JCL will be invaluable as you work with Java and XML professionals who've never touched a mainframe.

August 05, 2008

Many in IT are worried about losing their jobs to foreign companies that provide cheap labor. As a consultant I've worked for many U.S. companies that have trimmed U.S. staff by moving the jobs to overseas outsourcing firms. I've even trained the people in these countries to perform the work that I was doing.

As a mainframe professional you've also probably seen jobs being lost to projects that are moving applications off the System z and z/OS platform to cheaper platforms like Linux, UNIX or Windows. In addition, companies are feeling the pressure to move to these other platforms because of the shortage of skilled people who know and understand z/OS, COBOL, DB2 or JCL. Because these servers are cheap, most IT professionals coming out of college and foreign countries only have Linux, UNIX and/or Windows experience using mySQL, SQL Server, Oracle and DB2 for LUW.

With many z/OS professionals reaching retirement age and leaving the workforce, the problem is becoming critical. The question in everybody's mind is will the mainframe die a slow death? Should the average mainframe professional in the U.S. worry about having a job in 10, five, or even two years from now?

Of course, no one has a crystal ball. But to try and answer these questions, we can look at trends in the market. For this week and next, I'll provide a brief overview of these trends.

Legislation

It's true: Many jobs have been lost and many people have had to change careers due to the large number of jobs being sent overseas. This trend has impacted all IT professionals on all platforms (z/OS, Linux, UNIX and Windows). But organizations like WashTech are helping with the fight to save IT jobs in the U.S. WashTech provides relevant information in this area, including steps to contact members of Congress.

Lower Cost of System z and DB2

Are companies moving away from the mainframe due to the cost? From the research I've read and the people I've talked to, I know that IBM is being aggressive in its efforts to keep the System z and z/OS financially attractive to customers. With new, specialized CPUs like zIIP (which offloads DB2 workload from the main CPU), zAAP (which runs Java and XML workloads) and zIFL (which runs Linux), IBM is reducing the total cost of ownership for running existing applications and providing incentive for new customers to move to the System z platform.

Another way IBM is keeping System z hardware attractive is by allowing the consolidation of application servers to the platform. This very good article on this topic highlights the trend of companies consolidating separate application servers back into a single System z machine, while improving total cost of ownership.

What this article doesn't make clear is that these servers are being consolidated to Linux running on the specialized zIFL processor.

IBM is also providing incentives to run applications on z/OS through reduced software pricing. An example is the new DB2 for z/OS Value Unit Edition, which has a one-time charge.

Educating the Next Generation

To address the lack of mainframe-specific curriculum within the computer science programs at our local colleges and universities, IBM has started the IBM Academic Initiative System z program.

This program provides a low-cost alternative to schools that can't afford to purchase a mainframe by allowing them to use a shared mainframe. The IBM z program Web site lists schools that are participating in the program and providing a comprehensive enterprise systems curriculum, and as well as those that are growing their program.

Next week I'll examine other trends relating to application development.

IBM Systems Magazine is a trademark of International Business Machines Corporation. The editorial content of IBM Systems Magazine is placed on this website by MSP TechMedia under license from International Business Machines Corporation.