# IBM i (AS/400) DB2 SQL Data Extraction Script Generator

> Source: https://netray.co/tools/ibm-i-db2-sql-data-extraction-script-generator
> Publisher: Netray (netray.co), AI and integration engineering for ERP and manufacturing systems.
> Section: Free Tools

## Short answer

Generate DB2 for i SQL to inspect a file, count rows, and extract records with Julian date filters for JD Edwards World or other IBM i data.

## Category

developer

## Erp

IBM i / JD Edwards World

## Audience

- IBM i developer
- Data migration analyst
- JDE World administrator

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Library (schema)

**Key:** lib

**Type:** text

**Default:** PRODDTA

**Help:** The library that holds the physical file.

#### File (table) name

**Key:** file

**Type:** text

**Default:** F0911

**Help:** For example a JD Edwards World file.

#### Julian date column

**Key:** datecol

**Type:** text

**Default:** GLDGJ

**Help:** A date stored as a CYYDDD number, century offset then day of year.

#### From date (CYYDDD)

**Key:** from_jde

**Type:** text

**Default:** 124001

**Help:** 124001 is 1 January 2024. Add 100 per century digit: 1 means 20xx.

#### To date (CYYDDD)

**Key:** to_jde

**Type:** text

**Default:** 124366

**Help:** 124366 covers the full leap year 2024.

#### Columns to extract

**Key:** cols

**Type:** text

**Default:** GLDOC, GLDCT, GLDGJ, GLAID, GLAA, GLEXA

#### Row limit for test extract

**Key:** limit

**Type:** number

**Default:** 1000

**Blocks:**

#### 1. Confirm the file and list its columns

**Body:** SELECT COLUMN_NAME, DATA_TYPE, LENGTH, NUMERIC_SCALE, COLUMN_TEXT
FROM QSYS2.SYSCOLUMNS
WHERE TABLE_SCHEMA = '{{lib}}'
  AND TABLE_NAME = '{{file}}'
ORDER BY ORDINAL_POSITION;

#### 2. Count rows

**Body:** SELECT COUNT(*) AS TOTAL_ROWS
FROM {{lib}}.{{file}};

#### 3. Count rows in the date range

**Body:** SELECT COUNT(*) AS ROWS_IN_RANGE
FROM {{lib}}.{{file}}
WHERE {{datecol}} BETWEEN {{from_jde}} AND {{to_jde}};

#### 4. Test extract with a converted date

**Body:** SELECT {{cols}},
       CASE WHEN {{datecol}} > 0
            THEN DATE(CHAR(1900000 + {{datecol}}))
            ELSE NULL END AS CALENDAR_DATE
FROM {{lib}}.{{file}}
WHERE {{datecol}} BETWEEN {{from_jde}} AND {{to_jde}}
ORDER BY {{datecol}}
FETCH FIRST {{limit}} ROWS ONLY;

#### 5. Reconcile row counts by month (optional)

**Body:** SELECT MONTH(DATE(CHAR(1900000 + {{datecol}}))) AS MONTH_NO,
       COUNT(*) AS ROW_COUNT
FROM {{lib}}.{{file}}
WHERE {{datecol}} BETWEEN {{from_jde}} AND {{to_jde}}
GROUP BY MONTH(DATE(CHAR(1900000 + {{datecol}})))
ORDER BY MONTH_NO;

**Notes:** Run in Run SQL Scripts (ACS), STRSQL or a JDBC client. Verify library, file and column names on your release, as World file layouts differ between versions and local modifications. JD Edwards money fields are usually stored without a decimal point, so check the data dictionary for the implied decimals before using amounts. Always test with a row limit and use a read-only user.

## Content

**Intro:** Anyone who has had to move data off an IBM i knows the first problems: what columns does the file have, how many rows are there, and what does that Julian date mean? This generator produces the DB2 for i SQL for those first steps, with JD Edwards style Julian date handling built in.

**Sections:**

#### How to use the generator

**Body:** Enter the library and file name, the Julian date column, the date range and the columns you want. The tool fills five scripts: a column list from QSYS2.SYSCOLUMNS, a total row count, a count for the date range, a test extract with a calendar date added, and a monthly count for reconciliation. Copy them into Run SQL Scripts in IBM i Access Client Solutions, or into any JDBC client. Run the count queries first so you know the size before you extract.

#### How the Julian date works

**Body:** JD Edwards stores many dates as a number in CYYDDD format. C is a century offset from 1900, YY is the year and DDD is the day of the year. So 124001 is the first day of 2024 and 099365 is the last day of 1999. To convert, add 1900000 to get a seven digit year plus day number, such as 2024001, which DB2 for i accepts as a Julian date string. The script guards against zero so that empty dates do not raise errors.

**Bullets:**

- C = 0 for 19xx, 1 for 20xx
- 124001 = 2024, day 1
- A value of 0 means no date

#### Common mistakes

**Body:** People most often forget the implied decimals on amount fields, so a value of 12550 may be 125.50. They also run a heavy extract during the batch window, or against a production library without a row limit. Another mistake is exporting without reconciling: compare row counts and control totals by period against the source before and after the move. Use the monthly count script for that. For very large files, extract in date slices and keep the same selection rules for each run.

**Cta:** If you need an extraction pipeline for a full migration, with reconciliation and a read-only approach, Netray can discuss it. These scripts work without contact.

**Faq:**

#### How do I convert a JDE Julian date in SQL?

**Answer:** Add 1900000 to the CYYDDD value and cast the result to a character string, then pass it to DATE. For example, DATE(CHAR(1900000 + GLDGJ)). Guard against zero values, which represent blank dates.

#### Where do I run these queries?

**Answer:** In IBM i Access Client Solutions Run SQL Scripts, in STRSQL on a 5250 session, or through a JDBC or ODBC client. Use a user with read-only authority to the data library for safety.

#### Why are my amounts wrong by a factor of 100?

**Answer:** JD Edwards stores amounts as integers with implied decimals, often two. Check the file definition or the data dictionary for the decimal places and divide accordingly in your extract.

#### Can I export the result to CSV?

**Answer:** Yes. Run SQL Scripts can save results to a file, and many tools can write CSV from a JDBC connection. For large volumes, use a scripted export in slices and verify counts against the source.

## Search terms

db2 for i sql examples, as400 sql extract, ibm i sql data extraction, jde world julian date sql, qsys2 syscolumns query, as400 export data to csv sql, db2 for i julian date conversion, ibm i run sql scripts

---
Talk to an engineer: https://netray.co/contact?intent=erp_ai | Machine catalog: https://netray.co/agent/catalog.json