SQL query of multi-member file on AS400

sql jdbc db2 ibm-midrange jt400
tmtest picture tmtest · Dec 6, 2008 · Viewed 25.7k times · Source

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

Ryan Guill picture Ryan Guill · Jan 12, 2009

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

Related questions

SQL7008 Error - Workaround?

I'm using the JTOpen JDBC driver for a DB2 Universal database. I have very little experience with SQL beyond simple statements. From this question, I see that the error I'm getting (SQL7008) is thrown when trying to "insert/update rows …

Read More
How do you get values from all columns using ResultSet.getBinaryStream() in jdbc?

How do I to write an entire table to a flat file (text file) using jdbc? So far I've attempted the following: Statement statement = connection.createStatement(); ResultSet result = statement.executeQuery("SELECT * FROM tablename"); BufferedInputStream buffer; FileOutputStream out = new FileOutputStream("flatfile.…

Read More
Configure SQuirrel SQL Client to work for DB2/AS400

I use the ancient System i Navigator (french) to get to query a DB2 (AS/400) database. I liked to update the client and use a more fresh (and English) sql client for that database. So, I installed the SQuirrel SQL …

Read More