TECH-ASK-EXPERTS

Members of the Teradata Service Focus Team work with Teradata to address your support-related issues.

Tech2Tech

Ask the Experts

Take It
From Us

9 tips for a smooth upgrade.

by the Teradata Service Focus Team

Whether they're called "minor" or "major," all database software upgrades must be completed hitch-free and be smoothly integrated with client software and third-party applications.

As members of the Service Focus Team (SFT), a committee that's part of the PARTNERS User Group, we work closely with Teradata on issues related to support services and other areas that fall beyond the scope of product enhancements. From our collective experience we've developed a list of nine tips that we hope you will find helpful in your next upgrade endeavor.

1. GET ORGANIZED

Any successful project requires strong and focused project management, and a database software upgrade is no different. First, assign a project manager to:

  • Coordinate all tasks through completion
  • Ensure schedules are set and followed
  • Keep all team members communicating and up to date on developments

Next, create a team with DBAs, operations leaders, developers and others to ensure cross-team participation. A kickoff meeting and regular status updates will help keep project members informed and, consequently, more focused and helpful. Also, consider including your Teradata customer service representative (CSR) in the team meetings for another perspective.

As an ongoing resource for current and future projects, an upgrade-plan template can be a useful tool for tracking changes to your system. List in this document items to remember regarding specific circumstances and lessons learned.

Did you know?

Upgrades include database software with major, minor or maintenance changes. A major database software upgrade is moving from one database version to another and is represented by the software version numbering format: a.b.c.d, with "a" being a major release of the database. For instance, moving from Teradata 12 to Teradata 13 is a major upgrade.

2. PLAN AHEAD

For in-depth information on the upgrade, research sources such as PARTNERS presentations, reports that compare tools and outline upgrade benefits and risks, SQL differences and, of course, the new version manuals. Then, before starting the project, complete the following steps to compare the new software version with your current one to identify the significant changes and improvements:

  • Identify dependent applications. Study up on all of the Teradata client applications in use and investigate whether they are compatible with the new database version. Any third-party applications that need to be brought to the next level must be tested once the new version is implemented. The Teradata upgrade might need to be delayed until any critical applications can be made compatible.
  • Define benchmarks. Identify at least 20 queries to use for before-and-after benchmarking comparisons. Types of SQL to include are expedited and canary queries, heavy-hitting and frequently run queries, extract, transform and load (ETL), and some more complex queries that have been problematic. Also benchmark tools such as FastLoad, FastExport, MultiLoad, Teradata TPump, archive and restore jobs.

For an accurate comparison, run the queries when the system is quiet before and after the upgrade and long enough to retrieve comparable runtime figures. Keep in mind they might run faster than usual on a quiet system. Conversely, to prevent later surprises, test mixed workloads when the system is saturated to ensure they perform as expected.

Use Database Query Log (DBQL) to capture and collect details, explain statements output and elapsed times. Keep a backup of DBQL or whatever table you use to store this information in order to compare before and after performance.

Besides using these comparisons for testing purposes, the results can be presented to management to make the case for the upgrade's return on investment (ROI).

  • Conduct regression testing. While the comparison runs can be completed during off-peak times on the production system, it is advantageous to use a sandbox, if available, for regression testing. Use the benchmark queries mentioned earlier and include test queries from critical applications.

The utility Teradata System Emulation Tool will replicate the production test bed, including statistics, into the test system. This will ensure the same explain statements will be created in test as well as in production. If testing with production data isn't possible, run explains on the critical queries to check for syntax errors and parser problems, such as segment violations.

SIDEBAR: Pre-upgrade checklist

Before the Teradata software is upgraded, you will typically need to:

  • Open change controls—Teradata and local.
  • Clean reserved words.
  • Reboot nodes one week before upgrade.
  • Create backups of DBS control settings, SysSecDefaults, ResUsage, Teradata Dynamic Workload Manager, XCTL, Priority Scheduler settings, etc., in case a full recovery is necessary.
  • Run full backups of important data: All-AMP backups are preferred over clustered backups if the AMP configuration will be different after the upgrade. Keep in mind that cluster backup data sets must be restored serially to a different AMP configuration. An unreasonably long restore window may be required.
  • Run scandisk and checktable on the existing system in advance to allow time to address any problems discovered.
  • Clean leftover spool.
  • Generate DDL of hash indexes, join indexes, triggers and user-defined functions, then drop them before the upgrade.
  • Remove journal tables.
  • Disable DBQL, and offload to history repository.
  • Purge any unnecessary AMP-usage data.

3. COMMUNICATE

To keep users informed, consider publishing on the company's intranet site the project's progress and schedule. Another option is to present the new features in a class session.

Open dialogue is an opportunity to make users responsible for maintaining a clean data warehouse environment. During a system upgrade, for instance, users may be asked to drop unneeded objects from their personal databases and, more importantly, to back up their critical data.

4. CONTROL CHANGE

Establish a change control process to ensure modifications to the system and procedures are management-approved and introduced in a controlled and coordinated manner. Formal change control helps ensure that system upgrades have minimal negative impact on users and are considered a sound use of economic resources.

5. UPGRADE CLIENT SOFTWARE

Long before the Teradata software upgrade, the dependent client software must be identified. Work with your third-party software vendors to implement the required client-application updates.

Clients can interact with the Teradata Database platform using Teradata Tools and Utilities, which comprise the Teradata ODBC driver, SQL Assistant, Teradata Administrator (WinDDI) and BTEQ, among others. These tools might need to be updated before the database upgrade is implemented. Reference the Teradata Tools and Utilities Installation Guide for a complete list and detailed installation instructions.

SIDEBAR: Post-upgrade checklist

After the upgrade has been completed and validated by the Teradata customer service representative, you should do the following tasks:

  • Run checktable DBC only at level 3.
  • Run scandisk and checktable to verify that the file system is clean.
  • Check/reset Teradata Dynamic Workload Manager and Priority Scheduler settings, DBScontrol and XCTL.
  • Re-create journal tables, indexes, triggers and user-defined functions that were dropped before the upgrade.
  • Validate that statistics are refreshed.
  • Restart DBQL, access logging, system security, etc.
  • Restart the Teradata Manager server, if applicable.
  • Verify that the upgrade scripts have identified and recompiled any stored procedures from the Teradata Database; recompile and test any user-defined stored procedures.
  • Start and confirm Teradata Director Program, enable logons and notify users of system availability.
  • Restart the open systems backup application.

These are only some of the tasks that should be completed. Once all are finished, run and compare the pre- and post-upgrade benchmarks, then publish the results.

6. VERIFY BAR COMPATIBILITY

Your backup and recovery (BAR) software might need to be brought to a more current version of its own before the database upgrade can proceed. Common BAR software includes ARCMAIN on mainframes, and NetBackup (Symantec) and NetVault (BakBone) on open clients. Verify the BAR software compatibility with the database upgrade. If a new version of the BAR platform is needed, identify all system software levels that are used in the environment and make them current as well. Also verify that the operating system (OS) environment will continue to support the upgraded software.

If your organization has certain standards for software, make sure the new versions are permitted for use, or begin the process of certifying them as quickly as possible. Check that there are no licensing issues with upgrading to the new software version.

Plan enough time to implement the updates to your BAR software and schedule the process around any critical backup dates. Once it has been completed, document the new BAR software levels and test them again after the Teradata Database has been upgraded. During and after the process, keep track of patches that are released by Teradata that might fix newly discovered performance or connectivity issues.

7. PERFORM FINAL PREP

After the system has been readied and all necessary software and third-party upgrades have been completed, your DBA must perform these last-minute tasks:

  • Quiesce the system, abort remaining user sessions, back up DBC and bring down any Teradata Director Programs.
  • Shut down the Teradata Manager server and any open systems backup application (NetBackup, NetVault), and disable unnecessary user IDs.
  • Stop any job schedulers such as AutoSys, cron, etc.
  • Run checktable PENDINGOP to identify tables in a pending load status.
  • Drop all hash indexes, join indexes, triggers and user-defined functions.

    • 8. DOCUMENT EVERYTHING

    Soon after the procedure is complete, hold a final project meeting. Discuss the lessons learned and document the findings in your upgrade-plan template. When the next major upgrade is being planned, this information will be invaluable.

    9. WRAP IT UP

    Next comes the final, important step: Celebrate a successful project!

    Online Links

    Visit http://www.teradata.com/teradata-partners/committees/whitepapers.cfm to download the whitepaper "Customer Best Practices for Teradata Upgrades"