Optimization Techniques Of Commercial Bank's Application

With the process of data centralize project of Chinese commercial bank, the data is collected to several data centers. The whole nation's transactions have to face the risk of block since the increasing of application transactions, which depends on the excellent performance of parallel online application access.The article aims on how to solve the problem of huge data parallel accessing then give the solution. The main part answers for the current commercial bank application version management's status quo and the system maintain, how to get the most least possibility to reduce the system problem and ensuring the uninterrupted of banking IT system service with some technique measures, which shows a excellent example of parallel process of huge data.This thesis introduce a method which use expert rule to intelligently, rapidly inspect the application efficiency of database access in batch mode. The inspection results are loaded into the table, and can be inquiry compatible. All of this make the work of DBA more efficiency and more meticulous. In the thesis, we define the changed SQL in the new version program as new SQL, the unchanged SQL in the new version program as old SQL. The method processes the new SQL and old SQL in two different way. It compares the access path of the old SQL made before the applications installation with that made after application installation. For all changed access path and the SQL, we store them into the table as records . Base on the algorithm of DB2 Optimizer, these changes are classified into several changing type, such as Tablespace scan, Non-matching index scan, Matching index scan, List prefetch, Order by sort. The records of different type are processed in different expert rule, depending on the changing type. The processing result can tell which access path of SQL is changing worse, display the filter rate of the index matching and the reason of the inefficiency, and so on. If the access paths change worse, it can create some batch script for rollback. For the new SQL, the method use different types of expert rule to inspect the access path, and judge the access paths are good or not. The method can process the programs SQL statements of the new release as the original one , in the case of program codes and SQL statements No are changed while SQL statements are not changed. For this commercial bank whose release thousands of programs in one release of each season, the method can reduce much workload of DBA. The method enable the DBA customize expert rule, which made the SQL inspection more flexible.The inspection is executed in the testing stage and product stage usually. In the testing stage, we emphasize on the data structure change will decrease the efficiency of each SQL related or not, and the SQL in new programs are efficient of not. The method process the SQL in many ways. Disassemble the SQL statements into parts and process separately; compare the consumption of SQL with the efficiency demand. All of the SQL with problems and the corresponding problem reasons are insert into result table. In the testing environment, the data is same as production environment. And then DBA can optimize these SQL with reference to the result table. After the release is installed in product environment or DB2 is upgraded, the method inspect the changed access paths of SQL statement, insert them into the result table. The DBA can inquiry the result table to find the critical changes of access paths. If there are some changes make the efficiency decrease, the method can create batch job to restore the access path.With the use of the method, we can largely reduce the occurrence of database efficiency problem, make the mainframe database-based application run steadily. There are several original point in the method, which can improve the optimize technique.