SQL query of multi-member file on AS400

Question

On AS400 in interactive SQL in a 5250 session,

select * from myfile

returns rows from one member only when myfile has more than one member.

How can I get rows from a specific member?

Important: in the end I'd like to do this over JDBC with jt400 so really I want a solution that'll work there.

Thanks.

Deutsch

Answer 1

You can create an alias using the create alias command:

CREATE ALIAS myLibrary/myAlias FOR memberLibrary/memberFile(memberName)

This will allow you to run sql against that member using the alias like you would any other file:

SELECT * FROM myLibrary/myAlias

Just remember that the alias will stick around after your session, they are not temporary. So if you wont need the alias when you are done, either create the alias in QTEMP or explicitly drop the alias once you are done with it:

DROP ALIAS myLibrary/myAlias

HTH

Skiff

Thank you! I knew it could be down, now all I need is to query all the members. – Rustication

Answer 2

Create an SQL alias for the member and query the alias, see this page for an example.

Tack

Answer 3

SQL Alias

OS/400 R430 and later support an SQL alias statement. Create an alias for each member that must be accessed, then reference the alias from the application. The alias is a persistent object -- it must be created only once. The member referenced in the CREATE ALIAS does not have to exist when the ALIAS is created. Any SQL tool, such as OS/400 or i5/OS interactive SQL (STRSQL) or iSeries Navigator's Run SQL Scripts, can be used to create the alias, for example:

CREATE ALIAS MYLIB.FILE1MBR1 FOR MYLIB.MYFILE(MBR1) 
CREATE ALIAS MYLIB.FILE1MBR2 FOR MYLIB.MYFILE(MBR2)

http://www-01.ibm.com/support/docview.wss?uid=nas1f1eaeecc0af19cc38625669100569213

Electrometallurgy

Answer 4

This is an old thread and still the first on the search results list, I would like to enhance the previous responses:

Sometimes you only need to create the alias as a one time Database analysis, then you want to create the alias and drop it off immediately after your query; you also have a Data Base library with many tables and don't want to qualify the library in every query so you use SET SCHEMA; I love to use the QTEMP library to create alias since QTEMP by nature on IBM i AS400 servers is for temporary objects:

set schema=mylibrary;

create alias qtemp.aliasx for table1(membera);
create alias qtemp.aliasy for table2(memberb);

select  * from qtemp.aliasx;
select  * from qtemp.aliasy;

drop alias qtemp.aliasx;
drop alias qtemp.aliasy;

Adne

Answer 5

This query runs on IBM i 7.4, but should work for some earlier releases as well.

SELECT SYSTEM_TABLE_SCHEMA AS "Lib",
SYSTEM_TABLE_NAME AS "File",
SYSTEM_TABLE_MEMBER AS "Member",
SOURCE_TYPE AS "SrcType",
TO_CHAR(NUMBER_ROWS,'999G999G999G999') AS "Record",
TO_CHAR(DATA_SIZE,'999G999G999G999') AS "Size",
PARTITION_TEXT AS "Text",
CREATE_TIMESTAMP AS "CreatedDate",
LAST_SOURCE_UPDATE_TIMESTAMP AS "LastUpdated" FROM QSYS2.SYSPARTITIONSTAT
WHERE SYSTEM_TABLE_SCHEMA = 'XYZLIB' and
SYSTEM_TABLE_NAME = 'XYZFILE'
AND SOURCE_TYPE IS NOT NULL
ORDER BY LAST_SOURCE_UPDATE_TIMESTAMP

Etra