Oracle® Transportation Management Administration Guide Release 6.3 Part No.
Oracle Transportation Management Administration Guide, Release 6.3 Part No. E38415-08 Copyright © 2001, 2015, Oracle and/or its affiliates. All rights reserved. This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws.
Contents CONTENTS................................................................................................ III SEND US YOUR COMMENTS ....................................................................... IX PREFACE ..................................................................................................... X CHANGE HISTORY ...................................................................................................... X 1. ARCHITECTURE OVERVIEW .......................................
DISABLING PROPERTY SETS ..................................................................................... 4-5 MORE INFORMATION .............................................................................................. 4-5 5. ADVANCED CONFIGURATION: APPLICATION SERVER ....................... 5-1 CHANGING THE WEBLOGIC CONFIG.XML FILE ................................................................. 5-1 MANUALLY MAKE THE CHANGES TO THE “CONFIG.XML.FRESH” FILE ..........................................
CONFIGURING FUSION TRANSPORTATION INTELLIGENCE WITH OAM (SSO) ...................... 8-3 CONFIGURING ORACLE DATA INTEGRATOR (ODI) ........................................................ 8-3 ETL PERFORMANCE TUNING ..................................................................................................... 8-3 CONFIGURING OBI EE............................................................................................ 8-4 SETTING UP AN OBI EE AGENT .................................................
MICROSOFT WINDOWS ......................................................................................................... 12-1 UNIX ............................................................................................................................. 12-2 ORACLE TRANSPORTATION MANAGEMENT TOOLS ........................................................ 12-4 CONFIGURATION DATA COLLECTION ..........................................................................................
HASH PARTITIONING INDEXES ON RAC DATABASES ....................................................................... 13-11 CHANGING DATABASE POOL SIZE ............................................................................................ 13-12 PURGING EXCESS REPORTS FROM THE REPORT_LOG TABLE ........................................................... 13-12 PURGE PROCESS FOR WINDOWS .............................................................................................
Send Us Your Comments Oracle Transportation Management Administration Guide, Release 6.3 Part No. E38415-08 Oracle welcomes your comments and suggestions on the quality and usefulness of this publication. Your input is an important part of the information used for revision.
Preface This document contains information regarding the post-install configuration and maintenance of the following products: Oracle Transportation Management, Global Trade Management, Fusion Transportation Intelligence and Oracle In-Memory Logistics Command Center. There are additional, Logistics Command Center-specific details in the Logistics Command Center Administration Guide. Change History Date Docume nt Revision Summary of Changes 12/2012 -01 Initial release.
Date Docume nt Revision Summary of Changes 12/2013 -05 Reference to Enabling IP Address in Log-in History. Corrected reference to Migration Guide. Removed “Enabling Automatic Web UI Login” section from chapter 4. Added support for diagnostics across all web servers in a non-Scalable environment. New section added to the Admin Guide on Load Balancers. New section added on Configuring Global Trade Intelligence.
Date Docume nt Revision Summary of Changes 1/2015 -08 Added section on HTTP Integration. Changed the SMTP Authentication section to be the SMTP Integration section. Updated Business Number Generator sequences to be: glog.server.bngenerator.oracleSequence.xid.SHIP_UNIT_XID.DEFAULT=SHIP_U NIT_GID_SEQUENCE glog.server.bngenerator.oracleSequence.xid.ORDER_RELEASE_LINE_XID.DEFA ULT=ORDER_RELEASE_LINE_GID_SEQ Added section: “Using Timeouts with SQL Statements”.
1. Architecture Overview Oracle Transportation Management is built for interactive environments that leverage the internet as an information backbone to capture reference data such as updates to carriers, carrier rates, shipping schedules, locations, ports, and other logistics sources.
The Oracle Transportation Management web server uses the Oracle HTTP Server (OHS) web server and the Tomcat Java servlet engine to manage the user interface and communications with the application server. Application Server The application server controls the application logic and communicates that logic between the web server and the Oracle Database.
The Integration server and the application server communicate to the application server using Enterprise Java Beans (EJB) session bean calls. When an integration event occurs, the integration server notifies the application server using a session bean call. When the application service needs to communicate with the outside world, it does so directly, using an integration solution that provides a method to build transmissions and forward them to the EAI solution.
2. Configuring Optional Components These components include: Replicated Online Database (ROD) Oracle MapViewer server Oracle Spatial server Custom External Rating and Distance Engines GFI FAXmaker for Networks/SMTP 8.
following lines, setting the values on the right as appropriate (see the relevant section of the Oracle Transportation Management Install Guide for details on what each field’s value should be): # Oracle MapViewer server glog.mapServer=mapviewer.company.com glog.map.service_name=otm_world glog.map.basemap=otm_world.world_sample Once this change has been made, restart your Oracle Transportation Management instance.
2. Create a POP3 mailbox within your mail server that can be accessed by the FAXmaker server. The mailbox name must be identical to the email address for outgoing fax notifications that you entered during the Oracle Transportation Management application server install. Test this POP3 account using any email client that supports POP3. Please contact your System Administrators for more information as they will have greater insight into the makeup of your SMTP mail services. 3. 4.
2. 3. Test the modem to ensure that it gets a dial tone and can access an outside line. 4. Install RightFax according to the vendor’s installation guide and configure it to poll the POP3 mailbox created above. Create a POP3 mailbox within your mail server that can be accessed by the RightFax server. The mailbox name must be identical to the email address for outgoing fax notifications that you entered during the Oracle Transportation Management application server install.
firstName lastName company phone rawPhone (punctuation or space) message subject Replacements are represented by one of these identifiers surrounded by parenthesis; for example, {firstName}. Newline characters are preserved. To transform an email message body to one supporting FAXMaker, the picture string would be: ::{firstName},{company},{lastName},,{rawPhone}\n{message} The following properties control the default fax handler: glog.fax.defaultHandler.
pcmiler.host=pcmiler.company.com pcmiler.port=8145 Uncomment and modify the following lines for PCMiler Rail: pcmiler.rail.host=pcmilerrail.company.com pcmiler.rail.port=2001 Once this change has been made, restart your Oracle Transportation Management instance. Note that these changes for PCMiler products may be made in the APP_CUSTOM Property Set rather than the glog.properties file directly. Once completed, you must still restart your Oracle Transportation Management instance.
lines, setting the values on the right as appropriate (see the relevant section of the Oracle Transportation Management Install Guide for details on what each field’s value should be): milemaker.host=otmmm.company.com milemaker.port=1031 Once this change has been made, restart your Oracle Transportation Management instance. Note that these changes for MileMaker may be made in the APP_CUSTOM Property Set rather than the glog.properties file directly.
Configuring Kewill FlagShip Install the Kewill FlagShip server as described in your FlagShip user’s manual. You can set up Oracle Transportation Management to integrate with Kewill FlagShip during the initial Oracle Transportation Management installation (as described in the Oracle Transportation Management Install Guide), or you can modify an existing installation to use Kewill FlagShip. To modify an existing Oracle Transportation Management installation, you need to edit the glog.
3. Starting and Stopping the Oracle Transportation Management Servers Starting Oracle Transportation Management Servers Oracle provides shell scripts for use with UNIX operating systems to start and stop the various components. These scripts may be used as-is or may be modified as needed (in which case it is recommended that you save the modified files to a different filename). The shell scripts are suitable for use with most /etc/init.d-type automatic startup environments.
INFO | 2012/11/12 10:38:16 | INFO | 2012/11/12 10:38:16 |
4. Advanced Configuration: Custom Properties Much of the configuration and customization of Oracle Transportation Management involves managing properties used by the application. These properties are initially distributed in a set of hierarchical property files, where one property file can include another. This allows reuse of common properties on both web servers and application servers and the overriding of these staged properties for a particular installation.
The resulting list of values for a.property is now value1 and value3. Workflow Example A common example of multi-valued property use is the specification of thread count for a workflow queue. The multi-valued properties that define workflow threads are given by: glog.workflow.topicGroup=, By monitoring workflow queue backup, you may need to increase the default thread count for a specific queue. The batch queue, for example, handles many long-running batch processes.
#-------------------------------------------------------------------# Custom Properties – Beginning #-------------------------------------------------------------------# Place all changes, new properties, and custom properties here.
rather than in a file. Property sets can be modified via the Property Set Manager. By default, this is available only to the DBA.ADMIN user via Configuration and Administration > Property Management > Property Sets. Property sets support these types of instructions: Set. Set the value of a property Remove. Remove all or one value of a multi-valued property Include.
!include glog.webserver.properties 2. Edit the glog.properties file for each application server and insert the line !propertySet WEB_CUSTOM,sibling before the line: !include glog.webserver.properties Also insert the line: !propertySet APP_CUSTOM,sibling before the line: !include glog.appserver.
5. Advanced Configuration: Application Server Note: Default paths are used below and may differ from your configuration. Changing the WebLogic config.xml File WebLogic uses a file called “config.xml” to store and read configuration data. This file is generated automatically every time the application is started, using a file called “config.xml.fresh” as the basis. Changes made directly to the “config.
6. Advanced Configuration: UI/Web Server Note: Default paths are used below and may differ from your configuration. Note: Whenever you edit the Oracle Transportation Management property files, only edit the glog.properties file. Any changes that you make to any other files will be overwritten every time an update is applied. Creating and Installing Secure Socket Layer (SSL) Certificates This information has moved to the Oracle Transportation Management Security Guide.
Installing Multiple Web Servers Multiple web servers can be utilized to increase the performance of Oracle Transportation Management. Generally, with more web servers you can maintain more simultaneous user connections into the Oracle Transportation Management server’s web interface. You may also see increased performance in integration, since incoming integration files are posted to a servlet on the web server and are passed back to the application server.
web server of all potential web servers. To do this you will need to add properties to inform each web server of the existence of other web servers. To do this directly in the “glog.properties” file: 1. Back up the existing “glog.properties” file. $ cd /glog/config $ cp glog.properties glog.properties_20130417_2156 2. Edit the “glog.properties” file. 3. Locate the section that looks like this: !remove glog.scalability.web.topologyMachineURL glog.scalability.web.
After 4. Restart the web server instance. Installing Translations Under UNIX systems: 1. Log in as the Oracle Transportation Management user. 2. Change the directory to /glog/oracle/script8. 3. Run the following command: install_lang.sh []. Under Microsoft Windows: 1.
Fusion Transportation Intelligence Translations Oracle Fusion Transportation Intelligence translations are installed when the Fusion Transportation Intelligence application is installed; please see the Installing Other Languages for Oracle Fusion Transportation Intelligence Reports section of the Oracle Transportation Management Install Guide for more information. Notifications Translations To have notifications translated, set the following property in the application server's glog.
7. Advanced Configuration: Database Note: Default paths are used below and may differ from your configuration. Note: Whenever you edit the Oracle Transportation Management property files, only edit the glog.properties file. Any changes that you make to any other files will be overwritten every time an update is applied.
Note that these properties are reserved and cannot be set as properties in a property set. Special configuration in the RAC instance is needed to use a SCAN Listener. Please see the Oracle Real Application Cluster documentation for more details. International Characters in SQL*Plus Windows Oracle Transportation Management supports multiple languages. However, you can only display characters for one language at a time.
Features/Limitations: Simple and straightforward. There is no need to pre-create an Oracle Database in the destination machine. Fast. The total time needed is governed by physically transferring datafiles from source machine to destination machine. Whole database copy. If there are data in the source database that the client does not want to copy to the destination database, then data cleaning process should be carried out in the destination database after the copy.
Domain Copy Oracle Transportation Management provides utilities to copy domains between Oracle Transportation Management databases. There are two approaches to copy domains. The first one uses a PL/SQL procedure to generate INSERT statements with domain data to be copied from the source database. The insert statements can be run in the target database. The second approach uses Oracle Database TABLE mode export/import with WHERE clause to move domain data between source and destination databases.
insert into data_source (data_source_gid,data_source_xid,data_database_gid,manager,jdbc_driv er,jdbc_url,schema,oracle_user,oracle_password,vpd_user,is_profiling _leaks,is_multi_user,domain_name) values ('CUSTOM_DS','CUSTOM_DS','GC3','EXTERNAL JTS','oracle.jdbc.OracleDriver','jndi:CUSTOM','glogowner','glogdba', '','DBA.
Multiple Oracle Transportation Management Application Server Instances The new data source created using WebLogic console will not be available for all Oracle Transportation Management application instances. Repeat the above steps for each application server with same data source and JNDI name, target value will be different according to application server. Note: Do not copy config.xml.fresh or the jdbc configuration file from one server to another.
8. Configuring Fusion Transportation Intelligence Configuring Oracle Transportation Management Scheduling ETL through Oracle Transportation Management Process Management You can schedule the ETL to run using the Run ETL process in Oracle Transportation Management process manager. Run ETL run the ETL from the replicated online database (ROD) or the online transaction processing (OLTP) database to the historic database (HD).
Setting the Status for New Objects: Oracle Transportation Management ships with several PUBLIC automation agents to set the status of shipments, order bases, and order releases to ##_READY_TO_LOAD to allow business objects to load into Fusion Transportation Intelligence. You need to enable these automation agents as described below.
Oracle Fusion Transportation Intelligence offers additional external predicates from version 6.2. These are available under the following standard VPD Profiles shipped with the Oracle Transportation Management application: FTI_DEFAULT: This VPD profile is applicable for all Oracle Fusion Transportation Intelligence users who are not service providers in Oracle Transportation Management.
Creating Indexes First, you create indexes as follow: 1. Connect to the source database as glogowner and run the following: /script8/fti_indexes.sql 2. Connect to the source database using a sys user and run following commands: alter system set optimizer_index_caching=0 scope=BOTH; alter system set optimizer_index_cost_adj=100 scope=BOTH; 3. Install and configure the ODI Agent in the target (hdowner) database instance machine.
f. Log out of Oracle Transportation Management. 3. Log back into Oracle Transportation Management as the user that will create alerts. 4. 5. Click Transportation Intelligence > Alerts. 6. 7. In the My Account page, click Deliver Options tab. 8. 9. Click OK to save the device. In the upper right of the Oracle Business Intelligence page, click the down arrow next to Signed In As USER.ADMIN and click My Account. Set up a device and delivery profile.
9. Configuring Global Trade Intelligence Configuring Oracle Transportation Management Scheduling ETL through Oracle Transportation Management Process Management You can schedule the ETL to run using the Run ETL process in Oracle Transportation Management process manager. Run ETL run the ETL from the replicated online database (ROD) or the online transaction processing (OLTP) database to the historic database (HD).
GTI_DEFAULT: This VPD profile is applicable for all Oracle Global Trade Intelligence users. This includes all the external predicates available in the existing DEFAULT and FTI_DEFAULT Oracle Transportation Management VPD profiles and the new external predicates specific to the Oracle Global Trade Intelligence solution’s Historical Database tables.
10. Advanced Configuration: General Note: Default paths are used below and may differ from your configuration. Note: Whenever you edit the Oracle Transportation Management property files, only edit the glog.properties file. Any changes that you make to any other files will be overwritten every time an update is applied.
7. Run 6.3 migration scripts according to the latest Migration Guide 8. Run any 6.3.2-specific instructions 9. Create, inspect and run the customizations migration script Branding Oracle Transportation Management upgrades typically include updated web files (XSL, html, jpg, gif, JS, etc.) to fix specific reported problems and to provide minor enhancements requested by customers.
Note that any preference directory not referenced explicitly in this manner will not be migrated and should be migrated by hand. UI Branding – XSL Files As part of the 6.
UI Branding – CSS Files As part of the 6.
Changing Notification Settings The Oracle Transportation Management server sends out a variety of notifications to users. Changing these settings involves modifying the glog.properties file on your Oracle Transportation Management application server, or modifying the APP_CUSTOM property set, as described below. glog.mail.smtp.host=smtp.company.com This setting defines the SMTP server that Oracle Transportation Management uses when sending email and fax notifications. glog.workflow.notify.advisor.
Data > General > Unit of Measure. This page is only available when you are logged in as DBA.ADMIN. For more information on this topic refer to the help topic “Unit of Measure”. Changing Currency Settings By default, Oracle Transportation Management uses US Dollars when saving costs to the database. Also by default, Oracle Transportation Management triangulates all currency conversions through US Dollars.
2. Set your currency storage default by running the following insert statement (example using GBP as new storage default) as the DBA user: INSERT INTO UOM (TYPE, UOM_CODE, UOM_SHORT_DESCRIPTION, UOM_LONG_DESCRIPTION, UOM_RANK, IS_STORAGE_DEFAULT, IS_DISPLAY_DEFAULT, DOMAIN_NAME) VALUES (‘CURRENCY’, ‘GBP’, ‘UK POUND’, ‘UK POUND’, 1, ‘Y’, ‘Y’, ‘DBA’) Oracle Transportation Management still needs currency rates to convert between the currencies you use. You can download rates from the IMF website.
Country Code Conversion from ISO Alpha-3 Code to Alpha-2 Code 1. Oracle Transportation Management uses ISO alpha-3 code format for the country code. Country code format can be change to ISO alpha-2 code as described below. This process will change 3 character country code to 2 character country code in the Oracle Transportation Management database.
Process Flow APIs for Compiling pkg_user_exit UserExitCompiler Class The UserExitCompiler should be able to load pkg_user_exit package from database and compile it to the database. Additionally, it can perform backup and recovery of the package. The backup and recovery allows you to restore the package to previously functional state when the edited package compilation is failed or the user decides to discard the altered package.
Pkg_SQL_executor Package Class UserExitCompiler connects database through GLOGDBA. However, GLOGDBA does not have privileges to create database objects in Glogowner schema. To circumvent the issue, a new package called Pkg_SQL_executor is created to compile altered pkg_user_exit package in Glogowner schema. The privilege to execute the package is granted to GLOGDBA. The package is executed from UserExitCompiler.
Your line would look like this: !include ebs.properties Once this change has been made, restart your Oracle Transportation Management instance. Oracle Transportation Management Properties Files for BPEL Integration If integrating with the Oracle BPEL Process Manager, but not necessarily integration with Oracle EBusiness Suite, you will need to enable a property on the Oracle Transportation Management web and application servers. In the glog.
11. Advanced Configuration: LDAP This information has been moved to the Oracle Transportation Management Security Guide. Copyright © 2001, 2015, Oracle and/or its affiliates. All rights reserved.
12. Performance Monitoring Performance monitoring is a necessary part of running a complex multi-tier application like Oracle Transportation Management. In most cases, a sudden change in performance is caused by a recent configuration change or an increase in transactional data. A formal change management process is critical for maintaining a stable production system and for quickly diagnosing performance issues should they happen.
Force Garbage Collection: This will force the JVM to free up all unused memory. This should be run in order to get a true memory reading from the main performance screen. Keep in mind that forcing a garbage collection will slow the server down and shouldn’t be forced continually. The JVM usually manages garbage collections automatically as the server runs. 7. To monitor database connections, expand Services > JDBC > Connection Pools. 8. Select dbaPool. 9. 10.
system or iowait columns it could be an indication of other internal processes or swapping memory to and from disk that may need to be investigated further. TOP The “top” command-line utility produces a frequently-updated list of processes on most UNIX-based platforms. By default, the processes are ordered by percentage of CPU usage, with only the highest CPU consumers shown. The top command shows how much processing power and memory are being used.
Oracle Transportation Management Tools Configuration Data Collection The Configuration Data Collection tool captures important configuration data that can be used in diagnosing a performance issue. Users have the option of displaying the information on the screen, or they can have the data collected to them via FTP or via email. Information sent via FTP and email is encrypted, but this can be disabled via the following properties: glog.diag.output.ftp.encrypt=false glog.diag.output.email.
JVM Tools Console Logs For the web and application servers, system monitoring utilities will only be partially effective since the memory of a Java application is typically static and limited by the Java heap size. They will be useful if multiple applications are being run on a single machine and the total physical memory may be limited. If the entire Java heap cannot be contained in physical memory performance issues will be very likely due to memory swapping during JVM garbage collection.
/jdk/bin/jrcmd jrarecording time=300 filename=/logs/perfIssue.xml.zip threaddumpinterval=30s Please note the time is in seconds and this can be modified to any number of seconds. The directory and filename can also be changed. The JRA utility will write to the file for the entire time interval specified. Every time you run it, you would probably want a new filename to help differentiate between the different records.
Are agents and integrations not processing? Record the exact timeframe of when the issue occurred. 2. Monitor CPU, I/O, and network activity. Use a system monitoring tool to capture and review whether there is abnormally high CPU, network, or I/O activity on the web/app/database. 3. Check the exception logs. Capture and review the exception.log file for unusual exceptions. 4. Review logging that is enabled.
13. Performance Tuning JVM The JVMs supported by Oracle Transportation Management vary by platform. Each JVM has a set of parameters which can be used to tune the performance of the JVM. Most of these parameters affect the memory management process referred to as garbage collection. Oracle Transportation Management ships with a default set of these parameters to be used as a starting point for JVM tuning. Changing these parameters can have a significant impact on the overall performance of the system.
Web Server Simultaneous Users If your installation is going to have a large number of simultaneous users it may be necessary to tune some Oracle HTTP Server and Tomcat settings. It is necessary to do performance testing and monitoring in order to tune these settings for an Oracle Transportation Management installation. Depending on the volume of users and the type of activity performed by these users it may also be necessary to setup additional webserver instances. 1.
Application Server Logging Although logging is a vital function in Oracle Transportation Management, excessive logging is very common cause of poor performance. This is particularly true of bulk planning processes. The Log Files and Logging Overview pages can be used to review what logs are currently enabled. You can also temporarily disable all logging by setting the following property: glog.log.
Business Number Generator (BNG) Contention The Business Number Generator is an Oracle Transportation Management mechanism for creating IDs based on a complex set of business rules. Since the IDs are based on a sequence, it is necessary to generate the IDs one at a time in order to prevent duplicate IDs. For this reason, a process may have to wait for another process to finish generating an ID. This waiting can manifest itself as a performance issue.
DirectSQLUpdate DirectSQLUpdate is an agent action which allows you to update an attribute of an Oracle Transportation Management business object using a SQL statement. It is equivalent to a database trigger, but the agent action is capable of refreshing Oracle Transportation Management business caches to reflect the modified data. Performance tuning of SQL statements in DirectSQLUpdate is important. It is also important to optimally configure the use of the cache refreshing options in the action.
glog.workflow.topic.group.glog.server.agent.business.shipment.SlowTopic =slowEventGroup When modifying workflow threads via property sets, be sure to place your properties in the APP_WORKFLOW_THREADING set. Automatic Timeout for Stuck Threads For any event queue, we can set an automatic timeout that effectively kills the thread and redirects work to a new thread of the same name. To use this feature, you need to set: glog.eventQueueMonitor.polling= glog.eventQueueMonitor.
Oracle Database also has automatic statistics gathering job GATHER_STATS_JOB, which is enabled by default when a database is created, or when a database is upgraded from an earlier database release. This job gathers statistics on all objects in the database that have missing statistics or stale statistics (stale - the underlying object has been modified significantly, i.e. more than 10% of the rows). The Scheduler runs this job during a maintenance window (by default, 10pm to 6am).
Checking for Stale and Partial Oracle Database Statistics The following query can be used to check for stale schema statistics: select table_name, last_analyzed,num_rows, sample_size from dba_tables where last_analyzed is not null and owner = 'GLOGOWNER' and last_analyzed < (SYSDATE - 7) order by last_analyzed asc Statistics having a ‘last_analyzed’ date older than one week, should be updated. The following query can used to check if a partial analyze was run recently.
temporarily enable SQL logging in Oracle Transportation Management and review the logs for repetitive SQL statements. Once a poorly performing SQL statement is identified, an Explain Plan should be performed. Within Oracle Transportation Management this can be performed in SQL Servlet by pre-pending the SQL statement with “explain plan for”. Performing an explain plan outside of Oracle Transportation Management will supply more information about the query.
Statement Type Use Case Description Default, in seconds update directSqlUpdate DIRECT SQL UPDATE agent action 1800 update default All update statements 1800 call directSqlUpdate DIRECT_SQL_UPDATE agent action 1800 call default All other stored procedure calls 1800 INIT.ORA Parameters The values of INIT.ORA tuning parameters can have a significant impact on performance.
To use these services, users need to configure new connect strings in the corresponding glog.properties file5 for each application server like the following: otm_oltp glog.database.dbaOnly.t2client.connectionURL=jdbc:oracle:thin:@(DESCRIP TION=(ADDRESS=(PROTOCOL=TCP)(HOST=otm-rac01vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=otm-rac02vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=otm-rac03vip)(PORT=1521))(LOAD_BALANCE=yes)(CONNECT_DATA=(SERVER=DEDICATED)(SERV ICE_NAME=otm_oltp)))\ otm_batch glog.database.
7. Also query data dictionary to ensure that hash partitoned indexes are created, and all primary key and foreign key constraints are valid. SELECT table_name, index_name FROM dba_part_indexes WHERE owner IN ('GLOGOWNER','REPORTOWNER'); SELECT owner, status, constraint_type, COUNT(*) FROM dba_constraints WHERE owner IN ('GLOGOWNER','REPORTOWNER') GROUP BY owner, status, constraint_type; 8. Restart all web and application servers.
Parameters These parameters should be passed to the procedure in the order listed below: Start Date – this is the starting date from which the records are to be purged. % can be used to imply all dates. Default value is NULL. The format for the field is DD-MON-YYYY. End Date – this date is to date till which the records are to be purged. % can be used to imply all dates. Default value is NULL. The format for the field is DD-MON-YYYY.
The transmission purge process accepts the following arguments: Age in days (required): the age of the transmissions that you want to purge. If you have no idea of how many transmissions you have that are older than 30 days (for example), you can do the following query: sql> select count(*) from i_transmission where sysdate-create_date > 30; Total Minutes (defaults to 60): The maximum number of minutes you would like the total purge process to take.
Range Partitioning Here is an example of partitioning, or segmenting, a table where a certain year and month drive the partition the data should be placed in.
Quarterly Monthly Weekly Daily IE_SHIPMENTSTATUS I_LOG (mobile) IE_SHIPMENT_REFNUM I_LOG_DETAIL (mobile) IE_SHIP_GROUP_REFNUM I_TRANSACTION_ACK (mobile) IE_SS_EQUIPMENT I_TRANSACTION_DETAIL (mobile) IE_SS_REMARK I_TRANSACTION_REFNUM (mobile) IE_SS_STOP I_TRANSMISSION_ACK (mobile) IE_SS_S_EQUIPMENT I_TRANSMISSION_REFNUM (mobile) IE_S_SHIP_UNIT I_TRANSMISSION_REPORT (mobile) IE_S_SHIP_UNIT_LINE I_LOG I_LOG_DETAIL I_TRANSACTION_DETAIL PROBLEM I_TRANSACTION (inbound) I_TRANSMISSION (inbound)
If the table is monthly, then the data is segmented as follows: Month Partition Jan partition 1 Feb partition 2 Mar partition 3 Apr partition 4 May partition 1 June partition 2 … If the table is weekly, then the data is divided as follows: Week Partition Jan 1 –7 partition 1 Jan 8-14 partition 2 Jan 15- 21 partition 3 Jan 22-28 partition 4 Jan 29-Feb 4 partition 1 Feb 5–11 partition 2 … Every table reuses its partitions, because the intention is that before the end of the cycle,
Note: I_TRANSACTION table’s OUTBOUND5 partition stores tender data. OUTBOUND5 partition will not be truncated by this job; Tender data follows lifecyle of SHIPMENT data hence it will be deleted by OTM shipment purge process “mark for purge”. If Tender data needs to be deleted during the shipment purge process, then change the logic configuration parameter PURGE_TENDER_TRANSACTION to TRUE from the default value of FALSE. It can also manually purged with sql command “exec domainman.
Purged partition 2 for table I_TRANSACTION_DETAIL Purged partition 2 for table PROBLEM Purged inbound i_transaction records Purged inbound i_transmission records PL/SQL procedure successfully completed. SQL> @purge_partitioned_tables This routine will truncate the oldest partition for each table that has been identified to be purged at the inputted frequency. The valid values are 'Q' for quarterly, 'MM' for monthly, or 'WW' for weekly, or 'D' for daily.
It is recommended that, in an Oracle Transportation Management production database, following objects be pinned in the SGA: Package: VPD PARTIT PKG_PURGE RRL USER_EXIT_HELPER RPT_GENERAL Database Space Monitoring A DBA should periodically monitor Oracle Transportation Management database(s) space usage. There are several tables containing a column with data type of CLOB. These columns can have great amount of data. Some of the CLOB tables can be loaded quite often.
performance. For example, that the Finder Page Size is set to 500. Aside from the delay in retrieving that many records, the overhead of having a fetch size of 500 when the number of records may be much smaller may exceed the savings in round trips. The maximum property provides a system-wide constraint on the fetch size. For updates and inserts, the application can use Oracle Batch updates to minimize round trips and increase modification speed. The following properties control batch updates: glog.sql.
Inbound Integration Options Large Transmission Servlet The LargeTransmissionServlet can be used for sending exceptionally large transmission(s) into Oracle Transportation Management. The difference with WMServlet is that parsing of the XML is handled in the servlet, and there is suppression of storing the complete transmission in the database. The individual transactions are stored in the database. It persists only the individual transactions, not the transmission.
Active Table Oracle Transportation Management implements data security via the Oracle Database feature Virtual Private Database (VPD). VPD provides an ability to dynamically append where conditions to SQL statements at runtime. Oracle Transportation Management uses this capability for its external predicate functionality and for cross-domain visibility. By default, a “where” condition is appended to a SQL statement for each table in the SQL.
The valid values are: none – No optimization so all domains are used. currentDomain – Status types from the current domain only are used. childDomains – Status types from the current and child domains are used. grantedDomains – Status types from all granted domains are used. The following property controls how many domains will be included before the logic reverts to the same behavior as “none”: glog.query.
Functional Security CheckAccess Cache Sizes Oracle Transportation Management functional security is cached by user. If your installation is going to have a large number of simultaneous users it may be necessary to increase the size of the functional security CheckAccess cache to be equal to the peak number of users expected in order to obtain optimal performance. In the glog.
Report Transformation. BI Publisher transforms the data XML stream and the format template into a desired output document. This document may be HTML, XML, PDF, RTF, or an Excel spreadsheet. Distribution. For ad-hoc reports, the user may request the report be served directly back to their browser. Alternatively, they may want the report to an e-mail recipient via SMTP. Scheduled reports are either sent directly via e-mail or transferred to an Oracle Transportation Management Contact.
Note: These properties have no impact on scheduled reports: they always run on the application server. When reports are generated on the web server, web scalability can be used to scale generation limit the impact of report generation on a core cluster of web servers. See the Application Scalability Guide for more information. Management of Report Files During report generation, a number of files may be stored in the server’s file system.
Property Holds Default glog.bipreports.cleanup..frequency # of minutes between file scans Reports: 360 Images: 360 glog.bipreports.cleanup..files comma-delimited list of file wildcards to remove Reports: report*, *.html, *.rtf, *.pdf, *.xls Images: xdo* Valid types include: content, report, webImages and appImages.
14. Troubleshooting Known Issues Installation There are known problems with Windows X server Terminal Emulators and InstallAnywhere on Solaris. InstallAnywhere is the installation program used by both WebLogic and Oracle Transportation Management.
14-2 Copyright © 2001, 2015, Oracle and/or its affiliates. All rights reserved.
