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 to narrow down by library and description, respectively.

Note: The files and libraries here are significant to the user, not the operating system i.e. significance in this context refers to business and user impact, not system operation. You could view this as a list of important "data" files and libraries,  but it also describes libraries that house important program objects, not just business data.

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

The files listed below are physical files. When accessing files using record-level access (DFU) i.e. non-SQL methods, you may want to access the logical file instead, as logical files allow you to access a record using some useful index i.e. item number. When determining the name of the logical file, there are two general conventions:

1. For ASW files, the third character is revolving. For example, SROSRO is the physical file, and SR1SRO, SR2SRO etc. are logicals.
2. For files associated with UWD Extensions, or other custom development, physical files are mostly suffixed with a P, and cognate logicals are L1,L2 etc. i.e. XXITEMP -> XXITEML1

Items

These files are rebuilt nightly. Prior to the rebuild, an ALCOBJ (allocate object) is performed, which attempts to lock the file down so that the rebuild occurs successfully. If the file is in use e.g. early-morning Web Orders usage (which uses WSITEMP files), the rebuild is skipped.

File	Library
XXITEMP	UWDASWPRDD	Consolidated item master file (CIMF). Associated with many trigger programs. Read heavily by Infonet. Very useful for item queries as it contains a lot of information.
WSITEMP	UWDASWPRDD	Web Orders item file.
WSALTP	UWDASWPRDD	Web Orders alternate items (part of substitutions (ISS)).

Accounting

File	Library
SROLOGGL	UP1480BFVA	The G/L transaction log 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	UP1480BFVA	G/L interface file. Implicated here.

CNS/Customer information

The CNS tables are relational in design. While this design is good practice, it may make queries a little more difficult, as it necessitates joining several tables and linking on the unique ID (CNUNIN).

CNS

CNS is one of the major modules of UWD Extensions. Thus there are a number of files related to this module.

File	Library	Description
CNUSDALP	UWDASWPRDD	User data elements.
CNCLUVE	UWDASWPRDD	Customer-vendor linkage. Individuals are linked to a partner.
CNNAMEP	Example	Example
CNALPHP	Example	Example

Other

Customer information can also be found in other tables:

File	Library	Description
SRBNAM	UP1480BFVA	ASW contacts file. For reporting purposes (name, number, address), this file is probably the best one to use.

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.
ATLDOWNP	Example	Example
ATLARCHP	Example	Example
CIDOWNP	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.
WHOLOC	UP1480BFVA	Location balances.
WHOLOP	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.