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.

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

Answer 2

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