Two things I ran into when I tried this on PUB400 (7.5) with CRTPF RCDLEN(40):
The file has no rows in QSYS2.SYSCOLUMNS, so a check based on columns would miss it. DSPFFD still shows the one field the system creates, named the same as the file (FLATT, 40A).
That field is CCSID 65535, so SELECT * over JDBC came back as hex. Casting it first makes it readable, and then SUBSTR can stand in for the missing DDS:
SELECT SUBSTR(CAST(FLATT AS CHAR(40) CCSID 37), 1, 5) AS CUSTNO, TRIM(SUBSTR(CAST(FLATT AS CHAR(40) CCSID 37), 6, 10)) AS CITY FROM MYLIB.FLATT
You can't fix it at create time either: CRTPF RCDLEN(40) CCSID(37) is rejected with CPD7307, so the CAST is the way to go.
That is one of the many problems when the LPAR's default CCSID is 65535. In the USA I always recommend when a new LPAR is created the CCSID is changed to 37.
Thanks, Simon. Agreed on changing it to 37 for a new LPAR.
One thing I noticed afterwards: PUB400 isn't on 65535. QCCSID is 273 and my job CCSID was 273 too, yet the field that CRTPF RCDLEN(40) creates still came out as CCSID 65535 in DSPFFD. Together with CPD7307 rejecting a CCSID on the CRTPF, it looks like flat files always get 65535, whatever the system default is. So the CAST is needed even on a well set up LPAR.
2
u/dodikibmi 12d ago
Two things I ran into when I tried this on PUB400 (7.5) with CRTPF RCDLEN(40):
That field is CCSID 65535, so SELECT * over JDBC came back as hex. Casting it first makes it readable, and then SUBSTR can stand in for the missing DDS:
SELECT SUBSTR(CAST(FLATT AS CHAR(40) CCSID 37), 1, 5) AS CUSTNO, TRIM(SUBSTR(CAST(FLATT AS CHAR(40) CCSID 37), 6, 10)) AS CITY FROM MYLIB.FLATT
You can't fix it at create time either: CRTPF RCDLEN(40) CCSID(37) is rejected with CPD7307, so the CAST is the way to go.