FreightMath Logo

Innovative Instructions — AS400 Extraction for FreightMath

This guide explains how to use the KSMUploadStandalone extraction utility to pull data from an Innovative system running on IBM i / AS400 and generate FreightMath-ready Order and Segment extract files. It is intended for client IT, DBA, and analytics teams who support the integration.

Prerequisite This document is a TMS-specific companion to the core FreightMath Data Schema, which defines all required Order- and Segment-level fields. Use that page for what data FreightMath needs, and this page (Innovative Instructions) for how to pull the data from Innovative on AS400.

1. Purpose & High-Level Architecture

KSMUploadStandalone is a Windows console application that:

The core business logic has been packaged into a standalone project so that IT teams can run extracts on a schedule without needing to embed this logic directly into the TMS.

2. System Requirements

Component Requirement Notes
Operating System Windows Server or Windows 10/11 Used as the extraction host; must be able to reach both AS400 and SFTP.
Innovative Access ODBC access to Innovative DB2/400 schema Typically via a dedicated service account with read access to required tables.
IBM iAccess ODBC Client IBM iAccess v1r1 Windows Application Package (or approved equivalent) Installs the iSeries ODBC driver used by the extraction utility.
ODBC DSN System DSN matching the JSON config connection string Example: dsn=AS400x64 in the connection string.
.NET Runtime .NET runtime compatible with the compiled executable Confirm with KSMTA which runtime version is required if not bundled.
Network Connectivity Outbound to AS400 (DB2/ODBC) and FreightMath SFTP Firewall must allow ODBC and SFTP traffic from the extraction host.

3. Files in the Code Package

When you download the code package (Innovative Code Package (ZIP)), you will see something similar to the following structure: 

KSMUploadStandalone/
├─ KSMUploadStandalone.exe
├─ Company - Copy.json
├─ ReportJSON/
│  ├─ OrderExtract.json
│  └─ SegmentExtract.json
├─ ReportModels/
├─ Repositories/
└─ Readme.txt

4. Company Configuration JSON

Each Innovative client requires its own company configuration JSON, which tells the extractor how to connect to AS400 and how to label its output.

Example (ClientABC.json):

{
  "CompanyName": "Client ABC Trucking",
  "CompanyAS400ConnectionString": "dsn=AS400x64;UID=FREIGHTUSR;PWD=SuperSecret123;QueryTimeout=0",
  "Library": "IFILE",
  "SCAC": "ABCT"
}

4.1 Field Definitions

Security Note: When sharing a JSON file outside the client’s environment (e.g., with KSMTA support), always remove the password from CompanyAS400ConnectionString. The client should reinsert it locally.

5. ODBC DSN Setup (Innovative on AS400)

  1. Open ODBC Data Sources (64-bit) on the extraction machine.
  2. Go to the System DSN tab and click Add....
  3. Choose the IBM i Access ODBC Driver and click Finish.
  4. Configure the connection:
    • System: AS400 hostname or IP
    • Description: e.g., Innovative Production
    • Default Library: e.g., IFILE
    • User ID / Password: Dedicated service account for reporting
  5. Name the DSN to match the JSON, e.g. AS400x64.
  6. Use the Test Connection button to confirm connectivity and credentials.

If the DSN name doesn’t match the config file’s dsn=... value, the extractor will fail with an ODBC connection error.

6. Running the Extraction Utility

The executable accepts up to three arguments: 

KSMUploadStandalone CompanySettings CutOffDate StartDate

6.1 Examples

Example 1: Full/default extract (no dates)

KSMUploadStandalone ClientABC.json

Uses internal date defaults defined in the report repository logic. Good for initial tests or small data sets.

Example 2: Extract up to a cutoff date

KSMUploadStandalone ClientABC.json 20251201

Includes records up to December 1, 2025, using internal rules for the start date.

Example 3: Extract a specific date range

KSMUploadStandalone ClientABC.json 20251201 20251101

Includes only records between November 1, 2025 and December 1, 2025 (inclusive, depending on the query filters).

Recommended pattern: For daily runs, set StartDate to the last successful run date and CutOffDate to today. This keeps extract sizes small and simplifies debugging if something goes wrong.

7. What the Utility Produces

On a successful run, the utility builds two CSV files in the working directory and then uploads them via SFTP:

Examples for SCAC ABCT on January 7, 2025: 

ABCT_KSMTA_ORDER_20250107.csv
ABCT_KSMTA_SEGMENT_20250107.csv

Column names and field definitions correspond to the FreightMath Data Schema.

8. SFTP Upload Behavior

After generating CSV files, the utility:

SFTP host, port, username, and password are managed by KSMTA. Clients are not expected to change these values.

9. Scheduling Recurring Runs

Once a manual test is successful, most clients schedule recurring runs using Windows Task Scheduler on the extraction host.

  1. Create or choose a Windows service account with:
    • Permissions to run the executable.
    • Access to the folder containing the config and outputs.
    • Network access to AS400 and SFTP.
  2. In Task Scheduler, create a new task to run KSMUploadStandalone.exe with the appropriate arguments (e.g., daily at 1:00 AM).
  3. Optionally use a small wrapper script (.bat or .ps1) to compute date arguments dynamically (yesterday, last week, etc.).
  4. Configure logging by redirecting console output to a file, for example: 
    KSMUploadStandalone ClientABC.json 20251201 20251101 >> logs\extract_2025-12-01.log 2>&1

10. Common Errors & Troubleshooting

10.1 Syntax / Usage Errors

If you run the executable with no arguments, it will display usage instructions similar to: 

Syntax: KSMUploadStandalone CompanySettings CutOffDate StartDate

Fix: Always pass at least the company JSON file name.

10.2 ODBC / Connection Errors

Typical messages include:

Possible causes:

Fix:

10.3 JSON Configuration Errors

If the company JSON is malformed or missing fields, you may see JSON deserialization errors.

Fix:

10.4 SFTP Upload Failures

Symptoms include console messages such as:

Fix:

11. Related Documentation