This TechTip is the first of a new subseries of SQL 101, solely dedicated to Data Definition Language (DDL). I’ll take you on a tour of the most important aspects of “modern” database creation and maintenance.

If someone asks you to create a new file for this or that reason, you’ll probably fire up SEU (or a “modern” editor, like WDSC or RDi’s LPEX Editor) and start typing in the specs using DDS. Possibly, while you’re doing this, you’re already thinking about the new code you’ll have to write to enforce some data-related rules, or the additional files (single or joined logical files, for instance) you’ll have to create, and a slew of other small but crucial details that a new file usually entails.

What if I tell you that most of this can be managed by the database with a significantly smaller effort from you? That’s the beauty of DDL. It allows you to take some, if not all, data-related validations from your RPG code and store them in the database, thus making them available to every program that accesses and, most importantly, manipulates the data.

In today’s context, where new applications need to connect seamlessly with your core data, it’s of paramount importance that your data self-manages as much as possible. Yes, you can use the stored procedures and user-defined functions discussed in the last TechTips to handle data validations—believe me, you’ll need them—but those will be used to enforce business validations, not data validations. It might seem the same thing, but it’s not, as you’ll see in the next TechTip, about the CREATE TABLE instruction. For now, let’s start at the beginning: creating a collection or schema.

Providing a Parent for Your SQL Objects: Creating a Schema

Now is a good time to revisit one of the first TechTips of this series and review the SQL terminology. If you don’t have the time to do it now, all you need to know for the moment is that a schema or collection is a “parent” for a group of SQL objects. It provides a common ancestor or a way to group them to a batch of related tables, views, indexes, and other SQL types of objects I’ll discuss later, a bit like a system library does for your physical and logical files.

A schema does more than that, however. Upon creation of a new schema, the database engine creates a library, a journal, a journal receiver, a catalog, and, optionally, a data dictionary. The catalog is a set of tables and views that stores metadata about the schema’s “children”— all the objects related to that schema.

Creating a new schema is a simple operation:

CREATE SCHEMA <schema SQL name>

FOR SCHEMA <schema system name>

LABEL <whatever description you think it’s appropriate for the Schema>

This might seem a lot, but it’s actually quite simple, because only <schema SQL name> is mandatory. There are other parameters, but let’s skip them for now. The schema SQL name must be unique in the database, and because it’s an SQL name and not a system name, it can be longer than the 10-character system-name limit. However, it can’t start with SYS or Q because those designations are reserved for system schemas. If you want to, you can specify the schema system name; as you might have guessed, this one is limited to 10 characters. Both names must be unique in their respective contexts. Finally, you’re allowed to specify a LABEL, which is similar to the TEXT keyword in a native object creation. It provides a description for the object.

Here’s an example of the simplest possible statement for the creation of a new schema, named MYSCHEMA:

CREATE SCHEMA MYSCHEMA

However, you might want to make this a little more descriptive by adding the LABEL part:

CREATE SCHEMA MYSCHEMA LABEL “Schema for MYAPP application”

Because I didn’t specify the FOR SCHEMA part of the statement and MYSCHEMA has less than 10 characters, the system will take the SQL name of the schema and use it as-is for the schema system name. If the name were longer, a name would be generated according to the following rules:

• The first four characters from within the delimiters will be used as the first characters of the system schema name.
• If the first four characters are all uppercase letters, digits, or underscores, an underscore and a five-digit unique number is appended.
• Otherwise, a four-digit unique number is appended.

Let’s apply these rules to a couple of examples. Consider the SQL schema name of ThisIsMyApp. The system would apply the rules above and give the schema the name This00001 if this was the first schema object in the system starting with This, but if it was the fifth object starting with This, the new object’s name would be This_00005. As another example, a schema named JABA3_Just_Another_Business_Application_With_No_Relation_To_Star_Wars in SQL would get the JABA_00001 system name. (Yes, I know Jabba the Hutt is spelled with two b’s). Actually, I never tried to create a schema with such a long name, but you get the idea, right?

You can create as many schemas as you’d like, but I usually use one per application or one per application environment. In other words, if application MYAPP has three environments (development, test, and production), I might create three schemas for it.

Schemas are, in many ways, like the data libraries you’re used to. By themselves, they don’t do anything. You need to add files to store your data. If you remember the new terminology, they’re called tables. In the next TechTip, I’ll show you how they are created. Until then, feel free to comment in the Comments section below!