Accessing Physical and Logical Files

Specifying a URL

The general format of a URL that accesses a physical (PF) or logical (LF) database file is as follows:

http://server:port/qsys.lib/library.lib/db.file/member.mbr

where,

server: is the fully qualified domain name of the machine that contains the resource.
port: is the socket port on the host machine that is running the HTTP server. The :port is optional. If it is not included, it defaults to port 80.
qsys.lib: indicates that the data is contained in the QSYS file system on the AS/400.
library.lib: name of the library in QSYS that contains the database file.
db.file: the name of the PF/LF contained in the above library.
member.mbr: member in PF/LF that contains the document. If the member is not included in the URL then the first member will be returned unless the MBR keyword is used.

Determining Content Type

The content type indicates the type of data that is being returned from the server to the browser. Database files will normally contain plain text or HTML data. If the request's content type is plain text then the data by default is formatted by the server into columns with column headings.

The following order is used to determine the content type of data stored in a database file:

URL contains a valid content type extension
Defaults to plain text content type

Authority

The server user profile must have at least *USE authority to the database file and library. The library containing the database file must be available through the configured include libraries.

Supported Keywords

The following keyword parameters are supported when processing database files:

HEADER([library/]src-pf member[.html])

Used to specify a header document to include before the database data. A header can be used to set the document title and describe the database data that follows.

The first part of the header keyword value indicates the source physical file that contains the member containing the header information. Optionally, the source physical file can be pre-pended with the library containing the source physical file. The slash separating the library and src-pf should be escaped (e.g., / = %2F). The library specified must be accessible through the configured include libraries. If the library is not specified then the header source physical file must reside in the same library as the PF/LF that is being processed.

The second part specifies which member contains the header data. The data contained in the member is assumed to be plain text unless one of the following two methods is used to indicate the member contains HTML data:

.HTML is appended to the member name
Member type is set to HTML

Samples:

HEADER(SRCFILE+HEADERMBR)
HEADER(SRCFILE+HEADER.HTML)
HEADER(LIBRARY/SRCFILE+HEADERMBR)
HEADER(LIBRARY%2FSRCFILE+HEADER.HTML)

FOOTER([library/]src-pf member[.html])

Used to specify a footer document to include after the database data. A footer can be used to display summation information.

The first part of the footer keyword value indicates the source physical file that contains the member containing the footer information. Optionally, the source physical file can be pre-pended with the library containing the source physical file. The slash separating the library and src-pf should be escaped (e.g., / = %2F). The library specified must be accessible through the configured include libraries. If the library is not specified then the footer source physical file must reside in the same library as the PF/LF that is being processed.

The second part specifies which member contains the footer data. The data contained in the member is assumed to be plain text unless one of the following two methods is used to indicate the member contains HTML data:

.HTML is appended to the member name
Member type is set to HTML

The footer should not include either the <HTML> or <BODY> tag.

Samples:

FOOTER(SRCFILE+FOOTERMBR)
FOOTER(SRCFILE+FOOTER.HTML)
FOOTER(LIBRARY/SRCFILE+FOOTERMBR)
FOOTER(LIBRARY%2FSRCFILE+FOOTER.HTML)

HEADINGS(0 | 1)

Used to specify if column headings should be included in the formatted output. Valid values are:

'0' - Do not include column headings
'1' - Include column headings

If the HEADINGS keyword is not specified then by default column headings are included if the data being returned is plain text. Note that column headings can only be included when the content type is plain text.

Samples:

HEADINGS(0)
HEADINGS(1)

KEY(value)

Used to specify a key value when reading a keyed database file. The length of the key value must be less than or equal to the file's key length. If the key value's length is less than the file's key length then key matches will be performed on the key value's length number of characters. The value specified is case sensitive.

Hexadecimal characters can be included in the key by enclosing the hex characters as follows: X'chars' or x'chars' (e.g., X'00010A' which is converted to the 8-bit binary values 0,1,10 which equals 266 decimal). Hex characters can be used to search files that have numeric keys, although care must be taken to ensure the hex value matches the storage method used for the numeric field. Both character and hexadecimal values can be included in the key value.

The key value is combined with the KEYOPT keyword when processing the file. If the file is keyed and no key is specified then all records are returned.

Samples:

KEY(AAA)
KEY(X'0000001F')
KEY(X'F1F2F3F4'ABC)

KEYOPT(option)

Used to specify a 2 character processing option when reading a keyed database file. The KEYOPT determines how the specified key value is compared with the file's key. Valid values are:

EQ - Return records with a key equal
GE - Return records with a key greater than or equal
GT - Return records with a key greater than
LE - Return records with a key less than or equal
LT - Return records with a key less than

The value specified is case insensitive. If a key option is not specified and a key is specified then the key option defaults to EQ.

Samples:

KEYOPT(EQ)
KEYOPT(GE)
KEYOPT(LT)

KEY2(value)

Used to specify a secondary key value when reading a keyed database file. A secondary key value cannot be specified if the KEYOPT is set to EQ. Specifying two keys allows for a range of records to be returned. The following types of searches can be performed using 2 keys:

KEY < File Key < KEY2
KEY < File Key <= KEY2
KEY <= File Key < KEY2
KEY <= File Key <= KEY2

See the KEY keyword parameter description for more information on using key values. The key 2 value's length does not have to match the first key value's length. The key 2 value is combined with the KEYOPT2 keyword when processing the file.

Samples:

KEY2(BBB)
KEY2(X'0000009F')
KEY2(X'F1F2F3F9'ABC)

KEYOPT2(option)

Used to specify a 2 character processing option when reading a keyed database file with 2 keys. See the KEY2 keyword parameter description above for more information on using 2 keys to search a keyed file. The KEYOPT2 determines how the specified key 2 value is compared with the file's key. Valid values are dependent on the value of the KEYOPT keyword parameter:

KEYOPT is set to LE or LT, KEYOPT2 must be either:

GE - Return records with a key greater than or equal
GT - Return records with a key greater than

KEYOPT is set to GE or GT, KEYOPT2 must be either:

LE - Return records with a key less than or equal
LT - Return records with a key less than

The value specified is case insensitive. KEYOPT2 must be specified if the KEY2 keyword parameter is specified.

Samples:

KEYOPT2(GT)
KEYOPT2(LE)

COLUMNS(['prefix']numbers['suffix'])

Used to specify which columns/fields are to be returned from the database file. The columns also determine the order of the returned columns.

Optionally, a prefix can be added before the column(s) and a suffix can be added after the column(s). The prefix and suffix are text strings contained in single quotes that are outputted before and after the column(s) respectively. To insert a single quote specify two single quotes (e.g., a prefix of '''' would output one single quote). The column prefix and suffix text is never escaped. In most cases, column headings should be turned off when using prefixes and suffixes.

If multiple columns are to be returned then each column number must be separated by commas. A range of columns can be specified by separating two column numbers with a hyphen.

The SPACING keyword can be used to control spacing between columns.

Valid column numbers range from 1 to 256. Columns can be returned more than once. If COLUMNS is not specified then all columns will be returned in the file's order.

Samples:

COLUMNS(1)
COLUMNS(''1'')
COLUMNS(2,5)
COLUMNS(''2'',''''5'''')
COLUMNS(3,2,3)
COLUMNS(3,'<A HREF=%22'2'%22>',3'</A>') Note: %22 = "
COLUMNS(2-4)
COLUMNS(''2-4'')
COLUMNS(10-6,12)

SPACING(column.spacing)

Used to specify the spacing before each column that is being returned from the database file. The COLUMNS keyword can be used to control which columns are returned and add text before and/or after the columns.

Column spacing is specified by a column number and a spacing value that are separated by a period. Spacing for multiple columns must be separated by commas. A spacing value for a range of columns can be specified by separating two column numbers with a hyphen followed by the period and the spacing value.

Valid column numbers range from 1 to 256. Valid spacing values are from 0 to 256. If SPACING is not specified for a column then there will be no spaces before the first column and one space between each additional column. If multiple values are given for a single column the last once encountered will be used.

Samples:

SPACING(1.10) - Inserts 10 spaces before the 1st column
SPACING(2.5,5.3) - Inserts 5 spaces before the 2nd column and 3 spaces before the 5th column
SPACING(6-10.2,2.0) - Inserts 2 spaces before columns 6 through 10 and no spaces before column 2

NOESCAPE(numbers)

Used to specify which columns/fields data should not be escaped. By default the characters '<', '>' and '&' are escaped so the browser does not interpret the column as containing HTML tags. If the column does contain HTML tags then this keyword tells the server not to escape the special characters in the column's data.

Multiple columns can be specified by separating each column by commas. A range of columns can be specified by separating two column numbers with a hyphen.

Valid column numbers range from 1 to 256. This keyword effects only character fields.

If the content type is not plain text then by default none of the fields are escaped and specifying the NOESCAPE keyword will instruct the server to escape the specified values.

Samples:

NOESCAPE(1)
NOESCAPE(2,5)
NOESCAPE(3,2,3)
NOESCAPE(2-4)
NOESCAPE(10-6,12)

SPLF(0 | 1)

Used to specify that the database file contains a spooled file. The spooled file data must have been copied into the database file with the CTLCHAR(*PRTCTL) option on the CPYSPLF command. Valid values are:

'0' - Output the spooled file as pre-formatted data. This option uses a fixed font which maintains columns.
'1' - Output the spooled file as formatted data. This option uses a variable font and should only be used when the spooled file does not contain columns. If the line spacing is not correct it is because the browser being used does not support multiple line breaks.

If the URL indicates the database file contains HTML spooled file data then either option can be specified and the data is returned as is from the database file. Non-text content types will return the spooled file data with the printer control characters.

The following keywords are ignored when this keyword is used:

HEADINGS - Column headings are never returned for spooled files.
KEY, KEYOPT, KEY2 and KEYOPT2 - Key processing is not supported.
COLUMNS and SPACING - All columns are returned. The first 4 characters which contain control information are not returned.
NOESCAPE - All special characters are escaped unless the data being returned is identified as HTML.

Data can also be returned directly from spooled files.

Samples:

SPLF(0)
SPLF(1)

PAGES(start-end)

Used to specify which spooled file pages are to be returned.

The format of the PAGES keyword value is the starting page number followed by a hyphen and then the ending page number. If the starting page number is greater then the total number of spooled file pages then no spooled file data is returned. The ending page number may be set to the special value *LAST. The starting and ending page values are inclusive.

If PAGES is not specified then all of the pages will be returned.

Samples:

PAGES(1-*LAST)
PAGES(10-*LAST)
PAGES(5-8)

MBR(member)

Used to specify up to 10 characters for the name of PF/LF member to process. The following special values can be used:

*FIRST - Returns the first member
*LAST - Returns the last member
*ALL - Returns all of the members

The member specified is case insensitive. This keyword is ignored if the member was previously found in the URL. If there is not a member specified in the URL then the first member is returned.

Samples:

MBR(DBMBR)
MBR(*ALL)
MBR(*FIRST)

RECFMT(format)

Used to specify up to 10 characters for the name of LF record format to process. The format specified is case insensitive. If there is not a record format specified in the URL then the first record format is processed.

Samples:

RECFMT(RCDFMT1)
RECFMT(RCDFMT2)

SENDLEN(0 | 1)

Used to specify if the content length should be sent. Using the SENDLEN keyword overrides the send file content length configuration value. Valid values are:

'0' - Do not send content length
'1' - Send content length

Samples:

SENDLEN(0)
SENDLEN(1)

Samples are provided to demonstrate usage of keywords when retrieving data from database files.

Output Considerations

Requests for plain text data from database files results in the data being formatted by the server and returned as pre-formatted HTML data. If the data is identified as any other type of data (e.g., HTML data) the database file data is not formatted with HTML tags. The following lists different formatting considerations:

Columns/Fields returned

The COLUMNS keyword parameter can be used to control which fields are returned and the order of the fields. The COLUMNS keyword can also be used to add text before and/or after the column. The SPACING keyword can be used to control column spacing.

Column/Field justification

Numeric data is always right-justified and all other data is left-justified. If the maximum width of the formatted data is less than half the width of the column heading then the formatted data is centered under the column heading.

HTML tags

When returning plain text from a database file, if a field/column contains HTML tags then the NOESCAPE keyword must be used to inform the server that it should not escape the special characters '<', '>' and '&'.

The HTML "wrapper" tags are included or excluded based on the content type of the document/database file and the content type of the optional header. The HTML "wrapper" tags consist of:

<HTML>,</HTML>
<HEAD>,<TITLE>,</TITLE>,</HEAD>
<BODY>,</BODY>

The HTML "wrapper" tags are included in the following scenarios:

No header and database contains plain text
Plain text header and database contains plain text
Plain text header and database contains HTML

The HTML "wrapper" tags are not included in the following scenarios:

No header and database contains HTML
HTML header and database contains plain text
HTML header and database contains HTML

Edit codes and edit words

If a field that is being returned has an edit code/word the edit code/word is used to format the returned data. If the field does not have an edit code/word and the field is numeric then EDTCDE(1) is used. Hexadecimal data is always formatted as X'xxxxxx'.

Column headings

The text for the column heading is determined by the field definition. The first available of the following field values determines the column heading text:

Column heading (i.e., COLHDG DDS keyword specified)
Text (i.e., TEXT DDS keyword specified)
Field name

Column headings can be turned off or on using the HEADINGS keyword parameter. The HEADER keyword parameter can be used to return customized column headings.

Document Title

If there is not an HTML header the title of the document returned is set to the second-level message text of message ID (WWW0911 - normal database output, WWW0913 - formatted spool file output, or WWW0914 - pre-formatted spool file output) in the WWWMSGF message file. If a different default title is desired then change the second-level message text. If a customized title is required then include a HTML header using the HEADER keyword parameter.

NULL values

NULL field values are supported. If the field contains a NULL value then the second-level message text of message ID WWW0910 in the WWWMSGF message file is the value that is returned. The default value shipped is '*NULL'. If spaces are desired then change the '*NULL' value to ' '. If the formatted width is shorter than the message text, only the formatted width number of characters are returned (e.g., formatted width = 2 then *N would be returned).

Variable length fields

Variable length fields are supported. The width of the column is the maximum length of the variable length field.

Restrictions

Information can only be returned from one database file. If the data desired is contained in multiple files then a script will have to be written.

Data read from a database file is always converted. The retrieval of binary data (e.g., image) from a database file is not supported.

Examples

The following are examples of URLs that access database files:

http://system/qsys.lib/library.lib/database.file: Formats and returns all the fields of the first member in file LIBRARY/DATABASE. All records are returned.
http://system/qsys.lib/library.lib/database.file.html: Returns all the fields of the first member in file LIBRARY/DATABASE. No formatting is done. All records are returned.
http://system/qsys.lib/library.lib/keyed.file?KEY(AA)+KEYOPT(GT)+KEY2(AD)+KEYOPT2(LE): Formats and returns all the fields of the first member in file LIBRARY/KEYED. Only records that have a key greater than AA and less than or equal to AD are returned.
http://system/qsys.lib/library.lib/keyed.file?COLUMNS(3,1)+SPACING(3.10)+HEADINGS(0)+HEADER(SRCPF+HEADER.HTML)+FOOTER(SRCPF+FOOTER)+KEY(X'0000001F'): Formats and returns the 3rd and 1st column/field of the first member in file LIBRARY/KEYED. Ten spaces are inserted before column 3. No column headings are returned. A HTML header and footer (footer's member type is HTML) is included in the output. Only records that have key equal to packed decimal 1 are returned.