SQL Query of Multi-Member File on As400

List of source members in a file with SQL

Sadly SQL doesn't know anything about members, so all the sourcefile-info you could get from qsys2.syscolumns is, that they consist of three columns.

you want the member info and i suggest using the qshell( STRQSH ) together with a query to qsys2.systables as source files are specially marked there.

select table_schema , table_name from qsys2.systables where File_type = 'S'

i whacked together a qshell one-liner for copy&paste purposes ..

db2 -S "select '/QSYS.LIB/' concat table_schema concat '.LIB/' concat table_name concat '.FILE' from qsys2.systables where File_type = 'S'" | grep '/' | xargs -n1 find >/home/myuser/myfile                                                                            

it pipes every member it finds to the IFS directory /home/myuser/myfile you could also specify a Sourcefile member. feel free to modify to your needs.

PS: it throws errors for Sourcefiles directly sitting in /QSYS.LIB, but i think you don't want those anyway..

take care! :)

AS400 files - convert PF and LF to SQL

With System i Navigator on a PC, connect to your server and drill down into Databases and a schema. Click on either Tables or Views to list (possibly all) PFs or LFs in that library. Then, right-click the selections and select 'Generate SQL'. I suggest choosing to generate into 'Run SQL scripts'.

The result will be a SQL script with CREATE TABLE or CREATE VIEW statements for all selected objects. Most relevant LABEL statements will also be included for column attributes. You can either save the script into a .SQL file on the PC or a network share. I usually copy/paste the script rather than save it, but you'll want to experiment in order to understand how line endings (CR and LF) might be generated in your case.

Be sure to review (and possibly test) various options. Some might be better fits for your final objective.

As for multi-format LFs, I don't think I've seen them used on any system I've worked with since SQL became fully available. Since there is no good SQL equivalent, it's unlikely there is any automated method. Maybe someone knows an obscure possibility.

If your question involves a System/36 environment, edit your question to indicate so. Numerous additional comments might be needed.

AS400 SQL Script on a parameter file returns

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

Why can't my As400 select from a newly created member alias?

This error message can indicate that the file/logical file/member does not exist.

---

Related Topics