Guild Companies, Inc.  
 
Midrange Guru - Tech Tips
OS/400 Edition
Volume 2, Number 11 - February 13, 2002

SQL and Multi-member Files

Hey, Ted:

How can I use SQL to access a certain member of a DB2/400 database file from a Unix system?

-- Jim

Use the CREATE ALIAS SQL command on your iSeries machine to create distinct names for each member.

Suppose you have a physical file called PAYHIST with three members: PAY2001, PAY2000, and PAY1999.

Use interactive SQL, Operations Navigator's "Run an SQL script" database tool, or Howard Arner's SQLThing to enter the following SQL commands: 







create alias mylib/pay2001      
    for mylib/payhist(pay2001)   

create alias mylib/pay2000      
    for mylib/payhist(pay2000)   

create alias mylib/pay1999      
    for mylib/payhist(pay1999)

Now you can access the individual members from any system using select commands like these: 

select * from pay1999            
select * from pay2000            
insert into pay2001 (somecolumn) values('some value')

In these examples, I use the *SYS naming convention. If you use *SQL naming convention, adjust the examples accordingly.

If you rename a member, the alias will no longer point to it. You will need to either create a new member with the old member name or create a new alias for the renamed member.

-- Ted
Sponsored By
Business Computer Design Int'l, Inc.

See why WebSmart beat out 26 other vendors and was
Voted the BEST iSeries -- AS/400
e-Business / e-Commerce tool in the marketplace.

Free Download of the New Version. Build your own, or use / customize the 70+ Free templates and e-Commerce applications. Develop professional state-of-the-art applications in a day not months, all while using your existing skill set. Extend legacy applications.

Forget complex, expensive products that take months and months to learn, or bare bone products with minimal tools & features that make you do the work manually without HTML editing, restrict the look of your web apps, and restrict growth. Restrictions like no IFS, email or Graphics functions, session ids, persistent CGI handling and more.

WebSmart is a proven, state-of-the-art tool that is easy-to-use, requires little or no Web or iSeries400 programming, is packed with features to automate the work for you, and best of all it’s affordably priced.

Quickly deploy elegant, robust and secure B2B, B2C and browser based apps. Including: Inquiries, reporting, maintenance, wireless (XML, WML. . . ), ordering, product catalogs, shopping carts, EIS. . . .

State-of-the-art, portable PC-based design tool. Develop anywhere: Work, on the road & home and enjoy the freedom of choice.

Simply email objects to yourself or others. Unlimited end-users and unlimited developer seats. Automatically produces dynamic HTML CGI programs written in ILE/RPG. 128 bit AES encryption. Utilizes iSeries400 database and security features for scalability and reliability.

Ask for BCD’s 15-point opinion e-mail on why WebSmart is better than WebSphere TM, 1. Ability to run on smaller iSeries -- AS/400’s without having to upgrade hardware. 2. Significantly faster apps. . . .

WebSmart users include: Affinity Ins., Airways Freight, Arrow Environmental, Behr Process, Broward County Schools, Calvin Klein, D.J. Powers, Formica Corp., Goodyear, Hoshizaki America, Kauai Electric, Legacy Partners, Midwest Trophy, MSU, Oregon Dept., of Veterans Affairs, State of California, Testor Corp., Weigh-Tronix. . .

Also try CATAPULT, voted best e-mail / Automated Report Distribution Tool. FREE Downloads: WebSmart or CATAPULT

Visit http://www.BCDsoftware.com or call 630-986-0800, e-mail sales@bcdsoftware.com Trust BCD, Winner of 18 Industry Awards 20,000+ product installations - 9,000 World-wide customers.

THIS ISSUE
SPONSORED BY:
WorksRight Software, Inc.
Business Computer Design Int'l, Inc.
BACK ISSUES
TABLE OF CONTENTS
A JavaScript Version
of /COPY
SQL and Multi-member
Files
Reader Feedback and Insights: Keeping Users Informed
  Newsletters | Subscribe | Advertise | About Us | Contact | Search | Home  
  Last Updated: 2/13/02
Copyright © 1996-2008 Guild Companies, Inc. All Rights Reserved.