 Good morning, everyone. Thank you for participating very early in the morning. Did you enjoy your Singapore night? I'm having fun. Yesterday I went to Maraion Park with a co-worker. Well, I'm casting her from NTT Japan. The title of the presentation is Migration from Oracle to PostgreSQL, a problem and solutions. I hope you have a good time. So this slide shows the agenda of this presentation. First, I introduce myself. Next, I describe today's topic. That is the information of database migration. After that, I talk about what problems can occur in database migration projects. After that, I show the solutions for these problems using two open source OSS products. One is a scale code assessment program. And the other is a function library that bridges DBMS differences. Finally, I conclude my talk by summarizing. Okay. Again, my name is Kazuki Weher from Japan. I love traveling, not by car, but by bicycle. And I love taking photographs too. I like photography and landscape scenery and so on. I'm offering technical support and technical consultation of information systems of our customers. I sometimes perform functional verification and performance evaluation of IT products. So I'm especially interested in PostgreSQL, PgPool 2, Syroni 1 and so on. So I belong to NTT, Nippon Telegraph and Telecom Corporation. NTT is the biggest telecom company in Japan. What I'd like to say here is it is a really, really conservative company. So NTT OSS Center is offering a total support service such as support desk, introduction and maintenance OSS products to subsidiary companies. We also are developing OSS products and related tools within each community. The logos in this slide show roughly 60 OSS products we deal with. I'll talk about PostgreSQL. The PostgreSQL community says that it is the world's most advanced OSS DBMS. We basically agree with them. For almost 20 years, the community has continued improved futures and performance of the products. So now it is comparable to profitable and commercial products such as Oracle DB. In NTT group companies, we are operating about more than 600 systems using PostgreSQL. Okay, let's compare some interesting attributes of PostgreSQL 9.5 and Oracle 12. As you can see in this slide, the PostgreSQL can be compared favorably with Oracle DB. Okay, about today's topics. Today, I will not talk about how to migrate to Singapore. I love Singapore, of course, but it is not today's topic. Actually, the topic is what kind of problem may occur in the database migration project and how you can solve them. I'll focus on the database migration technique from Oracle to PostgreSQL. I'd like to ask you some questions. The purpose is to check how useful my talk would be. Are you using PostgreSQL? Please raise your hand. Thank you very much. Are you using Oracle DB? Finally, have you ever done the database migration? Thank you. I hope this presentation helps you to do the database migration project. Okay, thank you. Some of you may not be familiar with system migration, so let me explain a little bit about it. The system migration is to migrate system information system and the data from one environment to another one. There are basically three categories of system migration. Rehost, delight and rebuild. In the hosting, you replace only platform hardware. In delighting, you replace operating system and perhaps programming languages. In rebuilding, you remake the entire system. Some people may say that there are one more category, BPR, business process re-engineering. In BPR, you may change the purpose of the system. Anyway, database migration is needed during delighting or rebuilding. Next, let me move on the problems on database migration. Here, I start with the reason why you need database migration. Next, I list up items to be considered for the database migration. Then I'll cover some issues on database migration. Let's think about why you need database migration. If your database system is operating healthy, you do not consider migration. One typical time you may consider database migration is when you feel anxiety. Your server was brand new machine at one time. But now 10 years later, it looks a little dark and dull. Maybe the maker told you that they have only little stock of the parts of the system. Maybe it's time for you to change platform. Logically, this is not related to database migration. But in practice, you probably want to migrate your database system. Or maybe you have subtle discontent about the current system. However, you may be noticed that the license fee is too expensive. You may have become aware that the new version of some OSS database learns much faster than its ancestors. You may be fascinated by some new functionality of the brand new product. Or you may have realized that indeed your system did not need fancy function of commercial database. Now when you change the DBMS, you have to consider five items. First, you have to consider logical design of the database. Usually people use ER diagrams for the design. Second, you need to design the database physically. That is that you have to determine the actual arrangement of your data. Usually optimal arrangement highly depends on DBMS architectures. Third, you need to dump out your data from the old database and restore them into new database. Sometimes you have to consider system differences between the new database and the new database. Fourth, you have to rethink operational procedures of your system. Since such as maintenance tool like backup and batch, you might need to rewrite some procedures completely. Since available UTT tool set is different. Last, you probably need to adjust the SQL in your applications to post SQL. Post SQL conforms with SQL standard quite consistently. But as you may know, Oracle DB was designed long before SQL standards were in place. So there are some non-negligible SQL differences between Oracle and Post SQL. That is the main point of this talk. As I described in the previous slide, there are a lot of things to do when you migrate database. So what are the most important problems? I think there are two things which make you feel strong hesitation to migrate database migration. First one is the cost of assessment. I'm recommending and even encouraging the database migration. But I admit there were some cases such that migration work would be very difficult. How do you decide that you can or you cannot migrate your database system? By assessment of the system, of course. However, sometimes the assessment costs themselves were expensive. That is the problem one. Second one is the cost to modify the incompatible SQL. You have to find the incompatible SQL from 100 or even 1000 source files. After that, you have to think out how to modify the incompatible Oracle queries to fit to Post SQL world. You can be depressed. That's the problem two. I'd like to show you here what SQL incompatibility may look like. This slide shows part of sample application code written in Java to learn with Oracle Database. If you try to learn the code including this part with Post SQL, you will encounter errors. Actually, there are three incompatibilities in this part of code. Did you find that? Three SQLs, please raise your hand. SQL is two sentences, three points. Three, one, two, thank you. One, thank you. No problem. It is a very difficult task. The first incompatibility exists in the code segments, delete my table. This statement is completely legitimate in Oracle, but it is not in Post SQL. Since the delete statement needs to have a front post in Post SQL. The second and third incompatibilities exist in another code segment, select sys date from dual. Indeed, Post SQL has neither sys date nor dual table. Now, finally, I'll describe two OSS products which help with the issues described up to this point. They are Davy Shintak-Diff and Olafsy. I'll show you some migration case studies based on these two tools. First one is a tool named Davy Shintak-Diff. This tool was developed by MTK and has already been open sourced. Davy Shintak-Diff is an assessment tool of SQL incompatibility. This tool takes Java and Proxy application code as input and extract incompatibility SQL as output. Second one is Olafsy. This is a control module for Post SQL. This is something like an emulation library. Once loaded, Post SQL can accept quite many Oracle DB package functions. Have you ever heard of these two tools? Let's look at Davy Shintak-Diff. This slide shows the outline of Davy Shintak-Diff. This tool has a simple password to analyze the input files and extract the SQL sentences. Extracted SQL sentences are compared with each item in the incompatibility dictionary. The entire incompatibility dictionary is written using a regular expression. The dictionary is an independent standalone file, so by editing it, you can enhance the tool. Davy Shintak-Diff has more than 1400 check items right now. In the following four slides, I'll describe how to use Davy Shintak-Diff. Installation of these tools is very easy. As I prepare, you need to install some packages. Next, expand Davy Shintak-Diff file using the tab. After that, set some environment to variables, and you're done. Please note, if you use Zaranjabat 2.7.1 or later, you have to also set a password for serializer.jab. This slide shows command reference. As usual, you can use minus-minus help command switch to the program. Importantly, you need to set encoding and mode name which suits with input file. By the way, there are four kinds of modes, C++ and also for SQL mode, SQL files. Let me show you an example run of these two. In this slide, you can see the Java file. This code is same as the one in the slide I showed you before. In the bottom is command line. Here, I specify using command line switches that the input file name written in Java and encoding in UTFL. I also specify the input and output file name. In this example, the input is a single file. By specifying the directory name as input, you can recursively handle files in that directory. Here, I use the output of the command. The output is XML form, and each incompatibility point is listed one by one. To understand the output, you should focus on five items, five points. The first red line shows the incompatibility in each file. The second line shows the type of code snippet and difficulty level for modification. The type include SQL type and difficulty level include low, low to middle and high. Third chunk of two lines shows that in what line and column the tool found the incompatibility. The fourth one shows incompatible SQL statements or snippet. And the last red line, that is the fifth shows the description of incompatibility. Unfortunately, this part is written in Japanese right now, where I return to this point in this talk. This slide describes the LAPR tool of Davishing Tactics. This LAPR runs Davishing Tactics internally. Incidentally, the LAPR tool needs a configuration file instead of using command line option. Since the output of Davishing Tactics is XML format, it is very easy to process them further. Basically, we use this LAPR to create statistical information. Output of the LAPR tool is a CSV format. Using spreadsheet program, we can take a look in little sophisticated manner. It also includes statistical information of incompatibilities in each difficulty level. Using such statistical information, you may estimate the cost to modify application program. Since we are Japanese, this LAPR tool is written in Japanese. Actually, there is a lot of Japanese in this tool. Good news is that the LAPR tool is designed and concentrated modally. I believe it is relatively easy to translate into English, Chinese, Indonesian, Malaysian and so on. If you are interested, please help us. While Davishing Tactics is for detecting incompatibilities, ORAFC helps in dissolving some of those incompatibilities. As I said before, this is a loadable module for PostgreSQL. Installation of ORAFC is very easy and may be familiar to some of you. First, get RPM file from postgreSQL.org. Latest version is 3.2.1. Second, do standard installation by RPM. Third, say the magical words create extension ORAFC and you have that. This slide shows a simple example. ORAQDB has ORAQ2 data type. But PostgreSQL does not. So, if you use data type and you will encounter error, same error, exist no such things. ORAFC adds ORAQ2 data type to PostgreSQL. So, after you create ORAFC extension, PostgreSQL can accept ORAQ2 data type. Now, I am going to explain one database migration project as a case study. The target is a management system for business contract information. The original system was constructed using commercial operating system and commercial DBMS and commercial web server. We reconstructed the system using Red Hat Enterprise Reacts, Apache, MotoJK, JVOS, EAP and PostgreSQL. The system consists of about 740 files written with ordinary SQL, BDL, Proxy and Java. Total size of the source code is about 2,250 kg lines. We used both DB syntactic and ORAFC. Before deciding to migrate, we performed assessment of the system using DB syntactic. There are more than 10,000 incompatibilities. Luckily, almost all incompatibilities were in so-called low 1 and low 2 level. However, only 20 incompatibilities in so-called middle level and known as high level. So, we just thought that we could go ahead. We also decided to adapt ORAFC module. Using ORAFC, we could dissolve about 3,000 incompatibilities automatically. We modified remaining 7,000 incompatibilities by hand. We could say that they were not tough tasks. Based on our experience, by employing ORAFC, roughly 73% of ORAFC SQL can be used in PostgreSQL without modification. This slide shows the conclusion of this talk. Migration of SQL is the most difficult process in database migration. It will require significant cost to assessment for migration of SQL. It will require significant cost to modify incompatible SQL. These issues can be solved by DB syntactic and ORAFC. Let's try database migration, don't hesitate. If you have interest, please help the development of DB syntactic to start with translate the manual into English. That's the end of my talk. Thank you very much. Thank you for your attention. Can you handle that? Stop procedures. No, can't submit. 50% of application. Call statement in Java, calling is no procedure. It's not going to gain further, right? That's what I understand. Baby should agree? Yeah, that's fine. It's a part, part source. We need to identify it because it's called procedure. It doesn't matter, right? You still have to code the procedure in PostgreSQL. Yes. Just like you talked about how a major percentage of the ORAF SQL using the ORAFC module. I think it's a pretty big percentage. I remember it was 73 or 78%. Pretty big percentage that you said based on your experience. 33%. Now, if I understand it correctly, ORAFC basically implements the few functions that Oracle provides, not the SQL syntax. Is that correct? How did you come up with the number? It's 73%. I would be interested in knowing the experience and how you found this number. I understand. If you want to spot here, you can talk about it offline. Talk about it? It would be... I'm personally interested in that part. You can talk about it later. Okay. Emote their business that you need to replicate. This migration, the experience of migrating those sort of things into Postgres and how easy, hard...