(Tutorial : AS400) Chapter 4 - SQL

Now that we've created a library and saw some basic AS/400 features let's have a look at how we can create and manipulate tables using SQL.

You must create the DEMO library and define it has your current library to proceed with this chapter.

Type the following Start SQL command on the system prompt:

STRSQL

This way you begin a SQL session in AS/400. This application accepts most of the common SQL syntax (CREATE,INSERT, DELETE, SELECT, DROP, etc).

Creating a table with SQL:
From the SQL command line we're going to create the CLIENTS table and then insert some rows. For this tutorial let's assume that all phone numbers have, at most, 9 digits.

Press F3 to leave the table view and type the following commands. Type:

CREATE LIBRARY DEMO/CLIENTS( id_cli numeric(10) PRIMARY KEY, name_cli char(50), birth_cli date, phone_cli numeric(9) )

Note: You can place the code in separate lines (like in the example) or in a continued fashion (without indentation) as long as you don't press the Enter key before you finish the command.

A message will appear saying that the table was created.

If you want to see more empty command lines just press Page Down or move the mouse scroll down. Now Type:

SELECT * FROM CLIENTS

This way you can see that the table was created and it has no information in it. So let's insert some new rows.

Press F3 to leave the table view and type the following commands.

INSERT INTO CLIENTS (ID_CLI, NAME_CLI, BIRTH_CLI, PHONE_CLI) VALUES (1, 'Mary', '12/09/1967', '999999999')

INSERT INTO CLIENTS (ID_CLI, NAME_CLI, BIRTH_CLI, PHONE_CLI) VALUES (2, 'Tom', '09/01/1979', '123456789')

Do the SELECT command again to check if the rows were correctly inserted

Press F3 to leave the SQL command line. A set of options will appear. On the field you should write the option number. Try typing a number outside the option range (for instance 5) and then press Enter. As one would expect, an error message appears on the bottom of the screen. Try pressing F3 or inserting another value. It won't work because the screen is blocked. You will probably find some blocking errors and when you do press Esc the screen will go back to normal.

Now select the option 1 to exit the application, saving the session.


SQL Built-In Functions:
There are some very useful SQL built-in functions on the AS/400 system.

Basic Functions
If you know SQL you may already be familiar with these functions.
MAX -Returns the maximum value from a set of pre-defined values.
MIN -Returns the minimum value from a set of pre-defined values.
AVG -Returns the average value of a set of pre-defined values.
SUM -Returns the sum of a set of pre-defined values.
COUNT -Returns the number of elements in a set of pre-defined values.

Example (returns the maximum ID_CLI value from all the rows in the table CLIENTS):
SELECT MAX(ID_CLI) FROM CLIENTS


Numeric Functions
ABS(N) -Returns the absolute value of N.
CEILING(N) -Returns the rounding of N to the unit above.
FLOOR(N) -Returns the rounding of N to the unit below.


String Functions
CHAR(N) -Returns the the string representation N.
CHAR_LENGTH(S) -Returns the length of a string.
CONCAT(S1, S2) -Concatenates S1 with S2.
SUBSTR(S, I, L) -Returns a substring of S, starting at index I of lenght L.
LOWER(S) -Returns the lowercase representation of S.
UPPER(S) -Returns the uppercase representation of S.
TRIM(S) -Removes spaces from the beggining and and of S.


You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial
Read More...

(Tutorial : AS400) Chapter 3 - Libraries

A library is similar to a folder in Windows. In AS/400 a library is another object that can contain other objects (executable objects, source files, etc).

Libraries can't contain other libraries. AS/400 is structured as a list, the opposite of Windows which has a tree-like structure.

Creating a library:
We're now going to create a library, called DEMO, where we'll place all the files from this tutorial. Type the command:

CRTLIB DEMO

Your library is now created.

Changing the current library (CURLIB):
You can change the current library towork more easily with objects.This way you don't have to specify the library name each time youwant to work with a file.

To change the library you are currently in type:

CHGCURLIB DEMO

All the objects you create will be placed on your CURLIB (if you don't explicitly specify the library name).

If you want to refer to the library you are currently in you can use it's name or the keyword CURLIB.

You can change your default library (the library where you are when you enter the system) so that you don't have to change your current library each time you enter the system:

CHGPRF CURLIB DEMO

Be aware that if you don't change your opening or your current library, you can get some compilation errors. If you have a reference to a file (without it's library name) in your source code the compiler won't find it if it is placed on another library.

Libray Lists:
Every command we use is stored in a specific system library. When a command library isn't explicitly identified, the system will search for the command in every library in its library list until the command if found. So, if you have 2 commands with the same name on different libraries, the system will execute the one that is placed on the up most library on the list.

You can only have files with the same name in the same library if they are of different types.

You can see the library list with the command:

DSPLIBL

You can see in the listing that there are different types of libraries:

SYS: System libraries. All the essential objects to the system (commands, applications, compilers, etc).
CUR: The library you're currently in.
USR: User libraries (can be created by the user or the system manufacturer).

Press F3 to go back to the previous screen.


You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial
Read More...

(Tutorial : AS400) Chapter 2 - Commands

In AS/400 the commands can be executed from the system prompt. You can find it on the lower part of the screen.

Underneath the system prompt there is usually a list of function keys with tasks specific to the menu you are currently in.

Place the cursor at the prompt line,type GO and then press F4. A new screen will appear, specific to the GO command, where you can define all the options for this command.The F4 key can help you complete the syntax of most AS/400 commands.

Type MAJOR on the “Menu” option and press Enter. This shows the same result as typing GO MAJOR in the command line. This command shows you a list with the most important commands in AS/400.


The word “More...” on the bottom-right side of the list shows that there are lines that aren't visible. You can see these lines by pushing Page Down or using the mouse scroll.


Place the cursor on top of any of the options on the list and press F1. A window will appear with help about the option you chose. You can use this feature in most of the menus and applications. To close the help window press F3.


You can choose an option by typing its number on the prompt and pressing Enter.


Let's go back to the start screen. Press F3.

Command Syntax:
AS/400 commands usually have two parts (often with three letters each):a verb and a noun. For instance, CTRLIB is the create library command and it has a verb CRT (create) and a noun LIB (library). There are some exceptions, like the GO command we saw earlier. In the following list you can see frequently used command verbs and nouns.

VERB MEANING
---- -------
CPY - Copy
DSP - Display
DLT - Delete
WRK - Work

NOUN MEANING
---- -------
DEV - Device
F - File
MSG - Message
SPLF - Spool File

If you can't remember a command syntax, use the command GO VERB, which will show you a list of commands ordered by their functions.


You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial

Read More...

(Tutorial : AS400) Chapter 1 - Introduction

AS/400 is a computational platform launched in 1988 by IBM. Currently it is officially named System i5, although the term AS/400 is still widely used, because of that we will be using the term AS/400 throughout the tutorial, just be aware it's not its official name anymore.

The machine's operating system is usually OS400. This system has several application from database managers to compilers, editors, etc. AS/400 supports several programming languages like Java, C, SQL, Assembly, COBOL, PHP, etc.

Minimum requirements to complete the tutorial:

Basic programming knowledge.
Basic database knowledge (relational model, SQL).
Access to an AS/400 server.
Access terminal to the AS/400 server (in this tutorial the examples are given using Moshasoft).
A small application will be built through each chapter. After the last chapter the application will be able to manage a set of shops and their clients. At the end of each chapter you will be given the source code to the complete application.


You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial

Read More...