Deal of the Day

Wednesday 29 June 2011

SQL400 For Beginners Part - 1

Hi Guys....Today i just thought of sharing something on SQL400 for beginners...

SQL Relational Database In the relational model of data, all data is perceived as existing in tables. DB2 UDB for AS/400 objects are created and maintained as AS/400 system objects.

AS/400 System Terms and SQL Relational Database terms
System Terms
SQL Terms
Library. Groups related objects and allows you to find the objects by name.
Collection. Consists of a library, a journal, a journal receiver, an SQL catalog, and optionally a data dictionary. A collection groups related objects and allows you to find the objects by name.
Physical file. A set of records.
Table. A set of columns and rows.
Record. A set of fields.
Row. The horizontal part of a table containing a serial set of columns.
Field. One or more characters of related information of one data type.
Column. The vertical part of a table of one data type.
Logical file. A subset of fields and records of one or more physical files.
View. A subset of columns and rows of one or more tables.
SQL Package. An object type that is used to run SQL statements.
Package. An object type that is used to run SQL statements.
User Profile
Authorization name or Authorization ID.




SQL and system naming conventions
·      One can use either the system (*SYS) or the SQL (*SQL) naming convention in DB2® UDB for iSeries™ programming
·      System naming (*SYS)
In the system naming convention, tables and other SQL objects in an SQL statement are qualified by schema name in the form:  schema/table
·         SQL naming (*SQL)
In the SQL naming convention, tables and other SQL objects in an SQL statement are qualified by schema name in the form:  schema.table
Types of SQL Statements
        SQL schema statements, also known as data definition language (DDL) statements, ex - ALTER TABLE
       SQL data and data change statements, also known as data manipulation language (DML) statements, ex – DELETE, INSERT, UPDATE
       Dynamic SQL statements, ex – COMMIT, ROLLBACK
       Embedded SQL host language statements, ex – DESCRIBE, EXECUTE
      
SQL Objects
List of main SQL objects
·         Tables, Rows, and Columns
·         Aliases
·         Views
·         Indexes
·         Constraints
·         Triggers
·         Stored Procedures
·         User-defined functions
·         User-defined type
·         SQL Packages

SQL objects
SQL objects used on the AS/400 system are collections, tables, aliases, views, SQL packages, indexes, and catalogs. SQL creates and maintains these objects as AS/400 database objects. A brief description of these objects follows.
Tables, Rows, and Columns
A table is a two-dimensional arrangement of data consisting of rows and columns. The row is the horizontal part containing one or more columns. The column is the vertical part containing one or more rows of data of one data type. All data for a column must be of the same type.
Aliases
An alias is an alternate name for a table or view. You can use an alias to refer to a table or view in those cases where an existing table or view can be referred to.
Views
A view appears like a table to an application program; however, a view contains no data. It is created over one or more tables. A view can contain all the columns of given tables or some subset of them, and can contain all the rows of given tables or some subset of them. The columns can be arranged differently in a view than they are in the tables from which they are taken. A view in SQL is a special form of a non-keyed logical file.
Ex – The original file appears like:
PROJNOPROJNAMEDEPTNODEPTMGRPRSTAFF
CA2100Cost Automation C1100016012
CA2110Cost Programming D210001403
CA2112Circuit Design D010000903
CA2113Cost Control Prog C110001003



A view of the file selecting only the required fields:

PROJNOPROJNAME
CA2100Cost Automation
CA2110Cost Programming

Indexes
An SQL index is a subset of the data in the columns of a table that are logically arranged in either ascending or descending order. Each index contains a separate arrangement.
The arrangements are based upon Ordering, Grouping and Joining.
Ex - ORDER BY , GROUP BY etc
Constraints
Constraints are rules enforced by the database manager. DB2 UDB for AS/400 supports the following constraints:
Unique constraints - ensures that all values in a column are distinct
Referential constraints - is the rule that the values of the foreign key are valid only if:
       They appear as values of a parent key, or
       Some component of the foreign key is null.
Referential constraints are enforced during the execution of INSERT, UPDATE, and DELETE statements.
Triggers
A trigger is a set of actions that are executed automatically whenever a specified event occurs to a specified base table. An event can be an insert, update, or delete operation. The trigger can be run either before or after the event.
Stored Procedures
A stored procedure is a program that can be called using the SQL CALL statement. DB2 UDB for AS/400 supports external stored procedures and SQL procedures. External stored procedures can be any AS/400 program or REXX procedure.
User-defined functions
A user-defined function is a program that can be invoked like any built-in function. DB2 UDB for AS/400 supports external functions, SQL functions, and sourced functions. External functions can be any AS/400 ILE program or service program.
Consider a simple scenario where one wants to process some data. One can mention some selection criteria which can be expressed as a function SELECTION_CRITERIA(). Your application can issue the following select statement:
SELECT A, B, C FROM T
When it receives each row, it runs the program's SELECTION_CRITERIA function against the data to decide if it is interested in processing the data further. Here, every row of table T must be passed back to the application. But, if SELECTION_CRITERIA() is implemented as a UDF, your application can issue the following statement:
SELECT C FROM T WHERE SELECTION_CRITERIA(A,B)=1
In this case, only the rows and one column of interest are passed across the interface between the application and the database.
User-defined type
A user-defined type is a distinct data type that users can define independently of those supplied by the database management system. Distinct data types map on a one-to-one basis to existing database types.
SQL Packages
An SQL package is an object that contains the control structure produced when the SQL statements in an application program are bound to a remote relational database management system (DBMS). The DBMS uses the control structure to process SQL statements encountered while running the application program

Do write me @ iSeriesblogs@gmail.com for any query or you can simply post comments below.
Will continue in my next blog……
Till then happy learning

No comments:

Post a Comment