Looking at JDE from the Database Layer Tim

Looking at JDE from the Database Layer Tim Mc. Murdo / Darcy Bains August 22, 2018 #JDEINFOCUS

Agenda • What Databases and Platforms are Supported? • What is the Difference between Production Database & Local Database? • Why are there so many Database Schemas / Owners? • How does JDE Connect to the Database? • How are Non-Production Environments Refreshed? • What needs to be Backed Up? • When and How is Patching and Maintenance Done? • Database Tools, UTB / Databrowser, SQL Developer, OCM, Object Explorer • Common Database Scripts #JDEINFOCUS

What Databases and Platforms are Supported? • Platform / Operating System • • Linux / Unix Windows IBM i Solaris • Databases • • DB 2 for Windows / Unix / Linux DB 2 for IBM i Microsoft SQL Server Oracle Database #JDEINFOCUS

What Databases and Platforms are Supported? • The supported databases and platforms will varies depending on your Application Level (e. g. 9. 1 or 9. 2) and the Tools Release Level (e. g. 9. 1. 3. 0 or 9. 2. 2. x) • #JDEINFOCUS

What Databases and Platforms are Supported? • My Oracle Support - Certification Tab #JDEINFOCUS

What Databases and Platforms are Supported? #JDEINFOCUS

What is the Difference between Production Database & Local Database? • Production Database • Centralized specifications and data accessed by all users • Local Database • • Only accessed by local machine Resides on Deployment Server and Development Clients Used to store local specification on a Development Client Used to store HTML Serialized Objects when using Local Web on Development Client • Used to store the initial stand alone version of JDE (specifications and data) on the Deployment Server • Local Database has to be Oracle with newer Tools Releases • Password for Local Database is encrypted by JDE #JDEINFOCUS

How does JDE Connect to the Database? • Data Sources • Data Source Master table (F 98611) • A least two sets of table F 98611 exist. • They reside in a centralized system data source normally kept on an enterprise server which is accessed by workstations and in a server map data source, which each logic server requires. #JDEINFOCUS

How does JDE Connect to the Database? • Data Sources #JDEINFOCUS

How does JDE Connect to the Database? • Proxy User • Enterprise. One users are mapped to System users or Proxy users. System users are defined using application : P 980001 • System Users are used for communication with the Database. • A Database Connection Pool is keyed off Proxy User and Data. Source, so if we have a high number of Proxy user /Datasource combinations it will result in as many Database Connection Pools. Connections to Database cannot be shared across Connection Pools which results in higher number of Connections to Database. • Create Proxy users judiciously based on privilege to access data in a given Datasource. #JDEINFOCUS

How does JDE Connect to the Database? • Database activity from the HTML server uses JDBC connections • Additionally, user activity on the Web server triggers "User specific" actions, and "Global" actions. • For example when a user runs an application that needs to be JITIed/Serialized, the JAS application will read the specs from the database, perform the serialization and store the binary object in the F 989999 and F 989998 tables with an INSERT SQL query. This action is not "user specific" and therefore it will likely use the JDE account to insert those records #JDEINFOCUS

Why are there so many Database Schemas / Owners? Schemas / Owners #JDEINFOCUS SY 920 System Tables OL 920 Object Librarian DD 920 Data Dictionary SVM 920 Server Map DV 920 DV Central Objects TESTCTL DV Control Tables TESTDTA DV Business Data

Why are there so many Database Schemas / Owners? Common Tables JDE 920 OL 920, DD 920, SY 920, SVM 920 Application Tables DV 920 JDE_DEV DV 920 TESTDTA / TESTCLT PY 920 JDE_CRP CRPDTA / CRPCTL PD 920 JDE_Production PD 920 PRODDTA / PRODCTL PS_920 JDE_PRIST 920 PS 920 #JDEINFOCUS Business Data PS 920 / PRISTDTA / PRISTCTL

Why are there so many Database Schemas / Owners? Common Tables Application Tables Business Data DV 920 JDE_DEV JDE_QA DV 920 TESTDTA / TESTCLT QADTA / QACTL PY 920 JDE_CRP JDE_TR CRPDTA / CRPCTL TRDTA/ TRCTL JDE 920 OL 920, DD 920, SY 920, SVM 920 PD 920 JDE_Production PD 920 PRODDTA / PRODCTL PS_920 JDE_PRIST 920 PS 920 #JDEINFOCUS Business Data PS 920 / PRISTDTA / PRISTCTL

How are Non-Production Environments Refreshed? Business Data JDE_PRIST 920 JDE_DEV PRISTDTA / PRISTCTL Refresh via SQL JDE_Production Or PRODDTA / PRODCTL TESTDTA / TESTCLT JDE_CRP CRPDTA / CRPCTL UBE JDE_QA QADTA / QACTL JDE_TR TRDTA/ TRCTL #JDEINFOCUS

How are Non-Production Environments Refreshed? Common Tables Application Tables PS 920 Application Tables (Central Objects) should be rarely refreshed as the promotion path for all objects are from DV to PD DV 920 Common Tables are not refreshed as there is only one set of these tables #JDEINFOCUS Object Promotions - OMW JDE 920 OL 920, DD 920, SY 920, SVM 920 PY 920 QA 920 TR 920 PD 920

When and How is Patching and Maintenance Done? • First apply patches to a sandbox environment if available • Should be coordinated between DBA / CNC • Audit the DB Logs, Server Logs and JDE Logs • Apply to non production environments • Apply to production environment (includes system tables) • ** Always ensure database and server backups are working prior to applying patching ** • Virtualized snapshots are a great safety measure #JDEINFOCUS

What needs to be Backed Up? • Production System Database(s) • Schemas: SY 920 / OL 920 / DD 920 / SVM 920 • Business Data • Schemas: PRODDTA / CRPDTA / TESTDTA • Central Objects • Schemas: PD 920 / PY 920 / DV 920 • Deployment Server • Local databases – planner / metadata • Fat Clients * • Local database #JDEINFOCUS

Database Queries • Should we run SQL queries or use UBE’s? • Answer: BOTH ! • Some applications have bugs and you will have to use SQL. • Other times, the UBE is the most efficient, use SQL to validate UBE’s are working as expected • Build a repository of all database queries • Common Used queries – convert to dynamic reports and host them on a shared location • DBA’s, CNC’s to utilize queries for auditing, health checks #JDEINFOCUS

Database Queries • Example • E 1: ENV: Creating a Custom Environment Using Environment Director (P 989400) Causes Missing Server Map OCMs (Doc ID 1456050. 1) #JDEINFOCUS

Common Database Queries • Checking for missing OCM records by comparing between the environments • Currently there is no designated UBE for comparing the OCM between two environments in the same data source. • This needs to be checked by running SQL queries against the Database Server where the OCM tables are stored in the specific Database data source #JDEINFOCUS

Common Database Queries • Checking for missing OCM records by comparing between the environments SELECT OMAPPLID, OMOBNM, OMDATP, OMDATS, OMUGRP, OMOAPP, OMDATM, OMOVRE, OMSY, OMSTSO, OMPID, OMFUNO, OMUSER, OMOCM 1, OMJOBN, OMOCM 2, OMUPMJ, OMOCM 3, OMUPMT, OMOCMA, OMOCMB, OMOCMC, OMOCMDSC FROM SVM 920. F 986101 WHERE OMENHV='DV 920' AND OMSTSO='AV' AND OMOBNM NOT IN (SELECT OMOBNM FROM SVM 920. F 986101 WHERE OMENHV='UAT 920’); The above SQL query example is built for checking the OCM defined for the standard DV 920 environment, but they are not defined for a custom environment UAT 920, in the Server Map data source (SVM 920). #JDEINFOCUS

Database Tools OCM Configures the pointers for the tables to the data sources UTB / Databrowser Allows to view data through front end JDE SQL Developer Raw data access to database Object Explorer 3 rd party tool allows to view data XREF JDE application shows all relationships of objects and tables #JDEINFOCUS

Common Database Queries Amount of objects in each path code (central objects) DV 920 will typically have the highest amount of objects #JDEINFOCUS

Common Database Queries #JDEINFOCUS OMW Project Status Summary

Common Database Queries #JDEINFOCUS Show when accounts expire

Common Database Queries #JDEINFOCUS Show Locked out accounts

Common Database Queries #JDEINFOCUS Database Table Locks

Who is the Quest Community? A 55, 000+ member user community for Oracle Cloud, JD Edwards and People. Soft customers. What the Quest JD Edwards Community offers: Customized digital content Official JD Edwards newsletter Customer success stories Virtual and face-to-face events JD Edwards networking groups Visit www. Quest. Direct. org for more information! #JDEINFOCUS