Developer ToolsFree generatorPDF, Excel, Word exportInfor XADiscrete Manufacturing

Infor XA DB2 for i SQL Extract Generator

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.

Fill in

Library

File (table) name

Columns (comma separated)

WHERE condition

ORDER BY column

Rows to preview

Output CSV path in the IFS

Generated code

Download:

1. Preview a limited set of rows

SELECT ITNBR, ITDSC, ITCLS
FROM XALIB.ITEMFILE
WHERE ITCLS = 'FG'
ORDER BY ITNBR
FETCH FIRST 100 ROWS ONLY;

2. Count the rows that match

SELECT COUNT(*) AS ROW_COUNT
FROM XALIB.ITEMFILE
WHERE ITCLS = 'FG';

3. Check for duplicates on the order column

SELECT ITNBR, COUNT(*) AS DUPES
FROM XALIB.ITEMFILE
GROUP BY ITNBR
HAVING COUNT(*) > 1
FETCH FIRST 50 ROWS ONLY;

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

RUNSQL SQL('CREATE TABLE QTEMP.XAEXTRACT AS (SELECT ITNBR, ITDSC, ITCLS FROM XALIB.ITEMFILE WHERE ITCLS = 'FG') WITH DATA') COMMIT(*NONE)

CPYTOIMPF FROMFILE(QTEMP/XAEXTRACT) TOSTMF('/home/export/item_extract.csv') MBROPT(*REPLACE) STMFCCSID(1208) RCDDLM(*CRLF) DTAFMT(*DLM) STRDLM(*DBLQUOTE) FLDDLM(',') ADDCOLNAM(*SYS)

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.

XA data profiling for migration

An optional review of your extracts, duplicates and data quality before a move.

Optional. The tool and every export work without this. No spam.

How to use the generator

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.

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

What each step does

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

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.

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

Frequently Asked Questions

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

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?

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?

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?

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.

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.

Talk to an Expert Explore More Free Tools

Related Tools

Other ERPs

More Developer Tools tools

See all

Back to All Tools