Information Systems:Important files on Bart
Overview
This page is a list of important or commonly used files and libraries in our DB2 database on Bart, categorized by area of relevance. To do your own digging, remember that IBM provides a 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 in the SQL and IBM worlds. Otherwise, it can be very confusing when you hear these terms being thrown interchangeably, 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 | Description |
|---|---|---|
| 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 | Self-explanatory - the object that contains data. Object type is PF. |
| Logical file | View | An alternate organization of the data in a PF/table, according to some
specified index. A logical file "points" to the physical data and does not actually "contain" the data as a separate copy. Thus, when editing a logical file, the physical file and all other logical files are affected, and vice versa. |
| Record | Row | When you open a file in DFU, you page through records. Those records constitute the rows of a table in SQL. |
| Field | Column | When making an edit in DFU, you edit a single field, which belongs to a single record. When using the SQL UPDATE
command, you must explicitly filter for a single record using a where clause. There are advantages to using each method to make changes to files. |
Important Libraries
The descriptions below apply for the most part, but there may be some exceptions i.e. different developers may have preferred to work in different libraries.
Data Libraries
UP1480BFVA
- Contains "core" ASW files e.g. SROxyz etc.
UWDASWPRDD
- Contains files related to UWD Extensions (i.e. CNS, EDI tables).
- Files for additional extensions (after the Unity project i.e. developed by Sheila) are also contained within this library.
UNIPHARMIT
- Contains source code (QRPGLESRC and QCLSRC members) and the programs themselves for all overnight processes
Important Files
Accounting
| File | Library | |
|---|---|---|
| SROLOGGL | UP1480BFVA | The G/L file. This file is massive (100M+ records) and logs all G/L transactions. |
| SRODAT | UP1480BFVA | A/R transactions. |
| SROKBP | UP1480BFVA | Batch payments interface file. |
| SROOVF | Example | Example |
| Example | Example | Example |
Carrier Interface
| File | Library | Description |
|---|---|---|
| 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. |
| CIINVP | UWDASWPRDD | Contains invoice summary information from the carriers we download invoices from.
The more detailed information sits in CIWAYBILLP. |
| Example | Example | Example |
| Example | Example | Example |
| Example | Example | Example |
Orders/IOP
| File | Library | Description |
|---|---|---|
| IOPHDRP | UWDASWPRDD | Order header. |
| IOPDETP | UWDASWPRDD | Order lines. |
| IOPERRP | UWDASWPRDD | Contains IOP errors. Any time the IOP determines an error on an order line during the pre-check period of
order processing, it's logged here. Fun fact: This was used to query for invalid/blank pharmacist numbers. |
| OMSLINP | UWDASWPRDD | Contains lines in OMS messages. Each line in an order confirmation message is a record in this file. That's right, legacy programming. |
Purchasing
| File | Library | |
|---|---|---|
| SRBPOH | UP1480BFVA | PO header files |
| INUSERP | WEBPRDD | Infonet users. Includes Infonet authorities. |
| SRBIMI | UP1480BFVA | Purchase suggestion codes. |
| SRBJRN | UP1480BFVA |
Distribution Centre
| File | Library | |
|---|---|---|
| SROSRO | UP1480BFVA | Stockroom/warehouse/inventory balance |
| SROITR | UP1480BFVA | Stockroom/warehouse/inventory transactions. |
| WH1LOC | UP1480BFVA | Location balances. |
| WH1LOP | UP1480BFVA | Location transactions. |
| PLDETP | UP1480BFVA | Pick list detail |
| URBPAL | UP1480BFVA | Putaway suggestions. |
Refer also to these files in other areas (see other tables): SRBPOH
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. |
| PW_TRNF | XL_WEBSPT | Contains transaction set information. We use transaction sets to hold cart lines in Web Orders. |