AS400 SQL Script on a parameter file returns

Question

I'm integrating an application to the AS400 using Java/JT400 driver. I'm having an issue when I extract data from a parameter file - the data retrieved seems to be encoded.

SELECT SUBSTR(F00001,1,20) FROM QS36F."FX.PARA" WHERE K00001 LIKE '16FFC%%%%%' FETCH FIRST 5 ROWS ONLY

Output

00001: C6C9D9C540C3D6D4D4C5D9C3C9C1D34040404040,  - 1
00001: C6C9D9C5406040C3D6D4D4C5D9C3C9C1D3406040,  - 2

How can I convert this to a readable format? Is there a function which I can use to decode this?

On the terminal connection to the AS400 the information is displayed correctly through the same SQL query.

I have no experience working with AS400 before this and could really use some help. This issue is only with the parameter files. The database tables work fine.

There is likely no code, other than the shown SQL. This happens on any output of a given column in primarily ASCII SQL Clients like SQuirreL. It generally has to do with a CCSID error like using 65535 which means do not convert this. What is the CCSID in the fields that are displaying as hex characters? What is the system default CCSID? — jmarkmurphy, Oct 25 '18 at 12:37
I was running the sql through a small java program using jdbc and jt400 connector — Hetti, Oct 26 '18 at 10:48

score 2 · Accepted Answer · answered Oct 25 '18 at 16:02

2

What you are seeing is EBCDIC output instead of ASCII. This is due to the CCSID not being specified in the database as mentioned in other answers. The ideal solution is to assign the CCSID to your field in the database. If you don't have the ability to do so and can't convince those responsible to do so, then the following solution should also work:

SELECT CAST(SUBSTR(F00001,1,20) AS CHAR(20) CCSID(37))
FROM QS36F."FX.PARA"
WHERE K00001 LIKE '16FFC%%%%%'
FETCH FIRST 5 ROWS ONLY

Replace the CCSID with whichever one you need. The CCSID definitions can be found here: https://www-01.ibm.com/software/globalization/ccsid/ccsid_registered.html

answered Oct 25 '18 at 16:02

Player1st

1,575
10
14

I have come across another encoding which I am not familiar with in this same setup. I cant seem to convert the following "706F707F405F717F000F000F000F000F000F000F000F000F000F000F709F715F000F721F000F000F000F000F000F000F739F000F000F40404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040404040" when I convert this from CCSID 37 I get "???" ¬É" " . Does anyone recognize this format? and how could i convert it? thank you – Hetti Apr 04 '19 at 10:36
This should be put in its own question. By pasting it in the comment, I'm the only one likely to see it. That said, this encoding is not familiar to me. I'm going to guess this is a wide character encoding. Possibly UTF-16 unicode? – Player1st Apr 04 '19 at 23:14

score 1 · Answer 2 · answered Oct 25 '18 at 14:13

1

Since the file is in QS36F, I would guess that the file is a flat file and not externally defined ... so the data in the file would have to be manually interpreted if being accessed via SQL.

You could try casting the field, after you substring it, into a character format.

(I don't have a S/36 file handy, so I really can't try it)

answered Oct 25 '18 at 14:13

David G

3,940
1
22
30

1

FWIW: The data you're seeing appears to be the EBCDIC hex representation of the file data. The first record translates into 'FIRE COMMERCIAL'. – David G Oct 25 '18 at 14:15

Joop Eggen · Answer 3 · 2018-10-25T14:47:22.873

It is hex of bytes of a text in EBCDIC, the AS/400 charset.

static String fromEbcdic(String hex) {
    int m = hex.length();
    if (m % 2 != 0) {
        throw new IllegalArgumentException("Must be even length");
    }
    int n = m/2;
    byte[] bytes = new byte[n];
    for (int i = 0; i < n; ++i) {
        int b = Integer.parseInt(hex.substring(i*2, i*2 + 2), 16);
        bytes[i] = (byte) b;
    }
    return new String(bytes, Charset.forName("Cp500"));
}

passing "C6C9D9C540C3D6D4D4C5D9C3C9C1D34040404040".

Convert the file with Cp500 as charset:

Path path = Paths.get("...");
List<String> lines = Files.readAllLines(path, Charset.forName("Cp500"));

For line endings, which are on AS/400 the NEL char, U+0085, one can use regex:

content = content.replaceAll("\\R", "\r\n");

The regex \R will match exactly one line break, whether \r, \n, \r\n, \u0085.

This definitely would have come in handy if the CAST option didn't work. Thank you — Hetti, Oct 26 '18 at 10:58

score 0 · Answer 4 · answered Oct 26 '18 at 10:47

A Big thank you for all the answers provided, they are all correct.

It is a flat parameter file in the AS400 and I have no control over changing anything in the system. So it has to be at runtime of the SQL query or once received.

I had absolutely no clue about what the code page was as I have no prior experience with AS400 and files in it. Hence all your answers have helped resolve and enlighten me on this. :)

So, the best answer is the last one. I have changed the SQL as follows and I get the desired result.

SELECT CAST(F00001 AS CHAR(20) CCSID 37) FROM QS36F."FX.PARA" WHERE K00001 LIKE '16FFC%%%%%' FETCH FIRST 5 ROWS ONLY

00001: FIRE COMMERCIAL , - 1 00001: FIRE - COMMERCIAL - , - 2

Thanks once again.

Dilanke

AS400 SQL Script on a parameter file returns

4 Answers4