how is an sql select accomplished on a member file?

[biggexpress]

I've got a member that I'd like to run sql commands against and do not have command line access due to this system being an ASP. My only way to retrieve information is via IBM client access tools or using odbc connections. I prefer the later to use with web pages for many reasons.

I have a library called "ibger7" it contains a file called "arfile". "ARFILE" has a member called "arhst". This is the member that I'm trying to access.

Anyway, here is what I've tried:

select * from arhst
select * from arfile.arhst
select * from arfile(arhst)
select * from ibger7.arfile.arhst
select * from ibger7.arfile(arhst)
select * from IBGER7/ARFILE.ARHST
select * from IBGER7/ARFILE(ARHST)

I can't seem to figure it out. Given that I don't have command line, is there anything I can do with a SQL statement to accomodate my needs!?

Thanks,
Ryan

 

[stevexff]

Confused by your terminology - how do the terms 'library', 'file', and 'member' equate to DB2 tables?

 

[biggexpress]

Well, this question you ask me either makes me thing that I don't know a single thing about the as400 file system or you are someone just inquiring on my topic.

Either way, I admit that I'm not a 400 guru and know basic things about system administration and configuration. We had a 400 for about 4 years in house until we outsourced it with our main software provider due to our rapid growth and not enough time to get another 400 in house.

With that said, maybe I didn't explain it precisely to you r liking. Most 400 people that I have talked seem to talk about everything in terms of files, members, and libraries rather than databases/tables and directories.

I wasn't wanting to make this question this complex and complicated, but I guess I'm just to profoundly dumb to make it any other way.

Basically for years I've been writing vbscript/asp to run queries and programs against data stored in tables. I've been doing this simply through dsn connections that I have configured to point to a sql default "LIBRARY" where my tables are. This word "LIBRARY" comes from the odbc driver's select tab. If you look, you will see it too. Anyway, once this connection is done, I can write my programs to query the data and actually do something with it or use other tools like CuteSQL to query it.

Until this time I haven't had a need to query a file "MEMBER". I say "MEMBER", because this is the only way its been described to me. Also when doing a "client access data transfer from as/400", you select these members and it looks like this:

File Name: (LIBRARY_NAME)/FILE_NAME(XXXXXXXXX)

Again, if you have Client Access software you can do this too and see exactly what I see.

These are all the reasons that I have written my original question the way I have. I hope it makes sense now and it actually makes more sense to me now. IBM talks in terms of Libraries, files, and members. Not once in any of thier software do they talk about directories databases or tables.

Now... back to my original question...

DOES ANYONE KNOW NOW TO SELECT A FILE MEMBER AND DO A SQL QUERY ON IT???

Thanks,
Ryan

 

[gregsimpson]

ryan,

I don't know a single thing about the as400 file system. However I have only ever referred to db2(regardless of platform), in terms of databases and tables, so am somewhat surprised by your reply to stevexff.

The following link makes comparisons between the two different ways of holding data, the one you mention and the db2 way using tables, all on the as400.

http://www-106.ibm.com/developerworks/db2/library/techarticle/0211chong/0211chong.html

Your statement "IBM talks in terms of Libraries, files, and members. Not once in any of thier software do they talk about directories databases or tables", you will see is contradicted on this link and many millions of web pages.

I think you will be hard pressed to find any technical documentation as regards DB2, which doesn't mention tables, as the whole concept is table based.

In fact should you use the keyword search on this DB2 forum and simply enter the word "table", you will be greeted by the message

"Hundreds of records matched your query of table. Outputting records 1 to 20."

I appreciate the answer stevexff didn't crack your particular problem, he was merely asking you to explain a little clearer. Given the tone of your response I would be surprised if anyone else bothers to assist you.

Cheers
Greg Simpson

 

[biggexpress]

Considering this is an IBM/DB2 forum, I would have thought by some chance someone in here might be familar with an AS/400. This assumption was wrong I guess. I admitted that I wasn't a keen AS/400 administrator and am by far an expert database admin.

IBM tends to throw terms around that have other meanings and I'm merely trying to accomodate these terms in which other people may be accustomed. If you have installed any version of "Cleint Access Express", then you will understand the terms that I use.

I could be very wrong, but here is what I understand:

Libraries can be thought of as directories without subdirectories. They only have one level.

Files cover a wide range of types of data on the 400. They can be databases, objects, members, physical files, logical files, keyed logical file, and I'm sure some I forgot about and/or haven't listed.

Members... I'm not sure how to describe these. I would just simply call these a substrate of specific types of Files.

The page you pointed me to at

http://www-106.ibm.com/developerworks/db2/library/techarticle/0211chong/0211chong.html

does make some reference to these things I describe. There is a particular table that does a "DB2 UDB for iSeries DDS vs SQL concepts and terminology comparison". This I believe is why terminology is skewed. Again, its not my terminology... its Big Blue's.

Sorry if my first reply came off to harsh, I just wanted to explain myself as well as I possibly could in order to get my original question answered without having to go back and forth over symantecs of something someone may or may not understand and more so. MORE SO... I wanted to explain everything that I was or wasn't understanding about the AS/400.

Maybe my "tone" is a little lighter in this response?

heh
lol
lmao

maybe those help too

 

[MdnghtPgmr]

biggexpress,

You are close. I've been on AS/400-iSeries-I5 since they came out and it is confusing. In DDS (where I do my work) the terms are LIBRARY, FILE, MEMBER, RECORD, FIELD. In SQL (where I'm just starting to learn) the terms are COLLECTION, TABLE, none, ROW, COLUMN. I think...

I think I saw a posting on another site (iSeriesNetwork.com) where SQL does not do members. You may need to look into doing overrides.

Members can be thought of as "inside" physical files and then records are "inside" members. All physical files have at least one member. Most just have one and is named the same as the file.

Logicals (DDS word) are VIEWS in SQL terms. Logicals are said to "lay over" a physical file. Physicals and logicals can be keyed by multiple feilds within the file. Logicals can take this a step further by both changing the key fields and by selecting or omitting records based on the contents of one or more fields.

I hope this helps more than it confuses. Bottome line is I would put some time into finding out if you can use file database file overrides (see OVRDBF command) with SQL.

HTH,
MdnghtPgmr

 

[blom0344]

Bottomline for getting member-data using ODBC:
Create logical over the member and address the logical instead. I used this a number of times and believe it is the only way to get the job done..............

T. Blom
Information analyst
tbl@shimano-eu.com

 

[biggexpress]

I believe you are correct. I did happen to find a logical view of the member that was already created and waiting on me. Thanks for this future insight though! It is really appreciated. I also hope this whole thread has helped people to understand the as/400 file system more and how different IBM thinks than the rest of the world sometimes. The 400 is really a powerful system and should be understood more. Also, its probably used more than anyone thinks it is...behind the scenes...the core of most large companies.

 

[leveyp]

If you have the correct permissions you could also use a
slq "Create alias" statement which allows you to specify the member. This creates a DDM file (a pointer to a data file) on the i-series which you can then use with SQL.

 

[biggexpress]

leveyp, can you please give an example of a select statement with "create alias"? I've given it a shot, but don't think I have the syntax correct.

 

[leveyp]

Hi biggexpress.

OK. Assume I have Library(Collection) 'LEVEYP' with a File (Table) 'EMPLOYEE'. This file has a number of members; MEMBER01, MEMBER02, etc. If you run SQL against the table you will see the records in the first member. To create an alias use something like:
CREATE ALIAS LEVEYP.EMPL_02 FOR LEVEYP.EMPLOYEE (MEMBER02)
I would stick to max 8 chars for the alias name so that it remains the same when seen using OS/400 commands.
Please bear in mind that the 'CREATE ALIAS'alias does actually create an OS/400 object (speak nicely to your neighbourhood techie if you need permission granting) and that you can create an alias for a non-existant member.
Once the alias has been created you can then use
SELECT * FROM LEVEYP.EMPL_02
Hope this helps.