Process Steps to load GPS data into Truckmate
Truckmate is a very popular Transporation Management system. As such !Tsuite is especially suited to loading GPS data into either the truck (PUNIT) or TRAILER tables along with their associated breadcrumb tables. JAAT Solutions has many years of experience of working with TMW Truckmate © and TMW Suite ©. This article deals specifically with Truckmate.
Process Steps for a typical Truckmate © Installation
- First we install two utilities that we use for implementation. They can be removed (uninstalled) after you go live. They are just used to speed up implementation and testing. This is preferred over the standard SQL Editor in Truckmate
- Notepad++ -- (https://notepad-plus-plus.org/) This is free software and is used to manage text files and the creation of the sql statements.
- DBVisualizer – (https://www.dbvis.com/) – This is both a freeware utility and a purchase utility. I will be installing the freeware one for it is just the one I most comfortable with and speeds the implementation
- Install !tgateway
- This is the product that connects to the web service
- Create a dropbox folder on the c:\Drive
- This is typically used to manage the inbound XML while it is being parsed.
- C:\DROPBOX\GPS_IMPORT and C:\DROPBOX\GPS_IMPORT\ARCHIVE
- The gateway product will manage the logs and the user can set the retention of the logs
- Install !Tsuite
- This the Extraction, Transformation and Load (ETL) tool. It is comprised of several layers
- Tloader, TWatcher, Toutput, Tbatch, tmonitor, Tscheduler, Ttransform and a few other utilities all controlled from the main interface
- This is where the jobs are created to convert the XML into SQL calls to update Truckmate
- This the Extraction, Transformation and Load (ETL) tool. It is comprised of several layers
- Three services exist but only two will be used for a standard Truckmate installation
- !tgateway (monitors the web service) – GPS_IMPORT
- !Tsuite Monitor – Used to monitor the dropbox and then execute a Tloader job to lmport into Truckmate
- !Tsuite Scheduler – Used to execute Tsuite jobs on a schedule. Not used by a Truckmate installation
- Credentials for the DB2 installation
- This is the database IP, name, schema, userid and password.
- Customer usually makes this point to the test database first
- Create the GPS breadcrumb table on the DB2 instance
- This step is optional but highly recommended. It is our experience that GPS providers have more data elements than what TRUCKMATE can hold. By creating a GPS breadcrumb table for the provider, the Truckmate client has the ability to perform much richer analysis. Again this table creation is an optional step but highly recommended.
- Create jaat_closet_zone function onto db2 instance to get the closet zone in the event GPS_Import landmark is null
- Create job to load xml into GPS breadcrumb table
- Create the tloader job to load trailers and update breadcrumb history from GPS breadcrumb table
- Test Tloader jobs
- Create Tbatch job to merge into one process for efficiency
- Since the job is memory bound and windows has a way of bumping into others, we recommend a job to restart the tmonitor service nightly. This flushes all windows holds on text files and the service. It is not needed in many installations but we have found it removes many of the hidden Microsoft permission issues with text files and folders. Again optional but recommended. It is done using a task scheduler job via windows.
- Test the service till the customer is happy with the performance and updates
- Cut over to production
- Copy gps_breadcrumb table structure
- Copy jaat_closest_zone to db2 instance
- Stop the GPS_IMPORT web service
- Stop the tmonitor service
- Change database pointer from test to live
- Start the GPS_IMPORT web service
- Since most GPS_IMPORT connectors allow retrieving of history, the user can decide to backup the pointer to retrieve history in the event they want the breadcrumbs historically
- Start the Tmonitor service
- Monitor the activity to ensure all is going well.
