Recover or Replicate Using DSN1COPY

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.

SELECT SUBSTR(TP.DBNAME,1,8) AS DBNAME 
             , SUBSTR(TP.TSNAME,1,8) AS TSNAME 
             , TP.PARTITION                     
             , TP.FORMAT                        
FROM SYSIBM.SYSTABLEPART TP            
WHERE (TP.DBNAME = 'dbname’          
    AND TP.TSNAME = 'tsname')         

New in DB2 V9

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.

Recovery After Using RFTL

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:

DSNT404I
     SQLCODE = 20272, WARNING:  TABLE SPACE CSBPTS1A HAS BEEN
     CONVERTED TO USE TABLE-CONTROLLED PARTITIONING INSTEAD OF
     INDEX-CONTROLLED PARTITIONING, ADDITIONAL INFORMATION: *N

By issuing the –DISPLAY DB(*) SPACE(*) RESTRICT after the ALTER, you see the table space in REORGP status:

DSNT397I  -DB8G                                                         
NAME      TYPE PART STATUS      
--------   -------  -------- ----------------- ---------   
CSBPTS1A TS 0002 RW                                                   
    -THRU        0003                                                      
CSBPTS1A  TS     0004 RW,REORP                                             
CSBPTS1A  TS     0001 RW,REORP                                             
INVOICER  IX     0002 RW                                                   
    -THRU        0004                                                      
INVOICER  IX     0001 RW

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.

L# DBNAME    TSNAME    ICT  ICDATE  ICTIME  SHRL   LP        RBA       
---+-----------+--------------+-----+----------+----------+---------+----+--------------------
11 CSBPDAT   CSBPTS1A  W    080730   114821                  3      004CE8D6FD8D
10 CSBPDAT   CSBPTS1A  W    080730   114821                  4      004CE8D6FD8D
  9 CSBPDAT   CSBPTS1A  A     080730   114625                  4      004CE8D5F2BC
  8 CSBPDAT   CSBPTS1A  Q     080730   114419                  2      004CE8D5B473
  7 CSBPDAT   CSBPTS1A  Q     080730   114419                  1      004CE8D5B473
  6 CSBPDAT   CSBPTS1A  Q     080730   114419                  4      004CE8D5B473
  5 CSBPDAT   CSBPTS1A  Q     080730   114419                  3      004CE8D5B473
  4 CSBPDAT   CSBPTS1A  F     080730    114408   R            4      004CE8D54C8F
  3 CSBPDAT   CSBPTS1A  F     080730    114407   R            3      004CE8D42552
  2 CSBPDAT   CSBPTS1A  F     080730    114407   R            2      004CE8D2FE2D
  1 CSBPDAT   CSBPTS1A  F     080730    114406   R            1      004CE8D1D758

The ALTER rotate took physical partition 1 and made it logical partition 4. Now partition 2 becomes 1, 3 becomes 2, and 4 becomes 3.

Let's see if we can recover partition 2 back to the QUIESCE RBA of x'004CE8D5B473'. 

The answer is NO. The recover table space to RBA failed with these error messages:

DSNU050I    DSNUGUTC -  RECOVER TABLESPACE CSBPDAT.CSBPTS1A DSNUM 2 X'004CE8D5B473' REUSE
DSNU556I  -DB8G DSNUCASA - RECOVER CANNOT PROCEED FOR
                      TABLESPACE CSBPDAT.CSBPTS1A DSNUM 2 BECAUSE
                      A SYSIBM.SYSCOPY RECORD HAS BEEN ENCOUNTERED
                      WHICH HAS DBNAME=CSBPDAT TSNAME=CSBPTS1A DSNUM=2
                       ICTYPE=W STARTRBA=X'004CE8D6FD8D' LOWDSNUM=3
                       HIGHDSNUM=3         
DSNU500I    DSNUCBDR - RECOVERY COMPLETE, ELAPSED TIME=00:00:00               
DSNU012I    DSNUGBAC - UTILITY EXECUTION TERMINATED, HIGHEST
                      RETURN CODE=8    

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.

DSNT397I  -DB8G                                                          
NAME      TYPE PART STATUS            
--------------  ------- -------- ----------------- -------- --   
CSBPTS1A  TS     0002  RW                                                   
    -THRU        0004                                                      
CSBPTS1A  TS     0001  RW                                                   
INVOICER  IX     L*    RW

L# DBNAME    TSNAME      ICT  ICDATE  ICTIME  SHRL    LP        RBA       
---+--------------+---------------+-----+-----------+----------+---------+----+--------------------
9  CSBPDATT  CSBPTS1A  A      080731     094102                  4     004CE94C1174
8  CSBPDATT  CSBPTS1A  Q      080731     093859                  2     004CE94B279D
7  CSBPDATT  CSBPTS1A  Q      080731     093859                  1     004CE94B279D
6  CSBPDATT  CSBPTS1A  Q      080731     093859                  4     004CE94B279D
5  CSBPDATT  CSBPTS1A  Q      080731     093859                  3     004CE94B279D
4  CSBPDATT  CSBPTS1A  F       080731     093700   R            4     004CE949A651
3  CSBPDATT  CSBPTS1A  F       080731     093659   R            3     004CE94876BF
2  CSBPDATT  CSBPTS1A  F       080731     093658   R            2     004CE9474C3D
1  CSBPDATT  CSBPTS1A  F       080731     093657   R            1     004CE94624F0

A look at SYSCOPY tells you that the recovery RBA before the ALTER is x'004CE94B279D'. Run the RECOVER utility and you see these SYSOUT messages:

DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = CSBPDATT.RECVR                  
DSNUGTIS - PROCESSING SYSIN AS EBCDIC                                        
DSNUGUTC -  RECOVER TABLESPACE CSBPDATT.CSBPTS1A DSNUM 2 TORBA X'004CE94B279D'
DSNUCBAL - THE IMAGE COPY DATA SET CSBP.IC.CSBPDATT.CSBPTS1A.P2.T0935 WITH DAT
           IS PARTICIPATING IN RECOVERY OF TABLESPACE CSBPDATT.CSBPTS1A DSNUM
DSNUCBMD - MERGE STATISTICS FOR TABLESPACE CSBPDATT.CSBPTS1A DSNUM 2 -       
           NUMBER OF COPIES=1                                                
           NUMBER OF PAGES MERGED=3                                          
           ELAPSED TIME=00:00:02                                             
DB8G DSNUCARS - INDEX CSBPDATT.INVOICE_IX1 PARTITION 2 IS IN REBUILD PENDING ST
DB8G DSNUCALA - FAST LOG APPLY WAS NOT USED FOR RECOVERY                      
DSNUCBDR - LOG APPLY PHASE COMPLETE, ELAPSED TIME = 00:00:00                  
DSNUCBDR - RECOVERY COMPLETE, ELAPSED TIME=00:00:02                           
DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=4   

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.

DSNT360I  -DB8G ***********************************                         
DSNT361I  -DB8G *  DISPLAY DATABASE SUMMARY                                 
                *    GLOBAL                                                 
DSNT360I  -DB8G ***********************************                         
DSNT362I  -DB8G     DATABASE = CSBPDATT  STATUS = RW                        
                   DBD LENGTH = 4028                                        
DSNT397I  -DB8G                                                             
NAME      TYPE PART  STATUS            
----------  ----  ------- ------------      
CSBPTS1A  TS     0002 RW                                                      
    -THRU        0004                                                         
CSBPTS1A  TS     0001  RW                                                      
INVOICER  IX    L0002 RW,RBDP*                                                
INVOICER  IX    L0003 RW                                                      
    -THRU        0004                                                         
INVOICER IX    L0001 RW                                                      
******* DISPLAY OF DATABASE CSBPDATT ENDED   *******************      

Scenario 3: Recover a rotated partition

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:

DSNU000I    DSNUGUTC - OUTPUT START FOR UTILITY,
                       UTILID = CSBPDATT.RECVR       
DSNU1044I  DSNUGTIS - PROCESSING SYSIN AS EBCDIC                              
DSNU050I    DSNUGUTC -  RECOVER TABLESPACE CSBPDATT.CSBPTS1A
                      DSNUM 1 TORBA X'004CEB0ECA33' REUSE
DSNU556I  -DB8G DSNUCASA - RECOVER CANNOT PROCEED FOR
                      TABLESPACE CSBPDATT.CSBPTS1A DSNUM 1
                      BECAUSE A SYSIBM.SYSCOPY RECORD HAS BEEN
                      ENCOUNTERED WHICH HAS
                      DBNAME=CSBPDATT TSNAME=CSBPTS1A DSNUM=1 ICTYPE=A         
                      STARTRBA=X'004CEB0F0E66' LOWDSNUM=0 HIGHDSNUM=0         
DSNU500I    DSNUCBDR - RECOVERY COMPLETE, ELAPSED TIME=00:00:00                
DSNU012I    DSNUGBAC - UTILITY EXECUTION TERMINATED,
                      HIGHEST RETURN CODE=8      

I hope you find this information helpful. Please post questions and comments, and thanks to the readers who've previously sent me technical questions like this.

Mainframe Trends in Application Development

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.

Data Studio features integrated pureXML and pureQuery support. The use of XML is already common in the Linux, Unix, Windows (LUW) world, and, with DB2 9 for z/OS, XML is becoming more prominent on the mainframe. "The DB2 9 pureXML Guide," an IBM Redbook, and "IBM pureXML for SOA: Unlocking the Business Value of Information," a ZDNet whitepaper (registration required), offer technical background.

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.

Mainframe Career Trends in the Age of Outsourcing

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.