This module covers the creation, maintenance and data manipulation of the database objects and different methods of querying the data from these objects using the SQL(Standard Query Language) Method.
IBM has improvised the database on the SQL side to take the advantage that SQL provides.
Advantages of using SQL:
Data Centric applications: Way of using SQL approach leads to developing data centric applications instead of process centric applications
Increased referential integrity: Using SQL enforces increased referential integrity over the traditional application enforced referential integrity, which will function only while using the application and not always.
Reduced code creation & Maintenance: By using SQL, more work can be offloaded to DB2 engine, reducing the amount of code to be coded, tested and maintained.
Performance Improvements: Since DB2 engine does more work at a much lower level within the operating system, the performance will be much better. The advancements made to the SQL query Engine (SQE) is also a reason for the improved performance.
Json and XML support through Json_table and Xml_table functions
Concepts:
Since DB2 is a relational database, the data is organized in tables. Each table has one or more rows and each row has one or more columns. There is a definite relationship between the tables to maintain the referential integrity.
Using SQL, the validations can be set at the database level using constraints.
Terminologies:
Schema: Database objects can be grouped in a schema. It is created as a collection library. The database objects created in a schema will be automatically journalled.
Table: Defines the structure of the information stored in that object. It is implemented as a physical file. A table can have a name which is different from the name with which the physical file object is created.
View: A name given to the result table produced by the SELECT statement. Used to filter the data or to use subset of fields. Implemented as a logical file without a key field.
Index: Contains set of pointers to the base table. Used by the database manager to sort and to improve the performance of the query. Implemented as a logical file with one or more keys specified while creating the index.
Constraints: Rules enforced between tables and on columns by the database manager
Triggers: A set of action to be performed automatically when a database event like INSERT, UPDATE, DELETE occurs on a table. Easier way to implement business rules, since they are coded at one place instead of repeating them for every application.
Ways of executing SQL statements:
- Using interactive SQL on green-screen interface
- Using Run SQL scripts component of Navigator for i
- Using RUNSQLSTM command to execute the statements coded in a source member
- Using a RUNSQL command within a CL program without using a source member
- Embedding SQL statements within High Level Language programs
By using SQL to manage your database objects on AS/400, you can efficiently handle complex database operations and maintain your database with ease. If you have any specific questions or need more detailed examples, feel free to ask!
