Information Systems:Important files on Bart

Overview

This page is a list of important or commonly used files (physical or logical) in our DB2 database on Bart, categorized by area of relevance. To do your own digging, remember that IBM provides a file/table that lists and provides detailed info for all files in the system (all objects that are of type *FILE). This file is 'SYSTABLES' in QSYS2. Query it using SQL, filtering by table_schema and/or table_text

SQL and IBM i DB2 terminology

It is very important to be aware of the differences in the way database elements are referred to between the SQL and IBM worlds. Otherwise, it can be very confusing when you hear these terms, especially if you come from a PC background, where a file has always been something you can click on in Windows Explorer that has an extension, and can be opened with whatever associated program is specified for that extension. Take the time to understand the following table:

IBM	SQL	Layman's terms
Database	Database	We have 1 database: S1007B50. It's a DB2 database (for comparison, other types include Microsoft SQL Server, MySQL, and Postgresql).
Library	Schema	A collection of files/tables. Similar to a PC folder.
Physical file	Table	Contains data. Object type is PF.
Logical file	View	An alternate organization of the data in a PF/table, according to an index. "Points" to the data, does not contain the actual data. Modifying a logical file will thus affect the physical file.
Record	Row	When you open a file in DFU, you page through records. Those records appear as rows when viewed through SQL.
Field	Column	When making an edit in DFU, it applies to a single field in a single record. When using the SQL UPDATE command, you must filter for a single record using a where clause. There are advantages to each method.

Important Libraries

UP1480BFVA UWDASWPRDD

Important Files

Accounting

File	Library
SROLOGGL	UP1480BFVA	The G/L file. This file is massive.
SRODAT	UP1480BFVA	A/R transactions.
SROKBP	UP1480BFVA	Batch payments interface file.
Example	Example	Example
Example	Example	Example

Carrier Interface

File	Library
CIWAYBILLP	UWDASWPRDD	More or less, the "master file" in Carrier Interface. This is the file that is written to upon receiving invoices and waybill data from carriers. Lots of records and fields in this one.
Example	Example	Example
Example	Example	Example
Example	Example	Example
Example	Example	Example

WebSmart: Infonet and Web Orders

File	Library
WSUSERP	WEBPRDD	Web Orders users. Login credentials, pharmacist numbers, Web Orders authorities specified in thsi file. This is the file read by the menu option '60 - Web Interface' in Extensions.
INUSERP	WEBPRDD	Infonet users. Includes Infonet authorities.
PW_SESSF	XL_WEBSPT	Contains valid session IDs and their expirations.
PW_SVARF	XL_WEBSPT	Contains smurfs associated with a valid smurf ID / session ID.