Difference between revisions of "Information Systems:Important files on Bart"
| Line 7: | Line 7: | ||
{| class="wikitable" |
{| class="wikitable" |
||
|- |
|- |
||
| − | ! IBM !! SQL |
+ | ! 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. |
||
| + | |- |
||
| ⚫ | |||
|- |
|- |
||
| Logical file || View || An alternate organization of the data in a PF/table, according to an index. "Points" to the data, |
| 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. |
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. |
||
| − | | Example || Example |
||
|- |
|- |
||
| + | | Field || Column || When making an edit in DFU, it applies to a single field in a single record. When using the SQL UPDATE |
||
| − | | Example || Example |
||
| + | command, you must filter for a single record using a where clause. There are advantages to each method. |
||
|- |
|- |
||
| Example || Example |
| Example || Example |
||
Revision as of 15:03, 10 January 2017
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. |
| Example | Example | |
| Example | Example | |
| Example | Example | |
| Example | Example |
Important Libraries
UP1480BFVA UWDASWPRDD
Important Files
Accounting
Carrier Interface
| File | Library | |
|---|---|---|
| Example | Example | Example |
| Example | Example | Example |
| Example | Example | Example |
| Example | Example | Example |
| Example | Example | Example |
WebSmart: Infonet and Web Orders
- XXITEMP