Database File Support

One of the AS/400's most prominent uses is handling large, strategic databases. Web Server/400 provides an easy way of presenting the contents of these databases to a remote user. The presentation is appealing and easily accessible by any user with a Web browser. Database support is available for source physical files, physical files, and logical files as well as distributed database management files.

Source Physical Files

Source physical file members are normally used to store program source and file definitions. Source physical file members can also be used to store Web content that is in plain text form or marked up using HTML. Here some examples of what can be done with source physical files:

It is possible to access any member through the use of URLs. For example, the source physical file SPFDBSAMPL has 2 members (named MEMBER1 and MEMBER2) which can be accessed via the URLs:
- /QSYS/WWWSAMPLES/SPFDBSAMPL/MEMBER1.html
- /QSYS/WWWSAMPLES/SPFDBSAMPL/MEMBER2
It is also possible to retrieve members with the MBR keyword. This not only allows the access to named members, but also to special members which correspond to the first, last, and every member available. Here are some examples of the use of MBR keyword:
Plain text documents can also be retrieved from source physical file members. You will notice that plain text documents are indeed plain compared to HTML documents. Here are some examples:
- /QSYS/WWWSAMPLES/QSYSSAMPLE/PLAINTEXT
- /QSYS/WWWSAMPLES/QSYSSAMPLE?MBR(CLPGM)

For more information on retrieving Source Physical File members please see the documentation.

Physical and Logical Files

Physical and logical files are used to store application/business data on the AS/400. Web Server/400 supports returning formatted data (including HTML) that is stored in keyed or non-keyed database files. The server also supports querying data that is stored in keyed files. Here are some examples of what can be done with physical and logical files:

Like source physical files, physical and logical files can be accessed through the use of URLs. Here is an example of retrieving all the records from a customer database file:
- /QSYS/WWWSAMPLES/CUSTOMERS
The output would look better though if an explanation was added to what is being viewed. This can be accomplished by using the HEADER or FOOTER (or both) keyword. For example here is the customers database with a header added:
- /QSYS/WWWSAMPLES/CUSTOMERS? HEADER(HEADERS+CUSTOMERS.HTML)
And here is a receivables database file with both a header and footer added:
- /QSYS/WWWSAMPLES/RECEIVE? HEADER(HEADERS+RECEIVE)+FOOTER(FOOTERS+FOOTER)
Another useful feature of Web Server/400's handling of physical and logical files is the ability to use key values to limit output. This is accomplished with the use of the KEY, KEYOPT, KEY2 and KEYOPT2 keywords. The following examples retrieve records from the customers database file through a logical file that is keyed on the customer's name:
- /QSYS/WWWSAMPLES/CUSTNAME? HEADER(HEADERS+CUSTNAME1.HTML)+ FOOTER(FOOTERS+FOOTER)+ KEY(A)+KEYOPT(EQ)
- /QSYS/WWWSAMPLES/CUSTNAME? HEADER(HEADERS+CUSTNAME2)+ FOOTER(FOOTERS+FOOTER.HTML)+ KEY(AA)+KEYOPT(GT)+KEY2(K)+KEYOPT2(LE)
In this example the customers that have a customer number less than 005 are returned. The customers physical file is keyed by customer number which is a packed decimal field. To specify a numeric KEY value it must be passed as a hex value as follows:
- /QSYS/WWWSAMPLES/CUSTOMERS? HEADER(HEADERS+CUSTOMERS2)+ FOOTER(FOOTERS+FOOTER)+ KEY(X'00005F')+KEYOPT(LT)
It is also possible to limit and order the fields that are returned by an inquiry with the COLUMNS keyword. Thus from the customers database, it is possible to get a simple list of customer names and numbers with this URL:
- /QSYS/WWWSAMPLES/CUSTNAME? HEADER(HEADERS+CUSTNAME3)+ FOOTER(FOOTERS+FOOTER)+COLUMNS(2,1)
In this example a list of customers that owe money are returned. The customers database is joined with the receivables database by customer number using a logical file. The following URL returns the customers that owe money:
- /QSYS/WWWSAMPLES/NAMEANDREC? HEADER(HEADERS+CUSTANDREC.HTML)+ FOOTER(FOOTERS+FOOTER)+ KEY(X'00000000000F')+KEYOPT(GT)
The following example returns all of the customers that are in good standing. The column headings are turned off with the HEADINGS keyword and the column spacing is changed using the SPACING keyword.
- /QSYS/WWWSAMPLES/NAMEANDREC?HEADINGS(0)+SPACING(1.10,2.3,3.0)+ HEADER(HEADERS+CUSTGOOD)+KEY(X'00000010000F')+KEYOPT(LE)
This example displays a database file that has a field that contains HTML data. The NOESCAPE keyword is used to inform the server a field contains HTML data. The following URL returns sample server errors:
- /QSYS/WWWSAMPLES/SAMPERRORS? COLUMNS(2,3)+NOESCAPE(2)+SPACING(3.3)+HEADINGS(0)+ HEADER(HEADERS+SAMPERRORS)
The following example returns sample server errors in list form using the prefix option of the COLUMNS keyword:
- /QSYS/WWWSAMPLES/SAMPERRORS? COLUMNS('<LI>'2,3)+NOESCAPE(2)+SPACING(3.3)+HEADINGS(0)+ HEADER(HEADERS+SAMPLIST)+FOOTER(FOOTERS+SAMPLIST)
These examples display spooled file data that was copied into a database file. The SPLF keyword is used to indicate the database file contains spooled file data and control the formatting of the data. The following URLs return spool file data from a database file:
- /QSYS/WWWSAMPLES/SPLFSAMPLE?SPLF(0)
- /QSYS/WWWSAMPLES/SPLFSAMPLE?SPLF(1)
This example displays the CUSTOMERS database file's file description. The file description was printed and then copied into a database file. The SPLF(0) keyword value is used to retain the format of the output:
- /QSYS/WWWSAMPLES/SPLFCUST?SPLF(0)
This example displays only the first two pages of the CUSTOMERS database file's file description. The PAGES keyword is used to control the pages returned:
- /QSYS/WWWSAMPLES/SPLFCUST?SPLF(0)+PAGES(1-2)

For more information on retrieving data from Physical and Logical Files please see the documentation.