Optimizing fab performance with MES-database active archiving
04/01/2003
With increasing trends toward more fab automation, individual wafer tracking, and manufacturing accountability has come an enormous increase in databases associated with manufacturing execution software. The quick fixes, including more computer hardware, redesigning database structures, and saving flat files to tape, are often fraught with risks. Newly introduced to semiconductor manufacturing, and successfully used in other industries, active archiving is the more logical approach to lessening the burden that databases put on fab productivity.
Increasingly, it is a challenge to manage the large volumes of data being captured and stored inside a fab's manufacturing execution software (MES) database. Consider this simple example: For a set of 100 tools, the computerized maintenance management system (CMMS) creates 1–2GB of data each year; 1000 tools generate 10–20GB of data each year. If the facility is required to maintain data online for five years, the volume grows to 50–100GB of data just for the CMMS application alone.
When you add data collected and stored for all lot movements, engineering data collection, recipes, process plans, steps and operations, product-to-process associations, equipment automation, statistical process control, and many other applications critical to fab operation, the volume could quickly amount to gigabytes of data being collected and stored daily.
The data provide the necessary operational information to maximize production efficiency. However, most of these data accumulate in MES databases, impeding factory floor productivity by degrading manufacturing performance and limiting availability because of slower system response. While equipment uptime and response time are critical to maximizing fab output, when data growth occurs inside the MES database, the search algorithm and operator-display refresh rate take longer, delaying lot movements. This can reduce the productive time of a very expensive process tool.
Slower response times can cost semiconductor manufacturers several thousand dollars per hour, resulting from lost productivity and equipment depreciation. For this reason, it is not uncommon for semiconductor manufacturers to request a response time <10 sec on key transactions that assist in the lot movements. Similarly, 99.999% availability is a standard requirement on a request for proposal for an MES system.
Database growth accelerating
As the level of fab automation increases, data accumulation has risen proportionally. Compared to conventional lot-based tracking, highly automated 200mm and new 300mm fabs require wafer and even component level tracking that collects substantially more data.
So far, the new 300mm fabs, which were designed for starting 30,000 to 40,000 wafers/month, are not yet running anywhere near full capacity. This means that as the semiconductor industry emerges from the current downturn and these fabs ramp production toward full capacity, data volumes being collected on a daily basis will accelerate in proportion to ramp rate. In addition, with new fabs, initially significantly more data are collected before a given process stabilizes. Then, as these advanced fabs mature and as device linewidths continue to shrink, more complex functionality is added to computer integrated manufacturing (CIM) system capabilities, requiring additional data to be captured and stored online.
 Figure 1. MES archived and production data may be accessed from applications, third party tools, and archive administrative consoles. |
null
Although a full fab MES database starts at about 15GB, it can grow into the terabyte range in just a few years. Large volumes of transactional and historical data accumulate quickly, particularly in areas such as work-in-process traceability, equipment management, and process data collection. At some point, slower response time causes the estimated production performance to fall short of actual performance. Even adding hardware may not be enough to ramp up production as quickly as planned.
Data retention requirements are another key reason why database growth is accelerating. Semiconductor manufacturers are required to maintain historical data for years for business and regulatory compliance. The number of years varies by manufacturer, end products, and customers. For example, for most fabs, the minimum data retention standard is five years. Fabs serving the automotive industry must retain data for at least seven years. Fabs serving highly regulated products and industries, such as medical devices, aerospace, and defense, might need to retain data for 25 years.
The wrong fixes
The challenge within wafer processing is to sustain operational efficiency as databases expand. As a short-term fix, some semiconductor manufacturers are spending millions of dollars on hardware upgrades and maintenance fees. The rationale is that adding faster, more powerful processors and storage can speed access to information. But as MES databases continue to grow, upgrades are needed more often. Today's budget constraints are compelling organizations to find an alternative to this expensive, short-term approach.
Intensive database tuning is another approach to improving performance. Database administrators can reorganize a database more often, add indexes, implement partitioning, or even redesign parts of the database (although this last option may create additional application maintenance costs). As a given database continues to grow, however, such efforts yield diminishing returns and only postpone the inevitable need for a long-term solution.
 Figure 2. Active archiving precisely archives precise subsets of related data and preserves the business connection of the data (i.e., metadata). |
null
Some wafer fabs are pruning data volumes by purging or unloading data as flat files stored on tape. These tools are designed to streamline a database to improve response time on the manufacturing floor. This approach carries a high degree of risk, though, including losing access to data, corrupting the operational database, and damaging data integrity. Responding to audits, lawsuits, or government or security investigations, as well as answering a manufacturer's questions, may require access to purged data.
For example, let's assume Fab A manufactures semiconductors that are installed into sensors inside an automobile. If there is a field failure on this part and the manufacturer decides to recall it, then Fab A is required to quickly analyze the historical manufacturing database and trace the origins of the failed part in order to:
- assess the root cause of the failure;
- determine when that failure was introduced into the manufacturing cycle;
- determine other parts that may be affected by the failure;
- determine the full genealogy of the product to determine the remaining life cycle of the part; and
- determine which automobile manufacturers bought that part, and to which plant it was shipped.
Having completed this analysis, the company can now advise the affected manufacturers of the root cause and the potential impact of the failure, and help identify which models, from which assembly plant, must be recalled or retrofitted.
What if this semiconductor company deleted data records from their online database as a normal practice, and they had to reconstruct the full analysis database from weekly or monthly incremental backups?
What if this field failure occurred in a semiconductor that is a component in a life sciences or medical device product implanted inside humans? If the data have been purged, lost, or are irretrievable, the task of responding to questions can be insurmountable.
Despite the need to reduce MES database size, some semiconductor manufacturers have been reluctant to remove data from production databases because of the inherent difficulties and more important concerns about losing critical data. Accidentally deleting essential data could bring mission-critical systems to a halt. This concern is particularly valid when data are stored in an MES relational database, where data are normalized across hundreds of tables, interconnected by hundreds of relationships. To complicate matters further, these relationships may be managed by the application rather than by database-enforced referential integrity (RI) rules, which protect parent-child data relationships and prevent the possibility of creating orphan data. No one wants to risk breaking a database that works, particularly when it supports a mission-critical application.
It may seem safer to allow continued database growth rather than risk losing access to data forever —until the operational efficiency of the factory floor begins to erode.
The active-archiving solution
Increasingly, active archiving manages database growth to optimize the performance that MES solutions were designed to deliver (Fig. 1). Active archiving safely archives and removes precise subsets of relational data from MES databases.
The active-archiving process preserves not only the data, but also the metadata ("data about the data"), such as column attributes, table attributes, and relational attributes (Fig. 2). Preserving metadata is key to enabling active archiving to recreate and restore data that matches the attributes of the database from which it was archived. This ensures that archived data can always be accessed and restored if needed, even if the data model has changed.
An active-archiving software suite from Princeton Softech supports enterprise requirements across leading database management systems and across OS/390, NT, and UNIX operating platforms. These software solutions can be deployed with any type of custom-developed or packaged application. In 2002, the company was successful in introducing active archiving to the semiconductor industry through an alliance with Brooks-PRI Automation.
In a fab environment, Princeton Softech's Archive for Servers software is installed on a UNIX or NT Server. The software can be loaded on the same server as the MES database or on a different server. After the software is operational, active archiving is accomplished in an effective two-step process.
First, an access definition is created to define the sets of related data to archive, including the tables and relationships needed to maintain referential integrity. Regardless of the complexity of the data model, active archiving creates referentially intact subsets of related data by: processing recursive or complex relationship cycles; .handling relationships based on partial column values, concatenated columns, or data-driven relationships; and traversing relationships from parent to a child table and then from a child table to a second, different parent table and so on.
Second, the access definition is used to copy the data to an archive file. Active archiving provides users with a choice of several powerful, yet safe, relational delete capabilities. Users can create an archive and then remove data immediately following the archive processing, or they can archive the data and defer the delete to a later time, providing another level of verification before physically removing data from the production database. Active archiving's ability to delete data selectively is key because it allows users to retain relevant metadata (Fig. 2) in the production database and remove only specific subsets — at the row level. Without selective delete, a user is forced to write custom programs to pinpoint the data to be deleted.
After data is archived and removed from production, it can be dynamically compressed and stored on the most convenient and cost-effective medium — file server, EMC Centera, CD, or tape. Regardless of the storage medium, archived data can be easily accessed using a comprehensive set of capabilities ranging from built-in viewing and reporting functions to programmatic data access. A direct browse capability eliminates the need to restore data to another database before searching. When browsing, users can join related tables to display the data in their relational context.
The ability to browse archived data directly often eliminates any need to restore it, but when data must be restored, a selective restore capability is crucial. It should not be necessary to restore huge amounts of data for the sake of only a few rows. Active archiving has a selective restore capability that allows users to easily identify and restore a referentially intact subset of archived data in a single step.
For example, suppose that lot No. 45 was identified as having low quality and it was necessary to find and restore all of the data about that lot. With active archiving, just lot No. 45's data can be accessed and restored, without restoring the entire archive.
Active archiving enables best practices for MES data archiving in fab environments because it first preserves the business context of the data for even the most complex relational data model. It also allows for deleting related rows of data selectively and for selectively restoring subsets of related data (e.g., one lot, not all lots from a specific archive). Active archiving also compresses archived data and saves it to reporting tables, a history database, or flat off-line media, while preserving the referential integrity of the data; and it provides for fast, easy access to archived data on demand.
Active-archiving technology is unique because it understands and intelligently processes data relationships, regardless of their complexity. This understanding is obtained from a database catalog and from supplemental relationships defined to a shared directory. The technology makes it easy to define, retrieve, and manipulate complex, referentially intact sets of data from multiple tables, without writing low-level extract routines. These capabilities allow users to target the right subset of relational data to archive, referentially intact and complete, with 100% accuracy every time.
Optimizing MES performance
By separating and removing rarely accessed data, many fabs can safely reduce the size of overloaded MES databases by 20–30%, perhaps more, even with the initial archive. Rolling archive processing performed quarterly or annually during the maintenance shutdown window will maintain the MES database at an optimal size to support factory production goals. Significant improvements in application performance and availability are realized immediately because there is much less data for the MES application to process. As a result, fab throughput is improved and factory floor productivity is not compromised. Expensive upgrades and maintenance fees to maintain service levels can be deferred or eliminated, reducing operating costs.
By keeping MES databases streamlined, active archiving also significantly reduces time and resources needed to rebuild a database during planned or unplanned downtime. The recovery process can be staged by recovering mission-critical data first and nonessential data later, if necessary. This strategy enables IT organizations to maintain databases at a size that allows them to meet service level agreements. Software upgrades routine backup and restore procedures will also take much less time.
Implementing active archiving
A typical fab will retain one or two years of data online in its MES application. Multiple criteria can be used to select the data to archive, including date, active lot number, active lot status, active customer returns and inquiries, quality assurance, and process improvement. Depending on the level of process improvement, the criteria may become more sophisticated.
In some cases, MES applications maintain data in a live production database and a historical database, which is highly de-normalized for reporting purposes. Active archiving archives and removes data as configurable "snapshots" from both systems. Archiving data from the production database improves MES application performance. Archiving data from the historical database speeds ad hoc queries, report generation, and decision support.
Archived data can be easily accessed using a comprehensive set of capabilities, ranging from built-in viewing and reporting functions to programmatic data access. In all instances, file access is performed through existing virtualization and pooling configurations. Active archiving works within the framework of the fab's storage area network environment to optimize storage utilization.
Jim Lee, Princeton Softech, Princeton, New Jersey
Bharat Nair, Brooks PRI-Automation, Chelmsford, Massachusetts
Acknowledgments
Archive for Servers is a trademark of Princeton Softech. EMC Centera is a trademark of EMC Corp.
Jim Lee is VP of product marketing at Princeton Softech, 111 Campus Dr., Princeton, NJ 08540-6400; ph 609/627-5500, fax 609/627-7799, [email protected].
Bharat Nair is director of consulting practice and business development for the MES Business Unit at Brooks-PRI Automation.