CPYSPLF output to E1 table CCSID issues.

[HolderAndrew]

Hi everybody,

First time in a while that I have posted an issue, I hope there is some guru out there that can help!

I have been working on a new development request on an E1 9.0 system on iseries to get the IFS file listing (which contains a list of inbound orders) to an E1 described table.

There are various ways to do this, but one simple way is to use the native commands DSPLNK OBJ('/IFSDir/Orders/*') OUTPUT(*PRINT) and then CPYSPLF FILE(QSYSPRT) TOFILE(LIBRARY/F55XXXX) to a file. (I have also used QSH commands to get IFS listing but ends up with same issue as described below). The F55XXXX table contains one column only having length 132 and when generated from within E1 I can see the attributes on iseries are 132G for Graphic ie. total length 264 and CCSID(13488) *CONVERT for unicode conversion.

The big problem that I have is that the CPYSPLF command only seems to work when copied to a table having non-unicode characteristics since I was getting CCSID conflict messages. So I deleted the F55XXXX and F55XXXX_1 (which were created from E1 earlier) and then created a DDS for the F55XXXX having single field defined as 132G graphic and CCSID(65535) and compiled. The CPYSPLF command above then works ok and all the spool file contents are copied ok to F55XXXX and I can see the details inside E1 ok (either in application or in UTB for example).

I have a simple UBE report R55XXXX which reads data from F55XXXX and then does some processing. The UBE R55XXXX report works perfectly when ran locally on dev client but not on the server. Debug logging from server shows no detailed information as the process terminates immediately after the “SELECT * FROM LIBRARY/F55XXXX” line of code. We have tried usual things, redeploying packages, removing sql packages, new versions etc. with no luck. I suspect that the problem is with the server failing to retrieve the data from F55XXXX due to the CCSID conflicts but I have no evidence of this.

Have you please any ideas how or why this might be happening? We did try to setup a non unicode database source and then set up OCM mapping for the F55XXXX table but this didnt work either.

Is there any other way that you know of to get the spool file contents into an E1 unicode defined table that has CCSID of 13488? Or indeed a better method to get IFS details into E1?

Any assistance would be appreciated.

Best Regards

Andrew

 

[HolderAndrew]

Hi,

I have managed to solve this issue now, i had to use the CPYSPLF to a stream file on the IFS and then I could copy that back via the CPYFRMIMPF to unicode E1 table. The error that we were getting was OS400QL010 - FetchSQLRow:Fetch failed. QSQLMSG *LIBL - SQL0332 - Character conversion between CCSID &1 and CCSID &2 not valid, as expected.

BRgds
Andrew

 

[Ian Simmons]

Hi - If you are comfortable with RPG and SQL, there is a better solution, which is to create what's called a User Defined Table Function in SQL. Basically, you write a high-level language program (e.g. in RPGLE) that accepts parameters and lists a directory virtual table in memory, and wrap it in a callable SQL function. I found one called IFSDIR written by the excellent Scott Klement, which is available here: http://iprodeveloper.com/rpg-programming/udtf-ifs-directories. (You may have to register to access it.)

It is much more reliable than a spooled listing, which may change from one OS release to the next. Now it's in SQL, it can be called and read using SQL cursors. Here's a sample piece of code that uses the function. 'Foldername' is a variable that could contain your folder:

Exec SQL
Declare FOLDERLIST Cursor For
select filename
from table(ifsdirFolderName)) as t1
where type = '*DDIR';
Exec SQL
Open FOLDERLIST;
Exec SQL
Fetch FOLDERLIST into :custfolder;
Dow SQLState = SQLNORMAL;
.
.
.
Enddo;

You can even use it in an SQL session:

select * from table(ifsdir('/IFSDir/Orders/')) as OrdersList

You get all sorts of good stuff presented as columns, such as the file name, size, type (*DIR, *DDIR, *STMF etc.), timestamps for access, modify, change, CCSID, owner.

 

[lokesh.vedium]

Hi,

I think this is fully batabase failure, could you plz see the log files.I didn't get this issue previously....

Regards,
Lokesh.V