JTLSGO 6 0 Postgre SQL Information Zafer AKTAN

JTLS-GO 6. 0 Postgre. SQL Information Zafer AKTAN ROLANDS & ASSOCIATES Corporation

Introduction § Postgre. SQL is a powerful, open source object-relational database system. § It has more than 30 years of active development and a proven architecture. § It runs on all major operating systems, including Linux and Windows. § It is fully ACID compliant to ensure data integrity. § Postgre. SQL has full support foreign keys, joins, views, triggers, functions and stored procedures (in version 11 release). § It includes most SQL: 2011 data types (as of the version 11 release). § It has native programming interfaces for C/C++, Java, . Net, Python, Perl, ODBC, among others and its own PL/pg. SQL. § It supports spatial data (Post. GIS) and binary large objects. ROLANDS & ASSOCIATES Corporation Sept. 2019 2

Introduction (continued) § The Postgre. SQL license gives users the freedom to use, modify and distribute Postgre. SQL in any form they like, open or closed source. § Postgre. SQL is also a development platform upon which to develop in-house, web, or commercial software products that require a capable, powerful RDBMS. § Today, Postgre. SQL has a DISA STIG available allowing it be deployed in secure environments (https: //public. cyber. mil/stigs/downloads/? _dl_facet_stigs=appsecurity%2 Cdatabase) ROLANDS & ASSOCIATES Corporation Sept. 2019 3

Oracle RDBMS Utilization in JTLS-GO 5. 1 We currently utilize Oracle RDBMS in two separate segments in JTLS-GO 5. 1: 1. To support JTLS-GO scenario building process, using the Database Development System (DDS), prior to the GAME start. 2. To support Scenario Data Repository (SDR) clients, such as After-Action Review (AAR) client, during the GAME execution. ROLANDS & ASSOCIATES Corporation Sept. 2019 4

JTLS-GO Database Development System (DDS) ROLANDS & ASSOCIATES Corporation Sept. 2019 5

JTLS-GO Scenario Data Repository (SDR) Clients ROLANDS & ASSOCIATES Corporation Sept. 2019 6

Oracle Express Edition (XE) Limitations For JTLS-GO SDR Clients § The freely available Oracle Express Edition (also known as 11 g. R 2 XE) allows 11 Gb of user data and is currently sufficient in supporting the JTLS-GO scenario building process with DDS. § However, the 11 Gb of user data limitation of the free Oracle XE, is not sufficient for utilizing the JTLS-GO SDR clients, primary the AAR client. § To support the AAR client, the Oracle Standard Edition TWO (SE 2) version should be used instead. § Oracle Standard Edition TWO (SE 2) license can be expensive (especially on virtualized environments). § Postgre. SQL is an excellent alternative to Oracle for both JTLS-GO 6. 0 DDS and SDR applications. ROLANDS & ASSOCIATES Corporation Sept. 2019 7

Oracle to Postgre. SQL Migrations § The official technical term in database literature from moving from one database server product to another is called "migration". § The cost is the most obvious reason and also the benefit of migrating from Oracle RDBMS to Postgre. SQL. § The Postgre. SQL License, which is like a combination of BSD and MIT open source licenses, gives users the freedom to use, modify and distribute. § Most commercial relational databases are including newer Enterprise Level features in their high-end products only, including Oracle. § Increasing popularity of the Cloud and virtualization technologies, in support of Platform as a Service (Paa. S) or Infrastructure as a Service (Iaa. S) software deployments, also brought different licensing challenges, especially when it comes to Oracle Software. ROLANDS & ASSOCIATES Corporation Sept. 2019 8

Some Benefits of Migrating to Postgre. SQL § The most obvious benefit of migrating to Postgre. SQL is cost. § The ability to create technical solutions based on need not licensing. § Avoid deploy things sub-optimally to save money on licenses instead of the right technical solution. § The case when moving your applications to virtualized servers and to Cloud based services. § Updating to newer hardware because the newer servers all have more occupied sockets and cores than the previous generation. ROLANDS & ASSOCIATES Corporation Sept. 2019 9

Oracle to Postgre. SQL Migration Challenges for JTLS-GO 5. 1 to 6. 0 § The SDC and OEC database tables are mutually exclusive and with no relationships and no other specific database objects other than the tables. § The AARC tables have some child fields to a generic Object Lookup table, has some sequences, couple triggers, etc. § Also, the bitmap indexes (a capability only available in the Enterprise Edition of Oracle RDBMS) can be replaced with similar capability in Postgre. SQL. § The Oracle Pro*C code is the most challenging part of the SDR migration from Oracle to Postgre. SQL - given there is no Pro*C in Postgre. SQL. § However, there is a way to write embedded SQL statements in C code, which needs to be investigated and implemented for SDC, OEC and AARC client programs. § Postgre. SQL provides an extensive C Language API to write embedded SQL with C programs. ROLANDS & ASSOCIATES Corporation Sept. 2019 10

Oracle to Postgre. SQL Migration Challenges for JTLS-GO 5. 1 to 6. 0 (Continued) § The JTLS-GO Database Development System (DDS) will be the most challenging part of the Oracle to Postgre. SQL migration project, given that our database schema has become more and more complex (since version 4. 0 of JTLS). § We do have stored procedures, for renaming, for populating certain child tables. § We do have flashback version queries, utilized heavily by the Glass. Fish middle tier API's which are the foundation of data synchronization among DDSC's. We need to find an alternative solution applicable when Postgre. SQL is used. § We do have custom database triggers to support populating and managing dynamic lookup tables such as the UNIT table or the TARGET tables. § We do have custom triggers in conjunction with custom stored procedures to support the renaming of the named primary key column values - such as unit names, aircraft class names, etc. ROLANDS & ASSOCIATES Corporation Sept. 2019 11

Oracle to Postgre. SQL Migration Challenges for JTLS-GO 5. 1 to 6. 0 (Continued) § We do have custom triggers to manage other Foreign Key (FK) related checks, etc. in our DDS database schema. § In addition to all the custom database objects in our DDS Oracle database schema, we do have Oracle imposed restrictions such as not being able to have "ON UPDATE CASCADE" keys – which does not exist in Oracle but does exist in Postgre. SQL database, which will allow us to do the renaming in a different way, and by doing so - impacting our existing stored procedures, triggers, etc. § We also do have many shell scripts that utilizes Oracle database specific utilities, such as sqlplus and SQL*Loader which allows us to load ascii JTLS-GO scenario data files into related Oracle database tables. All of those scripts need to be revisited, modified to accommodate Postgre. SQL database server related alternatives. ROLANDS & ASSOCIATES Corporation Sept. 2019 12

Oracle to Postgre. SQL Migration Challenges for JTLS-GO 5. 1 to 6. 0 (Continued) § Some of the capabilities in our Java code such as parsing the tnsnames. ora file, which would not exist under Postgre. SQL implementation, needs to be modified as well. § All the Glass. Fish related API's need to be revisited to ensure that they do work against the Postgre. SQL database. § Our scenario data modification process from an older JTLS-GO version to the current JTLS-GO version, also needs to be modified based on the new Postgre. SQL requirements / restrictions, instead of the legacy use of Oracle. § Using a tool like ora 2 pg (http: //ora 2 pg. darold. net/index. html) significantly helped us during the migration. ROLANDS & ASSOCIATES Corporation Sept. 2019 13

Postgre. SQL by Big. SQL (A project by AWS Postgre. SQL Team) § A true container Open Source solution developed and maintained by a team of AWS employees. § Postgre. SQL 11. 5 (or higher 11 version) client environment will be included under the JTLS-GO 6. 0 version § Installation is much simpler compared to the repository based alternatives. python -c "$(curl -fs. SL https: //s 3. amazonaws. com/pgcentral/install. py)" § 100 % pure Postgre. SQL with no 3 rd party software. § Detailed information available from http: //www. bigsql. org/ § Installation details and demo will be available at our Simulation Center. ROLANDS & ASSOCIATES Corporation Sept. 2019 14

Questions ROLANDS & ASSOCIATES Corporation Sept. 2019 15