PLM System

1. PLM System Solution By Wei Liu

2. Methuen Lawrence Andover Colorado Springs Rochester San Jose Korea Shanghai Nogales Mexico Plano Shenzhen Taiwan HK Worldwide Community

3. PLM Twins Servers DOC APP LabView / CAD

4. System Layers Databases Scheduled tasks IIS Antivirus PLM App SQL Server 2005 Operating System Hardware

5. System Tiers CLIENT MACHINE SERVER MACHINE Oracle IIS PLM Web PLM Desktop PLM Web Services Administrator OmniOracleIF Designer Web services ODBC SqlClient OmniBOM PLM OmniDocs Transfer Reporter

6. Data Providers used by PLM ODBC - System.Data.Odbc OLE DB - System.Data.Oledb SQL Server - System.Data.SqlClient Refer to: web.config in PLMOracleIF and PLMWebServices

7. Work Flow and Comparison .NET Framework Data Provider for SQL Server .NET Framework Data Provider for OLE DB OLE DB Service Component OLE DB Provider The key difference is that the SQL Server provider bypasses the OLE DB layer and thus gives numerous performance benefits

8. “Using Data Provider for Oracle to Improve Performance” Where Is the Performance Boost Coming From? The OLE DB client classes are designed to provide a database-independent layer for accessing generic databases. While the value of a generic layer is nearly universal access, it is difficult to deliver database-specific optimizations in this generic access layer. Also, the OLE DB layer is implemented as a COM library, so the System.Data.Oledb namespace works through COM interop. To achieve the significant performance boost described here, the .NET Framework Data Provider for Oracle avoids the cost of COM interop, and also employs Oracle-specific optimizations.

9. Provider for OLE DB vs. provider for Oracle This graph shows that the .NET Framework Data Provider scales much better as multiple CPUs are added to the system

10. Security Concern People had the security concern (Case# 200807-5327) for PLM 4.0: “PLM Web Services allows access to reports and documents from a browser without user authentication.” One possible solution is to add a single sign-on page. As alternatives, the following two approaches can be considered:

11. Process Model 1

12. Process Model 2

13. Exception People complain: “The licenses are not getting released, we can not access!” “Don’t worry, let’s take a look.”. “…It’s an unknown exception which happens irregularly”…

14. Exception handling: An exception means an instance or case not conforming to the general rule, pattern or judgment. In software engineering, it means event resulted from incorrect data type, wrong format, data out of range, volume beyond the scope, input/out problem, connection failure/interruption or illegal operation, etc. In any case, the system should catch it and handle it during the ENTIRE process, covering multi-directional workflow. But a lot of designers/developers overlook and disregard exception handling, or only handle few specific exception(s), provided that users exactly follow requirements/procedures, business transactions are always within scope/limitation, no side effects and no network issue. When the unnamed exceptions occur or unknown exceptions happen, the process terminates or the system hangs, freezes, or has no response without log and error message To handle exceptions, the programs have to catch them first, catch all of them, especially unknown exceptions, provided that the users/clients are in an environment of full tolerance. Generally there are 3 ways to handle exception: solve it, or ignore it, or terminate the process (but with notification / tracing records). Our approach for this case is the second one.

15. The issue gets solved after we put a “out catch-handler" on observation since 11/13/2008: try{ }catch (Exception e){ handler_template; }

16. System Freezing The problem was raised when resources were locked, could not be freed or released as expected, new resource-intensive queries and time-consuming transactions continued coming in, pushing the system to the edges: CPU bottleneck, memory bottleneck, input/output bottleneck. We do not have fix or patch from vendor, nor effective solution from Microsoft (other companies have exactly same problem, and don’t solve it yet after installing MS Service Pack 1 and Service Pack 2). … … “Here is the story and a workaround:” http://www.acsaohio.org/will/docs/plm_improvement.doc

17. Performance Issue

18. Index Indexes are sort of cornerstone topic in SQL Server or any other database environment, and are not some thing to be taken lightly. They can drive your performance successes, but they can also drive your performance failure. There are huge tables(for example, table PartsList has 1,222,933 rows) in current PLM production DBs, but no index is defined by software vendor, the SQL Server retrieves the requested data by using TABLE SCAN(row by row) The case will be worse when tables continue growing day by day Indexs speed up performance when reading or searching or loading or reporting data. Indexes may slow down performance when inserting or deleting data (it needs also updating indexes B-Trees). This is a trade-off. Indexing is not difficult to implement, but needs very careful design based on functionality/usage, transaction, pros-cons balance, etc. In the Company, the packing sub-system (packing & shipping) has 27 tables with index and multiple indexes, and 15 tables without indexes; PLM system has no index in any table. The workaround here is to add index only on critical column of critical tables with MAX positive efficiency vs. MIN negative impact.

19. Index Experiment • Clustered Index (with ASC order) on PartsList and Entry. • Observation/review(before indexing): • *tested on PLM Desktop resided in the same box as server (without networking) • Testing Results(after indexing): • *tested on PLM Desktop resided in the same box as server (without networking)

20. Design Issue - Business Logic A. Incorrect design (open part) Networking Turns:791 B. Correct design Networking One request One response

21. Since mid of 1990’s, most applications are three-tiers (or more tiers) applications consisting of: Presentation Tier This is the topmost level of the application. The presentation tier displays information related to such services as browsing, searching, opening, reporting contents. It communicates with other tiers by outputting results to the user interface. Business Logic Tier The logic tier is pulled out from the presentation tier and, as its own layer, it controls an application’s functionality by performing detailed processing. It is deployed as middle tier in server side. Data Tier This tier consists of Database Server(s). Here information is stored, retrieved or manipulated.

22. Workaround for Design Issue Before PLMSoft moves Business Logic to the server, CITRIX can be seen as a workaround (Microsoft Windows Terminal Services is the underlying platform for Citrix XenApp). Here is how it works(video): http://www.citrix.com/English/ps2/products/feature.asp?contentID=1684340 The idea is to “Separate where applications are used from where they run” and to reduce “processing round trips on network”: http://www.citrix.com/English/ps2/products/subfeature.asp?contentID=164120

23. DB Backup Process 1. Generate backups 2. copy TSM Storage Living Data Logs