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. |