Is there a difference between Collection and Library in SQL?
db2, sql
Solution
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.
Problem
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'; ```