Is there a difference between Collection and Library in SQL?

Question

A task assigned to us by our professor states that we need to do the following:

Submit an sql script file containing your SQL statements to the following questions

• CREATE A COLLECTION
• CREATE ALL THE TABLES FROM ASSIGNMENT 1 SOLUTION UPLOADED TO BLACKBOARD
• ADD THE PRIMARY KEYS AND FOREIGN KEYS TO THEM
• INSERT (MINIMUM OF 3 RECORDS) TO EACH TABLE
• UPDATE AND DELETE (MINIMUM OF 1 RECORD) FROM EACH TABLE

However, in non of the lectures has he used the term collection, I've always heard library and some other stuff. What is a collection?

I am using notepad++ and set the language to SQL, and I typed in

CREATE COLLECTION, however, create highlights in blue but collection does not have a colour assigned to it (nor does Library).

When I tried googling for an answer, I got this from IBM

"An SQL collection is the basic object in which tables, views, indexes, and packages are placed"

So a collection would just be a library wouldn't it?

so if that's the case, then in iSeries (AS/400) I would type on the command line

CREATE COLLECTION ASSIGN1

but in a script would that be the same thing?

Thanks for your time.

EDIT My professor sent me this as an example, a .sql file that opens in a program from iSeries called "Run SQL Scripts", however, he didn't explain anything, just sent me this as an example.....so is it safe to assume Collection is the same as creating a Library?

CREATE COLLECTION FARA042;

CREATE TABLE FARA043.EMPLOYEE (
    EMP_NUM VARCHAR(10) CONSTRAINT FARA043.EMPLOYEE_PK PRIMARY KEY,
    EMP_FNAME VARCHAR(50),
    EMP_LNAME VARCHAR(50));

SELECT * FROM FARA043.SYSTABLES;
SELECT * FROM FARA043.SYSCOLUMNS
    WHERE TABLE_NAME = 'CHARTER';

Answer 1

You are correct. On IBM i (formerly known as iSeries, System i) the terms Library, COLLECTION, and SCHEMA all refer to the same thing. IBM now uses the term SCHEMA instead of the term COLLECTION, to conform to newer SQL standards, but they are synonymous. However, the term COLLECTION has been deprecated, and therefore should no longer be used.

There are however some subtle differences between CRTLIB and `CREATE SCHEMA' (or CREATE COLLECTION).

The CL command CRTLIB allows you to specify the description of the library, just as any IBM i object has an object description. You can also specify whether the library is to be treated as a *PROD or *TEST library when someone is debugging. On IBM i, when a developer starts debugging, one of the settings is a safety feature indicating whether the session will be allowed to update files (tables) in a *PROD library or not.

The SQL CREATE SCHEMA statement, on the other hand, not only creates a library, but sets it up with catalog views and automatic database journalling (logging).

Once you have created a schema in SQL, you can return to CL and use the CHGLIB command to set the library type and description, thus having the benefits of both methods.

One other difference, the SQL CREATE SCHEMA statement will allow you to create schemas with names longer than the IBM i 10-character standard. If you do this, I strongly suggest that you also give it a valid 10-character OS object name, by using the FOR SYSTEM NAME clause, otherwise the OS will then be forced to generate a 10-character library name.