Difference between revisions of "Information Systems:Important files on Bart"

From uniWIKI
Jump to navigation Jump to search
 
(31 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
=Overview=
  
=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.
 +
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==
  
==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:
  
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:
   
−
{| class="wikitable"
 +
{| class="wikitable" style="width: 55%"
 
|-
  
|-
 
! IBM !! SQL !! Description
  
! IBM !! SQL !! Description
 
|-
  
|-
−
| Database || Database || We have 1 database: S1007B50. It's a DB2 database (for comparison, other types
+
| Database || Database || We have 1 database: S1007B50. It's a DB2 database (for comparison, other types include Microsoft SQL Server, MySQL, and Postgresql).
−
include Microsoft SQL Server, MySQL, and Postgresql).
  
 
|-
  
|-
 
| Library || Schema || A collection of files/tables. Similar to a PC folder.
  
| Library || Schema || A collection of files/tables. Similar to a PC folder.
Line 16: Line 17:
 
| Physical file || Table || Self-explanatory - the object that contains data. Object type is '''PF'''.
  
| 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
 +
| 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.
−
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.
  
| 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
 +
| 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.
−
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.
  
 
|}
  
|}
   
Line 45: Line 43:
   
 
=Important Files=
  
=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:
  +
  +
# For ASW files, the third character is revolving. For example, SROSRO is the physical file, and SR1SRO, SR2SRO etc. are logicals.
  +
# 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.
  +
  +
{| class="wikitable sortable" style="width: 55%"
  +
|-
  +
! 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 || WEBPRDD || Web Orders item file.
  +
|-
  +
| WSALTP || UWDASWPRDD || Web Orders alternate items (part of substitutions (ISS)).
  +
|}
  +
 
==Accounting==
  
==Accounting==
−
{| class="wikitable sortable"
 +
{| class="wikitable sortable" style="width: 55%"
 
|-
  
|-
 
! File !! Library !!
 
! File !! Library !!
 
|-
  
|-
−
| SROLOGGL|| UP1480BFVA || The G/L file. This file is massive.
 +
| 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.
 +
| SRODTA || UP1480BFVA || A/R transaction file.
 
|-
  
|-
−
| SROKBP || UP1480BFVA || Batch payments interface file.
 +
| SROLTA || UP1480BFVA || A/P transaction file.
 
|-
  
|-
  +
| SROKBA || UP1480BFVA || A/R batch input/interface file; invoices.
⚫
| Example || Example || Example
  
 
|-
  
|-
  +
| SROKBP || UP1480BFVA || A/R batch input/interface file; payments.
⚫
| Example || Example || Example
  
  +
|-
  +
| SROOVF || UP1480BFVA || G/L input/interface file. Implicated [[Information_Systems:Cannot_Close_Period| here]].
 
|}
  
|}
  +
  +
==Business Intelligence==
  +
These are Analyzer files, comprising the data warehouse component in ASW. They are implicated in many reports as well as in Infonet Analyzer tools.
  +
  +
{| class="wikitable sortable" style="width: 55%"
  +
|-
  +
! File !! Library !! Description
  +
|-
  +
| ANOSTK|| UP1480BFVA ||
  +
|-
  +
| ANOBAL|| UP1480BFVA ||
  +
|}
  +
  +
==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 [[Information_Systems:CNS_-_Customer_Names_Subsystems_(Contacts)|one of the major modules of UWD Extensions]]. Thus there are a number of files related to this module.
  +
  +
{| class="wikitable sortable" style="width: 55%"
  +
|-
  +
! 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:
  +
  +
{| class="wikitable sortable" style="width: 55%"
  +
|-
  +
! File !! Library !! Description
  +
|-
  +
| SRBNAM || UP1480BFVA || ASW contacts file. For reporting purposes (name, number, address), this file is probably the best one to use.
  +
|-
  +
| SRBSUK || UP1480BFVA || ASW contact bank data.
  +
|}
  +
 
==Carrier Interface==
  
==Carrier Interface==
−
{| class="wikitable sortable"
 +
{| class="wikitable sortable" style="width: 55%"
 
|-
  
|-
−
! File !! Library !!
+
! File !! Library !! Description
 
|-
  
|-
−
| CIWAYBILLP || UWDASWPRDD || More or less, the "master file" in Carrier Interface. This is the file that is
 +
| 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.
−
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
 +
| ATLDOWNP || Example || Example
 
|-
  
|-
−
| Example || Example || Example
 +
| ATLARCHP || UP1480BFVA || Example
 
|-
  
|-
−
| Example || Example || Example
 +
| CIDOWNP || Example || Example
 
|}
  
|}
   
 
==Orders/IOP==
  
==Orders/IOP==
−
{| class="wikitable sortable"
 +
{| class="wikitable sortable" style="width: 55%"
 
|-
  
|-
−
! File !! Library !!
+
! File !! Library !! Description
 
|-
  
|-
 
| IOPHDRP || UWDASWPRDD || Order header.
  
| IOPHDRP || UWDASWPRDD || Order header.
Line 86: Line 147:
 
| IOPDETP || UWDASWPRDD || Order lines.
  
| 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
 +
| 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.
−
order processing, it's logged here. Fun fact: This was used to query for invalid/blank pharmacist numbers.
  
 
|-
  
|-
−
| OMSLINP || XL_WEBSPT || Contains lines in OMS messages.
 +
| 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==
  
==Purchasing==
−
{| class="wikitable sortable"
 +
{| class="wikitable sortable" style="width: 55%"
 
|-
  
|-
 
! File !! Library !!
 
! File !! Library !!
Line 107: Line 167:
   
 
==Distribution Centre==
  
==Distribution Centre==
−
{| class="wikitable sortable"
 +
{| class="wikitable sortable" style="width: 55%"
 
|-
  
|-
 
! File !! Library !!
 
! File !! Library !!
Line 115: Line 175:
 
| SROITR || UP1480BFVA || Stockroom/warehouse/inventory transactions.
  
| SROITR || UP1480BFVA || Stockroom/warehouse/inventory transactions.
 
|-
  
|-
−
| WH1LOC || UP1480BFVA || Location balances.
 +
| WHOLOC || UP1480BFVA || Location balances.
 
|-
  
|-
−
| WH1LOP || UP1480BFVA || Location transactions.
 +
| WHOLOP || UP1480BFVA || Location transactions.
  +
|-
  +
| PLDETL || UP1480BFVA || Pick list detail
  +
|-
  +
| URBPAL || UP1480BFVA || Putaway suggestions.
 
|}
  
|}
   
Line 123: Line 187:
   
 
==WebSmart: Infonet and Web Orders==
  
==WebSmart: Infonet and Web Orders==
−
{| class="wikitable sortable"
 +
{| class="wikitable sortable" style="width: 55%"
 
|-
  
|-
 
! File !! Library !!
 
! File !! Library !!
 
|-
  
|-
−
| WSUSERP || WEBPRDD || Web Orders users. Login credentials, pharmacist numbers, Web Orders authorities specified in thsi file.
+
| 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.
−
This is the file read by the menu option '60 - Web Interface' in Extensions.
  
 
|-
  
|-
 
| INUSERP || WEBPRDD || Infonet users. Includes Infonet authorities.
  
| INUSERP || WEBPRDD || Infonet users. Includes Infonet authorities.
Line 135: Line 198:
 
|-
  
|-
 
| PW_SVARF || XL_WEBSPT || Contains smurfs associated with a valid smurf ID / session ID.
  
| 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.
 
|}
  
|}
   

Latest revision as of 13:54, 18 November 2022

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 WEBPRDD 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.
SRODTA UP1480BFVA A/R transaction file.
SROLTA UP1480BFVA A/P transaction file.
SROKBA UP1480BFVA A/R batch input/interface file; invoices.
SROKBP UP1480BFVA A/R batch input/interface file; payments.
SROOVF UP1480BFVA G/L input/interface file. Implicated here.

Business Intelligence

These are Analyzer files, comprising the data warehouse component in ASW. They are implicated in many reports as well as in Infonet Analyzer tools.

File Library Description
ANOSTK UP1480BFVA
ANOBAL UP1480BFVA

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.
SRBSUK UP1480BFVA ASW contact bank data.

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 UP1480BFVA 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.
PLDETL 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.
Retrieved from "https://owl.unipharm.com/mediawiki/index.php?title=Information_Systems:Important_files_on_Bart&oldid=14342"