# Infor XA DB2 for i SQL Extract Generator

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

## Short answer

Fill in library, file, columns and filter to get a limited SELECT, a row count check and a CPYTOIMPF command to produce a CSV.

## Category

developer

## Erp

Infor XA

## Industry

Discrete Manufacturing

## Audience

- XA administrator
- IBM i developer
- Migration analyst

## Tool

**Kind:** template

**Output:** code

**Language:** sql

**Fields:**

#### Library

**Key:** library

**Type:** text

**Default:** XALIB

#### File (table) name

**Key:** file

**Type:** text

**Default:** ITEMFILE

#### Columns (comma separated)

**Key:** columns

**Type:** text

**Default:** ITNBR, ITDSC, ITCLS

#### WHERE condition

**Key:** where

**Type:** text

**Default:** ITCLS = 'FG'

#### ORDER BY column

**Key:** order

**Type:** text

**Default:** ITNBR

#### Rows to preview

**Key:** limit

**Type:** text

**Default:** 100

#### Output CSV path in the IFS

**Key:** outfile

**Type:** text

**Default:** /home/export/item_extract.csv

**Blocks:**

#### 1. Preview a limited set of rows

**Body:** SELECT {{columns}}
FROM {{library}}.{{file}}
WHERE {{where}}
ORDER BY {{order}}
FETCH FIRST {{limit}} ROWS ONLY;

#### 2. Count the rows that match

**Body:** SELECT COUNT(*) AS ROW_COUNT
FROM {{library}}.{{file}}
WHERE {{where}};

#### 3. Check for duplicates on the order column

**Body:** SELECT {{order}}, COUNT(*) AS DUPES
FROM {{library}}.{{file}}
GROUP BY {{order}}
HAVING COUNT(*) > 1
FETCH FIRST 50 ROWS ONLY;

#### 4. Create a work file and export it to CSV (run from a CL command line)

**Body:** RUNSQL SQL('CREATE TABLE QTEMP.XAEXTRACT AS (SELECT {{columns}} FROM {{library}}.{{file}} WHERE {{where}}) WITH DATA') COMMIT(*NONE)

CPYTOIMPF FROMFILE(QTEMP/XAEXTRACT) TOSTMF('{{outfile}}') MBROPT(*REPLACE) STMFCCSID(1208) RCDDLM(*CRLF) DTAFMT(*DLM) STRDLM(*DBLQUOTE) FLDDLM(',') ADDCOLNAM(*SYS)

**Notes:** File and column names in this tool are placeholders. XA files often have short system names, so run DSPFFD on the real file first to get exact names, and check whether your release uses a company or plant field you must filter on. Steps 1 to 3 run in STRSQL or an SQL client, and step 4 is run on a command line. Test against a copy library before querying production at busy times.

## Content

**Intro:** Pulling data out of an Infor XA system for a spreadsheet, an audit or a migration usually means DB2 for i SQL and a copy to a stream file. This generator builds the preview, count, duplicate check and CSV export commands from five inputs.

**Sections:**

#### How to use the generator

**Body:** Find the real file and column names first with DSPFFD, because XA field names are short and not always intuitive. Enter the library, file, columns, filter and an output path in the integrated file system. Run the preview to confirm the data, the count to size the extract, the duplicate check to test your key, and then the export to produce a CSV you can download.

**Bullets:**

- Get exact names with DSPFFD
- Always preview with FETCH FIRST
- Check the key for duplicates before loading elsewhere

#### What each step does

**Body:** The preview limits rows so a wrong filter does not scan and print a large file. The count tells you the size of the extract and lets you reconcile against the target after loading. The duplicate check shows whether the column you plan to use as a key is truly unique. The export builds a work table in QTEMP and uses CPYTOIMPF to write quoted, comma delimited text with column names in UTF-8.

#### Common mistakes

**Body:** Forgetting company or plant fields is the most frequent cause of wrong counts, since one file may hold several entities. Character fields padded with spaces cause surprises in comparisons, so trim in your SQL where needed. Packed and zoned numbers with implied decimals can look wrong when exported raw, so cast or divide as appropriate and compare totals with an XA report.

**Bullets:**

- Filter on company or plant where the file has one
- Trim padded character fields
- Reconcile totals with a known XA report
- Run big extracts outside peak hours

**Cta:** If you are extracting XA data for a migration, we can help profile it, find duplicates and load it into the target system or a read only query layer.

**Faq:**

#### Can I run these queries from Excel or an SQL client?

**Answer:** Yes. Steps one to three work from STRSQL or any client using an IBM i ODBC or JDBC driver. Step four uses CL commands, so run it from a command line or a CL program, then download the resulting file from the IFS.

#### Why use FETCH FIRST?

**Answer:** It limits the number of rows returned, so a mistake in the filter does not produce a huge result and slow the system. Use it while developing a query, then remove it for the final extract.

#### How do I get column names?

**Answer:** Run DSPFFD on the file to list field names, types and lengths. You can also query the catalog view for columns in the library, which gives names and data types you can paste into the generator.

#### Will this change any data?

**Answer:** No. The queries only read, and the export writes to a temporary table and a stream file. Even so, run them with a profile that has read only authority to the production files as a safety measure.

## Search terms

db2 for i sql select example, as400 sql to csv, cpytoimpf csv example, mapics data extract, infor xa data extraction, ibm i fetch first rows, as400 export to csv, legacy erp data extract sql

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