Database Files in IBM i Using SQL (Magic xpa 3.x)

Defining Files

To describe database files in IBM i using the SQL Gateway:

1. In the SQL Collection, which is the SQL database on IBM i, use the IBM i STRSQL command. This requires license program: 5722SS1 - DBL Query and SQL Dev Kit.

2. Execute CREATE SCHEMA SAMPLECOLL to create the SQL collection.

An SQL collection provides a logical grouping of SQL objects. A collection consists of an IBM i library, a journal, a journal receiver, a catalog, and optionally, a data dictionary.

By default, all the tables generated under an SQL collection will be under journaling. You can create and own many collections.

Running an SQL Command

To run an SQL command, start the Run SQL Scripts GUI utility supplied with the IBM i Navigator, or execute the STRSQL command from a 5250 emulator.

Defining a Customer File

To define a customer file, use the CREATE TABLE SAMPLECOLL.CUSTFILE SQL/400 command, where:

• CUSTID is NUMERIC(6)

• CUSTNAM is CHAR(20)

• CUSTADR is CHAR(25)

• CUSTPH is NUMERIC(9)

The CUSTFILE table will be created in the SAMPLECOLL SQL database.